SQL uses ternary (three-valued) logic and comparisons with NULL are tricky, counter intuitive. Regarding the condition:

WHERE valid NOT IN ('Green', 'Blue', 'White')

it expands to:

WHERE valid <> 'Green' AND valid <> 'Blue' AND valid <> 'White'

When valid is NULL, all these 3 sub-conditions evaluate to UNKNOWN (neither TRUE nor FALSE). The whole condition evaluates to UNKNOWN as well and thus the row rejected (WHERE keeps only the rows that the condition evaluates to TRUE).

When comparing nulls, you can think this way. I don't know the exact value of valid so I can't be sure if it's going to be different to 'Green' or not. It might be and it might be not. The result is UNKNOWN.

If the intention is to get in the result the rows where valid IS NULL, you can use either:

SELECT * FROM @Fiesty
WHERE valid IS NULL
   OR valid NOT IN ('Green', 'Blue', 'White') ;

or EXISTS / EXCEPT (or NOT EXISTS / INTERSECT):

SELECT f.* FROM @Fiesty AS f
WHERE EXISTS
      ( SELECT f.valid
      EXCEPT
        SELECT v.valid
        FROM
            ( VALUES ('Green'), ('Blue'), ('White') )
            AS v (valid)
      ) ;

There is also a NOT EXISTS way which is my opinion to be preferred to NOT IN, especially because the behaviour regarding nulls is more intuitive. (Notice also the similarity with the INTERSECT version):

SELECT f.* FROM Fiesty AS f
WHERE NOT EXISTS
      ( SELECT *
        FROM
            ( VALUES ('Green'), ('Blue'), ('White') )
            AS v (valid)
        WHERE v.valid = f.valid
      ) ;

Test at dbfiddle.uk.


How do I configure a MySQL Cluster with a redundant mysqld host?

A great place to get started with MySQL Cluster are these Quick Start guides:
http://mysql.com/products/cluster/get-started.html#quickstart

In terms of hardware profile, commodity servers are just fine - take a look at some of the recent benchmarks - 4.3m QPS across 8 x Intel dual socket servers with 24GB RAM:
http://mysql.com/why-mysql/benchmarks/mysql-cluster/

Hope this helps.  Let us know how you get along, and also go to the MySQL Cluster forum for direct access to engineering team: forums.mysql.com/list.php?25


What can cause low cost and high runtime in EXPLAIN ANALYZE?

My first thought is that some other activity is bogging down postgres causing this

My first thought is the data is better or worse cached than the assumptions which are implicitly baked into the random_page_cost / seq_page_cost ratio(and into some various other things).  My second thought is that the expected number of rows is very wrong (compared to the actual number of rows reported by EXPLAIN ANALYZE) and that that leads to wrong costs.  Sometimes those thought orders are reversed.

But I also always keep a terminal open running 'top', so if I am running a query in real time (as opposed to finding it in the slow query log) I can immediately rule out the idea that other work is slowing things down.

  Am I mistaken to expect similar cost:time ratios for different queries?

While that is clearly the goal (what is the point of assigning costs to alternatives and choosing the lowest cost plan if has no better than random chance of actually being faster than a higher cost plan?), planning is very difficult and you shouldn't expect the correlation to be all that tight.

If you are one of the "hackers" who writes the code behind PostgreSQL and you want to find weaknesses in the planner for future improvement, then it might make sense to develop sense of the typical ratio between "arbitrary cost units" and ms of execution time.  But you have to carefully control for how hot the cache is for this to have even a snowball's chance of working.


There is not really a recommended choice for compressed versus uncompressed when the storage subsystem has compression/de-dupe features as there are too many variables, generally though I always enable compression even with storage subsystem compression.

You need to determine which is best for your particular environment and implement that. There are several areas you can check for comparison to validate which is the best choice.

Query speed - Check the overall duration of backups with and without compression. Also, ask your storage team to disable de-dupe and compression at their end for your backup disks (if possible) and run a SQL compressed and non-compressed backup and measure the speed of those backups as well.
Query throughput - Related to the overall duration, record the throughput in MB/s for compressed and uncompressed backups. Again, if you can run these tests with and without storage level compression that gives you more data to validate your choice.
Compression ratio - You want to see who is getting the best compression ratio - if SQL is saving you more space than your storage subsystem for little to no cost in time then it may be the choice, or if it is only marginally larger in size but much quicker, again this may be an acceptable trade-off.
Overall size - This is similar to compression ratio, but requires you to calculate the storage requirements taking into consideration your retention periods. If you need to keep daily FULL backups for 10 years, how much total storage is required, and how much space does the storage layer de-dupe save in that scenario? To answer these questions, you need the compression statistics from both SQL Server and the storage layer.

The below query will get statistics from your backup history to help you collate the data from a SQL Server perspective, and you need to get your storage team to extract their own statistics to show size, compression, throughput etc for comparison.

You should also run these tests on a non-Production SQL Server where you can change your configurations without impacting production.

Script:

WITH BackupDetails AS (
    SELECT
        bs.[database_name] AS [DatabaseName],
        CASE
            WHEN bs.[type] = 'D' THEN 'Full'
            WHEN bs.[type] = 'I' THEN 'Differential'
            WHEN bs.[type] = 'L' THEN 'Log'
            WHEN bs.[type] IS NULL THEN 'No Backup'
        END AS [BackupType],
        COALESCE(CONVERT(VARCHAR(20), bs.[backup_start_date], 120),'-') AS [BackupStartDate],
        CASE
            WHEN DATEDIFF(SECOND, bs.[backup_start_date], bs.[backup_finish_date]) < 1 THEN 1
            ELSE DATEDIFF(SECOND, bs.[backup_start_date], bs.[backup_finish_date])
        END AS Duration_S,
        bs.[user_name] AS [BackupCreator],
        CAST(((bs.[backup_size])* 0.00000095367432) AS DECIMAL(15,2)) AS [BackupSize],
        CAST(((bs.[compressed_backup_size])* 0.00000095367432) AS DECIMAL(15,2)) AS [CompressedBackupSize],
        bmf.physical_device_name AS [File]
    FROM
        msdb..backupset bs
        INNER JOIN msdb..backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
)

SELECT [DatabaseName],
    [BackupType],
    [BackupStartDate],
    [BackupSize] AS [BackupSize(MB)] ,
    [CompressedBackupSize] AS [CompressedBackupSize(MB)],
    CAST(CAST(ROUND([BackupSize]/[CompressedBackupSize], 2, 1) AS DECIMAL(15,2)) AS VARCHAR(25)) + ' : 1' AS [CompressionRatio],
    [Duration_S],
    CAST([CompressedBackupSize]/[Duration_S] AS DECIMAL(15,2)) AS [Throughput (MB/s)],
    [File]
FROM BackupDetails
WHERE [BackupType] = 'Full'
ORDER BY
    [DatabaseName], [BackupStartDate] DESC


Try this:

SELECT correct_picks, points, pick_user_id, bp, (bp + points) AS total, user_user_id, username, avatar_date, avatar_width, avatar_height, gravatar, gender
FROM
(
SELECT COUNT(game.game_id) AS correct_picks,
       SUM(game.points) points,
       pick.user_id AS 'pick_user_id', -- Alias to avoid same name with user.user_id

       (SELECT SUM(game.points)
    FROM xf_nflj_pickem_pick pick
    LEFT JOIN xf_nflj_pickem_game game
    ON game.game_id = pick.game_id
    WHERE game.bonus=1 AND pick.user_id = user.user_id
        AND game.week_id <= 8 AND game.winner = pick.team_id
    GROUP BY user.user_id) AS bp,

       user.user_id AS 'user_user_id', -- Alias to avoid same name with pick.user_id
       user.username,
       user.avatar_date,
       user.avatar_width,
       user.avatar_height,
       user.gravatar,
       user.gender
FROM xf_nflj_pickem_game game
    LEFT JOIN xf_nflj_pickem_pick AS pick
    ON (pick.game_id = game.game_id)
    LEFT JOIN xf_user AS USER
    ON (user.user_id = pick.user_id)
WHERE (game.winner = pick.team_id)
    AND game.week_id <= 8
    AND game.pool_id = 1
    AND pick.team_id <> 0
GROUP BY pick.user_id
) tbl1 -- Alias for the subquery as table
ORDER BY (points + bp) DESC, username, pick_user_id ASC;

Made your query into a table(as a subquery) then used the ORDER BY (points + bp) DESC, username, pick_user_id ASC.

It seems like mysql does not allow using the aliases of the column to be used in the same line or hierarchy as the aliased column.


How to set the wal_level in AWS RDS Postgresql?

To find out the wal_level you can use PSQL and execute: show wal_level;
You need to set rds.logical_replication parameter to 1. It will automatically change wal_level to logical.

I found some details here


I finally resolve this error by using DBUA but I got errors in this step, then I returned to restore from RMAN again then everything just works


First, find out if "mail" guarantees that all data is utf8 or ascii or whatever.  If utf8, then use

MEDIUMTEXT CHARACTER SET utf8mb4

for a limit of 16MB.  For a 4GB limit, change to LONGTEXT.

If the mail is not forced into some character encoding, then use

MEDIUMBLOB or LONGBLOB

(without a charset).


Preventing full backups for unmodified/slightly modified databases

Skipping the backup altogether is not a good idea in my opinion. You should probably keep on taking differential backups instead. They will probably be very small and not a concern at all.

Detecting the percentage of data that changed since last FULL backup can be done using this function by Paul Randal.


Can an index speed up a query with GROUP BY/aggregate over the whole table (no selectivity)?

Not likely. GROUP BY and ORDER BY typically entail a sort. However, in this case a HashAggregate is used (likely because we're working with the whole table).

CREATE TABLE foo AS
SELECT x % 5 AS a, x % 10 AS b, x AS c
FROM generate_series(1,1e6) AS x;

With the HashAggregate plan,

# EXPLAIN ANALYZE SELECT a,b,sum(c) FROM foo GROUP BY a,b ORDER BY a,b;
                                                        QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=23668.04..23668.16 rows=50 width=14) (actual time=611.607..611.608 rows=10 loops=1)
   Sort Key: a, b
   Sort Method: quicksort  Memory: 25kB
   ->  HashAggregate  (cost=23666.00..23666.62 rows=50 width=14) (actual time=611.589..611.593 rows=10 loops=1)
         Group Key: a, b
         ->  Seq Scan on foo  (cost=0.00..16166.00 rows=1000000 width=14) (actual time=0.012..71.157 rows=1000000 loops=1)
 Planning time: 0.168 ms
 Execution time: 611.665 ms

so we add an index...

CREATE INDEX idx ON foo (a,b);
VACUUM FULL ANALYZE foo;

... still shows the same query plan. So we disable HashAggregate

SET enable_hashagg = false;

And, try again..

# EXPLAIN ANALYZE SELECT a,b,sum(c) FROM foo GROUP BY a,b ORDER BY a,b;
                                                            QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=0.42..61292.04 rows=50 width=14) (actual time=108.149..655.536 rows=10 loops=1)
   Group Key: a, b
   ->  Index Scan using idx on foo  (cost=0.42..53791.41 rows=1000000 width=14) (actual time=0.066..272.299 rows=1000000 loops=1)
 Planning time: 0.121 ms
 Execution time: 655.594 ms
