Why does PostgreSQL accept junk when parsing an INTERVAL

The documentation is the (open source) implementation of ParseDateTime in src/backend/utils/adt/datetime.c, which is used to parse all datetime data types. Essentially, this is not documented, but "working as implemented".
As to "why", you'd have to trawl the commit log and the related discussions on -hackers, but since some of that code is old enough to vote, this might prove difficult.
Perhaps the best answer is that the datetime parsing function is deliberately lax to allow various input formats.


There are a couple of separate points here, but I don't think how MongoDB stores data in RAM is really relevant here - MongoDB just uses the mmap() call and then lets the kernel take care of the memory management (the Linux kernel will use Least Recently Used (LRU) by default to decide what to page out and what to keep - there are more specifics to that but it's not terribly relevant).

In terms of your issues, it sounds like you might have had a corrupt index, though the evidence is somewhat circumstantial.  Now that you have done a repair (the validate() command would have confirmed/denied beforehand), there won't be any evidence in the current data but you may find more evidence in the logs, particularly when you were attempting to recreate the index, or using the index in queries.

As for the spikes in the page faults, btree stats, journal, lock percentage, and average flush time, that has all the hallmarks of a bulk delete that causes a lot of index updates, and causes a large amount of IO.  The fact that mapped memory drops off later in the graphs would suggest that once you ran the repair the storage size was significantly reduced, which usually indicates significant fragmentation (bulk deletes, along with updates that grow documents are the leading causes of fragmentation).

Therefore, I would look for a large delete operation logged as slow in the logs - it will only be logged once complete, so look for it to appear after the end of the events in MMS.  One of the quirks of not running in a replica set is that a bulk operation like this is relatively non-obvious - it shows up as a single delete operation in the MMS graphs (usually lost in the noise).

These bulk delete operations usually tend to be run on older data that has not been recently used and has hence been paged out of active memory by the kernel (LRU again).  To delete it you must page it all the data back in, then flush the changes to disk, and of course deletes require the write lock, hence the spikes in faults, lock percentage etc.

To make room for the deleted data, your current working set is paged out, which will hit performance on your normal usage until the deletes complete and the memory pressure eases.

FYI - when you run a replica set, bulk ops are serialized in the oplog and hence replicated one at a time - as such you can track such operations by their footprints in the replicated ops stats of the secondaries.  This is not possible with a standalone instance (without looking in the logs for the completed ops) and other secondary indications.

As for managing large deletes in the future, it is generally far more efficient to partition your data into separate databases (if possible) and then drop the old data when it is no longer needed by simply dropping the old databases.  This requires some extra management on the application side but it negates the need for bulk deletes, is far quicker to complete, limits fragmentation, and dropped databases also remove the files on disk, preventing excessive storage use.  Definitely recommended if possible with your use case.


Toad for Oracle: Procedures, Packages, Job, and DBMS_SCHEDULER

If so, can I call an existing script to create a procedure or would I
  need to paste the PLSQL into the procedure script?

Both works.

DBMS_SCHEDULER.CREATE_JOB

The type of the job (job_type) can be PLSQL_BLOCK or STORED_PROCEDURE as well.

For PLSQL_BLOCK, you can specify an anonymous block, a piece of PL/SQL code such as begin ... end;.

For STORED_PROCEDURE, you can specify the name of the stored procedure to call.


SQL Server 2008 R2 Remote Connection issue

By default Sql server may not be listening for TCP/IP connections.

Did you check this stackoverflow question?

Also check start the "sql server agent" service is started.


How can I configure RMAN to delete crontrol files automatically?

The RMAN Retention Policy for the control files and backup sets is defined as such:

(emphasis mine)

[CONFIGURE]..

  This is the command to start the configuration of the parameter

... RETENTION POLICY ...

  Specifies a persistent, ongoing policy for backup sets and copies that RMAN marks as obsolete, that is, not needed and eligible for deletion.
  As time passes, RMAN marks backup sets and copies as obsolete according to the criteria specified in the retention policy. RMAN automatically deletes obsolete backup sets and copies in the flash recovery area when space is needed. RMAN does not automatically delete obsolete files outside the flash recovery area: you must manually execute DELETE OBSOLETE to remove them.

... TO REDUNDANCY integer

  Specifies that RMAN should retain integer full or level 0 backups of each datafile and control file. The default retention policy setting is REDUNDANCY 1.
  If more than integer full or level 0 backups of a datafile or control file exist, then RMAN marks these extra files as obsolete. RMAN then determines the oldest of the retained backups and marks all archived redo logs and log backups older than this backup as obsolete. The DELETE OBSOLETE command removes obsolete datafile backups (full or incremental), control file backups, and archived log backups or image copies.

Applying the Above to Your Environment

In your case you have set the following:

CONFIGURE RETENTION POLICY TO REDUNDANCY 3;

Which means:

keep all full and level 0 backups for up to three versions [back]
keep all archive logs in-between these backups as well
keep all control file backups of each backup type as well

full and level 0 backup
archive log backup
when any autobackup of the control file is triggered

As you might notice, you could essentially have the same amount of backed up control files as you have the sum of:

full backup files
level 0 backup files
archive log backup files

...or even more. (Read: Oracle 12c – Automatic Control File Backups for more details)

And if you are not backing up to the flash recovery area of your Oracle instance, then you would have to manually delete these files after backing up your database by issuing the following command:

DELETE OBSOLETE;

You could also test this by first running:

REPORT OBSOLETE;

...to check that the control files in the directory are actually obsolete, and then manually delete them in RMAN with the command I mentioned before this one.

Summary

You could be seeing the following issues:

Everthing is OK, the instance has just created a lot of autobackup-ed control files.
The Control Files are outside the FRE and have to deleted manually
You hit a bug (...read on below).

Because we don't know your exact backup scripts and other settings, it's hard to tell which point you are encountering, but you have a few good starting points.

Apparent Bug

There are cases (apparently) where the control file never gets marked as obsolete:

12.2 controlfile backup never gets marked as obsolete

A bug has been filed, and a fix supplied, but it doesn't seem to work in all cases.

You might have to manually delete the control file backups in RMAN.

DELETE COPY OF CONTROLFILE LIKE '/mnt/backup/rman/xyz/<part_name_of_control_file>%';

Answering Your Question

  What can I do to delete these files also automatically?

Sorry, but that depends which situation you are hitting and can vary from:

Will happen automatically
Can be deleted with REPORT OBSOLETE and DELETE OBSOLETE
Will only happen manually with DELETE COPY OF CONTROLFILE LIKE ...
Might work again after bug fix

References

