Is there an efficient way to match multiples headers that have the same set of details? Here is an example of what I'm asking:

Detail Table:

HeaderId | DetailId
    1          100
    1          101
    2          100
    2          101
    3          101
    3          102
    3          103

I'm looking for a query strategy that will get me all header ids with the same set of detail ids for each.

So in the example, I would want HeaderId 1 and 2 to join together because they have the same two detail records, but 103 would not match because it has a third item in the set.

The strategy I'm taking so far is to use the STUFF technique to create a comma separate string of detail values, checksum that string, then join on the checksum result. It's seems to be working, but I'm not sure how to optimize it. Over a set of about 7000 headers, it returns in about 6-7 seconds.

Here is the query:

with Details as
(
    select distinct t2.HeaderId,
    checksum(stuff((
        select
            ',' + convert(varchar(15), t2.DetailId)
        from
            DetailTable t2
        where
            t2.HeaderId = t2.HeaderId
        for xml path('')
    ),1,1,'')) as ChkSum
from
      DetailTable t1
)
select
    *
from
    Details t1
        join Details t2
            on t2.ChkSum = t1.ChkSum
            and t2.HeaderId <> t1.HeaderId -- To avoid matching the same record

So - is this the right approach? And if it is, how can I optimize? Query plan doesn't have anything jumping out at me. The most weight is given to a table spool. Also, I'm trying to make this a function or proc if that helps.

Edit: I began researching relational division, and I think that is relevant here, but perhaps not in the context I am thinking. To give this more context, here is the business case I am trying to solve.

I have a set of Promotions that can have any number of UPCs in them. I'm trying to find promotions that have exactly the same set of UPCs in them. A lot of the solutions I'm seeing rely on using count(*). So - just some context for anyone looking at this. Thanks!

Here is a way using PIVOT and T-SQL that can work IF you have < 255 unique DetailIDs.  I ran into limitation on CONCAT function (2012+) after writing the thing and stress testing it.  It runs pretty well, <5 seconds on 20k headers over 40k rows, with 254 unique detail keys and a lot of matching.  If your set can fit that limitation, might be worth a look.

DECLARE @sql varchar(MAX)
DECLARE @d varchar(MAX)
SET @d = stuff((
        SELECT ',' + QUOTENAME(DetailId)
        FROM (SELECT DetailId FROM DetailTable GROUP BY DetailId) d
        for xml path('')
    ),1,1,'')
DECLARE @tbl TABLE (H int, D varchar(254))
INSERT INTO @tbl
EXEC(
'SELECT HeaderId,CONCAT('+@d+') Details --'+@d+'
FROM
(
    SELECT HeaderId, DetailId, 1 o
    FROM DetailTable
) as s
PIVOT
(
    COUNT(o)
    FOR DetailId IN ('+@d+')
) as pvt'
--Possible subquery and JOIN?
)

SELECT t1.H, t2.H H2  --matches
FROM @tbl t1 JOIN @tbl t2 ON t1.D = t2.D AND t1.H < t2.H

You should be able to get around the CONCAT 254 limitation by replacing the select with just @d, wrapping the PIVOT into a subquery, and stuffing out another @dj for a JOIN.


How to speed up query with anti-joins I have a query with 2 anti-joins (UserEmails = 1M+ rows and Subscriptions = <100k rows), 2 conditions, and a sort. I've created an index for the 2 conditions + sort, which sped up the query by 50%. Both anti-joins have indices. However, the query is too slow (4 seconds on production).

Here is the query:

SELECT
    "Users"."firstName",
    "Users"."lastName",
    "Users"."email",
    "Users"."id"
FROM
    "Users"
WHERE
    NOT EXISTS (
        SELECT
            1
        FROM
            "UserEmails"
        WHERE
            "UserEmails"."userId" = "Users". ID
    )
AND NOT EXISTS (
    SELECT
        1
    FROM
        "Subscriptions"
    WHERE
        "Subscriptions"."userId" = "Users". ID
)
AND "isEmailVerified" = TRUE
AND "emailUnsubscribeDate" IS NULL
ORDER BY
    "Users"."createdAt" DESC
LIMIT 100

Here is the explain:

Limit  (cost=1.28..177.77 rows=100 width=49) (actual time=6171.121..6171.850 rows=100 loops=1)
  ->  Nested Loop Anti Join  (cost=1.28..4665810.76 rows=2643614 width=49) (actual time=6171.119..6171.807 rows=100 loops=1)
        ->  Nested Loop Anti Join  (cost=0.86..3470216.17 rows=2707688 width=49) (actual time=0.809..6062.152 rows=28607 loops=1)
              ->  Index Scan using users_email_subscribers_idx on "Users"  (cost=0.43..1844686.50 rows=3312999 width=49) (actual time=0.055..2342.793 rows=1186607 loops=1)
              ->  Index Only Scan using "UserEmails_userId_emailId_key" on "UserEmails"  (cost=0.43..0.49 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1186607)
                    Index Cond: ("userId" = "Users".id)
                    Heap Fetches: 1153034
        ->  Index Only Scan using "Subscriptions_userId_type_key" on "Subscriptions"  (cost=0.42..0.44 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=28607)
              Index Cond: ("userId" = "Users".id)
              Heap Fetches: 28507
Planning time: 2.346 ms
Execution time: 6171.963 ms

And here is the index that improved the speed by 50%:

CREATE INDEX  "users_email_subscribers_idx" ON "public"."Users" USING btree("createdAt" DESC) WHERE "isEmailVerified" = TRUE AND "emailUnsubscribeDate" IS NULL;

EDIT:
I should also mention that the users_email_subscribers_idx is showing an Index Scan and not Index Only Scan likely because the index is being updated regularly.

Your best bet is probably to solve this at the application level.  This looks like a query you run as part of a data-cleaning exercise.  If that is so, why do you care if it takes 6 seconds to run, and why do you restrict it to 100 rows rather than reading all of them in one shot?  Perhaps you could use a materialized view or some other caching mechanism.  If you reject that option, read on for some "second best" options.

  I should also mention that the users_email_subscribers_idx is showing an Index Scan and not Index Only Scan likely because the index is being updated regularly.

That isn't why. You need columns out of the Users table that are not contained in the index, such as firstName and id. If you created an index with all those column at the end of the column list, you would get an index only scan. That might make the query 20% faster, but isn't going to make it 99% faster.

               Heap Fetches: 1153034

You need to vacuum UserEmails more aggressively.  Again, it won't be a 99% improvement, but it should help some.  Autovacuum doesn't do a good job at keeping tables sufficiently vacuumed to optimize index-only scans.  You can do manual vacuums.  Or you can try to force autovacuum to do a better job by lowering the per-table setting of "autovacuum_vacuum_scale_factor" to zero, and then setting the per table "autovacuum_vacuum_threshold" to be in control of the vacuuming.  If the table is updated randomly throughout the table, I'd set "autovacuum_vacuum_threshold" to about 1/20 of the number of blocks in the table.

How does the query perform if you experimentally set enable_nestedloop to off?  This will probably give you hash anti joins, and if your version is new enough you might get parallel versions of them.


Dynamic Oracle Pivot_In_Clause I'm kinda stuck. I want to do a user-role-relationship pivot table and my query so far looks like this:

WITH PIVOT_DATA AS (
     SELECT *
     FROM
     (
         SELECT USERNAME, GRANTED_ROLE
         FROM DBA_USERS@DB_LINK U LEFT OUTER JOIN DBA_ROLE_PRIVS@DB_LINK R
         ON U.USERNAME = R.GRANTEE
      )
)
SELECT *
FROM PIVOT_DATA
PIVOT
(
    COUNT(GRANTED_ROLE)
    FOR GRANTED_ROLE
    IN('CONNECT') -- Just an example
)
ORDER BY USERNAME ASC;

It works really fine and does the job, but I don't want to write to write any role I want to search for in the pivot_in_clause, because we got like tons of them and I don't want to check every time if there are any changes.

So is there a way to write a SELECT in the pivot_in_clause? I tried it myself:

[...]
PIVOT
(
    COUNT(GRANTED_ROLE)
    FOR GRANTED_ROLE
    IN( SELECT ROLE FROM DBA_ROLES@DB_LINK )
)
[...]

But it always gives me an ORA-00936: "missing expression" in line 1 of the whole query and I don't know why. Can't there be a SELECT in the pivot_in_clause or am I doing it wrong?

This may or may not help you, but with PIVOT XML instead of the simple PIVOT you can do what you want.

  Adding the XML keyword to the PIVOT operator allows us to convert the
  generated pivot results to XML format. It also makes the PIVOT a
  little more flexible, allowing us to replace the hard coded IN clause
  with a subquery, or the ANY wildcard.

(source)

This will return obviously an XML, this is why it may not help you...


How to automate the changes of parameters in ssis package run by a job? I have a job that runs a package with specific parameters as you can see on the picture below:

I want to set up those parameters (the 2 dates in yellow) to 1900-01-01 instead of 2019-02-27.

I need to automate this as I have a few of those packages to update.

How can I get this done via scripting?

The way I have been doing this is first I get the job_id and the command of the job by using the following query:

select @@servername -- this should be on the same server as the job
exec  msdb.dbo.sp_help_job @job_name = 'Daily DW CA'

that is to get the job_id and the step_id that has the command you want to update

you can also get that info like this:

note that this could be automated as well, but on this situation it was not necessary.

select * from
msdb.dbo.sysjobs
where name ='Daily DW CA'

then you use the @job_id and step_id in the script below.
the script below will change the dates from 2019-02-27 to 1900-01-01

set nocount on

declare @job_id uniqueidentifier = '90D3F4A2-1AC7-4741-BCCA-9A7B64A69B20'
declare @step_id int = 1
declare @command NVARCHAR(MAX)
declare @sql NVARCHAR(MAX)

--a quick select just to check
    SELECT step_id,
           step_name,
           subsystem,
           command,
           output_file_name,
           proxy_id
    FROM msdb.dbo.sysjobsteps
    WHERE (job_id = @job_id)
      AND ((@step_id IS NULL) OR (step_id = @step_id))
    ORDER BY job_id, step_id

--REPLACE(string, old_string, new_string)

    SELECT @COMMAND = replace(command,'2019-02-27','1900-01-01')
    FROM msdb.dbo.sysjobsteps
    WHERE (job_id = @job_id)
      AND ((@step_id IS NULL) OR (step_id = @step_id))
    ORDER BY job_id, step_id

print @command

SET @SQL =
'
EXEC msdb.dbo.sp_update_jobstep
@job_id=N''' +  '90d3f4a2-1ac7-4741-bcca-9a7b64a69b20' + ''',' + '
@step_id=1 , ' +
'@command=N''' + @command + '' + char(39)

print @sql

exec sp_executesql @sql

You can check if all is ok by having a look at the following command:

exec  msdb.dbo.sp_help_job @job_name = 'Daily DW CA'

you can see the desired values in the command shown below:


Do operations on indexed views happen in parallel? When I'm inserting into a table that has a view with a clustered index, the estimated execution plan appears to do the table insert and the view's clustered index insert operations in parallel.

Is this true?
Why do the %s not add up to 100%?

Below is somewhat of a contrived example.

/*
DROP INDEX [IX__AView] ON [dbo].[_AView]
DROP VIEW _AView
DROP TABLE _A
*/