(5 rows)

And, it takes more time 655ms compared to the previous 611ms.

Need faster?

If that's not fast enough (and 611ms to group and sum a million rows isn't bad). Then you can use a MATERIALIZED VIEW if your workload permits it (if the query is hot and/or infrequently updated),

CREATE MATERIALIZED VIEW foo2 AS
SELECT a,b,sum(c)
FROM foo
GROUP BY a,b
ORDER BY a,b;

Now you're down to sub-MS time when TABLE foo2. Then just do REFRESH MATERIALIZED VIEW foo2; to refresh the view. Or, you can create a trigger update another table and update it with triggers.

On the actually column being aggregated.

There are some exceptions, but sum() isn't one of them. Most aggregates do not use an index because they usually do not have a need for one. The exceptions are the aggregates that are order-specific (like min() and max()). So for instance, if after we created the index on (a,b) you run a sum(a),

# EXPLAIN ANALYZE SELECT sum(a) FROM foo;
                                                     QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=18666.00..18666.01 rows=1 width=4) (actual time=287.063..287.063 rows=1 loops=1)
   ->  Seq Scan on foo  (cost=0.00..16166.00 rows=1000000 width=4) (actual time=0.015..85.435 rows=1000000 loops=1)
 Planning time: 0.098 ms
 Execution time: 287.104 ms
(4 rows)

you can see that it still uses a seq scan.. You'll see the same plan for sum(c) which doesn't have an index at all. Now here is the kicker,

# EXPLAIN ANALYZE SELECT min(a) FROM foo;
                                                              QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=0.48..0.49 rows=1 width=0) (actual time=0.041..0.041 rows=1 loops=1)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.42..0.48 rows=1 width=4) (actual time=0.036..0.037 rows=1 loops=1)
           ->  Index Only Scan using idx on foo  (cost=0.42..56291.41 rows=1000000 width=4) (actual time=0.035..0.035 rows=1 loops=1)
                 Index Cond: (a IS NOT NULL)
                 Heap Fetches: 1
 Planning time: 0.171 ms
 Execution time: 0.080 ms
(8 rows)

min(a) unlike sum(a) can make use of an ordering, and so the query planner realizes that the index scan, which isn't free, has a benefit.

Proof using index on (a,b,c)

For whatever reason if you want to see proof that a further index on c doesn't matter for the purposes of summing (ask a question if after reading the above you still do not understand why),

-- turn this back on we turned it off earlier
SET enable_hashagg = true;
DROP INDEX idx;
CREATE INDEX idx ON foo (a,b,c);
VACUUM FULL ANALYZE foo;
EXPLAIN ANALYZE SELECT a,b,sum(c) FROM foo GROUP BY a,b ORDER BY a,b;
                                                        QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=23668.04..23668.16 rows=50 width=14) (actual time=608.888..608.889 rows=10 loops=1)
   Sort Key: a, b
   Sort Method: quicksort  Memory: 25kB
   ->  HashAggregate  (cost=23666.00..23666.62 rows=50 width=14) (actual time=608.869..608.871 rows=10 loops=1)
         Group Key: a, b
         ->  Seq Scan on foo  (cost=0.00..16166.00 rows=1000000 width=14) (actual time=0.015..72.613 rows=1000000 loops=1)
 Planning time: 0.130 ms
 Execution time: 608.947 ms
(8 rows)

No improvement whatsoever. Disabling hashagg still shows no improvement whatsoever.

TLDR;

In this specific and simple use case indexes don't matter. The planner chooses the best method.


How to Export SQL Server Database To A File For Later Import To Different Computer

I upvoted the comment by @Alex K. as this, I believe, is the easiest way of accomplish what you want. Backup the database, when asked copy the .bak file where you think is better for your needs, maybe even you can copy it already in a place where the other PC has access. Then do the opposite on the other SQL Server instance on the other PC, restore the database from the .bak previously saved.

Here is a link to a detailed description on how to backup the database using SSMS.

And here how to restore using the SSMS.


Access denied for user 'root'@'localhost' (using password yes)

If you installed MySQL 5.6 from the command line, the easiest way to find the temporary password is to go to the Linux command line do the following:

cd
cat `ls -la | grep mysql | grep secret | awk '{print $9}'` | awk '{print $18}'

This will quickly print the temporary password.

Try to login using that password