Deleting RMAN Backups and Archived Redo Logs (Oracle Docs)
Configuring the Backup Retention Policy (Oracle Docs)
Listing Backups and Recovery-Related Objects (Oracle Docs)
Deleting Specified Backups and Copies (Oracle Docs)
12.2 controlfile backup never gets marked as obsolete (PATRICK'S ORACLE DBA BLOG)


Compare multiple field to single IN query

You haven't supplied a clear definition of the problem, so I have to guess you probably mean "if any of these field values are in this list" or "if all of these field values are in this list".

Either way, you'll want to use logical array operators. The "any" case is array-overlaps, &&:

ARRAY[owner1,owner2,owner3] && (select array_agg(id) from login)

and the "all" case is array-contains, <@:

ARRAY[owner1,owner2,owner3] <@ (select array_agg(id) from login)


Postgres query cache analyzing table basis?

Clearing up confusion about IMMUTABLE

  So, for the next time pets_get_all(1337) is executed, the query will be fetched from the cache, the inner functions won't be executed and thus.

That's not true. In fact, even in the same query the result isn't typically "cached": which as far as I know isn't a correct description of what's going on. It's more of a planner reference trick. There is certainly no transactional memoization going on here. These are two different concepts and there is nothing about a function call especially one which may be inlined that implies what you're assuming.

IMMUTABLE and even STABLE does imply that the query-plan itself is cached. This means that future calls within the same transaction will not be re-planned.

CREATE FUNCTION stable_foo()
RETURNS void
AS $$
  SELECT pg_sleep(1);
$$
LANGUAGE sql
STABLE;

CREATE FUNCTION immutable_foo()
RETURNS void
AS $$
  SELECT pg_sleep(1);
$$
LANGUAGE sql
IMMUTABLE;

Just looking at the timing:

\timing

-- these will take 2 seconds.
SELECT * FROM immutable_foo() AS a, immutable_foo() AS b;
SELECT immutable_foo(), immutable_foo() FROM ( VALUES (1) ) AS t(x);
SELECT immutable_foo()::text || immutable_foo()::text;

-- these will take 1 second.
SELECT immutable_foo() FROM ( VALUES (1),(2) ) AS t(x);

-- but declared as `STABLE` like foo(), it takes two seconds.
SELECT stable_foo() FROM ( VALUES (1),(2) ) AS t(x);

So only under specific circumstances in the same query will a function call get optimized out.. However, you'll never find a situation where this happens across statement boundaries..

As far as I know, the contexts for which an immutable function is optimized away are undocumented. Historically, this is even something experienced folks have struggled with and there is a general try it and see mentality as to whether or not the optimization takes effect.

BEGIN;
  SELECT bar();
  SELECT bar();
END;

I prefer to think of IMMUTABILITY in terms of what it permits, things such as (a) functional indexes, (b) functional index predicates (c) check constraints and the like. They all require a function to return the same arguments given the same inputs to make sense -- and they'll check that they're given an immutable function. But, it doesn't follow that there is memoization that operates in the transaction, session, or server.

PgPool 2

If you do need an actually query-cache you should consider checking out PgPool 2 which is the industry-method of doing this.

It's essentially middleware (or a forward-cache depending on your age).

As it applies to you

Writing a system to exploit undocumented query optimizations is a bad idea. You're better of not doing this at all, stepping back and just using SQL. Sure, the results of a complex query are not cached. But the buffered heap fetches and plans are. If it's in the same transaction it's working off the same data.

I would find weak links in your application and ask questions about how to make them faster. What you're doing seems something akin to pre-optimization.


AWR dbms_workload_repository ash_global_report_text extract text from output

The DB time can be queried from dba_hist_sys_time_model.
resmgr: cpu quantum can be queried from dba_hist_system_event.
snap_id can be matched to time periods through dba_hist_snapshot.
Instead of generating a report then parsing it, use the above views to query what you need.


How to allow user access to non-owned objects in Postgres?

Read the manual on GRANT and ALTER TABLE.
There is no USAGE privilege for tables. You would see an error message if you tried your GRANT command.
You need permissions on the schema, too. Here USAGE is right. But you certainly want to revoke CREATE.
Ownership for sequences that are owned by serial columns is changed to the new table owner automatically. But any other sequences you'll need to reown manually. Or you go a more aggressive route with REASSIGN OWNED.
You may need permissions on sequences, too. (If you grant INSERT or want them to use currval() or nextval() functions). Those are currently (v9.6) separate from table permissions.
I strongly suggest to bundle permissions in a group role and grant / revoke membership in that role to selected user roles.

Something along these lines, as superuser:

REVOKE ALL ON DATABASE mydatabase FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM PUBLIC;                      -- you may or may not want this
-- REVOKE ALL ON ALL TABLES    IN SCHEMA public FROM PUBLIC;  -- only for more existing permissions
-- REVOKE ALL ON ALL SEQUENCES IN SCHEMA public FROM PUBLIC;  -- only for more existing permissions

ALTER TABLE mytable OWNER TO postgres;    -- or some admin role. best keep the superuser out of this
-- more tables? or even:
-- REASSIGN OWNED BY dbuser TO postgres;  -- to reassign *all* owned objects the current DB

CREATE ROLE usr_permissions;
GRANT usr_permissions TO dbuser;

GRANT CONNECT        ON DATABASE mydatabase            TO usr_permissions;
GRANT USAGE          ON SCHEMA public                  TO usr_permissions;  -- if you revoked from PUBLIC
GRANT SELECT, UPDATE ON ALL TABLES    IN SCHEMA public TO usr_permissions;  -- DELETE, INSERT?
GRANT USAGE          ON ALL SEQUENCES IN SCHEMA public TO usr_permissions;  -- if you also granted INSERT

Related:

How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA?
Grant access to all tables of a database

  Additionally, should I use a named schema other than public?

The schema public is in no way special. It only happens to be installed by default with permissions for PUBLIC and preset in the default search_path. Some DB admins even delete it. You can use it like any other schema. Depends on the complete situation. Related:

Is it recommended to install extensions into pg_catalog schema?


Stored procedure and prepared statements

Because the second one is, coincidentally, syntactically valid but not the correct way to do what you are doing.  You are trying to do dynamic sql without using dynamic sql.  What you are doing in #2 is actually putting the literal string ', param_CRITERIA ,' in the where clause, not the content of that variable.

The only way to do what you are trying to do is method #1.

I would also point out that if the value you're passing in method #1 is coming from an untrustworthy source (such as a web form or url url query string) then you are also potentially setting yourself up for an SQL injection vulnerability.

Edit:

You have updated your question so that example number 2 is no longer consistent with my answer, above, so for future clarity, I'll point out that your original query #2 looked like this:

... WHERE (,'param_CRITERIA',) group ...

Now you have removed the commas and the quotes:

... WHERE ( param_CRITERIA ) group ...

This does not work either, because, as I have pointed out, you are trying get the contents of a variable interpolated into the body of the query, blurring the boundary between logic and data.  The only way to do this is to craft a dynamic SQL query, which is what you're doing in #1.  Example #2 is not a dynamic query and cannot be made to be one.


Release unused space in SQL Server database

You can script the SQL commands that SSMS generates. Without us knowing what commands you execute, we don't have much to go on.
Also, pls be specific about each file and the file size. You have 12 files. How large was each file before the individual file shrink, and what was the file size after the individual file shrink?
Did you use the TRUNCATEONLY options of the DBCC SHRINKFILE commands? That is the default in the GUI.
Finally, no need to do a "final" DBCC SHRINKDATABASE. Work with each file separately instead.


Does altering a table and adding multiple columns rebuild the table multiple times

Will there be any difference between the above two approaches, in terms of how expensive the operation will be?

No in practice. Because in the second approach the operations execution is one-by-one, not parallel. Cause of this is simple - in theory the next step can be dependent by previous one and so impossible until next step fully performed.

  What are the best practices when performing such operations?

Create new table + copy data + (lock source table + copy data changes in source made by another clients while first copy) + drop old + rename new + restore access rights, FKs and triggers.

  What does a table rebuild, really mean?

In practice this procedure is performed as described in answer 2 + exclusive lock on the table modified.

  Is there a way to avoid a table rebuild, or make the operation faster: (ALGORITHM=INPLACE) ?

Add separate table with new columns linked to original table as 1:1 instead.


why can I only install MySQL 5.1 on cent OS 6.2?

You asked this similar question before about getting MySQL 5.5 and I answered it : I am getting an error while trying to download binary for mysql 5.5.11 (Please upvote it if it helped before)

Here is a script I use to get the latest MySQL 5.5 for RHEL6

HOWMANYBITS=64
WHATOS=6
BINARY_VERSION=000
if [ ${HOWMANYBITS} -eq 32 ] ; then BINARY_VERSION=i386   ; fi
if [ ${HOWMANYBITS} -eq 64 ] ; then BINARY_VERSION=x86_64 ; fi
MINOR_RELEASE=22
MYSQL_RELEASE=5.5.${MINOR_RELEASE}
OS_VERSION=000
if [ ${WHATOS} -eq 0 ] ; then OS_VERSION=linux2.6 ; fi
if [ ${WHATOS} -eq 5 ] ; then OS_VERSION=rhel5    ; fi
if [ ${WHATOS} -eq 6 ] ; then OS_VERSION=el6    ; fi
RELEASE_TAG=1
DEVEL__RPM=http://www.mysql.com/get/Downloads/MySQL-5.5/MySQL-devel-${MYSQL_RELEASE}-${RELEASE_TAG}.${OS_VERSION}.${BINARY_VERSION}.rpm/from/http://mysql.mirrors.pair.com/
SHARD2_RPM=http://www.mysql.com/get/Downloads/MySQL-5.5/MySQL-shared-compat-${MYSQL_RELEASE}-${RELEASE_TAG}.${OS_VERSION}.${BINARY_VERSION}.rpm/from/http://mysql.mirrors.pair.com/
SHARED_RPM=http://www.mysql.com/get/Downloads/MySQL-5.5/MySQL-shared-${MYSQL_RELEASE}-${RELEASE_TAG}.${OS_VERSION}.${BINARY_VERSION}.rpm/from/http://mysql.mirrors.pair.com/
CLIENT_RPM=http://www.mysql.com/get/Downloads/MySQL-5.5/MySQL-client-${MYSQL_RELEASE}-${RELEASE_TAG}.${OS_VERSION}.${BINARY_VERSION}.rpm/from/http://mysql.mirrors.pair.com/
SERVER_RPM=http://www.mysql.com/get/Downloads/MySQL-5.5/MySQL-server-${MYSQL_RELEASE}-${RELEASE_TAG}.${OS_VERSION}.${BINARY_VERSION}.rpm/from/http://mysql.mirrors.pair.com/
PERL_LOCATION=ftp://ftp.de.netclusive.de/pub/parallels/Plesk/Expand/2.3.1/packages
PERL_FILE=perl-DBI-1.53-2.fc7.x86_64.rpm
PERL_URL=${PERL_LOCATION}/${PERL_FILE}
cd
rm -rf MySQL5.5_Install
mkdir MySQL5.5_Install
cd MySQL5.5_Install
wget ${PERL_URL}
wget ${DEVEL__RPM}
wget ${SHARD2_RPM}
wget ${SHARED_RPM}
wget ${CLIENT_RPM}
wget ${SERVER_RPM}
ls -l

When you are satisfied with the downloads, then install them:

for RPM in `ls -lSr *.rpm | awk '{print $9}'` ; do rpm -Uvh --force ${RPM} ; done

Give it a Try !!!

I found the problem. For OS6, change i386 to i686 and the download works ...

[redwards@lw-lts-155 MySQL_5.5.22]$ HOWMANYBITS=32
[redwards@lw-lts-155 MySQL_5.5.22]$ WHATOS=6

MYSQL_INSTALL_FOLDER=MySQL_${MYSQL_RELEASE}
OS_VERSION=000
[redwards@lw-lts-155 MySQL_5.5.22]$ BINARY_VERSION=000
[redwards@lw-lts-155 MySQL_5.5.22]$ if [ ${HOWMANYBITS} -eq 32 ] ; then BINARY_VERSION=i686   ; fi
[redwards@lw-lts-155 MySQL_5.5.22]$ if [ ${HOWMANYBITS} -eq 64 ] ; then BINARY_VERSION=x86_64 ; fi
SERVER_RPM=http://www.mysql.com/get/Downloads/MySQL-5.5/MySQL-server-${MYSQL_RELEASE}-${RELEASE_TAG}.${OS_VERSION}.${BINARY_VERSION}.rpm/from/http://mysql.mirrors.pair.com/
[redwards@lw-lts-155 MySQL_5.5.22]$ MINOR_RELEASE=22
[redwards@lw-lts-155 MySQL_5.5.22]$ MYSQL_RELEASE=5.5.${MINOR_RELEASE}
[redwards@lw-lts-155 MySQL_5.5.22]$ MYSQL_INSTALL_FOLDER=MySQL_${MYSQL_RELEASE}
[redwards@lw-lts-155 MySQL_5.5.22]$ OS_VERSION=000
[redwards@lw-lts-155 MySQL_5.5.22]$ if [ ${WHATOS} -eq 0 ] ; then OS_VERSION=linux2.6 ; fi
[redwards@lw-lts-155 MySQL_5.5.22]$ if [ ${WHATOS} -eq 5 ] ; then OS_VERSION=rhel5    ; fi
[redwards@lw-lts-155 MySQL_5.5.22]$ if [ ${WHATOS} -eq 6 ] ; then OS_VERSION=el6    ; fi
[redwards@lw-lts-155 MySQL_5.5.22]$ RELEASE_TAG=1
[redwards@lw-lts-155 MySQL_5.5.22]$ DEVEL__RPM=http://www.mysql.com/get/Downloads/MySQL-5.5/MySQL-devel-${MYSQL_RELEASE}-${RELEASE_TAG}.${OS_VERSION}.${BINARY_VERSION}.rpm/from/http://mysql.mirrors.pair.com/
[redwards@lw-lts-155 MySQL_5.5.22]$ SHARD2_RPM=http://www.mysql.com/get/Downloads/MySQL-5.5/MySQL-shared-compat-${MYSQL_RELEASE}-${RELEASE_TAG}.${OS_VERSION}.${BINARY_VERSION}.rpm/from/http://mysql.mirrors.pair.com/
[redwards@lw-lts-155 MySQL_5.5.22]$ SHARED_RPM=http://www.mysql.com/get/Downloads/MySQL-5.5/MySQL-shared-${MYSQL_RELEASE}-${RELEASE_TAG}.${OS_VERSION}.${BINARY_VERSION}.rpm/from/http://mysql.mirrors.pair.com/
[redwards@lw-lts-155 MySQL_5.5.22]$ CLIENT_RPM=http://www.mysql.com/get/Downloads/MySQL-5.5/MySQL-client-${MYSQL_RELEASE}-${RELEASE_TAG}.${OS_VERSION}.${BINARY_VERSION}.rpm/from/http://mysql.mirrors.pair.com/
[redwards@lw-lts-155 MySQL_5.5.22]$ SERVER_RPM=http://www.mysql.com/get/Downloads/MySQL-5.5/MySQL-server-${MYSQL_RELEASE}-${RELEASE_TAG}.${OS_VERSION}.${BINARY_VERSION}.rpm/from/http://mysql.mirrors.pair.com/
[redwards@lw-lts-155 MySQL_5.5.22]$ PERL_LOCATION=ftp://ftp.de.netclusive.de/pub/parallels/Plesk/Expand/2.3.1/packages
[redwards@lw-lts-155 MySQL_5.5.22]$ PERL_FILE=perl-DBI-1.53-2.fc7.x86_64.rpm
[redwards@lw-lts-155 MySQL_5.5.22]$ PERL_URL=${PERL_LOCATION}/${PERL_FILE}
[redwards@lw-lts-155 MySQL_5.5.22]$ cd
[redwards@lw-lts-155 ~]$ rm -rf ${MYSQL_INSTALL_FOLDER}
[redwards@lw-lts-155 ~]$ mkdir ${MYSQL_INSTALL_FOLDER}
[redwards@lw-lts-155 ~]$ cd ${MYSQL_INSTALL_FOLDER}
[redwards@lw-lts-155 MySQL_5.5.22]$ wget ${PERL_URL}
--2012-04-10 15:40:23--  ftp://ftp.de.netclusive.de/pub/parallels/Plesk/Expand/2.3.1/packages/perl-DBI-1.53-2.fc7.x86_64.rpm
           => `perl-DBI-1.53-2.fc7.x86_64.rpm'
Resolving ftp.de.netclusive.de... 89.110.128.25
Connecting to ftp.de.netclusive.de|89.110.128.25|:21... connected.
Logging in as anonymous ... Logged in!
==> SYST ... done.    ==> PWD ... done.
==> TYPE I ... done.  ==> CWD /pub/parallels/Plesk/Expand/2.3.1/packages ... done.
==> SIZE perl-DBI-1.53-2.fc7.x86_64.rpm ... 625843
==> PASV ... done.    ==> RETR perl-DBI-1.53-2.fc7.x86_64.rpm ... done.
Length: 625843 (611K)

100%[===================================================================================================================================>] 625,843      702K/s   in 0.9s

2012-04-10 15:40:26 (702 KB/s) - `perl-DBI-1.53-2.fc7.x86_64.rpm' saved [625843]

[redwards@lw-lts-155 MySQL_5.5.22]$ wget ${DEVEL__RPM}
--2012-04-10 15:40:26--  http://www.mysql.com/get/Downloads/MySQL-5.5/MySQL-devel-5.5.22-1.el6.i686.rpm/from/http://mysql.mirrors.pair.com/
Resolving www.mysql.com... 156.151.63.6
Connecting to www.mysql.com|156.151.63.6|:80... connected.
HTTP request sent, awaiting response... 302 Found
Location: http://mysql.mirrors.pair.com/Downloads/MySQL-5.5/MySQL-devel-5.5.22-1.el6.i686.rpm [following]
--2012-04-10 15:40:26--  http://mysql.mirrors.pair.com/Downloads/MySQL-5.5/MySQL-devel-5.5.22-1.el6.i686.rpm
Resolving mysql.mirrors.pair.com... 216.92.2.145
Connecting to mysql.mirrors.pair.com|216.92.2.145|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 4909292 (4.7M) [text/plain]
Saving to: `MySQL-devel-5.5.22-1.el6.i686.rpm'

100%[===================================================================================================================================>] 4,909,292   3.01M/s   in 1.6s

2012-04-10 15:40:29 (3.01 MB/s) - `MySQL-devel-5.5.22-1.el6.i686.rpm' saved [4909292/4909292]

[redwards@lw-lts-155 MySQL_5.5.22]$ wget ${SHARD2_RPM}
--2012-04-10 15:40:29--  http://www.mysql.com/get/Downloads/MySQL-5.5/MySQL-shared-compat-5.5.22-1.el6.i686.rpm/from/http://mysql.mirrors.pair.com/
Resolving www.mysql.com... 156.151.63.6
Connecting to www.mysql.com|156.151.63.6|:80... connected.
HTTP request sent, awaiting response... 404 Not found
2012-04-10 15:40:29 ERROR 404: Not found.

[redwards@lw-lts-155 MySQL_5.5.22]$ wget ${SHARED_RPM}
--2012-04-10 15:40:29--  http://www.mysql.com/get/Downloads/MySQL-5.5/MySQL-shared-5.5.22-1.el6.i686.rpm/from/http://mysql.mirrors.pair.com/
Resolving www.mysql.com... 156.151.63.6
Connecting to www.mysql.com|156.151.63.6|:80... connected.
HTTP request sent, awaiting response... 302 Found
Location: http://mysql.mirrors.pair.com/Downloads/MySQL-5.5/MySQL-shared-5.5.22-1.el6.i686.rpm [following]
--2012-04-10 15:40:29--  http://mysql.mirrors.pair.com/Downloads/MySQL-5.5/MySQL-shared-5.5.22-1.el6.i686.rpm
Resolving mysql.mirrors.pair.com... 216.92.2.145
Connecting to mysql.mirrors.pair.com|216.92.2.145|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 1685312 (1.6M) [text/plain]
Saving to: `MySQL-shared-5.5.22-1.el6.i686.rpm'

100%[===================================================================================================================================>] 1,685,312   2.92M/s   in 0.6s

2012-04-10 15:40:30 (2.92 MB/s) - `MySQL-shared-5.5.22-1.el6.i686.rpm' saved [1685312/1685312]

[redwards@lw-lts-155 MySQL_5.5.22]$ wget ${CLIENT_RPM}
--2012-04-10 15:40:30--  http://www.mysql.com/get/Downloads/MySQL-5.5/MySQL-client-5.5.22-1.el6.i686.rpm/from/http://mysql.mirrors.pair.com/
Resolving www.mysql.com... 156.151.63.6
Connecting to www.mysql.com|156.151.63.6|:80... connected.
HTTP request sent, awaiting response... 302 Found
Location: http://mysql.mirrors.pair.com/Downloads/MySQL-5.5/MySQL-client-5.5.22-1.el6.i686.rpm [following]
--2012-04-10 15:40:31--  http://mysql.mirrors.pair.com/Downloads/MySQL-5.5/MySQL-client-5.5.22-1.el6.i686.rpm
Resolving mysql.mirrors.pair.com... 216.92.2.145
Connecting to mysql.mirrors.pair.com|216.92.2.145|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 14633936 (14M) [text/plain]
Saving to: `MySQL-client-5.5.22-1.el6.i686.rpm'

100%[===================================================================================================================================>] 14,633,936  3.52M/s   in 4.1s

2012-04-10 15:40:38 (3.44 MB/s) - `MySQL-client-5.5.22-1.el6.i686.rpm' saved [14633936/14633936]

[redwards@lw-lts-155 MySQL_5.5.22]$ wget ${SERVER_RPM}
--2012-04-10 15:40:38--  http://www.mysql.com/get/Downloads/MySQL-5.5/MySQL-server-5.5.22-1.el6.i686.rpm/from/http://mysql.mirrors.pair.com/
Resolving www.mysql.com... 156.151.63.6
Connecting to www.mysql.com|156.151.63.6|:80... connected.
HTTP request sent, awaiting response... 302 Found
Location: http://mysql.mirrors.pair.com/Downloads/MySQL-5.5/MySQL-server-5.5.22-1.el6.i686.rpm [following]
--2012-04-10 15:40:39--  http://mysql.mirrors.pair.com/Downloads/MySQL-5.5/MySQL-server-5.5.22-1.el6.i686.rpm
Resolving mysql.mirrors.pair.com... 216.92.2.145
Connecting to mysql.mirrors.pair.com|216.92.2.145|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 40659264 (39M) [text/plain]
Saving to: `MySQL-server-5.5.22-1.el6.i686.rpm'

100%[===================================================================================================================================>] 40,659,264  3.33M/s   in 11s

2012-04-10 15:40:58 (3.54 MB/s) - `MySQL-server-5.5.22-1.el6.i686.rpm' saved [40659264/40659264]

[redwards@lw-lts-155 MySQL_5.5.22]$ ls -l
total 61140
-rw-r--r-- 1 redwards users 14633936 Mar 13 19:55 MySQL-client-5.5.22-1.el6.i686.rpm
-rw-r--r-- 1 redwards users  4909292 Mar 13 19:55 MySQL-devel-5.5.22-1.el6.i686.rpm
-rw-r--r-- 1 redwards users 40659264 Mar 13 19:56 MySQL-server-5.5.22-1.el6.i686.rpm
-rw-r--r-- 1 redwards users  1685312 Mar 13 19:57 MySQL-shared-5.5.22-1.el6.i686.rpm
-rw-r--r-- 1 redwards users   625843 Apr 10 15:40 perl-DBI-1.53-2.fc7.x86_64.rpm
[redwards@lw-lts-155 MySQL_5.5.22]$

Just run this

for RPM in `ls -lSr *.rpm | awk '{print $9}'` ; do rpm -Uvh --force ${RPM} ; done

and you are good to go !!!


Replicate a database with a subset of data offered by custom queries

I have been through the same problem recently and can comment on some attempts and my conclusions after a lot of research into it.

In my case I had to partition some data that would be distributed in different databases. But note that we are dealing with the same fundamental problem that is partial data migration.

The first problem is regarding data extraction, pg_dump does not provide any dynamic and consistent partial data extraction mechanism.

You can do a full backup and try to erase data you don't want to use, but as you have already realized, this is a costly task and demands a lot of runtime for large volumes of data. And depending on the complexity of your database structure, assembling the delete scripts can be quite difficult. What's more, if your bank is constantly evolving this method is not reusable if you want to extract partial data later.

You will find many suggestions from people saying to use the COPY feature, but all in all I can say that it is extremely limited to the complexity of this task. Because it would be necessary to assemble a data select from all tables consistently and then execute them in a single transaction. Again, since your bank is dynamic and constantly evolving, this method is not reusable either. Because you would have to rewrite all selects every time you were extracting partial data.

Usually the partial data migration comes from a specific table that will restrict the other data, we will name it as a restrictive table. That said we can consider some more possibilities.

Let's assume a case where your primary bank has a table called user (restrictive table) with 100 records and you want to migrate all your bank records that link to the first 10 records in that table.

We can only dump/restore the structure.
We can generate a sql with the data from the 10 user table records
we need and insert it into the database.
Then we can dump all data in simple sql disregarding the user table.
We can run this sql script without transaction block and let the key
constraint check do the dirty work.
As in the previous case, this can take a long execution time
depending on the volume of data, but this solution is functional and
replicable.

Note that your database cannot contain cyclic key problems (This is the condition to dump data only correctly).

Also note that the dependency level of your restrictive table must be the lowest minimum, otherwise an adaptation would have to be made to this methodology, which I will not write because the text is already getting too large.

That said, what really worked for me was using the Jailer tool. This tool is developed in java and using ORM it can map the key constraint set of your database and assemble a data extraction model as you configure it.

The output is a simple sql script with partial data consistent with the restrictive rules that have been set.

If you have any questions about its use I can help you understand and set up your configuration environment.

A similar question was asked in Stack Overflow.


Getting Index Scan instead of a possible Index Seek?

You use a nonequality predicate so your "seek" operations are in fact scans which just start from some value (not from "first") and then go to the end of the clustered index leaf level.

In the other hand you return only one column which is the clustered index key so using any of indexes won't get any key lookup operations. The optimizer has to estimate what would be cheaper: scanning a nonclustered index (two int columns on the leaf level) or part scanning of your clustered index (all columns on the leaf level).

It estimates it depending on current statistics (how many rows) and metadata (what is one row size). We saw the optimizer made a mistake on >20,000 predicate.

  When faced with huge amounts of data and/or when it returns more than
  50% of the data set, the query optimizer will favor an index scan.

That is a fact when the optimizer has to choose performing clustered index or table scan versus nonclustered index seek + key lookups.

In your case if your index on CustomerID were nonclustered you would always see a seek operation on that index, but if you then added another column to your output you would see the index seek + RID lookups on short resultsets and table scan on the big ones.


INSERT and INSERT IGNORE add a new row unless the row specifies a duplicate for some PRIMARY or UNIQUE key on the table.  The only difference is whether the "duplicate key" is an error or ignored.

Do you want one row per user?  Or can the user provide many passwords?  In the former case, PRIMARY KEY(username); in the latter, PRIMARY KEY(username, password).

Do you want the user to be able to change his password?  Then consider:

INSERT INTO CyberSecurity (username, password)
    VALUES ('$username', '$password')
    ON DUPLICATE KEY UPDATE password = '$password';

But... In any case escape both the username and password, else a username such as O'Brien will blow up your program.  (And a hacker can quickly take over your machine by quickly discovering you have failed to escape stuff from $_POST.)


Creating a mysql trigger without hard-coding database name

I guess I'll guess an answer to my question. A lot of mysql documentation just references a table name without specifying a database, so I am assuming the database is optional and if you don't include it, mysql will use the current one.


Select TOP N Rows missing from SSMS temporal table

My SSMS 17.5 does show the context-menu for "Select Top 1000 Rows" against a SQL Server 2016 temporal-enabled table on Enterprise Edition.

In SQL Server 2016 Express, via SSMS 17.5, I'm not able to see the "Select Top 1000 Rows" context menu.

This appears to be a purposeful decision by Microsoft within SQL Server Management Studio.


Oracle 11g R2 - same query sometimes fast, sometimes slow

The query is sometimes fast because it is in the database's cache.

Make a test:

alter system flush shared_pool; (as sysdba - NOT IN PRODUCTION !!)
execute the query and display execution plan;
execute the query and display execution plan (should be faster because the query is not parsed but only executed );
alter system flush shared_pool; (as sysdba)
execute the query and display execution plan (should be slower the query is first parsed then executed)

In a ideal world, a query is parsed once and bound/executed over and over.


Assistance with: SQL Server detected a logical consistency-based I/O error

Error 824 points you to IO subsystem issue. I would highly recommend you to open a support case to Microsoft and they will be able to assist you and pinpoint the root cause. It usually cost you roughly 500USD.
Before you open a case, do due diligence and check your database for corruption. You can find the link here. The link will give you some guidance on the resolution and possible cause.
From my experience, check your firmware updates and filter drivers (antivirus) as a possible cause this error to show up.


You already linked rolando's post on how to do the migration. You can change the setting online to make use of per table files, but this will not fix your huge ibdata file size, even though single files are used afterwards.

To discern the time needed, try doing it on another installation you recreate from your backups. That way you will get reasonable numbers (as long as the systems have similar specs and hardware being used) how long this process will take, also you can try it out before laying hands onto the production system.

If you have hard uptime requirements another way to do this online might be through replication. To create a slave, you have create an initial mysqldump with special parameters, but this shouldn't be a problem since I guess you create your backups via dumps anyway?

When setting up the replication, you can let the slave run with innodb_file_per_table even though the master does not. Once all things are set, put the masters' ip onto the slave.

If all this is not possible due to the lack of hardware, then your system with the harsh uptime requirements was maybe sold too cheaply and you now have the ungrateful job to make something work with risks that should have been adressed in the initial planning by getting an adequate test setup besides your production setup.

For getting your mysqldumps to speed up, try piping them directly into parallel bzip, to get the cores to do more work and save time by reducing the needed disk accesses. Rougly like this:

# DUMP
mysqldump ... | pbzip2 -c > MYSQLDUMP.sql
# REPLAY
pbzip2 -dkc MYSQLDUMP.sql | mysql (...)


Is it possible to convert existing users from SQL Server authentication to Windows (AD) authentication? (SQL Server 2008 R2)

The security concept of SQL Servers differentiate between login and user. A login is at the SQL Server instance level and can be mapped to different users for different databases, as long as the database user is a SQL user and not a Windows user.

You could create the Windows login and the user (both having the same name) followed by script that copies the permissions from the SQL user to the Windows user. (Search for "sql server clone user permissions").


If you can upgrade to MariaDB version 10.2, you can use window functions.

The following assumes that table1 has - for every id - the same or more rows than table2:

WITH
  t1 AS
  ( SELECT id, time,
           row_number() OVER (PARTITION BY id ORDER BY time DESC) AS rn
    FROM table1
  ),
  t2 AS
  ( SELECT id, time,
           row_number() OVER (PARTITION BY id ORDER BY time DESC) AS rn
    FROM table2
  )
SELECT
    t1.id,
    CASE WHEN t2.time IS NULL OR t1.time >= t2.time
        THEN t1.time ELSE t2.time
    END AS time,
    CASE WHEN t2.time IS NULL OR t1.time >= t2.time
        THEN 'table1' ELSE 'table2'
    END AS "table"
FROM
    t1 LEFT JOIN t2
    ON  t1.id = t2.id
    AND t1.rn = t2.rn
ORDER BY
    id, t1.rn DESC ;

Tested at dbfiddle.uk


Restoring a differential backup in SQL server 2005

When restoring a sequence of backups (differential & logs) you specify NORECOVERY. This leaves the database in a state that will accept further restores, rather than recover it ready for use.

RESTORE DATABASE MyDb FROM DISK = '<path>\MyDb_FULL.bak' WITH NORECOVERY;
RESTORE DATABASE MyDb FROM DISK = '<path>\MyDb_DIFF.bak' WITH NORECOVERY;

-- Repeat following for each transaction log to restore if applicable
RESTORE LOG MyDb FROM DISK = '<path>\MyDb_201303050900_LOG.bak' WITH NORECOVERY;

RESTORE DATABASE MyDb WITH RECOVERY;


SQL Server Agent not observing "execute as" permissions

Have you checked which SQL user is actually running the query?  Depending on the permission level, SQL Agent may be allowing the user to 'impersonate' a higher level account.

Create a job with the T-SQL step:

SELECT ORIGINAL_LOGIN(), SUSER_NAME(), USER_NAME();

Make it log output to a table or text file and review which user is actually running the final query. If it's not the user you think it should be, then you'll need to trace back to work out where the impersonation is coming from.

I have ran into problems on CRM Dynamics databases using filtered views, where specified CRM users can see their filtered data.  If the job is using a system account impersonation it'll return no data as the system account doesn't exist as a CRM user.


see currently running background indexing jobs (MongoDB)

I have noticed the same thing in last week. Sometimes, the mongo shell returns any empty array:

Try running the currentOp() many times to get the progress.
Use use db command and then perform db.currentOp()


Unicode storage of \u202b RLE and \u202c PDE in a Unicode-aware database?

Arabic (as well as Hebrew and Syriac) are right-to-left languages. Hence they display in the opposite direction that the bytes are physical stored in. Having the proper display is controlled through non-printable characters that are interpreted only by the font / rendering system. These two characters in particular are used to control this (see original Unicode spec for starters: https://www.unicode.org/charts/PDF/U2000.pdf ), especially in the context of embedding right-to-left text in the same paragraph as left-to-right text (and the other way around).

So, you must keep them stored or else attempted to display this data later will render it backwards from how the language is supposed to appear and will hence be considered data loss. These are among many formatting control characters that are non-printable / zero-width.

The "official" description of how to work with these characters, from the Unicode Consortium, is (taken from "Chapter 23: Special Areas and Format Characters" top of page 868):

  As with other format control characters, bidirectional ordering controls affect the layout of the text in which they are contained but should be ignored for other text processes, such as sorting or searching. However, text processes that modify text content must maintain these characters correctly, because matching pairs of bidirectional ordering controls must be coordinated, so as not to disrupt the layout and interpretation of bidirectional text. Each instance of a lre, rle, lro, or rlo is normally paired with a corresponding pdf. Likewise, each instance of an lri, rli, or fsi is normally paired with a corresponding pdi.

Regarding the importance of keeping (not discarding) these hidden formatting code points, the "Unicode® Standard Annex #9: UNICODE BIDIRECTIONAL ALGORITHM", in section "2.7 Markup and Formatting Characters" states (emphasis mine):

  The explicit formatting characters introduce state into the plain text, which must be maintained when editing or displaying the text. Processes that are modifying the text without being aware of this state may inadvertently affect the rendering of large portions of the text, for example by removing a PDF.

and:

  Whenever plain text is produced from a document containing markup (ed: HTML and/or CSS), the equivalent formatting characters should be introduced, so that the correct ordering is not lost.

Further explanation is provided in the (excellent) "Understanding Bidirectional (BIDI) Text in Unicode" document by Cal Henderson (taken from the O.P.'s answer) states:

  ... we could disallow these explicit characters (U+202A - U+202E) which is pretty easy. This does mean that anybody who wants to use them to include Neutrals at the edges of their Arabic usernames will be out of luck - and that sucks more when it's a comment they're posting, where the period jumps to the 'beginning' of the text.

  If we want to allow use of these characters, the solution is fairly simple (if hard to implement): we need to make sure that every opening marker has a paired closing marker (PDF) so that the state stack coming out of the string is at the same state as when we went in. We also need to be careful that we don't allow any PDFs to be used without accompanying push markers, else we can't use any ourselves outside of the block.

So, even if the text of a particular cell is supposed to be entirely a right-to-left language, removing these markers could alter the placement of neutral characters (such as punctuation). For example (using SQL Server):

SELECT NCHAR(0x0671) + NCHAR(0x0679) +  N'!';
-- ٱٹ!

SELECT NCHAR(0x202B) + NCHAR(0x0671) + NCHAR(0x0679) + N'!' + NCHAR(0x202C);
-- ٱٹ!

Planning to add them back in later, or having a client app add them back in, won't work because there is no inherent means of knowing that they were even being used, and if so, where they were placed.

The safest approach is to keep these characters

For example, you are attempting to include some of this text at the top of the question:

^IArabic^I<202b>ﺰﻤﺑﺎﺑﻮﻳ<202c>^I<202b>ﺞﻫﻭﺮﻳﺓ ﺰﻤﺑﺎﺑﻮﻳ<202c>$

but clearly that's not displaying in the correct order. The bytes, however, are in the correct order:

Looking at just the first <202b>...<202c> section (again, using SQL Server, hence it's Little Endian):

SELECT CONVERT(VARBINARY(MAX), N'<202b>ﺰﻤﺑﺎﺑﻮﻳ<202c>');

the bytes are:

3C00 3200 3000 3200 6200 3E00 B0FE E4FE 91FE 8EFE 91FE EEFE F3FE 3C00 3200 3000 3200 6300 3E00
<    2    0    2    b    >    .    .    .    .    .    .    .    <    2    0    2    c    >

As you can see, there are no additional formatting characters. Because the Arabic characters are strong right-to-left, the characters that follow – <202 – which are neutral (<) and weak (202), continue to display heading to the left (even turning the < into >). And to be clear, the 202 itself displays left-to-right, which would be clearer if the number wasn't a palindrome. If the number was 203, then it would still show as 203 and not 302. But the c is strong left-to-right so it (and the characters that follow) display as expected.

How to fix? Just add the implicit left-to-right markers just after the Arabic to indicate that the right-to-left directionality should end at that point. If we add Code Point U+200E after the last Arabic character (and just before the <) in each of those two segments, we get the following:

^IArabic^I<202b>ﺰﻤﺑﺎﺑﻮﻳ<202c>^I<202b>ﺞﻫﻭﺮﻳﺓ ﺰﻤﺑﺎﺑﻮﻳ<202c>$

Now, if StackOverflow were to strip out the formatting, then it would revert to the incorrect display, and there is no indication as to the intention of what is desired here that can be discovered programmatically.

If you want to strip out the formatting and add it back in later, are you 100% certain as to the intention of why those characters are there? They aren't always used, so how do you know why they are used when they are present? Don't think there will be any non-Arabic characters? Ok, so how do you classify <202>? I left out the "b" and "c" because there could be punctuation and numbers without any Latin characters and still be "fully Arabic".

This is why I said that keeping them in was the "safest" route to go. Not the only route. But if you don't control the input values, then I don't see how you can guarantee never accidentally altering the meaning of the data.


How do retrieve unique rows where all all columns are the same except id

Omit the ID column from your SELECT ... statement and and a DISTINCT. As in:

SELECT DISTINCT COLUMN2, COLUMN3, .... FROM TABLE1

If you want to find how many rows exist for the same values then do a COUNT() combined with a GROUP BY. As in:

SELECT COUNT(ID), COLUMN2, COLUMN3, COLUMN4, ....
    FROM TABLE1
    GROUP BY COLUMN2, COLUMN3, COLUMN4, ....

Reference Material (MySQL Dev Site)

8.2.1.18 DISTINCT Optimization
3.3.4.8 Counting Rows
12.20.2 GROUP BY Modifiers


What is a good script or free/trial tool to inventory/replicate all settings in master db?

Fastest way...
1. Connect to instance with CU5 master
2. Right-click master database> tasks> Generate Scripts
3. Wizard is launched for scripting database objects.
To get server principals:
How to transfer logins and passwords between instances of SQL Server

Or, Powershell!
Automated Script-generation with Powershell and SMO


SQL Server 2008r2 won't start after windows updates, error log & wmi provider errors

The official solution as per Microsoft article is:

  To work around this problem, add the domain user account to the SQLServer2005MSSQLUser$ComputerName$InstanceName domain group.

  To do this, follow these steps:
  1. Click Start, point to Administrative Tools, and then click Active Directory Users and Computers.
  2. In the Active Directory Users and Computers snap-in, click Users.
  3. Double-click SQLServer2005MSSQLUser$ComputerName$InstanceName.
  4. In the Properties dialog box, click the Members tab.
  5. On the Members tab, click Add.
  6. In the Select Users, Contacts, Computers, or Groups dialog box, type the user in the DomainName\UserName format under Enter the object names to select, and then click OK.
  7. In the Properties dialog box, click OK.

References

Error message when you change the service account of SQL Server 2008 components to a non-domain administrator account on a Windows Server 2008 domain controller: "The service did not start due to a logon failure" or "WMI Provider Error" (Microsoft Support)
WMI Provider Error - When Changing SQL Services Account (SQLServerCentral)

SQL Server Configuration Manager

When you run the SQL Server Configuration Manager and modify settings the configuration manager will add and remove permissions on files and directories based on the configuration changes made.

This is why Microsoft states, that configuration changes should always be made with the SQL Server Configuration Manager.

  Important
  Always use SQL Server tools such as SQL Server Configuration Manager to change the account used by the SQL Server or SQL Server Agent services, or to change the password for the account. In addition to changing the account name, SQL Server Configuration Manager performs additional configuration such as setting permissions in the Windows Registry so that the new account can read the SQL Server settings. Other tools such as the Windows Services Control Manager can change the account name but do not change associated settings. If the service cannot access the SQL Server portion of the registry the service may not start properly.

Reference: SQL Server Configuration Manager (Microsoft Docs)

Actual Steps To Perform

In your case you might have performed several of the steps, but not in the correct order. Try the following:

Open SQL Server Configuration Manager
Set the SQL Server (INSTANCE) service account to a user you don't want the service to run under
Restart the SQL Server (INSTANCE) service for your instance (should fail)
Close SQL Server Configuration Manager
Add the service account that you want the SQL Server service to run under to the specified group (as detailed at the beginning of the post; steps 1-7)
Open SQL Server Configuration Manager
Set the SQL Server (INSTANCE) service account to the user you just added to the SQLServer2005MSSQLUser$ComputerName$InstanceName group.
Restart SQL Server (INSTANCE) service for your instance

In the case that this doesn't work you may have broken ACLs (permissions) at some level in the SQL Server Program Files structure.

SQL Server Accounts And ACLs

Your service still isn't starting and it could still be an issue with permissions (ACLs)?

Then you might have to fix the file/folder permissions. The full list of SQL Server Account and the required permissions are listed in the following exhaustive documentations:

Setting Up Windows Service Accounts (Microsoft Technet / 2008R2)
Configure Windows Service Accounts and Permissions (Microsoft SQL Docs / 2012)

Everywhere where MSSQLServer is referenced in the documentation, replace with the account you wish to run your SQL Server (INSTANCE) with.

For example when checking the Reviewing Access Control Lists Created for SQL Server Service Accounts section of the document, ensure that your YourServiceAccount has access to:

Service Account for | Files and Folders        | Access
--------------------+--------------------------+---------------
MSSQLServer         | Instid\MSSQL\backup      | Full control
                    | Instid\MSSQL\binn        | Read, Execute
                    | Instid\MSSQL\data        | Full control
....

Instead of MSSQLServer use YourServiceAccount.

References:

Setting Up Windows Service Accounts (Microsoft Technet / 2008R2)
Configure Windows Service Accounts and Permissions (Microsoft SQL Docs / 2016+)


Answering my own question. It turned out to be ClusterControl.

Despite having Performance Schema enabled & configured, ClusterControl's query monitor was starting & stopping slow_query_log as described here.

Disabling the Query Monitor in cluster control, and enabling slow_query_log resolved the issue.


In a DacPac deployment, if a stored procedure that is called by another stored procedure is updated, is the calling stored proc also redeployed?

Yes, it's expected.

If StoredProcA calls StoredProcB, and StoredProcB is modified, then the DacPac deployment will:

deploy the changes to StoredProcB
update the metadata for StoredProcA

What that looks like in the generated deployment script is:

GO
PRINT N'Altering [dbo].[StoredProcB]...';

GO
ALTER PROCEDURE [dbo].[StoredProcB]
/*
your new stored proc body here
*/
GO
PRINT N'Refreshing [dbo].[StoredProcA]...';

GO
EXECUTE sp_refreshsqlmodule N'[dbo].[StoredProcA]';

It's the call to sp_refreshsqlmodule that updates the modified date on StoredProcA.  The procedure hasn't been redeployed, but it's underlying metadata may have.

In your case, nothing has really changed.  The whole sp_refreshsqlmodule thing would be necessary if you updated a user-defined type that was used a parameter to StoredProcA, for example.  It's not necessary in this situation, but SSDT doesn't have complex rules about when to refresh dependent modules - it just updates them all if any have changed.


MySQL or Oracle Associate Certification?

Forgive me for sounding a little biased, but I would recommend MySQL DBA Certification for you without hesitation. I say this without reservation because that is how I become a MySQL DBA.

I actually wrote an earlier post about making such a transition from school, from a willing developer, from an unwilling developer, and from a sysadmin.

I also wrote another post on how DBAs should properly interact with developers and project managers.

I would personally say MySQL positions are very underrated in the eyes of most companies, yet the demand is out there. I feel have been respected because of my certification (unique experience, result vary based on who hires). I certified back in August 2007 with my employer paying for the course. At that time, no instructor-led MySQL course was required for certification. Now that Oracle owns MySQL, that may have changed. Please check with Oracle on that.

Most companies that hire MySQL DBAs may expect you to know everything else, (Linux, Apache, Nagios, Munin, JBoss, PHP, Perl, Python, etc). Many other companies may use only MySQL. Overall, you have to be selective when looking. The more disciplines/skills you have outside of MySQL, the better your employment chances are.

What makes the most money is a different story. Hands down, Oracle DBA/Developers make more money. Certification is more expensive because Oracle Associate requires only an exam. To earn deeper Certifications in Oracle, you take necessary exam(s) and you must attend at least one instructor-led Oracle course (Exams run about $200-$300, while the courses run $3,000/each). While Oracle Certification is more rigorous, the respect you get for working with Oracle and the money will indeed come. You can pretty much write your own ticket an an OCP. Going for OCA is a good start.

Regardless of MySQL, Oracle or any other RDBMS, nothing can substitute for having working experience. Due diligence in all DBAs takes will train you to certify quickly.

If there are any Oracle gurus out there, please post your own answers on Oracle as I am sure there a lot more positive things that can be said for Oracle.


Help with indexing narrow table with many millions of rows

Based on your question, I would index the Timestamp column with the clustered index. And to make the index unique, just make sure to include the identity column in the index definition:

... PRIMARY KEY CLUSTERED ([Timestamp], [Id])

If query performance for queries on Exchange_Id is still an issue after that, you can also add a non-clustered index that looks something like this:

UNIQUE INDEX ([Exchange_Id], [Timestamp], [Id])

.. but if you do, consider including any columns that the query may need, in order to create what's known as a covering index.

UNIQUE INDEX ([Exchange_Id], [Timestamp], [Id]) INCLUDE ([Type], [Amount], [Price])

Remember that there's a disk space issue involved as well, as you stated that your table contains a large number of rows. The clustered index will not change the amount of disk space your table consumes either way, but adding a non-clustered index will allocate extra space. If you INCLUDE all the columns from your table, like I did in the example, the non-clustered index will roughly take up as much space as the rest of the table does.


OS error 23 is "Data error (cyclic redundancy check)."
you can check those by running executing on the command prompt net helpmsg 23, this basically means that you have a serious error on your disk.

As the database is running what you need to to is to

DONT PANIC
Find your backups
Script your replication setup
Do a tail-of-the-log backup to have a restore point.
Run DBCC CHECKDB with NO_INFOMSGS, ALL_ERRORMSGS and keep the output
Turn of the SQL Server and copy the data and log files to a safe place -if possible
Run CHKDSK on the log drive.
Restart the SQL Server and run DBCC CHECKDB again if it comes out clean you should be able to restart the replication if not you will have to restore the database.


Your join is incorrect.  It is excluding all rows since TimeStamp can never be before the start date and after the end date.  Change it to:

JOIN b ON a.StartDate < b.TimeStamp
       AND a.EndDate > DATEADD(mm,10,b.TimeStamp)

Also, don't use the contraction, mm to indicate MINUTE (it actually indicates MONTH!).  See this Microsoft Docs page for details.  Use MINUTE:

JOIN b ON a.StartDate < b.TimeStamp
       AND a.EndDate > DATEADD(MINUTE, 10, b.TimeStamp)

To get a calculated total amount, you'll need to use the SUM() aggregate:

SELECT a.StartDate
    , a.EndDate
    , TotalAmount = SUM(b.Amount)
FROM a
JOIN b ON a.StartDate < b.TimeStamp
       AND a.EndDate > DATEADD(MINUTE, 10, b.TimeStamp)
GROUP BY a.StartDate
    , a.EndDate;

Pro-rating the start and end amounts, as you appear to indicate in your question, might be accomplished like this:

SELECT a.StartDate
    , a.EndDate
    , b.TimeStamp
    , b.Amount
    , ProRatedAmount = CASE WHEN DATEDIFF(MINUTE, a.StartDate, b.TimeStamp) < 0 AND DATEDIFF(MINUTE, a.StartDate, b.TimeStamp) > -10
            THEN b.Amount - (DATEDIFF(MINUTE, b.TimeStamp, a.StartDate) / 10.0 * b.Amount)
        WHEN DATEDIFF(MINUTE, a.EndDate, b.TimeStamp) < 0 AND DATEDIFF(MINUTE, a.EndDate, b.TimeStamp) > -10
            THEN DATEDIFF(MINUTE, b.TimeStamp, a.EndDate) / 10.0 * b.Amount
        ELSE b.Amount
        END
FROM a
    JOIN b ON DATEADD(MINUTE, -10, a.StartDate) < b.TimeStamp
       AND b.TimeStamp < DATEADD(MINUTE, 10, a.EndDate);

╔═════════════════════════╦═════════════════════════╦═════════════════════════╦════════╦════════════════╗
║        StartDate        ║         EndDate         ║        TimeStamp        ║ Amount ║ ProRatedAmount ║
╠═════════════════════════╬═════════════════════════╬═════════════════════════╬════════╬════════════════╣
║ 2018-03-06 10:13:00.000 ║ 2018-03-06 10:45:00.000 ║ 2018-03-06 10:10:00.000 ║    100 ║      70.000000 ║
║ 2018-03-06 10:13:00.000 ║ 2018-03-06 10:45:00.000 ║ 2018-03-06 10:20:00.000 ║     89 ║      89.000000 ║
║ 2018-03-06 10:13:00.000 ║ 2018-03-06 10:45:00.000 ║ 2018-03-06 10:30:00.000 ║     50 ║      50.000000 ║
║ 2018-03-06 10:13:00.000 ║ 2018-03-06 10:45:00.000 ║ 2018-03-06 10:40:00.000 ║     78 ║      39.000000 ║
╚═════════════════════════╩═════════════════════════╩═════════════════════════╩════════╩════════════════╝

Aggregating those amounts into a total might be accomplished with a CTE:

;WITH src AS
(
SELECT a.StartDate
    , a.EndDate
    , Amount = CASE WHEN DATEDIFF(MINUTE, a.StartDate, b.TimeStamp) < 0 AND DATEDIFF(MINUTE, a.StartDate, b.TimeStamp) > -10
            THEN b.Amount - (DATEDIFF(MINUTE, b.TimeStamp, a.StartDate) / 10.0 * b.Amount)
        WHEN DATEDIFF(MINUTE, a.EndDate, b.TimeStamp) < 0 AND DATEDIFF(MINUTE, a.EndDate, b.TimeStamp) > -10
            THEN DATEDIFF(MINUTE, b.TimeStamp, a.EndDate) / 10.0 * b.Amount
        ELSE b.Amount
        END
FROM a
    JOIN b ON DATEADD(MINUTE, -10, a.StartDate) < b.TimeStamp
       AND b.TimeStamp < DATEADD(MINUTE, 10, a.EndDate)
)
SELECT src.StartDate
    , src.EndDate
    , Amount = SUM(src.Amount)
FROM src
GROUP BY src.StartDate
    , src.EndDate
ORDER BY src.StartDate
    , src.EndDate;

╔═════════════════════════╦═════════════════════════╦════════════╗
║        StartDate        ║         EndDate         ║   Amount   ║
╠═════════════════════════╬═════════════════════════╬════════════╣
║ 2018-03-06 10:13:00.000 ║ 2018-03-06 10:45:00.000 ║ 248.000000 ║
╚═════════════════════════╩═════════════════════════╩════════════╝


Creating index on TIMESTAMP column to use with range operators

The name "foo_date" indicates a date and is a bad choice for a timestamp column. Option 1 would work just fine with actual dates.
A plain btree index in combination with Option 2 is the unequivocally best solution. Look no further. Except maybe for the special case of a BRIN index for large tables with physically sorted data. See:

Speed up creation of Postgres partial index

Proof of concept

That said, to make a GiST or SP-GiST index work, you could create an expression index on fake ranges. You do not need the module btree_gist for this. Demonstrating with SP-GiST since that is typically a bit faster here. See:

Perform this hours of operation query in PostgreSQL

CREATE INDEX foo_date_spgist_idx ON foo USING spgist(tsrange(foo_date, foo_date, '[]'));

SELECT * FROM foo
WHERE  tsrange(foo_date, foo_date, '[]') <@ tsrange('2018-01-01', '2018-02-01')

Or with range literal:

...
WHERE  tsrange(foo_date, foo_date, '[]') <@ '[2018-01-01,2018-02-01)'

But: bigger, more expensive to maintain, slower than the btree index. Not even less cumbersome to write. Pointless for your case.

Aside: technically, you could:

... WHERE foo_date BETWEEN '2018-01-01' AND '2018-01-31 23:59.999999';

The Postgres timestamp type is (currently) implemented with µs resolution, i.e. max. 6 fractional digits. Hence the expression does exactly what you want. But I strongly advice against building on this implementation detail. Option 2 is the way to go. Related:

Interval (days) in PostgreSQL with two parameters
Ignoring time zones altogether in Rails and PostgreSQL


Update Table contents from another table

According to Postgres docs (9.6):

UPDATE Products as P
SET P.CategoryCode = T.CategoryCode,
    P.CategoryName = T.CategoryName,
    P.SubcategoryID = T.SubcategoryID,
    P.SubcategoryName = T.SubcategoryName,
    P.BarCode = T.BarCode,
    P.Weight = T.Weight,
    P.ManufacturerSKU = T.ManufacturerSKU,
    P.ProductName = T.ProductName,
    P.WarrantyLength = T.WarrantyLength,
    P.Manufacturer = T.Manufacturer,
    P.DBP = T.DBP,
    P.DBP5 = T.DBP5,
    P.RRP = T.RRP,
    P.StandardRRP = T.StandardRRP,
    P.AvailabilityTotal = T.AvailabilityTotal,
    P.AvailabilityAdelaide = T.AvailabilityAdelaide,
    P.AvailabilityBrisbane = T.AvailabilityBrisbane,
    P.AvailabilitySydney = T.AvailabilitySydney,
    P.AvailabilityMelbourne = T.AvailabilityMelbourne,
    P.AvailabilityPerth = T.AvailabilityPerth,
    P.ImageUrl = T.ImageUrl,
    P.ImageFilename = T.ImageFilename,
    P.DescriptionLink = T.DescriptionLink
FROM tmpprod as T
WHERE P.StockCode = T.StockCode;

Although I used an alias, it is not indispensable.


Premium tiers all have SSD storage and are intended for IO intensive workloads. DTUs of Standard Tiers do not have the computation power of Premium tiers. Premium tiers have a dedicated hardware on Azure.

If the current workload reaches the DTU limit then throttling may be occurring. Those spikes in DTU may be related to missing indexes (indexes that need to be created) and to queries that need optimization. After creating missing indexes and optimizing some queries you may find DTU consumption is under control and you don't need to scale up the service tier of your database.

Make sure you enable Automatic Tuning to allow Azure create indexes needed on your database. Use Intelligent Insights to understand more if you need to scale up the tier of the database or not.


MySQL - Unix timestamp from columns with hour, day, month

Instead of transforming the columns in the WHERE clause, do so with the constants given by the application. Those calculations only have to be done once.

We can write the condition in a short, compact form:

SELECT STR_TO_DATE(...)
FROM a
WHERE  (2016, a.month, a.day, a.hour, 0, 0)
      >
      (  YEAR(FROM_UNIXTIME(456456)),
        MONTH(FROM_UNIXTIME(456456)),
          DAY(FROM_UNIXTIME(456456)),
         HOUR(FROM_UNIXTIME(456456)),
       MINUTE(FROM_UNIXTIME(456456)),
       SECOND(FROM_UNIXTIME(456456))
      )
  AND  (2016, a.month, a.day, a.hour, 0, 0)
      <
      (  YEAR(FROM_UNIXTIME(789789)),
        MONTH(FROM_UNIXTIME(789789)),
          DAY(FROM_UNIXTIME(789789)),
         HOUR(FROM_UNIXTIME(789789)),
       MINUTE(FROM_UNIXTIME(789789)),
       SECOND(FROM_UNIXTIME(789789))
      )

or in a long form, which has much more chances of using indexes effectively, in versions before 5.7:

SELECT STR_TO_DATE(...)
FROM a
WHERE ((2016 > YEAR(FROM_UNIXTIME(456456)))
    OR (2016 = YEAR(FROM_UNIXTIME(456456))
        AND a.month > MONTH(FROM_UNIXTIME(456456)))
    OR (2016 = YEAR(FROM_UNIXTIME(456456))
        AND a.month = MONTH(FROM_UNIXTIME(456456))
        AND a.day > DAY(FROM_UNIXTIME(456456)))
    OR (2016 = YEAR(FROM_UNIXTIME(456456))
        AND a.month = MONTH(FROM_UNIXTIME(456456))
        AND a.day = DAY(FROM_UNIXTIME(456456))
        AND a.hour > HOUR(FROM_UNIXTIME(456456))))
AND   ((2016 < YEAR(FROM_UNIXTIME(789789)))
    OR (2016 = YEAR(FROM_UNIXTIME(789789))
        AND a.month < MONTH(FROM_UNIXTIME(789789)))
    OR (2016 = YEAR(FROM_UNIXTIME(789789))
        AND a.month = MONTH(FROM_UNIXTIME(789789))
        AND a.day < DAY(FROM_UNIXTIME(789789)))
    OR (2016 = YEAR(FROM_UNIXTIME(789789))
        AND a.month = MONTH(FROM_UNIXTIME(789789))
        AND a.day = DAY(FROM_UNIXTIME(789789))
        AND a.hour < HOUR(FROM_UNIXTIME(789789)))
    OR (2016 = YEAR(FROM_UNIXTIME(789789))
        AND a.month = MONTH(FROM_UNIXTIME(789789))
        AND a.day = DAY(FROM_UNIXTIME(789789))
        AND a.hour = HOUR(FROM_UNIXTIME(789789))
        AND 0 < MINUTE(FROM_UNIXTIME(789789))+SECOND(FROM_UNIXTIME(789789))))

This will also allow MySQL to use indexes on this query, if available. If not already done, you should create an index as (month, day, hour) (in this order!).


Transactional replication from SQL Server 2000 to 2008

Could you try moving the distributor over on to the SQL 2008 box. Replication got much better after SQL 2005. Looks like you are using anonymous subscriptions, do you not have VPN so that servers can see each other? I am not familiar with Internet based subscribers so probably not much help on its specifics.


Copy database from one mysql sever to another mysql server

There are several methods you can use such as

mysqldump
folder copy
enterprise backup

for mysqldump use following command:

mysqldump -uuser -ppassword --database your_db_name --routines --triggers --events --lock-all-tables >outputfile.sql

and use following command at destination:

mysql -uroot -ppassword<outputfile.sql.

You can also use following method
  - stop server at source
  - stop server at destination
  - copy all the database folders from source to destination
  - start destination server
  - start source server

Hope it helps


Why can SELECT see updated row before commit?

Assuming the second SSMS window is using the default READ_COMMITTED isolation level, you will see the before value instead of being blocked by the uncommitted transaction if the database has the READ_COMMITTED_SNAPSHOT setting turned on.

SELECT name, snapshot_isolation_state_desc
FROM sys.databases
ORDER BY name;

The READ_COMMITTED_SNAPSHOT database setting uses row versioning instead of locking for read consistency so writers don't block readers and visa-versa.


Subtracting an integer in my db by 1. SQL

SQL allows to update the number of rows at once:

UPDATE users
   SET follower_num = follower_num + 1
 WHERE user_id IN (1,2,3,4...)

WHERE clause can contain any conditions you want. All users that satisfy that conditions will be updated.


Understanding `null` with Oracle Function's IF-Statement

If you pass any number except the number 1 then your select returns no data and raises a no_data_found exception.  Not sure what you are wanting to do but a simple solution would be to add an exception.

create or replace FUNCTION nullFunction(a NUMBER) RETURN NUMBER IS
varX NUMBER;
BEGIN
select 1 into varX from dual where exists(select 1 from dual where a = 1);
 RETURN 1;
EXCEPTION
WHEN no_data_found THEN
RETURN 0;

END;

Alternatively restructure the select from dual so that it cannot return a no_data_found.


I think you need to think some more :)

2) "there's no common attributes" is certainly true but irrelevant. We know there is a correspondence between C and D (and therefore are of the same type) because of the referential integrity constraint even though they have difference names.

e.g. here's an example in SQL that satisfies the conditions yet returns a row:

CREATE TABLE s
(
 D INTEGER NOT NULL,
 E INTEGER NOT NULL,
 F INTEGER NOT NULL,
 PRIMARY KEY (D)
);

CREATE TABLE r
(
 A INTEGER NOT NULL,
 B INTEGER NOT NULL,
 C INTEGER NOT NULL,
 PRIMARY KEY (A),
 FOREIGN KEY (C)
    REFERENCES s (D)
);

INSERT INTO s (D, E, F) VALUES (1, 1, 1);
INSERT INTO r (A, B, C) VALUES (1, 1, 1);
INSERT INTO r (A, B, C) VALUES (2, 2, 1);

SELECT *
  FROM s JOIN r ON B = E;

p.s. where you used the term 'records' you should actually use the term 'tuples'.


Microsoft Access 2003 vs SQL Server 2005 Express

Few things to consider with using SQL Express vs Access 2003:

SQL Server is limited to 1 CPU
SQL Server is limited to 1 Gig RAM
SQL Server is limited 4 Gig database file (2 Gig on Access)
Creating front end logic for users will take a longer time than Access 2003
Using SQL more reliable as you can do point in time recovery

More comparisons among the two in this link from ZDNET (UK)


You need to check the server's error log(s).  There are too many situations that could cause this problem.  For example:

The table is corrupt, and InnoDB detects this while reading an invalid page checksum.  The default behavior here is to shutdown to force an administrator to intervene.
Your server is over configured to use way too much memory.  The OOM killer terminates mysqld.
There is no problem at all.  The client disconnects, but the server keeps running the ALTER statement, and it eventually finishes.


Replace some rows in one table with rows of another table

UPDATE table1, table2
SET table1.field1 = table2.field1,
    table1.field2 = table2.field2,
--  .....
    table1.fieldN = table2.fieldN
WHERE table1.id = table2.id
  AND table1.id IN ('id1', 'id2', /* ... */ 'idN' )

Or you can use

REPLACE table1
SELECT *
FROM table2
WHERE table1.id IN ('id1', 'id2', /* ... */ 'idN' )

but it can give undesired interferention if there is any additional unique index except primary one.


Monitoring slow queries on large database (Percona 5.6)

You can use pt-query-digest --processlist
While there are four different ways to use it, IMHO the best way to use it is to poll the processlist and collect metrics every hour. This method does not need a slow query log.
I have suggested this before (See mysql 5.7 general_log table has user_host but the file does not)
I have promoted this idea many times over the years.


PostgreSQL: Log statements which do sequential scans

I think the only way to do this is to use the auto_explain module and enable dumping of execution plans if a statement is slower than e.g. a second.

The plan will be written into the Postgres log file

Then you can have a job that monitors the log file and takes actions if a Seq Scan is part of the plan.

A Seq Scan is not something that should be avoided at all costs. It often the fastest way to get the result.

I would focus on plans with Seq Scans, but simply try to find (and then fix) slow queries.

For analysing the logfile you want to have a log at pgbadger or you can use POWA to monitor your server in real time


How to access PostgreSQL Functions from Oracle through DBLink?

Have you tried creating a view in PostgreSQL and then query the view from Oracle? You need PostgreSQL to present the function like a view since Oracle doesn't really have that function.
I don't currently support PostgreSQL. However, if you create a view in PostgreSQL and test it to verify that it works, you should be able to select from that view in Oracle. It looks like the issue is selecting from a function, not selecting from PostgreSQL.


should I really disable the page file? and reduce it to 8 GB?

It isn't talking about disabling the page file - it is referring to disabling the auto-size settings.

  is there a rule for the size of the page file on dedicated sql server servers?

There are a great many, often contradictory, rules-of-thumb for the size and location of page files under various scenarios so the question will probably get closed as "opinion based" soon.

A few of my thoughts:

You certainly shouldn't turn of the page file completely.
If you have disk space issues on C: and can't grow C: then saving 24G this way would be useful to you. As an alternative to dropping the swap space completely you could move it to another drive?
Of course you probably don't ever want the machine using 8Gb of swap space, let alone 32. By that point the IO from swapping pages is going to be significantly slowing down the server, it'll have probably have ground to an effective halt before you even get close to 32Gb (at which point you have as much swapped out as in physical RAM).
If the machine is doing nothing except running SQL Server, then you should have the memory settings for the SQL instance(s) set such that it won't exhaust RAM ever. There will still be a little swapping out to the page file as the OS will prefer to page out bits of itself & other services that aren't touched, to favour file cache & buffers for things that are occasionally accessed.


Doing a backup and restore will be a good way to copy over all the user databases.

Backup DBs on the source server.
Copy the backup file to the destination server
Then restore the DBs onto the destination server.

The the system DBs are a bit different. Follow this link for one possible way. Then again, it depends what you need from the system tables. The master contains your SQL logins, which you can script out. Here is a link for scripting out the logins.


postgresql query hangs indefinitely over VPN - new firewall

We solved the problem. Technically, we never figured out exactly why it was happening, but UDP packets were getting received by the VPN server in an incorrect order, or sometimes not at all. The firewall didn't give us any indication that it was doing this, so as best we can figure it was an unlogged side-effect of CheckPoint's multiple layers of threat protection.

We switched over to using OpenVPN over TCP instead of UDP, and that seems to have solved the problem. Hopefully we don't suffer any adverse effects from that in the long-term!


Postgres Pass Subquery results to function that returns TABLE

Write a PL/pgSQL function and iterate through the conversation_view results:

DECLARE
   sub record;
BEGIN
   FOR sub IN
      SELECT 'conversations_zdx' as idx,
             ARRAY_AGG(id)::TEXT[] as labels,
             ARRAY_AGG(filters) as filters
      FROM conversation_views
      GROUP BY organization_id
   LOOP
      RETURN QUERY
         SELECT idx, labels, filters
         FROM zdb.filters(
                 sub.idx,
                 sub.labels,
                 sub.filters
              ) AS q(idx,labels,filters);
   END LOOP;
END;


SQL Server replication with subscriber doesn't know about the publisher

A VPN connection can be used to connect to each other. But moving a huge data over Internet is done using DHCP connection, And may be slower depending on your internet connection.


Average Time That an App's Client is connected to a Database?

MySQL does not keep any metric like that.

I would instrument the client in order to get the info.  (In fact, I have done such.)  At the beginning of the client (or when the client connects), capture the time to sufficient precision to get milliseconds.  At the end of the client (or connection), capture the time again.  Save the result in a table for such.  Later do SELECT AVG(...)... to get the information you desire.  It is a more complex mathematical problem to say "Most connections are between 50ms and 70ms."  For that, you might want to deduce the 10th and 90th percentiles.  (This is best done in client code, not SQL.)


Separate SQL Logins for reading from and writing to table good design?

You simply need to follow the Principle of Least Privilege.

If you have one entity, whether it be a user or an application, that entity should have an account with the minimum permissions required to do its work.  So if you will have a web API that will only read from one table, then its website should be using an account that only has permission to read that one table.

Your question doesn't seem to indicate who or what will be using the read/write account.  If nobody will be writing to the table (which I'm guessing is correct, based on the fact that the data came from another source), then only the ETL process should have permission to write to the table.


If you want to get just project id and clients (which are both on project table) there is no need to join Billings :

SELECT p.Project_ID, p.Project_Client
FROM  PROJECT p
    WHERE EXISTS
      (
         SELECT NULL FROM BILLINGS b WHERE b.Project_ID = a.Project_ID
         AND b.Bill_Date > 'your_date'
      )


MySQL query exponentially slower with parameter in subquery vs hard-coded value

Posted a bug / feature request with Oracle, they say it is a known issue. So there are two work-arounds:

1] Only use hard-coded values in sub-queries, no parameters. Not really an option for me, but may be for some people.

