texts: Relational tables with same content, custom solution

texts:
So let`s say that I am building an app where I can add comments for FOO. Normally I would have something like this:

Table foo and table foo_comments, where foo_comments contains id, foo_id and other columns.

Right now this is the only way that I am using comments in my app. I did get a note from somebody that, if they will update the app in the future and they will want to add comments also for BAR, I should add something custom from right now, even if I don`t need it right now. So I was advised to add something like this

A table comments (for general use), that would contain a type column (where type can be an int representing the table which is referring to: foo, bar or something else) and a type_id column that would contain the id of the row from the main table.

So it`s this a good practice?
Should I build something like this, just because the app can be changed in the future? Or I just keep it with foo_comments table, and then if the app will add comments for BAR table, I would create a bar_comments table also.


texts: Diagnosing the error in a Postgresql to Redshift migration through psycopg2

texts:
I am running a job class that contains the following:

A Postgresql connection that can issue SQL statements
A Redshift connection that can do the same
an S3 connection to function as an intermediary between the two

The current process I am taking is dealing with the columns with redshifts differences on a per-case basis. The types of columns used (disregarding duplicate types, can give the rest if needed) are:

 id                             integer          NOT NULL,
 client_id                      integer          NOT NULL,
 manual_dt                      date,
 scheduled_at                   timestamp,
 some_other_id                  varchar(255),
 is_good                        integer          DEFAULT 0 NOT NULL,
 url                            varchar(4096)    NOT NULL,
 image_width                    integer,
 image_dim_ratio                float8,
 invalid_reason                 varchar(256)     DEFAULT NULL::character varying

There are a total of ~2.3 million records that need to be copied in this initial load. I am using this as a SELECT and COPY on postgresql, uploading it to S3, then using Redshift's COPY to get it from the s3 source. All of this can be posted if needed too.

This works on loads of 100, 1000, 10000, 100000, and 1000000 records. But, if I go to the entire set or a limit of the exact number of records, I receive the following trace:

psycopg2.extensions.TransactionRollbackError: canceling statement due to conflict with recovery

DETAIL:  User query might have needed to see row versions that must be removed.

Is this the source of what is stopping the copy, or am I on the wrong track and it is another issue?


texts: How can I optimize dashboard performance?

texts:
I have a table like
FieldA, FieldB, FieldC, FieldD, TheDate, Count

I have a web app which provides a dashboard of the top few Count by each of the fields.  Not wanting to prematurely optimize, the original, brute force queries for these charts are like:
SELECT TOP 10
    FieldA,
    SUM(Counts) AS Counts
FROM TheTable
WHERE @StartDate <= TheDate AND TheDate <= @EndDate
GROUP BY FieldA
ORDER BY Counts

And the same for the other fields.  But the server ends up selecting by date range independently for each chart and when there is a lot of data, the system is bogging down.
It seems wrong to get all the data in the app (once) then do the summary by the columns locally.  And maybe the RDBMS caches a lot of the records so the second through fourth chart are more efficient that the first.
I'm using Azure SQL and neither SQL Server Management Studio nor DataGrip suggest any missing indices which could help.
Any thoughts on techniques to do multiple, similar options on the same data?  In general or for Azure SQL.  Thanks.


texts:
In SQL Server 2008 I have a table with similar structure.  Unfortunately changing the data structure is not an option, so I am stuck with attempting to come up with an alternative.  I have tried to run this query, but I get an error of:

  (5 row(s) affected)
  Msg 8114, Level 16, State 5, Line 9
  Error converting data type varchar to numeric.

Here is DDL - what syntax should be changed in the query in order for me to receive valid output and not an error message?

Declare @GreenHouse Table
(nomen varchar(100), vtc varchar(100), d1 date)

Insert Into @GreenHouse (nomen, vtc, d1) VALUES
('Green', '507.02', '2016-01-14'), ('Green', '4.44089e-015', '2016-01-03')
,('Green', '200.57', '2016-01-18'), ('Green', '649.01', '2016-01-19'),
('Green', '1849.85', '2016-04-30')

Select nomen, Round(Sum(Cast(vtc As Decimal(10,2))),0.00) As FormatedInfo, d1
FROM @GreenHouse Group By nomen, d1 Order By d1 DESC


texts: Can CTE sub-statements be prevented from executing at all?

texts:
Through this strategy, time consumed by queries for my application have dropped to extreme lows; however, some queries consume more milliseconds than necessary.

The reason why is that upon examination of EXPLAIN ANALYZE, I found that sub-statements that depend upon the RETURNING of another query will still execute despite the source query returning an empty set.

In my application, I've reduced the number of queries from many 10s to four.  All are bulk operations.  The ones that have many sub-statements are the ones that consume 5ms or more.  It's those I'd like to trim.

I gave functions another chance and tried to manually prevent chain sections by using IF statements.  I had to use TEMPORARY TABLEs to store the RETURNING results and check them in the IFs.

I can't explain it, but the time consumed was 2 to 4x that consumed by the equivalent CTE.

To that end, is it possible to completely prevent a CTE sub-statement from executing if a RETURNING that it depends upon has no rows?

Another 50% reduction

I long ago followed the advice here, stripped out my comma joins where I could and replaced them with EXISTS.

Due to my inexperience, I replaced an EXISTS with a comma join in one sub-statement because I thought one query needed it instead.  The average time consumed dropped again.

I changed all of the EXISTS back to comma joins, and for queries with empty RETURNINGs, the time was chopped in half or more.

In those cases, the EXISTS go to disk, and it appears as if they still execute despite a previous WHERE condition that would guarantee no writing.

Performance for when the previous RETURNINGs return are the same.  The resulting relationships are 1:1, and the tables referenced are collections of integer references to other tables' primary keys.  The estimated EXPLAIN ANALYZE costs have gone through the roof.


texts: Is it safe to let application administrators look at v$session, v$process, and v$sql?

texts:
In an Oracle database shared among multiple application, is it 'safe' (from a security and system integrity perspective) to allow application administrators access to v$session, v$process, and v$sql? Or perhaps an overlying view limited to the application schema?

Thanks.


texts: How to Natively Return Output when using Invoke-Sqlcmd?

texts:
How do I natively log output from the PowerShell Invoke-Sqlcmd?

I am using Invoke-Sqlcmd to loop through a large (50,000-90,000 SQL files w/ 1-10 inserts in each) set of small SQL insert statements. Most of the insert statements run correctly, but a few of the strings are too long and I get the "string or binary data would be truncated" error.

Right now I am using Start-Transcript to capture the output and identify the problematic insert statements, but Start-Transcript captures everything. I'd like to only catch the errors -or- just catch the Invoke_Sqlcmd statement output.

If this worked (as I believe it should), I would be all set:

Invoke-Sqlcmd -InputFile $sql -ServerInstance LocalHost -Database Test_DB -Credential $pwd -Verbose -OutputSqlErrors $true | Out-File -FilePath "C:\Temp\Errorlog.txt" -Append

Or if this worked:

Invoke-Sqlcmd -InputFile $sql -ServerInstance LocalHost -Database Test_DB -Credential $pwd -Verbose -OutputSqlErrors $true | Tee-Object -FilePath "C:\Temp\Errorlog.txt" -Append

But neither of those work.

I have also tried this monstrosity but I don't get any errors at all from it:

$sqls = (Get-ChildItem -Path "I:\2019_06_24\_SQL"-Filter *.sql | Sort-Object -Property CreationTime).FullName
        foreach ($sql in $sqls)
            {
            $Error.Clear()
            try
                {
                Invoke-Sqlcmd -InputFile $sql -ServerInstance LocalHost -Database Test_DB -Credential $pwd -Verbose -OutputSqlErrors $true
                }
            catch
                {
                'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' | Out-File "C:\Temp\Errorlog.txt" -Append
                $sql | out-file -filepath "C:\Temp\Errorlog.txt" -Append
                $Error | out-file -filepath "C:\Temp\Errorlog.txt" -Append
                }
            }

How do I log directly from the Invoke-Sqlcmd statement?


texts:
I have progress .bup files for my databases The environment I am restoring too has less space and because I have no control over this environment I wish to make the databases smaller.

To do this I need to exclude certain tables from the restore process. (Alternatively I want to specify which tables to restore.)

The list for excluding is much smaller so would make more sense to specify what I wish to exclude certain tables, rather than a list of what must be  included.


texts: Sql Server replication: Initial snapshot for publication is not yet available?

texts:
I had setup transactional replication and my distributor, publisher are on same windows server and 2 subscriber one on same server and another on other windows server both using push snapshot.

I am getting the message in the synchronization status, that snapshot is not yet available, just a few hours back it was working fine every transaction was synchronized. The only thing i did in between was, added more articles(stored procedures, etc.) to the publication using its properties.

Is it causing the issue here, there is no error message displayed. Just that I am not able to get the data replicated


texts: I am working with a database, with  some forms. What I am trying to do is an auto increment field in my form. For example ID -- every time when I type some new record, this field ID should increment automatically like this: ID0001, ID0002 etc. I need a formula to put in before update to do that. Thanks.


texts:
I'll try to keep this to the point.

System:
OPenVZ VPS, debian 6, 512 MB RAM, 50 GB HDD (25GB free)

I have a mysqldump dump file (3GB), that was created when the innodb database was working fine.

After purging, removing  mysql and any related files/folders mariadb was installed (Actually this process has been repeated several times).
Next the dump file created earlier was imported (mysql -u user -p < sql.sql)

Immediately after importing the dump, I tried to take a backup using xtrabackup, it complained of corruption and crashed.

Output of check table tableName.

+------------------------------+-------+----------+---------------------------------------------------+
| Table                        | Op    | Msg_type | Msg_text                                          |
+------------------------------+-------+----------+---------------------------------------------------+
| db.table                     | check | Warning  | InnoDB: Index "product_id" is marked as corrupted |
| db.table                     | check | error    | Corrupt                                           |
+------------------------------+-------+----------+---------------------------------------------------+

Now comes the weird part. In The mysql-error log the page, the ascii page dump has strange sentences that should not be part of my VPS let alone the database or the table :

Part of the InnoDB: Page dump in ascii and hex (16384 bytes):

A fun three-picture set of the Okavango Delta in four sizes each  Okavango Jigsaw publish                                                      open

                       3@ me      < l                Z  N    Z  N       Cute Puppies is a slider with 6 different pictures of super cute puppies. 3 pictures are easy, and 3 puppy pictures are a bit harder. Have fun!  Cute Puppies publish                                                      open

The table in question has 3 columns - INT | INT | VARCHAR(50)

A cat trans.sql | grep  "super cute puppies" confirmed that the unexpected sentences were not in my DB dump.

Anybody knows why my table is corrupt ?  I have even tried to do a import from an older backup with similar results. And what about those strange sentences in the page dump ??

Lastly this all began after my VPS went offline for a couple of days (SSH brute force attacks, migration to new datacenter)

Thanks

Edit:
Before: Mysql 5.5 | After: 5.5.42-MariaDB

I thought Repair table was not for innodb..

btw can there be a unconfigurable limit to a mysql table size for the VPS ?

Edit: The unexpected sentences were part of a website that is also hosted by the same host. Found this after googling. Not sure what it means.


texts: Fast query for 'between' with overlapping ranges

texts:
I'm asking something very similar to Find if number is between two numbers in different fields in a MySQL database on Stack Overflow.

In the two options provided there, one is:

SELECT * FROM your_table WHERE 1265631252 BETWEEN `IP FROM` AND `IP TO`;

...which another poster said is slower compared to his current (and my current) method:

select * from your_table where ip_from <= 1265631252 and ip_to >= 1265631252;"

My table is 700+K rows, and it takes a long time to search the thousands of IP addresses I have. It takes 1 to 2 hours to query the thousands of IP addresses I have, and I'm wondering if there's a faster way.

The table contains overlapping ranges (third row overlaps with second and fourth row), e.g.

 IP FROM      IP TO         REGISTRY  ASSIGNED      CTRY  CNTRY  COUNTRY
"1265631232", "1265893375", "arin",   "1152835200", "US", "USA", "United States"
"1265893376", "1265958911", "arin",   "1149120000", "US", "USA", "United States"
"1265948800", "1266089983", "arin",   "1149120000", "US", "USA", "United States"
"1266024448", "1266089983", "arin",   "1162425600", "US", "USA", "United States"

The EXPLAIN results shows:

id  select_type    table      type    possible_keys    key       key_len    ref     rows      Extra
1   SIMPLE         mytable    range   PRIMARY          PRIMARY   8          NULL    197252    Using where


texts: Mongo DB Replica set - not able to read also

texts:
We have deployed mongodb replica set:

mongod --port 27017   --dbpath /opt/eltropy/mongodb/data/rs0-1 --replSet rs0 --smallfiles --oplogSize 128

mongod --port 27018   --dbpath /opt/eltropy/mongodb/data/rs0-1 --replSet rs0 --smallfiles --oplogSize 128

mongod --port 27019   --dbpath /opt/eltropy/mongodb/data/rs0-1 --replSet rs0 --smallfiles --oplogSize 128

What I understand from mongdb documentation; even if your two nodes goes down, we still should be able to read from remaining working nodes (Single working node in this case). However, write is not possible.

But even reads are not working on the available node. I also try to add one arbiter but still no luck. Remaining node still stay in secondary stage.

I am using mongodb 3.0.


texts: Unable to restore a PostgreSQL database from a .backup file

texts:
I am using Ubuntu 18.04 and PostgreSQL. For the GUI, i am using pgadmin4 (browser version). I created a new database and when trying to restore, the following error message comes up:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2 Final//EN"> <title>400 Bad Request</title>
Bad Request

The CSRF token is missing.

I Tried using the terminal way using this tutorial but it does'nt seem to work.

Here is the screenshot of the problem:


texts: How merge multi statement into one Select statement in sql

texts:
How to merge multi statement into one select statement.

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

below i have mentioned all 4 statements.

Statement 1

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

Statement 2

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

Statement 3

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

Statement 4

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


texts: Can I find out current disk response time in MS SQL Server using DMOs?

texts: using sys.dm_io_virtual_file_stats DMV I get io_stall which return response time in ms, but it is an accumulated result. Using Activity Monitor Data File I/O Pane, in the Response Time(ms) column we get aggregated but instant results. Though this column points to a corresponding field in the DMV, the results widely differ. In Activity Monitor there are relatively small numbers. I want to know how can I get that same instant response time via script. Is it possible to get that information in SQL Server using tsql or should I use server monitoring tools?


texts: SQL server Audit- Does it cause overhead

texts:
If I enable Audit on production databases that have over 100 m rows and have constant DML statements going (24 hours), is that going to cause any overhead on SQL server performance or tempdb? Does that increase the .ldf log size of the database its monitoring?

Any issues this can cause that I should be aware of?


texts:
I have a data warehouse with a lot of views built in with the SSMS view designer.
Viewing the syntax, the FROM clauses are unintelligible to me because the ON statements do not follow straight after the relevant JOIN <table>. Often the principal table of the query is RIGHT joined and a random "type" table is specified in the FROM. So I have a situation like:

SELECT *
FROM tableC
LEFT JOIN TableB
RIGHT JOIN TableA
ON TableA.ID = TableB.ID
ON TableB.TypeID = TableC.TypeID
WHERE ....

Changing the order of the various JOIN and ON clauses to what I would have hand written changes the results in unexpected ways. And it would seem that most SQL prettyfiers choke on this ugly syntax but SSMS doesn't. I haven't been able to find rhyme, reason or documentation to the help unravel what is going on.

Any suggestions or links for figuring out and fixing view designer queries please?


texts:
we are using Tungsten master to slave replication in MySQL.
we have configured this setup very long back.
from last week onwards  slave is in lagging with master around 5 to 10 binary files.
we have checked the N/W connectivity it is fine b/w master and slave.

we are not understanding why lagging is keep on increase?

How to  make slave is in sync with master in tungsten replication?


texts: Is that a good idea to use one database for 50.000+ shops?

texts:
I know Shopify use only one database for all shops.
But how can they handle their database with such a big data?
Is that a good idea to use single database for 50.000+ shops?


texts: SQL Server 2008 R2 Cluster Service pack 3 fail to failover node

texts:
I have a SQL Server R2 cluster on Windows 2008 R2 boxes, and today i was to install SP3. I installed it without any errors and rebooted the passive node.

When i then try to failover to the passive node it fails. I get this from the Log

Log Name:      Application
Source:        MSSQLSERVER
Date:          2015-03-24 18:38:45
Event ID:      19019
Task Category: Failover
Level:         Error
Keywords:      Classic
User:          N/A
Computer:      computername.domain.se
Description:
[sqsrvres] OnlineThread: SQLClusterResourceWorkerWaitForCompletion failed (status 0)

Event Xml:

    19019
    2
    3
    0x80000000000000

    16109747
    Application
    computername.domain.se

    [sqsrvres] OnlineThread: SQLClusterResourceWorkerWaitForCompletion failed (status 0)

    4B4A0040010000000D0000005700460041004C0049005400450043004C00300031003400000000000000

Log Name:      System
Source:        Microsoft-Windows-FailoverClustering
Date:          2015-03-24 18:38:45
Event ID:      1205
Task Category: Resource Control Manager
Level:         Error
Keywords:
User:          SYSTEM
Computer:      computername.domain.se.se
Description:
The Cluster service failed to bring clustered service or application 'Computername' completely online or offline. One or more resources may be in a failed state. This may impact the availability of the clustered service or application.
Event Xml:

    1205
    0
    2
    3
    0
    0x8000000000000000

    369312

    System
    computername.domain.se.se

    computername

If i failback to the unpatched node, i get the resorces online without problem. But the patched node just keep on failing.

Does anybode have an idea on where i should start diggning?


texts: Sybase ASE 15 "reorg rebuild" fixes performance issues - why and how to tell when it's needed?

texts:
I had a table of several million rows in a Sybase ASE 15 server which was running extremely slow, even basic queries would take 5 minutes to execute, for example:

SELECT COUNT(1) FROM TABLENAME;

After googling the issue I discovered that running a reorg rebuild on the table fixed the issue.

Is there a way to determine when a table requires a reorg rebuild, or when running reorg rebuild would result in performance gains?

Should reorg rebuilds be performed on a regular basis? If so, under what circumstances?

What is the underlying cause of the degradation that a reorg rebuild fixes? Can it be avoided?


texts:
Why is the following query slow?

select count(*)

from [dbo].[mt_dispatch_link]
   , [dbo].[_mt_dispatch] [_mt_dispatch]

where   (mt_dispatch_link.contract_id_1 = _mt_dispatch.contract_id
     and mt_dispatch_link.dispatch_id_1 = _mt_dispatch.dispatch_id)

   or   (mt_dispatch_link.contract_id_2 = _mt_dispatch.contract_id
     and mt_dispatch_link.dispatch_id_2 = _mt_dispatch.dispatch_id)

It takes over 10 minutes, and then I tend to stop it at that point. My question is more about how to make sense of the query plan.

Looking at the query plan I can see that the bottom clustered index scan is returning about 250000 records, but the cost is 0% and it is being put into a temporary table.

The top index scan is about 25000 records.

But the cost of 95% comes from the nested join. What conclusions should I draw from this?

The above query plan shows two index scans, does that mean it is doing 25000 + 250000 index scans, or does it mean that it is doing 25000 * 250000 index scans?

If I change the query to this (adding FORCESEEK):

select count(*)

from [dbo].[mt_dispatch_link]
   , [dbo].[_mt_dispatch] [_mt_dispatch]

    WITH (FORCESEEK)

where   (mt_dispatch_link.contract_id_1 = _mt_dispatch.contract_id
     and mt_dispatch_link.dispatch_id_1 = _mt_dispatch.dispatch_id)

   or   (mt_dispatch_link.contract_id_2 = _mt_dispatch.contract_id
     and mt_dispatch_link.dispatch_id_2 = _mt_dispatch.dispatch_id)

I end up with a much better plan and the query runs instantly:

I ran update statistics on both tables. Didn't fix it unfortunately. The table design is not very good, so I think SQL Server doesn't really understand and is thus coming up with a poor query plan. Some more info about the table design at How to Optimise Query.

Why is the query optimiser not coming up with the optimum plan?


texts:
Select For UPDATE NOWAIT

is performed on a table by 20 processes in parallel

The error below is returned by the 4 processes out of 20 (16 processes are successful)

SQL_UPDATE_ERROR- : SQL update error ORA-02049: timeout: distributed transaction
waiting for lock ORA-02063

Are  there some parameters in Oracle, which define  how many connections can be made to a table using a database link?

Order of the Steps is Select


texts: How do I add a constraint after I've truncated a table?

texts:
I am writing a script. The purpose of this script is to

Get all foreign key constraints on a database.
Truncate a table.
Re-Add the foreign key constraints that were removed.

I'm having trouble adding the constraints again [step 3] and I believe this is to do with the fact that my constraint conflicts with what is available. The data initially is...

authors table

1   Kenneth Graham  k.graham@twitw.com

books table

1   The wind in the willows Classic childrens story about woodland animals  123456

Authorbooks table

1   1   1

I have the following constraints on the authorbooks tables:

ALTER TABLE [dbo].[AuthorBooks]  WITH CHECK ADD  CONSTRAINT [FK_Books_Author_xref_Books] FOREIGN KEY([BookId])
REFERENCES [dbo].[Books] ([ID])
GO
ALTER TABLE [dbo].[AuthorBooks]  WITH CHECK ADD  CONSTRAINT [FK_Books_Author_xref_Authors] FOREIGN KEY([AuthorId])
REFERENCES [dbo].[Authors] ([Id])
GO

So when I try and add the constraints I get the following error:

  The ALTER TABLE statement conflicted with the FOREIGN KEY constraint
  "FK_Books_Author_xref_Authors". The conflict occurred in database
  "PeteDb", table "dbo.Authors", column 'Id'.

Is there any way around this and being able to add the constraint in regardless?


texts: Failover Cluster, Availability Group, and Disaster Recovery

texts:
We have an active/passive Windows Failover Cluster setup as an HA solution. Here is an example of what that might look like:

Cluster1: [Node1 'active', Node2 'passive']

We're looking to set up a DR solution by making use of Availability Groups in SQL Server 2016. To do this, we plan to have the following setup:

Cluster1: PRIMARY[Node1 'active', Node2 'passive'], SECONDARY[Node3 'standalone']

Node1 and Node2 can have resources failover to each other. They even share a storage. Node3 will be part of the same Windows Failover Cluster because it has to be in order for High Availability to work. However, Node3 will NOT share resources with Node1/Node2. Node3 will host the secondary replica, the primary replica will be on the instance that is shared between Node1 and Node2.

Is our understanding that all nodes need to be on the same Windows Failover Cluster for Availability Groups to work correct? We read it here but aren't fully sure of what's being said.
Is it possible to set up HA in this manner? Where Node1/Node2 share resources but Node3 has a separate instance which cannot ever be a part of Node1/Node2.


texts: single or multiple databases advice needed

texts:
I know there are a few of these posts, but not in relation to my specific quandary.

I've always gone the 'per user' SQL Server DB rather than 'one DB for all' because:

a) I thought speed wise it's better for a lite user to only work within their small DB rather than all users being possibly affected by a few big users (surely searching a few hundred rows each time is better than searching through thousands of rows all the time).

b) If a server has too many DBs (very wishful thinking) then I can put the next lot of users on a 2nd, 3rd, 4th, etc server.

c) If a user no longer wishes to use my app or needs it restoring to a backup then I just bin/or restore their specific DB and don't affect anybody else.

My problem is that my next pet project involves pupils potentially competing against pupils in other schools and also showing a league table of schools so I just keep thinking a per school DB is going to be really messy compared to everybody in one DB.  Would you agree?

My fear of one DB is that later down the line there might be potentially 500 schools (again wishful thinking) with on average 300 pupils in each school which is a lot of pupil logins and results to manage in one DB - without it being lumpy.

Anybody had similar problems and overcome them?

Thanks


texts: How can I disable the @ symbol in SQL scripts for sqlplus?

texts:
In Oracle's sqlplus tool, when the script contains an @ symbol, it loads an SQL script with that name and executes it. I'm inserting a VARCHAR value which contains some CSS code, so when it encounters a @media query or @keyframes it tries to execute media.sql, which is not the desired effect. This does not happen when I execute the script in the Oracle SQL Developer window.

A small proof-of-concept script:

SET DEFINE OFF
SET SQLBLANKLINES ON
SET SQLTERMINATOR OFF
SET SQLPREFIX '%'
SET SCAN OFF

DECLARE
    loginId NUMBER;
    ncount NUMBER;
    css VARCHAR2(32767) DEFAULT '
@media only screen and (min-width: 980px) {
    .footer-column-copy {
        margin: 0;
        padding-top: 0;
        padding-bottom: 80px;
        background-size: 55px;
        background-position: top right;
        float: left;
        width: 260px;
    }
}
';

This triggers the following error:

$ echo @poc.sql | sqlplus username/password@localhost

SQL*Plus: Release 18.0.0.0.0 - Production on Wed Jul 17 08:53:16 2019
Version 18.5.0.0.0

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

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SP2-0310: unable to open file "LOGIN.SQL"
SQL> SP2-0310: unable to open file "media.sql"
 16
SQL> Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

In the same way that SET SQLPREFIX can disable the parsing of the # symbol, how can I disable the parsing of the @ symbol (or change it to something else like the SET SQLPREFIX command does)?


texts: How to join two table and show one query result in MySQL?

texts:
I have a table1 (records 3), and table2 (records 3). Where i have field name in both. Now i want to make a result from those two table, which will show me both table records and take only one if there is duplicate. So my expected output records will contain 5 rows not 6 rows. How do i do that?

Example:

table1:                       table2:

+-------------------------+   +--------------------------------+
| Name                        | Name
---------------------------   +---------------------------------
| A    |                      | C    |
| B    |                      | D    |
| C    |                      | E    |

My Expected output is:

+-------------------------+
| Name | ID
---------------------------
| A    | 1 table1
| B    | 2 table1
| C    | 3 table2 or table1 (no unique)
| D    | 4 table2
| E    | 5 table2

I tried this:

 SELECT
  name as name
 FROM
  table1
UNION
 SELECT
  anothernamename as name
 FROM
  table2
WHERE
   name like '%C%'

Error: #1054 - Unknown column 'name' in 'where clause'

Follow up: Union with Where clause + extra extra large requirement.

SELECT * FROM
(
    (
     SELECT * FROM table1
     WHERE ...
     ORDER BY ...
     LIMIT ...
    )
UNION
    (
     SELECT * FROM table2
     WHERE ...
     ORDER BY ...
     LIMIT ...
    )
) as t
WHERE ...
    ORDER BY ...


texts: Inheritance of unknown type at creation time

texts:
Say I have a table called People into which people are inserted from the moment they are born. Now lets say I want some subclasses of people based on their job. So there are also tables for Plumber, Carpenter and Electrician. Now, when a person is born, I don't know if they're going to grow up to be plumbers, or carpenters, or electricians - So I can't have reference the person in every table via a foreign key.

So when little Johnny grows up, how can I bind him to being a Plumber. Is there some sort of pattern for this, or is this a stupid question with an obvious answer? Either way I'd appreciate some help, thanks!


texts: MongoDB with Sharding has slow performance

texts:
I have the following setup for my MongoDB Sharded Cluster (everything is running on the same machine on localhost, just different port numbers):

ConfigServers : 1 ReplicaSet consisting of 3 mongod instances
Router : 1 mongos instance
Shard : 1 ReplicaSet consisting of 3 mongod instances

I have logged into the Router and executed the addShard command:

sh.addShard("shard_rs0/localhost:27022,localhost:27023,localhost:27024");

I have not added any shard keys or enabled shardding via the "sh.enableSharding" command. I also don't have any additional shards as I want to just test using the single shard on it's own and see if the unsharded collections maintain the expected write/read speeds.

I have a .NET Web API that connects to the mongos (i.e. Router) via the CSharpDriver.

The issue I am having is that when connecting to a ReplicatSet directly, I get decent read/write speeds but when I connect via the Router, I see a significant decrease in speed. Is this because I haven't supplied a ShardKey yet or perhaps that I only have 1 shard? I would expect the unsharded collections in the Primary Shard to be unaffected by the Cluster setup but it seems there are indeed overhead costs that are slowing down the queries?

Tests Results when adding 100 000 records (using the InsertOne method on the CSharpDriver for each records). I know I could have done a batch insert but I decided to stick with the InsertOne. I did this 7 times, each time adding 100 000 records to an unsharded collection, firstly directly to the Shard itself and then via the Router. When writing via the Router, I saw the following degradation in write speeds when compared to writing to the Shard directly:

- Writing is 30% slower
- Writing is 39% slower
- Writing is 38% slower
- Writing is 42% slower
- Writing is 36% slower
- Writing is 40% slower
- Writing is 36% slower


texts: What is the difference between NULL and \N while loading data from a csv using load data local infile

texts:
I have a table which has four fields

create table test_hc1(cola int,colb int,colc varchar(20),cold int);

Now, I am trying to load following data

1,2,\N,4

and the third field in table is coming out as NULL, which is the expected result.

In another case, I am trying to load following data

1,2,NULL,4

and it is also loading the third field as NULL.

Here is my question : What is the difference between using the two(\N, NULL) ?

I am using

LOAD DATA LOCAL INFILE 'PATH_TO_DATAFILE'
INTO TABLE TABLE_NAME FIELDS TERMINATED BY x'01';

Any help is appreciated.

Thanks


texts:
Consider a seat booking database. There is a list of n seats, and each one has an attribute is_booked. 0 means it isn't, 1 means it is. Any higher number and there is an overbooking.

What is the strategy for having multiple transactions (where each transaction will book a group of y seats concurrently) without allowing over bookings?

I would simply select all unbooked seats, select a randomly selected group of y of them, book them all, and check if that booking is correct (aka the number of is_booked is not over one, which would signify another transaction having booked the seat and committed), then commit. otherwise abort and try again.

This is run at isolation level Read Committed in Postgres.


texts: Select statement from 3 tables with join

texts:
How can I select from 3 tables (task, status, employee)?

Task

TASK_ID (PK),
TASK_NAME ,
DESCRIPTION ,
STATUS_CODE (FK for status table)
START_DATE ,
END_DATE ,
TASK_TYPE_CODE (FK) ,
PRIORITY_CODE(FK) ,
EMPLOYEE_ID (FK for employee table)

Status

STATUS_CODE (PK) ,
STATUS_NAME

Employee

EMPLOYEE_ID (PK),
EMPLOYEE_NAME

I want to select task_id, task_name, and end_date "from-to" (any date from the calendar) to (any end date) from the task table, status_ name from the status table, and employee_name from the employee table.


texts: Oracle Transaction recovery: lock conflict caught and ignored

texts:
In alert log there are a lot of "Transaction recovery: lock conflict caught and ignored" statements.

I've found some information, that this error is caused when there is an active dead transaction. For example, long running transaction was forcefully stopped and it did not managed to recover(caused by no free space in UNDO tablespace, or aborting database...)

When I query the following:

select b.name useg, b.inst# instid, b.status$ status, a.ktuxeusn
xid_usn, a.ktuxeslt xid_slot, a.ktuxesqn xid_seq, a.ktuxesiz undoblocks,
a.ktuxesta txstatus
from x$ktuxe a, undo$ b
where a.ktuxecfl like '%DEAD%'
and a.ktuxeusn = b.us#;

It returns 23 rows , so there are active dead transactions.

I tried to DUMP UNDO HEADER, dig in the trace files and find object ids that needed recovery. I found these objects and re-created them(was suggested in some blog) but still did not help.

Note: When there is a supplemental logging enabled , Oracle creates incident files and core dump files and writes mentioned error in alert log, which causes server space to be exhausted. If you turn the supplemental logging off, just alert log contains this error message and NO incident/core dump files are created, better variant, but still disturbing when entries are a lot.


texts:
I have this request to collect some statistics on a database over a period of time.

Unique entries of the queries being run on a database from a application server ( Can be done with running a Trace )
For each query - be able to see its total elapsed time and amount of data transferred (in KB / MB, not number of records). ( Can also get total elapsed time from the trace per query ). But I am not sure how to find data transferred.

Is there a way to collect this data using profiler and perfmon.


texts: Can connect using SQL Server Management Studio but cannot ping or via program

texts:
I have a remote SQL Server: myremotedb.myremotedomain.gbl.

I could connect to it from another server myserver.myareadomain (not in the same domain) using SQL Server Management Studio without problem.

But I couldn't ping it from my server (myserver.myareadomain), nor connect to it via the program that we developed.

PS: I don't have admin right on the remote server.

Do you have any ideas?


texts: When is it appropriate database design to use a separate schema?

texts:
I am developing a database which has data sourced from many different applications.  In my first pass at design, I placed the staging tables each in a schema named for their source application.

As several of the source applications have similar data and similar table names, I use the schema name to differentiate the source application.  The alternative I am considering would be using a single schema and including the source application in the table name.

I wanted to look into the design rules pertaining to when to use a different schema and the pros and cons of doing so and I could not find anything.

Is the schema purely for permissioning and security?

Does it make sense from an organizational point of view to create objects in separate schemas beyond what is required for application development or is this just needlessly adding complexity to queries?

Are there any other repercussions of this decision which I have neglected to consider?


texts: Viewing disk usage of temporary buffers of running queries in Postgres

texts: I know how to get the sizes of relations or total table disk usage per table in Postgres 9.5 (https://wiki.postgresql.org/wiki/Disk_Usage), but I have some queries running whose temporary disk buffers use significant amounts of space. How can I see how much each is using? My disk is slowly running out of space due to many long-running queries, and I need to kill one of them.


texts:
I have a .my.cnf file in my home directory on my desktop that includes:

[dbid]
user = myusername
password = mypassword
database = dbname
host = server.location.com

If I recall correctly, before upgrading from Ubuntu 10.04 to 11.04, I was able to use the command

mysql dbid

To connect directly to the database

But today I get this error:

ERROR 1049 (42000): Unknown database 'dbname'

Have I done something wrong?


texts: SARGable WHERE clause for two date columns

texts:
I have what is, to me, an interesting question on SARGability. In this case, it's about using a predicate on the difference between two date columns. Here's the setup:

USE [tempdb]
SET NOCOUNT ON

IF OBJECT_ID('tempdb..#sargme') IS NOT NULL
BEGIN
DROP TABLE #sargme
END

SELECT TOP 1000
IDENTITY (BIGINT, 1,1) AS ID,
CAST(DATEADD(DAY, [m].[severity] * -1, GETDATE()) AS DATE) AS [DateCol1],
CAST(DATEADD(DAY, [m].[severity], GETDATE()) AS DATE) AS [DateCol2]
INTO #sargme
FROM sys.[messages] AS [m]

ALTER TABLE [#sargme] ADD CONSTRAINT [pk_whatever] PRIMARY KEY CLUSTERED ([ID])
CREATE NONCLUSTERED INDEX [ix_dates] ON [#sargme] ([DateCol1], [DateCol2])

What I'll see pretty frequently, is something like this:

/*definitely not sargable*/
SELECT
    * ,
    DATEDIFF(DAY, [s].[DateCol1], [s].[DateCol2])
FROM
    [#sargme] AS [s]
WHERE
    DATEDIFF(DAY, [s].[DateCol1], [s].[DateCol2]) >= 48;

...which definitely isn't SARGable. It results in an index scan, reads all 1000 rows, no good. Estimated rows stink. You'd never put this in production.

It would be nice if we could materialize CTEs, because that would help us make this, well, more SARGable-er, technically speaking. But no, we get the same execution plan as up top.

/*would be nice if it were sargable*/
WITH    [x] AS ( SELECT
                * ,
                DATEDIFF(DAY, [s].[DateCol1], [s].[DateCol2]) AS [ddif]
               FROM
                [#sargme] AS [s])
     SELECT
        *
     FROM
        [x]
     WHERE
        [x].[ddif] >= 48;

And of course, since we are not using constants, this code changes nothing, and is not even half SARGable. No fun. Same execution plan.

/*not even half sargable*/
SELECT
    * ,
    DATEDIFF(DAY, [s].[DateCol1], [s].[DateCol2])
FROM
    [#sargme] AS [s]
WHERE
    [s].[DateCol2] >= DATEADD(DAY, 48, [s].[DateCol1])

If you're feeling lucky, and you're obeying all the ANSI SET options in your connection strings, you could add a computed column, and search on it...

ALTER TABLE [#sargme] ADD [ddiff] AS
DATEDIFF(DAY, DateCol1, DateCol2) PERSISTED

CREATE NONCLUSTERED INDEX [ix_dates2] ON [#sargme] ([ddiff], [DateCol1], [DateCol2])

SELECT [s].[ID] ,
       [s].[DateCol1] ,
       [s].[DateCol2]
FROM [#sargme] AS [s]
WHERE [ddiff] >= 48

This will get you an index seek with three queries. The odd man out is where we add 48 days to DateCol1. The query with DATEDIFF in the WHERE clause, the CTE, and the final query with a predicate on the computed column all give you a much nicer plan with much nicer estimates, and all that.

Which brings me to the question: in a single query, is there a SARGable way to perform this search?

No temp tables, no table variables, no altering the table structure, and no views.

I'm fine with self-joins, CTEs, subqueries, or multiple passes over the data. Can work with any version of SQL Server.

Avoiding the computed column is an artificial limitation because I'm more interested in a query solution than anything else.


texts:
I have a query in which i have used the following

date_add(DATE_FORMAT(ss.week_startdate,'%y-%m-%d'),INTERVAL (tt.day_id- 1 )DAY) as date

Datatype of the field is datetime and the result date is like this

2015-04-06

but the desired result is like this

2015-04-6

I don't need 0 in front of day. I tried this

date_add(DATE_FORMAT(ss.week_startdate,'%y-%m-%e'),INTERVAL (tt.day_id- 1 )DAY) as date

but didn't work. Can anyone help please!


texts: Fill in NULL values with those from another another table

texts:
MySQL 5.6

Table: Employees

    EmployeeID   |   Name   |   PIN
    -------------+----------+---------
    1            |   Bob    |  1234
    2            |   Jim    |  3214
    3            |  Claire  |  4321
    4            | Jessica  |  NULL
    5            |  James   |  NULL
   ...           |   ...    |  ...

Table continues and all the PINs are NULL.

There's a second table: PINS

    PinID   |   PIN
    --------+--------
    1       |   1010
    2       |   1983
    3       |   5281
    4       |   6292
    5       |   9998
   ...      |   ...

Table continues on.
These numbers need to be added in to the null values. There can be no duplicates and they need to be in the order that they're listed in.

Goal: Employee

    EmployeeID   |   Name   |   PIN
    -------------+----------+---------
    1            |   Bob    |  1234
    2            |   Jim    |  3214
    3            |  Claire  |  4321
    4            | Jessica  |  1010
    5            |  James   |  5281
   ...           |   ...    |  ...

I've been attempting stuff similar to this:

UPDATE Employees
SET PIN =(SELECT PIN
          FROM PINS
          WHERE Employees.PIN <> PINS.PIN )
WHERE PIN IS NULL;

I'm at a loss, is there any way to bring the PINS over?

EDIT: I made some progress with:

UPDATE Employees
SET PIN = (SELECT PINS.PIN
           FROM PINS
           WHERE PINS.PinID = Employees.EmployeeID)
WHERE EXISTS ( SELECT 1
              FROM PINS
              WHERE PINS.PinID = Employees.EmployeeID)

;

however it replaces the starting PINS as well

EDIT2: added the goal table


texts: Preserve order of array elements after join

texts:
I have a query that returns a CTE looking like

+-----------+-------------+
|   node_id | ancestors   |
|-----------+-------------|
|         1 | []          |
|         2 | []          |
|         3 | [1]         |
|         4 | [2]         |
|         5 | [4, 2]      |
+-----------+-------------+

What I want to do is join with the nodes table and replace the ids that are in the ancestors column with another column on the nodes table. Here's my query so far:

WITH RECURSIVE tree AS (
  -- snip --
)
SELECT node.entity_id AS id,
       array_remove(array_agg(parent_nodes.entity_id), NULL) AS ancestors
FROM tree
JOIN entity.nodes AS node ON node.id = tree.node_id
LEFT OUTER JOIN entity.nodes AS parent_nodes ON parent_nodes.id = ANY(tree.ancestors)
GROUP BY node.id;

The problem with that query is that is loses the order of the original ancestors array. Is there a way to perform the join while keeping the original order during the array_agg function?


texts:
I have 3 tables:

Products, containing - id, name and description
Attributes, containing - id, name
prod_attri_rel, containing - id, prod_id, attri_id and value

Products looks like:

 1 | test | just a description for test
 2 | test2| just another description

Attributes looks like:

 1 | height
 3 | length
 2 | width
 4 | power
 5 | id

prod_attri_rel looks like:

1 | 1 | 1 | 2
2 | 1 | 2 | 25
3 | 1 | 3 | 20
4 | 2 | 1 | 2
5 | 2 | 2 | 25
6 | 2 | 3 | 20
7 | 2 | 4 | UBEC
9 | 2 | 5 | BC2212-850

After looking at https://dba.stackexchange.com/a/33307/48727 and https://stackoverflow.com/a/695860/1820180

Now I would like to get this result:

id | name | height | width | length | maybe other attr | so on and so forth
  1| test | 2      | 25    | 20     | ...

When I use the following query:

SELECT p.id, p.name , par.value, a.name FROM products p
JOIN prod_attri_rel par on p.id = par.prod_id
JOIN attributes a on par.attri_id = a.id

I get:

id | name | value| name
1  | test | 2    | height
1  | test | 25   | width
1  | test | 20   | length

Then when I tried:

SELECT p.id, p.name , group_concat( par.value ), group_concat( a.name ) FROM products p
JOIN prod_attri_rel par on p.id = par.prod_id
JOIN attributes a on par.attri_id = a.id GROUP BY p.name

I get a better result but not really it:

id | name | value   | name
1  | test | 25,2,20 | width,height,length

Last I found this https://stackoverflow.com/a/10926106/1820180 but I did not understand it enough to adapt it to my problem.

Any help in this matter would be appreciated, even if it is finding another method, or getting it to work.

Purpose: This is a database for different parts to RC toys, such as motors, ESC's wheels, propellers and such, all have different attributes, but all motors have same attributes, all wheels have same attributes.


texts:
CAP Theorem vs. BASE (NoSQL)

Hi,
I’m trying to write a small paper for my work about NoSQL and have described the CAP Theorem as, if not all, then most NoSQL databases adheres to. I later read a paper about the difference between NoSQL and RDBMS which stated that NoSQL databases use the ACID counterpart BASE.

I know the properties of BASE, ACID, and CAP but I’ve a hard time figuring out what relation the CAP theorem and BASE has to each other. As I understand it, the CAP theorem leads to the BASE acronym but is it the right conclusion? Or is it two different approaches to “built” a database around which share some similar properties??

In this blog post (http://www.johndcook.com/blog/2009/07/06/brewer-cap-theorem-base/) the author writes:

  It’s harder to develop software in the fault-tolerant BASE world compared to the fastidious ACID world, but Brewer’s CAP theorem says you have no choice if you want to scale up. However, as Brewer points out in this presentation, there is a continuum between ACID and BASE. You can decide how close you want to be to one end of the continuum or the other according to your priorities.

In another article the author writes:

  Many of the NOSQL databases above all have loosened up the requirements on Consistency in order to achieve better Availability and Partitioning. This resulted in systems know as BASE (Basically Available, Soft-state, Eventually consistent). These have no transactions in the classical sense and introduce constraints on the data model to enable better partition schemes (like the Dynamo system etc). A more comprehensive discussion of CAP, ACID and BASE is available in this introduction.

This clearly state that CAP results in BASE.

I hope someone can clarify this to me and remove my confusion..

Thanks
- Mestika


texts: Why does an Oracle database require both the SYSTEM and the SYSAUX tablespaces?

texts:
Quoting from Oracle documentation about logical storage structures:

  A database must have the SYSTEM and
  SYSAUX tablespaces:

Why is it compulsory to have these tablespaces?


texts: How to get value from range datetime from date format to bigint in postgresql

texts: I'm new using postgresql and had difficult to convert datetime to bigint type. So my tables has column timecreated that datatype is bigint. I want to get value with range datetime format like dd/mm/yyyy example : select * from mytables where timecreated_at between '01/09/2021' and '10/09/2021' . How i can achieved this?


texts: Error Grouping By Multiple Columns using CASE

texts:
Is it possible to group by multiple columns in a CASE statement? I get error "Incorrect syntax near ','." in below code...

SELECT
  CASE
     WHEN Type = 'Test'
       THEN Description
     ELSE MAX([Description])
  END AS 'Description'
GROUP BY
CASE
    WHEN Type = 'Test'
        THEN Description
    ELSE ''
END,
Type, Account, Structure


texts:
I have two listners:

one is running on port 1521 another on 1531.

lsnrctl stat shows status of services running on port 1521 only.

How should I check whether the services running on port 1531 is proper or not.

Listner Entry:

    # listener.ora Network Configuration File: C:\app\Administrator\product\11.2.0\dbhome_1\network\admin\listener.ora
    # Generated by Oracle configuration tools.

    ADMIN_RESTRICTIONS_LISTENER = ON

    LISTENER =
      (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
        )
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
          (PROTOCOL_STACK =
            (PRESENTATION = GIOP)
            (SESSION = RAW)
          )
        )
      )

    ADR_BASE_LISTENER = C:\app\Administrator

    SID_LIST_PARIVARTAN =
      (SID_LIST =
        (SID_DESC =
          (GLOBAL_DBNAME = PARIVARTAN)
          (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)
          (PROGRAM = extproc)
          (SID_NAME = PARIVARTAN)
        )
      )

    PARIVARTAN =
      (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1531))
        )
      )

    ADR_BASE_PARIVARTAN = C:\app\Administrator

TNSNAMES Entry

    # tnsnames.ora Network Configuration File: C:\app\Administrator\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
    # Generated by Oracle configuration tools.

    ORCL =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
        )
        (CONNECT_DATA =
          (SERVICE_NAME = orcl)
        )
      )

    PARIVARTAN =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1531))
        )
        (CONNECT_DATA =
          (SERVICE_NAME = parivartan)
        )
      )

tns ping result:

    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
    (HOST = 127.0.0.1)(PORT = 1531))) (CONNECT_DATA = (SERVICE_NAME = parivartan)))
    OK (10 msec)


texts:
I am looking for insert IIS 6.0 access log ( 5 servers, and over 400MB daily ) to SQL database. What scares me is the size. There is a lot of information you are duplicating (i.e. site name, url, referrer, browser) and could be normalized by index and look-up table.

Reason why I am looking for own database instead using other tools is that is 5 servers and I need very custom statistics and reports on each, few or all. Also installing any (specially open source) software is massacre ( need have 125% functionality and take months ).

I wounder what would be the most efficient way to do it? Is someone saw examples or articles about it ?


texts: MySQL not updating a row (zero row(s) affected)

texts:
I cannot figure out why MySQL 5.6.25 will not update an existing row:

SELECT
  lcl_booking.file_number_id,
  lcl_booking.entered_datetime,
  lcl_booking.modified_datetime
FROM lcl_booking
WHERE lcl_booking.file_number_id = 197467;

yields:

file_number_id entered_datetime     modified_datetime
197467         2015-06-26 15:26:44  2015-07-08 10:39:15

Now, I do this:

UPDATE lcl_booking SET
  modified_datetime = "2015-07-21 10:54:04"
WHERE file_number_id = 197467;

returns:

1 queries executed, 1 success, 0 errors, 0 warnings

Query: UPDATE lcl_booking SET modified_datetime = "2015-07-21 10:54:04" WHERE file_number_id = 197467

0 row(s) affected

Execution Time : 0.019 sec
Transfer Time  : 0 sec
Total Time     : 0.019 sec

and....

SELECT
  lcl_booking.file_number_id,
  lcl_booking.entered_datetime,
  lcl_booking.modified_datetime
FROM lcl_booking
WHERE lcl_booking.file_number_id = 197467;

yields:

file_number_id entered_datetime     modified_datetime
197467         2015-06-26 15:26:44  2015-07-08 10:39:15

Here is the table (minus a bunch of columns)

SHOW CREATE TABLE lcl_booking;

CREATE TABLE `lcl_booking` (
  `file_number_id` bigint(20) unsigned NOT NULL,
  `entered_datetime` datetime NOT NULL,
  `modified_datetime` datetime NOT NULL,
PRIMARY KEY (`file_number_id`),
KEY `lcl_booking_fk1` (`file_number_id`),
CONSTRAINT `lcl_booking_fk1` FOREIGN KEY (`file_number_id`) REFERENCES `lcl_file_number` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='LCL Booking Header';

Anyone have any ideas?


texts:
Is there anyway to know when was the last fail-over happen and exact Root cause of fail-over? Can we get history of all the previous primary nodes list and time of their fail-overs?
I have explored a lot in internet. I didn't get the exact steps to see that.


texts: Scripting SQL Server into multiple SQL scripts

texts: I have a SQL Sever table with 2GB of data. I can create the script for the file. But the script file is not opening. I want to split that script file into multiple script files.


texts: Configure DB to respond only from a specific IP address

texts:
I have a server with multiple IP addresses (say ... 10.1.1.10 & 10.1.1.11) with several databases.
And I want to say DBx is only responding if the client uses the IP 10.1.1.10 and DBy is only reachable with the IP 10.1.1.11

I have found lots of example when you restrict the allowed client ip address but nothing so far for sticking DBs on dedicated IP address

any idea ?


texts:
I have an application front end which speaks to a PostgreSQL database. I'm trying to find the most efficient way to extract a structure similar to the below.

CREATE TABLE people (
  person_id SERIAL PRIMARY KEY,
  fname     VARCHAR
);

CREATE TABLE items (
  person_id INTEGER REFERENCES people (person_id),
  item_id   SERIAL PRIMARY KEY,
  title     VARCHAR
);

INSERT INTO people (fname) VALUES
  ('Bob'),
  ('Jim'),
  ('Geoff');

INSERT INTO items (person_id, title) VALUES
  (1, 'Cat'),
  (1, 'Dog'),
  (1, 'Monkey'),
  (2, 'Elephant');

My current approach would be to list all of the items, then (within the application) iterate over each and run a SELECT. Something similar to this Pseudo code:

items = db.Query(SELECT * FROM people);
for item in items
   itemsub = db.Query(SELECT * FROM items WHERE id = item.person_id)

There are a number of reasons why I don't like this.

It requires a minimum of 1 + N(People) queries to extract a simple data structure. For a page containing 1000 items, this would produce a ton of network traffic.
The application has to manually iterate over the list atleast twice more after the database, one to build the better structure and a second to render it.
It seems like something a database should be able to do

One other alternative would be to perform a JOIN, but this would result in extra processing to remove duplicates in the application

The resulting structure should be relatively simple:

peopleItems{
   array people{
             array items{}
         }
}

However, I can't for the life of me work out the best approach.


texts: I have following two MySQL queries and i want to execute it inside one procedure

texts:
Following query returns 10 rows of recipe id's i wants to pass first query's output which is of 10 rows to the second query and display the output of both the queries inside a procedure.

Query 1:

SELECT
    p.ID as recipe_ID,
    p.post_title as recipe_title,
    p.post_date as modified_date,
    p.post_name as recipe_url,
    CASE WHEN s.total_count THEN s.total_count ELSE 0 END as total_like_count,
    CASE WHEN c.bookmark=1 THEN 1 ELSE 0 END as bookmark_status,
    CASE WHEN c.like_status=1 THEN 1 ELSE 0 END as like_status,
    CASE WHEN pm.meta_value=1 THEN 1 ELSE 0 END as unlock_status

FROM
    posts p
LEFT JOIN social_val s on(p.ID=s.post_id)
LEFT JOIN social_status_connection as c on(p.ID=c.post_id AND c.user_id=1)
LEFT JOIN postmeta pm on(p.ID=pm.post_id AND pm.meta_value=1 AND pm.meta_key='recipe_lock'),
term_relationships as t, term_taxonomy tt
WHERE
  p.post_type = 'recipe'
    AND p.post_status = 'publish'
    and p.ID = t.object_id
    and tt.term_id = 1279
    and t.term_taxonomy_id=tt.term_taxonomy_id

ORDER BY modified_date DESC
LIMIT 0 , 10

Query 2
Below query is self join which generates image of above particular recipe id.

SELECT
    p.guid as recipeImageURL
FROM
    posts p,
    posts m
where
    p.post_type = 'attachment'
        AND p.post_parent = m.ID
        AND m.ID = ID ORDER BY p.post_modified DESC
LIMIT 1;


texts: Turn off (suppress) Msg 3013 "BACKUP DATABASE is terminating abnormally"

texts:
When backup operation fails, it produces 2 error msgs

Msg 911, Level 16, State 11, Line 22
Database 'not_existing_db' does not exist. Make sure that the name is entered correctly.

Msg 3013, Level 16, State 1, Line 22
BACKUP DATABASE is terminating abnormally.

It is making it very hard to capture the "root cause" error message (example 911) instead of that generic 3013 message

Is there any way to suppress message 3013 so SQL Server does not throw it anymore ?


texts: Create View in specified database with dynamic sql?

texts:
I am writting a dynamic sql to drop and create view in different database.

So I wrote:

set @CreateViewStatement =
                '
                USE ['+ @DB +'];
                CREATE VIEW [dbo].[MyTable]
                AS

                SELECT ........something
exec (@CreateViewStatement)

It gives me error:

  'CREATE VIEW' must be the first statement in a query batch.

If I remove the USE DATABASE statement it works fine, but then the database is not specify anymore....

How can I solve this problem?


texts: No mysql database in mysql server - how do I give users permissions?

texts:
I have a mysql server (5.5.44) running on my local Ubuntu 14 machine.
I can start the interactive shell via

$ mysql

When I ask to see all the databases, there is only one

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+

If I try to make a new database I get an error

mysql> CREATE DATABASE purchases;
ERROR 1044 (42000): Access denied for user 'danielsank'@'localhost' to database 'purchases'

I am puzzled by how to resolve this problem.
I'm used to mysql servers always having a database called mysql containing user access data: passwords, allowed hosts, etc.
My server installation doesn't seem to have this.

How do I give myself permission to create new tables, given that there is no mysql database in the server?


texts: What are all these databases with funny names?

texts: I help support SQL Server installation that hosts SharePoint and various related services. I never really looked at the database side of things until today when I noticed a whole bunch of databases with what look like GUIDs appended onto the database name. Can someone explain what these are? I tried to google for it but I didn't know what to google for and nothing came up from obvious google searches.


texts: Get The Lowest Date if The other Column is Null

texts:
I have the following

CREATE TABLE employees (employee_id int, from_date date, to_date date);

INSERT INTO employees(Employee_ID,From_Date,To_Date)
VALUES
    (1    ,       '1998-01-02'   ,  NULL),
    (1    ,       '2000-01-01'   ,  NULL),
    (1    ,       '2015-01-01'   ,  '2018-01-01'),
    (2    ,       '2005-01-01'   ,  '2006-01-01'),
    (2    ,       '2007-01-01'   ,  '2007-05-05'),
    (3    ,       '2002-02-02'   ,  '2004-02-02'),
    (3    ,       '2010-01-01'   ,  '2015-01-01'),
    (4    ,       '2001-01-01'   ,  '2005-03-03'),
    (4    ,       '2003-03-03'   ,  '2004-04-04');

I want to select all the records with the lowest From_Date only if the record has a To_Date and the time duration between the From_Date and To_Date of that record is less than three years. The result should look like below:

Employee_ID     From_Date         To_Date
    2          2005-01-01       2006-01-01
    3          2002-02-02       2004-02-02

In other words if any employee is missing the To_Date in their lowest From_Date record and if the record with lowest From_Date has a To_Date but the time_duration is more than 3 years, they shouldn't be considered. As it can be seen in above result table the employee with ID 1 is not considered because the lowest From_Date record has not To_Date and the Employee with ID 4 is not considered because the time duration between From_Date and To_Date of the lowest record is more than three years.


texts: Select from one table where range is held in columns belonging to another table

texts:
I am trying to write a SELECT statement in Oracle SQL where I have a TABLE A with a "START" column and an "END" column which holds division numbers.

I want TABLE B to only extract those division numbers that are between columns "START" and "END" in table A

E.g

TABLE_A

ID | START_DIVISION | END_DIVISION
---+----------------+--------------
1  | 01             | 03
2  | 06             | 08
3  | 09             | 09

TABLE_B

DivisionNo | DivisionName
-----------+----------------------------
01         | London
02         | East Midlands
03         | West Midlands
04         | Yorkshire
05         | Lancashire
06         | Scotland
07         | South Wales
08         | North Wales
09         | Rep of Ireland

What I am after is something like this:

SELECT * FROM TABLE_B
WHERE DivisionNo BETWEEN TABLE_A.START_DIVISION and TABLE_A.END_DIVISION

I'm assuming a JOIN statement may do this but I'm not quite sure how to do this in SQL.

Any help on this would be greatly appreciated.


texts: Can I connect mysql client to a mariadb server?

texts:
I am new to databases, so I might have done something very simple wrong.
I cannot connect to my Mariadb server via my mysql client. Is this possible at all?
cmd> mysql -h 192.168.0.17 -u root -P 3306

gives me an error:

ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0

On my host machine, I have mysql with version
cmd> mysql -V
mysql  Ver 14.14 Distrib 5.6.10, for Win64 (x86_64)

and on my VM, I have an ubuntu server
$ mysql -V
mysql  Ver 15.1 Distrib 10.3.23-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

and my config on VM:
$ cat /etc/mysql/my.cnf.dpkg-new | head -n 20 | tail -n 7

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

Ping is OK:
cmd> ping 192.168.0.17

Pinging 192.168.0.17 with 32 bytes of data:
Reply from 192.168.0.17: bytes=32 time<1ms TTL=64
Reply from 192.168.0.17: bytes=32 time<1ms TTL=64
Reply from 192.168.0.17: bytes=32 time<1ms TTL=64
Reply from 192.168.0.17: bytes=32 time<1ms TTL=64

Ping statistics for 192.168.0.17:
    Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
    Minimum = 0ms, Maximum = 0ms, Average = 0ms


texts: how to add muliple rows with insert in order

texts:
I have table that have the following columns:

  id, state_id, name

the id is auto_increment

now I want to insert into it multiple rows at once as following:

insert into tbl (name, state_id)
         Values ('b',4121),
                ('a',4121);

what I want is that those values get added in order that correspond to the name so for example the first row will have id 1 and name a and second row will have id 2 and name b.

how to do this?


texts: Taking a mysqldump from a specific point in time

texts:
Is there a way I can take mysql dump from a specific point in time until the latest?

Let me explain the need of such requirement, maybe someone could suggest a better option:

I took a mysqldump (DUMP) from a database AA which I want to import into three new databases A, B and C. At the same I would want to setup a master-slave replication with A being the master and B, C the slave. I started importing the DUMP a week back on A, B but forgot to do the same on C as the server C was not ready at that point of time. The import has ran successfully on A and B (But it took about a week to run) and I just started the import on C. Due to the time constraints I need to put atleast one of these three Databases in service, preferably the master A. So what I thought was, I could put A into service and then once the import is complete on C, I can then freeze A so that no other application writes on A and take mysqldump of the data which was written for the past week and import them on B and C. At this point of time all the three servers (A, B and C) are in the same state and now I can set up the replication.

Is there a way I can take mysqldump just for a specific time?


texts: Is it Possible that a variable name changes in a stored procedure after a restore?

texts:
I often restore my Testing database from Production db, but this time I noticed something odd:
we have a stored procedure called (USP_AddTS), one of its variable names have slightly a different name in both databases (which I capitalized in example below):

USE [ProductionDB]
GO
ALTER PROCEDURE [dbo.USP_AddTS]
 (  @tsNum varchar(75),
@tsVersion varchar(75),
@tsTitle varchar(450),
@systemID tinyint,
@RELEASEDATE smalldatetime,
…………
……
)
AS
INSERT into TransmittalSheet(TransmittalNumber,TransmittalVersion,TransmittalTitle,TransmittalReleaseDate,……………..

values (@tsNum,@tsVersion,@tsTitle,@releasedate,….

2)  USE [TestingDB]
GO
ALTER PROCEDURE [dbo.USP_AddTS]
(   @tsNum varchar(75),
@tsVersion varchar(75),
@tsTitle varchar(450),
@systemID tinyint,
@TSRELEASEDATE smalldatetime,
…………
……
)
AS
INSERT into TransmittalSheet(TransmittalNumber,TransmittalVersion,TransmittalTitle,TransmittalReleaseDate,……………..

values (@tsNum,@tsVersion,@tsTitle,@TSreleasedate,….

We recently added a new column named (TransmittalReleaseDate) to the table (TransmittalSheet), so we updated the (USP_AddTS) to include the new variable (@ReleaseDate) like I’m showing above , but for some reason after I restored my Testing DB from a recent  copy from ProductionDB, the variable name got updated to  @TSReleaseDate only in Testing DB, unlike the ProductionDB that shows it as @ReleaseDate, is this possible or am I mistaken?
There was another variable line from the declared list above that I commented out from the ProductionDB and I made sure it was saved, but from the TestingDB was not commented out and still available.

I will keep an eye when I refresh or restore again, as I may incorrectly chose the prior date’s backup copy to restore from, but I thought of asking here too.
I'm using SQL Server 2008R2, Thanks.


texts: how to structure availability relationships

texts:
I am building a recruitment database and need to structure 'current availability' data against candidates which can be one of three options - available now, a specific date (e.g. 5/3/2019) or a relative date (e.g. 4 weeks). When querying the database in my application the relative date will be calculated on the fly.

I am trying to work out the best way to store this data in a relational database. In my project I am using MySQL 5.7. I have tested and can see that storing this as JSON is possible e.g. the availability field in candidates table can be JSON with the following

{
    'immediate': true
}

or

{
    'relative': {
        'period': '4',
        'unit': 'Weeks'
    }
}

or

{
    'date': '2019-03-05'
}

the query can be something like this

select `candidates`.*,
CASE
    WHEN availability->>'$.immediate' = 'true' THEN date(now())
    WHEN availability->>'$.date' THEN date(availability->>'$.date')
    WHEN availability->>'$.relative.unit' = 'week' THEN date(DATE_ADD(now(), INTERVAL availability->>'$.relative.period' WEEK))
    WHEN availability->>'$.relative.unit' = 'month' THEN date(DATE_ADD(now(), INTERVAL availability->>'$.relative.period' MONTH))
END as available from `candidates`
order by `available` desc

Although this works I am adverse to using JSON in a relational database so wondering how the same result could be achieved in other more relational ways.


texts: Why does Veritas backup take longer through CMDEXEC than cmd?

texts:
"c:\...\dbbackex.exe" -f "C:\...\backup.bch" -p policy_name -s server_name -np

The above command, when run from command line takes something like 30sec to finish for a small db or transaction logs.  When I run the same command in a job step using CMDEXEC step type it takes right around 10 minutes.

In NetBackups I don't see the job in the status view until that last 30 seconds when the job is actually doing something, but I DO see a process kick off in the task mgr.

I've already talked to our NetBackups admin.  He showed me his log.  It clearly indicates the task is only taking the 30sec that it always does, so the slowdown is definitely local to the sql server.

I've already tried using cmd-xpshell in a tsql statement. Same results.
To be clear: the job does finish.  I don't get access denied errors or anything like that, it just takes much much longer.

Edit for future googlers

I talked with our NetBackups lead and she said she had seen it before but was never able to ping it down.  For now we are in research mode, so nothing super helpful, but at least I'm not crazy!


texts:
I have two person type: Users and Clients.
Users can login the panel but clients can't login the panel.
My current database: (but this design not good for me. I need to connect these tables to the Persons table.)
My database:
You can see these tables storing same data like: firstName, lastName... but the difference is that the customer does not have a password.

How can I connect these tables to Persons table.

Users can login with email and password.
Client can't login but have email for contact.

I would be happy, If you answer it with a database diagram.


texts: How to pass Oracle Wallet auth to sh script?

texts:
I am running an Oracle DB update script that requires password. I have Oracle wallet installed. How to pass Oracle wallet to sh script so that I don't have to enter password when running the script?

I have my sqlnet.ora

sqlnet.expire_time=60
sqlnet.inbound_connect_timeout=300
sqlnet.allowed_logon_version_server=10
sqlnet.allowed_logon_version_client=10

WALLET_LOCATION =
(SOURCE =
   (METHOD = FILE)
      (METHOD_DATA =
          (DIRECTORY = /oracle/app/oracle/product/base19/19/network/admin/wallet)
   )
)

SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = TRUE

Credentials are correctly installed:
    $ mkstore -wrl "/oracle/app/oracle/product/base19/19/network/admin/wallet" -listCredential

Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:
List credential (index: connect_string username)
1: SID USER PASSWORD

I am running a script update_sql.sh:

#!/bin/bash
sqlplus cobrball @release.sql

release.sql:

spool release.log;
exit;


texts: How to create a Table in Oracle which has a variable of type number?

texts:
Suppose I want to store the social security numbers with a prefix letter 'S' (eg. S123456789) or a driving license number D (eg. D12345).

Is there any function or method to prefix it? Or patters?
I just need a hint.


texts: How to check if there are no time gaps in PostgreSQL?

texts:
Schema:

CREATE TABLE "expenses_commissionrule" (
    "id" serial NOT NULL PRIMARY KEY,
    "country" varchar(2) NOT NULL,
    "created" timestamp with time zone NOT NULL,
    "modified" timestamp with time zone NOT NULL,
    "activity" tsrange NOT NULL
);

Description:

I'd like to create an application to manage commission calculations. Every rule has activity period. Each country has independent set of rules.

Constraints:

First. To avoid ambiguity these activity periods should not overlap. I did it with the following constraint:

ALTER TABLE expenses_commissionrule
ADD CONSTRAINT non_overlapping_activity
EXCLUDE USING GIST (country WITH =, activity WITH &&);

Second. In any point of time there should be only one commission rule, so, there should be no gaps between intervals in the table. In other words sum of all intervals should be -INF:+INF.

Question:
How can I add the second constraint?
Use case:
We have a rule with infinite period. I want to switch to a new rule, which should start from the next month. In this case I'd like to set current rule end period to the end of the current month and add a new rule in a single action.

Update:
In the future I'd like to add the following behaviour:

An ability to specify a rule only for specific user (via nullable foreign key "user_id");
Every user could have his own rule for each country. If user has no rule - "global" rules will be used for commission calculations, so it is like a fallback;
These "user" rules could have any period of activity - (-inf: inf) and concrete intervals as well. It is different from "global" rules - gaps are OK here.
As "may be" feature - extra varchar field, which will have a type of situation, when particular rule could be applied to have more flexibility with the calculation process. This "partial" rules might have different intervals as well, gaps are OK.

In other words all previous constraints should be applied only to rows where user_id IS NULL. How can this be accomplished?

PostgreSQL version: 9.6.2


texts: last_value ignore null in left join with additional dimension?

texts:
This is just a silly example that I started to think about, but can't seem to come up with an elegant solution for. Assuming a Calendar table (I'll use Db2 syntax):
create table calendar
( dt date not null primary key
);

insert into calendar (dt)
with t (dt) as ( values cast('2020-01-01' as date)
                 union all
                 select dt + 1 day from t where dt < '2020-01-11')
select dt from t
;

and a table that holds the balance:
create table balance
( dt date not null
, amount int not null
,     primary key (dt)
);

insert into balance (dt, amount)
values ('2020-01-03',100) ,('2020-01-05', -50);

If we want to copy the last known balance, we can use LAST_VALUE and 'IGNORE NULLS' like:
select c.dt, last_value(b.amount, 'IGNORE NULLS') over (order by c.dt)
from calendar c
left join balance b
    on c.dt = b.dt;

However, if we add a dimension, say cid (customer_id), it is no longer obvious what last_value means. Normally we would partition by cid, but cid is lost due to the left join:
create table balance1
( cid int not null
, dt date not null
, amount int not null
, primary key (cid, dt)
);

insert into balance1 (cid, dt, amount)
values (1, '2020-01-03',100) ,(1, '2020-01-05', -50)
     , (2, '2020-01-04',75), (2, '2020-01-08',55), (2, '2020-01-10', -35);

select c.dt, last_value(b.amount, 'IGNORE NULLS') over (partition by ?
                                                        order by c.dt)
from calendar c
left join balance b
    on c.dt = b.dt;

The best I could come up with was to use a cartesian product between calendar and distinct customers from balances:
select cid, dt, last_value(amount, 'IGNORE NULLS') over (partition by cid order by dt)
from (
  select cid, dt, amount from balance1
  union
  select distinct b.cid, c.dt, null
  from balance1 b
  cross join calendar c
  where not exists (
    select 1 from balance1
    where dt = c.dt and cid = b.cid
  )
) t
order by dt, cid
;

Not that pretty, and I'm looking for a more elegant solution. I used Db2 syntax above and in this Fiddle but it's the principle I'm after so any vendor syntax will do.


texts:
Say you want to query a database to discover the types of trigger it contains. One way to do this is to use the OBJECTPROPERTY function on all trigger objects in the database.

Sometimes the OBJECTPROPERTY function produces a confusing result. Its output seems to depend on the database context.

The following example query returns a row for each of the sysmail triggers in msdb:

SELECT
  object_id,
  name,
  OBJECTPROPERTY(object_id, 'ExecIsInsertTrigger') AS IsInsertTrigger,
  OBJECTPROPERTY(object_id, 'ExecIsUpdateTrigger') AS IsUpdateTrigger,
  OBJECTPROPERTY(object_id, 'ExecIsDeleteTrigger') AS IsDeleteTrigger
FROM msdb.sys.objects
WHERE
  [type] = 'TR' AND
  name LIKE 'trig_sysmail_%';
GO

The intent is to find out what DML action will fire each trigger. For example, the IsInsertTrigger column contains a 1 if the trigger is defined as AFTER INSERT, and 0 otherwise.

When I execute the query in the context of msdb, the result set contains a 0 or a 1 in each of the computed columns. It looks like this:

object_id   name                         IsInsertTrigger IsUpdateTrigger IsDeleteTrigger
----------- ---------------------------- --------------- --------------- ---------------
713105631   trig_sysmail_profile         0               1               0
745105745   trig_sysmail_account         0               1               0
761105802   trig_sysmail_profileaccount  0               1               0
777105859   trig_sysmail_profile_delete  0               0               1
793105916   trig_sysmail_servertype      0               1               0
809105973   trig_sysmail_server          0               1               0
825106030   trig_sysmail_configuration   0               1               0
841106087   trig_sysmail_mailitems       0               1               0
857106144   trig_sysmail_attachments     0               1               0
873106201   trig_sysmail_log             0               1               0

When I execute the query in the context of master, the result set contains NULL in each of the computed columns. It looks like this:

object_id   name                         IsInsertTrigger IsUpdateTrigger IsDeleteTrigger
----------- ---------------------------- --------------- --------------- ---------------
713105631   trig_sysmail_profile         NULL            NULL            NULL
745105745   trig_sysmail_account         NULL            NULL            NULL
761105802   trig_sysmail_profileaccount  NULL            NULL            NULL
777105859   trig_sysmail_profile_delete  NULL            NULL            NULL
793105916   trig_sysmail_servertype      NULL            NULL            NULL
809105973   trig_sysmail_server          NULL            NULL            NULL
825106030   trig_sysmail_configuration   NULL            NULL            NULL
841106087   trig_sysmail_mailitems       NULL            NULL            NULL
857106144   trig_sysmail_attachments     NULL            NULL            NULL
873106201   trig_sysmail_log             NULL            NULL            NULL

MSDN notes that the OBJECTPROPERTY function returns NULL when:

the property name is not valid.
the object id is not valid.
id is an unsupported object type for the specified property.
the caller does not have permission to view the object's metadata.

I can rule out reasons 1 and 3 because the query returns the correct result in the context of msdb.

At first I thought it might be a cross-database permissions issue (reason 4), but I am sysadmin on the server.

That leaves reason 2, which leaves me with these questions:

Is the object id invalid in a cross-database query?

Which database's OBJECTPROPERTY function is being called?


texts: DDL for PostgreSQL for DB2 tables (migration)

texts:
How would I create the DDL for PostgreSQL for the following DB2 tables?
It should be a one-to-one migration, so I would like to change only as little as possible at the schema (I have difficulties with the BLOB/CLOB datatypes and the default values).

CREATE TABLE table1
(
   field_1  DECIMAL(15)   NOT NULL,
   field_2  ROWID         NOT NULL WITH DEFAULT,
   field_3  DECIMAL(15)   NOT NULL WITH DEFAULT,
   field_4  BLOB          NOT NULL WITH DEFAULT,
   field_5  TIMESTAMP     NOT NULL WITH DEFAULT,
   field_6  INTEGER       NOT NULL WITH DEFAULT,
   field_7  VARCHAR(64)   NOT NULL WITH DEFAULT,
   field_8  SMALLINT      NOT NULL WITH DEFAULT,
   field_9  CHAR(60)      NOT NULL WITH DEFAULT,
   field_10 DATE          NOT NULL WITH DEFAULT,
   field_11 DECIMAL(12,3) NOT NULL WITH DEFAULT,
   field_12 TIME          NOT NULL WITH DEFAULT,
   field_13 BIGINT        NOT NULL WITH DEFAULT
);

Second table:

CREATE TABLE table2
(
   field_1  DECIMAL(15)     NOT NULL,
   field_2  ROWID           NOT NULL,
   field_3  VARCHAR(2500)   NOT NULL,
   field_4  CLOB
);


texts: When choosing which structural table design for one-to-many relations

texts:
I am facing a problem. I have to design a database schema table structure. Generally I have a small hierachical tree structure with one-to-many relations:
A
|-B1
|  |-C1
|  |-C2
|
|-B2
   |-C3
   |-C4

Now, from my point of view, I have two options to design this:

For A and B, I could create two tables, whereas table_a references table_a directly via a foreign key. I thinks, this could be the better approach because you'll need less join operations on queries.
CREATE TABLE table_a (
    id int PRIMARY KEY,
    some_specific_data text
);

CREATE TABLE table_b (
    id int PRIMARY KEY,
    some_other_specific_data text,
    id_parent int REFERENCES table_a(id)
);

For A and B I could create two independent tables and an additional relation table which connects both tables (as I would do for many-to-many relations). Here both tables are more independent, which, I believe, should be desirable in general.
CREATE TABLE table_a (
    id int PRIMARY KEY,
    some_specific_data text
);

CREATE TABLE table_b (
    id int PRIMARY KEY,
    some_other_specific_data text
);

CREATE TABLE a_to_b (
    id int PRIMARY KEY,
    id_a int REFERENCES table_a(id),
    id_b int REFERENCES table_b(id)
);

From my point of view, both are valid designs. However, I am not able to decide which one should be chosen. Are there any arguments for choosing either option 1 or option 2 or even a third option?
Edit:
To be more specific:
A and B are not of the same type. You can think of it as:

A is a house
B is a door
So, a house has many doors
a door is contained by one house


texts: SQL Server: How to get total number of backups done in a month?

texts:
I need to gather monthly statistics for my boss, on how many FULL backups were done in a month and which number Succeeded and which failed if any.

Is there a simple query that could give me this, based on SQL Server logs?

Thanks


texts:
I am trying to index my blogentries database for better performance but found an issue.

Here is the structure:

CREATE TABLE IF NOT EXISTS `blogentries` (
  `id_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `title_id` varchar(100) COLLATE latin1_german2_ci NOT NULL,
  `entry_id` varchar(5000) COLLATE latin1_german2_ci NOT NULL,
  `date_id` int(11) NOT NULL,
  PRIMARY KEY (`id_id`)
)
ENGINE=MyISAM
DEFAULT CHARSET=latin1
COLLATE=latin1_german2_ci
AUTO_INCREMENT=271;

