How can I obtain the last inserted row with INSERT ... SELECT in PostgreSQL?

It seems that you need only the maximum of the last inserted (user_id,object_id) as a couple. The first INSERT could be:

WITH inserted_rows as (
INSERT INTO dest_table
SELECT user_id, object_id, object_type, colN, ...
FROM source_table
ORDER BY user_id, colN, object_id  -- this is indexed
LIMIT 1000  -- batch size
RETURNING user_id, object_id
)
SELECT row(user_id,object_id) FROM inserted_rows
 ORDER BY 1 DESC LIMIT 1;

This form uses a row to refer to the (user_id,object_id) couple for convenience. Row elements are compared left-to-right as needed in your case to use this couple of values as a boundary. See
 Row Constructor Comparison in the documentation.

With this solution, the subsequent INSERTs would inject the value returned by the last preceding insert. This avoids looking up the last inserted row in dest_table.

WITH inserted_rows as (
INSERT INTO dest_table
SELECT user_id, object_id, object_type, colN, ...
FROM source_table
  WHERE row(user_id,object_id) > row(...inject the result of the previous query here...)
ORDER BY user_id, colN, object_id
LIMIT 1000
RETURNING user_id, object_id
)
SELECT row(user_id,object_id) FROM inserted_rows
ORDER BY 1 DESC LIMIT 1;

  Do I need to create a function for this (e.g. plpgsql) or am I missing
  something obvious in plain SQL that let me do this? That would be
  highly favorable.

In plain SQL, you would do a single INSERT in one step without any batching. Since you want to break it into batches, there has to be some loop driving these INSERTs with a stop condition, and this procedural logic goes beyond pure SQL.

If you don't feel like reinjecting the boundary through a variable, it could be persisted in a dedicated, single-row table.


How to insert multiple records using XML in SQL server 2008

The whole "inserting multiple records with XML" has pretty much been superseded by table parameters in SQL Server 2008.

And mentioned in Erland Erland Sommarskog's Arrays and Lists in SQL Server 2008: the definitive article on this subject.

.net has better XML handling than the SQLXML implementation too.


MySQL Insert/Update across 3 tables with 1m+ rows

LEFt JOIN increases the Number of Records, you will have only have 2 two people with the same name and the same pets name and age, but hopefully you get not much of them.
But still you want only a INNER JOIN, vecause you only want records, that are actual in the MasterTable and have a pet.
so you should switch to inner joins
INSERT INTO Records(PersonID, PetID, ID)
SELECT People.PersonID, Pets.PetID, MasterTable.ID
FROM MasterTable
INNER JOIN People ON MasterTable.FirstName = People.FirstName AND People.LastName = MasterTable.LastName
INNER JOIN Pets ON Pets.PetName = MasterTable.PetName AND Pets.PetAge = MasterTable.PetAge
WHERE MasterTable.ID BETWEEN  0 AND 50000

But you limit the number of rows inserted, by only choosing a number records, try as i shown in theexampe with 50000 at first, and see if that is fast enough
And the take the next.
In case you want all people, regardless if the have a pet.
INSERT INTO Records(PersonID, PetID, ID)
SELECT p.PersonID, pe.PetID, m.ID
FROM People p
LEFT JOIN MasterTable m ON m.FirstName = p.FirstName AND p.LastName = m.LastName
INNER JOIN Pets pe ON pe.PetName = m.PetName AND pe.PetAge = m.PetAge
WHERE p.PersonID BETWEEN  0 AND 50000

Here you see that all pöople are choosen to be in the records, even when they have no pets, that is what LEFT JOIN mean.
But here you must limit the number of Entries to be inserted with person.PersonID
With a million records, you need a lot of buffer and tmp space to pluu that of, but you can serialize  by dividing the number of MasterTable.
FYI: If something like this occurs, look into the error logs of MySQL. It will show you what you have to do and what MySQL has encountered for a problem


How to run a cold backup with Linux/tar without shutting down MySQL slave?

FLUSH TABLES WITH READ LOCK will not halt writes to InnoDB.

FLUSH TABLES WITH READ LOCK does NOT work with InnoDB (MySQL Forums)
How FLUSH TABLES WITH READ LOCK works with Innodb Tables (mysqlperformanceblog.com)
How fast is FLUSH TABLES WITH READ LOCK? (mysqlperformanceblog.com)

It may block access to writing to tables, but InnoDB will allow writes to ibdata1 to provide MVCC info for redo and undo logs.
Check out Map of InnoDB's Infrastructure

Please note the physical independence of a table from the ibdata1, and how log files are related.
Since the box is a Slave, you have two options :
OPTION #1 (Warm Backup)

Run STOP SLAVE;
Run FLUSH TABLES;
Run your Linux/tar
Run START SLAVE;

OPTION #2 (Cold Backup of Fully Committed Data)

Run SET GLOBAL innodb_fast_shutdown = 0;
Run service mysql stop
Run your Linux/tar
Run service mysql start

OPTION #3 (Cold Backup)

Run service mysql stop
Run your Linux/tar
Run service mysql start

EPILOGUE
Options #2 and #3 would be the better choices by far. What is the difference between them ?

OPTION #2 flushes all transactional changes out of ibdata1 and the transaction logs (ib_logfile0, ib_logfile1). This makes for a longer shutdown. However, you will have a faster mysql startup because InnoDB Crash Recovery would not need to be performed.
OPTION #3 lets you shutdown faster, leaving InnoDB in suspended animation (all transactional changes in ibdata1 and the transaction logs (ib_logfile0, ib_logfile1)). The transactional changes are applied on mysql startup during the InnoDB Crash Recovery phase.


Does Postgres CHECKPOINT process affects streaming replication?

You are correct, the speed of promoting a standby is not affected by checkpoint timing on the primary. WAL is streamed to the standby as soon as possible, and writing WAL is mostly independent of checkpoints (the only connections are that a checkpoint writes a WAL record and removes obsolete WAL segments).
What can delay promotion is if there is a conflict that prevents replaying WAL on the standby, but that's something entirely different.


How to remove files and filesgroup from dis that have data in it?

You need to remove all allocations from that filegroup. That includes indexes, tables and partitions that resides on that filegroup. Then you can remove the files and finally the filegroup.

For just a file and to keep other files for the same filegroup (not what I think you're asking about), then you can shrink that file using the EMPTYFILE option and then remove that file.


If you see below segment of the code

CREATE DATABASE [TestTom]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'TestTom', FILENAME = N'G:\MSSQL11.MSSQLSERVER\MSSQL\DATA\TestTom.mdf' , SIZE = 1048576KB , FILEGROWTH = 1048576KB )
 LOG ON
( NAME = N'TestTom_log', FILENAME = N'F:\MSSQL11.MSSQLSERVER\MSSQL\Data\TestTom_log.ldf' , SIZE = 1048576KB , FILEGROWTH = 1048576KB )
GO

The size which you are pre-allocating is 1048576KB which is approx 1G.

The same is with Log file 1048576KB which is 1G.

Can you alter your script and put it to few MB's and see if you again get the locking. You can later run alter database modify file script to increase size. This is something I have witnessed to telling you. This is not a definitive answer but I am sure it will reduce the creation time which exactly is your problem.

Did you also noted if any process was blocking the table creation script.

Plus first run create database script and then the alter database commands.


Are you considering using SQL Server to query data inside the mesh? For example, are you trying to pass in a list of points, and then have SQL Server figure out whether points in that area match up to any of the mesh data for existing rows inside the database?

If so, you're gonna have a bad time - that's not what SQL Server is designed for.

If, on the other hand, you're just using SQL Server as a key/value store ("give me the mesh data for this desk design") then you can use any data type you like, like JSON or XML or BINARY. You're correct, though, by saying that this rarely ends well. If you're looking for key/value storage, then use a key/value data store instead.


SQL Server Replace not returning expected result

ISNULL('','0')

Returns the first argument '' (as that is not null) so your question boils down to asking why

SELECT REPLACE('','','0')

Doesn't return 0.

The documentation for REPLACE states

  string_pattern Is the substring to be found. string_pattern can be of
  a character or binary data type. string_pattern cannot be an empty
  string (''), and must not exceed the maximum number of bytes that fits
  on a page.

TSQL is not unusual in this respect.

In C# attempting "Some String".Replace("", "0") would give an error "String cannot be of zero length."

This is not surprising as in any string of text you could argue that there are infinitely many empty strings.

There could be 20 between the "T" and "h" at the start of this sentence. As they are zero length how could we tell?

For your actual use case you can use

SELECT CASE
         WHEN Threshold <> ''
           THEN CAST(Threshold AS DECIMAL(4, 2)) --Not null and not empty string
         ELSE 0
       END


Create a copy of the database in same server

do dump of your current database

mysqldump -p yourDatabase > file.sql

then create new database and restore file.sql

mysql -p -e "create database new_DB"

mysql -p new_DB < file.sql


SQL Server is caching query plans with the same query when using parameterized SQL

You've got a few different questions in here:

Q: Why optimizer create a new plan again and again which is already exists?

These attributes all affect plan reuse:

Different databases each get their own plans
Different parameter lengths get their own plans (especially with this NHibernate bug, but similar issues can occur with other ORMs as well)
Different connection settings like ANSI NULL can get their own plans
Different spacing or comments can get their own plans (like if your code is dynamically adding comments to indicate where the query came from)

Q: Is there a good way to force the optimizer reuse query plans in terms of parameterized SQL?

Yes - think about the above things that will impact plan reuse. Obviously if you have a Software-as-a-Service type model where every client gets their own database, you can't really help things there, but you can help the other two factors. Be consistent with your application's connection settings, don't inject different spacing or comments dynamically into queries, and make sure your datatypes in the application match the datatypes in the database.

Q: Is this situation normal?

It's not unusual to see out in the field, especially with older NHibernate/EF versions that used different parameter lengths based on whatever parameter you were passing in (as discussed in that link.)

The performance symptoms are higher CPU use (due to increased query compilations) and higher plan cache memory use, but they're not usually the largest problem on a SQL Server.


DateDiff Returning Dates and Not Integer Values

It's converting the integer to a datetime- the column is a datetime.

You're seeing the datetime conversion of the results of the DATEDIFF function. Implicit conversion from integer to datetime is in days beginning with 1/1/1900 as 0.

  SELECT CAST(1 AS DATETIME)

Returns

1900-01-02 00:00:00.000

If you want to store the integer result in the column, just change the data type to integer.


TSQL change column constraint between null and not null

When trying to return the column back to NOT NULL SQL Server immediately checks the constraint, which fails because the rows for that column were set to NULL.

For an existing column, you have to update the table to remove any NULL values before you can set it as NOT NULL.


Is this a good case for multiple SQL Server instances on a VM?