2] Create a temporary data set and filter the outer query with it in code behind. This is the route I went with. I create a set of strings for the inner query using a separate query, then I filter the outer query with this set in code behind to produce the final set.


Execute sp_update_jobstep to change SQL Agent job step type

You can use the @subsystem=N'TSQL' parameter of the sp_update_jobstep to alter the Step-Type.  Refer to sp_add_jobstep for a list of valid subsystem values.

Example:

EXEC msdb.dbo.sp_update_jobstep @job_name = N'test'
    ,@step_id = 1
    ,@subsystem = N'TSQL'
    ,@command = N'EXECUTE [dbo].[IndexOptimize], @Databases = ''USER_DATABASES'', @LogToTable = ''Y'', @LockTimeOut = 300'


SQL Server take an unencrypted full backup

By default SQL will not encrypt backups. From what I see in your screenshot, the backup you're taking there, will not be encrypted either. (The option is just disabled)
A simple way would be to just use T-SQL to create your backup. ( Note copy_only unless you want to be starting a new backup chain) (and replace the folder with your backup dir)
BACKUP DATABASE DBase TO DISK = '\\Folder\DBase_20200109_1034.bak' WITH COPY_ONLY,STATS = 5

Check BOL if you need more parameters like compression.
Aslong as you don't add the encryption clause, your backup will remain non-encrypted.
Of course, feel free to use the GUI as well, aslong as you don't set "Back up to a new media set, ...." and click to encrypt your backups they should all be non-encrypted