CREATE TABLE _A (Name VARCHAR(10) NOT NULL)
GO

CREATE VIEW _AView WITH SCHEMABINDING AS
SELECT Name FROM [dbo]._A
GO

CREATE UNIQUE CLUSTERED INDEX [IX__AView] ON [dbo].[_AView] ([Name] ASC)
GO

/*
--run the estimated execution on this
INSERT INTO _A (Name) values ('cheese')
*/

It appears to do it in sequence in your plan. Not in parallel. The insert happens into the heap. The inserted rows are inserted into the eager spool which is a blocking operator. When all rows are inserted into the base table the rows are inserted from the spool to the view.

As for the percent issue it appears that the spool confuses SSMS. Despite showing up twice in the plan (with the same NodeId) its cost should only be counted once but it seems as though its cost is completely disregarded when calculating the overall estimated subtree cost for the plan and then it displays the operator percentages as a proportion of this incorrect total.

+---------------------+-----------+------------+--------------------------+
|                     |           | % of total | % of total without spool |
+---------------------+-----------+------------+--------------------------+
| Table Insert        | 0.0100022 | 39.84%     | 50.00%                   |
| Spool               | 0.0051012 | 20.32%     | 25.50%                   |
| CX Insert           | 0.010001  | 39.83%     | 49.99%                   |
| Sequence            | 0.000002  | 0.01%      | 0.01%                    |
| Total               | 0.0251064 |            |                          |
| Total without spool | 0.0200052 |            |                          |
+---------------------+-----------+------------+--------------------------+

SQL Sentry Plan Explorer correctly handles the issue.


Validate and Verify Data After SAN Migration We are preparing to migrate to a new SAN and I have been tasked with verifying/validating data after migrating a server.

Other than the DBCC commands that would tell me about data integrity, and comparing against a restored backup on a different server (on the old SAN), what would you recommend to do in order to V&V the migration?

Thanks

ps - I will have instances that range from MSSQL 2000-2008

I'd compare the data using, say, Red Gate SQL Data Compare in addition to DBCC.
Comparing all data in all tables row by row means checking every populated page.

However, for larger databases this will a long time, so you can run aggregates over columns (say AVG or CHECKSUM_AGG). I use this to quickly compare results but it would scale and force data to be read.


controlling postgresql log generation My disk space fills up daily with postgresql logs. One optimzation solution I'm pursuing is that instead of turning off logs, I should overwrite the original log file (i.e. rotate?) once it hits a certain size (say 200MB).

How do I enable such a setting in postgresql.conf? I'm aware that a better solution would be to backup all logs, but for now this is the one I want to pursue.

Reduce your log levels; for example, turn off log_transaction and set log_statement to something above all. Use pg_stat_statements instead of relying on full statement logging. Use Munin/Cacti/collectd/similar to collect system stats rather than relying on tools like pgbadger for log parsing.

Use logrotate if you're on a Linux system and using a system PostgreSQL install. It knows how to compress then age out logs. See /etc/logrotate.d/ .

PostgreSQL's built-in log rotation, as you have observed, is somewhat limited and really expects you'll be archiving and deleting them via a cronjob or similar. But you can do just that and use log_rotation_size.

You can also log to syslog. This gives you severity filtering, etc with a decent syslog like rsyslogd. You can make it keep ERROR level logs for ages, but quickly discard INFO and older, for example, by working alongside logrotate. You can even log to a remote syslog server with abundant disk space if you want to do long term archival.

There are commercial log ingestion products you can use to feed your logs in for archival and analysis too.


Convert an interaction matrix into a ternary relation I have what I'm calling a sparse "interaction matrix" of data in MS Excel that I wish to convert into a "ternary relation" (table) in Access.

The spreadsheet data are in this pattern:

Name   Act-1  Act-2  Act-3  ... Act-n
name-1         week
name-2                week
name-3  week                     week
  :                         week
name-m         week   week

There are n activities as Excel headings, m names in the first column of each data row, and zero or more specific weeks entered wherever a person (name) did an activity.
If the n were small and fixed, I could keep the relation as a matrix, but n can get quite large and the matrix will be very sparse.