I'd say it's all based on the resource consumption (cpu / ram / concurrent disk activity) of each instance, as well as the overall health of the virtualization environment. Virtualization is all about resource queues. If it looks like physical environment might hit a bottleneck based on the cumulative resource consumption of three instances in one VM, it's better to break that into three VMs. Smaller VMs are more agile and have less of an overall impact to the physical machine, which lends to better performance. Larger VMs give you less individual OSs to manage, but tend to be larger consumers of resources and therefore harder to maneuver in the virtual environment.

If the overall load looks reasonable, you might be ok with the three instances on a VM. If the overall load is high and growing, split them apart into three VMs to be safe now and for the future.


ORA-39097: Data Pump job encountered unexpected error -942

Known issue:

https://community.oracle.com/thread/4125750

ORA-39006, ORA-39065 and ORA-00942: table or view does not exist When Starting DataPump in 12.2 (Doc ID 2341607.1)

Rebuild the missing part by running:

catalog.sql
catproc.sql
utlrp.sql


That is how it tends to work in small companies, but that is how it always used to work for small companies: at a small scale an expert database person was often seen as a grandiose expense when all the devs can do the basics. This is not really a factor of ORMs and noSQL becoming popular recently.

ORMs still have datastores behind them that someone needs to understand and be able to monitor, backup, repair, analyse, scale & optimise and as companies and/or projects grow it still pays to have a specialist in that area. Trusting devs with no data handling specialism is often fine on a small scale, but can lead to significant problems when scaling up. It is quite common to see people gush about an ORM or noSQL solution and claim it means they don't need to worry about the database, only to complain bitterly later when something they tested on a few thousand records falls to the floor when asked to deal with tens of thousands or millions or more (or sometimes even "a few thousand plus one" for really bad designs) and they have quickly got to the point where throwing more hardware at the problem is no use. You'll often find massively complicated caching layers getting created which would not be required if a little more thought had gone into either the initial design of the data store (or a later refactor) - those layers need development and maintenance too so those projects are often not saving money long term by not having a "data guy". In fact if you can keep a foot in both camps (dev and data) then there is much interesting work (and good money) in certain "architect" roles: people either designing the model for projects or earning their bread from taking overly complicated & inefficient monstrosities and helping the dev teams turn them into relatively sleek & scalable creations.

Also don't constrain yourself to being a DBA for a dev team: all of our clients (banking types, councils, a small airline or two) have an infrastructure department in part populated by DBAs who are responsible for making sure their data is appropriately handled. They challenge us (and other suppliers) to ensure we are following good practise, they are responsible for wider issues such as integration of data from systems from desperate suppliers, and they are of course responsible for maintaining in-house databases (mirrors, backups, monitoring, ...) - so there are other places a DBA optimised skill-set is required.

Of course you must stay up-to-date with the newer tools, that goes without saying, but while the job overall has evolved a bit over the last decade it hasn't changed nearly as much as some would have you believe (nor I expect will it in the near future, though keep your ear to the ground for new developments to stay current and avoid obsolescence if I'm wrong about that).


When you run the log backups from a secondary server, when the log backup is complete the secondary replica will tell the primary replica that the log backup has been completed and what the last LSN to be backed up was.  The primary replica will then mark the VLFs which were backed up as inactive.  That change (which is logged) will then be transferred to the secondary replicas.

In other words, no you don't need to take backups from the primary replica as well as the secondary.


Ola Hallengren has already done the work for you here, no need to roll your own code.  You can even cherry pick specific indexes or tables.

https://ola.hallengren.com/

I'd also recommend going ONLINE with those rebuilds if you're on Enterprise Edition, that'll save you locking of the tables for writes.


The basic concept is to use GRANT/DENY Schema Permissions. You can efficiently manage permissions by creating a role and then adding members to it.

Below is an example that will explain you in detail

use master
go
--Create Logins
CREATE LOGIN UserA WITH Password='UserA123';
go
CREATE LOGIN UserB WITH Password='UserB123';

use AdventureWorks2008R2
go
--Create Database Users
CREATE USER UserA;
go
CREATE USER UserB;
go
--Create the Test Schemas
CREATE SCHEMA SchemaA AUTHORIZATION UserA
go
CREATE SCHEMA SchemaB AUTHORIZATION UserB
go

-- create test tables
create table schemaA.TableA (fname char(5))
go
insert into schemaA.TableA (fname) values ('Kin-A')
go

create table SchemaB.TableB (fname char(5))
go
insert into SchemaB.TableB (fname) values ('Kin-B')
go

Now test :

--Test for UserA in SchemaA
EXEC('select * from schemaA.TableA') AS USER = 'UserA'
go
--Kin-A

-- Test for UserB in SchemaB == this should fail
EXEC('select * from SchemaB.TableB') AS USER = 'UserA'
go
--Msg 229, Level 14, State 5, Line 1
--The SELECT permission was denied on the object 'TableB', database 'AdventureWorks2008R2', schema 'SchemaB'.

Now create Stored Procedures :

CREATE PROCEDURE SchemaB.proc_SelectUserB
AS
    select * from schemaA.TableA;
go
create procedure schemaA.proc_SchemaA
as
    select * from schemaA.TableA

Now Grant execute permissions to UserA on schemaB's SP

GRANT EXECUTE ON OBJECT::[SchemaB].[proc_SelectUserB] TO [UserA]
go

Test it .. to see if UserA is able to run SP from schemaB. This will PASS

EXECUTE AS LOGIN='UserA';
    Exec SchemaB.proc_SelectUserB;
    revert;
go
--- Kin-A

But UserA wont be able to see data from SchemaB

EXECUTE AS LOGIN='UserA';
    select * from SchemaB.TableB
revert;
go

--- Msg 229, Level 14, State 5, Line 3
--- The SELECT permission was denied on the object 'TableB', database 'AdventureWorks2008R2', schema 'SchemaB'.

Alternatively you can use DATABASE ROLE and just add users to it for better manageability of permissions:

EXEC sp_addrole 'SchemaBUsesSchemaAProc'
go
EXEC sp_addrolemember 'SchemaBUsesSchemaAProc','UserA';
go

Below statement will make sure that UserA is able to see schemaA and NOT schemaB. The good thing is that you can just add users to SchemaBUsesSchemaAProc role and they will inherit all the permissions granted to that role.

GRANT SELECT ON SCHEMA::SchemaA TO SchemaBUsesSchemaAProc;
go

If you only want to allow UserA to execute SP's which are owned by SchemaB then below statement will do the job:

GRANT EXECUTE ON OBJECT::[SchemaB].[proc_SelectUserB] TO [SchemaBUsesSchemaAProc]
go

This way, UserA is not able to see SchemaB's tables, but still can execute procs from SchemaB.

Below will explain the permission hierarchy :


Possible reasons for really long database recovery

If this is a PRODUCTION environment, definitely DO NOT CANCEL recovery.  This will only prolong your pain.  DO NOT RESTART SQL SERVER.

Wait until recovery is finished, no matter how long it takes.
Ensure you have a good backup.
Reduce the Virtual Log File count on databases that have an unreasonably high number of them.
Set autogrowth on the database to a reasonable number, NOT 10MB or 10%!  If your log file is currently 50GB or larger, for instance, you might want to consider a growth size of 4GB or 8GB.
Use this script, or some variation thereof, to monitor VLF counts on your databases, and pro-actively manage them so you don't have this problem in future.

This answer has many great points about VLF performance.

Kimberly Tripp has an excellent article about how to get better transaction throughput.

MSDN has a blog post about slow database recovery from high VLF counts.  The article is targeted at SAP users, but the information applies equally well to all SQL Server installations.

Linchi Shea has some good points over at sqlblog.com

If this is some non-production environment, then all bets are off.  You can kill that sucker as much as you like to find out the cause/solution.


How to group time intervals by order of magnitude in Postgres?

A CASE expression can help:

SELECT CASE WHEN gap <= INTERVAL '10 minutes'
            THEN '0-10 minutes'
            WHEN gap <= INTERVAL '59 minutes'
            THEN '10-59 minutes'
            ...
            WHEN gap <= INTERVAL '10 days'
            THEN '2-10 days'
            ELSE 'more than 10 days'
       END AS gap_range
...
GROUP BY gap_range;


Execute privilege granted successfully, but I can't execute the procedure

It seems the user lacked CREATE VIEW privilege.
I can reproduce the error, but not the part about needing to grant it to yourself, which doesn't make sense. If you could grant privileges to yourself there would be no security at all
Connected to Oracle Database 19c Enterprise Edition Release 19.0.0.0.0
Connected as sys AS SYSDBA

SQL> create user ellie identified by ellie quota unlimited on users;

User created

SQL> grant create session, create table, create procedure to ellie;

Grant succeeded

SQL> conn ellie/ellie
Connected to Oracle Database 19c Enterprise Edition Release 19.0.0.0.0
Connected as ellie

SQL> create or replace procedure insert_into_purified as
  2  begin
  3      execute immediate 'create or replace view demo_view as select * from dual';
  4  end insert_into_purified;
  5  /

Procedure created

SQL> exec insert_into_purified
begin insert_into_purified; end;

ORA-01031: insufficient privileges
ORA-06512: at "ELLIE.INSERT_INTO_PURIFIED", line 3
ORA-06512: at line 1

SQL> conn / as sysdba
Connected to Oracle Database 19c Enterprise Edition Release 19.0.0.0.0
Connected as sys AS SYSDBA

SQL> grant create view to ellie;

Grant succeeded

SQL> conn ellie/ellie
Connected to Oracle Database 19c Enterprise Edition Release 19.0.0.0.0
Connected as ellie

SQL> exec insert_into_purified

PL/SQL procedure successfully completed

SQL> desc demo_view
Name  Type        Nullable Default Comments
----- ----------- -------- ------- --------
DUMMY VARCHAR2(1) Y


Sending one email when all jobs completes successfully

The way I've achieved this is to add two steps to each backup job.  The first writes to a results table; it doesn't matter where this lives so long as all jobs can reach it.  The second step runs sp_start_job, targetting a common, new job.

This new job has three steps.  One, check the results table.  If any backup job has not yet reported then stop.  If all backup jobs have reported success then continue.  Step 2 - reset the results table.  Step 3 - send the email.  Two and three can be reversed depending on your tolerence for failure and your recovery practices.

The results table can be managed by insert/truncate or update/update.  You can get inventive with run numbers and history logging, too, I've that's needed.

You have to think how you want to recover from failed jobs?  Purge the results and start all again?  Re-start failed jobs only?  Have a precursor step which launches your backups, which then must be idempotent etc. etc.


RMAN list archivelogs that are needed for to recover specified backup