SQL Server AG Listener issues in an AWS VPC

Can you please review the AWS documentation on how to properly set up AG on their environment?
http://docs.aws.amazon.com/quickstart/latest/sql/part3.html

There are some specific steps that need to be done for this to work.
You may also want to read about the MultisubnetFailover (https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/sqlclient-support-for-high-availability-disaster-recovery) as without using this feature it may take several minutes (depending on how much you set up your HostRecordTTL setting) for the DNS setting to be updated and the listener name start pointing to the new active node IP. On my tests, without changing the HostRecordTTL and the multisubnetfailover it was taking up to 15 minutes for the listener name to start working again after a failover so I had to play with these parameters to have something that would work for all my client applications


Is there a -w equivalent for isql when already connected?

I think it is not possible to change dynamically this parameter.
I have found this article:

How do I pipe the output of one isql to another.

There, you can look at the other utilities to use in this scenario (sqsh, for example).
Luck.


MongoDB 3.2.10 Index rebuilding did not complete: 11000 E11000 duplicate key error collection

I have deleted all data from dbpath and again restarted replica set sync and it looks it is going fine now. I was trying to restart a failed replica set sync and it looks like MongoDB was not able to handle it, once I cleaned up failed replica set data and restart mongo and looks like it is passed point where it was failing before.