cd
OLDPASSFILE=`ls -la | grep mysql | grep secret | awk '{print $9}'`
PASSWORD=`cat ${OLDPASSFILE} | awk '{print $18}'
mysql -uroot -p${PASSWORD}

If the temporary password is no longer available, then you will have do the following:

NEWPASS="whateveriwant"
service mysql stop
service mysql start --skip-grant-tables
mysql -ANe"update mysql.user set password=PASSWORD('${NEWPASS}') where user='root'"
service mysql restart

Give it a Try !!!


Filtering out duplicate domains from URL column using Postgres full-text search parsers

After asking around on IRC, the Postgres mailing list, and a few other places, this is the best I've been able to come up with. I'm going to put it up in case it helps someone else with similar problem,and also because I'm hoping that someone can tell me how to improve on it. I first created the following views:

    CREATE VIEW scored_pages AS (
        SELECT crawl_results.crawl_id, crawl_results.score,
            crawl_results.page_id, pages.url
        FROM crawl_results
        JOIN pages ON crawl_results.page_id = pages.id
    );

    CREATE VIEW scored_links AS (
        SELECT scored_pages.score, links.source,
            links.target, links.link_text
        FROM links
        JOIN scored_pages ON scored_pages.url = links.source
    );

Then, using these views, I did the following query to extract the links from the lowest scored pages in the results:

SELECT score, host, target
FROM (
        SELECT DISTINCT ON (token) token AS host, score, target
        FROM scored_links,
        LATERAL ts_parse('default', target) WHERE tokid = 6
        ORDER BY token, score
) as x
WHERE NOT EXISTS (SELECT pp.id FROM pages pp WHERE target=pp.url)
ORDER BY score;

I would appreciate any feedback on how to improve this, or anything I'm doing wrong. In the meantime, I'll leave this question open in case someone comes by later with a better answer.


Help understanding InnoDB Deadlock Detection

Your (2) transaction holds S-lock on table Resources.ResourceAllocations. (1) and (2) transactions wait for X-lock for that table -> deadlock.

What happened:

Trx(2) got S-lock on ResourceAllocations with the select statement (because of create table ... select)
Trx(1) needs X-lock to make update on ResourceAllocations -> wait for X-lock
Trx(2) needs X-lock (for unknown reason) on ResourceAllocations -> wait for X-lock

As the result Trx(1) waits for X-lock from Trx(2) (because Trx(2) has S-lock) and Trx(2) waits for X-lock from Trx(1) (because it is behind Trx(1) in a queue for X-lock).

It's hard for me to say what can be done because I do not see other queries in transactions, but the solution definitely would be: do not require for X-lock in transaction (2).

In simple words: commit transaction(2) after create table statement or do not update tables in this statement from any other transactions until it is done.


Reducing impact of SUM(DATALENGTH()) over linked server

I am not sure exactly how you have determined that all of the data is going across the wire (or why that should be the case) but you could always have a computed column:

ALTER TABLE dbo.SavedImage ADD PictureSize
  AS CAST(DATALENGTH([Image]) AS BIGINT);

Or just add a column and calculate that yourself whenever you insert/update a row, either by forcing inserts/updates through a stored procedure, or using a trigger.

And persist and/or index that as necessary, and then just use SUM(PictureSize) in the query.

Of course the big impact is still going to be on the fact that you have to scan the entire table or index in order to generate the aggregate.

This method could also be used to maintain the total size of all columns in some other table, but I'm largely against materializing redundant data unless there is a demonstrated advantage to doing so.

In any case, I really don't think this query is slow because it is moving the whole varbinary data over the wire and then calculating the length at the wrong end; if you have evidence to suggest that is the case, please update the question and we'll try to address it.


Adding to the SQL Mail Fail to Send quoted in the comments by LowlyDBA

From this question:

Comprehensive troubleshooting of DatabaseMail

I would first of all see what is the outcome of the following query (as you already got dbmail up and running):

--==============================================================
-- I’m doing a TOP 100 on these next several queries as they tend
-- to contain a great deal of data.  Obviously if you need to get
-- more than 100 rows this can be changed.
-- Check the database mail event log.
-- Particularly for the event_type of "error".  These are where you
-- will find the actual sending error.
--==============================================================
SELECT TOP 100 *
FROM msdb.dbo.sysmail_event_log
ORDER BY last_mod_date DESC;

--==============================================================
-- Check the actual emails queued
-- Look at sent_status to see 'failed' or 'unsent' emails.
--==============================================================
SELECT TOP 100 *
FROM msdb.dbo.sysmail_allitems
ORDER BY last_mod_date DESC;

--==============================================================
-- Check the emails that actually got sent.
-- This is a view on sysmail_allitems WHERE sent_status = 'sent'
--==============================================================
SELECT TOP 100 *
FROM msdb.dbo.sysmail_sentitems
ORDER BY last_mod_date DESC;

--==============================================================
-- Check the emails that failed to be sent.
-- This is a view on sysmail_allitems WHERE sent_status = 'failed'
--==============================================================
SELECT TOP 100 *
FROM msdb.dbo.sysmail_faileditems
ORDER BY last_mod_date DESC

Depending on the outcome of these queries, there are a few ways to troubleshoot.

Maybe the problem is not within sql? for that I use powershell to send a few test emails see if everything is ok. (change the details in the script below)

$smtpServer = "200.1.1.223"
$smtpPort = 25
$emailFrom = "donotreply@xxxxx.co.uk"
$emailTo = "mmiorelli@xxxxx.co.uk"
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$smtp.Port = $smtpPort
$subject = "ccatsql"
$body = "test email from ccatsql "
$smtp.Send($emailFrom, $emailTo, $subject, $body)

check if the .Net framework 3.5 is installed - that can cause weird errors, even in sql server 2016, as quoted in

SQL Server 2016 - Database Mail doesn't work without .NET 3.5

and from my own experience, sometimes, no error will be written to any log whatsoever, if the .NET 3.5 or any underlying component is missing or corrupt.

To work around this issue (ONLY if this is the issue), you can implement any one of the following:

Create the DatabaseMail.exe.config and drop it next to the DatabaseMail.exe under the Binn folder.

You can use notepad.exe or any other editor to edit it.

Just make sure you save it by using UTF-8 encoding (in notepad.exe, select Save As... and in the Encoding combo box, select UTF-8):

Run a repair setup action of SQL Server 2016.
Manually install .Net Framework 3.5 on the machine.

please see more information on this answer.


PostgreSQL psql - not working but no errors

Jumped on the IRC postgresql channel and had this answered in 30 seconds.

Issue was that I was not ending my SQL commands with a semicolon. Doh!


If you don't have something that captured the queries being executed at the time, all you can do is guess.  In case you're on SQL Server 2016 or higher, and you have Query Store enabled for the affected database, I'll mention that you could track down long-running or slow queries there.  If you don't have query store or some other way of tracking activity after a problem, you might want to look into a monitoring tool, or at least some simpler "home grown" solutions, like logging sp_WhoIsActive to a table.
If the queries that caused this problem are still in the plan cache, you could try running sp_BlitzCache to find the most CPU-intensive queries:
EXEC master.dbo.sp_BlitzCache
    @DatabaseName = N'YourDatabaseName',
    @SortOrder = 'avg cpu';

To be clear, there's no way to know for sure if those queries are the problem ones, but it might be your best bet at this point.
You could also try to narrow down the tables involved (based on which areas of the application or features you said you know were inaccessible).  Once you've done that, you could run sp_BlitzIndex on that table and get a breakdown on how it's being used:
EXEC master.dbo.sp_BlitzIndex
    @DatabaseName = N'YourDatabaseName',
    @SchemaName = N'ProbablyDbo',
    @TableName = N'YourTableName';

That will show you if there are indexes being scanned a lot, or experiencing lock escalation, or long lock waits, etc.  Again, this would all be sort of circumstantial evidence of potential problems.  But it might help.


SSIS data flow to update source table rows after copying to destination

With T-SQL there's an output inserted clause which would be really helpful in this scenario.

I'm not aware of a way to do that with SSIS, so your alternate approach of using a second data flow after your first (you can connect the second data flow to the first so your dependency will be honored), querying the web database, and updating the internal one as appropriate will work as long as your workload can tolerate the additional latency involved in waiting to update the SSIS_TRANSFERRED column.


Is the data fetched by an explicit cursor immutable in PL/SQL?

Oracle will provide a read-consistent view of the data.  Assuming that you're using the default transaction isolation level of read committed, the data that a query will return is fixed at the point in time that the cursor is opened (whether the cursor is implicit or explicit is irrelevant).

The only exception to this is that PL/SQL that is called by the query (i.e. stored functions) will see the data as it exists at the point in time that the function is called not at the point that the cursor was opened.


Get if field has max value and display value

I think this will give you want you want:
 select project_id,
        version,
        stop,
        t_id,
        status,
        mp,
        case when rowno = 1 then 'Y' else 'N' end is_last_version
        from (
              select project_id,
                     version,
                     stop,
                     t_id,
                     status,
                     mp,
                     row_number() over (partition by projecT_id order by version desc)
                        as rowno
              from t_proj_f t
              order by project_id)


Designing for maximum performance for an anticipated large user-base, I would suggest ensuring bookmarks and tags are not duplicated unnecessarily.  Using my suggested schema below, http://google.com/ would become a single bookmark row.  Users who want a bookmark to Google would have a row in UserBookmarksTags referencing the Bookmark row and the associated tag row.

I would tend to abstract the UserID away from the Bookmarks table, and use a cross-reference table to determine which users have bookmarks, and what they've tagged each bookmark with.

Using SQL Server, I would do it like this:

USE tempdb;
GO
CREATE TABLE Users
(
    UserID INT NOT NULL
        CONSTRAINT PK_Users
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , UserName varchar(255)
);
CREATE TABLE Bookmarks
(
    BookmarkID INT NOT NULL
        CONSTRAINT PK_Bookmarks
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , BookmarkName varchar(255)
    , BookmarkURL varchar(255)
);
CREATE TABLE Tags
(
    TagID INT NOT NULL
        CONSTRAINT PK_Tags
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , TagName varchar(255)
);

If every entry in your system requires both a bookmark and a tag, you could use the following table for maximum performance:

CREATE TABLE UsersBookmarksTags
(
    UserID INT NOT NULL
        CONSTRAINT FK_UsersBookmarksTags_UserID
        FOREIGN KEY REFERENCES Users(UserID)
    , TagID INT NOT NULL
        CONSTRAINT FK_UsersBookmarksTags_TagID
        FOREIGN KEY REFERENCES Tags(TagID)
    , BookmarkID INT NOT NULL
        CONSTRAINT FK_UsersBooksmarksTags_BookmarkID
        FOREIGN KEY REFERENCES Bookmarks(BookmarkID)
    , CONSTRAINT PK_UsersBookmarksTags
        PRIMARY KEY CLUSTERED
        (UserID, TagID, BookmarkID)
);

The above version of the table UsersBookmarksTags allows you to quickly return a list of bookmarks for a specific user that matches any given tag, or all bookmarks for that user.

Alternately, you could create the cross reference table that allows null entries for both Tags and Bookmarks, as in:

CREATE TABLE UsersBookmarksTags
(
    UsersTagsBookmarksID INT NOT NULL
        CONSTRAINT PK_UsersBookmarksTags
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , UserID INT NOT NULL
        CONSTRAINT FK_UserBookmarks_UserID
        FOREIGN KEY REFERENCES Users(UserID)
    , TagID INT NULL
        CONSTRAINT FK_UserBookmarks_TagID
        FOREIGN KEY REFERENCES Tags(TagID)
    , BookmarkID INT NULL
        CONSTRAINT FK_UserBooksmarks_BookmarkID
        FOREIGN KEY REFERENCES Bookmarks(BookmarkID)
);

This allows rows that have a bookmark without a tag, and also allows tags to be associated to a user without a bookmark.

If there is a requirement to know all the tags a user has added, without those tags necessarily having an associated bookmark, I would consider adding another table for that purpose, as:

CREATE TABLE UserTags
(
    UserID INT NOT NULL
        CONSTRAINT FK_UserTags_UserID
        FOREIGN KEY REFERENCES Users(UserID)
    , TagID INT NOT NULL
        CONSTRAINT FK_UserTags_TagID
        FOREIGN KEY REFERENCES Tags(TagID)
    , CONSTRAINT PK_UserTags
        PRIMARY KEY CLUSTERED
        (UserID, TagID)
);


Create a second Replica set on same 3 nodes as another Replica set

Yes, for testing and development. Not for production, obviously. Note below, the different port numbers and data paths for each mongod instance.

If you had the first replica set like this:

mkdir -p /srv/mongodb/rs0-0 /srv/mongodb/rs0-1 /srv/mongodb/rs0-2

mongod --port 27017 --dbpath /srv/mongodb/rs0-0 --replSet rs0 --smallfiles --oplogSize 128 --fork

mongod --port 27018 --dbpath /srv/mongodb/rs0-1 --replSet rs0 --smallfiles --oplogSize 128 --fork

mongod --port 27019 --dbpath /srv/mongodb/rs0-2 --replSet rs0 --smallfiles --oplogSize 128 --fork

You could add a second replica set like this to the same localhost with the following:

mkdir -p /srv/mongodb/rs1-0 /srv/mongodb/rs1-1 /srv/mongodb/rs1-2

mongod --port 37017 --dbpath /srv/mongodb/rs1-0 --replSet rs1 --smallfiles --oplogSize 128 --fork

mongod --port 37018 --dbpath /srv/mongodb/rs1-1 --replSet rs1 --smallfiles --oplogSize 128 --fork

mongod --port 37019 --dbpath /srv/mongodb/rs1-2 --replSet rs1 --smallfiles --oplogSize 128 --fork

Reference: Deploy a Replica Set for Testing and Development


To find out last activity/access date and date of expiration for all SQL Server 2012 User login

For logins, only things that are like server audits.

A SQL trace to capture logins. This is more intensive than using an audit though.
An extended event session to capture the logins.
You could cobble up a login trigger to record logins to a table, but this is probably the worst of them all.

For expiries you can use Loginproperty('login_name', 'DaysUntilExpiration') but I think this is for SQL logins only. You may need to run a separate PowerShell script to determine the same for AD logins.

Neither of these will help when it comes to an AD account access through an AD group. For those it's much more complicated, either through impersonating each login and selecting the list of activate groups from sys.login_token and recording it all to a table; or cycling through AD with PowerShell again.


PostgreSQL streaming replication lag for synchronous standbys

The pg_xlog_location_diff within your query may  return more than 0.

There are three writing timing of WAL data to WAL segment file: 1) When a transaction is committed, 2) When WAL buffer is filled up, 3) When WAL writer process writes periodically (default is 200 milliseconds).

And basic sequence of streaming replication is shown below:

(master) Write WAL data.
(master) Send WAL data to a slave.
(slave) Receive WAL data and replay it.
(slave) Return some information (e.g. replay location, receive location, flushed location.)

I show an extreme example to clarify the problem. If you run a long transaction to insert huge data, master continues to send WAL data  till the transaction is committed.  When you issue your query during the period from 2 to 3, pg_current_xlog_location() returns the current location whereas replay_location returns the previous replay location, pg_xlog_location_diff(pg_current_xlog_location(), replay_location) therefore returns more than 0.

Other case is the following: The configuration parameter max_standby_*_delay is enable and a conflict occurs, your standby suspends replaying WAL data, therefore pg_xlog_location_diff() returns more than 0.

(Both of examples are logical extreme cases.)

In any case, I think your problem does not depend on whether replication mode is sync or async. The difference between sync and async is that the master confirms a synchronous slave commits transactions or not.
Your problem is related to general sequence of WAL data sending and replaying, but is not depend on the synchronous mode.

(Consider how a long transaction is committed in synchronous mode. I discussed what happens when your query is issued  during the period from 2 to 3 (it's very short period) in the streaming replication sequence I showed. I did not discussed when transaction is committed because it is not essential of the answer, i think.)


It's part of a distributed query. The string after the @ is a database link.

You can see where the database link is pointing by querying the all_db_links view, or the user_ or dba_ equivalents depending on the owner and your privileges. It might refer to a TNS alias, in which case you could get further details from the tnsnames.ora file.

When you query table you're (usually) looking at a table in your own schema. When you query table@dblink you're looking at a table in a remote database. They are different objects so you'd expect different results. Your local table is empty, but the remote one has some data.

I say 'usually' because table could be a synonym pointing at another schema, or even to a schema on a remote database itself. And dblink could be pointing back to your own database - it would be unusual but I've seen it done as a way of accessing another schema (not something I'd recommend). Without seeing if there is a synonym or where the dblink is going it's hard to be definitive, but these are less common scenarios. In this case though you're clearly hitting two different tables, probably on different databases. (Well, unless there's a VPD and connecting to the same table as a different user gives a different view... but starting to go down the rabbit hole now...)


Your query uses SELECT * ... which will select every column from the tables (in the order that they exist within the tables).  So if you have:

CREATE TABLE t1 (
  id bigserial primary key, -- bleh - never use just "id" .. this is only for example
  desc text
);

CREATE TABLE t2 (
  id bigserial primary key,
  t1_id bigint references t1 (id),
  desc text
);

select
  *
from t1
join t2 on t1.id = t2.t1_id

The query will give you the following columns in your result set:

id, desc, id, t1_id, desc

The simple solution is to basically NEVER use "*" ... always SPECIFY which columns you want .. and then you can also ALIAS those columns..

select
  t1.id AS t1_id,
  t1.desc AS t1_desc,
  t2.id AS t2_id,
  t2.desc AS t2_desc
from t1
join t2 on t1.id = t2.t1_id;

This query will return the following column names

t1_id, t1_desc, t2_id, t2_desc

One major problem with using "*" in your queries is that if the table structure ever changes, it will affect every single query that is using that table, which will often times break code.  Where as if you are specifying the column names, the chances of that happening are significantly reduced.  The resulting error messages that the database will give you are also a lot easier to understand when it comes to "what broke?"


The formula is correct.
The number of live rows is taken from the reltuples column in pg_class, and the result of the formula is compared to n_mod_since_analyze from pg_stat_all_tables.
Note that autovacuum_analyze_scale_factor and autovacuum_analyze_threshold can be overridden by storage parameters on th table.
If all available autovacuum workers are running (autovacuum_max_workers), you may have to wait.
Analyze only takes an ACCESS SHARE lock on the table (it only reads), so it can run concurrently with almost anything. Like a SELECT statement, it will see the rows that are visible to it.
If you need a table to be analyzed between an INSERT and an UPDATE, don't wait for autoanalyze to run. Explicitly start ANALYZE on the table.


My best recommendation for you is to use already applauded backup solution from Ola

CleanupTime
Specify the time, in hours, after which the backup files are deleted. If no time is specified, then no backup files are deleted.

I normally, use backup database ... with init and then delete the backup files older than X days.
Ola's solution is a one stop solution and its very flexible, so you can tune it as per your needs.


You could enable binary logging. If you have not done so already.
To find out if binary logging is enabled, run this:
SHOW MASTER STATUS;

If you get back, Empty set, it is not enabled.
If you have not, please add this to my.cnf under the [mysqld] section
[mysqld]
log-bin = mysql-bin

Then, restart mysql.
From that point forward, you could look for all changes within timeframes.
For example, suppose you run SHOW BINARY LOGS; and get something like this:
mysql> show binary logs;
+----------------+------------+
| Log_name       | File_size  |
+----------------+------------+
| bin-log.000645 |    5933323 |
| bin-log.000646 | 1073745654 |
| bin-log.000647 | 1073759862 |
| bin-log.000648 | 1073757229 |
| bin-log.000649 | 1073776952 |
| bin-log.000650 | 1073791824 |
| bin-log.000651 | 1073743420 |
| bin-log.000652 | 1073785139 |
| bin-log.000653 |  338795678 |
+----------------+------------+
9 rows in set (0.00 sec)

You could then use the mysqlbinlog utility to dump a text representation of all the SQL commands executed within specified timeframes along with the timestamps.
The options to use are

--start-datetime
--stop-datetime

What to do next
Go find the folder where the binary logs are kept. From the command line run this
ls -l bin-log.*
total 7737092
-rw-rw---- 1 mysql mysql    5933323 Aug 17 17:26 bin-log.000645
-rw-rw---- 1 mysql mysql 1073745654 Aug 17 19:04 bin-log.000646
-rw-rw---- 1 mysql mysql 1073759862 Aug 17 20:45 bin-log.000647
-rw-rw---- 1 mysql mysql 1073757229 Aug 17 22:18 bin-log.000648
-rw-rw---- 1 mysql mysql 1073776952 Aug 18 00:05 bin-log.000649
-rw-rw---- 1 mysql mysql 1073791824 Aug 18 05:18 bin-log.000650
-rw-rw---- 1 mysql mysql 1073743420 Aug 18 08:59 bin-log.000651
-rw-rw---- 1 mysql mysql 1073785139 Aug 18 11:31 bin-log.000652
-rw-rw---- 1 mysql mysql  338795678 Aug 18 12:30 bin-log.000653
-rw-rw---- 1 mysql mysql        351 Aug 18 11:31 bin-log.index

The datetime stamp for each file represents the last time the binlog received SQL commands.
You would request the commands by the timeframe and by the binlog names ordered by timestamp
To see all SQL commands executed from midnight to 8:00AM today, you run this
SQLCMDS=/tmp/SQLCommands.txt
DT1="2014-08-18 00:00:00"
DT2="2014-08-18 08:00:00"
BINLOGS="bin-log.000649 bin-log.000650 bin-log.000651"
mysqlbinlog --start-datetime="${DT1}" --stop-datetime="${DT2}" ${BINLOGS} > ${SQLCMDS}

The output file, /tmp/SQLCommands.txt, will contain every INSERT, UPDATE, DELETE, and DDL command executed in the timeframe you requested. Just make sure you supply the necessary binlogs. If you include all binlogs, the mysqlbinlog dump will still work but would take longer because it would parse through binary logs it does not need (i.e., you don't need to parse bin-log.000645 - bin-log.000648, bin-log.000652, bin-log.000653).
Give it a Try !!!
CAVEAT
If you want to see everything, including the SELECTs, you go with Craig Efrein's answer.
UPDATE 2014-08-21 10:45 EDT
@CraigEfrein made a excellent point in his comment

Activating bin-logs also have an impact on performance and disk space.

To reduce disk I/O based on binary logging, you should config binlogs to be stored on a separate disk. See my old post How do I determine how much data is being written per day through insert, update and delete operations? (under the subheading SSD LIFESPAN)


Optimization for 100 GB Real-time info table

Assuming you have 300 Bytes per row, that makes a whopping 95GB per three weeks - that's not very much in today's terms - a 1TB disk would last 30 weeks - that's almost 1/2 of a year. If you compressed this data, I'm fairly sure that you could store at least a couple of years (possibly a lot more) on a single 1TB disk. I would keep the "live" data on one machine (or even disk) and the compressed data (> 3wks) on another machine (or disk) for analysis. How about the Archive engine for older data? Or maybe you could consider InfiniDB for analysis (or Infobright)?  These are columnar based stores optimised for data analysis.

[Edit] You might also want to look at this - link to data compression. I am implicitly assuming that you're on MySQL since you used TINYINT.


Creating a User to access multiple databases

I'm not certain about the service broker que but it is possible to setup a user without a login.  This user will not be able to authenticate but you can still assign the user rights.  The examples below are from the microsoft article for createing users http://msdn.microsoft.com/en-us/library/ms173463.aspx

D. Creating and using a user without a login

The following example creates a database user CustomApp that does not map to a SQL Server login. The example then grants a user adventure-works\tengiz0 permission to impersonate the CustomApp user.

USE AdventureWorks2012 ;
CREATE USER CustomApp WITHOUT LOGIN ;
GRANT IMPERSONATE ON USER::CustomApp TO [adventure-works\tengiz0] ;
GO

To use the CustomApp credentials, the user adventure-works\tengiz0 executes the following statement.

EXECUTE AS USER = 'CustomApp' ;
GO

To revert back to the adventure-works\tengiz0 credentials, the user executes the following statement.

REVERT ;
GO


Finding list of Duplicate records from 2 tables in DB2

SQL is closed so the result of a query is a new table that can be futher used. You can therefore join the result of your duplicates query with the original tables. Something like below should work:

select t2.*
from (
    SELECT  A.FIRST_NAME, A.LAST_NAME, B.ADDR, B.ZIPCODE
    FROM  SCHEMA.PERSON A
    JOIN  SCHEMA.ADDRESS B
        ON A.ADDR_ID = B.ID
    group by A.FIRST_NAME, A.LASTT_NAME, B.ADDR, B.ZIPCODE
    having count(1) > 1
) as t1
join (
    SELECT  A.*, B.*
    FROM  SCHEMA.PERSON A
    JOIN  SCHEMA.ADDRESS B
        ON A.ADDR_ID = B.ID
) as t2
     on (t1.FIRST_NAME, t1.LAST_NAME, t1.ADDR, t1.ZIPCODE)
      = (t2.FIRST_NAME, t2.LAST_NAME, t2.ADDR, t2.ZIPCODE)

Using a CTE will probably be a bit more efficient:

 with t as (
    SELECT  A.*, B.*
    FROM  SCHEMA.PERSON A
    JOIN  SCHEMA.ADDRESS B
        ON A.ADDR_ID = B.ID
)
select t1.*
from t1
join (
    SELECT  FIRST_NAME, LAST_NAME, ADDR, ZIPCODE
    FROM  T1
    group by FIRST_NAME, LASTT_NAME, ADDR, ZIPCODE
    having count(1) > 1
) as t2
    on (t1.FIRST_NAME, t1.LAST_NAME, t1.ADDR, t1.ZIPCODE)
     = (t2.FIRST_NAME, t2.LAST_NAME, t2.ADDR, t2.ZIPCODE)

I replaced the "," joins with explicit ones, I also used * since I did not know the names of the additional columns.


The column aliases there override the column names/aliases of the internal select subquery (derived table). The same way, they can override the column names of the table (whether it's base table, a view, a derived table or a cte does not matter at all).

So, the simple example will give an error:

select
    a, count_a              -- invalid here (have been overridden)
from
    ( select t.a, count(*) as count_a
      from t
      group by t.a
      order by count_a desc           -- count_a is valid here
      limit 8
    )
      as d (b, count_b) ;

but this will work:

select
    b, count_b              -- valid column aliases
from
    -- identical as above

the names a and count_a are valid inside the subquery (derived table) but not outside because they have been overridden by b and count_b.

  Do you have to know the exact sequence of columns defined in the table, or can you set an alias just for one or two of these in the FROM clause?

Yes, you do have to know the sequence of columns.

But you don't have to change all columns. Say the table has 5 columns. If you use:

select t.*
from table_name as t (a,b,c) ;

only the first 3 columns will appear with the new names (a,b,c). The 4th and 5th will show with their real names. You'll get an error if you provide more aliases than needed (eg. 6 aliases for a 5-column table).

  What if you only want to set a column_alias for one column with a very long name (and leave the other columns not aliased); is this possible? (If so, is this Postgres specific?)

Only if it's the first. Or by providing all the previous column names up to the column you want to alias with a different name.

  I suppose you can't just give an alias for the third column only, or something like that?

I don't know of any syntax to allow you to alias only the 3rd column, without providing the names of the 1st and 2nd column.

Overall, the usefulness of the feature is at least debatable when used for base tables. And the above query that overrides the names of just 3 of the possibly many columns reeks obfuscation and could be very well considered bad practice.

But the feature is provided because it's standard SQL and for completeness. It wouldn't make sense to have this only for subqueries and CTEs and not for other kinds of tables.

One case where it can be useful is (not with base table but with) the VALUES construct, where the columns get default names of column1, column2, etc. and this aliasing can be used to select more meaningful names:

select
    a, b
from
    ( values
         (1, 2),
         (2, 3),
         (3, 5)
    )
      as d (a, b) ;


How to prevent data loss during intra-site failover

The crux of your issue is:

  Given two physical sites, each site has two SQL servers with requirement that at any given time application is able to access one of its local SQL server instance to write/read data. Then the data need to be synchronized cross site and all four servers will contain synchronized data. Which SQL server’s HA solution or replication solution or combination of both should we pick to achieve such result?

The "one size fits all" answer is, the current offerings that SQL Server has won't fit into this. SQL Server doesn't work well in a multi-master zero data loss game. Other database solutions have similar issues where multi-master has the potential (like SQL Server) for data loss. I don't believe you're going to get a single bullet one size fits all solution here from the database layer itself.

The only way, with SQL Server, to get what you're asking for it to create the application or middle tier around this philosophy and put the onus on the application/middle tier to make sure all databases are in sync and let the application/middle tier determine how to handle what happens when they aren't (for example, a write fails on one database but 3 the other databases successfully committed - do you proceed?).


Preventing duplication on a Postgres table with 2 columns

Enforce a particular canonical ordering on the values:

alter table sometable add constraint sdljfsdfj check (col1<=col2);

If they are in the wrong order, the check constraint will reject them.  They are in the correct order the unique constraint will.

You could couple this with a BEFORE INSERT trigger to swap the order, if you don't want to force the application to deal with that logic.


How to trust trust a document in access via PowerShell so I can trigger my database export marcos

Microsoft Office documents, including Microsoft Access .accdb databases, need to reside in a "trusted" location in order for macros and VBA functions to run without requiring intervention from the user.

Add the desired target location to the list of trusted locations by making a change to the system registry:

HKEY_CURRENT_USER\Software\Microsoft\Office\15.0\Access\Security\Trusted Locations

See this StackOverflow post for further details.


Downloading files based on most recent date and timestamp

As the answer by @MarkSinkinson shows, you can use WinSCP .NET assembly.

There is an official example on WinSCP site for downloading the most recent file.

As it shows, to select and download the most recent file, use:

// Get list of files in the directory
RemoteDirectoryInfo directoryInfo = session.ListDirectory(remotePath);

// Select the most recent file
RemoteFileInfo latest =
    directoryInfo.Files
        .Where(file => !file.IsDirectory)
        .OrderByDescending(file => file.LastWriteTime)
        .FirstOrDefault();

// Any file at all?
if (latest == null)
{
    throw new Exception("No file found");
}

// Download the selected file
session.GetFiles(RemotePath.EscapeFileMask(remotePath + latest.Name), localPath).Check();

For a full code, see the link.

See also the article on using the WinSCP .NET assembly from SSIS.

(I'm the author of WinSCP)


MySQL's "slow query" log: strange "Query_time"

Ah, I should have checked that first:

log_queries_not_using_indexes has been turned on, so a whole number of queries has been logged because they were not using indexes.

Apparently, a query "not using indexes" is not marked in any way in the slow query log, so one has to guess whether an entry has been logged because of an execution time exceeding the long_query_time threshold or because of indexes.


Rename interval partitioning system generated partition names

There's a trick you can use, at least for date-range partitions: the HIGH_VALUE field in user_tab_partitions is a valid date expression. If you "eval" that, you'll get a date object for the cutoff point for that partition. You can then use that to build your partition name from.

Sample PL/SQL to try out (generates a string from the high_value column):

declare
  hv varchar2(9);
begin
  for x in (select partition_name, high_value
              from user_tab_partitions
              where table_name = 'FOO' and partition_name not like 'PART_%')
  loop
    execute immediate 'select to_char('||x.high_value||'-1,''YYYYMMDD'') from dual' into hv;
    dbms_output.put_line('alter table foo rename partition '||x.partition_name
                       ||' to PART_'||hv);
  end loop;
end;
/

(Replace the put_line with execute immediate once you've verified that it actually does what you want. The -1 in the date select is to make it more "human friendly", i.e. partition name to match the date it contains.)

As for dropping old partitions, you can use the same trick. Same type of loop to get a date object for each partition, and generate an alter table ... drop partition ... statement for the partitions you don't want to keep.

Simplistic example where you would drop partitions older than 10 days (be vary wary of off-by-one errors, there probably is one below):

declare
  dt date;
begin
  for x in (select partition_name, high_value
              from user_tab_partitions
              where table_name = 'FOO')
  loop
    execute immediate 'select '||x.high_value||' from dual' into dt;
    if dt < sysdate - 10 then
      dbms_output.put_line('to drop: '||x.partition_name);
    end if;
  end loop;
end;
/

You're then left with taking care of index maintenance if necessary.


Is it possible to keep track of which rows were selected and fetched last in MySQL?

Although you can track which rows were selected last using MySQL Enterprise Audit, this would be a little like using a Saturn V rocket to go to the corner store... The cost would be ridiculously high for such a small function.

Since you are ordering rows by timestamp, I am assuming your timestamp value is unique. Wouldn't adding a select clause like where <mytimestampcolumn> < [smallest timestamp column value from last result set]; take care of the requirement?

This would have the additional benefit of making your application scale better relative to other alternatives, such as retrieving the complete result into a cursor and having the servlet fetch the result set 5 rows at a time.


Option 1: Use a different account which have only sufficient permission. Don't use DBA rights account until that is required.

Option 2 : enable the auditing to track all the changes in databases and made by whom. I hope this will full fill the compliance requirement.


How to get all tables in informix database and their sizes in GB?

There is a lot of nuances to this question.  Like do you want the size of the total number of pages currently allocated for use (so this would include pages available to be used by the table...so pages reserved for use by only this table, but that currently don't have any data on them), or do you only want the size of the currently used pages?  Do you want to include the size of any indexes that would be defined on the the table?

So here is a query that could be used that would get the size of any non-system catalog table in the current database connected to that is the size of all the space reserved for the table, not including detached indexes:

    select a.tabname, (b.nptotal * b.pagesize / 1024 / 1024 / 1024) as tabsize
    from sysmaster:systabnames a, sysmaster:sysptnhdr b, systables c
    where c.tabname = a.tabname and
    a.partnum = b.partnum and
    c.tabid > 99;

Sample output from my tiny test system (note that t3 is a fragmented table where 1 fragment has no data, so you could use sum and group by if you wanted to report at a table level rather then fragment level if the your schema included fragmented tables)

    tabname  t1
    tabsize  1.5258789063e-05

    tabname  t2
    tabsize  1.5258789063e-05

    tabname  t3
    tabsize  0.00000000000000

    tabname  t3
    tabsize  1.5258789063e-05

If you change the query to the following, it would change the results to only count the pages that are currently used for the table, not all the space that is reserved for use:

    select a.tabname, (b.npused * b.pagesize / 1024 / 1024 / 1024) as tabsize
    from sysmaster:systabnames a, sysmaster:sysptnhdr b, systables c
    where c.tabname = a.tabname and
    a.partnum = b.partnum and
    c.tabid > 99;

If you want the sizes of detached indexes to be included in the table sizes, you would then have to include sysfragments into the query as well (would probably be a union of 1 form of the above 2 queries along with something that then got the index partition information by also then including the sysfragments into the join as well)


MySQL / MariaDB SSL Connection without verify CA

Its possible to authenticate with SSL without verify the client certificate with server CA (ssl_ca) ?

No, you will need to create a CA, generate a Certificate Generation Request (CSR), then generate a client certificate from the CA.  Both sides of the connection will need the client cert and the CA.

You can reuse the same client certificate for all clients.  Or, when creating the user you can use REQUIRE CN= to enforce the user to connect with a specific certificate (this requires a different step when generating the client cert).

I recommend using passwords as well for authentication
Pay attention to the expiration date of your CA.  Updating the CA or client certs requires a restart of MySQL
You can have more than one CA in your ssl_ca file to allow for a transition period if you are required to rotate CAs.
The CN for your CA cannot be the same as your client cert.  You will tear your hair out trying to figure out what you did wrong if you do this.


How to know max specified memory in Oracle XE11g release 2?

The documentation is clear about this. See Oracle Database Licensing Information

  Oracle Database Express Edition  Oracle Database Express Edition
  (Oracle Database XE) is an entry-level edition of Oracle Database that
  is quick to download, simple to install and manage, and is free to
  develop, deploy, and distribute. Oracle Database XE makes it easy to
  upgrade to the other editions of Oracle without costly and complex
  migrations. Oracle Database XE can be installed on any size machine
  with any number of CPUs, stores up to 11 GB of user data, using up to
  1 GB of memory, and using only one CPU on the host machine. Support is
  provided by an online forum.

The 10g documentations shows explicitly: 1 installation, 1 instance on a machine. Compared to 10g in 11g you can use more userdata on disk.


Why doesn't pgAgent run my job every minute?

pgAgent updates the pgagent.pga_job row for the job once the scheduled job completes.  pgAgent will then only run the job a minimum of 1 minute later.  The thread for jobs that are scheduled to run "every minute" looks for new jobs to run once per minute, at the start of each minute.  So, if your job completes at 13:01:26, the next time the schedule runs will be 13:02:00, however since the job completed 34 seconds ago, it does not run the job.  The next time the scheduler runs, at 13:03:00, it sees the job ran more than one minute ago, and runs the job.

The pgAgent source code for pgagent.pga_next_schedule notes this:

-- Get the time to find the next run after. It will just be the later of
-- now() + 1m and the start date for the time being, however, we might want to
-- do more complex things using this value in the future.
IF date_trunc(''MINUTE'', jscstart) > date_trunc(''MINUTE'', (now() + ''1 Minute''::interval)) THEN
    runafter := date_trunc(''MINUTE'', jscstart);
ELSE
    runafter := date_trunc(''MINUTE'', (now() + ''1 Minute''::interval));
END IF;


Range Optimizer in Mysql: What is an Index dive during Query Optimization?

To "dive" is to drill down the BTree from the 'root' down to the leaf node for a particular key.

Let's say you have a non-unique INDEX(x).  Further, let's say that there are 100 rows with x=123.  WHERE x = 123, the two dives would be to "find the first index entry for x=123" and to find the "last" such row.

His first use of "accurate" (in the article) was to say that the dives are more accurate than using statistics that may be stale.

I suspect his use of "accurate" is an exaggeration in the other uses.  I say this because I cannot believe that counting the rows between the first and last dive is no more than an estimation.  To get the exact number would require fetching each block -- this is too costly.

Note the 100 rows might be in the same block, or might (if the rows are wide) be spread across multiple blocks.  For the purposes of EXPLAIN and query planning, an estimate is considered "good enough".  The exact ("accurate") number can be quickly counted if the start and end are in the same block, but, if spread across multiple blocks, I think then it would only estimate the row counts for the intermediate blocks.

The "dives", even if they involve a disk-read, are usually not wasted.  This is because you will (presumably) then proceed to perform the query and need those block(s) anyway.

Side note:  Since Jørgen Løland wrote that article, the Optimizer has employed a "cost basis" algorithm.  However, the "dives" are still important in it.


In general you can replace a NOT IN (...) subquery with a NOT EXISTS (...) and the latter will be faster. Logically they are the same though some database optimizers handle the NOT EXISTS better as they can stop the subquery check after the first match.

In contrast, if the DB optimizer isn't smart enough to figure out what your doing and the subquery has a lot of rows, then it must buffer all the rows for each subquery before performing each NOT IN check.

Try out something like this:

SELECT *
FROM table1 a
WHERE NOT EXISTS(SELECT 1
                   FROM table2 b
                  WHERE b.user_id = 2
                    AND b.index_field = a.field1)

In this example the optimizer need only check if a single row exists for the matching subquery, including the additional filter on b.user_id = 2. It should use an index on the index_field and user_id if they are available.


max_wal_size is a soft limit - under many scenarios it gets crossed.
Specifically as for pg_start_backup it just commits and outputs the wal location - if you have archiving in place than you have nothing to worry about.

Starting the backup will create more wal files but doesn't prevent rolling them - that's why you need the archiving as all wal files generated in the duration of the backup are needed for a successful restore.

See: max_wal_size documentation
Also: backup documentation


Oracle ASM : Alter asm_diskstring when ASM Instance is Down,

You can create pfile to set asm_diskstring.

$ vi asmpfile.ora
instance_type=asm
asm_diskstring=path_to_asmdisks

Then can start asm instance using this pfile and later can create spfile from pfile.


This should get you going - basically what irimias did at this link

Declare @Table table (FirstName varchar(100), LastName varchar(100))
DECLARE @ConcatString varchar(100)

insert into @Table (FirstName,LastName) values('TomFirst','TomLast'), ('DickFirst','DickLast'), ('HarryFirst','HarryLast')

SELECT @ConcatString = isnull(@ConcatString + ' AND ', '') + FirstName + ' ' + LastName
from @Table

print @ConcatString


You can use window functions to achieve your goal.  lag() and lead() are ones which can help you in a query like

SELECT lag(di_timestamp) OVER ordering, lead(di_timestamp) OVER ordering
  FROM data_item
 WHERE fk_fc_id IN (35246,35247)
WINDOW ordering AS (ORDER BY di_timestamp
                    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);

This will return the previous and the next timestamps, if there is any.


How can I run a pgAgent job every 10 second?

As it is using a cron-style schedule definition it is unlikely to support sub-minute granularity as most (all?) cron implementations don't.

A hack I've used to run a task more often than every minute with cron is a script that cron calls once per minute, which performs its task, sleeps a bit, then repeats. For example to run for times per minute: dotask; sleep 15; dotask; sleep 15; dotask; sleep 15; dotask;. You could do something similar with pg_sleep. There are a few caveats to watch out for including:

Your task may run a few more times after you stop it via your schedule configuration.
If the task takes an appreciable amount of time you need to adjust for this in your sleep durations. If the time it takes is variable the you need to be brighter than a simple static sleep call: for the 4/minute example your first sleep would be for "15-minus-current-seconds-on-wall-clock seconds", the second for "30-minus-current-seconds-on-wall-clock seconds".
If the task ever takes longer than your target timing granularity to complete, you have the potential problem of two instances trying to run at the same time which might cause locking issues and/or bog your database down.

Another option is to define multiple tasks all set to go off on the minute. The first runs immediately, the second sleeps for  seconds before running the actual work, the second sleeps for *2, etc. So for running the task four times per minute via cron you'd have:

* *    * * *    dotask.sh
* *    * * *    sleep 15 && dotask.sh
* *    * * *    sleep 30 && dotask.sh
* *    * * *    sleep 45 && dotask.sh

(where 5 *s means "run every minute" in cron's config files)
This removes the exactly-how-long-to-sleep-for problem (the second issue above), but potentially makes the multiple-instaces-running-at-once matter (issue three above) more of a concern. Both options are somewhat hacky...


SQL-Server: What is a good way to aggregate on different columns within the same query?

You could also use window functions:

Select Category,
    Count(Category) As [Count],
    Count(Category)*100.0 / Sum(Count(Category)) Over () As Perc
  From TableA
  Group By Category;


SQL Error [1292] "Truncated incorrect INTEGER value" - with Varchar columns and CASE. Why?

Seems like a bug in mysql5.7 - it works both in mysql5.6 and mysql8.0.
I have no idea why it can be like that, but I was able to reproduce that with simpler insert statement:
insert into TGT(C)
select
    CASE
        WHEN if(Z in ('XYZ'), A, NULL) IS NULL THEN NULL
    END AS C
from SRC;

Seems like some implicit conversion makde by mysql. Forcing explicitly convert helps:
insert into TGT(C)
select
    CASE
        WHEN if(Z in ('XYZ'), convert(A, char(63)), NULL) IS NULL THEN NULL
    END AS C
from SRC;

(but converting whole case..end does not.


Two queries usually run fast but get slow when joined

Change the line

UNIQUE KEY media_id (user,media_id)

to

UNIQUE KEY media_id (media_id)

then execute the query it will perform better


Alternative to EAV for dynamic fields in a star schema data warehouse

I can think of three solutions - EAV, XML, and Sparse Columns. The latter is vendor-specific and may not be useful to you.

Whichever method you choose, you may wish to consider storing the original request data in a raw format, in a table or flat file. It will make it easy to try new ways of storing the data, allow you to reload data if you discover a mistake with the way you're parsing your requests, and offer opportunities for parsing the API requests using batch processing or "big data" tools if you find that your data warehouse isn't able to efficiently deal with the data.

EAV considerations

EAV/KVS, as you've described it above, is likely to be the most straightforward implementation.

Unfortunately it's also going to be very expensive - to get any sort of efficient queries on commonly used keys you'll need to have indexes on the key column, which could get very fragmented. Querying for particular keys would be extremely expensive.

You may be able to reduce the cost of indexing or index scans by supporting your EAV store with materialised views (many vendors support this) for querying keys or values that you care about.

XML

Most enterprise database systems offer very mature XML handling, including validation, indexing, and sophisticated querying.

Loading the API request into the database as XML would provide one tuple per request, which logically might be a bit more palatable to you than having an unknown number of rows in an EAV table.

Whether this is efficient would depend a lot on your RDBMS vendor and your implementation.

The biggest downside is that this is probably the only way of managing data that's more complicated than string manipulation of the original request!

Sparse Columns / traditional tables

It's possible that you could load your data into a traditional table structure, with one column per key.

SQL Server's Sparse Columns feature is a great alternative to an EAV store. A table with Sparse Columns behaves much the same as a normal table, except that it can have up to 30,000 columns, and NULL values in sparse columns consume no space in the table.

Combining them with Filtered Indexes (another SQL Server specific feature) can provide an extremely efficient alternative to an EAV store if you're frequently querying for a couple of specific columns and/or values.

Using a traditional table with other vendors may be viable - IBM supports over 700 columns per table and Oracle about 1000, and features such as compression or Oracle's treatment of trailing nulls might mean that you can store your API data fairly efficiently.

The obvious downside of this approach is that as you added new keys to your API, you'd need to adjust your schema accordingly.


I am quoting from the official MySQL 5.7 documentation in my answer. This question has an evolving state and (hopefully) will contain as much information as possible.

mysqld, also known as MySQL Server, is the main program that does most of the work in a MySQL installation. MySQL Server manages access to the MySQL data directory that contains databases and tables. The data directory is also the default location for other information such as log files and status files.
When MySQL server starts, it listens for network connections from client programs and manages access to databases on behalf of those clients.

The mysqld program has a lot of options that can be specified at startup. A complete list of options can be retrieved when you run this command:

shell> mysqld --verbose --help

The MySQL documentation recommends that:

When troubleshooting, it is usually best to run the MySQL server from the command prompt, rather than through mysqld_safe or as a Windows service

If you want to see what the mysqld service starts or touches during the start-up procedure, you can start mysqld directly at the command prompt with:
shell> mysqld

You might find information during the startup procedure, which are causing your performance issues.
If you are using the default innoDB configuration you will make use of the InnoDB Temporary Table Undo Logs which are part of the Temporary Tablespace which is initialised during the start-up process:

The temporary tablespace is recreated on each server start and receives a dynamically generated space ID, which helps avoid conflicts with existing space IDs.

Temporary table undo logs have restrictions:

Temporary table undo logs, introduced in MySQL 5.7.2, are used for temporary tables and related objects. This type of undo log is not a redo log, as temporary tables are not recovered during crash recovery and do not require redo logs. Temporary table undo logs are, however, used for rollback while the server is running. This special type of non-redo undo log benefits performance by avoiding redo logging I/O for temporary tables and related objects. Temporary table undo logs reside in the temporary tablespace. The default temporary tablespace file, ibtmp1, is located in the data directory by default and is always recreated on server startup. A user defined location for the temporary tablespace file can be specified by setting innodb_temp_data_file_path.
32 rollback segments are reserved for temporary table undo logs for transactions that modify temporary tables and related objects, which means that the maximum number of rollback segments available for data-modifying transactions that generate undo records is 96. With 96 available rollback segments, the limit on concurrent data-modifying transactions is 96K. For more information see Section 15.3, “InnoDB Multi-Versioning” and Section 15.8.8, “Limits on InnoDB Tables”.

You might be hitting an issue where the number of transactions creating temporary objects is above this 96K level of connections. Or you might be just generating too many temporary objects.
Restarting your MySQL instance will (according to the Temporary Tablespace documentation) clean up your temporary tablespace and all temporary tables locate therein:
The temporary tablespace is removed on normal shutdown or on an aborted initialization
Given the information provided, I would suggest you to check your temporary objects, because you might have a problem in your temporary tablespace.


If you are using a MySQL ODBC connector in SSRS you can't use named parameters but you should use ? instead.

So for your query it should look like:

SELECT DISTINCT CompanyId, Company
FROM            User_Hierarchy_Mapping
WHERE           UserId = ?

That way you should get a parameter named Parameter1. If you have more than one question mark your parameters will be named Parameter1, Parameter2 etc in the same order as they appear in the query.

This isn't specific to MySQL but to any ODBC connection. See Binding Parameters ODBC for reference.


What should I do with mongodb security installed in aws ec2?

Here is a few things to read up upon:
https://docs.mongodb.com/manual/administration/security-checklist/

In your mongo.conf its good to disable the REST API and http interface.

Use a non default Mongo port e.g not 27017 and use AWS security groups to only allow that port to a given IP not to the world.


It's actually usually referred to as SQLOS and it means SQL Server Operating System.

Details in this series

Also it can be found referenced in documentation in some places, e.g. here:

  [...] associated with the SQL Server Operating System (SQLOS). The SQLOS is responsible for managing operating system resources that are specific to SQL Server.


I'm building up a large MyISAM table from a few dumps. The first 21GB took less than 10 hrs total. The next 5GB is forecasted to take two months

After a bit more research, I ran "OPTIMIZE table_name", and now the restore that would have taken two months is taking half an hour.  According to the docs, the following is a good time to do run OPTIMIZE:

  After deleting a large part of a MyISAM or ARCHIVE table, or making
  many changes to a MyISAM or ARCHIVE table with variable-length rows
  (tables that have VARCHAR, VARBINARY, BLOB, or TEXT columns). Deleted
  rows are maintained in a linked list and subsequent INSERT operations
  reuse old row positions. You can use OPTIMIZE TABLE to reclaim the
  unused space and to defragment the data file. After extensive changes
  to a table, this statement may also improve performance of statements
  that use the table, sometimes significantly.

I didn't do any deletions (does "changes" include insertions?), but I do have big VARCHAR fields.

from here: http://dev.mysql.com/doc/refman/5.1/en/optimize-table.html


Oprhaned entries in msdb..sysjobactivity

A StackOverflow answer has a possible explanation:

https://stackoverflow.com/questions/13037668/what-does-it-mean-to-have-jobs-with-a-null-stop-date/13038752#13038752

To quote:

  Each time the SQL Agent starts, it puts a new row in syssessions and
  subsequently any jobs run will get that session_id in sysjobactivity.
  For your jobs that have a null stop date, my guess is that they're not
  for the "current" session which would mean that they were still
  running when the agent was stopped.

I had a similar issue where two entries in sysjobactivity appeared to be "stuck", with a start_execution_date from six weeks back and no stop_execution_date.  Following the suggestion in the quote above I checked the session_id for each of those stuck records and found in each case the stuck records were from previous sessions.

The following StackOverflow answer shows how to get only the activity for the current session:

https://stackoverflow.com/a/18062236/216440

Their code:

SELECT
    job.name,
    job.job_id,
    job.originating_server,
    activity.run_requested_date,
    DATEDIFF( SECOND, activity.run_requested_date, GETDATE() ) as Elapsed
FROM
    msdb.dbo.sysjobs_view job
JOIN
    msdb.dbo.sysjobactivity activity
ON
    job.job_id = activity.job_id
JOIN
    msdb.dbo.syssessions sess
ON
    sess.session_id = activity.session_id
JOIN
(
    SELECT
        MAX( agent_start_date ) AS max_agent_start_date
    FROM
        msdb.dbo.syssessions
) sess_max
ON
    sess.agent_start_date = sess_max.max_agent_start_date
WHERE
    run_requested_date IS NOT NULL AND stop_execution_date IS NULL


can't execute opatch even I'm in the oinstall group with -rwx--x---

First, let me state that I think that running anything as the non-oracle user on your filesystem may result in significant database issues! (I'll explain why in a minute)

Aside from the pathing issue, which you solved, I think that Colin is correct. You (or your admin) does need to give read on the binary:

[root@db01 ~]# useradd captjackvane
[root@db01 ~]# groups captjackvane
captjackvane : captjackvane
[root@db01 ~]# usermod -a -G oinstall captjackvane
[root@db01 ~]# groups captjackvane
captjackvane : captjackvane oinstall

[captjackvane@db01 OPatch]$ ls -ltrh opatch
-rwx--x--- 1 oracle oinstall 31K Mar 26  2013 opatch
[captjackvane@db01 OPatch]$ ./opatch lsinventory
/bin/sh: ./opatch: Permission denied

oracle@db01:(psu1) ~]$ chmod 750 ./opatch
oracle@db01:(psu1) ~]$ ls -ltrh opatch
-rwxr-x--- 1 oracle oinstall 31K Mar 26  2013 opatch

[captjackvane@db01 OPatch]$ ./opatch lsinventory
chmod: changing permissions of `/u01/oracle/app/product/db_1/.patch_storage': Operation not permitted
The chmod command failed for ./u01/oracle/app/product/db_1/.patch_storage.. It is possible that you have no permission to access it. OPatch cannot proceed!
OPatch returns with error code = 1