Depending on your requirements and what other backups you have done since then, you could use (from http://docs.oracle.com/cd/B28359_01/backup.111/b28270/rcmreprt.htm#BRADV89594):

REPORT NEED BACKUP RECOVERY WINDOW OF n DAYS
Displays objects requiring backup to satisfy a recovery window-based retention policy.

REPORT NEED BACKUP REDUNDANCY n
Displays objects requiring backup to satisfy a redundancy-based retention policy.

REPORT NEED BACKUP DAYS n
Displays files that require more than n days' worth of archived redo log files for recovery.

REPORT NEED BACKUP INCREMENTAL n
Displays files that require application of more than n incremental backups for recovery.

These will tell you which files you need to backup to provide 'n' days worth of restore capability or 'n' redundancy.


The backup operation will run on the database server, and the backup pieces may be created on the database server, attached storage disk, a backup server through a 3rd party library (Networker, TSM, etc.), or any other server through network shares (NFS, samba), depending on your configuration.


Is it possible to synchronize two ms access db?

You can use the import data tools that are part of access to import data from one database into the other. It is under the "External Data" tab. This should work as long as you have network access to both databases.

Some more info : http://office.microsoft.com/en-us/access-help/import-or-link-to-data-in-another-access-database-HA001227658.aspx

Before doing this, have a little think about your table design - do you need to have a flag to indicate which store the data came from? If the data is normalized, you may have have issues importing data that may be resolved by the order that the data is imported in.

Also, remember to regularly backup these database files before trying to import. This way if you break something you can go back and start again. Also store them on physically different disks just in case....


Practical way to temporarily shut down a database

You can take if offline or open as a single user.

To take it offline adapt the followin script, original at sqlauthority

-- Create Test DB
CREATE DATABASE [myDB]
GO
-- Take the Database Offline
ALTER DATABASE [myDB] SET OFFLINE WITH
ROLLBACK IMMEDIATE
GO
-- Take the Database Online
ALTER DATABASE [myDB] SET ONLINE
GO
-- Clean up
DROP DATABASE [myDB]
GO

To make it available to a single user account original at sqlauthority:

ALTER DATABASE [YourDbName]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;


How export a sql server 2008 diagram to PDF filetype?

There is no direct way to transform the standard SQL Server diagram to a PDF or an image file type. Actually there is no simple way to export the diagram from within the tool.

You can, however, save it externally and send it to someone by:

print to a file printer (an .xps file) which is formatted like a pdf and an XPS file viewer exists on any windows machine;
open the diagram and right click somewhere in the middle and you'll have there the option to "Copy diagram to clipboard". From here you paste to MSPaint or another image creator and save to wanted format;

Not very straightforward, but can do the job.


Does a unique clustered index give array-like lookup performance?

Yes and no.

An "array" in a programming language does some trivial address arithmetic to quickly locate the Nth entry in the array.

In MySQL, most indexes are built as B+Trees.  (See Wikipedia)  This structure is more complex than an array, but still the best available.  WHERE id=2 requires drilling down a "tree" of nodes to locate the item with "2" in the "id" column.

A BTree has several advantages over a simple array.  And these advantages are necessary for the generality of database operations.

Database tables are designed to handle an arbitrarily large number of items.  Arrays are limited to what can fit in RAM.  This effectively prevents the address arithmetic of arrays, and forces some other implementation.
You can DELETE * FROM t WHERE id=2.  (Arrays don't allow holes; BTrees do.)  Note that it prevents the use of "address arithmetic" to locate a record.
A BTree index can use a string for lookup -- WHERE name = 'Venus'.  And this works as easily and almost as fast as using numbers.
Since BTrees are "blocks"; they get scattered around and they could become empty.  This leads to the overhead of maintenance of the tree.  Don't worry about this; BTrees are still very fast, on average.
"Clustering" in this context implies that ids 1,2,3,... are "consecutive" and "adjacent" in some sense.  (Or "1,3,...", if id=2 has been deleted.)  In practice, there are about 100 consecutive values sitting in one B+Tree block.  This allows for "range" to be very efficient.  Example:  WHERE name BETWEEN 'Mars' AND 'Venus' would be store alphabetically.
When a 'range' query runs off the end of a B+Tree block, the next block is linked from it.  (This is the "+".)
Technically, array lookup is O(1) and a BTree lookup is O(logN).  But the log is not that big a number -- A BTree for a million rows is about 3 levels deep; for a trillion rows, only about 6 levels deep.  That is, looking up a row in a trillion-row table is only about twice as slow as in a million-row table.
MySQL (InnoDB specifically) requires a unique PRIMARY KEY and clusters it with the data.
Hence, a lookup by the PK (WHERE id=2, if id is the PK) is one drill-down of one BTree to get to the entire row.
A secondary index (not the PK) is implemented as a B+Tree with the key column(s) plus the column(s) of the PK.
Hence, lookup by a secondary key (SELECT * FROM t WHERE name='Venus' with INDEX(name)) is a little more complex.  First drill down the name index to find the id, then drill down the PK+data BTree to find the entire row.
Prevention of duplicates -- This happens when INSERTing or UPDATEing a row.  Effectively, it looks up the row via the PRIMARY KEY (if given -- cf AUTO_INCREMENT) and via every UNIQUE index.  If any of those are a match, you get an error (unless doing INSERT IGNORE).  Otherwise, the PK's BTree and the Unique BTrees are poised to take the new/modified row.  Dealing with potential dups is not free, but cheap.
For a BETWEEN, the first row is accessed (O(logN)), then each subsequent row is O(1).

So, yes, an array lookup takes nanoseconds, which is faster than a database lookup, which takes microseconds (if cached in RAM), or milliseconds (if I/O is needed).  That is the price you have to pay for unlimited size and many more features.


You need to look into proactive caching for your partitions and dimensions where you can set up notifications based on a tracking table.

From the documentation:

  Proactive caching provides automatic MOLAP cache creation and
  management for OLAP objects. The cubes immediately incorporate changes
  that are made to the data in the database, based upon notifications
  received from the database. The goal of proactive caching is to
  provide the performance of traditional MOLAP, while retaining the
  immediacy and ease of management offered by ROLAP.

The subject is a bit broad for an answer and there are quite a few options and drawbacks to consider.

In general you check the box "enable proactive caching" and pick some latency options:

and then you specify a tracking table on the "notification" tab:

What all the options such as enable ROLAP aggregations and the latency settings do is explained in more detail in this blog post or this one


Any known issues upgrading from MySQL 5.1.73 to 5.6.21?

ISSUE #1 : Upgrade Path

Your grant tables can get screwed up because you are leaping two versions instead of one.

I just helped someone a week ago because they did just that (MySQL service stops after trying to grant privileges to a user). The solution for that question was to manually fix the mysql.user. Rather than going through that heavy-handed route, there are two ways go about this.

TECHNIQUE #1 : Upgrade the Politically Correct Way

Upgrade From MySQL 5.1 to MySQL 5.5
Run mysql_upgrade --upgrade-system-tables using MySQL 5.5's binaries
Upgrade From MySQL 5.5 to MySQL 5.6
Run mysql_upgrade --upgrade-system-tables using MySQL 5.6's binaries
Generate the System Tables that use InnoDB (See my post InnoDB: Error: Table "mysql"."innodb_table_stats" not found after upgrade to mysql 5.6) since the MySQL 5.6 version of mysql_upgrade does not create them.

TECHNIQUE #2 : Upgrade Directly from MySQL 5.1 to MySQL 5.6

I already described how to upgrade two versions

Apr 11, 2013 : MySQL upgrade 5.0.88 to latest (Best Reference)
Feb 08, 2012 : will replication from 5.5.20 to 5.0.XX server work?
Jul 26, 2011 : Restoring an old backup to latest MySQL release

ISSUE #2 : FULLTEXT Indexes

If you use FULLTEXT indexes before MySQL 5.6, those tables use the MyISAM Storage Engine. You need to convert those tables to InnoDB (See my post When to switch from MyISAM to InnoDB? under NEED #2). Then, separately tune the InnoDB FULLTEXT options (See my post MySQL FullText search on string shorter than 3 chars returns no rows).

ISSUE #3 : Security Issues

I am sure you are aware of this since you are going straight to MySQL 5.6.21

For the rest who do not know, as of October 16, 2014, Oracle just announced that there are security issues with MySQL 5.5 and MySQL 5.6. This was also announced at Amazon. Both MySQL 5.5.40 and MySQL 5.6.21 will have the security patches in places, so it is best to upgrade to one of these.


How to securely connect app and database servers?

I'm not sure why you've been told that VPN technologies are not generally used. It is correct that they have a performance cost but then again, so does everything! For the amount of data typically moving between an application server and its database server, the CPU overhead of TLS should be negligible, especially on the database server. If that is not the case then look at purchasing SSL-offloading hardware.

My preference is two network cards in the application server. The connection between it and the database server goes over a different network than client traffic.

If a separate network is not possible then my preference is TLS combined with configuring the firewall on the database server to only allow incoming connections from a small number of computers - the application server (obviously), administrative workstations, operations management servers, etc.


Is there an Express version of Azure SQL DB?

Azure SQL DB does not have a direct counterpart for SQL Server Express.
Some functionalities that exist in SQL Express or other versions are simply not there, like filestream. (Also not supported on managed instances).

As a result, you need to find the minimal functionalities you need and see if Azure SQL DB is fit to be a replacement of your SQL Express.

To do this, you would have to look into the Azure SQL DB Purchasing Models.

There are some differences in functionalities depending on the azure tier, as @AaronBertrand mentioned:

  There are some functionality differences between tiers and service
  levels. As two off-the-cuff examples, Columnstore is only in S3 and
  above and In-Memory is only available in Premium and Business Critical
  - you can spot other differences in this doc

As some of these functionalities that are present in higher tiers are available in SQL Express Edition, you will have to check for these and find the minimal tier you could use.

After finding the minimum tier you could use, all that is left is the sizing of your database, the DTU's and other aspects like max concurrent connections.

This all depends on your pricing levels.

At the time of writing, and solely based on the DTU-based model, the pricing levels are: Basic tier, Standard tier, Premium tier and PremiumRS tier.

The vCore-based model also exists, but (again, at the time of writing) there is no low cost option like Basic or low DTU standard.

vCore-based model tiers

Comparison

Basic tier

Basic tier might be what you are looking for.
The downside is that it only goes up to 2GB.
It is not free like SQL Server Express edition, but it will be the lowest cost, granted that:

you are able to stay under 2GB
you don't need any of the other functionalities
a maximum of5 DTU's is enough.
...

Basic tier

Standard tier

If one or more of the above conditions are not met, then you will have to at least go for the standard tier.

It will be more expensive, the price depending on how many DTU's you are going to need (S0 until S12). You can go up to 250GB in this tier.

Standard tier

As a sidenote, there might also be functionalities that you have or use in SQL Server Express which are simply not available in Azure SQL DB, such as filestream. (Also not supported on managed instances). As a result, you need to find the minimal functionalities you need and see if Azure SQL DB is fit to be a replacement of your SQL Express.


How to efficiently copy millions of rows from one table to another in Postgresql?

Dump the table in csv format

COPY table TO '/tmp/table.csv' DELIMITER ',';

use the COPY command which is far more efficient for large amounts of data.

COPY table FROM '/tmp/table.csv' DELIMITER ',';

Check postgres docs at http://www.postgresql.org/docs/current/static/sql-copy.html for more info


Are there any database engines which will intuit the join condition based on the existing foreign keys?

I'm not aware of any that use foreign keys (and that would can be messy if you have more than one foreign key back to the same table)

But Oracle will base joins off of name of columns if you don't specify a specific join type nor a join condition, or if you use NATURAL JOIN


set session wait_timeout inside of phpmyadmin for export/import

File:

libraries/dbi/DBIMysqli.class.php

138     public function connect(

before return link you can run query:

223         $link->query("SET SESSION wait_timeout = 600");
224         return $link;

This would set any session to mysql db from phpmyadmin with wait_timeout = 600.


Can I change table structure in a transaction and then roll it back if there is an error?

Yes, this is possible.

Most DDL statements can be rolled back in SQL Server (There are a few exceptions such as CREATE DATABASE)


Why do we need listener for Oracle EBS application server?

The ICM starts up a Service Manager on each node that is enabled for concurrent processing, by instructing the node's Applications Listener (which is dedicated to Concurrent Processing) to spawn a process running the Service Manager executable (FNDSM).

The Applications Listener must be configured to source the Oracle E-Business Suite environment file before FNDSM is spawned.

Following startup, the Service Manager acts as an agent of the ICM to start and stop concurrent managers on that node, according to their defined work shifts.

For more info Click here to Oracle Docs


This is often done as a single Note table, and then seperate join tables to allow for multiple records.

So the tables you end up having are Customer, Location, CustomerNote (CustomerId, NoteId), LocationNote (LocationId, NoteId), Note.

I can't tell you the benefits or negatives of it but that's how it's done.


vba Excel data Validation error 'Application-defined or object-defined error'

I found a way around the issues i had, related to Excel Data Validation setup from Access via VBA.
Here:
https://msdn.microsoft.com/en-us/library/office/ff840715.aspx

For some weird reason, VBA would not set data validation based on property names.
E.g. if I use Type:=xlValidateList - nothing changes in the spreadsheet.
While the below does the job:

... .Validation.Add Type:=3, Operator:=2, AlertStyle:=2, Formula1:="YES,OK"


Cross-product between table column and input values

In other RDBMS (like SQL Server before 2008 - as per Paul's comment) one might cross join to a subquery with UNION ALL SELECT, but there are more convenient and efficient options in Postgres.

And you don't need a CTE for this. You can use it, but it has no performance benefit.

Provide a set with VALUES:

  VALUES computes a row value or set of row values specified by value
  expressions. It is most commonly used to generate a "constant table"
  within a larger command, but it can be used on its own.

SELECT t.i, m.col1
FROM   mTable m
CROSS  JOIN (VALUES (1), (2)) t(i);

Provide an array and unnest()

2a. with an array constructor:

SELECT i, m.col1
FROM   mTable m
CROSS  JOIN unnest (ARRAY[1,2]) i;

2b. With an array literal:

SELECT i, m.col1
FROM   mTable m
CROSS  JOIN unnest ('{1,2}'::int[]) i;

Add ORDER BY i, m.col1 if you need the sort order in your result.

About row and array syntax:

Array of strings when updating a field


Lock pages in memory and Virtual SQL Server

From your description it seems like things are fine for you even when LPIM privilege is their for SQL Server service account. If things are fine why do you want to change it I suggest you keep it like this.

Now LPIM protects paging of SQL Server process, so unless you have mis-configured VM host or some other applications running on OS which can leak or "Pull" memory from OS LPIM is actually not required. I as well believe that normally on VMware SQL Server should not be given LPIM unless required and forced by Ballooning or other application pulling memory unnecessarily. VMware ballooning is feature which should not be disabled but the VM host should be configured such that memory is balanced most of the time.

Quoting From Vmware Online Doc

  When setting the SQL Server Lock Pages in Memory user right, the
  virtual machine’s memory reservation should also be set to match the
  amount of the provision memory. Setting virtual machine memory
  reservations prevent the balloon driver from inflating into the SQL
  Server virtual machine’s memory space. Lock Pages in Memory should
  also be used in conjunction with the Max Server Memory setting to
  avoid SQL Server taking over all memory on the virtual machine.

Moral: If you are not seeing any anomaly after enabling LPIM I suggest you leave it as it is but before doing this speak to your VMware admin and make sure the VMware configuration is correct


SQL Server DML auditing - Triggers vs SQL Server Audit Feature (vs temporal tables vs change data capture)

CDC and temporal doesn't say who did the modification.
Audit has lower overhead than triggers. Audit uses extended events under the covers.
Audit might not give you which rows where read or modified (when parameterized sql is submitted), but that wasn't a prerequisite. I. e, I'd look at Server Audit first.


You should be safely calculate using wc -l filename.sql
On windows you may use following in command prompt: findstr /R /N "^" filename.sql | find /C ":"

As such not a part of question but out of intereset, you can also list content of the 32G dump using mysqldumpsplitter:
mysqldumpsplitter.sh --source filename --desc [More how-tos]


As per Robomongo Tool error shows like that

  A primary with different host name [:27017] found in server side.
  Please double check if same host name and ports are used as in
  server's replica set configuration. If same set name is used for
  different replica sets, this configuration is supported only on
  different instances of Robomongo. Please open a new Robomongo instance
  for each replica set which has the same set name.

As per github blog documentation here it seems like the problem is Robomongo cannot learn the set name which is a critical information for connections to replica sets.

For Example Workaround

Find a way to get your replica set name, something like rs-..., it
shows it in the URL
Close Robomongo
Open this file in your editor

~/.config/robomongo/1.0/robomongo.json on macOS
C:\Users[your name].config\robomongo.json on Windows
~/.3T/robo-3t/1.2.1/robo3t.json on Linux

Search for the empty setName and add the replica set name there and
save the file
Start Robomongo again and it should work

For further your ref here


ORACLE restore - how to restore specific .bkp file?

TL;DR: Just supply the tag of the backup you want to restore the database from, for example restore database from tag 'INTERESTING_TAG';

DISCLAIMER

The solution provided here is based solely on my own experience, you use it on your own risk. I'm not liable for any damages (including data loss) caused by using this solution.

  Also, do I always need to restore spfile and control file because I
  did not create some additional backups (just ran command backup
  database)?

By default RMAN is configured to automatically back up control file and spfile after every successful backup and on every database structural change (for example, adding datafiles) which causes these changes to be reflected in control files. Thus after every successful database backup with backup database, the spfile and control file will be automatically backed up.

You can determine if autobackup is enabled by issuing show controlfile autobackup; in RMAN, and enable it saying configure controlfile autobackup on; in RMAN.

Since you supplied the paths to the backup sets you want to restore your database from, I assume that you obtained them from your current control file using RMAN. It won't hurt to save this information and other information about your existing backups to a plain text file because you will restore one of the previous control files and chances are this information will be lost:

[oracle@oca ~]$ export NLS_DATE_FORMAT='DD-MON-YY HH24:MI:SS'
[oracle@oca ~]$ rman target=/ log 'list_backup.txt'

RMAN> list backup;
RMAN> exit;

You'll need to restore your database AND control file(s), because the database datafile headers of every datafile should be in sync with control file(s), i. e. they should have the same System Change Number (SCN).

As I already said, you just need to supply the tag name to restore the database from a specific backup. You can determine which tags were assigned to the backups by you or by the system, the completion date and time of the backups, and other information about backups saying list backup in RMAN (we already saved this info to the text file). Here's the output of list backup in my sample installation:

RMAN> list backup;

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ------------------
78      Full    300.26M    DISK        00:01:40     10-APR-13 05:10:32
        BP Key: 78   Status: AVAILABLE  Compressed: YES  Tag: TAG20130410T050852
        Piece Name: /u01/app/oracle/fast_recovery_area/OCAEXAM/backupset/2013_04_10/o1_mf_nnndf_TAG20130410T050852_8pbc14yv_.bkp
  List of Datafiles in backup set 78
  File LV Type Ckp SCN    Ckp Time           Name
  ---- -- ---- ---------- ------------------ ----
  1       Full 3985848    10-APR-13 04:52:40 /u01/app/oracle/oradata/ocaexam/system01.dbf
  2       Full 3985848    10-APR-13 04:52:40 /u01/app/oracle/oradata/ocaexam/sysaux01.dbf
  3       Full 3985848    10-APR-13 04:52:40 /u01/app/oracle/oradata/ocaexam/undotbs01.dbf
  4       Full 3985848    10-APR-13 04:52:40 /u01/app/oracle/oradata/ocaexam/users01.dbf
  5       Full 3985848    10-APR-13 04:52:40 /u01/app/oracle/oradata/ocaexam/example01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ------------------
79      Full    9.39M      DISK        00:00:02     10-APR-13 05:10:39
        BP Key: 79   Status: AVAILABLE  Compressed: NO  Tag: TAG20130410T051037
        Piece Name: /u01/app/oracle/fast_recovery_area/OCAEXAM/autobackup/2013_04_10/812350360_8pbk.bkp
  SPFILE Included: Modification time: 10-APR-13 04:55:39
  SPFILE db_unique_name: OCAEXAM
  Control File Included: Ckp SCN: 3985848      Ckp time: 10-APR-13 04:52:40

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ------------------
80      Full    300.30M    DISK        00:01:39     10-APR-13 05:18:03
        BP Key: 80   Status: AVAILABLE  Compressed: YES  Tag: DELETE_ME
        Piece Name: /u01/app/oracle/fast_recovery_area/OCAEXAM/backupset/2013_04_10/o1_mf_nnndf_DELETE_ME_8pbch94j_.bkp
  List of Datafiles in backup set 80
  File LV Type Ckp SCN    Ckp Time           Name
  ---- -- ---- ---------- ------------------ ----
  1       Full 3986589    10-APR-13 05:12:33 /u01/app/oracle/oradata/ocaexam/system01.dbf
  2       Full 3986589    10-APR-13 05:12:33 /u01/app/oracle/oradata/ocaexam/sysaux01.dbf
  3       Full 3986589    10-APR-13 05:12:33 /u01/app/oracle/oradata/ocaexam/undotbs01.dbf
  4       Full 3986589    10-APR-13 05:12:33 /u01/app/oracle/oradata/ocaexam/users01.dbf
  5       Full 3986589    10-APR-13 05:12:33 /u01/app/oracle/oradata/ocaexam/example01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ------------------
81      Full    9.39M      DISK        00:00:01     10-APR-13 05:18:11
        BP Key: 81   Status: AVAILABLE  Compressed: NO  Tag: TAG20130410T051810
        Piece Name: /u01/app/oracle/fast_recovery_area/OCAEXAM/autobackup/2013_04_10/812351553_8pbm.bkp
  SPFILE Included: Modification time: 10-APR-13 05:13:53
  SPFILE db_unique_name: OCAEXAM
  Control File Included: Ckp SCN: 3986589      Ckp time: 10-APR-13 05:12:33

From this output, you can see that the first backup was (automatically) assigned the tag TAG20130410T050852, and that the control file and spfile autobackup follows immediately (check the Completion Time field). You can also see that I performed another database backup, and I manually assigned it the tag DELETE_ME, and, of course, it's immediately followed by autobackup too. Notice also that the files in every backup have the same SCN, and that SCNs match the SCNs of the control files in the adjacent autobackups.

We will restore the database from the backup tagged TAG20130410T050852 which is older than the other backup tagged DELETE_ME, and we will restore the control file from the autobackup first.

In order to restore the control file from backup with RMAN, your instance should be in NOMOUNT state (only spfile is accessed in this state by the instance–control file(s) and datafiles are not accessed):

RMAN> shutdown immediate;

RMAN> startup nomount;

connected to target database (not started)
Oracle instance started
database mounted

(Though, you may use abort clause instead of immediate, and Oracle won't bother shutting down the database orderly–you'll restore it from the previous backup anyway.)

Restore the controlfile from autobackup:

RMAN> restore controlfile from
2> '/u01/app/oracle/fast_recovery_area/OCAEXAM/autobackup/2013_04_10/812350360_8pbk.bkp';

Starting restore at 10-APR-13 06:01:25
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output file name=/u01/app/oracle/oradata/ocaexam/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/ocaexam/control02.ctl
Finished restore at 10-APR-13 06:01:27

In order for RMAN to be able to read backup records in control file (just like it did when we were issuing list backup), we need to put the database in MOUNT state:

RMAN> sql 'alter database mount';

We're now ready to restore the database:

RMAN> restore database from tag 'TAG20130410T050852';

The final step is to open the database:

RMAN> sql 'alter database open resetlogs';

We specified resetlogs clause here because the existing redo log files are no longer usable since they were in use by the previous database, and thus should be reset so that they can be used by the restored database.

Now query for great good!


MongoDB SSL peer certificate validation failed: unable to get issuer certificate

By removing CAFile: /etc/ssl/ca.pem option from mongoDB config file, problem gone away.


Will restarting SQL Server clear my transaction log?

I believe his transaction log is simply full and restarting may be counting as a checkpoint? Is this a valid theory?

No, this is not right. That is the most brutal thing you can do to sql server to just clear out the log (which makes things worse). If there is a long running transaction, then restarting your sql server will put that database in recovery until the rollback happens.

since you are running in simple recovery mode, ONLY a CHECKPOINT - manually or sql server running it at regular intervals will allow SQL Server to reuse the transaction log (the log will be marked for truncation).

Note that TRUNCATE_ONLY is not supported in sql server 2008 and up, but that was used to do a FORCE log truncation (sql server 2005 and earlier versions) - BACKUP LOG <database_name> WITH TRUNCATE_ONLY

Please read  - Why Does the Transaction Log Keep Growing or Run Out of Space? (putting it in bold to emphasis to read it until you fully understand and digest the 2 best answers).

Also, make sure you have a good auto growth setting for your database.

  Do we know of any other reasons why restarting the MS SQL service would make a problem disappear for a few days?

You should start with How to analyse SQL Server performance


Slow enumeration of schemas and tables in a largish database

This can also be a shortcoming of pgAdmin. There have been a number of improvements for recent versions. Which one do you use? 1.14.2 is the current version (as of 2012-05-21).

Are queries from the command line interface psql slow, too?

Apart from that, you need to keep vacuuming all your tables in the database, not just pg_tables. I would strongly advice to enable the auto vacuuming daemon.

Also, pg_tables is not relevant at all. It is a system view to implement the SQL standard information schema. The relevant system tables would be pg_namespace, pg_class, pg_attribute and others. But it is pointless to target just one of them. For your kind of setup you need to be vacuuming the whole catalog. And it is very advisable to vacuum all of your database on a regular basis anyway. So, let me repeat: use autovacuum.

For starters, you can run in a Debian shell:

vacuumdb -afz
reindexdb -a
reindexdb -s

The last line targets system tables for possible bloat of indexes in the catalog in particular. See man reindexdb or the fine manual.

Finally, another option would be to upgrade to PostgreSQL 9.1. There have been a number of improvements for VACUUM and ANALYZE (among many others). In particular VACUUM FULL has been rewritten for 9.0 and does a much better job now.


Database design for multiple user attributes

You could look at using Entity-Attribute-Value design.

This consists of a table with three columns, one for the user id, one for the type of attribute, and one for the attribute value.

With this design, you can have an unlimited number of attributes without needing to implement new columns for new attributes.

Many architects will say this design cannot scale well, and they'd mostly be right; however if implemented correctly without going overboard on the number of attributes, and with proper indexing and efficient column types, it can work well.

For instance:

+---------+------------+
| USER_ID |  USER_NAME |
+---------+------------+
| 1       | You        |
| 2       | Me         |
| 3       | Them       |
+---------+------------+

+--------------+-----------------+
| ATTRIBUTE_ID |  ATTRIBUTE_NAME |
+--------------+-----------------+
| 1            | Eye Color       |
| 2            | Hair Color      |
| 3            | Number of Legs  |
+--------------+-----------------+

+---------+--------------+--------+
| USER_ID | ATTRIBUTE_ID |  VALUE |
+---------+--------------+--------+
| 1       | 1            | Brown  |
| 1       | 2            | Blue   |
| 1       | 3            | 2      |
| 2       | 1            | Blonde |
| 2       | 2            | Green  |
| 2       | 3            | 27     |
| 3       | 1            | Black  |
| 3       | 2            | Black  |
| 3       | 3            | 42     |
+---------+--------------+--------+


sys.dm_hadr_database_replica_states, understanding the different *_time columns

For a starter, here's the Microsoft Docs Page

As an aside, I suggest you do some reading into the mechanics of an availability group, purely so you can understand my answer. (Microsoft Docs Page)

As for the answer to your question, as normally in the case with questions around an RDBMS, it depends.

In this case is depends on what you mean by "...for data to appear"

Let's have a look at the columns in your question:

last_sent_time,
last_received_time,
last_hardened_time,
last_redone_time,
last_commit_time

It is also worth noting that the System DMV you are referring to is at the database level

last_sent_time

This time indicates the last time that the PRIMARY sent a Log Block to the available secondaries. This is the start of the data synchronisation process.

last_received_time

This indicates the last time that the secondary received a log block.

last_hardened_time

This indicates the last time that the secondary cached the recieved log block data to disk.

last_redone_time

This is the time that the last LSN was redone on the target database.

last_commit_time

This is the time of the last commit record was redone and reported back to the primary.

Summary

Of the above, there are various entry-points of the data into the secondary systems.

The data first enters the server into memory at last_received_time

The data first enters the server on disk at last_hardened_time

The data first enters the database data files at last_redone_time

The data first becomes committed and available for reading by queries (outside of strange NOLOCK situations) at last_commit_time

I suspect that the answer to your question is the latter of the 4 concepts. There is a small overhead to the time in this column however, due to the transmission time of the data between the SECONDARY and PRIMARY. This is likely to be unimportant in calculations for determining the speed of data throughput though.


Same script produces 'Specified key was too long' on one server and not on another

There are 2 possible scenarios that can cause this, it's hard to tell which one you are having if we don't see the SQL file you are trying to import.

Maybe your default engine isn't the same on both servers.
If you are loading data in a MyISAM table on your localhost and a InnoDB table on Real CMS this could cause the issue because the maximum prefix is 767 bytes long for InnoDB and 1000 bytes for MyISAM. So if you have a key with a prefix of, lets say, 800 bytes it would load fine on your localhost but not in Real CMS.

It could also be caused by a different server configuration. The max key length for a MyISAM table can be controlled by the innodb_large_prefix system variable.

Please see the documentation for CREATE INDEX which states:

  Prefix support and lengths of prefixes (where supported) are storage
  engine dependent. For example, a prefix can be up to 767 bytes long
  for InnoDB tables or 3072 bytes if the innodb_large_prefix option is
  enabled. For MyISAM tables, the prefix limit is 1000 bytes. The NDB
  storage engine does not support prefixes


Reclaim Transaction Log space of SQL Server

Yes, take log backups more frequently. Is there a reason they are so infrequent? It seems like you have a lot of potential for losing data (not just in complete disaster). The log file will continue to grow until a backup is taken, because it will not re-use the space.


PostgreSQL: a Login Role to only access Views

Unfortunately that's not that easy because there is no "select any view" privilege (as e.g. Oracle offers).

If you can put all views into a single schema, then this could be achieved by granting the select privilege on that schema. Something like this:

First you need to create a role that can be used for this

create role view_reader;

The role only needs to be created once.

Then create a schema that contains all the views:

create schema view_holder;

Then allow view_reader to read everything in that schema:

grant select on all tables in schema report to view_reader;
alter default privileges in schema view_holder grant select on tables to view_reader;

The all tables is misleading because it also includes views - but also tables. That's the reason why you need to the views into a different schema than the tables. Because of the alter default privileges any new view created in that schema will automatically be accessible to the view_reader role.

You can now create a user that can select anything:

create user client password 'foobar';
alter user client set search_path = 'view_holder';
grant view_reader to client;

If client logs in, the default schema will be view_holder and thus a select * from some_view will default to the views in that schema. But as the user has only privileges on the views in that schema he cannot select from the underlying tables.

You can skip the intermediate role (view_reader) if you want and assign the privileges directly to client. But I think it's better better to give each client a separate user.

This should also work if the views in that schema simple select from the "real" views if you can't move the views into a different schema.

Note that this will increase the maintenance complexity for the views because unfortunately Postgres won't let you change the list of columns of a view if other views depend on it.


Insert into table from a query to a linked database hangs

Based on your question and other discussion comments, I didn't see any clue for network and blocking issues.

Here, I'd like to give following approaches to try which can help in your situation:

Approach#1:

Suspecting performance issue because of indexes in STG_TRANSIT table. In the sutuation of big STG_TRANSIT table
and there are indexes available, what if you might drop them and rebuild them after populating tables during off hours.

Approach#2:

Create temporary table -

create table temp_STG_TRANSIT
as
select * from
(
  SELECT TRIM(r.R_ID), 1, TRIM(r.R_CODE), TRIM(r.R_MISC4), trim(rc.DESCR)
  FROM sradmin.RACEWAY@sr25 r
  LEFT JOIN sradmin.RWAYCODE@sr25 rc ON r.R_CODE = rc.R_CODE
)
Where Rownum <= 10000;

Perform bulk insert -

insert /* APPEND */ into STG_TRANSIT select a.* from temp_STG_TRANSIT as a;

Approach#3:

Trying with DRIVING_SITE hint which forces query execution to be done at a different site than the initiating instance, following query pattern can help in your context:

INSERT INTO STG_TRANSIT(TRANSIT_ID, SHIP, TRANSIT_TYPE, COMPARTMENT_ID, DESCRIPTION)
SELECT /*+ DRIVING_SITE(X) */ *
FROM
(
    SELECT TRIM(r.R_ID), 1, TRIM(r.R_CODE), TRIM(r.R_MISC4), trim(rc.DESCR)
    FROM sradmin.RACEWAY@sr25 r
    LEFT JOIN sradmin.RWAYCODE@sr25 rc ON r.R_CODE = rc.R_CODE
) X

Refer link for the details of DRIVING_SITE hint.


Composite multicolumn index for geopoint range and numeric range query

@jjanes' answer on stackoverflow that I accepted

With the help of the btree_gist extension, you can include the
event_type and start_date columns into the GiST index along with the
event_position.  However, the event_type won't be very useful in the
index as long as the restriction clause is something like event_type in (0, 1, 2).  (But if the list only contains one element, it will be
rewritten into an equality, and in that case it can use that column in
the index efficiently.)  So using the other two columns would be my
starting point.  I would put the usually more selective one first.  If
you are going to leave past events in the table rather than clearing
them out, then the date is probably going to eventually be the more
selective one.
If the location condition is not very selective at all (e.g. most of
your events are in NYC, and most of your users are in NYC, and almost
everything is within 100km of almost everything else), then you might
want a different approach.  Just make a default BTREE index on
(event_type, start_date).  Unlike GiST, such a BTREE can effectively
use a condition like event_type in (0, 1, 2) along with AND start_date between x and y.
I don't think that there is a fundamental reason GiST can't use the
in-list effectively while BTREE can.  Maybe that will be fixed for
GiST in some future version.


Can PostgreSQL slave server act as master for a new database?

You have a few options. You could use DBLINK for your queries to run them on slave and get the data over to DB2 on master.  You could also do that with FDW's set up in DB2 on master.

The other option which sounds more like what you want in your title would require you running 2 separate Postgres clusters on the machines.  So you'd have two separate installs, 2 different data directories, running on different ports. DB2 would live on a different cluster than DB1.  You could set up your streaming replication differently for each of those databases at that point (where as if they're on the same cluster, you can't pick and choose).

Hope that helps.


Speed up large clustered index creation?

You'll need to look at the wait type when the query is running.  Odds are you need faster disks as building an index on a table that large is going to cause MASSIVE amounts of reads and writes.

In a nutshell you'll need to read the 120 Gig table, sorting it based on the clustering key (which is going to cause a ton of spill to tempdb writing probably 100 Gigs to tempdb), then write the clustered index to the database which causes 120 Gigs of writes.

On top of this any non-clustered indexes on the table will need to be rebuilt at the same time so those all need to be read and rewritten as well (along with all the memory spill that goes with rebuilding the non-clustered indexes).

You can speed things up by disabling the non-clusterd indexes, then manually building them after the clustered index has been rebuilt.  You'll want to make sure that end users aren't able to touch the system while the non-clustered indexes are being built as their query performances will be awful until the non-clustered indexes are created.

Assuming Enterprise Edition you can create the commonly used indexes then let the users back in, then create the other indexes online so that users an work.

No matter what you'll be looking at a REALLY long maintenance window where you'll be slamming the disks as hard as they can go.


Result on execution of any SQL is number 2

You need to terminate the SQL statement with a semicolon (;) or a put a slash (/) in the new line instead of hitting Enter. For example: select * from dual;. Otherwise SQLPlus will believe you have not finished your SQL statement, and it starts counting the lines. 2 is actually a line number, it is the 2nd line of your SQL statement. If you type nothing here and just hit Enter, SQL*Plus will terminate your statement and return the prompt, it can not handle empty lines.


Postgresql rds: relation .... does not exists

jjanes answered the question in his comment.
Why use the convolution relname::regclass when the relid is directly available in the table?
using the relid  fixed the issue


Why does MongoDB 4.0 require journaling for replica set members using WiredTiger?

Seems like the point of checkpoints was to move fast, but at the risk of losing a minute of data at a time. Whereas the journal is all about accounting for everything. So why have both? Seems like having the Journal kind of overrides any gains that Checkpoints were supposed to give.

Although journal and checkpoint processes both write data to disk, they serve different purposes. Journal writes are fast append-only writes to smaller journal files (up to 100MB), whereas a checkpoint persists changes to the data files (which has more overhead in terms of files and complexity of reconciling updates).

The journal requirement for MongoDB 4.0+ replica set members using WiredTiger is motivated by improvements to replication performance and rollback behaviour:

In MongoDB 3.2+, a w:majority write concern implies j:true (acknowledged writes are confirmed by a majority of data-bearing members and will not be rolled back) if j is not specified. If the journal is not enabled, the alternative is to either acknowledge in-memory writes for j:true (which could potentially lead to rollback) or trigger a checkpoint (which will force syncing changes to data files on every write). Requiring journaling is a stronger guarantee that acknowledged majority writes will not be rolled back, which is important for applications or server features like Change Streams that return data based on majority read concerns.
A WiredTiger performance improvement in MongoDB 4.0+ reduces the write amplification of some data changes that were previously journaled multiple times (data written to the oplog and to the target collection) and allows faster rollback without limitations on data size (prior to MongoDB 4.0 the rollback limit was 300 MB of data before manual intervention was required). However, the new Recovery to a Timestamp (RTT) rollback algorithm relies on the journal to recover the oplog to a consistent point. For more technical details see the Startup Recovery and Rollback descriptions in the MongoDB source repo on GitHub.

If you do not have a replica set configuration it is still possible (but not recommended) to run with the journal disabled in MongoDB 4.0+.

If you attempt to start a MongoDB 4.0+ replica set member using WiredTiger with the journal disabled, mongod will fail to complete the startup sequence and shutdown with a log message like:

Running wiredTiger without journaling in a replica set is not supported. Make sure
you are not using --nojournal and that storage.journal.enabled is not set to 'false'.


How can I decrypt data on a replicated MSSQL database?

I have imported the certificate and the key and I have created a symmetric key however, whenever I try to decrypt the credit cards, it just returns NULL.

If the symmetric key you are talking about is one you've manually created in the user database then you'll need to create it with the same initialization vector as the previous one by specifying a Key_Source and the same algorithm. If you haven't done this (or restored the database containing the same key) then that's your issue.

Here is a walk through to show you how to create the same symmetric keys in multiple databases.


Size of the database and table while inserting and deleting in sql server 2005

WHen you use delete the rows actually not deleted but marked as GHOST records, which will finally deleted by dedicated process named GHOST CLEANUP which works relatively slow in background

To force deletion of ghost records run:

DBCC FORCEGHOSTCLEANUP


Storing last N samples in a table coming from multiple clients

My idea is to use new table to store next index to update for each user:

CREATE TABLE next_sample_index (
  id     uuid     NOT NULL,
  index  integer  NOT NULL,

  PRIMARY KEY(id)
);

Just fill it with 0s for each user. I would use it as a kind of serial like this:

UPDATE
    next_sample_index
SET
    index = index + 1
WHERE
    id = XX
RETURNING
    index;

You will get next index and increase it at the same time.

Then you calculate modulus index % 50 so it acts as a round-robin queue.

To insert new sample:

DELETE FROM
    samples
WHERE
    id = XX AND
    index = --index-just-calculated--;

This DELETE command will do nothing if no such an index existed before.

INSERT INTO
    samples(
        id,
        index,
        text
    ) VALUES (
        XX,
        --index-just-calculated--,
        'f85c6f06-ae0b-48de-8c36-bfc86208495c'
    );

You just need to make sure that next_sample_index table is properly initialized or make a function to get next index that checks IF NOT FOUND condition (and makes INSERT if necessary).

XX in the examples is user id.

Hope this helps.


Understanding the use of bitwise operators in MySQL?

As an aggregate function the BIT_OR is evaluated as a bitwise OR of all the values in the group. If it equates to 0, none of the value have been set.


Postgres - how to tell when the last VACUUM FULL was run?

PostgreSQL doesn't track that information.


First rule of  *nix admin is: Logs are your friends

Looking at logfile:

2020-05-15 14:41:00 1108 [ERROR] Event Scheduler: [phpmyadmin@localhost].[integrity.DELETE_EXPIRED_SESSIONS] execution failed, user no longer has EVENT privilege.
2020-05-15 14:41:00 1108 [ERROR] Event Scheduler: [phpmyadmin@localhost][integrity.DELETE_EXPIRED_SESSIONS] Access denied for user 'phpmyadmin@localhost' to database 'integrity'
2020-05-15 14:41:00 1108 [Note] Event Scheduler: [phpmyadmin@localhost].[integrity.DELETE_EXPIRED_SESSIONS] event execution failed.

The user defined at Definer: phpmyadmin@localhost MUST be an existing user and with all privileges to access your database...

Fixed that, everything worked fine.


If you run the statement without the where clause you'll see why:

lang | id    | lang | id
-----+-------+------+------
en   | hello | en   | hello
en   | hello | fr   | hello
fr   | hello | en   | hello
fr   | hello | fr   | hello
en   | world | en   | world
en   | world | de   | world
de   | world | en   | world
de   | world | de   | world

The join on the "id" column works like this:

Take the first hello from the table and look for all rows that contain hello - that yield two rows for the first hello. The same happens with the second hello, so you wind up with 2x2 rows for the join on hello. And the same for world

The outer join does not play any role, because there is a match for each id (actually: two matches).

You can never get your first (intended) result because that implies that all rows in the "a" table have lang='en' (which is of course not true).

To get the missing translations you need to first create the combination of all languages and ids:

select distinct a.id, b.lang
from trans a
  cross join trans b;

Now you need to find all rows that are not in that result:

select *
from (
  select distinct a.id, b.lang
  from trans a
    cross join trans b
) ac
where not exists (select 1
                  from trans mt
                  where mt.id = ac.id
                    and mt.lang = ac.lang);

You can achieve this with an outer join as well. I simply prefer the not exists because it documents more clearly the intention (and because I hardly ever work with MySQL which is known to perform poorly with sub-queries like that)

select ac.*
from (
  select distinct a.id, b.lang
  from trans a
    cross join trans b
) ac
  left join trans mt on mt.id = ac.id and mt.lang = ac.lang
where mt.id is null;

Here is an SQLFiddle: http://sqlfiddle.com/#!2/9804d/6

Edit

after testing the peformance with larger tables, it seems that Sean's version of the cross join is much more efficient than mine.

So this statement should be faster than the ones above:

select at.*
from (
    select lang_code, label_code
    from (
        SELECT distinct lang_code
        FROM translations
    ) as translang
      cross join (
         SELECT distinct label_code
         FROM translations
      ) as transid
) at
  left join translations mt
         on mt.lang_code = at.lang_code
        and mt.label_code = at.label_code
where mt.lang_code is null;

Edit 2

And another version to be tested (SQL-Fiddle):

SELECT a.lang, a.id,
       l.lang AS blang
FROM trans a
  CROSS JOIN
    ( SELECT DISTINCT lang
      FROM trans
    ) l
  LEFT JOIN trans b
    ON  b.id = a.id
    AND b.lang = l.lang
WHERE a.lang = 'en'
  AND b.id IS NULL ;


How do I go about easy table input without creating an application?

If I am not missing anything... Assuming you always specify the user name in such a way as to exclude possibility of multiple matches (if e.g. you decided to look the user up by a mask using LIKE rather than by a complete name), you could try inserting data into the record table using an INSERT...SELECT statement like below:

INSERT INTO testreport.records (userid)
SELECT userid
FROM testreport.r_user
WHERE username = 'specific user name';
/* or:
WHERE username LIKE 'some pattern'
- just make sure it matches the single row you meant to reference
*/


Need Help Designing Tennis Ladder Database

This looks good, except I would combine the Ladder_Users table with the UserLadderStats table. There's no reason to have them separate and it just introduces the chance for data integrity problems. Just move all the stats onto the Ladder_Users, and then you can drop UserLadderStats.

Also, note that the WinnerUserID and LoserUserID in LadderMatch are foreign keys, even though they aren't explicitly marked as such.


It depends.

Most products duplicate the data, and require ETL to "refresh" the data, running on a regular basis but implying there is some delay between live data and OLAP data. It also implies that you can keep historical data in the cube, even if the old OLTP data is removed.

Some products will not duplicate all data, and allow to control which data is duplicated or not. That would be somehow like indexes: Some redundant data that allows for better performance, but not all the data is duplicated.

For SQL Server, for example, check the difference between MOLAP, ROLAP and HOLAP models


Ola Hallengren's SQL Server Maintenance Solution & CMD Syntax

How to automate SQL Server Express database maintenance using Windows Task Scheduler and Ola Hallengren’s maintenance solution has numerous examples of using SQLCMD to run Ola's scripts.
For example:
Database integrity check
sqlcmd -E -S .\SQLEXPRESS -d Tools -Q "EXECUTE dbo.DatabaseIntegrityCheck @Databases = 'USER_DATABASES', @LogToTable = 'Y';" -b -o D:\OLA\Logs\DatabaseIntegrityCheckUserDatabases.txt

Index optimize
sqlcmd -E -S .\SQLEXPRESS -d Tools -Q "EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES', @LogToTable = 'Y';" -b -o D:\OLA\Logs\IndexOptimizeUserDatabases.txt


From the MySQL Manual:

  Some MySQL storage engines, including InnoDB, do not support
  per-partition optimization; in these cases, ALTER TABLE ... OPTIMIZE
  PARTITION rebuilds the entire table.

There is a reason to optimize separately each partition, in order to avoid locking and performance issues. However, if you are using Innodb, you will get the following warning:

Table does not support optimize on partitions. All partitions will be rebuilt and analyzed

Which basically makes the OPTIMIZE PARTITION syntax useless. Also, if you only have 2 partitions (p0 and p1), you are basically executing OPTIMIZE TABLE.

My questions is why you are running optimize to start with and if it is justified? You should also add the MySQL version and engine used. In any case, you are probably executing the first option implicitly or explicitly.


Is Cassandra's manual node addition/removal tasks tedious and practical in a large cluster?

There are mentions of clusters with size 300-400 & more machines.  But plenty of tools are provided with Cassandra that could help with automation of deployment process.  Plus you can find a plenty of recipes for Ansible (1, 2, ...), or some other automation frameworks.

More work could be required for other admin tasks, like, making sure that repairs are running, etc.  But people are doing it, and there are "3rd-party" tools for them as well.


Sql join with three tables and multiple rows

The data upload process should assign the accurate min and max values to BMP and EMP in the Sense table(they remain approximations). I would be working on the upload process to make it more accurate. However, to put this question to rest:

What I did to fix this was to find the min and max from the Details table and update the Sense table to match the actual range(how it should have been in the first place).
However, the problem I had was that when I use this:

  (b.BMP+b.EMP)/2>=c.BMP and (b.BMP+b.EMP)/2<=c.EMP

If the average is say 2.45 and the Sense table shows two ranges like this: 0 to 2.45 and 2.45 to 5 for the same ID then it would give me a duplicate.
So for now I changed the query to this and it works:

Select distinct *
from Runners a
left join RaceDetails b
on a.ID=b.ID
left join RaceSense c
on a.ID=c.ID
and (b.BMP+b.EMP)/2>=c.BMP
and (b.BMP+b.EMP)/2<c.EMP --removed = sign

If somebody comes up with a query that would work without updating the table in the long run. I would select that answer.


Wrap you expression in a CASE statement:

SELECT * FROM tbl
WHERE  CASE $1 WHEN '*' THEN TRUE
       ELSE some_tsvector_column @@ $1::tsquery END;

This way you can pass '*' to disable the filter or any other valid tsquery string to actually filter.


I would not apply formatting directly in the query, but rather at the application layer.

If you really want to, you could do it like this:

WITH CTE
AS
(
SELECT  p1.name,
        u1.email,
        ROW_NUMBER() OVER(PARTITION BY Email) as rownum,
        p1.user_id
FROM users as u1
INNER JOIN purchases as p1
ON u1.id=p1.user_id
WHERE u1.email IN (SELECT u.email
                   FROM users as u
                   INNER JOIN purchases p
                   ON u.id=p.user_id
                   WHERE p.name='Williams Selden')
)
SELECT
name,
CASE WHEN rownum = 1 THEN email
ELSE ''
END AS email
FROM CTE
ORDER BY user_id;

DB-Fiddle

But I would change the IN to EXISTS, due to how IN handles NULL values (and remove the extra inner join)

WITH CTE
AS
(
SELECT  p1.name,
        u1.email,
        ROW_NUMBER() OVER(PARTITION BY Email) as rownum,
        p1.user_id
FROM users as u1
INNER JOIN purchases as p1
ON u1.id=p1.user_id
WHERE EXISTS  (SELECT u1.email
               FROM  purchases p
               WHERE u1.id= p.user_id
               AND p.name='Williams Selden')
)
SELECT
name,
CASE WHEN rownum = 1 THEN email
ELSE ''
END AS email
FROM CTE
ORDER BY user_id;

DB-Fiddle

Result for both

| name                | email                      |
| ------------------- | -------------------------- |
| Pauletta Arends     | Isabel.Breeding@gmail.com  |
| Miyoko Potter       |                            |
| Wendie Westmoreland |                            |
| Williams Selden     |                            |
| Jettie Dilks        |                            |
| Catina Kiser        |                            |
| Bradly Vasko        |                            |
| Inocencia Cousineau | Ozella.Yoshimura@gmail.com |
| Cammy Bough         |                            |
| Williams Selden     |                            |


Exactly what error is returned by PostgreSQL `FOR UPDATE NOWAIT` when a selected row cannot be locked immediately?

SQLState: 55P03

In Postgres 9.4.x I tested this by performing an unresolved SELECT FOR UPDATE in pgAdmin and then doing a SELECT FOR UPDATE NOWAIT in my Java app. I used the JDBC driver JDBC41 Postgresql Driver, Version 9.4-1201.

The result was the following PostgreSQL Error Code.

Message:ERROR: could not obtain lock on row in relation "my_table_"
Class:Class 55 — Object Not In Prerequisite State
SQLState:55P03
Condition Name:lock_not_available


Find "second-greatest" string in each "group"

Something like this would split the results into multiple columns, but you would need to know in advance the maximum number of employees per purchase_id:

With Ordered_cte As (
    Select employee,
        purchase_id,
        RowNo = Row_Number() Over (Partition By purchase_id Order By employee)
      From tbl_purchase_employee)
Select purchase_id,
    Employee1 = Max(iif(RowNo = 1, employee, Null)),
    Employee2 = Max(iif(RowNo = 2, employee, Null)),
    Employee3 = Max(iif(RowNo = 3, employee, Null))
  From Ordered_cte
  Group By purchase_id;


Show full detail error message on the console

What you see is just the part of the line where PostgreSQL sees a problem, you cannot bet more.

But since you know that the problem is in line 1, you should be able to

SELECT json
FROM event_json
WHERE json::JSONB->>'room_id' LIKE '%matrix.org%'
LIMIT 1;

and search for the offending \u0000.


Can only execute mysqladmin as root account created with unix socket, all other accounts fail

I have finally figured this out.  I have SSL enabled, which I may have not mentioned in my original post.  When I added the --ssl-ca parameter to the connection string, I was able to connect.
mysqladmin -u orgdba --ssl-ca=/etc/mysql/ssl/ca-cert.pem -p status

now returns the status as expected.
Hopefully this helps someone out in the future.


What should I monitor on the SQL Server machine?

Some of the options you mention (file auto grow for example) are already logged in the SQL Server default trace http://msdn.microsoft.com/en-us/library/ms175513.aspx

Other options for your own monitoring can be done via windows perfmon to monitor all kind of windows or SQL Server variables. You could also save output from any of the SQL Server DMV to store various options over time (query plan statistics, wait types, memory, locks etc).

You could investigate using the new Management Data Warehouse feature to collate lots of information http://msdn.microsoft.com/en-us/library/bb677306.aspx.

Having said all of that, that is going to be a steep learning curve and it is difficult to give a definitive answer. As a beginner is buying a third party tool that will do all this monitoring for you an option? Some packages are: -

http://www.sqlsentry.com/products/performance-advisor/sql-server-performance
http://www.red-gate.com/products/dba/sql-monitor/


How to specify a task in a SQL Server maintenance plan conditionally?

I am not sure how dirty you want to get on this one but the only method is going to be opening up SQL Server Data Tools and building (or modifying) an SSIS package. One would be to import the maintenance plan into SQL Server 2012 Data Tools and modify the SSIS package. The other would be to create it from scratch. You have the same options as the maintenance plans when you create an SSIS package.

The one thing you want to add is an Expression Task, which is an awesome addition to SQL Server 2012. You could use the DATEPART SSIS expression to determine whether it was a weekday or weekend based on the system variable for package start time. Now I am not the best with expressions but this would be the one I would use:

DATEPART("dw",@[System::StartTime])

This will return a number between 1 and 7, with 1 = Sunday and 7 = Saturday. So from that task you would have two precedence constraints going to each path of your maintenance plan. If the value is 1 (one) then it goes to the rebuild index task, if it is anything else it goes to the reorganize task.

Although if I was going into this much detail I would stop using the index task with maintenance plans. I would suggest going to using T-SQL code that actually helps you maintenance the index fragmentation much more efficiently. I will say though I don't know if SQL Server has improved in 2012 with the index task. The most well known solution for index fragmentation management can be found here, Ola Hallengren.


Access Linked Tables and Their Network Connections

Your Access form would need to delete the linked tables, and recreate them in order to continue to have access to the remote table.

An interruption of the network connection would certainly cause the symptoms you report.  Having said that, you likely shouldn't be having network issues.  I'd try to find the cause of the issue, as well as building in the ability to deal with the problem in Access.


How can I show the values from my input that don't exist in a table?

create table order_table(order_number number);
insert into order_table values (43454235423);
commit;

with input_values as
(
  select
    to_number(column_value) as order_number
  from
    xmltable('43454235423, 785686847856, 1243215421, 6437647, 978423454')
)
select
  v.order_number, 'not found' as status
from
  input_values v
left join
  order_table o on (v.order_number = o.order_number)
where
  o.order_number is null
;

        ORDER_NUMBER STATUS
-------------------- ---------
        785686847856 not found
          1243215421 not found
             6437647 not found
           978423454 not found


SQL Server - Is it possible to forbid creation of default constraint name?

I think you could use a DDL trigger if you could build a reliable LIKE expression that determines that a column has been specified with a default but does not contain the word constraint before the word default. But that won't be very fun. If you don't have PBM you're probably better off just manually reviewing the DDL in the default trace or having a DDL trigger that e-mails you on create/alter table, and slapping wrists when it happens.

(I mean, how often are people creating tables in a way that violates your corporate naming standard, and why are they still allowed to create tables if they can't follow the rules?)


Cannot rebuild index, but there's no reason why not?

I skipped the code example, and jumped to what seems to be the real question here

  On top of that, a manual inspection of the index in question shows no text, ntext, image, xml or varchar(MAX), nvarchar(MAX) or varbinary(MAX). Could there be something I'm missing here?

  For the record, this is a clustered index.

You certainly are missing something, and the answer is in the body of the error message:

  For a clustered index, the column could be any column of the table

For Online Index Operations there is a difference between a Clustered and a Non-Clustered index.
both may not contain a BLOB column in order to rebuild online,
but while a Non-Clustered Index "contains" the columns in it's definition (and included columns),
a Clustered Index "contains" the entire table with all of the columns.

in your case there is a column named MEMO which is of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml, or large CLR type.
this column is part of the table, and thus part of the Clustered index.

Your options are:

Perform the REBUILD operation offline
Upgrade to SQL Server 2012
Change the column's data type

Guidelines for Performing Online Index Operations


Simulate deadlock with more than 2 processes

If you know how to simulate it with 2, you know how to simulate it with 3, or with N. You have to build a cyclic graph:

2 nodes: A->B->A  (process A waits on B, process B waits on A)
3 nodes: A->B->C->A  (process A waits on B, B waits on C, C waits on A)
...
N nodes: P1->P2->...Pn->P1

To construct the Px->Py primitive (Process X waits on Process Y) use your favorite blocking mechanism. For example, row locking on a specific key P1->P2->P3->P1:

CREATE TABLE rows (key INT NOT NULL PRIMARY KEY);

1) P1, from session 1:

BEGIN TRANSACTION
INSERT INTO rows (key) VALUES (1);

2) P2, from session 2:

BEGIN TRANSACTION
INSERT INTO rows (key) VALUES (2);

3) Have P1 wait on P2. From session 1:

SELECT key FROM rows WHERE key = 2;

4) Continue with P3, session 3:

BEGIN TRANSACTION
INSERT INTO rows (key) VALUES (3);

5) Add the P2->P3 wait, in session 2:

SELECT key FROM rows WHERE key = 3;

6) To complete the cycle, add the P3->P1 wait, in session 3:

SELECT key FROM rows WHERE key = 1;

As the wait graph created a cycle, the 3 processes are now deadlocked. In a short while the engine deadlock detection mechanism (which periodically walks the wait graphs looking for cycles) detects this cycle and breaks it by choosing a victim and aborting it's transaction then raising error 1205 in the victim session.

To extend to N nodes, repeat steps 4) and 5) as necessary.


Does `WITH XMLNAMESPACES` have the ability to trigger a DDL database trigger?

To the question in the title: I'm going to say NO. Something else is going on to force the DDL trigger to fire.

You should (at least temporarily) change the DDL trigger to store the EVENTDATA() output to a table so you can examine what else the app is sending when they call this stored procedure. There must be additional DDL that is being sent, such as an ALTER or DROP/CREATE, because a DDL trigger can't - by definition - be fired simply from executing a stored procedure that doesn't, itself, issue DDL commands.

You may want to set up a server-side trace, too, filtered to this application so you can see what else it might be sending to the server in addition to the call to the stored procedure, since there is a chance it is doing something they don't know. Trust but verify.


I know I'm late for party, but someone else with similiar issue may find it useful so anyways: I would say that you don't have AUDIT_TRAIL set to DB,EXTENDED but perhaps only to DB or something.

If you want SQLTEXT to be populated it is mandatory to have AUDIT_TRAIL set to DB,EXTENDED.