A query like the following uses the index properly:

EXPLAIN SELECT id_id,title_id FROM blogentries ORDER by id_id DESC

+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type |    table    | type  | possible_keys |   key   | key_len | ref  | rows |  Extra      |
+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | blogentries | index | NULL          | PRIMARY |     114 | NULL |  126 | Using index |
+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+

However, when I add the entry_id into the SELECT query it uses the filesort

EXPLAIN SELECT id_id,title_id,entry_id FROM blogentries ORDER by id_id DESC

+----+-------------+-------------+------+---------------+------+---------+------+------+----------------+
| id | select_type |    table    | type | possible_keys | key  | key_len | ref  | rows |   Extra        |
+----+-------------+-------------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | blogentries | ALL  | NULL          | NULL | NULL    | NULL |  126 | Using filesort |
+----+-------------+-------------+------+---------------+------+---------+------+------+----------------+

I was wondering why this is happening and how I can avoid it? Is it due to the VarChar, and that should be changed to something else?

I am trying to have all my queries use the index as I'm running into high Handler_read_rnd and Handler_read_rnd_next values.

If you need any other info I can post it too.


texts: PostgreSQL - Enforcing unique constraint on date column parts

texts:
I'm trying to enforce a unique month and year combination on a table with a data column. e.g.

create table foo
(
    mydate date
);