Thus the desired relational table structure (I'll call it Assignment) is like this:

Assignment: Name, Activity, Week

Is there an easy tool in either Excel or Access for facilitating this data conversion?

A psuedocode algorithm would be

for each Name (row)
    for each Activity (column)
        if Week, add Assignment: Name, Activity, Week

While I've coded in VBA (years ago), I've never used it in conjunction with Excel.
I'm leaning towards using Python and CSV files. Maybe there's a good SQL technique (even if non-Access-specific)? Or is it something to do with "pivoting" (I never understood what that was about)?

Do you have any experience you'd like to share with this sort of conversion?

My solution is to save the spreadsheet as a tab-delimited "matrix" file, run the following "data structure conversion" program, then import the resulting tab-delimited "relation" file into Access.

The Python 3.4 code:

import csv
source_path = 'matrix.txt'
target_path = 'relation.txt'
source_deli = '\t'
target_deli = '\t'
target_head = ("Name", "Activity", "Week")
fin = open (source_path, 'r', newline='')
fout = open (target_path, 'w', newline='')
reader = csv.DictReader (fin, delimiter=source_deli)
writer = csv.writer (fout, delimiter=target_deli)
writer.writerow (target_head)
for row in reader:
    for key in row:
        if key != "Name" and row[key]:
            target_row = (row["Name"], key, row[key])
            writer.writerow (target_row)
fin.close()
fout.close()


Performance when using truncated VARCHAR as index in MySQL According to the answer of choice of this question: https://stackoverflow.com/questions/3489041/mysqlerror-specified-key-was-too-long-max-key-length-is-1000-bytes

  CREATE INDEX example_idx ON YOUR_TABLE(your_column(50))
  Assuming that your_column is VARCHAR(100), the index in the example above will only be on the first 50 characters. Searching for data beyond the 50th character will not be able to use the index.

I don't think so. I think when query with a string longer than 50 chars, MySQL will first use the first 50 chars and index to get a match list (which generally will be a very short list), then search in that list without index.

Is it true that MySQL won't use index at all when the query string is longer than 50 chars?

You are only partially right. MySQL query optimization engine will still try to use that index, as long as:

Query has exact match (a = 'b', a <> 'b', IN clause etc. - not "LIKE" clause),
Storage engine is InnoDB, XtraDB (Percona), or possibly some other fork of InnoDB.
Server is recent enough (at least 5.0 or 5.1 - I'm not sure now, but in 4.x varchar indexes worked a bit differently).


Open pgAdmin4 server on a Mac I had pgAdmin4-3.6 installed on my Mac. Worked as it should. And there was created a shortcut at installation to open the pgAdmin server.

Installing version pgAdmin4-4.6 there was no shortcut created to start the pgAdmin4 server. The old shortcut opens the old pgAdmin4.

open -a pgAdmin\ 4

also opens the old pgAdmin4-3.6

http://127.0.0.1:49206/browser/# and the "elephant meny" only works if the server is started. It starts when reinstalling from the dmg package.

How do I create a shortcut to start the new pgAdmin4-4.6 server on a Mac?
Or redirect the old pgAdmin4.app to the new pgAdmin4-4.6?

Do you have different browser processes running? I haven't tested this but try to close all the open browser windows. pgAdmin could cause some troubles regarding to system default browser https://stackoverflow.com/a/51780450/6618610

Did you try to restart the pgadmin service ?
https://stackoverflow.com/a/45519198/6618610

Restarting postgres would be even better.

I had an issue with pgAdmin4 so I restarted everything. That didn't go through seamlessly neither:

~ % pg_ctl -D /usr/local/var/postgres status
pg_ctl: server is running (PID: 1238)
/usr/local/Cellar/postgresql/12.1/bin/postgres "-D" "/usr/local/var/postgres"
~ % pg_ctl -D /usr/local/var/postgres restart
waiting for server to shut down............................................................... failed
pg_ctl: server does not shut down
~ % pg_ctl -D /usr/local/var/postgres stop
waiting for server to shut down............................................................... failed
pg_ctl: server does not shut down

I had a brew install so I used:

~ % brew services stop postgresql

After that I used pg_ctl to have the control over auto-start:

~ % pg_ctl -D /usr/local/var/postgres start

See the comments section https://stackoverflow.com/a/24251522/6618610

Or another approach, certainly better than killing processes:
https://stackoverflow.com/a/56611776/6618610

Your postgres service should be back running. Now retry again to start pgAdmin from the launchpad. Don't forget to check on Slonik, because it might appear up there and you won't even know : )

Copy the server URL from there or get it in the pgAdmin address file located in home folder. Type

~ % cat .pgAdmin4.###.addr

  ### a random numeric value use TAB for autocomplete

Now start your favourite browser and paste the link in the bar.

Also read those references:
pgAdmin 4 version 3 fails to open a second time
https://stackoverflow.com/questions/45671327/correct-way-to-start-stop-postgres-database-pg-ctl-or-service-postgres
https://stackoverflow.com/questions/7975556/how-to-start-postgresql-server-on-mac-os-x#23628638


SSAS MD dimension joins to same table twice (but processing fails as attribute has 2 names for same ID) My cube has a product dimension, and each product has an "availability" status attribute. In the database, SellableItemAvailabilityId in DimSellableItemVersion (which contains products) joins to Id in SellableItemAvailability (which contains the statuses). See the image below for the tables used in the dimension.

Each product also has a parent product. These are not the same as the products mentioned above (this is not an SSAS parent-child relationship) - think of them as ways to group related products together (e.g. a product style that comes in a number of colours & sizes).

Each parent product also has an "availability" status attribute. In the database, SellableItemAvailabilityId in SellableItemParent (which contains parent products) joins to Id in SellableItemAvailability.

Because of the foreign keys between the tables, the DSV automatically makes the relationships as seen in the image below.

I defined the Product Availability attribute (i.e. the availability of products) with:

Key=DimSellableItemVersion.SellableItemAvailabilityId
Name=SellableItemAvailability.StatusName

However processing fails due to the attribute having more than 1 name for the same key. I guess this might be due to products where the parent product has a different availability to the products inside it. If the relationship between SellableItemParent and SellableItemAvailability is deleted from the DSV, processing succeeds.

I want to keep the relationship between SellableItemParent and SellableItemAvailability so I can add a "parent product availability" attribute to the dimension. What do I need to do to make this work?

You have a product dimension using all 3 tables. The dimension key is DimSellableItemVersion.SellableItemId and you can use SellableItemAvailability.Id two times as an attribute, once for the product and once for the parent-product. But SSAS does not know which is which or when you want to use it only once, which on you mean. Do you want to go left or right along your foreign-key-circle.

When you want both SellableItemAvailability instances then I would use the data source view to create a second copy of the table SellableItemAvailability. Then you can break the circle and the dimension can work.


Difference between date value change Link: Query (duration) different between data value change

I am trying to write a query that would get me the duration of a status in a table. This query needs to work in SQL Server 2008.

Say I have the following table:

CREATE TABLE RateTable(
    [Key] [int],
    [Value] varchar(10) NULL,
    [RecordDate] [DateTime] NULL
    )
GO

INSERT INTO [RateTable] VALUES (1,'abc','2012-01-01');
INSERT INTO [RateTable] VALUES (1,'abc','2012-01-02');
INSERT INTO [RateTable] VALUES (1,'xyz','2012-01-02');
INSERT INTO [RateTable] VALUES (1,'xyz','2012-01-05');
INSERT INTO [RateTable] VALUES (1,'abc','2012-01-05');
INSERT INTO [RateTable] VALUES (1,'abc','2012-01-08');
INSERT INTO [RateTable] VALUES (2,'abc','2012-01-10');
INSERT INTO [RateTable] VALUES (2,'abc','2012-01-11');
INSERT INTO [RateTable] VALUES (2,'abc','2012-01-12');
GO

So it contains the rows that follow:

Key Value RecordDate
--- ----- ----------
1   abc   2012-01-01
1   abc   2012-01-02
1   xyz   2012-01-02
1   xyz   2012-01-05
1   abc   2012-01-05
1   abc   2012-01-08
2   abc   2012-01-10
2   abc   2012-01-11
2   abc   2012-01-12

I would like to have output (the result set) for the value 'abc' only, as shown below:

Value StartDate  EndDate
----- ---------- ----------
1     2012-01-01 2012-01-02
1     2012-01-05 2012-01-08
2     2012-01-10 2012-01-12

Yet another grouping or window. It is a bit longer due SQL Server 2008 doesn't support LAG functions and it must be simulated.

-- add a row number to simulate a lag function
with a as
(
    select [key], value, recorddate, row_number() over (partition by [key],value order by [key], value, recorddate) rn
    from ratetable
    where value = 'abc'
)
  -- set reset points
  , b as
  (
      select t1.[key], t1.value, t1.recorddate, t2.recorddate rd2,
             case when t2.recorddate is null
                       or t1.[key] <> t2.[key]
                       or datediff (day, t2.recorddate, t1.recorddate) > 1 then 1 end is_reset
      from a t1
      left join a t2
      on t2.[key] = t1.[key]
      and t2.rn = t1.rn -1

  )
    -- generate groups
    , c as
    (
        select [key], [value], recorddate, count(is_reset) over (order by recorddate) grp
        from   b
    )
    -- max/min of each group
    select [Key], min(recorddate) as StartDate, max(recorddate) as EndDate
    from c
    group by [Key], grp;

GO

Key | StartDate           | EndDate
--: | :------------------ | :------------------
  1 | 01/01/2012 00:00:00 | 02/01/2012 00:00:00
  1 | 05/01/2012 00:00:00 | 05/01/2012 00:00:00
  1 | 08/01/2012 00:00:00 | 08/01/2012 00:00:00
  2 | 10/01/2012 00:00:00 | 12/01/2012 00:00:00

Warning: Null value is eliminated by an aggregate or other SET operation.


How do I deal with items that I need to repeat monthly? I have a table where users will enter transactions on a weekly basis, these transactions will either be single items or will be recurring i.e. rent would be recurring and should be the same for every month. How should I store this recurring item?

We thought just add in 12 new entries so it atleast covers the year (The users will want to look a few months into the future) but then the problem is that if the cost changes, we have to update all following entries

Our other option was to create another table which would store the key of the original with the dates or day of the month that it needs to repeat on?

Is there a better solution than these?

You shouldn't try to do too many things with one data store.

If you have a table for transactions, then just keep transactions in that table.  Don't mix in scheduled future transactions with current transactions.  Actual (historical) transactions will have different attributes (columns) than future, scheduled transactions.

Keep the recurring/scheduled transactions (like rent) in a separate table.  When the rent is paid, create a new record in the (actual) transaction table.

The scheduled transaction table could have a totally different structure.  Instead of keeping a record for each expected transaction, you could keep one record with a date range and recurrence fields.  This would save having to update multiple records when the rent price changes, for example.  Consider something like this for your scheduled/recurring transaction table:

Scheduled_Transaction:=

CustomerID
StartDate
EndDate
Recurrence (monthly, weekly, every two weeks, - whatever you need)
Amount


What is the most efficient way to update a product table with 1 million rows in MySQL I need to update a product table which currently has 1 million rows. For each row, I need to perform an expensive http operation and then update 1 field in the row based on a returned header.

I've read that it might be faster to insert into a new table than updating an existing one. Is that true and, if so, why?

What alterable properties of the product table e.g. Indexes might slow down updates? There are a number of indexes on the table (i did not create these).

MySQL version is 5.6.12 and the engine is MyISAM

What alterable properties of the product table e.g. Indexes might slow down
  updates?

What do you care? It is totally irrelevant. See, by your own words:

  For each row, I need to perform an expensive http operation and then update 1
  field in the row based on a returned header.

So, all the MySql performance wont matter - unless you run that on a mobile phone - because you are going to be controleld in speed by "an expenive HTTP operation".

Make a queue (can bedb based), request the http stuff one by one, run updates. THe updates wont take long. Get into multi threaded programming and use some hundred threads - each running in parallel - to get some http speed.

But the update performance in MySQL will not matter unless you want to wait untill you got all data, which may work or not - normally I would say it is not an optimal way.


Grant an AD Group permission to edit all MSSQL Agent jobs We have a team of people, we all log in MSSQL Server from SSMS using our Active Directory accounts, by Windows Authentication.
Our AD accounts are added to an AD group, which is used to grant all permissions on MSSQL. For simplicity, we don't grant any permissions directly to our accounts, and for security we don't use local MSSQL accounts.
We're struggling to setup permission so that everybody (on the AD group) is able to list, view, edit, start, stop, enable, disable, etc MSSQL Agent jobs, and list and set proxies on CmdExec steps. It shouldn't matter who had created each job, we should all have full access to all jobs.
I had googled about it, read answers here, made a question myself. I read the SQL Server Agent Fixed Database Roles doc and noticed that SQLAgentOperatorRole is the most privileged role and still doesn't have full access to all jobs.
Is there any way to have this kind of permission set for an AD group?
If not, my next path will be to create a local MSSQL account, deny it permission to R/W on all databases, and then everybody will know its password and all jobs be created and managed from it. But I really wish to not do such thing.

Only users in the sysadmin role can edit jobs they aren't the owner of via the SSMS object explorer so if you add all of the users to an AD group that is in the sysadmin server role, it will work, but you probably don't want to do that.


How to setup an ssh connection to a postgres 12 db using dbeaver I am currently in the process of trying to connect to my local postgres 12 db. In the past (9.3/9.6) you were able to edit the .pg_hba file to modify which connections were allowed but that file doesn't no exist in 12 (it has been over a year since I have used postgres). I am unsure what the equivalent is or if there is a different method that should be used to connect to the db.

I am struggling to find any tutorials for how to accomplish this. Every one that I have seen so far just assumes that you already have something ready to connect and simply provides the necessary command needed to connect. I do have an ssh key on my local computer which I assume should be sufficient to use to connect.

dbeaver requires the following:

host/ip - localhost
post - defaults to 22
user name - postgres (default)
authentication method - Public Key
Private Key - path to my private key in .ssh

What here am I missing to connect to my postgres db?

Side note - I am unable to connect using ident either since dbeaver always requests a password even if there isn't one

The default name is pg_hba.conf, not .pg_hba.  The file still exists in v12.

It is possible to pick any name you want for this file by changing the setting hba_file in postgresql.conf or from the start-up command line.  But if someone has done that, it is a difference in how your server was configured, not a inherent difference between versions of PostgreSQL.


Sudden performance decrease in SQL Server I've got a problem with a SQL Server 2008 R2 instance. Last week the performance suddenly broke down. All inserts are very very slow. It takes 4-5 times longer than before. On the hardware we found no defects. We had the same problem a month ago, but after one week the problem disappeared as fast as it came.

I'm not an expert on these things but I noted that the performance on all databases on the instance is poor. CPU and Disks are not at their limits, so it shouldn't be a hardware performance issue.

I'll took a look at the cached pages count with this query:

select
       count(*)as cached_pages_count,
       obj.name as objectname,
       ind.name as indexname,
       obj.index_id as indexid
from sys.dm_os_buffer_descriptors as bd
    inner join
    (
        select       object_id as objectid,
                           object_name(object_id) as name,
                           index_id,allocation_unit_id
        from sys.allocation_units as au
            inner join sys.partitions as p
                on au.container_id = p.hobt_id
                    and (au.type = 1 or au.type = 3)
        union all
        select       object_id as objectid,
                           object_name(object_id) as name,
                           index_id,allocation_unit_id
        from sys.allocation_units as au
            inner join sys.partitions as p
                on au.container_id = p.partition_id
                    and au.type = 2
    ) as obj
        on bd.allocation_unit_id = obj.allocation_unit_id
left outer join sys.indexes ind
  on  obj.objectid = ind.object_id
 and  obj.index_id = ind.index_id
where bd.database_id = db_id()
  and bd.page_type in ('data_page', 'index_page')
group by obj.name, ind.name, obj.index_id
order by cached_pages_count desc

Here I found very high values, but I don't know if it's bad or not. The count is from 201.236 to 1 on the different tables an indexes. Could this be an issue of the poor performance?

Another thing I noted is a very high number of log writes while inserting data. With SELECT INTO ... it's much faster.

Can anyone give me a hint, or can tell me what this cached_pages_count exactly means?

edit:

I also mentioned high waititme during the inserts (up to 300.000 ms) Waittype is CXPACKET and wait ressource is e_waitPortOpen and e_waitPipeNewRow. The high values occur on e_waitPortOpen.

kind regards
Thomas

Sounds like your statistics are getting out of date, then eventually SQL's auto update statistics will kick in and update the stats, proper execution plans are being generated, then everything starts running faster again.

If you have installed Service Pack 1 for SQL Server 2008 R2 then you can turn on Trace Flag 2371 which will change the algorithm which is used from the static 20%+500 to a changing value depending on the number of rows in the table.

If you don't have service pack 1 installed then I'd recommend setting up a job that runs UPDATE STATISTICS for all your tables weekly or nightly.


MySQL grant privileges has no effect I've executed the following queries:

GRANT USAGE ON my_db.* TO my_user@127.0.0.1 IDENTIFIED BY 'pass';
GRANT ALL ON my_db.* TO my_user@127.0.0.1;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_db.* TO my_user@127.0.0.1;
FLUSH PRIVILEGES;

But they don't show up.

SHOW GRANTS FOR my_user@127.0.0.1;

results in

GRANT USAGE ON my_db.* TO my_user@127.0.0.1 IDENTIFIED BY '*123...';
GRANT ALL PRIVILEGES ON my_db.* TO my_user@127.0.0.1;

Aley,

GRANT ALL ON my_db.* TO my_user@127.0.0.1;

is a superset of:

GRANT SELECT, INSERT, UPDATE, DELETE ON my_db.* TO my_user@127.0.0.1;

So the last one is superfluous. You must understand that SHOW GRANTS do not show every grant query that you executed, and that in some specific cases, it may not show all grants for a user. That is something that can be very confusing in MySQL.

Also, another tip is that you do not need FLUSH PRIVILEGES; if you only use GRANT/CREATE/DROP keywords, only if you modify directly the mysql.* tables.


How to batch multiple insert statements for database dump with MySQL? I have a MySQL database with some large tables. In particular, the largest table (by far) currently has ~11,000,000 rows. According to the a query I ran, the table clocks in at around 1.7GB, but it's MyISAM and apparently that number is unreliable (but it's a big table, anyway). The database is backed up daily using mysqldump.