Check your current AUDIT_TRAIL status:

select * from v$parameter where name like '%trail%';

If it's not set to DB,EXTENDED, you set it with the following command:

alter system set audit_trail=db,extended scope=spfile;

Bear in mind that after setting it to DB,EXTENDED, you have to restart your instance for changes to take effect.

Afterwards after each audit record your SQLTEXT in SYS.AUD$ will also be populated.

From Oracle's documentation you will see more:
https://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams016.htm#REFRN10006


How to speed up Oracle to PostgreSQL data transfer

It might be worth pushing a subset to a file and loading it with copy, just to get a sense of the throughput. Copy is...legendary....for high-speed bulk loading into an empty table.

https://www.postgresql.org/docs/current/sql-copy.html


As from your question if the scenario is like that.

You are on your local machine and accessing the remote servers A and B.

Process is simple

Step1 : Connect to server A where A is username of server

ssh A@192.168.9.214

Step2 : Generate the database dump of the server A as

mysqldump -uroot -proot  db1  > database_db1.sql

Step3 : Copy the dump on the server B using SCP as

scp database_db1.sql B@192.168.9.215:/home/user/Desktop

Step4 : Connect to the Server B as

ssh B@192.168.9.215

Step5 : Restore the copied dump here using mysql client utility

mysql -uroot -proot db < /home/user/Desktop/database_db1.sql