Basically,I have removed host from relica set configuration, cleanup dbpath, restarted mongo and again added host in replica set configuration.


Multiple service_name and hosts with same alias

You need different DESCRIPTION sections to have different SERVICE_NAME in the CONNECT_DATA section.

DWH_connection =
  (DESCRIPTION_LIST =
    (LOAD_BALANCE=OFF)
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = Server1)(PORT = 1521))
      (CONNECT_DATA =
        (SERVICE_NAME = Oracleinstance1)
        (SERVER = DEDICATED)
      )
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = Server2)(PORT = 1521))
      (CONNECT_DATA =
        (SERVICE_NAME = Oracleinstance2)
        (SERVER = DEDICATED)
      )
    )
  )

The default value of LOAD_BALANCE is ON, but in your case, connections should go to the first server, and go to the second one only if that fails, so LOAD_BALANCE=OFF is needed, otherwise connections would be distributed between the 2 servers.


Optimal for both queries:
INDEX(roomId, status,   -- first, in either order
      userId,           -- 3rd
      isMuted)

Notes:

In InnoDB, the PRIMARY KEY is tacked onto the end of each secondary index (so it can get to the record).
All 5 columns (including id) are in that index, so it is "covering" for the first query.
In both queries, the tests in the WHERE clause are completely handled by the first few columns.
In the 2nd query, userId is available, so the index is covering for that query, too.  (The extra isMuted` is not harmful.)
I assume that id is the PK of b, so that is optimal, too.
If you add my index, drop combine4.  (The left part exactly matches.)
More:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql


Should I disable filesytem journaling for MS SQL Server (Linux/Windows)

Filesystem-level journaling is not listed as a requirements for the installation of SQL Server on Windows or Linux.  SQL Server goes to great lengths to make sure writes are durable (check out this mammoth article on some improvements made in SQL Server 2017 CU6 on Linux in that regard).

Since it's not required, I would conclude that it is safe to disable.

Since, as you noted, it will inevitably result in more load (writes) on the system, I think the same guidance would apply to SQL Server as it does to Postgres (that disabling the filesystem journaling behavior would be beneficial to system performance).

As there is no official guidance around this filesystem feature that I can find, I'm just sharing my personal thoughts on that matter.


How to replicate data between two SQL Server 2012?

Lets take a quick look at the official Replication features page:

Transactional replication. For more information, see Transactional Replication.
Merge replication. For more information, see Merge Replication.
Snapshot replication. For more information, see Snapshot Replication.

In short:

Transaction replication: incremental changes between the databases.
Merge replication: like transactional but with the subsequent changes.
Snapshot replication: means a full copy/overwrite of the given database. Basically this is used for creating the initial replicated instance of your database.

You will also have to overview and setup the SQL server agents for replication.
For a basic technological understanding the SQL Server replication mechanism utilizes the Publisher-subscriber pattern.
EDIT: i was intentionally mentioning the used pattern for replication, let me elaborate on it a bit more.
Architectural overview of the basic transactional replication:

Summarizing simplified the flow of operations:

Publisher (in your case DB-1, the source database server) makes the data ready for replication.
Distributor (the chosen replication agent) is the intermediary between the publisher and subscriber (note the pattern mentioned above).
Subscriber (in your case DB-2, the destination database server) which receives the published data.

This way you replicate your data from DB-1 to DB-2, and not backwards (though you can configure it to do that too, if you may want).


Need help with Regex in SQL Server query editior

If you are willing to do this in two step, here is an approach that works, using the Regex option in SSMS's Replace dialog.

Step 1

Delete all line endings, squashing it all into a single line.

Replace: \r\n

With:

Explanation: The \r\n looks for a line ending, which is almost certainly a Carriage Return (\r) followed by a New Line (\n). Replace it with nothing.

Step 2

Replace all occurrences of "END" with "END" plus a new line ending.

Replace: END

With: END\r\n

Explanation: Replace "END" with "END" plus a carriage return plus a new line.

Oddity

Your example above has two spaces after the first "END", and no spaces after the second one. This will cause the results after Step 2 to look something like this:

IF NOT EXISTS...
  IF NOT EXISTS...

If your real code also has that oddity, you can fix that by ensuring that you don't have spaces after "END", with a Step 0 of:

Replace: END (two spaces after "END")

With: END (no spaces after "END")

Tested in SSMS 17.9.1.


granting access for remote account to local database instance

You are going to want to create an AD service account on your SQL server. Then also make sure that AD service account is on application server, and is you local admin group on that box (I'm guessing on this part).

Create the AD Service account
Add the newly created AD account to your SQL Server, but wait a bit for the AD account to propagate. Give it a minute or five.
Create a new SQL login, keep Windows Auth selected, and click the Search... button
When the Select User or Group window appears, click the Locations... button If you are wanting to add a group, then you will want to click the Objects Type button, and select the Groups checkbox before you search

On the Locations window select Entire Directory, and click OK

Enter in the user name you just created, and click the Check Names button. If it finds the user you will know, and click the OK button
Now, assign it to a server role, or assign it a database with the permissions needed for your application.

Now try to connect your application server to your SQL Server.

Now, you can cheat. However, I wouldn't recommend this, but if you don't really care who is making the call from your application server. Run this script below. Change the domain to your domain, and replace SERVERNAME with the name of your server. Once this created on the SQL server give it perms to the server or database in question.

 CREATE LOGIN [DOMAIN\SERVERNAME$] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]


I would not touch that directory, I have not found any references of other users using the SQL Developer software that comes with database binaries. Additionally, SQL Developer shipped with the database binaries is kind of outdated. Even 12.1.0.2 comes with SQL Developer 3.2

I would just perform a seperate install.

Also, I find it unusual that someone, who is not the DBA, wants to run SQL Developer on the database server itself. Why not just install it on the client side?

(To be honest, I would not even include SQL Developer in the database binaries package and install process, but it can not be customized.)


The DBA_OBJECTS view describes all the objects in the database. In it, you can find, by instance, the name of an object, its owner or its type.

Example:

SELECT owner, object_name, object_type FROM dba_objects WHERE object_name = 'MY_OBJECT'; -- Be careful, the name is case sensitive.

OWNER               OBJECT_NAME         OBJECT_TYPE
------------------- ------------------- -------------------
MY_USER             MY_OBJECT           PROCEDURE

Now, you can easily drop it:

DROP PROCEDURE MY_OBJECT;


How to update/insert/inject nodes into XML

You just need to specify the first SiteInfo node using [1]

update [dbo].[Sites]
SET [SiteInfo].modify('insert <Anothernode>ABC123</Anothernode> into (/SiteInfo[1])')
where Siteid = 1


Given the data as is (as of 11:38 UTC, 2021-04-08), this might help (see PostgreSQL fiddle here):
CREATE TABLE tab
(
  user_id TEXT NOT NULL,
  step    TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL,
  device  TEXT NOT NULL
);

Populate it (slightly rearranged from original data to put it in order - user_id, step & created_at - makes it easier to figure out what's going on):
INSERT INTO tab
VALUES

( 'user1', 'step1', '2021-03-16 14:03:16', 'mobile'),
( 'user1', 'step1', '2021-03-16 14:04:02', 'desktop'),

( 'user1', 'step2', '2021-03-16 14:03:16', 'mobile'),
( 'user1', 'step2', '2021-03-16 14:03:47', 'desktop'),
( 'user1', 'step2', '2021-03-16 14:04:07', 'mobile'),

( 'user1', 'step3', '2021-03-16 14:03:55', 'mobile'),
( 'user1', 'step3', '2021-03-16 14:04:00', 'mobile'),
( 'user1', 'step3', '2021-03-16 14:04:07', 'mobile'),

( 'user1', 'step4', '2021-03-16 14:04:08', 'desktop'),
( 'user1', 'step4', '2021-03-16 14:04:09', 'tablet');

I'll show the steps that led me to the solution - might be helpful (remove surplus field as appropriate for your requirements):
SELECT
  user_id, step, created_at, device,
  FIRST_VALUE(device)     OVER (PARTITION BY user_id, step
                            ORDER BY     user_id, step, created_at),
  FIRST_VALUE(created_at) OVER (PARTITION BY user_id, step
                            ORDER BY     user_id, step, created_at),
  LEAD(device)            OVER (PARTITION BY user_id, step
                            ORDER BY     user_id, step, created_at),
  CONCAT(
  (FIRST_VALUE(device)     OVER (PARTITION BY user_id, step
                            ORDER BY     user_id, step, created_at)),
  ' > ',
  (LEAD(device)            OVER (PARTITION BY user_id, step
                            ORDER BY     user_id, step, created_at)))
  AS device_concatenated,
  ROW_NUMBER() OVER (PARTITION BY user_id, step
                            ORDER BY     user_id, step, created_at) AS rn
FROM tab
ORDER BY user_id, step, created_at;

Result (better viewed on fiddle):
user_id step    created_at  device  first_value first_value lead    device_concatenated rn
user1   step1   2021-03-16 14:03:16 mobile  mobile  2021-03-16 14:03:16 desktop mobile > desktop    1
user1   step1   2021-03-16 14:04:02 desktop mobile  2021-03-16 14:03:16     mobile >    2
user1   step2   2021-03-16 14:03:16 mobile  mobile  2021-03-16 14:03:16 desktop mobile > desktop    1
user1   step2   2021-03-16 14:03:47 desktop mobile  2021-03-16 14:03:16 mobile  mobile > mobile 2
user1   step2   2021-03-16 14:04:07 mobile  mobile  2021-03-16 14:03:16     mobile >    3
user1   step3   2021-03-16 14:03:55 mobile  mobile  2021-03-16 14:03:55 mobile  mobile > mobile 1
user1   step3   2021-03-16 14:04:00 mobile  mobile  2021-03-16 14:03:55 mobile  mobile > mobile 2
user1   step3   2021-03-16 14:04:07 mobile  mobile  2021-03-16 14:03:55     mobile >    3
user1   step4   2021-03-16 14:04:08 desktop desktop 2021-03-16 14:04:08 tablet  desktop > tablet    1
user1   step4   2021-03-16 14:04:09 tablet  desktop 2021-03-16 14:04:08     desktop >   2

Then, we use the above result as a sub-query as follows:
SELECT user_id, step, created_ts, device, dc AS device_concatenated
FROM
(
  SELECT
    user_id, step, created_at, device,
    FIRST_VALUE(device)     OVER (PARTITION BY user_id, step
                                  ORDER BY     user_id, step, created_at),
    FIRST_VALUE(created_at) OVER (PARTITION BY user_id, step
                                  ORDER BY     user_id, step, created_at) AS created_ts,
    LEAD(device)            OVER (PARTITION BY user_id, step
                                  ORDER BY     user_id, step, created_at),
    CONCAT(
    (FIRST_VALUE(device)   OVER (PARTITION BY user_id, step
                                 ORDER BY     user_id, step, created_at)),
    ' > ',
    (LEAD(device)            OVER (PARTITION BY user_id, step
                            ORDER BY     user_id, step, created_at)))
  AS dc,
  ROW_NUMBER() OVER (PARTITION BY user_id, step
                            ORDER BY     user_id, step, created_at) AS rn
  FROM tab
  ORDER BY user_id, step, created_at
) AS t
WHERE rn = 1
ORDER BY user_id, step, device, created_ts;

Result:
user_id   step  created_ts             device   device_concatenated
  user1  step1  2021-03-16 14:03:16    mobile       mobile > desktop
  user1  step2  2021-03-16 14:03:16    mobile       mobile > desktop
  user1  step3  2021-03-16 14:03:55    mobile        mobile > mobile
  user1  step4  2021-03-16 14:04:08   desktop       desktop > tablet

Which is pretty close to the desired result. To obtain the desired result, we need to know how/why for step3, mobile > desktop is valid and mobile > mobile is not? Same goes for step4 - why is desktop > tablet not correct? There isn't even a single entry for mobile for step4... On receiving an explanation, I will edit as appropriate!


WorldWide site - should I save UTC or in fixed time?

Always store data in the database in UTC and then use the appropriate timezone to convert it during display, in the application. Is the only sensible approach when it comes to consider data mobility (geo-replication), user location and , most importantly, daylight savings. If you store local time there will be times when you cannot distinguish between column values and you cannot order them properly due to daylight savings. Think that, in Germany, Oct. 27th 2:05 am will happen twice this year... And that is an easy case compared to, say, Morocco.


how to make a table unlocked within transaction block

You'd have to do it outside of the transaction

For example: Spool the data in a table variable which don't participate in transactions and write once at the end (after commit). Or use Service Broker to decouple log writes.

Any logging otherwise is subject to the transaction semantics eg locks, can be rolled back etc


Best practice design schema for implementing form approval tracking?

I am working on a similar process and found this very useful site that proved helpful for my processing issues.  This details a request and approval process that can be changed to fit other applications.


In most cases, locks don't cause waits.
Imagine I have a simple banking system.  In order to transfer money from account A to account B, I start a transaction and acquire a row-level lock on both account records.  That lets me run my validations (i.e. A has enough money for the transfer, the fraud department doesn't have a flag on either account, etc.), update both accounts to reflect the new balance, generate whatever logging is needed, and commit without worrying that some other session has made a change to one or both accounts that should have prevented the transfer.
Unless some other session comes along in the fraction of a second that I'm processing the transfer which tries to lock either A or B, the locks I acquired don't generate any waits.  Sure, it's possible that another session will be processing a transaction for A at the same instant that my session is, hence the need for the lock.  But given the thousands of accounts and millions of transaction that need to be processed, it is very unlikely that there would be contention for the lock.  Particularly if I design the system sensibly so that, for example, all batched transactions for a particular account are processed by one thread.  If there is no contention, there are no waits.


If the column OtherText is null, you do not know the value of the column IssueType.  It could be fuzzy or grainy.  So IssueType is not dependent on OtherText.

Your design is not a 3NF violation.


Postgres Greatest Query not returning correct timestamp

So, I got it figured out and I'm leaving the answer for posterity.

Essentially, remove the * from the query and add a LIMIT 1 and it all appears to work. In addition, as @ypercubetm stated, you can use AND in place of OR, but it depends on your usecase.

SELECT GREATEST(books.updated_at, books.deleted_at, posts.updated_at, posts.deleted_at) AS greatest_time
FROM books, posts WHERE books.user_id = 1
OR posts.user_id = 1 ORDER BY greatest_time DESC LIMIT 1;

returns a single, latest time.


Migrating everything to do with a schema from one Oracle DB server to another

Datapump is one answer.

Look at http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm#i1007524 - specifically "Transportable Table mode".  Moves the metadata, but not the data.  You can choose which schemas to include with the SCHEMAS switch, along with the INCLUDE/EXCLUDE parameters.