As you can see, I can now "execute" the binary, but other permissions issues remain. So here's where I get to my first point:

Running things, especially anything that stops/starts a database and/or the cluster as someone other than the Oracle user can lead to serious issues. I remember in my career-youth I accidentally started a development database as my physical user, instead of Oracle. Which meant that many (but not all) of the processes used by the database had my name on them (and by extension, my permissions) instead of Oracle.

Your best bet here is to use the Oracle user, or ask your admin to complete whatever the task is for you.


What does MySQL actually use to decide which index should be used?

What is in the WHERE clause.
An estimation of how many rows will be fetched -- to decide whether to use an index, or simply scan the table.
Size of two tables in a JOIN -- if there is no other good way to decidw which table to start with.
The value of N in a LIMIT rarely matters.
Whether the columns's datatypes match -- mismatch of COLLATION may inhibit use of an index.
Not used: whether the needed blocks is already cached in RAM.
Not used: Spinning disk vs SSD.  (There is a tunable in 8.0 to help.)
In certain situations, an index is dynamically created for a subquery.
Size of the index.
"Cost" of the various options.
ORDER BY is used only if the entire WHERE and GROUP BY are handled first.  Or the Optimizer may skip the WHERE and only use an index for the ORDER BY, thereby avoiding 'filesort'.
GROUP BY and ORDER BY, of not compatible enough may lead to two filesorts.