inno_db_buffer_pool_size and key efficiency

If your tables are InnoDB (not MyISAM), innodb_buffer_pool_size should be about 70% of available RAM.  However it does not need to be much bigger than the total Data_length and Index_length for all your tables.

It does not hurt to make that tunable "too big" unless you need the RAM for something else.

If you would like further analysis, please provide SHOW VARIABLES and SHOW GLOBAL STATUS.  From that, we can sort out which engine the vague "key efficiency" refers to, plus check other things.  (I have automated about 200 checks.)

Also, it is unclear whether "InnoDB writes per second" is INSERTs or disk writes.  Since you have SSDs, it could be either.


ORACLE file I/O withouth explicit flushing on 10g and 11g

According to the documentation for

10g http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/u_file.htm#i1003326

11g http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/u_file.htm#ARPLS70908

and

12c http://docs.oracle.com/cd/E16655_01/appdev.121/e17602/u_file.htm#i1003326

  If there is buffered data yet to be written when FCLOSE runs, then you may receive a WRITE_ERROR exception when closing a file.

So you should call utl_file.fflush(foo); before closing.


'Incorrect datetime value: for column 'last_login' at row 1'

Description:
The sql_mode changes behaviour from 5.6 to 5.7.
I notice a create table statement that replicates fine on a 5.6 master breaks on the 5.7 downstream slave.