Currently, restoring the database from this dump file takes ~10 hours, and is only going to get worse. I'm using the --skip-extended-insert option because restoration will fail with a single multi-valued insert statement for all rows in the table. My understanding is that max_allowed_packet can be extended up to 1GB, but that's still not enough.

I thought that this would be a common enough problem and might have a generally accepted solution but I'm having trouble finding anything. So, my questions are:

Does mysqldump have an option that allows multi-valued inserts to be batched for each table e.g. instead of creating a single insert, create multiple inserts for n rows
If not, is there some other utility/alternative to accomplish this, or do I have to roll my own backup script that queries for table rows in batches of n using the limit clause and manually create the insert statements myself?

MySQL v5.5.40
Tables are a mixture of MyISAM and InnoDB.
The backup takes place locally.

Your issue may involve the configuration settings for either OS or MySQL
HISTORICAL EXAMPLES
Years ago when I worked at a website firm, I used --skip-extended-insert to load a DB Server that had 2GB RAM. That made a whole world of sense to me because the server I dumped it from had 64GB RAM and the client wanted the data on a smaller machine.
At my current placement of employment, an outside vendor dumped data from SQL Server and produced a mysqldump compatible dumpfile. The problem with that dump file was each table had a single insert with all the rows. One table had 3.5 million. They could not be loaded into in-house VM will less than 8GB RAM configured. Shockingly, the solution to that was to scp the file to may laptop, load it into MySQL 5.5.37 on my laptop, mysqldump from it, send the new mysqldump back to the Linux CentOS VM, and load the mysqldump.
BTW, the 3.5 million row table loaded as a single insert into my Windows 7 laptop in 4.5 hours without changing any MySQL settings. To be honest with you, I did believe that "shot in the dark" would work because I did not reconfigure mysqldump on the laptop to work with the dump. My guess is that it probably loaded because the undo log in ibdata1 just grew on disk and held everything.
You should not have to live to skip-extended-insert if you dump on reload on the same machine.
SUGGESTIONS
While I was typing my answer, I saw you inject the following into the question

According to the a query I ran, the table clocks in at around 1.7GB, but it's MyISAM and apparently that number is unreliable (but it's a big table, anyway).

SUGGESTION #1
If that one MyISAM table has 1.7GB and has the 11 million rows, my suggestion would be for you to increase the size of the bulk insert buffer. The bulk insert buffer is for MyISAM usage only and supports extended inserts. Set bulk_insert_buffer_size to 256M.
Go to my.cnf or my.ini and add this
bulk_insert_buffer_size = 256M

Then, login to MySQL and run
mysql> SET GLOBAL bulk_insert_buffer_size = 1024 * 1024 * 256;

SUGGESTION #2
As for your InnoDB, you need to use a bigger log buffer. Set it to 64M by adding this to my.cnf
innodb_log_buffer_size = 64M

You must restart MySQL to use that new value.
SUGGESTION #3
You may also need bigger InnoDB Logs by increasing innodb_log_file_size. This require manual intervention as follows:
STEP 01: Run this command as root
mysql> SET GLOBAL innodb_fast_shutdown = 0;

STEP 02: Shutdown mysql

Linux : service mysql stop
Windows (as Administrator) : net stop mysql

STEP 03: Add this option to my.cnf or my.ini
innodb_log_file_size = 1G

STEP 04: In the OS, goto the folder where ib_logfile0 and ib_logfile1 are located and run
Linux
mv ib_logfile0 ib_logfile0.bak
mv ib_logfile1 ib_logfile1.bak

Windows
rename ib_logfile0 ib_logfile0.bak
rename ib_logfile1 ib_logfile1.bak

STEP 05: Start mysql (the log files get recreated so start will take an extra 1-2 min)

Linux : service mysql start
Windows (as Administrator) : net start mysql

After making one or more of these suggested changes, try dumping and reloading. It goes go much smoother. Start with just SUGGESTION #1 and try it. Try the other other two to see if it improves more.
SUMMARY
You need these options for your target MySQL DB Server in your my.cnf or my.ini
bulk_insert_buffer_size = 256M
innodb_log_buffer_size = 64M
innodb_log_file_size = 1G
max_allowed_packet = 1G

GIVE IT A TRY !!!


Oracle 11g Enterprise downgrade to Standard One I'm checking possibilty of downgrade Oracle 11g from Enterpise Edition to Standard One edition. I've cross-checked edition comparison with

select name from dba_feature_usage_statistics where currently_used='TRUE'

Query result from dba_feature_usage_statistics:

Job Scheduler
Oracle Java Virtual Machine (user)
Oracle Java Virtual Machine (system)
Automatic Memory Tuning
Oracle Utility Metadata API
Segment Advisor
Automatic SQL Tuning Advisor
Object
Materialized Views (User)
Audit Options
Automatic Maintenance - Optimizer Statistics Gathering
Automatic Maintenance - Space Advisor
Automatic Maintenance - SQL Tuning Advisor
Automatic Segment Space Management (system)
Automatic Segment Space Management (user)
Automatic Undo Management
Client Identifier
Character Set
Locally Managed Tablespaces (system)
Locally Managed Tablespaces (user)
Parallel SQL Query Execution
Partitioning (system)
Recovery Area
Recovery Manager (RMAN)
RMAN - Disk Backup
Backup BASIC Compression
Logfile Multiplexing
Server Parameter File
Shared Server
Streams (user)
LOB
SecureFiles (system)

I think Parallel SQL Query Execution and Partitioning (system) are only in Enterprise Edition, but I'm not sure about rest, especially:

Automatic Memory Tuning
Segment Advisor
Automatic SQL Tuning Advisor
Automatic Maintenance - SQL Tuning Advisor
Recovery Area

Do you guys know which edition of Oracle is needed to use these features?

Ultimate source for such answers is Oracle Database Licensing Information.

Sadly to downgrade from Enterprise to Standard Edition you have to export all the data install Oracle Standard Edition and import data (Doc ID 139642.1).

From your list "Automatic SQL Tuning Advisor" is Enterprise Edition only feature. As a rule of thumb - features which needs AWR are Enterprise Edition only plus you need to buy Oracle Diagnostics Pack.