See EXPLAIN FORMAT=JSON SELECT ... for some extra clues.  See also the "Optimizer trace".

My cookbook approaches your question in a different way...  "Given this SELECT, what is the best INDEX for it?"


I've found the problem.

The doc ID 1597395.1 says:

  The problem occurs when Datapump Export is being performed on a
  multi-node RAC where the dumpfile destination is not shared to all
  nodes for access.  Since multiple nodes will be running the Datapump
  job, ALL nodes must have access to the mount point where the dump file
  will be written.

So I've just used the parameter cluster=n in export to resolve it.


VLDB with large quantity of data file imports

Below are general recommendations and your milage may vary depending on you workload running and your IO subsystem, server hardware configuration, etc.

At SSIS level, you can look into below things to speed up data read and data load :

Fast Parse Option along with its limitations.
Use the SQL Server Native Client 10.x OLE DB provider for an In-Memory, high performance connection
Set the Packet Size to 32767
Select the OLE DB Destination  Data Access mode “Table or View – fast load”  option

Refer to Speeding Up SSIS Bulk Inserts into SQL Server for more details.

Now coming to SQL Server - optimizing data loading :

Use Trace Flag 610 : inserts into a table with indexes will generally be a minimally logged operation. Tables with clustered indexes support multiple bulk load stream inserting concurrently, as long as these streams contain non-overlapping data. If data overlap is detected, streams will block (but not deadlock).
Look for table partitioning (Partitioned Tables and Indexes in SQL Server 2005) especially sliding window technique.
Look for -E startup parameter as well.