Last_SQL_Error: Error 'Invalid default value for 'last_change'' on query. Default database: 'db1'. Query: 'CREATE TABLE mytable (
id int(10) unsigned NOT NULL,
field2 tinyint(3) unsigned NOT NULL,
last_change timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
....

sql_mode on the 5.6 master is: sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
sql_mode on the 5.7 slave is: sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
(both the config in /etc/my.cnf)

Given the sql_mode change the value and this is being replicated the 5.7 stricter behaviour is being applied on the 5.7 slave by the SQL thread when in fact the 5.6 behaviour should be applied.

How to repeat:
see above.

Suggested fix:
Make the SQL thread aware of the master version being 5.6 and treat the sql_mode as if it were running on a 5.6 server to maintain the same behaviour.

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


I do this by putting each stored procedure/function into its own SQL file and in a folder and then executing them with a a recursive PHP script.

Example file procedure_name.sql would look something like this.

DROP PROCEDURE IF EXISTS procedure_name;
DELIMITER //
/**
Procedure Documantation
*/
CREATE PROCEDURE procedure_name()
BEGIN
   Code goes here
END//
DELIMITER ;

This also makes it easy to do a find and replace of column names etc.

The PHP code looks like so

foreach ($paths as $p) {
     if (strpos($p, '.sql') !== false) {
            echo $p . "\n";
            exec("mysql -u " . $user . "   -p" . $password . " < $p");
      }
 }

