texts: Composed primary keys : "should" duplicates and null values be accepted, and according to which norm?

texts:
I just passed some SQL test including a question about the possibility to have duplicates in a primary key column.
The expected answer was NO.  Mine is, "Yes, if the column is part of a composed primary key".

I did some testing to check that I was right, and my MySQL has no problem with this at all.
Null values, on the other hand, weren't accepted.  Only converted to 0 if using non-strict mode

I tried to check for the official norm, which I assume would be the iso specification... but it's not free : https://www.iso.org/obp/ui/fr/#iso:std:iso-iec:9075:-1:ed-5:v1:en

I'm not sure using columns allowing duplicates as part of primary keys make much sense, but fact is, it's possible.

Is what I'm seeing with MySQL specific to that DBMS?

I don't have any other DBMS at the current time, but would be interested to know if SQL Server, PostgreSQL or Oracle Database have stricter restrictions about this.

Edit : here's what I did on my MySQL

DROP DATABASE IF EXISTS pk_dup_test;
CREATE DATABASE pk_dup_test;

DROP TABLE IF EXISTS `pk_dup_test`.`datadup`;
DROP TABLE IF EXISTS `pk_dup_test`.`datasimple`;

CREATE TABLE `pk_dup_test`.`datasimple` (
  `id`  INT,
  `val` INT,
  CONSTRAINT datasimple_primary  PRIMARY KEY (`id`)

);
###############################
INSERT INTO `pk_dup_test`.`datasimple`(id, val)
VALUES (1, 1), (2, 22), (3, 31337), (4, 42), (5, 7), (6, 666), (7, 62);

CREATE TABLE `pk_dup_test`.`datadup` (
   `id1` INT UNIQUE
  ,`id2` INT
  ,`id3` INT UNIQUE
  ,`val` INT
  ,`name` TEXT

  ,CONSTRAINT datadup_primary        PRIMARY KEY (`id1`, `id2`, `id3`)
  ,CONSTRAINT datadup_foreign_naive  FOREIGN KEY (`val`) REFERENCES `pk_dup_test`.`datasimple`(id)
  ,CONSTRAINT datadup_foreign_on_pk  FOREIGN KEY (`id1`) REFERENCES `pk_dup_test`.`datasimple`(id)
);
########################################
DELETE FROM `pk_dup_test`.`datadup`;
INSERT INTO `pk_dup_test`.`datadup` (id1, id2, id3, val)
VALUES
     (1, 2, 3, 4)
    ,(2, 2, 2, 4)
    ,(3, 2, 1, 2)
    ,(5, 0, 4, 2)
;

Everything works fine, with strict mode.

I checked the table constraints and inserted lines. Nothing is missing. (and 3 lines have id2=2)

The tables are using InnoDB.
Tested with  phpMyAdmin, on WAMP  and MySQL version 5.6.17.


texts: Versioning entities in db with draft option, select query

texts:
I have postgresql db where i keep data for jee application. User can edit those data without creating new version of entity(draft). During edit, entity is copied as draft(version set to -1) after editing user can save all changes as new version. My problem is to create proper sql query to get data by version instead of extract all of them and do it in application.

Table schema:

id(uniq), field_id,version,otherFields....

id is uniq value and automatically incremented. When user start editing new field is created with field_id=parent.id. So i can extract all versions of field using group by field_id

Case 1

I want to get current version which means max value of version field or version set to -1.

Case 2

I want to get specific version. It is not that easy because data in db are not changed if field was not edited(to save space). For example field a was created with version 1 and then changes was done on other fields. Current global version is 7 but field a still have one entry in db with version=1(this field was not edited). So to extract specific version different than current i need to get MAX(version) where version<=:selected_version

Example data:

id(uniq),field_id,version
1,1,1
3,1,7
55,1,-1
53,53,14

Entity with field_id=1 has 2 version and draft(user is editing it).

Entity with field_id=53 has one version and no draft.

I hope that my explanation of problem is understandable. Could you help me with those sql query? It could be two queries, one for actual version with draft and other for old versions.

Case 2 probably solution

I think that specific version i can extract using such query

select field_id, max(version) ver from field_mappings where version<4 and version!='-1' group by field_id

I don't know why i can't extract id. When i change query to

select *, max(version) from field_mappings where version<4 and version!='-1' group by field_id

Then i got error

ERROR: column "field_mappings.id" must appear in the GROUP BY clause or be used in an aggregate function
SQL: 42803

And still don't know how to add case for draft(max(version) or version=-1).


texts: MS SQL Server 2012 Failover Cluster connection issue

texts:
I was about to give up with this but I'll give it a go here if anyone can help.

I've set up a fail-over cluster SQL Server with 2 SQL nodes, Domain controller and file witness. All seems to work fine, cluster manager shows no errors, I can move the SQL Server role from one node to the other. However, when using SQL studio management to run some queries, I can only connect from the node the SQL Server is running from.

I've opened all related ports (1433 and also 1434), changed SQL Server Network configuration (TCP/IP, Named Pipes, added aliases...), tried Windows Authentication and SQL Server, enabled remote connections check box... and still nothing :(