Cannot execute as the database principal because the principal “guest” does not exist Recently moved SQL Server from a desktop to a virtual machine and now facing the below error message while connecting to SQL Server using SQL Authentication.

  -2147205987:[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot execute as the database principal because the principal “guest” does not exist, this type of principal cannot be impersonated, or you do not have permission.

The user ID SQLADMIN is used to connect to SQL Server.

May I know the permissions that SQLADMIN should have to do a BULK_COPY operation along with normal functions?

Currently SQLADMIN is set with the PUBLIC and BULKADMIN permissions. For Database1 and Database2 (used in the project), SQLADMIN is given PUBLIC and db_OWNER permissions.

After these changes, I am still facing the same issue.

I have not needed to overcome this problem on a SQL Server except when I make a mistake with permissions.  I do not endorse the below, because I have not spent time on the problem.

However, for what it is worth, Symantec has an article on this issue using SQL Server 2012 and Windows 2012 at:

Cannot execute as the database principal because the principal “guest” does not exist

Their answer is based on how your SQL Server is configured.

A default instance running under Local System should run under NT Service\MSSQLServer, then NT Authority\System has db_datareader role to the “master” database.
A named instance running under Local System should run under NT Service\MSSQLServer or NT Service\MSSQL$ServerXInstanceX, then ONLY NT Authority\System has db_datareader role to the “master” database.
If running under Domain or Local user account the instance should run under Mydomain\MyUser, then Mydomain\MyUser has “db_datareader” role to the “master” database


Clustered Index improvemens for particular queries I found a table that looks like this:

 CREATE TABLE [dbo].Table1 (
  id INT primary key IDENTITY (1, 1),
  [idUser] INT  NOT NULL ,
  [Amount] INT NOT NULL ,
  [Attempts] INT NOT NULL ,
  [date] [datetime] NOT NULL ,
  [SUM_Amount] INT NOT NULL
 ) ON [PRIMARY]

This table is created and populated with aggregated data for a particular period by a job.

Particularities:

This table will hold up to a million rows
idUser is unique
sum_Amount is running total of previous rows of amount.

This table will last as is, no update or delete or insert operation.
Just this type of queries:

 select top (@n) * from table1
 order by [SUM_Amount] desc, [Attempts] desc

 select top (@n) * from table1
 where [SUM_Amount] >=@m order by [SUM_Amount] asc

I think it will improve preformance to change to a clustered index like this:

 CREATE TABLE [dbo].Table2 (
  id INT IDENTITY (1, 1),
  [idUser] INT  NOT NULL ,
  [Amount] INT NOT NULL ,
  [Attempts] INT NOT NULL ,
  [date] [datetime] NOT NULL ,
  [SUM_Amount] INT NOT NULL

 CONSTRAINT [PK_Nueva]
   PRIMARY KEY CLUSTERED ([SUM_Amount] desc, [Attempts] desc, id asc)

 ) ON [PRIMARY]

I read that using a no unique clustered index will add a 4 bytes hidden column (http://msdn.microsoft.com/en-us/library/ms190639(v=sql.90).aspx), so I decide to add Identity to cluster index (not sure if it is the right approach)

I want to ask (at the risk of sound ridiculous, but need to be sure):

How could it be improved?
Will I have an impact on disk size?
Should I rebuild the index once all data have been inserted?

EDIT:

About id, I think is there just as a bad habit. I'll kept it, not sure how previous job calculate running total (I've no access to it)

There are a lot of tables like this, like hundreds for each day(don't ask me why). That is why DBA team ask me to not create a new index because of size issues. That is why I thinking of rearrange table structure via clustered index. Also changing data types which exceeds normal ranges.

Yes, placing a clustered index like you suggest will give you excellent query performance for those two particular types of queries, but it may spell disaster for most other SELECT queries on that table. I would offer an alternative solution:

I would suggest adding a basic clustered index on a single, unique column like idUser:

ALTER TABLE dbo.Table1 ADD
    PRIMARY KEY CLUSTERED (idUser)
        WITH (FILLFACTOR=100);

... and then building a separate, non-clustered indexe specifically for your queries:

CREATE INDEX IX_Table1_SUM_Amount
    ON dbo.Table1 (SUM_Amount DESC, Attempts DESC)
    WITH (FILLFACTOR=100);

The clustered index (the primary key) will not add any space to your table - it's just another way of organizing the storage by presorting the rows in a binary tree, which will make access to the table a lot quicker.

The non-clustered index will take up some space, but it's comparably small: Both SUM_Amount and Attempts are integer values, so they're 4 bytes large each, meaning that your non-clustered index should end up at roughly 12 MB with a million rows (including the "clustering key", which is also an integer).

Your two example queries will use the non-clustered index to find the largest/smallest values, and this will generate a Key lookup operator in the execution plan, but because you're using TOP (@n), I recon you won't even notice the performance cost of that. If you absolutely need even better performance and want to eliminate the Key lookup, you could INCLUDE () all the relevant columns in the non-clustered index to form a so-called covering index, effectively making the index a sorted copy of the original table:

CREATE INDEX IX_Table1_SUM_Amount
    ON dbo.Table1 (SUM_Amount DESC, Attempts DESC)
    INCLUDE (id, idUser, Amount, date)
    WITH (FILLFACTOR=100);

There is no need to rebuild an index if the data hasn't changed, particularly if the entire table is populated in a single batch. If the table has been populated over time, however, you may see some fragmentation.


MongoDB replicaset - Delay in election I have local set up for 3 mongo DB instances with successful replica set. (1 Primary, 2 secondary).

If I terminate Primary instance, one of the secondaries becomes Primary but its taking around 10 seconds.

Is there a way I can reduce the time.

I have updated rs.config as below:

  config = rs.config()
  {
"_id" : "rs0",
"version" : 3,
"protocolVersion" : NumberLong(1),
"members" : [
    {
        "_id" : 0,
        "host" : "127.0.0.1:27020",
        "arbiterOnly" : false,
        "buildIndexes" : true,
        "hidden" : false,
        "priority" : 1,
        "tags" : {

        },
        "slaveDelay" : NumberLong(0),
        "votes" : 1
    },
    {
        "_id" : 1,
        "host" : "127.0.0.1:27021",
        "arbiterOnly" : false,
        "buildIndexes" : true,
        "hidden" : false,
        "priority" : 1,
        "tags" : {

        },
        "slaveDelay" : NumberLong(0),
        "votes" : 1
    },
    {
        "_id" : 2,
        "host" : "127.0.0.1:27022",
        "arbiterOnly" : false,
        "buildIndexes" : true,
        "hidden" : false,
        "priority" : 1,
        "tags" : {

        },
        "slaveDelay" : NumberLong(0),
        "votes" : 1
    }
],
"settings" : {
    "chainingAllowed" : true,
    "heartbeatIntervalMillis" : 1000,
    "heartbeatTimeoutSecs" : 3,
    "electionTimeoutMillis" : 10000,
    "catchUpTimeoutMillis" : 2000,
    "getLastErrorModes" : {

    },
    "getLastErrorDefaults" : {
        "w" : 1,
        "wtimeout" : 0
    },
    "replicaSetId" : ObjectId("5976e7bd5b7837e0fbba2871")
  }
}

EDIT

I have reinstalled MongoDB enterprise edition:

   MongoDB shell version: 3.2.15

Yes!
Check here.

You can adjust

settings.heartbeatTimeoutSecs
settings.electionTimeoutMillis
settings.catchUpTimeoutMillis
settings.heartbeatIntervalMillis


Stored Procedure Resultset Row Count and Resultset Size in Bytes/KBs Is there any way to get total row count and size of result-set generated by a stored procedure execution?

@@ROWCOUNT is a good option if its already added in each stored procedure but I just want something like all RPC calls with in specific time period, with Row count and Size (in Bytes or KBs) for each call without modifying any stored procedure.

The question is unanswerable as asked since stored procedures do not produce result sets. Only (certain) statements produce result sets. Executing procedures may produce none, one or more results sets depending on the statements contained and the branches executed.

For statements the row count will be tracked in sys.dm_exec_query_stats.

Result sets have no size (in bytes or KB), size is an attribute pertinent only to encoding and representation. A sensible question would be to ask how many bytes are transferred between the client and the server, and this can be tracked by transport performance counters.


SQL Server 2012 Login Failure As of this morning we have had thousands of failed login attempts to our SQL Server 2012 box.

Error :

  Login failed for user 'Domain\User'. Reason: Infrastructure error occurred.
  Check for previous errors. [CLIENT: 10.xxx.xxx.xxx]

  Error: 18456
  Severity: 14
  State: 1

I have checked all the logs, permissions, and scoured forums for help on this issue but to no avail.

  Server Login & Database User accounts are enabled
  Server Login is mapped to the database that it has access too
  Windows & SQL Server Authentication is applied
  SA account is enabled
  No orphaned users
  etc etc...

Any ideas ?

The box lost connection to the domain controller, hence the login issues


MySQL Server > 90% memory consumption I have a fairly large (16 vCPU / 47 GB of RAM) linode box which hosts a running MySQL Server. This box currently does nothing else (and we are not planning to install anything else on it for now).

The server is monitored by New Relic. Our warning policy places the server in RED (=danger) state when either : CPU > 80% or Memory > 90% or Disk IO > 90% or Disk Full > 90%.

The DB is fairly large (lots of data!) but low access rate (< 1 request per second). We are basically collecting lots of data, and we will do something out of it "someday". I am not complaining about the DB perfs. The CPU usage is flat around 1 or 2 %. No issue there either.

Should I still be worried about the memory warning or should I modify the server warning policy to "untrack" memory usage ? Or should I simply force mysql to use less memory (via a config somewhere).

Is there any relevant measure you think I should keep an eye on. As you guessed I have little to none dev ops / admin experience.

Well, having 10% free of big RAM is still a lot of space, but MySQL memory usage should be fairly predictable. You can check your config with http://www.mysqlcalculator.com/ or similar tool.

You don't want it to get to swap or OOM if swap is disabled, so tune it to use less than total RAM, maybe keep it under 90% for mysql and normal system usage. Then if it gets over 90, you actually know something is off (maybe some OS service memleak or similar stuff), but you still have few GB to delay real trouble.


How can I implement a viewed system for my website's posts? Here is my current structure:

// posts
+----+--------+----------+-----------+------------+
| id | title  | content  | author_id | date_time  |
+----+--------+----------+-----------+------------+
| 1  | title1 | content1 | 435       | 1468111492 |
| 2  | title2 | content2 | 657       | 1468113910 |
| 3  | title3 | content3 | 712       | 1468113791 |
+----+--------+----------+-----------+------------+

// viewed
+----+---------------+---------+------------+
| id | user_id_or_ip | post_id | date_tiem  |
+----+---------------+---------+------------+
| 1  | 324           | 1       | 1468111493 |
| 2  | 546           | 3       | 1468111661 |
| 3  | 135.54.12.1   | 1       | 1468111691 |
| 5  | 75            | 1       | 1468112342 |
| 6  | 56.26.32.1    | 2       | 1468113190 |
| 7  | 56.26.32.1    | 3       | 1468113194 |
| 5  | 75            | 2       | 1468112612 |
+----+---------------+---------+------------+

Here is my query:

SELECT p.*,
       (SELECT count(*) FROM viewed WHERE post_id = :id) AS total_viewed
 FROM posts p
WHERE id = :id

Currently I've faced with a huge date for viewed table. Well what's wrong with my table structure (or database design)? In other word how can I improve it?

A website like stackoverflow has almost 12 million posts. Each post has (on average) 500 viewed. So the number of viewed's rows should be:

12000000 * 500 = 6,000,000,000 rows

Hah :-) .. Honestly I cannot even read that number (btw that number will grow up per sec). Well how stackoverflow handles the number of viewed for each post? Will it always calculate count(*) from viewed per post showing?

What makes you think that there is a viewed table like yours on stackoverflow's database? Raw logs are very costly to maintain on a relational database, and on high-traffic websites, caching has a very important role, and it is almost impossible to conserve full logs of everything (they are summarized). For Wikipedia, for example, we maintain a complete different infrastructure for analytics (fed with Apache Kafka from Varnish and Mediawiki), and then an API is offered that can be called from Mediawiki itself: https://tools.wmflabs.org/pageviews/?project=en.wikipedia.org&platform=all-access&agent=user&range=latest-20&pages=Main_Page

If you had to implement viewed X times functionality within the datbase, you could denormalize the tables by maintaining an extra column as part of the main posts table:

// posts
+----+--------+----------+-----------+------------+------------+
| id | title  | content  | author_id | date_time  | view_count |
+----+--------+----------+-----------+------------+------------+
| 1  | title1 | content1 | 435       | 1468111492 | 3          |
| 2  | title2 | content2 | 657       | 1468113910 | 2          |
| 3  | title3 | content3 | 712       | 1468113791 | 2          |
+----+--------+----------+-----------+------------+------------+

For several reasons, in some cases a separate table may be preferred (e.g. to avoid making the main table hot; if there is a very normalized table such as post_properties; to save space if most posts have 0 views)- it all depends on external factors. In that case, you would have:

// posts_views
+---------+------------+
| post_id | view_count |
+---------+------------+
| 1       | 3          |
| 2       | 2          |
| 3       | 2          |
+---------+------------+

And then:

SELECT posts.*, post_views.view_count
FROM posts
LEFT JOIN post_views
ON posts.id = post_views.post_id
WHERE id = :id

(The LEFT JOIN is in case you have posts without view counts, if not, just use a regular JOIN)

In both cases, after a "view" is added, additionally (or alternatively) add 1 to the number of views in a SERIALIZED way.

Try to avoid logs on relational databases (specially MySQL). You can bring the summaries back after being analysed.


Beautify/reformat the SQL script button partially working I am making my way through Murach's MySQL, 2nd Edition and I've encountered a bit of a speed bump.

When writing scripts to create tables, etc., I've always been able to write scripts and with the cursor at the end, press the Beautify/reformat the SQL script button and they get cleaned up.

With the following code, it only gets partially cleaned up (just the SELECT statement). The following code does what it's supposed to, but the Beautify/reformat button doesn't clean up everything in the editor. Any suggestions?

use ap;

drop procedure if exists test;

delimiter //

create procedure test()
begin
declare first_invoice_due_date date;

SELECT
    MIN(invoice_due_date)
INTO first_invoice_due_date FROM
    invoices
WHERE
    invoice_total - payment_total - credit_total > 0;

if first_invoice_due_date < now() then
select 'Outstanding invoices overdue!';
elseif first_invoice_due_date = now() then
select 'Outstanding invoices are due today!';
else
select 'No invoices are overdue.';
end if;
end//

Bug:  NOW() is to the second.  You probably want CURDATE(), which is midnight this morning.  And would work nicely if first_invoice_due_date were declared DATE.

Avoid Cursors!  They are a speed bump.  Instead, learn how to write SQL statements that do everything all at once.

(As for beautifing, your code is prettier than most of the stuff on this forum!)


How to identify all WALs created during start_backup and stop_backup? I'm implementing incremental backups using filesystem-level snapshots. pg_start_backup() and pg_stop_backup() can be used to this end. Via these pair of functions or via the backup_label file, I can identify the "START WAL LOCATION" and "STOP WAL LOCATION" and associated files. The problem is, when these files are not the same and not strictly consecutive (ie, there are intervening files), how do I deterministically determine them? While it might be the case that the files are monotonically increasing, how can I be sure that is the case, and how can I be sure I've located all the intervening files? Can I be guaranteed the files will be written or changed in chronological order? Or in lexicographical order? (From what I've seen, neither is the case.)

After 3x reading the documentation, I can't get any clarity or resolution on this: it tells us that the backup process must include these files, but offers no indication on how to identify them! If I've overlooked a page in the documentation, I'd appreciate being it pointed out.

BTW: Please don't tell me to use pg_basebackup unless you want a kick in the face.

BTW2: I'm trying to make a complete backup set without doing a full backup. Barman stores the archive logs somewhere else, which is what I want to avoid. Even if I implemented barman (which I don't want to), I'd still need to know which files I need for the backup during its operation.

The file names are 24 hex digits long, broken into 3 fields of 8 digits each.  The first is the timeline, which you probably don't need to worry about here.  The other two are called the "segment" and the "file".  They are really a single counter, the "file" rolls over by incrementing the "segment" by one and going back to zero.

Oddly, only the last two digits of the "file" chunk are ever non-zero.  (This is apparently so that the offset into a logical 'segment' fits in a 32 bit uint, which was important at some point in the distant past)

In 9.1 and 9.2, the "file" skips FF.  So from FE, it rolls back over to 00 while incrementing the segment number.  In 9.3, it switches so that FF is used.  So the sequence for your version would go:

0000000100000000000000FD
0000000100000000000000FE
000000010000000100000000
000000010000000100000001
...
00000001000000BE000000F9
00000001000000BE000000FA
00000001000000BE000000FB
00000001000000BE000000FC
00000001000000BE000000FD
00000001000000BE000000FE
00000001000000BF00000000
00000001000000BF00000001
00000001000000BF00000002
00000001000000BF00000003
00000001000000BF00000004

Getting the files from pg_xlog is tricky, because those files get recycled (i.e. renamed to have the name of some future file-name, then overwritten once it gets around to needing that file).  This means that the timestamps on the files are all screwy.  A recycled log file will have the name of some future file, but the contents and timestamp of some past file.

If a file was recycled before you had a chance to copy it, then your backup has failed and you need to try again with a higher wal_keep_segments.

And of course if you copy the "current" file before it was finalized, it will be missing the changes that occurred after the point you copied it.

It is much easier to get them from the wal archive, because that doesn't have the recycling problem and because they don't show up at all until they are finalized.


"Merge" two rows in a Postgres table, with foreign keys I am keeping a database of books I've read, using the following two tables in PostgreSQL:

CREATE TABLE authors (
    id SERIAL PRIMARY KEY,
    name text
);

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title text,
    author_id integer REFERENCES authors(id) ON UPDATE CASCADE ON DELETE CASCADE,
    UNIQUE(title, author_id)
);