I want to enforce that only one row per month and year is valid i.e.

insert into foo values ('2018-01-01'); -- valid
insert into foo values ('2018-01-15'); -- Wouldn't be valid as one row already exists for January 2018
insert into foo values ('2018-02-15'); -- valid

The day portion is irrelevant. The application should only ever insert the first day of the month but as long as there's only one row per month & year it doesn't matter.

In Oracle or SQL Server I would be able to use deterministic functions in a function based unique index so I could use the result of date_trunc('month',mydate) and it would enforce what I want, but that doesn't seem to be possible in PostgreSQL.

I also don't seem to be able to create virtual/computed/calculated fields so can't enforce it that way either.

How should I be enforcing this constraint?

Am I doing something really silly?


texts: Can a BRIN index be used instead of Table Partitioning in Postgres?

texts:
Given an append only table with a GUID and a timestamp (and a bunch of other columns) which can grow by ~50Mio entries / year. I want to keep the number of indices low and just use a normal (B-Tree) index on data which is queried by id. Additionally, data will be queried by the timestamp for analysis. And for this I wanted to partition the table on a monthly or yearly base.

But since my table is append only and the timestamp will be quite continuous (maybe not to a 100% but generally I won't deliberately post items from the past) shouldn't a BRIN index basically give me the same functionality as table partitioning without the trouble of manually creating sub tables and instead of triggers? (at least that's how partitioning was described on the documentation: https://www.postgresql.org/docs/current/static/ddl-partitioning.html )

Update

Maybe some additional context - I thought about partitioning it by year or month since that is the only predicable thing. In both cases I'll have to perform queries on this table which will not include any timestamp information so for those queries I'd still be using the btree indices and in case of a pratition postgresql wouldn't be able to infer from the query which smaller table (and index) could be used.


texts: SQL join multiple "id" columns from a single row to another table

texts:
Apologies if this has been asked but I've tried searching and haven't come across anything similar. I have a two tables in a MySQL database. Athletes and Teams

The athletes table contains the following columns:

athleteID
firstName
lastName

and the Teams table consists of:

teamID
athleteID_1
athleteID_2
athleteID_3
athleteID_4

Given a teamID, I would like to retrieve the firstName, lastName, and athleteID of all the athletes on the team.

Currently, I am doing five queries

SELECT athleteID_1, athleteID_2, athleteID_3, athleteID_4 FROM Teams WHERE teamID=3

Then using a for loop in PHP to run the following query four times (once for each athleteID:

SELECT firstName, lastName from Athletes where athleteID=$athlete_id_n

I would like to end up with a four arrays, each with two entries (firstName and lastName).


texts: Insert into DynamoDB when data has different attributes

texts:
Is there any way dynamodb can handle dynamic attributes? Depending on the request, I need to store different web query parameters in the same table.

What I want to do is like this, if I make a java hashmap with query parameter's key and value, and just put them into dynamodb, then the dynamodb automatically uses hashmap's key for attribute's name and hashmap's value for attribute's value.

Actually I'm using Scala for server side and I know that Scanamo library can do this in easy way by making case classes for every case but it's quite inefficient for me because I have to make every case class for every combination of parameters and I don't event predict what kind of parameters would come.

Is there any way to do this in scanamo or dynamodb except for using insert JSON format?


texts: How to design a parent-child relationship that includes child priority/order

texts:
I am trying to design a table with a string column in SQLite3, I believe this is necessary to connect the tables parent to child, which represent entity types that are involved in a connection with a one-to-many (1:M) cardinality ratio. Te relevant data should be ordered by priority, something like this:

"1|3|5|6"

...this example contains these order values:

1, 3, 5, 6

Is it a good design strategy to create other two tables, one containing the order of the child and the other containing the child connection with the parent.id?
Current considerations
Here's a picture to clarify the situation, illustrating the design options I have evaluated so far:

Is there a better way to represent this kind of scenario?


texts: Import folder with Multiple CSV's into SSMS w/ out SSIS

texts:
I am wondering what's the best and most effective way to import csv's stored in a local folder into DB w/ in SSMS.

Folder path- C:\Users\ngutierrez\Desktop\Open_Air_Tables

Folder contains about 30 csv files. Each time these csv's are updated I need the DB to reflect the changes.


texts: Replication stops with GTID_NEXT error after creation/drop of memory table in mysql5.6

texts:
We have recently upgraded to mysql5.6.25 from mysql5.5.x/mysql5.1.x on our mysql-cluster.
Below is a brief snapshot of our architecture.

Since we have upgraded and enabled gtid-mode we have been intermittently getting slave errors similar to :

Last_SQL_Error: Error 'When @@SESSION.GTID_NEXT is set to a GTID, you must explicitly set it to a different value after a COMMIT or ROLLBACK. Please check GTID_NEXT variable manual page for detailed explanation. Current @@SESSION.GTID_NEXT is 'd7e8990d-3a9e-11e5-8bc7-22000aa63d47:1466'.' on query. Default database: 'adplatform'. Query: 'create table X_new like X'

Our observations are as below..

These slave errors are resolved simply by restarting the slave.
Such errors are always with Create/Drop of tables which have Memory Storage Engine.
Errors on Complete-Slave(B) show up continuously at a fixed minute (39th) of the hour and have been repeating since we have upgraded, almost a week.
Errors on Complete-Slave as well as Partial slave are observed whenever its master is restarted.
Cluster-1 and Cluster-2 have centos machines and Cluster-3 have ubuntu-machines. Slaves on centos machines also fail with the same error whenever its master(C/D) is restarted, but slave on ubuntu machines do not fail!!.

We have temporarily been able to live with this issue by setting up an action-script on our monitoring system which fires on slave error alert on any machine.

A look into gtid_next section in replication-options doc of mysql tells following

  Prior to MySQL 5.6.20, when GTIDs were enabled but gtid_next was not
  AUTOMATIC, DROP TABLE did not work correctly when used on a
  combination of nontemporary tables with temporary tables, or of
  temporary tables using transactional storage engines with temporary
  tables using nontransactional storage engines. In MySQL 5.6.20 and
  later, DROP TABLE or DROP TEMPORARY TABLE fails with an explicit error
  when used with either of these combinations of tables. (Bug #17620053)

This seems related to my issue but still doesn't not explain my scenario.
Any hints/direction to solve the issue would be greatly appreciated...

EDIT :
I managed to find a similar recently reported bug in mysql(#77729), description of which is as follows :

https://bugs.mysql.com/bug.php?id=77729

  When you have table with Engine MEMORY working on replication master,
  mysqld injects "DELETE" statement in binary logs on first access query
  to this table. This insures consistency of data on replicating slaves.

  If replication is GTID ROW based, this inserted "DELETE" breaks
  replication.  Logged event is in STATEMENT format and do not generate
  correct SET GTID_NEXT statements in binary log.

Unfortunately, the status of this bug is marked as Can't Repeat...


texts: Modeling Optional Foreign Key from Composite Primary Key field (Oracle Data Modeler)

texts:
I am trying to add an optional foreign key in Oracle SQL Developer Data Modeler v4.1.0.873(Build 873), as per diagram below:

The issue is that I can only make such a relationship if it is mandatory (note solid black line - I need it to be dotted).  The 'Mandatory' checkbox cannot be unchecked, or where it can be, I get:

  MKT_DATA_BONDS__FK: FK cannot be optional because some columns are
  part of the PK

How do I show this relationship in the model?  (We don't want the BONDS table to contain all bonds).  I tried adding an index (on BOND_ID) but wasn't able to reference that either.