After making the changes one can either execute the sql file in MySQLWorkbench of run the PHP script. To sync them to another server you push them up with git pull them down and then execute the PHP script.

Do note that if you have functions they need to be created first.


Reporting table needs to get refreshed without killing user's read connection to it, any ideas?

Sure.  The simplest thing is to just make sure the reporting users using Row Versioning, either in SNAPSHOT ISOLATION, or by setting the database to READ_COMMITTED_SNAPSHOT.  See eg, Snapshot Isolation in SQL Server.

Then just make your updates in a transaction.  Reporting users will not be blocked, and will continue to see the "old" version of every row you change until you commit your transaction.

If the changes take a long time, you can alternatively build new tables, and once they are done start a transaction where you drop and rename the tables, or perform ALTER TABLE ... SWITCH to switch the table data out to a staging table, and switch the new data into the target table.


Populating one foreign key based on entry of another foreign key in Access 2016

Just because [TownshipID] is populated (i.e. not null) and because there is a relationship defined between the two tables, these facts do not mean that the table [Townships] is open and positioned at any particular record.  You must specifically direct Access to open the table and look for the related record.  You do that in the Before Change Data Macro by using the LookupRecord action.

Look Up a Record In: [Townships]
  Where Condition: Townships.ID = Certificates.TownshipID

Within that action's group is where you place the SetField action.

Other comments:

Data Macros are best for validating data.  They also have their place in keeping tables in sync as you are doing, but these conditions should be rare, especially if your tables are properly normalized.

It appears that Certificates.TownshipID is optional and can be null.  I assume that means the county can be specified independently for the certificate when there is no township.  Correct?  If so, your technique is probably justified.  Otherwise, there would be no need to also store the county with the certificate since it could always be retrieved via the Township relationship.

Since you already have a form where Certificate data is entered, you could also place relevant code in the form's module, for example in the TownshipID_AfterUpdate() event handler (or similar method) to retrieve and update the county information.