Look for optimizing network configuration as well :

Use fast NIC and switches.
Have the latest certified drivers for your
NIC.
Enable full duplex.
Enable support for Jumbo frames. Use TCP
Chimney Offload. Use Receive Side Scaling (RSS).

Also, you should monitor Wait Statistics on the server especially SOS_SCHEDULER_YIELD resulting in scheduler contention on Servers having multiple CPUs running concurrent Bulk load operations and competing for the same CPU Cycles.

Refer to this excellent whitepaper The Data Loading Performance Guide which have everything that I mentioned with diagrams and examples.

Also, to automate the sliding window technique - creating staging tables, loading data into it and then switching the partitions, you can fully automate it using  SQL Server Partition Management -- it has command line option as well -- available at CodePlex.


ALTER DATABASE MODIFY FILEGROUP [myfilegroup] AUTOGROW_ALL_FILES

Complementing to both Max and Kenneth's answers ..

I would suggest you to apply SQL Server 2016 CU1 as there is a known bug with AUTOGROW_ALL_FILES property -

  The property isn't preserved after the database or the server restarts. The sys.filegroups viewstill shows that the corresponding column for the property (is_autogrow_all_files and is_read_only) is set to 1. However, it's not actually effective.

Also, its important to note that in SQL Server 2016 you dont need TF 1117 since is_autogrow_all_files is equivalent of TF1117