Now when going through my list of authors, I found the following two entries:

id | name
----------
 1 | Mark Twain
 2 | Samuel Clemens

What I'd like to do is delete the "Mark Twain" entry, and effectively update all books referencing "Mark Twain" to reference "Samuel Clemens". I know I could do this manually, but I want a solution that works, regardless of which tables are referencing the authors(id)

I thought about doing it like this (within a transaction):

Change Mark Twain id to 2, letting UPDATE CASCADE take care of changing the references.
Delete Mark Twain entry

But this runs into a few problems, mainly:

The first step creates a duplicate primary key
I'm not sure how to reference the right row to delete, once they both have the same ID!
The DELETE CASCADE worries me for the second step

There's also a subtler problem, that can be illustrated with a portion of my (poorly curated) books table:

id | title              | author_id
------------------------------------
 1 | "Huckleberry Finn" | 1
 2 | "Huckleberry Finn" | 2

Here, even if my two-step process succeeded, I would be violating the UNIQUE contstraint on books.

Is there a way to do this, and work around most/all of these issues? Using Postgres 9.4.

Assuming you just want to delete duplicates in books after merging duplicate authors.
BEGIN;
LOCK books, authors;

CREATE TEMP TABLE dupes ON COMMIT DROP AS (SELECT 2 AS dupe, 1 AS org);

DELETE FROM books b  -- delete duplicate books
USING  dupes d
WHERE  b.author_id = d.dupe
AND    EXISTS (
   SELECT 1
   FROM   books
   WHERE  title = b.title
   AND    author_id = d.org
   );

UPDATE books b  -- now we relink all remaining books
SET    author_id = d.org
FROM   dupes d
WHERE  b.author_id = d.dupe;

DELETE FROM authors a  -- now we can delete all dupes
USING  dupes d
WHERE  a.id = d.dupe;

COMMIT;

The temp table could hold many rows to remove many dupes at once.
Repeat the first two steps for every tables referencing authors.id. If there are many I would create and execute the statements dynamically ...
I lock the tables explicitly to avoid concurrent disturbances.
Automation
A basic function could look like this:
CREATE OR REPLACE FUNCTION f_remove_dupe(_tbl text, _col text, _dupe int, _org int)
  RETURNS void  AS
$func$
DECLARE
   _ftbl text;
   _fcol text;
BEGIN
   FOR _ftbl, _fcol IN
      -- table and column name behind all referencing FKs
      SELECT c.conrelid::regclass::text, f.attname
      FROM   pg_attribute  a
      JOIN   pg_constraint c ON a.attrelid = c.confrelid AND a.attnum = c.confkey[1]
      JOIN   pg_attribute  f ON f.attrelid = c.conrelid AND f.attnum = c.conkey[1]
      WHERE  a.attrelid = _tbl::regclass
      AND    a.attname  = _col
      AND    c.contype  = 'f'
   LOOP
      EXIT WHEN _ftbl IS NULL;  -- skip if not found
      EXECUTE format('
         UPDATE %1$s
         SET    %2$I = $2
         WHERE  %2$I = $1'
       , _ftbl, _fcol)
      USING _dupe, _org;
   END LOOP;

   EXECUTE format('
      DELETE FROM %I WHERE %I = $1'
    , _tbl, _col)
   USING _dupe;
END
$func$ LANGUAGE plpgsql;

Call:
SELECT f_remove_dupe('authors', 'id', 2, 1);

This simple version ...

... only works for a single dupe.
... ignores UNIQUE constraints in referencing tables.
... assumes all FK constraints only use the one column, ignoring multi-column FKs
... ignores possible interference from concurrent transactions.

Adapt to your requirements.
Related:

Find referenced field(s) of foreign key constraint
Can't execute dynamic DDL, argument is NULL
SQL injection in Postgres functions vs prepared queries


Continuously move data from server A to Server B while deleting the data in server A? I'm developing an ad server that is expected to handle ad impressions/billion clicks per day.

The most difficult challenge I am facing is moving data from one server to another.

Basically the flow is like this :

Multiple front facing load balancers distributes traffic (http load balancing) to several servers called the traffic handler node.
These traffic handler nodes job is to store the click logs in mysql table (data like geo , device, offer id, userid etc) and then redirect traffic to the offer landing page.
Every minute cron job runs in all traffic nodes that which transfers all the clicks logs to reporting server (server where all reports generation is done) in batches of 10000 rows per minute , and then delete the data after confirming that the data is successfully received by the reporting server. The reporting servers uses clickhouse database engine

I need to replace the mysql database engine from the traffic nodes as I'm facing a lot of issues with MySQL. Between the heavy inserts and then the heavy deletes it's getting slow. Plus, the data is being transferred via cron job so there is 2 minutes average delay.

I can't use clickhouse in these server as Yandex clickhouse do not support updates and deletes yet and the click logs are supposed to be updated many times (how many events happened on the visit)

I'm looking at kakfa but again I'm not sure how to achieve one way data transfer and then deletion of data.

Maybe my whole approach is wrong. I would be very grateful for any expert to guide in right direction.

I don't think that your approach is wrong, but I don't have much information. Your question is clear, but this space is limited compared to the complexity of your system.

Definitely you shouldn't consider using Clickhouse for OLTP. Not only because DELETE and UPDATE are not (yet) supported, but also because this database is designed to provide good performance for analytics. It lacks more or less everything is needed to optimize an OLTP workload.

Kafka is a good idea? Maybe. But you won't have transactions, for example.

I suggest to try to optimize your MySQL environment first. Some very generic points - sorry if they sound obvious to you, but of course I can't know your skills lever:

Tune your configuration for speed, as far as I understand you can give up some consistency.
Use READ UNCOMMITTED wherever there is not a risk of conflicts. The benefits of this isolation level are greatly underestimated.
Instead of running many DELETEs, consider partitioning tables by time and periodically drop the oldest partition. This is much faster.
Consider switching to a storage engine optimized for write-intensive workloads, namely MyRocks or TokuDB.
Consider using multiple slaves for scaling reads.
Consider using multiple masters for scaling writes.
These servers can even be distributed geografically, if needed.
Don't consider Galera or InnoDB Cluster, as writes don't really scale.

Hope these hints are useful.


firebird is very slow compared to sqlite I am using Firebird for past few months and today a customer complained about performance issue.

I checked into client's database and they had 167,77,216 rows in one table. So I made a clone of that table into another empty database and loaded with dummy records and tested with a simple query

select count(journal_id) from acc_journal

It took 14.28 seconds.

After that I made a sqlite database and loaded with this data and it took 2.216 seconds.

Is this Firebid's nature or I am missing something?

Firebird table structure:

CREATE TABLE ACC_JOURNAL (
  JOURNAL_ID       INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  ACCOUNT_HEAD_ID  INTEGER,
  REF_ID           INTEGER,
  REF_TYPE         VARCHAR(20),
  DESCRIPTION      VARCHAR(512),
  DEBIT            DECIMAL(18,4),
  DEBIT_EX         DECIMAL(18,4),
  CREDIT           DECIMAL(18,4),
  CREDIT_EX        DECIMAL(18,4),
  CURRENCY_ID      INTEGER,
  STATUS           VARCHAR(20),
  EX_RATE          DECIMAL(18,4),
  ENTRY_DATE       VARCHAR(20),
  ENTRY_TIME       VARCHAR(20),
  BRANCH_CODE      VARCHAR(20),
  LAST_SYNCED      VARCHAR(20),
  LAST_UPDATED     DECIMAL(18,4),
  ENTRY_DATE2      VARCHAR(20),
  /* Keys */
  PRIMARY KEY (JOURNAL_ID)
);

SQLite table structure:

CREATE TABLE ACC_JOURNAL (
    ACCOUNT_HEAD_ID INTEGER,
    REF_ID          INTEGER,
    REF_TYPE        VARCHAR (20),
    DESCRIPTION     VARCHAR (512),
    DEBIT           DECIMAL (18, 4),
    DEBIT_EX        DECIMAL (18, 4),
    CREDIT          DECIMAL (18, 4),
    CREDIT_EX       DECIMAL (18, 4),
    CURRENCY_ID     INTEGER,
    STATUS          VARCHAR (20),
    EX_RATE         DECIMAL (18, 4),
    ENTRY_DATE      VARCHAR (20),
    ENTRY_TIME      VARCHAR (20),
    BRANCH_CODE     VARCHAR (20),
    LAST_SYNCED     VARCHAR (20),
    LAST_UPDATED    DECIMAL (18, 4),
    ENTRY_DATE2     VARCHAR (20),
    JOURNAL_ID      INTEGER         PRIMARY KEY AUTOINCREMENT
);

Both files can be downloaded from following links:

Firebird DB
SQLite DB

Unfortunately, this is due to the fact that Firebird has to do a full table scan to get the count. I'm not familiar with how SQLite works, but a quick search seems to indicate that SQLite can use an index to get a count (eg primary key index for count(*) or an index on a column if using count(columnname), though I haven't verified if this is actually correct).

The reason Firebird needs to do a full table scan, is because Firebird is a MVCC (Multi-Version Concurrency Control) database, but record visibility information is not included in its indexes. So, to be able to know that a record is visible to your transaction and therefor should be included in the count, Firebird needs to read the actual record. And because the query is an unconditional count, using an index and then checking each record would be slower than just reading each record.