Is there anything I'm missing? I will eventually need to connect externally but if I can't connect even from one of the fail-over nodes to the other... :(


texts: Is SQL Server Data Tools a paid product of Microsoft or a Free Product?

texts:
I don't find an evidence in Microsoft Pages regarding SQL Server Data Tools Licensing and Price details.

https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-2017

Here all the instructions to download and install SQL Server Data Tools and no term mentioning it is Licensed.

As I understand searching for such details are to up to our responsibility but I didn't get a clue for it. So my question is SQL Server Data Tools is Free or Not? If Free then Shall we simply download and install as instructed in the below link? If Paid, where is the pricing details?

https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-2017


texts: I can login user SEC_MGR when I use the TOAD tool. But, It has error when i login by SQLPLUS:


texts: 32-bit versus 64-bit ODP.NET to Oracle connection

texts:
I seem to be having an issue connecting a 32-bit .NET application to a 64-bit server, without a full Oracle client.

If I build a 64-bit application, using the 64-bit ODP.NET dll's or a 64-bit instant client, everything seems to work fine. But if I build a 32-bit ODP.NET application or use the 32-bit instant client, it always goes "looking" for the 64-bit dll's, and errors out on me.

Has anyone encountered this before, or have any suggestions as to what issues we might be having?

We don't have the same problem connecting to SQL Server (of course, SQL server uses mixed-mode assemblies...)


texts:
I'm trying to set up replication between of a database between two servers. The servers are both Server 2012 R2 running SQL SQL Server 2014 SP2 CU5 (12.0.5546.0).

I'm following the tutorial from Microsoft and am stuck on this section (Lesson 2: Creating a Subscription to the Transactional Publication)

I have completed each of the steps as described, but when I view the synchronization status, I get the error

  *The process could not connect to Subscriber ''.

I have gone through and confirmed that I have created all the required local users and re-set their passwords to ensure they are correct. Both machines are on the same domain connected on the same physical LAN.

Any suggestions on how to go about diagnosing? I'm fairly certain it's security related, but just diagnosing what has me stumped.


texts: Is it possible to change collation for an existing database with PostgreSQL?

texts:
Such as change from C to utf8?
I tried this but seems not allowed.
postgres=# ALTER DATABASE mydb SET "Collate" To 'en_US.UTF-8';
ERROR:  unrecognized configuration parameter "Collate"


texts: syntax error at or near "current_timestamp" while creating partition table

texts:
I am trying to create a partition for the last 30 days. Yet when I run this code:

create table Test_Older30day partition of Test
    for values from (current_timestamp + interval'-30 day') to (current_timestamp);

I get this error:

syntax error at or near "current_timestamp"

I am confused, what am I doing wrong here?

the table looks like this:

create table if not exists Test(
source_name varchar,
event_name varchar,
service_data json,
stamp timestamptz
)partition by range(stamp);

data types match and I have used the partition by range on the date field.

I already know I can insert them manually

create table Test_Older30day partition of Test
        for values from ('2019-08-11') to ('2019-09-11')

like that. What I am trying to attempt is a more automated way of doing it. Basically the idea is to avoid typing it every single month or week. Just to run the query and it would use the current date.


texts: Does PostgreSQL support ICU collation's options and settings?

texts:
ICU Specifies different LDML Collation Settings. Some of them seem pretty interesting, especially the ones on case and accent,

  “Ignore accents”: strength=primary
  “Ignore accents” but take case into account: strength=primary caseLevel=on
  “Ignore case”: strength=secondary
  “Ignore punctuation” (completely): strength=tertiary alternate=shifted
  “Ignore punctuation” but distinguish among punctuation marks: strength=quaternary alternate=shifted potentially a better method of doing what

You can also see these documented here. Are these ICU options and settings possible with PostgreSQL 10 ICU collation support?

CREATE COLLATION special (provider = icu, locale = 'en@strength=primary');
SELECT 'Å' LIKE 'A' COLLATE "special"; # returns false

I've also tried the CLDR BCP47

  Starting with ICU 54, collation attributes can be specified via locale keywords as well, in the old locale extension syntax ("el@colCaseFirst=upper") or in language tag syntax ("el-u-kf-upper"). Keywords and values are case-insensitive. See the LDML Collation spec, Collation Settings, and the data file listing the valid collation keywords and their values. (The deprecated attributes kh/colHiraganaQuaternary and vt/variableTop are not supported.)

For that, this looked right

CREATE COLLATION special (provider = icu, locale = 'en-ks-level1');
SELECT 'Å' LIKE 'A' COLLATE "special"; # returns false

Also tried en-u-ks-level1 That method seems to be what the docs go for,

CREATE COLLATION german_phonebook (provider = icu, locale = 'de-u-co-phonebk');


texts: How to update the datetime difference between all of the rows of a table?

texts:
I've got the following table with some data in it:

CREATE TABLE schedules
(
    ID        INT PRIMARY KEY,
    StartTime DATETIMEOFFSET(4)
);

INSERT INTO schedules
VALUES (1, '2020-03-27 08:00:00.000 -05:00'),
       (2, '2020-03-27 09:00:00.000 -05:00'),
       (3, '2020-03-27 10:00:00.000 -05:00'),
       (4, '2020-03-27 11:00:00.000 -05:00');

Currently, the interval of start times is every one hour. I want to run a query to change this interval to every 45 minutes. I tried the following query but it didn't work.

UPDATE r2
SET StartTime = DATEADD(MINUTE, 45, r1.StartTime)
FROM schedules r1 JOIN schedules r2 ON r1.ID + 1 = r2.ID;

Any advice would be appriciated!


texts: Date comparsion within subquery fails with: "out-of-range value"

texts:
I have a an update statement which uses a subquery to filter records. My input table contains dates in a varchar field, and some of them are invalid.

CREATE TABLE [dbo].[input](
    [id] int,
    [START_DATE] [varchar](30) NULL
) ON [PRIMARY]

INSERT INTO [dbo].[input] ([id], [START_DATE]) VALUES(1, NULL) ;
INSERT INTO [dbo].[input] ([id], [START_DATE]) VALUES(2, '') ;
INSERT INTO [dbo].[input] ([id], [START_DATE]) VALUES(3, '01 JUL 0201') ;
INSERT INTO [dbo].[input] ([id], [START_DATE]) VALUES(4, '01 JUL 2016') ;

In the sample data only record 4 is valid.

My query:

select a.[id]
FROM (
    select [id], convert(datetime, [START_DATE],106) as csd
    FROM [dbo].[input]
    where len([START_DATE]) > 0
    and substring([START_DATE],7,5) > 2010
    and isdate([START_DATE]) = 1
    ) a
    Where a.csd < getdate()

I've had a play with various clauses to exclude junk:

len > 0 excludes nulls and ''.
Substring looks for a sensible year.
isdate checks again for a valid date.

I'm sure there's other more efficient ways of doing this, but the point being the subquery executes and correctly returns valid records. in this case id = 4. Which has a valid date 1 July 2016.

The outer query should simply now be comparing the converted date with today's date. But instead i get:

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

Why? What am i missing?

The inner query has filtered and converted the value I am evaluating.

Is there a better way of filtering this list to be used in an update query?


texts:
Recursive queries to find the tree-structure (parent-child) in a table are commonly found here, however most have the problem that the childID must be higher than the parentID.

A possible solution for this is using a query like (given parent X, find all children)

SELECT * FROM (
    SELECT @pv:=(
        SELECT GROUP_CONCAT( child_id SEPARATOR "," ) FROM link_parent_child WHERE FIND_IN_SET( parent_id, @pv )
        ) AS lv FROM link_parent_child
        JOIN
        (SELECT @pv:=$parentStartID$) tmp
    ) a
WHERE lv IS NOT NULL

OR Given child X, find all parents

SELECT * FROM (
    SELECT @pv:=(
        SELECT parent_id FROM link_parent_child WHERE child_id = @pv
        ) AS lv FROM link_parent_child
        JOIN
        (SELECT @pv:=$ChildStartID$) tmp
    ) a
WHERE lv IS NOT NULL

However, with a table of over 500k records, this is somewhat slow... (understatement)

CREATE TABLE `link_parent_child` (
    `parent_id` int(11) NOT NULL,
    `child_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
ALTER TABLE `link_parent_child`
    ADD PRIMARY KEY (`child_id`,`parent_id`);
    ADD KEY `child_id` (`child_id`),
    ADD KEY `parent_id` (`parent_id`),
    ADD KEY `parent_id_2` (`parent_id`,`child_id`);

even with indexes set, and only 10k records, the query takes more than 80 seconds to run.

So, this query (correct me if I'm wrong) is so slow because the inner part returns NULL for almost all rows, which (i guess) are stored in a temp table and filtered out by the outer part of the query. I guess that the query would speed-up if the NULL's are filtered out earlier, but is that possible?

My tree's have a dynamic length, but mostly not deeper than 3-10 layers. Should I take my losses and solve this programmatically, or is there a method to speed this up?


texts:
Looking at SQL Server maximum capacity limits on here: http://msdn.microsoft.com/en-us/library/ms143432(v=sql.120).aspx

Database size can be as big as 524,272 terabytes.

Here, you could see VM size limits on Azure: http://msdn.microsoft.com/en-us/library/windowsazure/dn197896.aspx

If you look at the table on the second page, the maximum number of data disks that could be attached is 16 (1 TB each). Does that mean on Azure, SQL Server cannot house more than 16 TBs and anything more than that would require a new VM?


texts:
Hope someone can help me to learn how SQL Server uses stats to estimate the number of records.

Test Scripts

USE [tempdb]

GO

CREATE TABLE t1
(
a INT NOT NULL,
b INT NOT NULL,
c INT CHECK (c between 1 and 50),
CONSTRAINT pk_a primary key(a)
);

GO

INSERT INTO t1(a,b,c)
SELECT number, number%1000+1, number%50+1
FROM master..spt_values
WHERE type = 'P' AND number BETWEEN 1 and 1000;

GO

CREATE STATISTICS s_b ON t1(b);
CREATE STATISTICS s_c ON t1(c);

GO

Sample Query

DECLARE @c INT=300
SELECT * FROM t1 WHERE b>@c
SELECT * FROM t1 WHERE b>300

Query 1

Estimated Number of rows 300

Query 2

Estimated Number of rows 700

Questions

Why such a huge difference between 1st and 2nd query row estimation.
2nd Query how SQL estimating to 700 from the Stats
Any good article to learn these.


texts:
I use MySQL  5.5.23-1~dotdeb.0 (Debian) and here is the relevant part of my.cnf

default-storage-engine = innodb
innodb_buffer_pool_size = 3G
innodb_log_file_size = 256M
innodb_thread_concurrency = 8
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 4M
innodb_additional_mem_pool_size = 20M

The problem is that no matter what value I choose for innodb_buffer_pool_size (I tried also 4G and 8G and 8000M). I get the following from mysql> SHOW global  VARIABLES;

| innodb_buffer_pool_size | 134217728

Result from the Tuning primer script:

INNODB STATUS
Current InnoDB index space = 621 M
Current InnoDB data space = 3.82 G
Current InnoDB buffer pool free = 0 %
Current innodb_buffer_pool_size = 128 M
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory

I appreciate your hints to fix this.


texts:
I think we are all familiar with database normalization.

My question is:  What are some guidelines that you use when you want to denormalize the tables?


texts:
I need an OODBMS for my website, to handle hierarchies and inheritance.
I use PHP and I have a feeling I am asking too much and will have to do with regular RDBMS...


texts: Best implementation of Service Broker when multiple readers needs to read their own message

texts:
I have implemented service broker using rusanu.com articles and advices.

Now I need to implement the following scenario :

The service broker will manage Many messages for differents workers and each worker has to receive it's very own message.

What I tried to implement is first keeping One queue for different workers , each worker having it's own Conversation_group_id to send and receive messages.

So to grab the messages I first have to get the conversation_handle of the first message related to my conversation_group

SELECT TOP 1 @ConversationHandle = Receiveside.conversation_handle
FROM sys.conversation_endpoints Sendside
JOIN sys.conversation_endpoints Receiveside on Receiveside.conversation_id = Sendside.conversation_id
WHERE Sendside.far_service = @ReceiveService
      AND Sendside.conversation_group_id = @CurrentConversationGroupID
      AND CONVERT( BIT , Receiveside.is_initiator ) = 0
      AND Receiveside.far_service = @SendService
      AND Sendside.state <> 'CD'
      AND Receiveside.state <> 'CD'

Then I just Receive the message on My queue using the where clause

WAITFOR(
   RECEIVE message_type_name ,
                conversation_handle ,
                message_body FROM ddr.DataRelayWebServiceReceiveQueue
   INTO @returnTable
  WHERE CONVERSATION_HANDLE = @ConversationHandle
) , TIMEOUT 1000;

It is functionnal but very poor on the performance side, it takes roughly 25 seconds to dequeue 1500 messages.

I choosed another way which is the creation of as many queues as needed so no collision on the table sys.conversation_endpoints. It takes roughly 16 seconds to dequeue 1500 messages.

I tried to implement Remus rusanu trick which is starting a conversation on the receiving side but without any success.

Any advice on which solution I may choose? I would ideally implement the simple case where two queues could share the same conversation_group_id naturally (without having to search in the table sys.conversation_endpoints), If any of you have already managed to do that I would gladly accept any advice.


texts:
I am trying to compare total number of records for a particular table in two databases. They are from the same database, but both the database are running in two different servers.

Table structure is same on both the tables , but no of records varies on both the tables.

I want to find out the missing records from both these tables.

mysql> select count(*) from dlfilerank;
+----------+
| count(*) |
+----------+
|    38259 |
+----------+
1 row in set (0.02 sec)

mysql> select count(*) from dlfilerank;
+----------+
| count(*) |
+----------+
|    38359 |
+----------+
1 row in set (0.02 sec)


texts:
I have a 100 csv files named sequentially 1,2,3....100.csv

I copy the csv files with this script:

COPY location FROM 'C:\Program Files\PostgreSQL\9.5\data\´1.csv' DELIMITER ',';

I would to execute the COPY with a single script that covers all the files (1,2,3 .... 100.csv).

I use pgadminIII on MS Windows


texts: SQL installation fails with "Could not find the Database Engine startup handle". Tried almost all suggestions from internet, but no luck

texts:
I am trying to install a new standalone SQL instance. It fails with

Could not find the Database Engine startup handle.
I am trying to install SQL server 2019 Express edition in windows 10 Home OS.

I have tried:

Uninstalling all instances, deleting all files and changing the account to "Local System" while installing. I even reset windows to factory settings . Still am getting same error.

I have exited installation wizard and then starting the SQL service from configuration manager. It either fails or stops immediately after starting.
The event log contains the following error:
FileMgr::StartLogFiles
2(The system cannot find the file specified.)
d:\dbs\sh\s19s\0924_133725\cmd\2\obj\x64retail\sql\mkmastr\databases\mkmastr.proj\modellog.ldf

But there is no D drive in my laptop at all

I have also given full control to local system, administrator, users group to the installation folder.


texts: How to do an in-place upgrade of SQL Server 2005 Express to SQL Server 2008 R2 Express

texts:
I would like to upgrade my SQL Server 2005 Express instance which is installed using this command:

SQLEXPR.EXE/qs /action=INSTALL /IACCEPTSQLSERVERLICENSETERMS
   /FEATURES=SQLEngine /INSTANCENAME=ABCD
   /SQLSVCACCOUNT="NT AUTHORITY\SYSTEM" /SECURITYMODE=SQL
   /SAPWD=abc#Wd1234 /TCPENABLED=1 /NPENABLED=1

but when I am trying to upgrade the server instance ABCD with this command

SQLEXPR_x64_ENU.exe /q /ACTION=upgrade /INSTANCENAME=ABCD /IACCEPTSQLSERVERLICENSETERMS=1

the instance ABCD is not upgraded to SQL Server 2008 R2 Express.

When I check ADD/Remove program list of OS, it shows me both SQL Server 2005 and 2008 R2 are installed (that may be I am doing a side by side upgrade but not a in-place one) and when I click on start button of OS and then goes to SQL Server 2008 R2 folder in AllPrograms list. It has a sub folder configuration tools which contains SQL Server Installation center. When I open the installation center it show various option to install SQL Server 2008 R2 or to upgrade from 2005 etc. I am confused whether SQL Server 2008 R2 is even installed on my machine after running the above 2nd query. Please help me where I am going wrong.


texts: compaction strategies and high performance storage

texts:
My apologies for this being a somewhat newbie question. However, my understanding of Cassandra is that is use three different compaction strategies:

are these tunable, or is this area a black box, i.e. there is nothing you can tweak,
if compaction can be tweaked are there any recommendations for high performance flash storage, the reason I ask is because I have come across comments in blogs etc  suggesting that "Compaction can be rough on flash storage".


texts: How to manage a large SQL Server database: transaction log, reduce size, alter table

texts:
Hello I have some confusion on how to manage a large SQL Server database.

Let's say size is 15GB, with 3GB available, in simple recovery mode.

Server is not managed by me, I cannot change the size, not even temporarily, nor detach/reattach backup/restore.

Let's say I can only manage the data and the structure of the database.

If I want to alter a table, to add a column, or delete only certain rows (before a datetime), or want to NULL a few nvarchar(MAX) columns to save space, how does this impact?

Often I receive the error "no more log space".

Then I shrink the DB, nothing has changed, and I have less space free...

I don't need the transaction log, so what can I do to perform these operations without incurring in error messages regarding the transaction log size?

Thanks!


texts:
Our co is getting a new SAN with all flash storage which would be attached to a physical 8 core 48 GB RAM server. I have been studying what is the best way to configure this setup. Our DB has mostly read transactions, write occurs only during midnight when data is interface from core ERP system to this data warehouse DB. There really isn't any performance issues in our old setup right now, except the usual long running queries while crunching millions of financial records every now and then.

So, I have some concern about setting partition offset, file allocation unit size and stripe unit size. I know that file allocation unit size should be 64kb for datafiles volume, stripe unit size provided by vendor is 256kb, but i am not sure about setting the partition offset. But before that, does this 3 parameters even matter when we are using SSDs now? I get that this 3 parameters is important to improve IOPs on traditional mechanical harddrives, but on SSDs, does it matter at all?

Also, since the new setup is all flash, does it matter to split data files, log files and tempDBs into their each volume?

Would appreciate if anyone could provide some insights or advice on the configuration part.


texts:
Recently, I started working on a project, where I came across many MySQL statements like this:

SELECT s.Important, c.Interesting
FROM table1 s, table2 c
WHERE s.IdForTableC = c.id

or

SELECT q.Important, c.Interesting
FROM table1 q, table2 p, table3 c
WHERE q.IdInTableP = p.id
AND p.IdInTableC = c.id
AND c.Measure = 1

I am completely new to optimizing MySQL and read a few guides that advised me to use EXPLAIN SELECT. I did and get type all for the above query and type index for the second one. I guess this means, that both queries should be optimized, but I don't know how. Is it even possible without changing the whole structure of the database?

EDIT: the EXPLAIN output of the first one is:

'1', 'SIMPLE', 'c', 'ALL', 'PRIMARY', NULL, NULL, NULL, '467', ''
'1', 'SIMPLE', 's', 'ref', 'IdInTableC', 'IdInTableC', '5', 'table2.c.id', '2', 'Using where'

and INDEXES are:

'table2', '0', 'PRIMARY', '1', 'id', 'A', '14184441', NULL, NULL, '', 'BTREE', '', ''
'table2', '1', 'otherID', '1', 'otherID', 'A', '92708', NULL, NULL, '', 'BTREE', '', ''
'table2', '1', 'IdInTableC', '1', 'IdInTableC', 'A', '3048', NULL, NULL, '', 'BTREE', '', ''


texts: What are the rules for using COLLATE in a query?

texts:
I am putting a script together, that gives me the permissions on database objects.

SELECT  permission_order=710
       ,permission_type = 'Object Level Permissions'
       ,db = db_name(),
login_=null,
role_=dp.name collate Latin1_General_CI_AS,
Obj = sys.schemas.name + '.' + so.name collate Latin1_General_CI_AS,
Permission = permission_name collate Latin1_General_CI_AS,
[script]=
'IF OBJECT_ID ('  + '''' + '['+ sys.schemas.name + '].[' + so.name + ']'  + '''' + ') IS NOT NULL ' + CHAR(10) + state_desc +
' ' + permission_name + ' on ['+ sys.schemas.name + '].[' + so.name + '] to [' + dp.name + ']' collate Latin1_General_CI_AS + CHAR(10) +
'ELSE ' + CHAR(10) +
'print ' + '''['+ sys.schemas.name + '].[' + so.name + '] - does not exist'''
+ CHAR(13)

from sys.database_permissions a
INNER JOIN  sys.objects so on a.major_id = so.object_id
INNER JOIN sys.schemas on so.schema_id = sys.schemas.schema_id
INNER JOIN sys.database_principals dp on a.grantee_principal_id = dp.principal_id
WHERE dp.name NOT IN ( 'public', 'guest')
AND a.class = 1

I want to use the word collate as few times as possible and still have a script that runs for multi-databases, multi-collations server.

How can I achieve that?
What is the rule for applying collate?


texts: Is there a way to obtain 'NaN' using only numbers?

texts:
PostgreSQL can contains NaN (not a number) value in numeric and float columns and variables: https://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-FLOAT

https://dbfiddle.uk/?rdbms=postgres_10&fiddle=1fdf20510b1b6a1416754599c60fc93f

Is there a way to obtain NaN different of inserting or updating directy to such value?

In this question I considered that all of these are the same:

insert into t (v) values ('NaN');
insert into t (v) values ('N'||'a'||'N');
insert into t (v) values (E'\116\101\116');
insert into t (v) values (chr(78)||chr(65)||chr(78));
update t set v='NaN';

I try things like:

insert into numeros (id, n1, n2) values (5, 1e306/1e-306, 1e306/1e-306); -- not representable
insert into numeros (id, n1, n2) values (6, 0/0, 1/0);  -- division by 0


texts: SQL Server Mirroring and automatic failover - why don't all DBs failover to the mirror?

texts:
On SQL Server 2008 R2, I'm mirroring 20 databases on my SharePoint SQL Server with a witness, high-safety.  All mirrored DBs have the default mirroring_connection_timeout = 10.  And, occasionally, a handful of DBs will failover to the mirror (not more than once a month).  Today, it was 3 DBs and the only abnormality I noticed was some highish CPU at the time of failover.

I assume that the 10 second timeout for receiving a PING has been exceeded and failover occurs.  What I don't understand is why, if all DBs have the same timeout they don't all failover when this happens?


texts: using regexp substitution part in postgres string functions

texts:
I hit this question trying to answer one in SO. When I pass \1 as first argument to lpad(string text, length int [, fill text]) function, it looks like it interprets \1 as regexp substitution part looking for string to pad chars, but then interprets same \1 as text (it is literally - '\1') calculating its length to substract from second argument: lpad('aaa',5,'b') calculates 5-length('\1'), not 5-length('aaa').

So the question is - are we meant to use SQL string functions with regexp substitution part meta syntax? What are the restrictions?
Eg: concat, format, repeat, substr understand it, while length, initcap, ascii interpret it as just text.

Now I see that length, initcap, ascii accept string as argument, while format, repeat accept text, but then ltrim(text... and lpad(text... seem to be mix - they "understand" regexp metasyntax to find position and then fail to "convert string to text" to count input length?..

Here an example:

t=# with a as (select 'a 2 b'::text s)
t-# select
t-#   regexp_replace(s,' (\d){1} ','')
t-# , regexp_replace(s,' (\d){1} ','\1') _regular
t-# , regexp_replace(s,' (\d){1} ',concat('.','\1','.')) concat_regular
t-# , regexp_replace(s,' (\d){1} ',length('.\1')::text) length_string
t-# , regexp_replace(s,' (\d){1} ',reverse('\1')) reverse_string
t-# , regexp_replace(s,' (\d){1} ',initcap('\1')) initcap_broken
t-# , initcap(regexp_replace(s,' (\d){1} ','\1')) initcap_orig
t-# , regexp_replace(s,' (\d){1} ',ltrim('\1','a')) ltrim_broken
t-# , regexp_replace(s,' (\d){1} ',lpad('\1',5,'.')) lpad_broken
t-# , regexp_replace(s,' (\d){1} ',ascii('\1')::text) ascii_broken
t-# , regexp_replace(s,' (\d){1} ',format('\1')::text) format_regular
t-# , regexp_replace(s,' (\d){1} ',repeat('\1',3)) repeat_regular
t-# from a
t-# ;
 regexp_replace | _regular | concat_regular | length_string | reverse_string | initcap_broken | initcap_orig | ltrim_broken | lpad_broken | ascii_broken | format_regular | repe
at_regular
----------------+----------+----------------+---------------+----------------+----------------+--------------+--------------+-------------+--------------+----------------+-----
-----------
 ab             | a2b      | a.2.b          | a3b           | a1\b           | a2b            | A2b          | a2b          | a...2b      | a92b         | a2b            | a222
b
(1 row)

Time: 0.420 ms


texts: Can someone please tell me where I went wrong for an exercise submission?

texts:
I recently submitted an assignment in a group for our databases class and we lost points on one of the exercises and the correction is unclear. If someone could examine the question, the submission and the grader's comment for me and tell us what we did wrong I would greatly appreciate it.

Question:

  Given a set of cities in some country, we would like to design an ER-diagram which records the distance between every pair of cities. Assume the attributes for each city is its name and its population with the name being unique, i.e., no 2 cities have the same name. Form an ER-diagram to model this application.

Our submission:

In this example, we see that there is a city entity set, and a country entity set. The city set has attributes of Cname, to distinguish between unique cities, and population. The relationship between country and city is “Located In” and is an M-1 relationship, because there are many cities in only one country. Additionally, city has a recursive relationship that measures the distance between a city and another unique city. The relationship between cities is “Distance” and has a M-1 relationship as well, because the distance from a single city can be compared to many other cities in a country.

The comment:
"Use distance as a relation."

Any help is appreciated. Thank you!


texts: The OR clause in a join condition takes more time to execute in SQL Server

texts:
The below query in SQL Server takes more time [approx 600ms] to execute. Based on the query execution plan. I figured out that the code indicated is the culprit. How can I try to optimize this query?

SELECT
DISTINCT ur.UserId, o.CompanyId
--*
FROM [eapp].[UserRole] ur
INNER JOIN [eapp].[Users] u ON u.UserId = ur.UserId AND u.AccountId = ur.AccountId
LEFT OUTER JOIN eapp.UserCompany uo ON uo.UserCompanyId = ur.UserCompanyId AND uo.AccountId = ur.AccountId
LEFT OUTER JOIN eapp.ProjectUser pu ON pu.ProjectUserId = ur.ProjectUserId AND pu.AccountId = ur.AccountId
LEFT OUTER JOIN eapp.Project p ON p.ProjectId = pu.ProjectId AND p.AccountId = ur.AccountId
INNER JOIN (SELECT CompanyId, ParentId, AccountId FROM [eapp].[GetCompanyTree](1)) o
  ON (/* the problem code is here */
      o.CompanyId = uo.CompanyId
      OR p.CompanyId = 1
     )
WHERE ur.IsActive = 1 and ur.AccountId = 1 AND u.IsActive = 1

The Query execution plans are downloadable from here
I though that there can be a union all that has the 3 statements that select the users in the organizations and the users in the projects and the users that are not allocated to any project nor belong to any organization. But that seems to be a bit clumsy approach to me. Can any one help me optimize this query to bring down the execution time?

I have tried the following. It's  NOT a good way to approach this problem. Maybe experts here can help me fix this issue with the best practices.

SELECT DISTINCT ur.UserId, o.CompanyId FROM [eapp].[Users] u
INNER JOIN [eapp].[UserRole] ur ON u.UserId = ur.UserId AND u.AccountId = ur.AccountId
INNER JOIN eapp.UserCompany uo ON uo.UserCompanyId = ur.UserCompanyId AND uo.AccountId = ur.AccountId
INNER JOIN (SELECT CompanyId, ParentId, AccountId FROM [eapp].[GetCompanyTree](1)) o ON (o.CompanyId = uo.CompanyId
)
WHERE ur.IsActive = 1 and ur.AccountId = 1 AND u.IsActive = 1

UNION ALL

SELECT DISTINCT ur.UserId, P.CompanyId FROM [eapp].[Users] u
INNER JOIN [eapp].[UserRole] ur ON u.UserId = ur.UserId AND u.AccountId = ur.AccountId
INNER JOIN eapp.ProjectUser pu ON pu.ProjectUserId = ur.ProjectUserId AND pu.AccountId = ur.AccountId
INNER JOIN eapp.Project p ON p.ProjectId = pu.ProjectId AND p.AccountId = ur.AccountId
WHERE ur.IsActive = 1 and ur.AccountId = 1 AND u.IsActive = 1

UNION ALL

SELECT u.UserId,null FROM eapp.Users as u
INNER JOIN eapp.UserRole as ur on u.UserId = ur.UserId and ur.AccountId= u.AccountId
where u.IsActive=1 and ur.IsActive = 1 and u.AccountId=1 and ur.AccountId=1 and U.UserId
not in (
    select distinct UserId from eapp.ProjectUser where isactive=1
    union all
    select distinct UserId from eapp.UserCompany where isactive=1
)


texts: Slow query log of wp_options WHERE autoload = 'yes'

texts:
Slow query log:

# User@Host:
# Query_time: 181.648376 Lock_time: 2.114138 Rows_sent: 1425838 Rows_examined: 1663532
SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes';

Table has only 650 rows.
Any ideas how can this happen?


texts: Turning "Set Statistics IO" for stored procedure doesn't show all tables used in stored procedure - why?

texts:
I'm reviewing a stored procedure that's been a performance problem. And I just realized that some of the tables which are used in the stored procedure don't even show up AT ALL in the results from Set Statistics I/O?
Is this normal, or a bug? It's a little disturbing because I use the results of Set Statistics I/O a lot, when figuring out what to index.
The tables that don't show up in the output of Set Statistics I/O are in a subselect on the main query, like the ActivityFeedCache table below:
SELECT *
FROM (
    SELECT AF.ActivityFeedID
        ,A.ActivityType AS ActivityType
        ,A.IconClass
        ,AF.SubscriptionID
        ,AF.ObjectID
        ,AF.ObjectType
        ,(
            SELECT L.Locale
                ,AFC.LocalizedMessage
            FROM ActivityFeedCache AFC
            JOIN Locales L
                ON AFC.LocaleID = L.LocaleID
            WHERE ActivityFeedID = AF.ActivityFeedID
            FOR JSON PATH
            ) AS MessageJSON
    FROM ActivityFeed AF
    ETC
    ETC
    ) AS Results

This is SQL Server 2017 (RTM-CU13).
EDIT:
I've narrowed down to a small query that shows a missing table in the Statistics IO output. The Locales table is missing in Statistics I/O, but shows up in the execution plan. Here it is:
-- Locales table doesn't show in statistics i/o output
SELECT AF.ActivityFeedID
        ,(
            SELECT L.Locale
                ,AFC.LocalizedTeamMessage AS LocalizedMessage
            FROM ActivityFeedTeamCache AFC
            JOIN Locales L
                ON AFC.LocaleID = L.LocaleID
            WHERE ActivityFeedID = AF.ActivityFeedID
            FOR JSON PATH
            ) AS TeamMessageJSON
    FROM ActivityFeed AF
Where AF.ActivityFeedID = (select top 1 ActivityFeedID from ActivityFeed)

Here's the Statistics I/O output:

(1 row 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 'ActivityFeedTeamCache'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ActivityFeed'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

And here's a small chunk of the actual execution plan XML, just so you can see the Locales table is actually being used:
  <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
    <DefinedValues>
      <DefinedValue>
        <ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Locales]" Alias="[L]" Column="Locale" />
      </DefinedValue>
    </DefinedValues>
    <Object Database="[Database1]" Schema="[dbo]" Table="[Locales]" Index="[PK_Locales]" Alias="[L]" IndexKind="Clustered" Storage="RowStore" />
    <SeekPredicates>
      <SeekPredicateNew>
        <SeekKeys>
          <Prefix ScanType="EQ">
            <RangeColumns>
              <ColumnReference Database="[Database1]" Schema="[dbo]" Table="[Locales]" Alias="[L]" Column="LocaleID" />
            </RangeColumns>
            <RangeExpressions>
              <ScalarOperator ScalarString="[Database1].[dbo].[ActivityFeedTeamCache].[LocaleID] as [AFC].[LocaleID]">


texts: Where is documented that no other transaction can update a locked row?

texts:
In the manual for InnoDB Locking, it is stated how shared and exclusive locks work.

As I found out by trial and error, both LOCK IN SHARE MODE and FOR UPDATE ensure no other transaction can update the rows that are selected.

What I found in the documentation, is that if a row is locked FOR UPDATE then no other transaction can get a LOCK until the transaction is commited or rolled back. Also if there are multiple LOCK IN SHARE MODE then no updated are possible. But I did not find that if there is any LOCK on a row, than no transaction (even without LOCK) can update the row.

I did not find that rule in the docs. Is it missing, or did I just overread it?


texts: Run a report by specifying which server to execute the query on

texts: I have a couple of stored procedures that I have written that gathers some information about all of the databases on the server it's stored on.  I would like to create a report such that I can select the server I want the report to run on, and then execute the stored procedures there.


texts: Unexpected unique constraint violation during update

texts:
In table foo with two columns (id and seq), I'd like to add +1 to seq for all records with an arbitrary seq > 4738. The plan is to insert a new record at seq=4739 once all the seq > 4738 records are shifted by +1.
This is the table.
CREATE TABLE foo
(
  id uuid NOT NULL,
  seq integer NOT NULL,
  CONSTRAINT seq_key UNIQUE (seq)
)

CREATE UNIQUE INDEX idx_id
  ON foo
  USING btree
  (id);

CREATE UNIQUE INDEX idx_seq
  ON foo
  USING btree
  (seq);

I try to achieve the +1 shift with the following query. Note, that I use a subquery in an attempt to update the > 4738 records in descending order (i.e. assuming max seq=10000 then the last record is updated first (10000->10001), then second last (seq=10000 doesn't exist at this point, seq=9999 -> seq=10000 (no constraint violation), then 9998 -> 9999, ... to avoid at any one time that a unique constraint violation occurs). However, this assumes sequential execution of the update query which isn't what's seemingly happening.
When running
UPDATE foo SET seq=anon_1.new_seq FROM (
  SELECT foo.id AS id, foo.seq + 1 AS new_seq
  FROM foo
  WHERE foo.seq > 4738 ORDER BY foo.seq DESC
) AS anon_1 WHERE foo.id = anon_1.id

I get the following error.

duplicate key value violates unique constraint "seq_key"
DETAIL:  Key (seq)=(7334) already exists.

Obviously, that's unexpected (as the constraint was satisfied before the UPDATE). Is there anything I could try to resolve the problem (different index type, only constraint, only index)? I noticed that this error very much depends on the number of updated records. If fewer records are updated, then this problem doesn't seem to occur (this may hint to some parallel execution for when there are too many records to update which may interleave and cause some intermediate state in which the constraint/index is not unique any more). Some thoughts and ideas would be very appreciated.


texts: How to determine if an ALTER statement will be a metadata-only operation O(1) or a size of data operation O(n)?

texts:
We are dealing with very large tables in Sql Azure.

Size-of-data operations can lock tables for a very long time so we must be careful to run these only on week ends.

Metadata operations are quick and we can run them in production at any time.

Looking at a given ALTER TABLE statement, how can I determine if this will be a metadata-only operation or a size-of-data operation?

As far as I know it's not possible to view an ALTER's execution plan?


texts: Should Postgres function return/coerce numeric scale/precision?

texts:
The following function returns two generic numerics, not the numeric(20,10) I think I'm specifying in the function return definition. This is unexpected (by me). Is there a way to ensure the function does the "coercion" to numeric(20,10) so outside code doesn't have to cast?
drop function test_numeric;
CREATE OR REPLACE FUNCTION test_numeric ()
RETURNS TABLE(m numeric(20,10), n numeric(20,10)) AS $$
-- RETURNS TABLE(m numeric, n numeric) AS $$
BEGIN
    RETURN QUERY
    select
        -- returned as written (with more than 10 decimal places)
        88888888.88888888888888888 as mm,
        -- truncated as expected
        88888888.88888888888888888::numeric(20,10) as nn;
END;
$$ LANGUAGE plpgsql;

[Edit based on feedback in answers] Here's a fiddle to show how the function signature does not enforce the return type (hoped for). M is the point. Even though the signature says we're returning a numeric(20,10) a generic numeric gets returned. The problem is when subsequent code expects a numeric(20,10) but the type is numeric (comes in when doing a create or update view/table etc). What's most unexpected is that the function signature basically gets ignored, or overridden by the code, to the next user down the road sees the function signature, expects a 20/10 and gets something totally different.
Fiddle
Another fiddle showing it doesn't matter at all what scale or precision is specified to be returned; any numeric scale/precision can be returned and will remain the scale or precision it was inside the function body.
Fiddle
[Further Edit] Apologies for beating the horse and demonstrating my misunderstanding of the Numeric type perhaps. But if the scale and precision matters when changing this view, it's hard to understand why it wouldn't matter when defining the table the function will return. I could maybe understand if numeric(20,10) was a subtype of numeric and could be passed if I'd specified numeric to be returned. Just never heard of something less specific passing for something specified to be more specific (in a strongly typed language).
Fiddle


texts: sql 2005: Timeout- Error 1222 Severity 16 State 18

texts:
A user complained that a SQL query has started timing out. I couldn't see the timeouts in sql logs but when I started a trace, I could see timeouts: "Error 1222 Severity 16 State 18"

It was from spid 17 which is a system process but in user database. After some research I found that "This error means that a lock was requested in msdb and timed out. Usually, that's going to mean it's a big transaction on a big temp table or a big sort, or something of that type"

Source: Link.

As per this link, I tried disabling ghost record removal process using:

DBCC TRACEON (661,-1)

But that didn't help either so I changed it back.

Any idea what I can do?

Thanks!


texts: PostgreSQL parallel scan with "greater" or "less" than

texts:
I have two queries. One has "=" other one has ">" in as filter condition. My question is why planner plans only query with equals in condition to run in parallel.
If I mess with defaults for parallel_tuple_cost and parallel_setup_cost I can make both run in parallel. But I would like to know why it is planned this way with default configuration.
I am providing queries which I executed and planner explain output. There are no indexes in for fields which are used in fiter.

This runs in parallel with two workers:
VACUUM; EXPLAIN ANALYSE select screen_name from accounts where followers_count = 100;

(cost=1000.00..81857.06 rows=401 width=12) (actual time=0.442..149.981 rows=5028 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Seq Scan on accounts  (cost=0.00..80816.96 rows=167 width=12) (actual time=0.100..113.974 rows=1676 loops=3)
        Filter: (followers_count = 100)
        Rows Removed by Filter: 1154464
Planning Time: 0.040 ms
Execution Time: 150.294 ms

This runs with single worker:
VACUUM; EXPLAIN ANALYSE select screen_name from accounts where followers_count > 100;

Seq Scan on accounts  (cost=0.00..106105.64 rows=2278992 width=12) (actual time=2.608..562.870 rows=2286022 loops=1)
  Filter: (followers_count > 100)
  Rows Removed by Filter: 1182399
Planning Time: 0.059 ms
JIT:
  Functions: 4
   Options: Inlining false, Optimization false, Expressions true, Deforming true"
   Timing: Generation 0.369 ms, Inlining 0.000 ms, Optimization 0.186 ms, Emission 2.316 ms, Total 2.871 ms"
Execution Time: 640.952 ms

Queries have only different sign in filter condition.
EDIT1:
Using less conservative settings:
SET parallel_tuple_cost to 0;
SET parallel_setup_cost to 0;
SET max_parallel_workers_per_gather to 4;
VACUUM; EXPLAIN ANALYSE select * from accounts where followers_count > 100;

Gather  (cost=0.00..73589.74 rows=2278693 width=121) (actual time=0.220..391.700 rows=2286022 loops=1)
  Workers Planned: 4
  Workers Launched: 4
  ->  Parallel Seq Scan on accounts  (cost=0.00..73589.74 rows=569673 width=121) (actual time=0.024..117.873 rows=457204 loops=5)
        Filter: (followers_count > 100)
        Rows Removed by Filter: 236480
Planning Time: 0.065 ms
Execution Time: 484.223 ms

Using default settings:
SET parallel_tuple_cost to 0.1;
SET parallel_setup_cost to 1000;
SET max_parallel_workers_per_gather to 2;
VACUUM; EXPLAIN ANALYSE select * from accounts where followers_count > 100;

Seq Scan on accounts  (cost=0.00..106100.83 rows=2278739 width=121) (actual time=2.610..552.259 rows=2286022 loops=1)
  Filter: (followers_count > 100)
  Rows Removed by Filter: 1182399
Planning Time: 0.083 ms
JIT:
  Functions: 2
"  Options: Inlining false, Optimization false, Expressions true, Deforming true"
"  Timing: Generation 0.462 ms, Inlining 0.000 ms, Optimization 0.175 ms, Emission 2.239 ms, Total 2.876 ms"
Execution Time: 634.788 ms


texts: Moving Reporting Processes from Access to MySQL

texts:
I am soon due to be given SQL access to our CRM system, which will allow me to automate some reporting processes. At present I use Access which I update with Excel imports.

As one example I have created a report which is used to assess sales team performance. To do this I manipulate the data using queries in Access and then link Excel to the final query. My queries have many steps which reference each other - this seemed to be the only way to make this work in Access; use a query to select/re-order some data before you can apply the next query to it, and so on.

I am playing around with some partial data in MySQL with the idea of completely re-creating this database (and others like it) and I have some questions:

Is it worth doing this, or should I simply use the SQL connections to automate the Access data update and continue to run the report from it? There are no problems with it at present, it is just tedious to update it manually.
My approach in MySQL so far is to use Views in place of the multi-layered queries I use in Access, as they seem to be the closest thing available. Is this appropriate/advisable? I will be making new reports using MySQL and would automatically approach it in this way but I'm completely unsure if this is suitable. Views seem easier to keep track of than subqueries etc.

Any help would be great.

Thanks


texts: Query for getting reserved seats in a cinema

texts:
I have this cinema project for a course.
And I have to visually show the seats to user so they can reserve their preferred seats.
I need to get the reserved seats from the database of that particular show but I can't seem to get proper sql query. I'm using phpMyAdmin and mySQL.

I just have shid (primary key of showingTimes table) available and from that shid I need to get all the seats.

It's like seats table has foreign key rid (primary key for reservation table) from reservation table and reservation table has foreign key shid from showingTimes table. I know this will be done by inner join but can't get it right.

I previously tried this SQL but this doesn't seem to work.

SELECT `seats`.*, `reservation`.*
FROM `seats`
LEFT JOIN `reservation` ON `reservation`.`rid` = `seats`.`rid`
                       AND `reservation`.`shid` = ?;

Either I should change my database and add shid to seats table, but then there will be redundant data.

My database tables are as following.
This is the showingtimes table.

CREATE TABLE `showingtimes` (
   `shid` int(11) NOT NULL,
   `mid` int(11) NOT NULL,
   `start` time NOT NULL,
   `end` time NOT NULL,
   `hid` int(11) NOT NULL,
   ADD PRIMARY KEY (`shid`),
   ADD KEY `hid` (`hid`),
   ADD KEY `mid` (`mid`) USING BTREE;
   ADD CONSTRAINT `showingtimes_ibfk_2` FOREIGN KEY (`hid`) REFERENCES
   `hall` (`hid`),
   ADD CONSTRAINT `showingtimes_ibfk_3` FOREIGN KEY (`mid`) REFERENCES
   `movies` (`mid`);
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

And here's some data from this table.

INSERT INTO `showingtimes` (`shid`, `mid`, `start`, `end`, `hid`) VALUES
(1, 109, '03:00:00', '06:00:00', 2),
(2, 112, '03:00:00', '06:00:00', 4);

This is reservation table.

CREATE TABLE `reservation` (
  `rid` int(11) NOT NULL,
  `uid` int(11) NOT NULL,
  `shid` int(11) NOT NULL,
  `nos` int(11) NOT NULL,
  `pay` varchar(10) NOT NULL,
  `day` datetime NOT NULL'
  ADD PRIMARY KEY (`rid`),
  ADD KEY `uid` (`uid`),
  ADD KEY `shid` (`shid`),
  ADD CONSTRAINT `reservation_ibfk_1` FOREIGN KEY (`uid`) REFERENCES
  `users` (`uid`),
  ADD CONSTRAINT `reservation_ibfk_2` FOREIGN KEY (`shid`) REFERENCES
  `showingtimes` (`shid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

And here's some data from this table.

INSERT INTO `reservation` (`rid`, `uid`, `shid`, `nos`, `pay`, `day`)
VALUES
(1, 4, 2, 3, 'Yes', '2018-12-14 01:00:00'),
(2, 2, 2, 3, 'Yes', '2018-12-14 02:00:00'),
(3, 5, 1, 2, 'Yes', '2018-12-14 01:00:00'),
(4, 4, 1, 2, 'Yes', '2018-12-14 11:00:00');

And this is the seats table.

CREATE TABLE `seats` (
  `sid` int(11) NOT NULL,
  `rid` int(11) NOT NULL,
  `row` varchar(11) NOT NULL,
  `col` int(11) NOT NULL,
  ADD PRIMARY KEY (`sid`),
  ADD KEY `rid` (`rid`),
  ADD CONSTRAINT `seats_ibfk_1` FOREIGN KEY (`rid`) REFERENCES `reservation`
  (`rid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

And here's some data from this table.

INSERT INTO `seatsrffm` (`sid`, `rid`, `row`, `col`) VALUES
  (1, 1, '1#A', 1),
  (2, 1, '1#A', 2),
  (3, 1, '1#A', 3),
  (4, 2, '2#B', 3),
  (5, 2, '2#B', 4),
  (6, 2, '2#B', 5);

Any help would be greatly appreciated.


texts: SQL Server 2016- Email Alerts fo Errors/ Warnings

texts:
I am using Microsoft SQL Server 2016

I am trying to set up email alerts for errors and I am getting this error:

07/24/2017 19:42:23,,Error,3111,The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2017-07-24T19:42:23). Exception Message: Cannot send mails to mail server. (A recipient must be specified.).<nl/>),22216,3029,,7/24/2017 7:42:23 PM,sa
07/24/2017 19:41:23,,Error,3110,The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2017-07-24T19:41:23). Exception Message: Cannot send mails to mail server. (A recipient must be specified.).<nl/>),22216,3029,,7/24/2017 7:41:23 PM,sa

However I do have recipients set not sure what I am missing here.

I ran the following query to see logs:

select log_id, event_type, log_date, description, process_id, mailitem_id,
account_id, last_mod_date, last_mod_user
from sysmail_event_log
where event_type = 'error'
order by log_date asc

select * from sysmail_faileditems

however for the 2nd query (failed items) it does have a valid email address in the recipient column. I cannot figure out what I missed in the database mail configuration.


texts: User based payment system table designing

texts:
We've been developing user based payment system.
It's pretty much metering how much users use some contents per hour.
And we send the bill at the end of every month.

I'd love to know how user based payment system table can be implemented.
Any documentation or resources?

[CF]

This is meant for database deign not algorithm.
But in my mind there are two ways to achieve it.
Also if you have an experience to implement something like this, please give me some advice.

First, event status model (  standing for "on/off" status )

whenever you create contents, 1 will be inserted, on the other hand -1 will be inserted if you destroy that.
And we can only calculate from 1 to -1 part.

It's just good because we can skip start point if you already started.

Ex.

create  create create  destroy
    1            1        1           -1
     <=================>

So there are many events inserted in one table (start/stop)

Secondly, contents model

It would be more simple.
contents has columns "created_at" and "destroyed_at".
Just whenever you create/destroy some content, the time will be inserted for each column.

Which is more efficient or any other ways?

Personally the second is little cleaner, but i'd also love to hear some opinions.


texts: Time taken by VACUUM FULL to reclaim space

texts:
After receiving some insightful guidance in a previous post here, I'm going to run VACUUM FULL on 4 PostgreSQL 9.3.10 tables. Table sizes are:

1) links_publicreply: ~30M rows, 9 columns, 3 indexes (types: int, timestamp, char, bool)

2) links_reply: ~25M rows, 8 columns, 6 indexes (types: int, text, timestamp, char)

3) links_link: ~8M rows, 14 columns, 3 indexes (types: int, text, dbl precision, timestamp, char bool)

4) links_user_sessions: ~2M rows, 7 columns, 4 indexes (types: int, text, timestamp, inet)

This is my first attempt at reclaiming disk space. It's a busy server of a local social networking website. No time is actually "downtime". But the least busy is ~4:00 AM, so that the window I'll use.

Speaking from experience, can you guys form any opinion on how long VACUUM FULL would take for the 4 tables I pointed out? I'd like to put up a "under maintenance till xx:xx:xx" message on the website while it's happening. I know no one can be sure, but is this deterministic enough for you to form a ballpark opinion?

Secondly, just so that we're on the same page, the commands I'd be running on psql are simply VACUUM (FULL, VERBOSE, ANALYZE) link_publicreply; (and so on), correct? Don't want to screw it up.


texts: Is there a penalty for using BINARY(16) instead of UNIQUEIDENTIFIER?

texts:
I've recently inherited a SQL Server database that uses BINARY(16) instead of UNIQUEIDENTIFIER to store Guids. It does this for everything including primary keys.

Should I be concerned?


texts: Make a DBMS-enforced multi-column "UNIQUE" constraint, with a "match all" wildcard

texts:
Well I wonder if I can do the following on database level (instead of server level). We are using PostgreSQL as the SQL flavour.

I have multiple columns. Now I wish to make sure each entry into the database has a unique column. Normally this would be a simple

ALTER TABLE test
ADD UNIQUE(X, Y)

However in our application "NULL" means not "no data" but rather "wildcard". (unless you think there's a better general purpose to specify this). What this means is that for unique constraint when one of the columns is not filled, it "matches everything", ie the following tables would violate unique constraints (in addition to normal constraints):

X    | Y
'a'  | 'B'
'a'  | null

X    | Y
'a'  | 'B'
null | 'B'

While this would be obviously "ok":

X    | Y
'a'  | 'B'
null | 'C'

X    | Y
'a'  | 'B'
'b'  | null

Can I put such a constraint on database level? Or do I have to guarantee this on server level?

ps: this is not the primary key, there is always a column id for primary keys. Also in this example I use strings, but actually the datatypes are different and can be anything.


texts: Using LIKE or Full-text when searching database

texts:
I am fairly new to SQL and I am currently trying to create a search function for my website, much like the one on imdb.com. I have 3 tables and I need to search two rows (nvarchar) in each table when a user makes a search. The data to be searched is maximum 20 letters long but there are a lot of rows, and hopefully many more will be added.

I want to "future-proof" the website and I was looking up Full-text and FREETEXT searches. I have been using LIKE before but it says this is not very optimal for performance when the search has to look through a lot of data.

So basically before I start working on this very important part of my website I want to know what experts like you think. Is it acceptable to use LIKE when making a big search or should I learn how to use Full-text indexes and FREETEXT searches?

My current way of searching:

SELECT table1.col1, table1.col2
FROM table.1
WHERE table1.col1 LIKE '%@search%'
UNION ALL
SELECT table.2.col1, table2.col2
...

Database version:
Microsoft SQL Server 2012 - 11.0.5058.0 (X64) May 14 2014 18:34:29 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.3  (Build 9600: )

EDIT: The data are always just 1 word, so there will be no use for stopwords that are provided by full-text.


texts: Migrating data from 11G Linux to 19C on Windows

texts: I'm using datapump to move data from 11.2.0.4 on Linux to 19C on Windows. It seems to be working except for some objects in the system schemas. Is there any reason to bring these schemas across or can I simply exclude them?


texts:
I analysed a huge query in DTA. The database is kind of badly designed and does not have primary keys on tables, also they dont have clustered indexes on them. I found around 80 tables as such. Now, without indexes the queries are obviously going to run slow but when I put them through the DTA, it always asked me create a non clustered index (even on heaps). Something like

CREATE NONCLUSTERED INDEX [_dta_index_IO_Status_MAster_10_1899153811__K2_4_5_1912]
  ON [Scorecard].[IO_Status_MAster]
  (
    [WBS] ASC
  )
  INCLUDE ([OrderNumber], [IO_STATUS])
    WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

My question, does DTA works on the premise that table has a clustered index on it? Why does it not advise to create a Clustered Index?


texts: Guidance on strategy for serving files remotely using SQL Server

texts:
Thanks for the help.  "I" am the IT department for a small business and my ignorance may keep me from asking the right questions so please bear with me.
Currently, we are using MS Access front and back - transitioning to SQL Server Express.  For now, we'll be keeping MS Access as the front-end, keeping in mind that eventually, the data will be consumed in various front-ends such as tablets and cell-phones.  We have all kinds of documents "attached" or associated with various entities such as Projects, Invoices, Suppliers, Employees, etc.  Currently, I simply store the documents on a share and refer to them by building a path using a simple naming convention based upon the entity name and key for each record.  The files are simply opened on demand.  This has worked great in a LAN environment.  Now that we will be accessing the data via SQL Server remotely, I have looked at FILESTREAMs and FILETABLEs and my head is spinning a bit.  I'm not sure if that's the way to go.  Also, FTP and/or VPN.  I'm not sure if a VPN will work for us in the future because I don't know about setting that up for mobile devices or if that's even a viable option.  I have the same concerns using FTP.  We are a small company and I'm attempting to keep expenses low and manage this myself if possible.  Let me know if you need more details.  What are your thoughts?


texts:
I've used many times this technique for transferring databases between servers:

pg_dump --no-owner -Fd mydb -j 4 -f tmp/mydb
scp -r tmp/mydb otherserver:tmp/

then in the other server:

dropdb --if-exists mydb &&
createdb -T template0 mydb &&
pg_restore -j 4 -d mydb tmp/mydb &&
rm -rf tmp/mydb

It has worked flawlessly.
Using Postgres 9.3.16 and CentOS 7.

But now, in a new server, pg_restore complains:

pg_restore: [archiver] parallel restore is not supported with this archive file format

I could remove the -j 4 parameter, but theorically it should be valid.
According to the pg_restore doc:

  -j number-of-jobs
  --jobs=number-of-jobs

  Run the most time-consuming parts of pg_restore — those which load
  data, create indexes, or create constraints — using multiple
  concurrent jobs. This option can dramatically reduce the time to
  restore a large database to a server running on a multiprocessor
  machine.

  Each job is one process or one thread, depending on the operating
  system, and uses a separate connection to the server.

  The optimal value for this option depends on the hardware setup of the
  server, of the client, and of the network. Factors include the number
  of CPU cores and the disk setup. A good place to start is the number
  of CPU cores on the server, but values larger than that can also lead
  to faster restore times in many cases. Of course, values that are too
  high will lead to decreased performance because of thrashing.

  Only the custom and directory archive formats are supported with this
  option. The input must be a regular file or directory (not, for
  example, a pipe). This option is ignored when emitting a script rather
  than connecting directly to a database server. Also, multiple jobs
  cannot be used together with the option --single-transaction.

I'm using the directory format in pg_dump: -Fd !
Why does it complain?


texts:
Property Owner is not available for Database 'DBNAME'. This property may not exist for this object, or may not be retrievable due to insufficient access rights.  (Microsoft.SqlServer.Smo)

I am getting this error when trying to 'right click' a properties on the database.
Does anyone know how to fix this ?

I've tried following:

use DBNAME
EXEC sp_changedbowner 'myUser'

Getting the following message:

  The proposed new database owner is already a user or aliased in the database.

Any Ideas how to fix this ?


texts: SQL Server take an unencrypted full backup

texts:
I have to take an unencrypted full backup of a database so that it can be sent over to other team after which they can restore it in their own environment. The other team has a different environment and a different SQL Server certificate.

My server setting for a full backup is taking an AES 128 encrypted backup. And I don't intend to share the decryption key and certificate with the other team. My version of SQL Server is Standard 2014 SP2.

I have seen encrypting the backup but this is something I haven't come across yet. Has anybody encountered this type of scenario before. Any help will be greatly appreciated.

EDIT -

In my case, all SQL server database backups are encrypted using a self signed certificate. This certificate is installed on all servers so only a single certificate needs to be managed. In the event the certificate expires it will need to be renewed as backups will not run with an expired certificate. Additionally, it is required to have the certificate and private key loaded to restore backups created with the key pair.

It was this certificate and private key which I do not intend to share with the other team. Hence, require a copy-only full backup that can bypass the certificate encryption. Is there any way, I can get through the above scenario by creating a backup which doesn't require certificate and private key to be loaded.

Adding the output of the query as asked by learning-dbadmin -


texts:
My application is doing extremely slow updates, despite my query being covered by indexes. My query is:

        query = {'spot': df_youtube['spot'].values[key],
                 'segment': df_youtube['segment'].values[key],
                 'brand': df_youtube['brand'].values[key]}
        collection.update(query,
                          {"$set": {'youtube':
                                    df_youtube['youtube'].values[key],
                                    '_youtube_datetime': today}
                           },
                          multi=True, upsert=False)

I have created an index over the fields {"spot" : 1, "segment" : 1, "brand" : 1}. Never the less update are extremely slow:

Here for example a snippet from my log:

 2015-08-26T15:30:20.902+0200 [conn2359638] command reckitt.$cmd
 command: update { update: "qoc", updates:
 [ { q: { segment: "stain removal",
          brand: "Vanish", spot: "Vanish VAN66 Nurse 20sec" },
  u: { $set: { _youtube_datetime: new Date(1440597687411), youtube:
      "MXFbFAkZALc" } }, multi: true, upsert: false } ] }
 keyUpdates:0 numYields:0  reslen:95 166240ms

The is only one other process inserting records, it permanently write on another database.

update

Some information about the collection and the indexes obtained with the command db.qoc.stats():

{
"count" : 14582310,
"size" : 44546741664,
"avgObjSize" : 3054,
"storageSize" : 50088046272,
"numExtents" : 44,
"nindexes" : 7,
"lastExtentSize" : 2146426864,
"paddingFactor" : 1,
"systemFlags" : 1,
"indexSizes" : {
    # not all 7 indexes are shown ...
    "idx_spot_segment_brand" : 1218093184,}
}

Could the other process compete for writing? The other process writes are very fast (about ~100ms).

Why are updates taking so long? What can I do to improve the situation?


texts: Oracle listagg forces SORT (GROUP BY) execution plan

texts:
I have the following query

SELECT /*+ USE_HASH_AGGREGATION */ id
--, count(id)
, listagg(type, ', ') within group (order by null) types
FROM test
group by id

Both type and id are short strings.
The execution plan for when I comment out listagg and leave count aggregation in is a HASH GROUP BY (even without the hint) and works fast.
With the listagg aggregation Oracle always chooses SORT GROUP BY which is an order of magnitude slower.  Is there any reason for that?


texts: Elasticsearch Rack Awarness Doesn't seem to work

texts:
I have 5Baremetals, each Baremetal is cut into 2VMs and installed ES 6.8.
I made the setting on an existing cluster that shouldn't have Primary and Replica on same BM(Baremetal) and I have mentioned like below.
node.attr.rack_id: 028
cluster.routing.allocation.awareness.attributes: rack_id

This setting is given to VMs on same BM so they don't have same primary and replica on same BM.
Example:
Baremetal is prd-esbm028.com
VMs inside prd-es028.com will be prd-esvm011.com and prd-esvm012.com .
So these VMs prd-esvm011.com  and prd-esvm012.com  will have
node.attr.rack_id: 028
cluster.routing.allocation.awareness.attributes: rack_id

After this setting did rolling restart on both the nodes, now cluster is green.
But I still see the shard P and R are staying on same location BM no change found after change and restarts.


texts: Deleting multiple rows based on the first instance of the value

texts:
I have a table with the below structure. I would like to delete all of the records that records that match ColA for any of the ColB's that have a ColC that has an even-numbered value. But only for the first instance of ColA. (In  this example all of the AA records would be deleted)

ColA and ColB are unique and sorted in alphabetical order. ORderByCol is part of the query that builds this table: ROW_NUMBER() OVER(PARTITION BY ColA ORDER BY ColB)

ColA    ColB    ColC    OrderByCol
AA      A       8       1
AA      B       3       2
AA      F       5       3
BB      B       7       1
BB      D       9       2
CC      A       1       1
CC      Q       5       2

I can do this with an ColA in a subquery, but that seems like a lot of work and it is really slow.

Query:

DELETE T FROM Table1 AS T
WHERE T.ColA IN (
    SELECT DISTINCT ColA FROM Table1
    WHERE EXISTS (SELECT * FROM Table1 WHERE ColC % 2)
    AND ORderByCol = 1
)

So the query will give me a list of all unique ColA values that have the first item not primary, but it is really slow. I'm sure there is a faster way to do this.

EDIT: If someone knows a better way to do what I am asking that would be awesome! I always like learning new ways to do things in SQL. However I found out why my query was slow. I didn't have an index on the table because I thought that table variables (@table1) tables couldn't have indexes. Once I put in an index on my table it reduced my run time from 1 min 20 sec to 2 sec.

Thank you to every one who has helped out with this.


texts: use an Index correctly to optimize queries

texts:
I have an SQL database with millions of records and when I'm querying the data like

select * from ActCosts where ScenarioID= 456

The tables has 1,323,718 rows  and it gives me like 50,000+  rows which is surprisingly taking more than 3 minutes. So I am now thinking of how I could improve this performance. I find one way is to create an index on the column "SomeID". I have created this index but the query takes the same time

Execution plan

Index Script

CREATE NONCLUSTERED INDEX [IX_ActCost_ScenarioID] ON [dbo].[ActCost]
(
    [ScenarioID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
GO

ALTER TABLE [dbo].[ActCost] ADD  CONSTRAINT [PK_ActCost] PRIMARY KEY CLUSTERED
(
    [ActCostID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
GO


texts: DB Design - Different tables that share a common table - each has their own values

texts:
I have a  Persons table. This table has two tables depended on, the Clients and Clerks tables, Persons has many Clerks and Persons has many Clients

Something like this, visually:

Persons
|     \

Clerks Clients

Clients and Clerks both need a relation with Locations table; both the Clients and the Clerks has a latitude, longitude combination and many other geographic related elements. I'm not sure about this approach: I cannot link the Clerks and Clients table with a one to one relationship, because Locations will have two foreign keys from different tables.

Persons
|     \

Clerks Clients

 \        /
  Locations

Is there another approach?


texts: For two triggers(before insert and after insert),is it allowed for you to use where in the NEW keyword

texts:
I have two triggers.One after insert trigger and the other before insert trigger.The two triggers are using the NEW keyword to get the last inserted message.

Both triggers are watching a table called incoming_sms and the row inserted has three fields. Id,name,service

Service contains a unique number 9999999 for first service and 8888888 for second service.

Is it possible that when using the NEW keyword that i also use where service = 9999999 just to be sure i am referring to the right last inserted message by specific service.

If i just use New.col without specifying the service,i may be referring to the wrong service since i will get the last inserted value by any service.

I hope i explained that clearly.


texts: How to verify that a certain commit has been transmitted to standby

texts:
Scenario:

Oracle Data guard 12c with 1 primary and 1 standby, Max. Av. protection mode
Primary open, standby mounted (all green in OEM)
Perform a dummy CTAS in primary

Question:

Without opening it, how can I be sure the data has been received by standby? Is there a log, a table, anything, that I can consult in Primary?

select sequence#, applied from v$archived_log;

shows a lot of not applied logs but I'm not sure it is relevant. Also, it does not really identify the query.

Thank you


texts: How do I query this 20 million record view faster?

texts:
For a search functionality I am using a view that has the records from all the tables within which I need to search for. The view has almost 20 million records. Searches against this view are taking too much time.

Where should I look to improve the performance of this view?

The rough definition for the view is below. It includes thirteen tables and around thirty fields.

CREATE VIEW [dbo].[v_AllForSearch]
AS
SELECT
  FT.firstField AS [firstField]
, FT.fld_primary AS [fld_primary]
, FT.fld_thirdField AS [thirdField]
, FT.fld_fourthField AS [fourthField]
, ISNULL(ST.[fld_firstSearchField],'') AS [firstSearchField]
, ISNULL(TT.[fld_thirdSearch],'') AS thirdSearch
, ISNULL(TT.[fld_fourthSearch],'')AS fourthSearch
, ISNULL(TT.[fld_fifthSearch],'')AS fifthSearch
, ISNULL(FRT.[fld_sixthSearch],'') As [sixthSearch]
, ISNULL(FRT.[fld_seventhSearch],'') AS [seventhSearch]
, ISNULL(FRT.[fld_eightSearch],'')AS [eightSearch]
, ISNULL(FIT.[fld_nineSearch],'') AS [nineSearch]
, ISNULL(SIT.[fld_tenthSearch],'')AS [tenthSearch]
, ISNULL(SET.[fld_eleventhSearch],'') AS [eleventhSearch]
, ISNULL(ET.[twelthSearch],'')AS [twelthSearch]
, ISNULL(NT.[thirteenthSearch],'')AS [thirteenthSearch]
, ISNULL(NT.[fourteenSearch],'') AS [fourteenSearch]
, ISNULL(NT.[fifteenSearch],'') AS [fifteenSearch]
, ISNULL(NT.[sxteenSearch],'')  AS [sxteenSearch]
, ISNULL(NT.[seventeenSearch],'') AS [seventeenSearch]
, ISNULL(NT.[eighteenSearch],'')AS [eighteenSearch]
, ISNULL(TT.[ninteenSearch],'') AS [ninteenSearch]
, ISNULL(ELT.[twentySearch],'') AS [twentySearch]
, ISNULL(ELT.[twentyOneSearch],'') AS [twentyOneSearch]
, ISNULL(TWT.[twentyTwoSearch],'') AS [twentyTwoSearch]
, ISNULL(THT.twentyThree,'') AS [twentyThree]
, ISNULL(THT.twentyFour,'') AS [twentyFour]
, ISNULL(THT.twentyFive,'') AS [twentyFive]
, ISNULL(THT.twentySix,'') AS [twentySix]
FROM
      tblFirstTable AS FT
      LEFT JOIN [tblSecondTable] AS ST
            ON ST.[fld_primary] = FT.[fld_primary]
      LEFT JOIN [tblThirdTable] AS TT
            ON TT.[fld_primary] = FT.[fld_primary]
      LEFT JOIN [tblFourthTable] AS FRT
            ON FRT.[fld_primary] = FT.[fld_primary]
      LEFT JOIN [tblFifthTable] AS FIT
            ON FIT.[fld_primary] = FT.[fld_primary]
      LEFT JOIN [tblSixthTable] AS SIT
            ON SIT.[fld_primary] = FT.[fld_primary]
      LEFT JOIN [tblSeventhTable] AS SET
            ON SET.[fld_primary] = FT.[fld_primary]
      LEFT JOIN [tblEighthTable] AS ET
            ON ET.[fld_primary] = FT.[fld_primary]
      LEFT JOIN [tblNinthTable] AS NT
            ON NT.[fld_primary] = FT.[fld_primary]
      LEFT JOIN [tblELTnthTable] AS TT
            ON TT.[fld_primary] = FT.[fld_primary]
      LEFT JOIN [tblEleventhTable] AS ELT
            ON ELT.[fld_primary] = FT.[fld_primary]
      LEFT JOIN [tblTwelthTable] AS TWT
                            ON TWT.[fld_id] = ELT.[fld_id]
              LEFT JOIN [tblThirteenthTable] AS THT
            ON THT.[firstField]= FT.[firstField]
WHERE fld_Status ..


texts: How to Audit for Access with Cross Database Ownership Chaining

texts:
I've just joined the data team of a new company and we have a large SQL Server environment (100+ instances) and cross database ownership chaining is on by what I gather is a necessity. I'm looking for 3rd party tools or even scripts for some way to audit potential access to a database including the potential of cross-database ownership chaining. Does anyone know of any way to accomplish this, or any products that do the same sort of thing?

I have mostly 2008 R2 and some 2012 instances; there may be one or two 2005 machines but they can be safely ignored. Most are Enterprise Edition although some might be Standard Edition.


texts: Generate multiple running totals with GROUP BY day

texts:
I have a set of transactions of stock purchases by users and I want to keep track of a running balance of each stock as the year progresses. I am using a windowing function to track the running balance but for some reason I cannot get the GROUP BY portion of this query to work.

It continues to have duplicate days in the result set even when I attempt to group by the date (created_at). Sample below:

select
  t.customer_id,
  t.created_at::date,
  sum(case when t.stock_ticker = 'tsla' then t.amount end) over (order by t.created_at::date rows unbounded preceding) as tsla_running_amount,
  sum(case when t.stock_ticker = 'goog' then t.amount end) over (order by t.created_at::date rows unbounded preceding) as goog_running_amount,
from transactions t
group by t.created_at, t.customer_id, t.stock_ticker, t.amount
order by t.created_at desc;

Test setup:

CREATE TABLE transactions (
   transaction_id varchar(255) NOT NULL,
   amount float8 NOT NULL,
   stock_ticker varchar(255) NOT NULL,
   transaction_type varchar(255) NOT NULL,
   customer_id varchar NOT NULL,
   inserted_at timestamp NOT NULL,
   created_at timestamp NOT NULL,
   CONSTRAINT transactions_pkey PRIMARY KEY (transaction_id)
);

INSERT INTO transactions(transaction_id, amount, stock_ticker, transaction_type, customer_id, inserted_at, created_at)
VALUES
  ('123123abmk12', 10, 'tsla', 'purchase', 'a1b2c3', '2020-04-01 01:00:00', '2020-04-01 01:00:00')
, ('123123abmk13', 20, 'tsla', 'purchase', 'a1b2c3', '2020-04-03 01:00:00', '2020-04-03 01:00:00')
, ('123123abmk14',  5, 'goog', 'purchase', 'a1b2c3', '2020-04-01 01:00:00', '2020-04-01 01:00:00')
, ('123123abmk15',  8, 'goog', 'purchase', 'a1b2c3', '2020-04-03 01:00:00', '2020-04-03 01:00:00');

CREATE INDEX ix_transactions_customer_id ON transactions USING btree (customer_id);

The result here always comes back with multiple rows per day, when I want them to be grouped all into one day.

After doing some research I attempted to cast created_at to date in the GROUP BY clause as well, but I get this error:

Column t.created_at must appear in the GROUP BY clause or be used in an aggregate function

In addition, the results are only going to show days in which a transaction has happened for a user. I need to be able to show a row for each day in a time series (1 year) even if the user did not make a transaction that day. (Using the most recent running balance on the row instead.)

I think that generate_series() is the way to go, but I am having trouble understanding how to fit it in.


texts: Postgres - Encoding, Collation and CType

texts:
List of databases
          Name           |  Owner   | Encoding |          Collate           |           Ctype            |
-------------------------+----------+----------+----------------------------+----------------------------|
 MyDatabase              | postgres | UTF8     | English_United States.1252 | English_United States.1252 |

Can someone explain how Encoding, Collate, and Ctype relate to one another?

I know that encoding affects how the information is actually stored (IE whether 'A' requires one byte or multiple bytes, and what value those bytes have, depend on the encoding).

I've been told that collate specifies rules for comparing characters.  If you were to sort a bunch of strings, the collate type would dictate the order.

I've struggled to find what Ctype is; possibly related to concepts like uppercase and lowercase (given 'a' knowing that 'A' is the uppercase form?).

I don't understand how (as in my example) I can have a UTF8 encoded database and use a collate value of English 1252.  UTF8 has many characters that win1252 does not; what happens if I attempt to sort or compare them?  Is my current setup nonsensical....it seems like I would always want the Encoding/Collate/Ctype to agree?


texts:
I have this query in my mysql database, when i execute this, it takes about 4 sec. To give me the result (on xampp 1.8.0 with mysql 5.5)
I was wondering if there is any way to enhance this query so that it executes faster?
This query returns 10K rows.

SELECT @n := @n + 1 rank,user_id, username, highscore, stage, total_played
FROM (select @n:=0) initvars, (
    SELECT m.user_id, m.username, SUM(ts.highscore) AS highscore, MAX(ts.stage) AS stage, total_played
    FROM (
        SELECT user_id, MAX(highscore) AS highscore, stage
        FROM games
        INNER JOIN single_player
        USING(game_id)
        GROUP BY stage,user_id
        ) AS ts
    INNER JOIN (
        SELECT user_id, COUNT(*) AS total_played
        FROM single_player
        GROUP BY user_id) AS tc
    USING(user_id)
    INNER JOIN members AS m
    USING(user_id)
    GROUP BY user_id
    ORDER BY stage DESC, highscore DESC) as r
ORDER BY rank

Edit: Raad this the result you asked for :)

I changed the query a little bit, execution time reduced to 0.15 sec!!!

SELECT *
FROM (
    SELECT @n := @n +1 rank, user_id, username, highscore, stage, total_played
    FROM (SELECT @n :=0)initvars, (

            SELECT user_id, username, SUM( ts.highscore ) AS highscore, MAX( ts.stage ) AS stage, SUM( count ) AS total_played
            FROM (

                SELECT user_id, MAX( highscore ) AS highscore, stage, COUNT( * ) AS count
                FROM games
                INNER JOIN single_player
                USING ( game_id )
                GROUP BY user_id, stage
            ) AS ts
        INNER JOIN members
        USING ( user_id )
        GROUP BY user_id
        ORDER BY stage DESC , highscore DESC
    ) AS rank
) AS final
LIMIT {$limit}
OFFSET {$offset}

$limit in most cases is 10
I ran this on a core i5 2.5GHz cpu and got these results
I just added my primary & unique keys, so can I make it even faster? Like declaring some columns as INDEX?


texts:
We have on several databases on same instances, some stored procedures have references to tables on other databases. We are in the middle of a big project to take our database under source control. Creating the baselines is not an problem. But after baseline creation, when trying to deploy on other environments, say a local developer instance or staging or alike, this cross reference issue is causing us several headaches. After doing some research and checking on possible approaches I still can't find a reliable way of managing this issues.

One approach proposed is to split baseline creation into "modules" that could be run individually so the cross referenced objects remain in one single script and be run alone. But what if the referenced objects on the other database has changed, then how I can track those changes? Maybe an index was added on referenced table and directly affects performance on the current sp? Or for whatever reason the referenced table changed column name, even deleted that column that is referenced and added a different one? What a nightmare.

So, question is, is there a recommended solution for this issue? For solution I mean, methodology, step-by-step approach, or whatever else that helps getting close to a solution with the deploying issue when having cross reference between database objects.

And for the moment, removing the database cross references is a no go. Way to much application dependent stuff to be corrected/updated and can't be done for the moment.

Not completely sure if this question follows the standards, as it can maybe trigger some opinion based answers. But I don't have more information to throw in for the moment. If you feel it should be flagged, then go.


texts: If i index two columns (indepentently) do i get the same performance as putting and index on the two

texts:
I want to put an index on two columns to improve my query performance for each. I also want to perform queries where both columns are used.
From my understanding, if you had a BTREE index on each column, you could theoretically get the same performance by querying both indices and then combining the range you got - if that makes sense?

e.g.
SELECT * FROM table WHERE f_name = 'first' and l_name = 'last'

Instead checking an index for ('first', 'last') you'd check the f_name index for first and the l_name index for last. Now, you can check if the results of both index queries are the same (the result of the initial query). Obviously, this is slightly slower than a composite index, but still much faster than no index.
With this idea, you get performance benefits not only when querying both columns together, but either column independently.
My main question is: if I have these 2 independent indices, would I need another index on both, to improve the performance of queries using both columns?


texts: Recursive query to find shortest path in graph

texts:
I have an assignment to find the shortest possible path between two people in a table. The table is Company(company_name, job, name), all of them not null, but none of them unique (primary key is the combination of all three values).

I'm supposed to use a recursive select query to find the shortest path between (for example) Bob Ross and Celine Dion. I know Bob is director of Painters, the manager of Painters is Carl, Carl is also an administrator in Sunny-D, and the director for Sunny-D is Celine Dion. So every name and company is a node, and the lines connecting them are the jobs. I'm not very good in SQL (we use PostgreSQL), and on top of that, I'm terrible at recursion, so if anyone could point me the right direction, I'd very much appreciate it.

What I have so far:

WITH RECURSIVE path(company_name, job, name, step) AS (
        SELECT c.company_name, c.job, c.name, 1
        FROM Company c
        WHERE person = 'Bob Ross'

        UNION ALL

    SELECT p.company_name, p.job, p.name, c.step+1
    FROM path p JOIN Company c ON p.person = c.person
    WHERE c.person = 'Celine Dion'
)
SELECT * FROM path;

I don't really know what to put in the recursive step where clause, or if I'm even joining them correctly


texts: PostgreSQL full text search: How to change what classifies as a "word separator"?

texts:
Using ts_debug I can see what symbols Postgres treats as (what I call) a "word separator".

Example:

SELECT ts_debug('english', 'Hello. ABC')

Result:

(asciiword,"Word, all ASCII",Hello,{english_stem},english_stem,{hello})
(blank,"Space symbols",". ",{},,)
(asciiword,"Word, all ASCII",ABC,{english_stem},english_stem,{abc})

". " (dot space) is treated as a word separator.
My problem is, I need Postgres to treat a single dot "." as a word separator too.

Right now, if I search for ABC no entry with the content Hello.ABC (without space after the dot) will be found, only with Hello. ABC.

I am using this search query:

SELECT description FROM incident WHERE
  to_tsvector('english', description) @@ to_tsquery('english', 'ABC')

Another example: Searching for IOException will not find java.io.IOException

Is there a way to treat a single dot (without space) as a word separator?


texts: Schema design - Reference slightly different data in different tables

texts:
I'm trying to design a schema for a MySQL database and got stuck on the following.
My database stores documents which should have relations with blocks of content.

create table document (
  `id`        int unsigned auto_increment,
  `title`     varchar(128) not null,

  primary key(id)
);

create table block_html (
  `id`        int unsigned auto_increment,
  `type`      varchar(32) not null,
  `contents`  TEXT not null,

  primary key(id)
);

create table block_image (
  `id`        int unsigned auto_increment,
  `type`      varchar(32) not null,
  `url`       varchar(255) not null,

  primary key(id)
);

As you can see the block data differs slightly between different types and I'm not sure how to design the relation. Obviously having a simple reference table will not work.

create table document_block (
  `document_id`     int unsigned,
  `block_id`        int unsigned,

  // foreign key??
);

What is a good solution to this problem?


texts:
I have a query that looks like this:

    SELECT *
      FROM TBLA A
 LEFT JOIN TBLB B ON A.Col1 = B.Col2
RIGHT JOIN TBLC C ON B.Col3 = C.Col4
      JOIN TBLD D ON C.Col5 = D.Col6

In which order will the joins be resolved?  I'm most interested in SQL Server, and will mark an explanation for it as the answer, but am equally interested in the ANSI/ISO standard and how it works in the various RDBMS.

The reason for this question was to figure out why the results differed from this query

    SELECT *
      FROM TBLA A
CROSS JOIN TBLC C
 LEFT JOIN TBLB B ON A.Col1 = B.Col2 AND B.Col3 = C.Col4
      JOIN TBLD D ON C.Col5 = D.Col6


texts: SELECT query on unique number field returns no rows

texts:
This is a cross-post of an issue I field on the GORM Github repo. I am unsure if the issue is with Postgres 11 or with GORM.

See: https://github.com/jinzhu/gorm/issues/2872

I have a table containing an ID as primary key and only one more column (mega_herz) which is a numeric(7,3). The numeric field also has a unique constraint on it. When I do the following query from pgadmin4 or psql I get one row as a response:

SELECT *
FROM "ttnmapper_frequencies"
WHERE ("ttnmapper_frequencies"."mega_herz" = 868.3)
ORDER BY "ttnmapper_frequencies"."id" ASC
LIMIT 1

But when I do the same query via GORM it does not return any results, and when I try and do an insert it fails:

(/home/jpmeijers/go/src/ttnmapper-postgres-insert-raw/main.go:342)
[2020-02-03 14:52:28]  [3.07ms]
SELECT *
FROM "ttnmapper_frequencies"
WHERE ("ttnmapper_frequencies"."mega_herz" = 868.3)
ORDER BY "ttnmapper_frequencies"."id" ASC
LIMIT 1
[0 rows affected or returned ]

(/home/jpmeijers/go/src/ttnmapper-postgres-insert-raw/main.go:342)
[2020-02-03 14:52:28]  [2.10ms]
INSERT  INTO "ttnmapper_frequencies"
("mega_herz")
VALUES
(868.3)
RETURNING "ttnmapper_frequencies"."id"
[0 rows affected or returned ]

(/home/jpmeijers/go/src/ttnmapper-postgres-insert-raw/main.go:345)
[2020-02-03 14:52:28]  pq: duplicate key value violates unique constraint "ttnmapper_frequencies_mega_herz_key"

Why would the select query return no result? Should I specify the number with three decimals in the where clause?

Update 2020-02-09:

The log output from Postgres shows the following:

2020-02-09 05:43:45.679 UTC [59458] ttnmapper@ttnmapper LOG:  execute <unnamed>: SELECT * FROM "ttnmapper_frequencies"  WHERE ("ttnmapper_frequencies"."mega_herz" = $1) ORDER BY "ttnmapper_frequencies"."id" ASC LIMIT 1
2020-02-09 05:43:45.679 UTC [59458] ttnmapper@ttnmapper DETAIL:  parameters: $1 = '868.2999877929688'
2020-02-09 05:43:45.688 UTC [59458] ttnmapper@ttnmapper LOG:  statement: BEGIN READ WRITE
2020-02-09 05:43:45.689 UTC [59458] ttnmapper@ttnmapper LOG:  execute <unnamed>: INSERT  INTO "ttnmapper_frequencies" ("mega_herz") VALUES ($1) RETURNING "ttnmapper_frequencies"."id"
2020-02-09 05:43:45.689 UTC [59458] ttnmapper@ttnmapper DETAIL:  parameters: $1 = '868.2999877929688'
2020-02-09 05:43:45.704 UTC [59458] ttnmapper@ttnmapper ERROR:  duplicate key value violates unique constraint "ttnmapper_frequencies_mega_herz_key"
2020-02-09 05:43:45.704 UTC [59458] ttnmapper@ttnmapper DETAIL:  Key (mega_herz)=(868.300) already exists.

The query

SELECT * FROM "ttnmapper_frequencies"  WHERE ("ttnmapper_frequencies"."mega_herz" = 868.2999877929688) ORDER BY "ttnmapper_frequencies"."id" ASC LIMIT 1

returns no results.

But however the query

SELECT * FROM "ttnmapper_frequencies"  WHERE ("ttnmapper_frequencies"."mega_herz" = 868.3) ORDER BY "ttnmapper_frequencies"."id" ASC LIMIT 1

returns a single row.

I therefore have to assume Postgres does not automatically round parameters for select queries. What would be the best solution for this?


texts: SQLAnywhere: Rename Foreign Key or Primary Key

texts:
I have a database with many duplicate index, foreign key, and primary key names. Duplicate index names can be renamed using:

ALTER INDEX <index name> ON <creator>.<tablename> RENAME TO <new name>

So that's fixed.

But keys are not indexes, so it doesn't work on keys:

"Cannot find index named '<index name>'"

There is a constraint rename syntax:

ALTER TABLE <creator>.<tablename> RENAME CONSTRAINT <constraint name> TO <new name>

This doesn't work on keys either:

"Constraint '<constraint name>' not found"

Is there any way to rename a foreign or primary key in SQLAnywhere without dropping and recreating the key?


texts:
I want my Linux MariaDB database to have disk quota implemented.
How can I achieve this these days?

I have found some tools but they are for MySQL, don't know if they are supposed to work on MariaDB.


texts: I have a production database with numerous scalar functions. My goal is to create set based alternatives for reporting. Unfortunately, I don't have a good way to view the T-SQL actually being executed because the functions reference a CLR assembly. Is there a good way to view the T-SQL inside the assembly for each function?


texts: Implement AKA in SQL Server Master Data Services

texts:
I'm working with a bunch of scientists and we're trying to implement a master data solution for the ingredients they use in formulations. The problem with their historic data is Scientist #1 will use Ingredient A but call it 'Alpha 100' while Scientist #2 will use Ingredient A and call it 'A100'. We want to implement Master Data Services to make sure the way the materials are characterized scientifically is consistent and then develop a template that will force scientists to pick ingredients from a drop down. However, Scientist #1 will be looking for 'Alpha 100' and Scientist #2 will be looking for 'A100' in a dropdown, so I'm curious, is there a way to implement an AKA (also known as) solution is SQL Server Master Data Services?
I was thinking it could just be a bunch of extra attributes that can be null and then unpivot them in a view, but that doesn't feel elegant.


texts:
I have values inserted with random intervals in the table. I would like to fetch the first (if exists) value in (say) 5 minutes interval. So is required to have 5 minutes column and the values column should express the first possible value before the next 5 minute range.
Using the aggregate function it is possible to get min, max, avg ... which is not the case here. I need the first possible value in the range.

Id  TSDest                  Value
1   2019-02-18 08:31:00.000 3472
2   2019-02-18 08:35:00.000 3471
3   2019-02-18 08:39:00.000 3464
4   2019-02-18 08:43:00.000 3462
5   2019-02-18 08:47:00.000 3456
6   2019-02-18 08:51:00.000 3455
7   2019-02-18 08:55:00.000 3463
8   2019-02-18 08:59:00.000 3466
9   2019-02-18 09:04:00.000 3166

I need the values aligned to a constant time interval e.g. 5 minutes
(A plus feature would be to get the nearest timestamp value in the range).

Id  TSDest                  Value
1   2019-02-18 08:30:00.000 3472
2   2019-02-18 08:35:00.000 3471

4   2019-02-18 08:40:00.000 3462
5   2019-02-18 08:45:00.000 3456
6   2019-02-18 08:50:00.000 3455
7   2019-02-18 08:55:00.000 3463

9   2019-02-18 09:00:00.000 3166

Help appreciated,