Can SQL Server 2014 Standard Edition Trial be upgraded to a full Core license?

Do you mean upgrade from Evaluation to the full Enterprise?  If so, then yes that is supported.  Take a look at the link here on BOL to show supported edition upgrades (scroll towards the bottom, it has SQL Server 2014 upgrades between editions and what's possible).


First of all you need to edit/create the /etc/sysctl.conf and insert the following:

 kern.sysv.shmmax=1073741824
 kern.sysv.shmmin=1
 kern.sysv.shmmni=4096
 kern.sysv.shmseg=32
 kern.sysv.shmall=1179648
 kern.maxfilesperproc=65536
 kern.maxfiles=65536

reboot. Then google db2 express c mac osx and download db2 express c 9.5.2 for mac osx. Once downloaded uncompress the file

tar -xzvf db2exc_952_MAC_x86_64.tar.gz

enter the exp directory and start the installation wizard as root

cd exp
sudo ./db2setup

before continuing you need to create 2 users, one for administration purposes and another one as instance owner. For the second user name use no more than 8 characters and use just letters, otherwise the node name won't be accepted later on. Follow the installation wizard steps: enter the username for the DAS and for the instance owner. Once the installation is complete, you need to switch to the instance owner (dbuser in our case):

su dbuser

add the following line to your .bashrc file:

. ~/sqllib/db2profile

exit and relogin. If everything went well you should be able to start db2 (as a root) and create the SAMPLE database

sudo db2start
db2sampl
db2

That's it!


Error while importing sql - Specified key was too long; max key length is 1000 bytes

According to the MySQL Documentation on CREATE INDEX:

  Prefix support and lengths of prefixes (where supported) are storage engine dependent. For example, a prefix can be up to 1000 bytes long for MyISAM tables, and 767 bytes for InnoDB tables.

My guess would be the character set you are using in conjunction with

UNIQUE KEY  `uemail` (  `uemail` ,  `uname` ) ,

Please note that both of those columns are VARCHAR(255). Together their maximum character length could be 511 (255 characters X 2 + 1 byte for VARCHAR length management). Since UTF8 is a multibyte character set, 511 * 2 = 1022. That's way past InnoDB's limit of 767 bytes. Even if you convert the table to MyISAM, it's still past 1000 bytes.

I would suggest drastically reducing the widths of uemail and  uname.


Very slow Insert and update on 20 core 250 Gb RAM dedicated MYSQL 5.5 server on Ubuntu

EDIT: after reading threw it, I noticed that your limiting it to 16 threads?
"innodb_read_io_threads = 16"
your server has 20 cores of cpu, and therefor you will only use a very small amount of CPU, try adding more threads and report back if it makes any difference,other then that i can't think of any thing else!

it may be due to server load, the fact that you have 20 cores may be useless, if the program can't multithread, whats your specs and general overall server load? other then that, I can't really think of any thing else that may be causing this.


IN SSIS, you can probably set up a lookup step with the foreign key data from table 1; rows that don't match could be routed into an error file, and the rest routed into the table.

Alternately, load the data into a staging table without the foreign key constraint, and run a script/stored procedure to validate the data in the staging table, including making sure the foreign keys are present.


Better way for multiple joins on same table but different id ranges

The LEFT joins are actually INNER joins, as all the tables in the right sides have columns that appear in WHERE clause. So you could change them - just to be precise, there is no efficiency gain.

For the performance problems, my suggestion would be to add various composite indexes. EAV designs need queries with multiple joins and without appropriate indexes, they are often inefficient.

For the specific query, you could try indexes either on (Entity, TypeID, EntityID) INCLUDE (Value) or on (Entity, EntityID, TypeID) INCLUDE (Value). Then check the execution plans to see which of the two is used.

If the conditions TypeId IN (1,2,3), etc are fairly common and stable, you could use even more targeted indexes, like this:

 (Entity, EntityID) INCLUDE (Value) WHERE TypeID IN (1,2,3)


If my understanding of BCNF is correct, how did we know that R1 was not in BCNF?

You are asking:

  I have a question, how did we know that R1 was not in BCNF and needs to be decomposed?

In the BCNF Decomposition Algorithm, when a relation is decomposed, one should find the dependencies of the subschemas, in this case R1(ACDE) and R2(BCD).

Let’s start from R1. To find the dependencies that hold in R1, one should actually project the original dependencies over the subschema, but, for simplicity, we would consider only those dependencies in which all the attributes are present in the subschema.

So, in R1 the dependencies {C D → E, D E → A} hold, C D is the key, and the algorithm continues by searching if the dependency D E → A violates the BCNF (of course the first one does not violates it since C D is the key of R1).

And it is easy to see that D E → A violates the BCNF since D E is not a superkey of the relation, so that now R1 must be decomposed.

So, the answer to your question is:

  The algorithm knows that R1 is not in BCNF simply by checking if the dependencies that hold in R1 have a determinant which is not a superkey of R1.

From comments:

  After the 1st decomposition, we should calculate the minimal keys again. Am I correct?

We know the key of R1 because of the way in which it is built (as the closure of the determinant of the original dependency CD -> E). We must find the key of R2, instead.

  Which part of my answer needs to be corrected?

Simply cancel the sentence "Since, CD->E is already applied in a decomposition, we don't need to use that again. It will produce the same result.", and continue with "(1) Finding the violating FDs:
DE->A is violating the BCNF and it is also not applied yet..."


Running Excel Macro Code by SSIS package configured with SQL Server Job agent

Does the service account for the SQL Server Agent have access to the directories for the objects being modified by the Excel macro?  My experience is when code works in your development tools but fails when running through the SQL Server Agent, it is because the service account for the SQL Server Agent is missing permissions that your user account has when running interactively in the development tools.


Selecting only overlapping elements from array of ranges

This works as desired:

SELECT d.sensor, r.overlapping_ranges
FROM   data d
JOIN   LATERAL (
   SELECT array_agg(range) AS overlapping_ranges
   FROM   unnest(d.ranges) range
   WHERE  range && '[873021700,873021800]'::numrange
   ) r ON overlapping_ranges IS NOT NULL;

About LATERAL:

What is the difference between LATERAL and a subquery in PostgreSQL?

For big tables, it would be much more efficient to normalize your design with a separate ranges table (one range per row) instead of the ranges array. You can use a GiST index for that:

PostgreSQL: how do I query over a column with a custom range?
2 B-tree indices OR 1 GiST index on tsrange -- which will perform better?

Solution for huge table

For a huge table like you mention in the comments (1 billion rows) I would consider a separate ranges table, optimized for size and a BRIN index to go along with it.

Assuming:

Read only (or mostly) data.
A maximum of 6 fractional digits (scale) and a maximum of 18 digits total (precision). Scaled by 1000000, this fits into a bigint without loss, which is considerably cheaper to store. See below.
Postgres 9.5 or later.

The operator class for range types shipped with Postgres 9.5 is range_inclusion_ops, which supports the overlaps operator &&.

To optimize disk space some more I would just save two bigint numbers (your numeric values multiplied by 1000000) and make it a functional BRIN index. Basically like this:

CREATE TABLE sensors (
  sensor_id serial PRIMARY KEY
, sensor text NOT NULL);

CREATE TABLE ranges (
  sensor_id int NOT NULL REFERENCES sensors
, range_low bigint NOT NULL
, range_hi  bigint NOT NULL
);

INSERT INTO sensors (sensor) VALUES ('sensor1');

INSERT INTO ranges (sensor_id, range_low, range_hi) VALUES
   (1, 872985609.0 * 1000000, 873017999.0 * 1000000)  -- scaled
 , (1, 872929250.000000 * 1000000, 872985609.000000 * 1000000);

CREATE INDEX ranges_brin_idx ON ranges USING BRIN (int8range(range_low, range_hi, '[]'));

Query to get the same result as before:

SELECT s.sensor, r.ranges
FROM  (
   SELECT sensor_id
        , array_agg(numrange(range_low * .000001, range_hi * .000001, '[]')) AS ranges
   FROM   ranges
   WHERE  int8range(range_low, range_hi, '[]')
       && '[873021700000000,873021800000000]'::int8range  -- scaled as well
   GROUP  BY sensor_id
   ) r
JOIN   sensors s  USING (sensor_id);

Storage size of bigint vs. numrange

A numrange with numbers of 15 precision occupies 32 bytes on disk,
resulting in 64 bytes per row (plus int column, tuple header and item identifier).

While the same with two bigint columns (2 x 8 bytes) results in 52 bytes total. Makes the table around 12 GB smaller. Index size is the same.

See for yourself:

SELECT pg_column_size((1::bigint, '[873021700.123456,873021800.123456]'::numrange))
     , pg_column_size((1::bigint, 873021700123456::bigint, 873021700123456::bigint));

Detailed explanation for row size:

Configuring PostgreSQL for read performance


Details of transactions in SQL SERVER 2008 R2

Use SQL Server Audit, after upgrading to a supported version of SQL Server.  For SQL Server 2008 there's only SQL Trace.  And in either case there's no way to get this information after the fact.  You have to set something up to capture it.


Convert Character Encoding, and Check Encoding for Issues

That won't fix or recognize any malformed data already in the table.

Yes, that is a good technique.  ALTER..CONVERT TO.. does the bulk of the work.  And, since utf8 is a subset of utf8mb4, there should be no differences discovered in step 4.

However, there is still a possibility of step 4 showing something.  This is because the definition of "equal" (hence <>) is different for _general_ci versus _unicode_ci.

For example, in German, ss and ß are unequal in _general_ci (either utf8 or utf8mb4), but equal in virtually all other collations.  If, for example, you currently have a UNIQUE (or PRIMARY KEY) with a value that differs in just ss vs ß, the conversion will have a problem with "duplicate key" error.

Another problematic pair:  ae vs æ

A different issue - not equality, but ordering - Ð < E for _unicode_ci, but not for some other collations.

Meanwhile, as long as you are changing the COLLATION, you may as well go  to the newer Unicode Algorithm in utf8mb4_unicode_520_ci.


Go to Programs and Features, find the Microsoft SQL Server 2012 (64-bit) entry, select Uninstall/Change, and then select Remove in the window that pops up.

You select the instance you are working with:

Then check Reporting Services you installed:


inconsistent read before/after checkpoint?

To answer your specific questions about this scenario:

  if connection B read record A before checkpoint, does it mean it will read the old value?

No.

  if connection B read record A after checkpoint, does it mean it will read the new value?

It will read the new, committed value regardless of the CHECKPOINT.

  isn't that weird that sometimes connection B get new value and sometimes it get old value, is the way to fix this problem is A needs to manually trigger a checkpoint?

No, issuing a manual CHECKPOINT is not necessary.

When a query needs to read a particular data page, it first tries to read that page from the buffer pool (RAM).  This is called a "logical read."

If that page is not in the buffer pool, it will be read from disk, placed into the buffer pool, and then that in-memory value will be used by the query.  This is called a "physical read."

The point being that rows / data pages that have been updated in RAM (so-called "dirty pages") can be read from memory by multiple queries, and by multiple connections, without first needing to be written to the data file on disk.

In fact, a value in the buffer pool can be updated multiple times before being written to disk.

The CHECKPOINT process is not really related to isolation levels, locking, or correctness of query results.  It's mainly a performance optimization (to prevent lots of small writes from happening all the time) and a recoverability feature (to reduce the time it takes to recover the database to a consistent state from the log).

You can read a lot of in-depth information about these topics in the following Microsoft Docs articles:

Memory Management Architecture Guide - Buffer management
Reading Pages
Writing Pages


Join a 'default' value only when there is no fully matching record

You should be able to get it by using an OUTER APPLY join plus TOP 1

select
    *
from
    righttable r
where
    (r.race=1 and r.program = 'gold')
    or
    r.race is null
order by
    case when r.race is null then 1 else 0 end

returns:

commission | program | race
---------: | :------ | ---:
        14 | gold    |    1
        23 | gold    | null

but you just need the first row, that you can get by using a TOP 1 in this way:

select
    l.race,
    totalvalue = sum(value),
    commission = SUM(value * (commission * 0.01))
from
    lefttable l
outer apply
    (select top 1
        commission
     from
         righttable r
     where
         (r.race=l.race and r.program = l.program)
         or
         r.race is null
     order by
         case when r.race is null then 1 else 0 end) r
group by
    l.race;

race | totalvalue | commission
---: | ---------: | :---------
   1 |        100 | 14.00
   4 |        100 | 23.00
   5 |        100 | 23.00

db<>fiddle here


The answer is NO.  Today, Jehad's answer is wrong.  It may have been correct for some older version of MySQL and for some particular Engine (such as MEMORY before Version 8.0).

Today, Data and indexes are packed VARCHAR, they do not use the max length.  I verified with 5.6 with InnoDB and MyISAM.

That is, a VARCHAR column that averages 20 bytes will take only that much space.  However, if you experiment with this in InnoDB, the numbers will be quite confusing.  Here are some reasons why the sizes you get will be more than (20 * num-rows) in InnoDB:

Overhead for each row (in data and in index): 20-30 bytes
Overhead for each block: 10-20%
Overhead for BTree block splits: 40%.  This may not be needed if you insert rows in order or if the Change Buffer effectively sorts for you.
Allocation units:  At first, InnoDB allocates 16KB, but it quickly switches to 16*16KB, then 4MB.  So SHOW TABLE STATUS will show much bigger numbers for Data_length and Index_length, and the excess will not be included in Data_free.

A negative for Hashes:  They are notoriously "random".  That is, caching (the buffer_pool or key_buffer) becomes useless once the data (or index) grows much beyond the cache size.

If you are going to use an MD5 (or SHA1), pack it into BINARY(16) (or (20)) using HEX(). to save space.  Even doing that is not enough for me to recommend hash over an avg of 20 bytes in a VARCHAR.

Another tip:  Consider dropping the AUTO_INCREMENT and promoting the VARCHAR (or hash) to be the PRIMARY KEY.  In many (not all) situations, performance and space consumption both improve.


Possible to have a statement in stored procedure outside of current transaction?

Declare the log table to be ENGINE=MyISAM.


PG fulltext search: Boost factor for word in the title

Yes, you can do that using ts_rank:
You can use setweight to give different parts of the text search vector different weight:
SELECT * from atable
WHERE (to_tsvector('english', title) ||
       to_tsvector('english', content)
      ) @@ to_tsquery('english', 'happy <-> home')
ORDER BY ts_rank(setweight(to_tsvector('english', title), 'A') ||
                 setweight(to_tsvector('english', content), 'C'),
                 to_tsquery('english', 'happy <-> home'));

Then the title will be ranked higher (weight A) than the content (weight C).


T-SQL is not working on DBeaver on Linux

DBeaver parses queries one by one using a statement delimiter (“;” by default) and executes them consecutively.

Try removing the empty lines and those “;”.

Check more details here.