However, there are some things you can do to improve performance:

Use count(*) instead of count(journal_id).

Using count(*) will just count records, while count(journal_id) will count records where journal_id is not null. Firebird doesn't use the not null state of a primary key, so it checks if that column is not null for each record. On my system, changing to count(*) reduces the query time by around 25%.
Use a larger page size.

Your example database has a page size of 4096, increasing it to a page size of 16384 reduces the execution time your example query (with count(journal_id)) by around 20%, while the count(*) (compared to your original query on page size 4096) is reduced by more than 50%.

However, given the overall poor performance of an unconditional count, the primary advice would be to avoid having to use unconditional count.


How do you turn off the croak sound for Toad? How do you turn off this annoying feature? I don't want my DB manager to croak at me as loud as it can.

Issue: You are experiencing a noise / croak sound when TOAD starts up.

Solution: http://www.odipundits.com/2017/01/how-to-get-rid-of-croak-sound-at-toad.html

View in the top toolbar -> Toad Options from the list.
Click on Startup in the left pane after scrolling down a ways.
Uncheck the box Play Toad wave file and then click ok. Click apply instead if you wish to continue navigating through the pane.

The next time it starts up, it should not play that sound.


Normalizing: when to apply 2NF and 3NF I am taking college classes in relational databases for half a year now. As an assignment get to normalize data with 1NF, 2NF and 3NF.

I do not find any difficulties making my schema compatible with 1NF. After that I have not managed to find any examples where to apply 2NF and 3NF as well. It feels like I think a bit ahead when I do 1NF.

When I deliver the assignment, all I have done is 1NF to my understanding. And it still is being approved as valid design.

Is this a normal thing to have? At the moment I do not see the use of 2NF and 3NF when all the redundancy is taken away in 1NF already. Your thoughts?

I think that you will understand the needing of normalization thinking to the anomalies that happens when you try to delete, insert or update rows in your tables.

Think to a 1NF tables with students, exam and the teacher of the professor of the exam.

An anomaly that happens when you delete a record is that you will lose every information associated to It. For example if only a student took an exam and you delete the tuple Student-Exam-Professor you will lose every information about that exam and the professor (the information about the professor that teaches that exam is lost because it existed in your database only on that tuple). 1NF allow this anomaly.

An anomaly that happens when you try to insert a new tuple is that you have to know all data associated to that tuple. If you want to insert a new tuple, you have to know the professor that teaches it (for example if you have a Not null constraint). For example, a Not null constraint on Student can't allow you to insert a new exam with its professor if you have no students to associate it. 1NF allow this anomaly.

An anomaly that happens when you try to update a value of a tuple in your a table is that you have to update all copies of that value.  For example, if you update some data about the professor, the exam or the student changes you have to update every tuple that have that values or you'll have inconsistency in your DB.  1NF allow this anomaly.

Don't forget the redundancy of a 1NF table that repeats every time the professor associated to that exam even if you need it only one time.

If you change it to a 2NF table, you will have two tables, the students-exams one and the exams-professors one, and you will have no anomalies.

Another NF is the Boyce and Codd Normal Form, BCNF. This one sometimes doesn't preserve some FD.


postgres - pg_dump and pg_restore without roles I'm trying to restore a dump without having the appropriate roles on the receiving database.
As mentioned here but also here, you need to have the --no-owner as an option, either in pg_dump or pg_restore or both.
I've used the following command line to create my dump:
"C:\Program Files\PostgreSQL\9.3\bin\pg_dump.exe" --no-owner -Ft --dbname=postgresql://avo******:AV0******?@127.0.0.1:5432/BI_DB > K:\BI\backup\sort\bck_%timestamp%.tar

The restoring line is as follows:
"C:\Program Files\PostgreSQL\9.3\bin\pg_restore.exe" --host localhost --port 5432 --username "postgres" --dbname "BI_TEST2" --no-password  --no-owner --role=postgres --exit-on-error --verbose "D:\D\avo\backup\bck_04042017_1410.tar"

As you can see, both have the --no-owner option but eventually, I have the error below:

What terribly bugs me is the log below:
pg_restore: [programme d'archivage (db)] Erreur pendant le traitement de la TOC (« PROCESSING TOC ») :
pg_restore: [programme d'archivage (db)] Erreur à partir de l'entrée TOC 2633 ; 0 0 ACL adm avo******
pg_restore: [programme d'archivage (db)] could not execute query: ERREUR:  role « avo****** Â» does not exist

Why does it say it needs a role even if the --no-owner was specified?
Did I miss something?
I'm running on Postgres 9.3

The error occurs when pg_restore set the ACLs : you can use --no-acl to prevent GRANT commands.

With the -Ft option in pg_dump, you can skip roles and ACLs only in pg_restore. You can also edit the catalog with --list if you need more details.


Clustered index and Non clustered index This might be a silly question but i really want to know the internals.This is one of small table just to test as below

I have create a table as above and 'name' column is primary key and clustered index so it has sort all words in alphabetical order . Now when i create a non clustered index on column 'number' , the rows in 'name' column not anymore in alphabetical order as below

My question is, the name column should remain the rows in alphabetical order rite since it has clustered index and how come it changed after i add non clustered index on different column? i m sure there might be certain things i missed.Please let me know.Thank you

Order of your data returned is not guaranteed when you do not specify an order by.

Consider this example:

CREATE TABLE dbo.SmallTable([name] varchar(25) PRIMARY KEY NOT NULL,
                            Number int);

INSERT INTO dbo.SmallTable ([name],[Number]) VALUES ('compi',15 ), ('jack',5 ), ('malik',20 ), ('nana',10 );

If you were to just select without an order by:

SELECT [name],[Number]
FROM dbo.SmallTable;

The order this time is the same as your first example:

name    Number
compi   15
jack    5
malik   20
nana    10

But this is not guaranteed.

If you want the ordering to be guaranteed then you need to add an order by:

SELECT [name],[Number]
FROM dbo.SmallTable
ORDER BY [name] ASC;

What you are seeing when creating the nonclustered index on number is the query using this new NC index.

For example:

CREATE INDEX IX_Number
on dbo.SmallTable(Number);

Running the query again:

SELECT [name],[Number]
FROM dbo.SmallTable;

I get the same result as you:

name    Number
jack    5
nana    10
compi   15
malik   20

Because now sql server decides to use the IX_Number index to return the results:

Which is ordered by number asc.

To guarantee the ordering the query needs to be changed to:

SELECT [name],[Number]
FROM dbo.SmallTable;
ORDER BY [name] asc;

The ordering could even be different than the index order from the index that was used. This means that even if now the ordering is on the number or name column, it could be on any one of these columns or even none.

Always use an order by if the ordering is important.

More info on the subject can be found here, here, here and here


SET keyword in SQL Server I have been using SET keyword to assign the values to parameters declared in SQL Server something like,

Declare @StudentName Nvarchar(max)
SET @StudentName='ABC'

I'm not sure how to write this if they are more than 1 student name.

Say student names are being fetched from another Select Statement (Select student_name from student_tbl)

Will that be

SET @StudentName= 'Select student_name from student_tbl'

I'm not sure how SET works in case of multiple values.

Multiple values shouldn't be assigned to a scalar variable. It makes no sense

You need a table variable (or temporary table).

Declare @StudentName TABLE (sname Nvarchar(max));
INSERT @StudentName (sname) VALUES ('ABC'),('DEF'),('PQR'),('XYZ');

You can JOIN etc to this as you would a normal table. Or use it in an IN clause

WHERE
    X.SomeCol IN (SELECT sname FROM @StudentName)

Edit, after comments

INSERT @StudentName (sname)
SELECT ... -- the other query here


How should I model this data? I have a need to model the following requirement for a web application (PHP/MySQL5):

A user can compete in a distance-based challenge which consists of entering progress along a defined round-the-world route

A JS map displays an individual user’s progress in distance based on the
distance they have covered in the challenge relative to the total
target distance (a user updates distance covered periodically)
A Challenge can have many lat/long points through which the
challenge route travels
More than one user can participate in the same challenge

I have roughly sketched how I would do this but would appreciate advice on the DB design. Initially there will actually only be one pre-defined challenge as this is for a prototype but I would like to model the data in such a way that it can cope with future development e.g. Users challenging other users to a 'race;' i.e participating in the same challenge and displaying both their paths on the same map.

Tables:

challenges

id
title
total distance

challenge_coordinates

id
lat
long
challenge_id

challenges_users

user_id
challenge_id
distance_covered

I already have a user-management system in place with a users table.

What you've modelled is a snapshot of the user's total progress.

I think you might want to track the user progress a little differently.  Instead of letting them enter their distance covered directly, why not track their way-points?  If you make a kind of audit trail of where they went (lat/lon) and when (date) then you can easily calculate their distance covered using Vincenty or another numerical method.

By tracking the progress on a map (i.e. by lat/lon) by date you can have more interesting reporting of the data.  For example, what if two "contestants" want to race but can't start in the same place at the same time?  How do you compare their progress?


How to model the relationships (a) between properties and owners and (b) between properties and tenants? I have a database design and I'm having trouble with two tables. I have a Users table, and a Properties table. The Properties table may only have 0, 1, or 2 users, let's call them owner and tenant, and only one of each (per property), and a User may be an owner or a tenant in many Properties.

So, here are my two options according to the case:

Have two foreign keys in the Properties table (since the maximum of users referenced by a property are 2 and not N), called owner_id and tenant_id which reference the Users table.
Have a N:M intermediate table, with the user_id and property_id, along with another column to indicate the type of user in that property (say boolean 1 = owner, 0 = tenant).

Which is the best design option?

Clarifications:

A user has a name and email, and a property a street and its number as attributes.
A user may be only an owner or tenant in a property, not both, since an owner implies he owns the property (wether he lives there or not), and a tenant is another user actually living there, so there needs to exist an owner before a tenant is added.
A property may have no users assigned.

Business rules

So, (1) after we engaged in some deliberations via comments and chat, and (2) once you discussed the possibilities with your customer and colleagues, it has been defined that, in fact:

A Property has zero-one-or-many Owners, each of which must be a User
A User is the Owner of zero-one-or-many Properties
A Property has zero-one-or-many Tenants, each of which must be a User
A User is the Tenant of zero-one-or-many Properties
A Property is uniquely identified by the combination of its Street and its Number
A User is uniquely identified by its EmailAddress

Therefore —in your business context— among other aspects, there are two distinct conceptual-level associations (or relationships) of cardinality many-to-many (M:N) between the entity types Property and User.

Illustrative IDEF1X diagram

Consequently, I have created an IDEF1X1 diagram that consolidates the business rules formulated above, which is shown in Figure 1:

As demonstrated, every entity type —be it associative or independent— is portrayed by means of its individual box.

I depicted (a) ownership and (b) tenancy as two different associative entity types since, although very similar, they represent the prototypes of two distinct things of interest in you business domain, so it is very convenient to manage them separately because, e.g.:

Apart from

solely retaining the fact that a given user is the tenant or owner of a property by way of true or false attributes —named, say, is_owner and is_tenant—,

you might as well be interested in

keeping the exact point in time when all the ownership and tenancy instances where registered in the system, thus establishing a time-related attribute —entitled, say, created_datetime— for said entity types would be very advantageous.

In the future, one of these entity types may entail keeping track of certain attributes that do not apply to the other, hence administering them discretely yields benefits from the beginning, making up a more extendable and versatile conceptual schema.

It is worth to mention that, in the diagram, the user_id attribute migrates from (i) the user entity type to (ii) ownership and (ii) tenancy as owner_id and tenant_id respectively —both quite descriptive role names that you provided in the question—.

Expository logical SQL-DDL design

Then, based on the IDEF1X diagram detailed above, I wrote the expository DDL layout that is declared as follows:

-- You should determine which are the most fitting
-- data types and sizes for all your table columns
-- depending on your business context characteristics.

-- Also, you should make accurate tests to define the
-- most convenient INDEX strategies based on the exact
-- data manipulation tendencies of your business domain.

-- As one would expect, you are free to utilize
-- your preferred (or required) naming conventions.

CREATE TABLE user_profile (
    user_id          INT      NOT NULL,
    first_name       CHAR(30) NOT NULL,
    last_name        CHAR(30) NOT NULL,
    gender_code      CHAR(3)  NOT NULL,
    birth_date       DATE     NOT NULL,
    email_address    CHAR(30) NOT NULL,
    created_datetime DATETIME NOT NULL,
    --
    CONSTRAINT user_profile_PK  PRIMARY KEY (user_id),
    CONSTRAINT user_profile_AK1 UNIQUE ( -- Multi-column ALTERNATE KEY.
        first_name,
        last_name,
        gender_code,
        birth_date
    ),
    CONSTRAINT user_profile_AK2 UNIQUE (email_address) -- Single-column ALTERNATE KEY.
);

CREATE TABLE property (
    property_id      INT      NOT NULL,
    street           CHAR(30) NOT NULL,
    number           CHAR(30) NOT NULL,
    created_datetime DATETIME NOT NULL,
    --
    CONSTRAINT property_PK PRIMARY KEY (property_id),
    CONSTRAINT property_AK UNIQUE      (street, number)
);

CREATE TABLE ownership (
    property_id      INT      NOT NULL,
    owner_id         INT      NOT NULL,
    created_datetime DATETIME NOT NULL,
    --
    CONSTRAINT property_PK              PRIMARY KEY (property_id, owner_id),
    CONSTRAINT ownership_to_property_FK FOREIGN KEY (property_id)
        REFERENCES property (property_id),
    CONSTRAINT ownership_to_owner_FK    FOREIGN KEY (owner_id)
        REFERENCES user_profile (user_id)
);

CREATE TABLE tenancy (
    property_id      INT      NOT NULL,
    tenant_id        INT      NOT NULL,
    created_datetime DATETIME NOT NULL,
    --
    CONSTRAINT tenancy_PK             PRIMARY KEY (property_id, tenant_id),
    CONSTRAINT tenancy_to_property_FK FOREIGN KEY (property_id)
        REFERENCES property (property_id),
    CONSTRAINT tenancy_to_tenant_FK   FOREIGN KEY (tenant_id)
        REFERENCES user_profile (user_id)
);

Hence, in such logical-level design:

each base table represents an individual entity type (which prevents ambiguities introduced by denoting distinct meanings and intentions by virtue of the same base table);
every column stands for a specific attribute of the respective entity type;
a particular data type is fixed for each column in order to ensure that all the values it contains belong to a single and well defined set, be it INT, DATETIME, CHAR, etc.; and
multiple constraints are configured (declaratively) in order to guarantee that the assertions in form of rows retained in all the tables comply with the business rules determined in the conceptual schema.

All of this in agreement with the stipulations by Dr. Edgar Frank Codd in his relational model (as far as the capabilities of the Structured Query Language permit at the time of writing).

Of course, with this logical layout you can still, e.g., derive in BOOLEAN form whether a given User is the Owner of a particular Property or not, with the aid of a SELECT operation that produces a scalar value that evaluates to either 'True' or 'False', which can be accessed from one or more (present or future) application programs sharing this database.

Covering the initial business rules with the same logical-level arrangement

These aspects are now mostly anecdotical, but some of the initial business rules were:

A Property has zero-or-one Owner, which must be a User
A User is the Owner of zero-one-or-more Properties
A Property has zero-or-one Tenant, which must be a User
A User is the Tenant of zero-one-or-more Properties

And they can be coped with using the same logical design previously discussed declaring the property_id column as an ALTERNATE KEY in both the ownership and the tenancy tables, via two more constraints, i.e.:

ALTER TABLE ownership
    ADD CONSTRAINT UNIQUE ownership_AK (property_id);

ALTER TABLE tenancy
    ADD CONSTRAINT UNIQUE tenancy_AK (property_id);

This shows other facet (that can be of help for a future visitor) of the versatility offered by the arrangement proposed in this answer.

Endnote

1 Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was established as a standard in december 1993 by the United States National Institute of Standards and Technology (NIST). It is solidly based on (a) some of the theoretical works authored by the originator of the relational model, i.e., Dr. E. F. Codd; on (b) the entity-relationship view, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown.


pgAdmin4 version 3.1 how to update to the last version We use the pgAdmin4 version 3.1 on Red Hat connecting to a server PostgresQL 9.6.
In the log of the server, I notice some errors generated by the pga4dash :

ERROR:  column "waiting" does not exist at character 193

Indeed, the column waiting has been renamed to wait_event in Postgresql 9.6.

I tried to upgrade the pgadmin4 package by using :

yum updgrade pgadmin4

The upgrade was realized without problem but it's still running the oldest version 3.1 instead of the new downloaded (after a reload of httpd daemon)

How can I do to use the newest pgadmin4 version downloaded? I search on the documentation of pgadmin and there are no notes about an upgrade of the pgAdmin4

I found the solution, I just recreated the configuration file in the apache2 directory and restarted the httpd daemon.

This ticket can be close.


How to check if I am hitting the Express Edition size limit? I am confused. AFAIK SQL Server 2005 Express has a limit of 4GB database data size. However I have the following results from sp_spaceused:

How can I check if my DB is hitting the size limit?
Is unallocated space the space left untill I hit the limit?
How much space do I have left?
Does index_size count in the limit?

Here is a good script I shamelessly ripped from here:

use [Insert DB Name]

select
a.FILEID,
[FILE_SIZE_MB] =
convert(decimal(12,2),round(a.size/128.000,2)),
[SPACE_USED_MB] =
convert(decimal(12,2),round(fileproperty(a.name, 'SpaceUsed')/128.000,2)),
[FREE_SPACE_MB] =
convert(decimal(12,2),round((a.size-fileproperty(a.name, 'SpaceUsed'))/128.000,2)) ,
NAME = left(a.NAME,15),
FILENAME = left(a.FILENAME,30)
from
dbo.sysfiles a

This is good because it will give you the free space in each DB file (you might have multiple files and perhaps someone set it up to put some objects in each) as well as the free size.

For example, you have have a 4GB data file which has 3GB of free space.  Maybe you have 1 MDF without a lot of data but a NDF with lots of data.  This query will tell you the free size in each file and what DB that file is allocated to.  Remember to add up all the 'SPACE_USED_MB' for each DB to get the total size.

Good luck!

Edit:
Removed a unsupported and buggy command that I thought I could get away with posting in here for this query. :(


psycopg2 connection already closed In one of our environments we execute quite heavy query from python script using psycopg2. It takes more then 10 min to execute but we get a result and can consume received data.

When we move the same script to different environment then after ~5min we get an error: psycopg2.InterfaceError: connection already closed

What may be the reason for that?

The most likely reason is you have a firewall or gateway which is not very patient and is dropping the connection after 5 minutes.  Usually when this happens, it makes it look to the client like the server closed the connection, and to the server like the client closed the connection.  That seems to be compatible with the error message you see in python.  What does the server's log file blame the problem on?


Speeding Up Querying Views We've got a complicated stored procedure.  The biggest pain point of the procedure is a join on a complicated view--it has unions and subqueries.  I can't add indexes to it.

So, I want to cheat.

By creating a procedure that copies the view to an actual table with indexes.  The procedure runs quickly, less than a second.  But the effect for our stored procedure is amazing.  50 executions used to take 12 seconds.  Now it takes only 4.  I'm thinking of implementing this method and then recreating the "compiled" table as needed, but never more than every 2 or 3 seconds.

My question is this.  How bad is this practice?  Are there better ways of handling such a complicated view?

240 msec isn't that bad for a complex view involving unions and subqueries. It's not great, but I've seen a lot worse. Beyond this, I'm afraid I have more questions than answers.

It's not clear from your question whether this is currently an indexed view. Is it?

It sounds like the first user to hit stale data will have to wait at least a full second for the table to be repopulated. Is that right? Does that matter?

Look at the execution plan for both the complicated view and for the join on the view that the stored proc does.  Are both execution plans reasonable?
Are existing indexes being used?
Are existing indexes the best you can do? (There are a lot of options to the CREATE INDEX statement that most people don't know about.)
Do other indexes need to be built?
Can you use an indexed view?
Did you try the Database Engine Tuning Advisor?
Calculate the cost of the existing, slower process against the programmer time needed to "fix" it. How many times per day does this procedure get executed? How long will it take you to recover the cost of "fixing" this?
What's the cost of a user getting stale data using the new "create a table from this view" process?
What's the likelihood of a user getting stale data using the new process?
What's the cost of repopulating your table for 1 second out of every 5 seconds?
How does this new table (and its log files) affect disaster recovery?
Are the underlying tables reasonable? (Statistically, most performance problems are structural problems.)  Look hard at the use of ID numbers, and at how normalization has been carried out.


How to join 2 tables in mysql but with different values in one of the tables I have 2 tables(as shown by the image below) inbox_messages_table and users_table

So what i am trying to do is combining the 2 tables ON user_id but in my inbox_messages_table i have an id which i created for the website itself which is (-1) to have it unique so how can i do this and be able to retrieve the -1 too with others ?

my query so far is this :

// $user_id is the logged in user

SELECT * FROM inbox_messages_table i
    JOIN users_table u ON i.from_user_id = u.user_id
    WHERE u.banned = 0
        AND to_user_id = $user_id
        AND deleted = 0 ORDER BY message_timestamp DESC

You can use LEFT JOIN instead of JOIN.

With LEFT JOIN the query will return all records from the left table i.e. the one in the FROM, even if there is no matching record in the joint table.

SELECT * FROM inbox_messages_table i
LEFT JOIN users_table u ON i.from_user_id = u.user_id
WHERE u.banned = 0 AND to_user_id = $user_id AND deleted = 0 ORDER BY message_timestamp DESC


ALTER VIEW drops Index from View as the header line suggests: Using an ALTER VIEW statement on a VIEW that has an index on it will drop this (all?) indexes from the VIEW without warning. I would prefer the ALTER VIEW statement to fail, informing me to drop the index(es) first.

Is there a setting in SQL SERVER to change this behaviour? Or is it changed in a version after SQL 2012 (SP3)?

There is no setting to change this behavior nor has it changed in newer SQL Server versions. An excerpt from the current documentation remarks section for SQL Server 2019 and Azure SQL Database:

  ALTER VIEW can be applied to indexed views; however, ALTER VIEW
  unconditionally drops all indexes on the view