Join and count boolean columns by logical OR

All users present on Monday:

SELECT u.*
FROM  (
   SELECT user_id
   FROM   schedule_table
   GROUP  BY 1
   HAVING bool_or(mon)
   ) s
JOIN   user_table u ON u.id = s.user_id;

The aggregate function bool_or() comes in handy.

Summary table:

SELECT count(mon OR NULL) AS mon
     , count(tue OR NULL) AS tue
     , count(wed OR NULL) AS wed
     , count(thu OR NULL) AS thu
     , count(fri OR NULL) AS fri
FROM  (
   SELECT user_id
        , bool_or(mon) AS mon
        , bool_or(tue) AS tue
        , bool_or(wed) AS wed
        , bool_or(thu) AS thu
        , bool_or(fri) AS fri
   FROM   schedule_table
   GROUP  BY 1
   ) s;

Result as desired:

mon tue wed thu fri
4   3   4   4   4

Note that I didn't join to user_table at all here. It's not needed.

SQL Fiddle.

About counting techniques:

For absolute performance, is SUM faster or COUNT?

However, your database design is odd, schedule_table has no information to signify the period of the day. And why bundle attributes for the same period on all days in one row?

Either, to store explicit information:

CREATE TABLE schedule (
  schedule_id int PRIMARY KEY
, schedule text NOT NULL
);

INSERT INTO schedule VALUES
  (11, 'mon, period 1')
  (12, 'mon, period 2')
  (13, 'mon, period 3')
  (14, 'mon, period 4')
  (21, 'tue, period 1')
  (22, 'tue, period 2')
  -- etc.
  ;

CREATE TABLE user_schedule (
  user_schedule_id integer,
  user_id int REFERENCES users,
  schedule_id int REFERENCES schedule
);

Only enter rows for periods with presence.
Or, to optimize storage:

CREATE TABLE users (
  user_id serial PRIMARY KEY
, start_dt date
, presence_bitmap bit(20)
);

INSERT INTO users (start_dt, presence_bitmap) VALUES
 ('2015-02-14', '11111111111111111111'),
 ('2015-02-14', '00000000000000001111'); -- etc.

Only needs a fraction of the storage on disk.


T-SQL - OPTION (FAST x) and trace flag 8722

As far as I know, trace flags 8722, 8755, and 8602 were never officially documented. The last time I remember 8722 and 8755 being effective was in SQL Server 2000, so it is not surprising you find they are ignored in SQL Server 2012. Trace flag 8602 is still functional in SQL Server 2019 as a global or start-up flag to ignore index hints specifically.
For specific query patterns, it is often possible to remove the FAST n hint using plan guides. Even so, the best fix is to get the AX developers to fix it on the application side of things.


AlwaysOn AG and Cross-Database/Server Transactions

Is there a way to live with this limitation?

As of now, no not in a supported manner (I won't reference the link, as you have already done that in your original question).  There are potential inconsistencies that can arise from this configuration.  With that being said, there will be no hard enforcement on this, so you will not be prevented from the implementation.  But with the possible inconsistent pitfalls that may arise, I wouldn't and couldn't make the recommendation to go forward with AGs and distributed transactions.

  What do you guys do who use AG and have cross-database and cross-server transactions?

Currently, not implement AGs in this type of environment.


You are trying to write a PIVOT query.  If you're trying to use a CASE to do so (which implies that you are not on 11g where you can simply use the PIVOT operator), you need to do an aggregate

SELECT work_id,
       MAX( CASE WHEN <<condition1>> then group else null end ) as "1.group",
       MAX( CASE WHEN <<condition2>> then group else null end ) as "2.group",
       MAX( CASE WHEN <<condition3>> then group else null end ) as "3.group",
       MAX( CASE WHEN <<condition1>> then effort else null end ) as "1.effort",
       MAX( CASE WHEN <<condition2>> then effort else null end ) as "2.effort",
       MAX( CASE WHEN <<condition3>> then effort else null end ) as "3.effort"
  FROM <<table name>>
 GROUP BY work_id

Of course, you presumably don't actually have a column named GROUP in your table since that is a reserved word.


How to analyze impact of a query on another queries?

Is there a such tool that can monitor some query performance over
  time?

There are several solutions such as Spotlight, SolorWinds, SQL Monitor (Red Gate).


Does sys.dm_os_waiting_tasks include RUNNABLE threads?

A task that yields the scheduler because it exhausts its quantum will not appear in sys.dm_os_waiting_tasks; however, any signal wait time it experiences will be accumulated in sys.dm_os_wait_stats under SOS_SCHEDULER_YIELD.

A task that yields to acquire a currently-unavailable "waitable resource" does appear in sys.dm_os_waiting_tasks with the appropriate wait_type and is suspended. It accumulates resource wait time until signalled (when the resource becomes available) and runnable. The task then accumulates signal wait time until it acquires the scheduler and transitions to running status. The task remains visible in sys.dm_os_waiting_tasks until the wait is fully completed (including signal wait), and the wait_type never changes.

That is my understanding, and observation. I could not find this level of detail officially or semi-officially documented anywhere.

A note of caution: sys.dm_os_waiting_tasks is a non-transactional view over rapidly-changing internal SOS task data structures. Many DMVs work in this way, and have been written by different developers at different times. Their output can be very useful, but joining several DMVs can easily lead to apparently inconsistent results.

Comparing snapshots of the sys.dm_os_waits DMV is a better way of splitting aggregate resource and signal waits for a particular wait type. Extended events e.g. scheduling.wait_completed can be used to capture specific wait events if that level of detail is required.

  ...do you agree the wording of BOL is not correct/confusing?

Technical writing is hard. There'll always be some who complain there is too much detail, while others say there is too little. The DMV shows waits on some resources, until the wait is over. I guess it could be clearer in some respects, but how far can you go without detailing the internal implementation, and ultimately providing the source code?


How to load more than one MS-Access-db tables with data in SQL-Server-DB

In SSMS, you can right click on your target database, select Tasks...Import Data, then choose your Access database as the Source and then choose your target SQL Server database as the Destination.


Find and Insert Missing data in Mongodb Collection

Assuming your numbers are sequential (which appears to be the case for BTC height values), you can use the $range aggregation expression to generate the expected sequence of values for comparison with the existing values.

Here's an example aggregation function using the mongo shell:

function findMissingValues(start, end) {
   var results = db.blocks.aggregate([

      // This stage would benefit from a relevant index like `height: 1`
      { $match: {
         height: {$gte: start, $lte: end}
      }},

      // Find existing height values in the given range
      { $group: {
          _id: null,
          heights: {$push: "$height"}
      }},

      // Add the expected range (inclusive of start and end values)
      { $addFields: {
          allHeights: { $range: [ start, end + 1]}
      }},

      // Find any expected values that are missing from the existing data
      { $project: {
          _id: 0,
          missing: { $setDifference: [ "$allHeights", "$heights" ] }
      }}

   ]).toArray();

   printjson(results[0]);
}

Sample usage:

> db.blocks.insert([ { height: 550000 }, { height: 550002 } ])

> findMissingValues(550000,550005)
{ "missing" : [ 550001, 550003, 550004, 550005 ] }

With the missing blocks identified, you can now retrieve and insert the relevant BTC data.


How to configure mongodb replica set with redundant networks

Yes, you can have mongod at multiple networks, but when you configure replica set, you use only addresses of one network. Like network 10.1.1.x addresses. To clients you can give list of addresses from 10.1.2.x network, if that is preferred.

Those addresses what you configure with --bindip are addresses where mongod answers connection attempts.


SSRS report subscriptions generate job entries in SQL Agent.  Find the corresponding agent job and set email alerts through it's properties for failure.


Finding Item's distribution over customer routes

select sd.item,
       sd.brand,
       cm.Route,
       cm.Route,
       count(*) as TotalCustomersCount
from SalesData sd with (nolock)
  inner join CustomerMaster cm
    on sd.CUSTOMERCODE=cm.CODE
group by cm.ROUTE

I am not sure what BuyingCustomersCount is.


One way is to calculate the aggregate inside a CTE (I blogged about this last month):

;WITH CTE AS
(
  SELECT time1, time2, t2 = SUM(time1) OVER()
  FROM #abcd
)
UPDATE CTE SET time2 = t2/60/60;

But I don't understand the value of putting the same SUM(), aggregated over the whole table, into that column for every single row. Seems easier to just store this single value in a variable, or calculate it at runtime (just use the query inside the CTE).


pg_dump. Some COPY processes stuck on 'idle in transaction' after pg_dump completion

It is slightly surprising that pg_dump returned sucessfully with a return code of 0. The only theory I can come up with is that some of the 10 jobs got done early and started to idle, whereupon a misconfigured firewall cut the connection. The backends should die after around 2 hours due to TCP keepalive.
What you could do is reduce the keepalive idle time and see if that improves things:
pg_dump -d 'host=... port=... dbname=... user=... keepalives_idle=300' -f dumpfile


Doing a select based on a count(), a date range, and string match

It's not a trivial query. You need one more join or a subquery:

FROM [ECITATION].[dbo].[tblDocument] AS d
    JOIN [ARK].[dbo].[DocStatus] AS ds
    ON  d.DocStatus = ds.StatusNum AND ds.StatusName LIKE 'void%'

WHERE ds.DateModified BETWEEN @BeginDate AND @EndDate
--DateModified is when the document was voided--

  AND ( SELECT COUNT(*)
        FROM [ECITATION].[dbo].[tblDocument] AS dc
        WHERE dc.CreatorUserId = d.CreatorUserId
          AND dc.DateCreated BETWEEN @BeginDate AND @EndDate
      ) >= @MinVoids ;

Not sure about the name of the column (used DateCreated) in table [tblDocument]. Modify accordingly


Global only scoped trace flags in MS SQL Server

If the scope of the trace flag is global only does it mean I can't use it only for one connection while not affecting the performance of others?

The scope signifies the affect of trace flag, a global scope means it will be applicable to all connections of SQL Server, it cannot be made to work only for particular connection/session. On the other hand trace flags whose scope are limited to session will be applicable to particular session.

If you refer to Trace Flags documentation it says

  Trace flag behavior may not be supported in future releases of SQL Server.

If you are using SQL Server 2008 and above you have extended events to get deadlock information. Please see

Getting historical deadlock info using extended events
Graphically Viewing Extended Events Deadlock Graphs


Extended Events are notorious for being poorly documented.  Duration / units is especially so, see:

Extended event duration Millisecond or Microsecond?

Despite the fact that "seconds" is in the event field name, you're correct that the value being reported is nowhere near seconds.  You mentioned that you have to divide the number by a trillion (a 1 with 12 zeroes after it).  Rather than microseconds, that actually means the number is in picoseconds.  Which sounds...crazy.  But I think it's right.

To confirm this, I ran SELECT * from dbo.Posts; on the StackOverflow2010 database while running three different trace tools simultaneously and compared the results.

Extended Events: perfobject_logicaldisk

This event gets logged every 15 seconds.

For the "C:" instance_name at timestamp 09:44:43 (thus a span starting at 09:44:28), I got an average_disk_seconds_per_read of 3,839,009,107 during that 15 second interval.

For the "HarddiskVolume1" instance_name at that same timestamp, I got average_disk_seconds_per_read of 2,239,089.

There's also an entry for instance_name "HarddiskVolume3" with average_disk_seconds_per_read of 1,046,233.

I think those two volumes are system reservations and restore partitions respectively.  For reference, here's the output from DiskPart on my machine

C:\WINDOWS\system32>diskpart

Microsoft DiskPart version 10.0.17134.1

Copyright (C) Microsoft Corporation.
On computer: *****************

DISKPART> list volume

  Volume ###  Ltr  Label        Fs     Type        Size     Status     Info
  ----------  ---  -----------  -----  ----------  -------  ---------  --------
  Volume 0     D                       DVD-ROM         0 B  No Media
  Volume 1         System Rese  NTFS   Partition    500 MB  Healthy    System
  Volume 2     C                NTFS   Partition    977 GB  Healthy    Boot

PerfMon.exe: LogicalDisk -> Avg. Disk sec/Read

This Windows performance counter object is documented as being reported in seconds.  I only had it log the "C:" instance name.  I see values around .002 seconds (2 milliseconds) on average during the period mentioned above.

PerfView: Windows Kernel/DiskIO/Read ETW event

PerfView samples much more aggressively than the other methods here (see screenshot with a snippet of the details below).  Dumping that output (which is filtered to only include I/O done by the SQL Server process) into Excel indicates that the average seconds per read was 2.7 milliseconds over the entire sample.

What does it all mean?

ETW Tracing and Performance Counters, which are well known Windows components for measuring various performance metrics, indicate that reads were taking on average somewhere between .002 and .003 seconds (2-3 milliseconds).

Since the value we got from extended events was 3,839,009,107 I think we can assume it's trying to tell us .003 seconds (closer to .004).  Backing in to that number, we'd have to divide by a trillion to get there.

I might be bad at math, but it looks like the number reported by this EE field is in picoseconds.

Terrible.  Just...terrible.


Configured MySQL replication but its not working

What concerns me here is the use of both replicate-do-db on the Slave and binlog-do-db on the Master at the same time.

There is no need to use both. I would remove binlog-do-db from the Master. I would also look over any queries with fully qualified table names. Why ?

If you replicate any SQL use fully qualified table names (dbname.tblname) and you use replicate-do-db, MySQL Replication gets confused if you execute USE dbname.

Percona explained this in conjunction with binlog-do-db and replicate-do-db back in May 2009.

Percona also suggested reading the replication filtering rules thoroughly.


How does Database Snapshots Work (MySQL)?

In this specific case, it's because the sequential restore of a blob of data is faster than the traversal in order that MySQL does it in, and because MySQL could be CPU bottlenecked during some parts of the operation. Additionally, snapshot restores in AWS may not be constrained by the storage IOPS you are paying for.

In general, restoring a snapshot can be incredibly quick - with ZFS a rollback to a snapshot doesn't require restoring any data at all, it involves only marking newer data blocks as discarded. This takes milliseconds to seconds, rather than minutes.


Execution plan with multiple missing indexes

The Query Optimizer in SQL Server can make multiple missing index suggestions for individual queries.  However the part of SQL Server Management Studio (SSMS) which displays execution plans visually only displays a single missing index suggestion; it looks like a bug.  However these multiple index suggestions are visible in SSMS, eg in the properties for the first operator (eg SELECT), press F4.

As you've also noted, multiple suggestions are visible in the plan XML, or third-party tools like SQL Sentry Plan Explorer.  Multiple suggestions will also be visible in the missing index DMVs (eg sys.dm_db_missing_index_details )

This simple query resulted in multiple suggestions for me:

USE tempdb
GO

SET NOCOUNT ON
GO

IF OBJECT_ID('dbo.t1') IS NOT NULL DROP TABLE dbo.t1
CREATE TABLE dbo.t1
(
    rowId INT IDENTITY
)
GO

IF OBJECT_ID('dbo.t2') IS NOT NULL DROP TABLE dbo.t2
CREATE TABLE dbo.t2
(
    rowId INT IDENTITY
)
GO

INSERT INTO dbo.t1 DEFAULT VALUES
GO 100000

INSERT INTO dbo.t2 DEFAULT VALUES
GO 100000

SELECT *
FROM dbo.t1 t1
    INNER JOIN dbo.t2 t2 ON t1.rowId = t2.rowId
WHERE t2.rowId = 999;

HTH


MySQL (MariaDB) bound to 0.0.0.0, but doesn't appear to be listening to all IPs?

Solved this issue!
It turns out that it was a bug in the local private IP assignment with Ubuntu 18.04 and the host provider, Linode. After looking into things a little deeper, even though the private IP was assigned, manually setting up Netplan instead of the pre-existing automated networking setup resolved the communications issue.


mysql load data local infile is skipping the 1st column of the 1st row

Depending on the source of the text file, the csv file here, it may have the byte of order (BOM) character.

In this case, the very first column from fist row will be an invalid integer, and will be saved as 0 in the DB.

To check if the file has BOM letters at its beginning, refer to this link.


I think this is a situation where you just need a cross apply.  If you are not familiar with it, look at this link.

With your data I created a table and inserted the data with this scrip

CREATE TABLE school
(
    schoolcode VARCHAR(20)
    ,SchoolYear INT
    ,Semester INT
    ,Term INT
    ,TermEnd DATETIME2

);

INSERT INTO dbo.school
        (
          schoolcode,
          SchoolYear,
          Semester,
          Term,
          TermEnd
        )
VALUES  ('EXAMPLE', 20142015, 1, 1, '2015-01-23')
, ('EXAMPLE', 20142015, 1, 2, '2015-06-20')
, ('DEMO', 20142015, 1, 1, '2015-01-23')
, ('DEMO', 20142015, 1, 2, '2015-06-20');

And then I run the select to get your desired results in the following fashion

SELECT DISTINCT t.*
FROM School s
CROSS APPLY (SELECT TOP 1  *
                FROM school s1
                WHERE s1.schoolcode = s.schoolcode
                AND s1.SchoolYear = s.SchoolYear
                AND TermEnd >= '2015-01-20'
                ORDER BY TermEnd) t

If you change your date, you should continue to get those desired results.

The APPLY operator can be your friend.


Can PostgreSQL UPDATEs in autocommit mode deadlock?

That is certainly possible if the update affects more than a single table row.
This can come to pass in two ways:

The query modifies more than a single row in the target table.

The updated table has a foreign key constraint to another table. In that case, you may end up with a FOR KEY SHARE lock on the referenced table.

The deadlock can happen if your statement successfully locks a couple of rows and then blocks on a lock on some other row.


MySqli - select from db based on time difference with CURTIME() or SUBTIME()

Ok, i 've found the solution and i want to share it with you.

SELECT * FROM tbl_name WHERE dt_column < DATE_SUB(NOW(), INTERVAL 20 MINUTE)

This brings all entries were last login  was more than 20 mins in the past.


Sorry for being late to the party. :)
Here's my answer, based on having used Mnesia since 1996 and various other database technologies since 1988.

Mnesia and MySQL are indeed different beasts, and which one is the best depends very much on how you intend to use it.

If your application is written in Erlang, Mnesia allows you to store the data in the same memory space as your application, which means you can fetch a single data object as quickly as a few microseconds. This is not possible in MySQL, since your application and the database will be separated in memory. The reason why Mnesia can do this and still be robust, is that Erlang implements memory 'protection' at the language level.

Overall, SQL databases tend to favor throughput over latency, and when it comes to latency, Mnesia+Erlang are generally outstanding. You need to decide which one is most important to you. As it says in the docs (above), Mnesia's target applications were telecom switching applications, where response time requirements for e.g. a call setup were around 20 ms. This essentially meant that you could read from the database only if the data was in shared memory, but would avoid writing to persistent storage on a per-call-setup basis. OTOH, these applications have practically no need for ad-hoc query support, and do not use very large data sets. Some work has been done to extend the suitability of Mnesia for other domains, but it is not a priority for the Erlang/OTP development team. Mnesia is what it is, and is likely to stay that way.

In the link above where Mnesia and MySQL are compared for speed, one needs to remember that it's in eJabberd, which runs against a single server if it's MySQL and runs a fully replicated database if it's Mnesia - and large eJabberd clusters can have as much as 10 or more erlang nodes (and thus, 10 or more Mnesia replicas). From a redundancy standpoint, this is fairly ridiculous and costly, and Mnesia by no means forces you to do so. It obviously gives wicked-fast reads on each node, but writes will be very expensive. Several comparisons I've read have ended up comparing distributed Mnesia with a single-node MySQL; if redundancy is not needed for MySQL, it shouldn't be required for Mnesia either. Mnesia is quite flexible in letting you choose replication patterns, and data location is transparent to the application.

Mnesia is also not limited to 2 GB per table (although a particular storage option is). The largest Mnesia database I know of has ca 600 GB of data in (64-bit) RAM+disk - although I do not recommend this. Anything up to 10-20 GB should be perfectly fine with modern hardware though, but skip disc_only_copies entirely and use disc_copies - buy more RAM if you have to. I'd think twice before using the sharding support (mnesia_frag) - it works, but is rarely worth the trouble.

Perhaps the biggest difference between Mnesia and MySQL is SQL itself: Mnesia doesn't really have comparable functionality; QLC offers some support for ad-hoc queries, but it is not in the same league as SQL, and neither is the level of query optimization. In tooling and provisioning, MySQL is also superior, and if you need analytics, there's no question which one you should choose (i.e. NOT Mnesia).

The best way to view Mnesia is as an extension to the Erlang language. It puts data right at your fingertips, and is excellent for small data sets where the data structure and access patterns are well known. For this purpose, using MySQL is about as uncomfortable as is using Mnesia for the things where MySQL works best.

Most applications fall somewhere in between, and this is where it becomes a judgement call. You may well end up using both...


Single foreign key for referencing one of multiple tables

Yea there's no reason not to if it fits your use case better, there's nothing wrong with that design. My only suggestion would to add a extra_type field to explicitly identify whether the record is of type person or car. You'll likely find a field like that will be helpful later on. But yes that's a valid schema design.

After further clarification in the comments, you'd have to make a couple changes to natively accomplish this to still be able to support foreign key constraints in the database (otherwise what you'd be looking for is a polymorphic foreign key which isn't natively supported).
The first change is you'd have to merge your person and car table into a single table, e.g. person_or_car with the person_or_car_id primary key field. The second change is you'd need to add an extra_type_id field to the person_or_car table to distinguish between the two. And finally you'll need to add the extra_type_id field to the extra_m2m table, and create the foreign key constraint on it.
Example query with the extra_type_id field, as discussed in the comments, for step 3:
create table extra_m2m
(
    person_or_car_id uuid not null,
    extra_type_id int,
    extra_id uuid not null references extra(id) on delete cascade,
    primary key (person_or_car_id, extra_id),
    foreign key (person_or_car_id, extra_type_id) references person_or_car (person_or_car_id, extra_type_id) on delete cascade
);


Distributed transaction coordinator between SQL Server and Oracle

Technically it is possible. This Oracle Documentation might help you to setup the distributed transaction processing


What is WITH QUERY EXPANSION MODE in MySQL Fulltext Search

The short answer to your question is that query expansion enhances the original search term by adding extra, relevant, information to the search term. Mis-quoting an example from Derek's link:

  An example could be searching for books by Georges Simenon about
  Maigret, when a user is not sure how to spell “Maigret”. A search for
  “Megre and the reluctant witnesses” finds only “Maigret and the
  Reluctant Witnesses” without query expansion. A search with query
  expansion finds all books with the word “Maigret” on the second pass.

This tutorial should make things clearer:

Using MySQL Query Expansion

Summary: in this tutorial, you will learn about MySQL query expansion to widen the search results based on automatic relevance feedback.

Introduction to MySQL Query Expansion

In some cases, users want to search for information that based on the knowledge that they have. Users use their knowledge to define keywords to search for information, and typically those keywords are too short. To help users to find what they want based on the too-short keywords, MySQL full-text search engine introduces a concept called query expansion.

The query expansion is used to widen the search result of the full-text searches based on automatic relevance feedback (or blind query expansion). Technically, MySQL full-text search engine performs the following steps when the query expansion is used:

First, MySQL full-text search engine looks for all rows that match
the search query.
Second, it checks all rows in the search result and finds the
relevant words.
Third, it performs a search again but based on the relevant words
instead the original keywords provided by the users.

From application perspective, you can use the query expansion when the search results are too few. You perform the searches again but with query expansion to offer user more information that are related and relevant to what they are looking for.

To use the query expansion, you use the WITH QUERY EXPANSION search modifier in the AGAINST() function. The following illustrates the syntax of the query using the WITH QUERY EXPANSION search modifier.

SELECT column1, column2
FROM table1
WHERE MATCH(column1,column2)
      AGAINST('keyword',WITH QUERY EXPANSION)

MySQL Query Expansion Example

Let’s take a look at an example to see how MySQL query expansion works.

We will use the productName column of the products table to demonstrate the query expansion feature. First, we enable the full-text search feature for this column.

ALTER TABLE products
ADD FULLTEXT(productName)

Second, we search for product whose product name contains the 1992 keyword without using query expansion.

SELECT productName
FROM products
WHERE MATCH(productName) AGAINST('1992')

Gives

+-----------------------------------+
| productName                       |
+-----------------------------------+
| 1992 Ferrari 360 Spider red       |
| 1992 Porsche Cayenne Turbo Silver |
+-----------------------------------+
2 rows in set (0.00 sec)

As you see, the search results has 2 products whose product name contains 1992.

Third, we can widen the search result by using query expansion as the following statement:

SELECT productName
FROM products
WHERE MATCH(productName)
      AGAINST('1992' WITH QUERY EXPANSION)

Gives

+-------------------------------------+
| productName                         |
+-------------------------------------+
| 1992 Porsche Cayenne Turbo Silver   |
| 1992 Ferrari 360 Spider red         |
| 2001 Ferrari Enzo                   |
| 1932 Alfa Romeo 8C2300 Spider Sport |
| 1948 Porsche 356-A Roadster         |
| 1948 Porsche Type 356 Roadster      |
| 1956 Porsche 356A Coupe             |
+-------------------------------------+
7 rows in set (0.00 sec)

We got more rows in the search result when we used query expansion. The first two rows are the most relevant and the other rows come from the relevant keyword that are in the first two rows e.g., Ferrari.

Notice that blind query expansion tends to increase noise significantly by returning non-relevant results. It is highly recommended that you use query expansion only when the searched keyword is short.

In this tutorial, we have introduced you to MySQL query expansion to widen the search results when the keywords provided by users are short.

Source: Using MySQL Query Expansion


Linked Server with Windows Authentication Permissions

The "Be made using this security context" doesnt support Windows Authentication.

  Be made using this security context Specify that a connection will be
  made using the login and password specified in the Remote login and
  With password boxes for logins not defined in the list. The remote
  login must be a SQL Server Authentication login on the remote server.

You need to use this:

  When connecting to the local server using Windows Authentication
  (recommended), select Be made using the login's current security
  context to connect to the remote server using the same Windows
  Authentication credentials.

Or you could use "Not be made" and specify the login with impersonate. Requires that the user exists on both servers aswell.

You can read more here: https://technet.microsoft.com/en-us/library/ms188477(v=sql.105).aspx


Is an in-memory table faster to read from than a table cached in memory?

What is the wait type you experience when your on-disk table is slow to read from when it's already cached?

Memory-optimized tables are optimized for writing, not reading, so it's very unlikely you'd see any performance benefit from migration to Hekaton.


Information_schema appearing in slow_log

Found the issue, Google App Engine runs the query for monitoring


Installing PostgreSQL 9.2 and PostGIS 2.0 on Ubuntu 12

Just a followup: I just checked it and now there is a package called postgresql-9.2-postgis-2.0.  The problem is finally gone.


SSIS is going to execute outside of SQL Server's address space. The data flow transformation within SSIS derives its performance through in-memory transformation to data. The more ram you can give it, the better theoretical performance you can obtain.

All things being equal between servers, I'd be surprised if there was an appreciable difference no reference sited

Things are rarely equal, even between servers that are spec'ed the same

If A has 32GB of RAM with SQL Server sized to take 24GB, that leaves 8 gigs for the OS and any other applications, like SSIS, to run in. If B is similarly allocated, things may still not be exactly the same - do people regularly RDP into one over the other? Do they do copy files around (#3 Check task manager) Lots of little things like that will skew the amount of memory left to the OS. Moral of the story, run it on the box that likely has the most amount of RAM available.

Unless you profile your SSIS packages and determine that you're CPU bound, in which case you need to see which server is less CPU intensive, which may vary based on the ETL schedule. I've had large servers that are basically idle during "working hours." When the ETL fires off, the CPU's go to 100 percent for few hours.

There is a destination within the Data Flow Task, the SQL Server destination. It only works if the package is executed on the destination server, B. When SSIS was brand new, everyone wanted to use this as it was screaming cheetah wheelies fast. Under the covers, they do this awesome memory space mapping thing into SQL Server - sort of like a partition switch. The data is in SSIS space and poof it's now in SQL Server's space. Made for really fast destination. Except it's fragile as hell. Not only does it have the aforementioned limitation (which you now understand due to the whole memory mapping exercise going cross server is not an option) but you would regularly encounter errors that basically said "oops, that didn't work, try again" Restart the package with all the same data and now it saves fine. No code changes, no data changes, nothing. Just randomly fails, or succeeds depending on how you view the situation. Moral of the story, use the OLE DB Destination - performance is just as good as SQL Server destination but without the baggage.

If A or B have different disk configurations and/or performance signatures, Spinning Rust on A while B has the better-than-SSD-memory-thing do your processing on the one with better resources, especially if you are pulling LOB types or see the "Buffers Spooled" performance counter being non-zero then your in memory ETL is now disk based file processing and you will feel the pain.


You need to run ALTER PROCEDURE to change a stored procedure definition. See docs here.

You also need to make sure you're using the correct database. I bet if you look in master, it'll be there.

In the meantime, put USE [YourDatabaseName] at the top of your code.


Lack of space in SYSTEM and SYSAUX tablespace in oracle 11g R2

The SYSTEM and SYSAUX tablespaces are reuqired by the RDBMS to function correctly.

The SYSAUX tablespace

A list of objects that reside in the SYSAUX tablespace can be retrieved using the V$SYSAUX_OCCUPANTS view. This view displays the following information:

OCCUPANT_NAME        VARCHAR(64)    Occupant name
OCCUPANT_DESC        VARCHAR(64)    Occupant description
SCHEMA_NAME          VARCHAR2(64)   Schema name for the occupant
MOVE_PROCEDURE       VARCHAR2(64)   Name of the move procedure; null if not applicable
MOVE_PROCEDURE_DESC  VARCHAR2(64)   Description of the move procedure
SPACE_USAGE_KBYTES   NUMBER         Current space usage of the occupant (in KB)

Reference: V$SYSAUX_OCCUPANTS

The SYSAUX table is home for the following components:

SYSAUX Table                         Previous Location
------------------------------------ --------------------------
Analytical Workspace Object Table    SYSTEM
Enterprise Manager Repository        OEM_REPOSITORY
LogMiner                             SYSTEM
Logical Standby                      SYSTEM
OLAP API History Tables              CWMLITE
Oracle Data Mining                   ODM
Oracle Spatial                       SYSTEM
Oracle Streams                       SYSTEM
Oracle Text                          DRSYS
Oracle Ultra Search                  DRSYS
Ora interMedia ORDPLUGINS Comp.      SYSTEM
Ora interMedia ORDSYS Components     SYSTEM
Ora interMedia SI_INFORMTN_SCHEMA C. SYSTEM
Server Manageability Components      -
Statspack Repository                 User-defined
Oracle Scheduler                     -
Workspace Manager                    SYSTEM

Reference: Database Components and the SYSAUX Tablespace

If you can reduce the size of the data being stored by the components, then you can reduce the size of the required SYSAUX tablespace.

The largest portion of the SYSAUX tablespace is occupied by the Automatic Workload Repository (AWR). The following size recommendations are provided by Oracle to determine the AWR size and to a large portion the size of the SYSAUX tablespace:

Number of CPUs                                 2    |     8    |    32
Number of concurrently active sessions        10    |    20    |   100
Number of user objects: tables and indexes   500    | 5,000    |50,000
Estimated SYSAUX size with default config    500 MB |     2 GB |     5 GB

Reference: Controlling the Size of the SYSAUX Tablespace

If your database has been constantly growing, or if you are monitoring a lot of objects with Enterprise Management Console, then your SYSAUX tablespace will slowly fill up.

The SYSTEM tablespace

The SYSTEM tablespace always contains the data dictionary tables for the entire database. So if your database grows in size due to new objects, then your SYSTEM tablespace will require more space too.

Data Dictionary

What does the data dictionary contain:

The definitions of all schema objects in the database (tables, views, indexes, clusters, synonyms, sequences, procedures, functions, packages, triggers, and so on)
How much space has been allocated for, and is currently used by, the schema objects
Default values for columns
Integrity constraint information
The names of Oracle users
Privileges and roles each user has been granted
Auditing information, such as who has accessed or updated various schema objects
Other general database information

Reference: The Data Dictionary

Solutions:

Reduce the amount of monitored objects in EM
Resize the SYSAUX tablespace according to the recommendations in above table.

ALTER DATABASE DATAFILE '/path/systemSID.dbf' RESIZE 1000M;

Resize the SYSTEM tablespace

ALTER DATABASE DATAFILE '/path/sysauxSID.dbf' RESIZE 1000M;


How to display the dates without DUPLICATE and ORDER by MAX DATE

This is yet another installment of the MySQL sql_mode = only_full_group_by saga.

<rant>
The problem is that MySQL's default (till 8) will return results which, depending on the order of the records being INSERTed into the table (and/or PRIMARY KEY ordering), will vary and be correct or wrong DEPENDING. This is absolutely the worst sort of difficult to reproduce error that is so typical of MySQL.
<\rant>

However, it is good to see that MySQL's default behaviour is now the correct one (more than 25 years after PostgreSQL, FirebirdSQL, and the major vendors (Oracle, SQL Server &c...)!!).

What you need to do is the following:

-- create the tables - the fiddle (for MySQL version 8 is here)

CREATE TABLE memb
(
  last_m VARCHAR (30),
  CONSTRAINT memb_pk PRIMARY KEY (last_m)
);

-- and

CREATE TABLE tblprint
(
  last_m VARCHAR (30),
  printdate DATETIME NOT NULL,
  CONSTRAINT tp_pk PRIMARY KEY (last_m, printdate),
  CONSTRAINT tp_memb_fk FOREIGN KEY (last_m) REFERENCES memb (last_m)
);

-- see end of this answer for populating these tables with data

I added more records and deliberately inserted them in an order which was not that of the dates either ASCENDING or DESCENDING - see discussion below.

The correct answer is

SELECT
  m.last_m,
  MAX(tp.printdate) AS "p_date"
  -- COALESCE(MAX(tp.printdate), '"NULL"') AS "for_display" -- optional, just for show!
FROM
  tblprint tp
RIGHT JOIN memb m
  ON tp.last_m = m.last_m
GROUP BY last_m
ORDER BY last_m, p_date;

Strictly speaking, the RIGHT JOIN should be a RIGHT OUTER JOIN.

Result:

last_m  p_date
ANDRES, JOSE    2024-07-29 13:33:24
LORISA, MARIA   2021-07-29 13:37:44
SENINA, JUSTINA
TAN, LOLITA

which is the correct result, no matter what the record insertion order is.

Now, there is an alternative fiddle for version 5.6 (available here) which is the closest to 5.5 which you are using. A dbfiddle.com version for 5.5 is available here - it appears to take the first date arbitrarily by PRIMARY KEY order - so it selects the smallest.

As you will be able to see - the defauts of MySQL, the 5.6 version, are much more permissive than 8. You can see that sql_mode = only_full_group_by for version 8. You can set this (HIGHLY RECOMMENDED) for version 5.5. I would also strongly recommend that you upgrade to version 8 - it's been around a while now.

You will also find that if you experiment with the record insertion order, you can get different results in 5.6 - for example, if by coincidence, the data is inserted in reverse date order by last_m into the tblprinter table, you will be able to get the "correct" results from @HiteshRathi 's  query - of course this is very very dangerous!

I recommend that you "play around" with the various queries in the fiddles and see what happens - record insertion order and PRIMARY KEY order are factors to look at. Make sure your queries don't return different results with differing record insertion orders and/or PRIMARY KEY order.

-------------------- Data for the tables ---------------------------

INSERT INTO memb VALUES ('ANDRES, JOSE');
INSERT INTO memb VALUES ('LORISA, MARIA');
INSERT INTO memb VALUES ('SENINA, JUSTINA');
INSERT INTO memb VALUES ('TAN, LOLITA');

INSERT INTO tblprint VALUES ('ANDRES, JOSE', '2019-07-29 13:39:44');
INSERT INTO tblprint VALUES ('ANDRES, JOSE', '2001-09-01 23:33:41');
INSERT INTO tblprint VALUES ('ANDRES, JOSE', '2002-07-29 13:33:42');
INSERT INTO tblprint VALUES ('ANDRES, JOSE', '2003-07-29 13:33:43');
INSERT INTO tblprint VALUES ('ANDRES, JOSE', '2024-07-29 13:33:24');
INSERT INTO tblprint VALUES ('ANDRES, JOSE', '2020-07-29 20:20:20');

INSERT INTO tblprint VALUES ('LORISA, MARIA', '2014-07-29 13:34:44');
INSERT INTO tblprint VALUES ('LORISA, MARIA', '2007-07-29 13:31:44');
INSERT INTO tblprint VALUES ('LORISA, MARIA', '2008-07-29 13:32:44');
INSERT INTO tblprint VALUES ('LORISA, MARIA', '2009-07-29 13:33:44');
INSERT INTO tblprint VALUES ('LORISA, MARIA', '2018-07-29 13:35:44');
INSERT INTO tblprint VALUES ('LORISA, MARIA', '2021-07-29 13:37:44');
INSERT INTO tblprint VALUES ('LORISA, MARIA', '2020-07-29 13:36:44');


Transaction Log Truncation with Copy Only

I'll be the first to admit the documentation is not very clear on this. They state that you should be offloading your backups to the secondaries, but while most statements are made in a general sense, I think they really mean, specifically, log backups (and copy_only backups, if you have some need for those).

You should occasionally run full backups on the primary IMHO. The copy_only restriction is about full backups, not log backups, and only applies to the secondary AFAIK.

You will not be able to use shrinkfile against your current transaction logs because they are full of activity that has technically not been backed up. Once you take a full (non-copy_only) backup on the primary, then let one log backup run, you should be able to shrink the log file manually. Currently your log backups are working because the database is set to full, but they are continuing to grow because (presumably) you've never run a proper full backup on the primary.

This should be a one-time operation, and you shouldn't shrink them too small; you need to set your primary to be backing up regularly, and you need to accommodate for the largest set of activity that will occur between full or log backups. I won't go on and on about how shrinking files only so they can grow again is a futile exercise and guarantees poor performance, but I could. :-)


Would querying from a partitioned table be faster than from multiple tables with the same table definition?

Run the same queries twice.  Did they run faster the second time?  Guess what.  The first time the data was sitting on disk and had to be pulled from disk.  The second time, it was all sitting in RAM.  The difference might be 10-fold.

Why would you ever fetch 5M rows at once?  And, if you split 50M rows into 10 'partitions', why would that 5M be relevant?  Might you need a different slice of the data?

Partitioning, either manually, or with PARTITION, rarely provide any performance benefit for real tasks.

If you often need to fetch 10M rows, let's discuss ways to avoid fetching that many rows, yet still get the same answer.  Using Summary Tables is a common technique in Data Warehousing.

Provide more details, and I will explain in more detail why I am throwing cold water on your Question.


The word option is a reserved word :

You need to surround the word option with back quotes.

LOAD DATA LOCAL INFILE '/tmp/phpnm6Zek'
IGNORE INTO TABLE loaddata_temp
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES (@sku, @model, @option, @option_value, @required,
@quantity, @subtract, @price, @points, @weight) SET sku=@sku, model=@model,
`option`=@option, option_value=@option_value, required=@required, quantity=@quantity,
subtract=@subtract, price=@price, points=@points, weight=@weight

Give it a Try !!!


The default port used for dbcontrol is 1158. You may have changed the port though, of course.

The emctl control commands also display the 'about' URL which includes the port number. For example:

$ emctl status dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0
Copyright (c) 1996, 2011 Oracle Corporation.  All rights reserved.
https://myhost.mydomain.com:1158/em/console/aboutApplication
...


Can Fill Factor be changed on specific tables without index rebuild?

This is not how FILLFACTOR works. It only applies to pages written during a CREATE or REBUILD operations. Pages are always filled as much as possible as they are created or split.

As per the MSDN documentation for CREATE INDEX:

  The FILLFACTOR setting applies only when the index is created or rebuilt.

So, even if you change the FILLFACTOR during a REBUILD (since your Clustered Index already exists), only the existing pages will make use of that value. As new pages are created, and existing pages are split, they will be filled as if the FILLFACTOR were set to 100.

Or, stated another way: the rebuild operation is not your problem here as you will never get new and split pages to use a FILLFACTOR below 100.

Also: No, it is not possible to "pre-define" a change to the FILLFACTOR value that will take affect the next time there is a REBUILD. It can only be specified in the WITH clause of a REBUILD operation. BUT, once you have applied the new value during a rebuild, that new value will be stored in sys.indexes and will be used again for subsequent rebuilds unless it is changed again by someone specifying WITH (FILLFACTOR = x).

Or, again stated another way:

The FILLFACTOR value can be changed only when actually doing a REBUILD (I am not considering a DROP and re-CREATE of an Index to be a "change").
The FILLFACTOR value is used only by CREATE INDEX and ALTER INDEX ... REBUILD (and technically also DBCC DBREINDEX, but that shouldn't be used as it has been deprecated as of SQL Server 2005); FILLFACTOR is not used as rows are inserted or updated as that would defeat the purpose of having this option in the first place.

FILLFACTOR reserves space on existing data pages so that the space can be used (hence reducing the chances of getting page splits); not allowing pages to fill beyond the FILLFACTOR value would permanently reserve that space for no purpose, effectively reducing the size of the data pages, which would actually increase, not decrease, the chances of getting page splits.

Page split operations always move approximately 50% to the new page.


Take a look here (from one of the founders of stackexchange). Be ABSOLUTELY sure to follow the five links therein to K. Scott Allen's series on "the philosophy and practice of database version control" - in the top 1-2% of the best technical writing that I have ever read.

There are many tools (see here and here) for this (pay and Open Source) - the one that seems to get the most coverage around here is liquibase (disclaimer - haven't used any - my DB versioning involved using v. old Unix VCS software.).


Performance differences between executing via Linked Server vs. SSMS

The difference between SSMS and .Net client performance could well be down to how plans are cached. Part of the cache key is the session options - for me, at least, SSMS sets ARITHABORT, whereas .Net SqlClient does not, by default.  You sound like you're ending up in the situation where a bad plan is cached vs. one cache key, and a good plan vs. another.


Create SP for inserting data from a table to a table

Before you start thinking about a solution, try to evaluate if your requirements make sense.

Is it really the best solution to create duplicate rows if multiple phone numbers exist or would it be better to create a new table PhoneNumbers with a foreign key?

And splitting data can be done more elegantly in the tool that loads the data into the database. If you have to do it in SQL, the solution depends on the database system you use.
For instance, here is a solution for SQL Server: https://stackoverflow.com/q/5493510/1595345


How to get "mysqlbinlog" utility for Ubuntu?

mysqlbinlog utility is in mysql server package. You should try install mysql server.


Getting two values for a colums which is not PK

SELECT c_number
FROM CLAIM
GROUP BY c_number
HAVING COUNT(DISTINCT c_closed) > 1;

As we have to retrieve the c_number where the rows have more than one unique c_closed value for every c_number , we have to do the following things:
 1. Group the rows by c_number , so we will get rows for every unique c_number.
 2. Within every group (each unique value of c_number causes a new group, 10 unique c_number = 10 groups) use HAVING CLAUSE to filter out groups where each unique c_number has only one unique c_closed value.

To count only distinct c_closed values you have to use DISTINCT.
HAVING CLAUSE it is like WHERE CLAUSE but it is used after aggregation when all rows with the same values of chosen columns collapse into one row.


SQL Server 2016 Express restore size limit

SQL Server leaves out empty space when it backs up a database. If you want to know the size of the actual data and log files inside the backup file, you need to view the header of the file.

From inside Management Studio, run the following query (you should fill in the correct value to the path of the backup file):

RESTORE FILELISTONLY FROM DISK = 'C:\path\to\backup.bak';

The results will show one or more data files, and one transaction log file.

Add up the values in the Size column to find out exactly how big the database is. The total cannot exceed 10GB.


(Mysql) Using Memory engine for 4.3 million row table?

ENGINE=MEMORY is not designed for big tables.  InnoDB is well designed for virtually all use cases; switch to it.

It sounds like you need more than an ENGINE and index type change.  Please provide SHOW CREATE TABLE for the relevant tables, and the statement that is so slow.

set a.id=b.id?? -- By convention, DBAs call the PRIMARY KEY id.  It is very strange to be setting the PK of one table to the PK of another.

Chunking

One approach to UPDATEing 4M rows will less impact is to "chunk" up the problem.  I discuss details here.


The data of a MV is stored in a regular table, there is no magic to that. But access is typically (much) faster for multiple possible reasons:

multiple tables joined already
smaller row size with only relevant columns for common queries
pre-computed values
pre-selected rows, possibly in expensive ways
possibly much less bloat (fewer dead tuples)
multiple of the above items result in potentially much smaller table size as compared to underlying tables
rows physically sorted favorably (clustered), so that queries only have to read few data pages
size of indexes can be much smaller accordingly
some kinds of indexes only become possibly this way, like a multicolumn index on columns from multiple underlying tables

In short: most expensive work of sophisticated queries on underlying tables is already done, which allows potentially much faster access.


Configure wallet location in sqlnet.ora and listener.ora

You can change the $ORACLE_UNQNAME to $ORACLE_SID. Assuming each Container DB has a unique SID, you can start the CDB with the right wallet as ORACLE_SID is required to start the database anyway.


Improving query performance on mysql star catalog

Instead of SELECT *, try selecting the fields you need (unless you need all of them!): SELECT id, magG, ... FROM
Try to have the fields you are selecting in the used index. This way you will have a covering index, so the data will be read once only from the index
If you need many fields to be returned, you may first select the IDs using the query you already have, then send another query like this: SELECT * FROM Catalog WHERE id IN (list of IDs from first query) [In total, this may not be much faster, but try it to see if the difference is significant]


How connect by is really working? How is it multiplying rows?

If you look at the standard SQL way of constructing a recursive query, you would see something like this:

with t (num) as (
  select rownum-1 num from dual
  union all
  select num+1 from t where num+1 < 10
) select * from t;

SQLFiddle: http://sqlfiddle.com/#!4/9eecb/4371/0

As you can see, it starts with selecting one row from the DUAL table, then recursively applying a UNION ALL to that single row while the limiting condition remains true. The CONNECT BY construct is just hiding this SQL plumbing from you.

If you click on the Execution Plan link in the fiddle above, you'll see that.


If you only need the SELECT statements for each server

SELECT 'SELECT CONVERT(NVARCHAR(MAX),column_name) from ' + QUOTENAME(name) '.db_name.dbo.table_name;' FROM sys.servers where server_id != 0

This gives me the statements for my two linked servers

SELECT CONVERT(NVARCHAR(MAX),column_name) from [Server1].db_name.dbo.table_name;

SELECT CONVERT(NVARCHAR(MAX),column_name) from [Server2].db_name.dbo.table_name;

If you need to execute the statements at once, you could use a cursor and dynamic SQL.

DECLARE @server varchar(50)
DECLARE @SQL NVARCHAR(MAX)
DECLARE C CURSOR LOCAL FAST_FORWARD FOR SELECT name FROM sys.servers where server_id != 0
OPEN C
FETCH NEXT FROM C INTO @server

WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL =
'
SELECT CONVERT(NVARCHAR(MAX),column_name) from '+QUOTENAME(@server)+'.db_name.dbo.table_name;'
--PRINT(@SQL)
EXEC(@SQL)
FETCH NEXT FROM C INTO @server
END
CLOSE C
DEALLOCATE C


How do I uninstall the old version of Postgres on CentOS 7?

The newer version of Postgres will be in /usr/pgsql-9.6 (or a similar location) so you can simply use the alternatives command to make this one the default.


Before adding new datacenter in production environment, the first step should be to prevent the client from connecting to the new datacenter and to ensure reads or writes does not query the new datacenter unless it is completely ready to serve since it may result into latency and timeout errors.

These are few point should follow while adding a new datacenter.

Make sure to change QUORUM consistency level to LOCAL_QUORUM and ONE to LOCAL_ONE so your driver wont hit new data center until it is not ready to serve.
you have to Alter Keyspaces to use network topology if not using (Do not mention new datacenter in Alter Keyspace command at this point).
if using PropertyFileSnitch, cassandra-topology.properties file should be updated with new node's IPs on all the existing nodes.

After adding datacenter successfully.
1.alter Keyspace to set replication factor in new datacenter and mention the number of replica as well.


Remote Scan when updating using functions

Am I missing anything here?

Not really.  You are discovering that distributed query processing has lots of tricky limitations like this.
So the rule of thumb for using Linked Servers is to always use passthrough queries if you want remote execution.
exec (
'
UPDATE database1.dbo.table1 WITH(ROWLOCK)
SET number = ISNULL(number,0)
WHERE accounts = ''123''
'
) at LINKEDSERVER1


Selecting rows that might contain null values based on column comparison

You can also try a solution based on window MAX():

WITH
  FirstMatches AS
  (
    SELECT
      *,
      MAX(CASE WHEN DateA = DateB AND DateB BETWEEN '2011-01' AND '2011-12' THEN DateA END)
        OVER (PARTITION BY Idkey) AS FirstDateA
    FROM
      table1
  )
SELECT
  Idkey,
  DateA,
  DateB,
  FieldA,
  FieldB
FROM
  FirstMatches
WHERE
  DateA <= FirstDateA
;

This solution assumes that the order of rows implied by "starting from" is DateA DESC, same as ypercubeᵀᴹ's solution.

Note that while the query will be looking for rows with matching dates specifically among those where DateB is in the year 2011, the output may include rows with DateA dates earlier than 2011. If you only meant to get rows where DateA is in 2011, you can specify that as a WHERE filter in the CTE, also removing the range filter for DateB in the CASE expression as no longer necessary:

WITH
  FirstMatches AS
  (
    SELECT
      *,
      MAX(CASE WHEN DateA = DateB THEN DateA END)
        OVER (PARTITION BY Idkey) AS FirstDateA
    FROM
      table1
    WHERE
      DateA BETWEEN '2011-01' AND '2011-12'
  )
SELECT
  Idkey,
  DateA,
  DateB,
  FieldA,
  FieldB
FROM
  FirstMatches
WHERE
  DateA <= FirstDateA
;


Transactions, references and how to enforce double entry bookkeeping? (PG)

First, this is exactly the question I had in mind when I asked Modelling constraints on subset aggregates? which is certainly the place to start.  That question is more general though than this and so my answer here will have a bit more information regarding practical approaches.

You probably do not want to do this declaratively in PostgreSQL.  The only possible declarative solutions either break 1NF or are extremely complicated and so this means doing it imperatively.

In LedgerSMB we expect to do this enforcement in two stages (both of which are strict).

All journal entries will come in via stored procedures.  These stored procedures will accept a list of line items as an array and check that the sum is equal to 0.  Our model in the db is that we have a single amount column with negative numbers being debits and positive numbers being credits (if I was starting over, I would have positive numbers as debits and negative numbers as credits because this is just a little more natural but the reasons here are obscure).  Debits and credits are merged on storage and separated on retrieval by the presentation layer.  This makes running totals a lot easier.
We will use a deferred constraint trigger which will check on commit based on the system fields on the table.  This means that the lines entered in a given transaction must balance, but we can do this beyond the lines themselves.


As has been mentioned, the database is perhaps not the best place to accomplish this; however, it can be done, using a stored function.  You wouldn't want to rely on this in a live environment, bit it works.

If the following seems a bit primitive, that's an indication of the somewhat poor match between tool and task.

DELIMITER //

DROP FUNCTION IF EXISTS `dedup_csv` //
CREATE FUNCTION `dedup_csv` (in_str LONGTEXT) RETURNS LONGTEXT
DETERMINISTIC
NO SQL
BEGIN

-- http://dba.stackexchange.com/questions/87144/remove-duplicate-terms-from-column
-- given a comma-separated string of values, return a comma-separated string of
-- unique values found in the list

DECLARE out_str LONGTEXT DEFAULT NULL; -- pending output
DECLARE next_str TEXT DEFAULT NULL;    -- next element under consideration

dedup:
LOOP

  IF CHAR_LENGTH(TRIM(in_str)) = 0 OR in_str IS NULL THEN
    LEAVE dedup; -- no more data to consider
  END IF;

  SET next_str = SUBSTRING_INDEX(in_str,',',1);                   -- find the next element
  SET in_str = SUBSTRING(in_str FROM (CHAR_LENGTH(next_str) + 1 + 1)); -- remove that element

  SET in_str = TRIM(in_str), next_str = TRIM(next_str); -- trim the new and the rest

  IF FIND_IN_SET(next_str,out_str) OR CHAR_LENGTH(next_str) = 0 THEN -- if empty or already found
    ITERATE dedup;
  END IF;

  SET out_str = CONCAT_WS(',',out_str,next_str); -- append the new to pending output

END LOOP;

RETURN out_str;

END //

DELIMITER ;

See this live demo: http://sqlfiddle.com/#!9/f32ee/1

The output from this function, given the input above:

Lakshmi Bar & Restaurant,Dining & Leisure,Restaurants,Restaurants- Multi Cuisine,Hotel,
East#West,Malad East,Dafftary Road,Mumbai,Maharashtra,Features,Cuisines,Services,Meal For Two,
Last Order/Booking (P.M),BookUrTableId

Leading and trailing spaces and empty values are cleaned up automatically.

mysql> SELECT dedup_csv('foo,bar,,,   ,baz,foo,  foo  , bar') AS deduped;
+-------------+
| deduped     |
+-------------+
| foo,bar,baz |
+-------------+
1 row in set (0.00 sec)

Again, the database not being the ideal tool is reflected in the benchmarks, which are highly dependent on input length and number of elements.

On the short and simple string shown above, the actual runtime on MySQL 5.5 on a 1 GHz Opteron 1218 is ~ 150 µsec, while the long string in the original question requires about 2400 µsec to process... which works out to a fairly substantial amount of time for large data sets.


First, are you using "database" in the Oracle sense of the term?  Or are you using it in the sense that other database vendors (such as SQL Server or MySQL) use the term?

If you are using "database" in the Oracle sense, that would be the size of the SYSTEM and SYSAUX tablespaces at a minimum and would possibly include the size of the UNDO and TEMP tablespaces.  On a small laptop system, that's probably on the order of 2 - 3 GB but it could be much bigger.

If you are using "database" in the sense that other database vendors use it, that's what Oracle calls a schema.  An empty schema (a schema with no objects), by definition, consumes 0 bytes of storage.  That's because space is allocated to tablespaces which are independent of schemas-- a single schema can have objects in many different tablespaces, a single tablespace can have objects owned by many different schemas, and a single object (if you're using partitioning) can be spread across multiple tablespaces.


ORA-02070: database does not support antijoin in this context

Got a workaround from Oracle:

/*+ opt_param('_simple_view_merging', 'false') */

or

alter session set "_simple_view_merging"=false;

So the following query works:

SELECT COUNT(patientId) n FROM (
SELECT /*+ opt_param('_simple_view_merging', 'false') */ DISTINCT pat.patientId, pat.bval1, pat.bval2
FROM pat
 LEFT OUTER JOIN
 (SELECT /*+ CARDINALITY(tab 10000) */ setmemberid FROM tab WHERE uuid = '7C06216F-EFC8-418A-A6D9-77C7F6FE7AB1') fsq1
 ON pat.pcpid = fsq1.setmemberid
WHERE fsq1.setmemberid IS NULL
) pat
GROUP BY GROUPING SETS ((pat.bval1), (pat.bval2), ())
;


Filling two tables with a Foreign Key Relationship

CREATE TABLE Customers
(
CostumerID INT IDENTITY (1,1) PRIMARY KEY,
Info1 NVARCHAR(50),
    Info2 NVARCHAR(50),
    Info3 NVARCHAR(50)
)
GO
CREATE TABLE FinInfo    (
 FinInfoID INT IDENTITY (1,1) PRIMARY KEY,
 CustomerID INT,
 Date date,
 Info1 INT,
 Info2 INT,
 CONSTRAINT FK_Customers FOREIGN KEY(CustomerID)
 REFERENCES Customers(CostumerID)
 ON DELETE CASCADE
 ON UPDATE CASCADE
 )
 GO

DECLARE @CustomerID INT
 INSERT INTO dbo.Customers
         ( Info1, Info2, Info3 )
 VALUES  ( N'info1', -- Info1 - nvarchar
           N'info2', -- Info2 - nvarchar
           N'info3'  -- Info3 - nvarchar
           )
SELECT @CustomerID = SCOPE_IDENTITY()
INSERT INTO dbo.FinInfo
        ( CustomerID, Date, Info1, Info2 )
VALUES  ( @CustomerID, -- CustomerID - int
          GETDATE(), -- Date - date
          7, -- Info1 - int
          8  -- Info2 - int
          )
GO


MariaDB 'You do not have privileges to manipulate with the users!'

Can you login with a command line client? (just to have a more clear picture of what is happening)
If yes :
   What does "select user, host, db, select_priv from db;" tell you?

Are you able to start the database with the --skip-grant-tables option?
If yes :
   Now have a look at mysql.db and mysql.user to check permissions.

-- EDIT:
Just wondering: Can you do something like :
grant ALL on . to ''@'';
grant ALL on . to ''@'localhost';

I have the feeling your user lacks the appropriate permissions.
(If the above works you would probably be able to do more with 'your_user' now.


You just need to add an MDX where clause. Look at the Basic MDX syntax.

In that where you need to specify the SET to slice on. Have a look at Using Sets in MDX Queries

SELECT
NON EMPTY { [Measures].[Order Ref Product Count] } ON COLUMNS,
NON EMPTY { ([Product].[Product ID].[Product ID].ALLMEMBERS ) } ON ROWS
FROM [Sales]
WHERE {[Product].[Product ID].[3],[Product].[Product ID].[4]}

In my personal opinion it's no use trying to relate MDX queries to SQL queries. Both are an entirely different beast.

You need to stop thinking in tables and joins, but in tuples and sets and members and measures.

One of the best resources in my opinion to get started is the Stairway to MDX series.


WHERE Clause with pattern matching to find rows with any characters NOT in a list

This will work as well:

SELECT *
FROM PATTERN_TEST
WHERE STRING LIKE '%[^a-zA-Z0-9_ .-]%' ;

Tested at rextester.com

The only difference is that dash (-) is put at the end of the [...] pattern. It has to do with the special meaning of the dash character (A-Z is interpreted as "any character from A to Z").

In your pattern the last three characters (space-dash-dot: -.) are interpreted as "any character from space () to dot (.)", and the results are not the wanted/expected.

You could also escape the dash:

WHERE STRING LIKE '%[^a-zA-Z0-9_ $-.]%' ESCAPE '$' ;

Note though that using ESCAPE may adversely affect cardinality estimation.

See the MSDN documentation for some details: LIKE:

  If there is no character after an escape character in the LIKE pattern, the pattern is not valid and the LIKE returns FALSE. If the character after an escape character is not a wildcard character, the escape character is discarded and the character following the escape is treated as a regular character in the pattern. This includes the percent sign (%), underscore (_), and left bracket ([) wildcard characters when they are enclosed in double brackets ([ ]). Also, within the double bracket characters ([ ]), escape characters can be used and the caret (^), hyphen (-), and right bracket (]) can be escaped.

To be clear: special characters need escaping when they are used as non-special. Inside [], the three (^, -, ]) are special. Putting the dash at the end is more like a hack.


Managing SQL Server MDF and LDF file size

If you just acquired an instance of SQL Server from someone, I would first ask have you gone over the instance configuration and all to understand how the previous person had it configured?

I would suggest taking a look at Brent Ozar's sp_BLITZ script. It is a stored procedure that will collect some good information for you on your instance configuration and give you a good overview of what it sees. I would read over Brent's blog on this script in detail before ever running this on your server, FULLY understand what it is doing.

Regarding your database, are you running out of disk storage or database storage? How much free space does the database contain? I would first understand the architecture of the files before I start making changes. A good article to read on file management is from SQLSkills.com here.

Just my $0.02...


I assume you are trying to set isactive in the department table to the same value currently in projects, for all rows involving the specified duid.

UPDATE d
  SET isactive = p.isactive
FROM dbo.GEMS_DEPARTMENT AS d
INNER JOIN dbo.GEMS_PROJECTS AS p
ON RTRIM(d.du_proj_id) = p.du_id
WHERE p.du_id = @duid;

The most common answer for this problem is found here.


Find current users DDL statement running, Oracle 11g

You really need the statements? The drop / truncate commands don't have that many options. You can get this info from these functions in the trigger (that I think is enough for what you need):

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_type
ora_dict_obj_name
ora_dict_obj_owner

Ps: if you want to capture the statements, I would recommend you choose AUDIT feature available in the database, instead of using triggers.


Use the pg_catalogs.pg_cursors view.

postgres=# BEGIN;
BEGIN
postgres=# DECLARE "db" CURSOR FOR SELECT * FROM book_lines;
DECLARE CURSOR
postgres=# SELECT * FROM pg_cursors ;
 name |                     statement                     | is_holdable | is_binary | is_scrollable |         creation_time
------+---------------------------------------------------+-------------+-----------+---------------+-------------------------------
 db   | DECLARE "db" CURSOR FOR SELECT * FROM book_lines; | f           | f         | t             | 2016-03-05 21:17:01.565456+08
(1 row)

Note that it shows the whole statement defining the cursor, not just the query that underlies the cursor. I don't know of any way to get just the cursor's query, so you'd probably have to do text processing to get that.


DB schema for storing the process tree of linux

The design should be according to your requirements. If your requirement includes to store only some columnar data that are mostly required (mostly not null) then use tables with relations (foreign keys). If you just need some logging with situational tree structure data then use JSONB.
For example, suppose that you store io_counters when counters are higher than some point, then use JSON.
I presuppose you use minimum postgresql 9-4.


Numbering rows consecutively for a number of tables

If I understand correctly, you want to restart the numbering with 0 for every table.
Use the window function row_number() in an UPDATE:

UPDATE tbl t
SET    xid = n.xid
FROM  (SELECT ctid, row_number() OVER (ORDER BY aid, bid, cid) - 1 AS xid FROM tbl) n
WHERE  t.ctid = n.ctid;

Using ctid as poor man's surrogate for a primary key, since you neglected to disclose your table definition.

SQL Fiddle.
db<>fiddle here


What prerequisites package is needed for Oracle 11g + 12c on the same OL7 server?

The problem with oracle-database-server-12cR2-preinstall is that it does not install gcc. For a base release, it is fine, but upon installing a PSU/RU, opatch fails because of that.

[root@o61 ~]# yum deplist oracle-rdbms-server-11gR2-preinstall-1.0-14.el6.x86_64 | grep dependency | grep gcc
  dependency: gcc-c++
  dependency: gcc
  dependency: libgcc
[root@o61 ~]#

[root@o71 ~]# yum deplist oracle-database-server-12cR2-preinstall.x86_64 | grep dependency | grep gcc
  dependency: libgcc
[root@o71 ~]#

It is not needed to install both packages. I would just do this (as I usually do):

yum install oracle-database-server-12cR2-preinstall gcc gcc-c++ -y


My first suggestion would be to use intelligent re-indexing rather than just blindly re-indexing everything. This will help to minimise the impact on the logs un-necessarily. Ola Hallengren's offers one such solution.  See http://ola.hallengren.com/

External storage on a SAN that uses either cheap discs (SATA), or dedupe technology, makes it more affordable than expensive SAN discs.

Increasing the frequency of the log backups,and the copy and restore operations, makes sense to me. Ensure the maintenance is out of hours so that any learning curve about capacity minimises any pain.


Can multiple instances of procedure create same #Temp simultaneously?

"I cannot run multiple instance of this procedure simultaneously. Why?"

Could you clarify what you mean by "cannot run"? Do you get an error?
Now, you don't need to check the existence of the table in your stored procedure with the IF OBJECT_ID() thing.
SQL Server will create an instance of the temp table per instance of the stored procedure, and it will only be visible to the creating scope, which is the stored procedure in this case, and to inner scopes, such as if you call another sub-procedure in your code. Once the procedure completes, the temporary table will be gone.
Under the covers, the query processor appends some unique string to the table name in TempDB for each instance.
BOL has all the details: CREATE TABLE (Transact-SQL)
-- In response to OP comment regarding the stored procedure generating the same suffix. --
You are not giving SQL Server enough credit here. If your claim was true, this would have been a major bug; executing multiple instances of a stored procedure which use temp tables is completely supported.
I suspect that you are testing it on a single connection, and seeing that upon re-execution of the same proc, you get the same suffix. This is an optimization for reusing the suffixes for repeated executions from the same session.
What you should test is:
CREATE PROCEDURE P
AS
BEGIN
CREATE TABLE #T(Col1 INT);
WAITFOR DELAY '00:00:10';
SELECT * FROM TempDB.sys.tables;
END

And then on two or more connection, execute concurrently
EXEC P

Here is what comes up for me:

name

#T_____________000000000004

#T_____________000000000005

HTH


Move data from mysql 4.0 to 5.6 without corrupting encoding

Here is how I did it. First, I launched old mysqld and issued this command:

mysqldump -u root -p --tab=tmp --default-character-set=latin1 database tablename

Then, I started new mysqld and created table structure using phpmyadmin. I selected latin5_turkish_ci as collation for database and table rows.

I also copied resulting tablename.txt to new mysql data directory. And I imported the file like this:

mysqlimport -u root -p --default-chracter-set=latin5 database tablename.txt

This fixed issues I was having.


Migrate from Standalone Instance to Failover Node Instance

This is not really advisable. Install and validate your WSFC then install and test the clustered SQL Server instance on each WSFC node. SQL Server can be really picky about installing to additional nodes of a WSFC cluster and you could easily find it failing leaving you with a one node cluster.
Also storage has a level of indirection for a cluster with ownership of a disk(s) that contains the datafiles for an instance being passed around as necessary.


PostgreSQL data directory/files portability

The best option is probably to install PostgreSQL 9.1 from http://apt.postgresql.org/ if it's available for your distro/version.

If not, you can install a temporary copy of your old Ubuntu version in a VM for recovery purposes - use virtualbox, kvm, etc. Vagrant can make life easier, just grab a prebuilt Ubuntu box from https://vagrantcloud.com/search?utf8=%E2%9C%93&sort=&provider=&q=ubuntu , bring it up, restore your DB, dump it, and shut the box down again.

A PostgreSQL data directory from Linux is not compatible with PostgreSQL on Windows. So you cannot use a 9.1 install on Windows to restore your data from Linux.

Once you've recovered, set up regular backups.


Why does a plan with FULL optimization show simple parameterization?

Simple Parameterization is attempted when a trivial plan is found. The parameterization attempt may be considered safe or unsafe.

The key point is that a trivial plan is found and considered safe. If the cost of the trivial plan exceeds the cost threshold for parallelism, the optimizer will go on to later stages of optimization, where parallel plans may be considered. Whether the final result is a serial or parallel plan, it will be simple parameterized if the safe trivial plan found (but not ultimately used) was parameterized.

In the question example, setting the cost threshold for parallelism higher than the cost of the trivial plan will allow the optimizer to stop at that stage.

Looking at the query plan isn't always enough to figure out if your query has actually been Simple Parameterized.

The safest way is to check some DMVs to verify:

/*Unsafe auto param*/
SELECT *
FROM sys.dm_os_performance_counters AS dopc
WHERE dopc.counter_name LIKE '%Unsafe Auto-Params/sec%';

/*Safe auto param*/
SELECT *
FROM sys.dm_os_performance_counters AS dopc
WHERE dopc.counter_name LIKE '%Safe Auto-Params/sec%';

/*Trivial Plans*/
SELECT *
FROM sys.dm_exec_query_optimizer_info AS deqoi
WHERE deqoi.counter = 'trivial plan';

Additionally, you can also use undocumented trace flag 8607, but not as an OPTION clause hint. Using the OPTION clause prevents a trivial plan.

DBCC TRACEON(8607, 3604);
/*Wait*/

/*Run*/
SELECT u.CreationDate, u.Id
FROM dbo.Users AS u
WHERE u.Reputation = 2;

/*Clean up*/
DBCC TRACEOFF(8607, 3604);

If the plan is considered safe for Simple Parameterization, you'll see a message confirming it here.

********************

** Query marked as Cachable

** Query marked as Safe for Auto-Param


Allow user to drop only tables / views created by that user in MySQL

I'd create a dedicated MySQL instance per user (with docker for example) and let them do whatever they want. Your situation maybe different, of course, I'm just sharing an idea.


Why do you need to shrink the file? Rebuilding the CI after a massive delete (without shrinking the file) will help make those pages available for future data. Shrinking the file just means it has to grow again when you add more data. So there was no benefit to shrinking it in the meantime - ooh, you freed up some space, but only temporarily.

Another thing you should look at is table partitioning (depending on SQL Server edition). It can make disposing of old data much, much easier.


How do I recover data from the postgres data directory on my hard drive?

If you need a method to restore the database but do not have an Intel/AMD-based system available, then a virtual machine on Amazon, DigitalOcean, or elsewhere may give you what you need. If you’ve never had an Amazon Web Services account, you can even do all of this for “free”.
The process might work like this:

Sign up for an account with a VPS provider
Configure it with an operating system of your choice. For the sake of this example, I’ll use Ubuntu Server 20.04 and PostgreSQL 12
Install PostgreSQL
sudo apt install postgresql postgresql-contrib

Confirm the installation was successful by connecting to the database:
sudo -i -u postgres
psql

So long as you can connect, then it’s all good. Let’s exit:
\q

Now we need to stop the server:
service postgresql stop

With the basics in place, it’s now time to do some file management.

Delete the files located in the PostgreSQL data directory
sudo rm -Rf /var/lib/postgresql/12/main

Copy the old data files to that location
Set file permissions accordingly:
sudo chown -R postgres:postgres /var/lib/postgresql/12/main
sudo chmod -R u+rwx,g-rwx,o-rwx /var/lib/postgresql/12/main

Restart PostgreSQL
sudo service postgresql start

Get your data back

All in all, this may be about 30~45 minutes of work. Hope it allows you to recover the missing records.

Note: If you choose to create an AWS account and use their free tier options, remember to terminate the instance when you’re done to avoid any unexpected charges one year from now.

Note 2: Because these are virtual machines, if anything goes wrong, simply terminate the machine and start over.


This is because some "system" session are showing "sa" as the username.
I think you can add a
where is_user_process = 1

in your query to exclude those system session and you should no longer see "sa" as a connected user.


How can I copy the content of a cell in the output pane where the latter is detached from the query window in pgAdmin III?

For reasons best known to the designers of PgAdmin3, it's apparently not possible to do this.

The Cut/Copy and Paste options become greyed out when the window is floating. The logic behind this escapes me (wracks brain.... no, no good).

The only thing that I can think of is to use the green write arrow with the floppy and save your data to a file and dig out your data element that way - cumbersome I know. Or, you could just re-dock the window which does work.

If you'd like to file a feature request on the PgAdmin site, I'll gladly file a "me-too" if you provide the link back here.


SQL Server - Ignoring object dependencies to copy a DB (despite some column referenced in a stored procedure no longer existing)

As others have said, you have problems which are goign to require serious re-evaluation of how you do business.  You can keep patching things with virtual duct-tape, or you can come up with a plan to fix the problem.

However, a duct-tape solution for this particular problem would be to check for the existance of those columns first (using information_schema.columns), and then create the appropriate version of the stored procedure (or not at all).  This will continue to create havoc, but it will at least buy you some time.


Alternative to sequence and timestamp: uniquely ordering records in time

I recommend ordering by timestamp. As long as the only records with identical timestamps are inserted in the same transaction, you can use the sequence-based ID as a secondary ordering.


How to verify that a PostgreSQL base + WAL backup has been restored correctly

The answer will contain two sections - first, what's acceptable to see in the logs after the restore, and second a few examples of what is not. The first section should be fairly deterministic, while the second one is basically a random assortment of whatever happened to us that indicated we had a problem.

Acceptable log output

at the start:

2015-07-23 06:51:24 UTC LOG: database system was interrupted; last known up at 2015-07-23 02:10:42 UTC

It's important to see that the restoring PostgreSQL knows when it was last up. I think that's so because that means it's starting from a checkpoint.

xlog min recovery request ... is past current point

Right at the beginning, a few of these can happen:

2015-07-23 06:51:30 UTC WARNING:  xlog min recovery request 1027/B0A28D98 is past current point 1027/2BE36DA8
2015-07-23 06:51:30 UTC CONTEXT:  writing block 0 of relation base/117264/9551898_vm
       xlog redo insert: rel 1663/117264/8310261; tid 68622/40

But according to http://www.postgresql.org/message-id/CAB7nPqTd43hqpuC+M8fo+xkqHv1WtFe_16NUttu1pHcBtZhZmw@mail.gmail.com that is harmless

FATAL: the database system is starting up

Any number of these can happen:

2015-07-23 06:51:24 UTC FATAL:  the database system is starting up

This should actually be harmless because they were in our case the result of automated SELECT 1 ping-like queries that scripts run to check that PostgreSQL is ready.

unexpected pageaddr ... in log file ..., segment ..., offset ...

At the end, there's this:

2015-07-23 06:52:21 UTC LOG:  restored log file "0000000100001027000000B2" from archive
2015-07-23 06:52:21 UTC LOG:  consistent recovery state reached at 1027/B2F8F2F8
sh: 1: cannot open ../../../wal_backup/0000000100001027000000B3: No such file
2015-07-23 06:52:21 UTC LOG:  unexpected pageaddr 1027/AA000000 in log file 4135, segment 179, offset 0
2015-07-23 06:52:21 UTC LOG:  redo done at 1027/B2F8F2F8
2015-07-23 06:52:21 UTC LOG:  last completed transaction was at log time 2015-07-23 02:17:33.842307+00

But according to http://www.postgresql.org/message-id/CAGrpgQ-BbXUNErrAtToYhRyUef9_GdUQz1T3CXbpTMLTnuKANQ@mail.gmail.com that's also harmless

Note that there may be more of the WAL restorations after that point:

2015-07-23 06:52:21 UTC LOG:  restored log file "0000000100001027000000B2" from archive

That would merely mean that you supplied more WAL files via recovery.conf than strictly necessary.

00000002.history: No such file

At the very end of the WAL unroll process there's this:

sh: 1: cannot open ../../../wal_backup/00000002.history: No such file
2015-07-23 06:52:21 UTC LOG:  selected new timeline ID: 2
sh: 1: cannot open ../../../wal_backup/00000001.history: No such file
2015-07-23 06:52:21 UTC LOG:  archive recovery complete

This is apparently/hopefully irrelevant, because that's where the restored database (clone) starts a new life (timeline).

Unacceptable log output

at the start:

2015-07-20 12:38:31 UTC LOG: database system was interrupted while in recovery at log time 2015-07-20 01:41:22 UTC

This is critical - it means that the backup process did not start at the right time - after a pg_start_backup(...) checkpoint - rather that the database was working normally and was at some random point, which means that this restore is more akin to restoring a crashed database.

missing chunk in pg_toast...

This indicates that the restore wasn't right. As a quick fix, we tried the recipe from http://postgresql.nabble.com/select-table-indicate-missing-chunk-number-0-for-toast-value-96635-in-pg-toast-2619-td5682176.html

mydb=# vacuum analyze mytable; -- trigger the error to see the problem toast
ERROR:  missing chunk number 0 for toast value 13044178 in pg_toast_2619
mydb=# reindex table pg_toast.pg_toast_2619;
REINDEX

This could sometimes get the table back in a working state, but it would also sometimes not have that effect. After that we poked at it some more, and thought we found it's just pg_statistic which is disposable:

mydb=# reindex table pg_statistic;
ERROR:  could not create unique index "pg_statistic_relid_att_inh_index"
DETAIL:  Key (starelid, staattnum, stainherit)=(884792, 34, f) is duplicated.
mydb=# delete from pg_statistic;
DELETE 188540
mydb=# reindex table pg_statistic;
REINDEX
mydb=# vacuum analyze mytable;
VACUUM

right sibling's left-link doesn't match

CREATE TABLE "myschema"."mytable" ( ... )
ERROR: right sibling's left-link doesn't match: block 27 links to 21379 instead of expected 21393 in index "pg_depend_reference_index"

We tried to quickly bypass this by doing:

mydb=# set zero_damaged_pages=on;
SET
mydb=# reindex table pg_depend;
REINDEX
mydb=# set zero_damaged_pages=off;
SET

could not read block in file ...

2015-05-12 13:32:53 UTC ERROR:  could not read block 76408 in file "pg_tblspc/4606764/PG_9.1_201105231/117264/4614269": read only 0 of 8192 bytes

This was obviously a bummer. We couldn't quickly hack our way around this:

mydb=# select cl.relfilenode, nsp.nspname as schema_name, cl.relname, cl.relkind from pg_class cl join pg_namespace nsp on cl.relnamespace = nsp.oid where relfilenode = 4614269;
 relfilenode | schema_name | relname | relkind
-------------+-------------+---------+---------
     4614269 | myschema    | mytable | r
(1 row)

mydb=# select pg_relation_filepath('myschema.mytable');
               pg_relation_filepath
---------------------------------------------------
 pg_tblspc/4606764/PG_9.1_201105231/117264/4614269
(1 row)

% sudo ls -lah /var/lib/postgresql/9.1/main/pg_tblspc/4606764/PG_9.1_201105231/117264/4614269
-rw------- 1 postgres postgres 597M May 11 19:22 /var/lib/postgresql/9.1/main/pg_tblspc/4606764/PG_9.1_201105231/117264/4614269

That was a good indicator that too much data was getting "lost".

duplicate key value violates unique constraint "pg_type_typname_nsp_index"

This was another indicator that the restore was broken:

CREATE TABLE "myschema"."mytable" ( ... )
ERROR: duplicate key value violates unique constraint "pg_type_typname_nsp_index" DETAIL: Key (typname, typnamespace)=(mytable_mycolumn_seq, 3780903) already exists.

The quick hack for this was to move the sequence position:

SELECT setval('mytable_id_seq', (SELECT MAX(id) FROM mytable));


MySQL - replicate some tables between transaction and report database

You can replicate Transaction to Reporting without any issues are it's "independent" but Reporting to Transactional may cause issues depending on your foreign-key settings.

You can easily replicate single table with the help of replication filters (in your case replicate-do-table or replicate-wild-do-table).

There are plenty of blogs for replication setup instructions, say this one from Percona.


I recommend keeping the document very simple and short so it is something that is easy to read, easily understood by people that are not experts in database technology and easy to update.

People have far better things to do than read long test plans, however the test plan should be detailed enough to be followed, its a tough process.

Based on this I believe the things that should be included in the test plan are:

Success Criteria for each tier (tier-1, tier-2,tier-3, etc)
Method of Testing for each of the Success Criteria
preferably including links to scripts in source control and instructions on how to execute each test.
List each Server(s) covered in each of your tiers and therefore covered by each success criteria.
Glossary of any Key terms to remove Ambiguity (plain english explanation of Recovery Time objective, what tier-1 / tier-2 means, etc)
Date Last Modified
Create a team calendar event which is used to remind the team to revise the document each 3 / 6 months to verify the document still reflects the environment.
Scope Definition (In-Scope & Out-Of-Scope)

Other things may come to mind, but simplicity I think is key.

The only other thing that pops in my mind is while you may be writing a test plan for the database, don't forget that a live database that can't be reached by applications still does not meet business continuity objectives; though it may satisfy a data protection need. Consider adding some minimal application testing to your plan or explicitly rule it out by mentioning what is In-Scope and what is Out-Of_Scope.


MSSQL Stored procedure becomes slow after few days

I would look at your plan cache to see what's happening there. Could be a bad plan, could be multiple single use plans are being generated? Look closer at these DMVs, sys.dm_exec_cached_plans and sys.dm_exec_query_stats, they will give you more insight. This is a small query that I use:

USE master
GO
SELECT
    t1.objtype AS ObjectType,
    t1.cacheobjtype AS CacheType,
    COUNT_BIG(*) AS TotalPlans,
    SUM(CAST(t1.size_in_bytes AS DECIMAL(18, 2))) / 1024 / 1024 AS TotalSizeInMB,
    AVG(CONVERT(bigint, t1.usecounts)) AS AvgUseCount,
    SUM(CAST((CASE WHEN t1.usecounts = 1 THEN t1.size_in_bytes
    ELSE 0
    END) AS DECIMAL(18, 2))) / 1024 / 1024 AS SingleUseSizeInMB,
    SUM(CASE WHEN t1.usecounts = 1 THEN 1
        ELSE 0
        END) AS CountOfSingleUsePlans
FROM sys.dm_exec_cached_plans t1
GROUP BY
    t1.objtype,
    t1.cacheobjtype
ORDER BY 3 DESC


SQL Server DMV to show if database is used

SQL Server doesn't really track database access in the way you want, at least going backward (you can set up things like server-side trace, extended events, auditing, etc. going forward).

There is one ballpark thing you can use: DMVs that track index usage and procedure/trigger/query stats. For example:

;WITH d AS
(
  SELECT d = database_id, name FROM sys.databases
  WHERE state = 0 AND database_id BETWEEN 5 AND 32766
),
index_usage(d,lsk,lsc,llk,lupd) AS
(
  SELECT database_id, MAX(last_user_seek), MAX(last_user_scan),
    MAX(last_user_lookup), MAX(last_user_update)
  FROM sys.dm_db_index_usage_stats
  WHERE database_id BETWEEN 5 AND 32766
  GROUP BY database_id
),
proc_stats(d,lproc) AS
(
  SELECT database_id, MAX(last_execution_time)
    FROM sys.dm_exec_procedure_stats
    WHERE database_id BETWEEN 5 AND 32766
    GROUP BY database_id
),
trig_stats(d,ltrig) AS
(
  SELECT database_id, MAX(last_execution_time)
    FROM sys.dm_exec_trigger_stats
    WHERE database_id BETWEEN 5 AND 32766
    GROUP BY database_id
),
query_stats(d,lquery) AS
(
  SELECT t.[dbid], MAX(s.last_execution_time)
    FROM sys.dm_exec_query_stats AS s
    CROSS APPLY sys.dm_exec_sql_text(s.plan_handle) AS t
    WHERE t.[dbid] BETWEEN 5 AND 32766
    GROUP BY t.[dbid]
)
SELECT d.name,i.lsk,i.lsc,i.llk,i.lupd,p.lproc,t.ltrig,q.lquery
FROM d LEFT OUTER JOIN index_usage AS i ON d.d = i.d
LEFT OUTER JOIN proc_stats  AS p ON d.d = p.d
LEFT OUTER JOIN trig_stats  AS t ON d.d = t.d
LEFT OUTER JOIN query_stats AS q ON d.d = q.d;

Note that these statistics aren't completely reliable, since you may not have any stored procedures, and the queries found in sys.dm_exec_query_stats may reference more than one database, and may not ever reflect the one you are concerned about.

Also, they are reset when SQL Server is restarted, or a database is detached / attached or restored, or when a database is auto-closed, and can also depend in some cases on the plans still being in the cache (which another database could completely take over within minutes). So if you are looking into the past, unless you know none of these things have happened for an entire business cycle, I wouldn't rely on just these numbers to determine whether a database is used (there could also be automated processes that are making a database look current, even if you don't care that those automated processes will fail when you remove the database).

Another note is that certain index access may not be tracked in the index usage views; for example, in SQL Server 2014, which adds memory-optimized tables, activity against those hash indexes are not captured this way (and the views where you think the activity would be captured, like sys.dm_db_xtp_hash_index_stats, do not include any date/time columns). If you are using SQL Server 2014 and in-memory OLTP ("Hekaton"), you may want to add some research to cover those objects (in case they are the only ones being referenced in a database).

And one more note is that the queries captured by sys.dm_exec_query_stats could be false positive. For example, if your database has filestream/filetable, you will see these queries being run by the system occasionally:

select table_id, item_guid, oplsn_fseqno, oplsn_bOffset, oplsn_slotid
from [database].[sys].[filetable_updates_<some_id>] with (readpast) order by table_id

So, you may want to add additional filtering to the above query to filter those out (as long as the filter doesn't accidentally filter out queries you do care about). This is probably a safe addition to that derived table:

AND t.[text] NOT LIKE N'%oplsn_fseqno%'

In the end, the safest thing to do in a dev environment is to take the databases you're not sure about offline for a week. If nobody complains, back them up, and drop them. If it takes more than a week for someone to notice they're missing, you can always restore them (there or elsewhere).

I've blogged about this a bit as well:

https://sqlblog.org/2008/05/06/when-was-my-database-table-last-accessed


Installing and enabling both master and slave semisynchronous plugins on same server

This will not cause any problems. I have always setup all MySQL 5.5. DB servers for clients with both plugins. In fact, I answered a question back on Aug 05, 2011 where I posted an update on Aug 08, 2011 on how to install both plugins at the same time
Please see Is MySQL Replication Affected by a High-Latency Interconnect?
Here is the excerpt from that update
EXCERPT
The configuration of MySQL 5.5 Semisynchronous Replication is straightforward
Step 1) Add these four(4) lines to /etc/my.cnf
[mysqld]
plugin-dir=/usr/lib64/mysql/plugin
#rpl_semi_sync_master_enabled
#rpl_semi_sync_master_timeout=5000
#rpl_semi_sync_slave_enabled

Step 2) Restart MySQL
service mysql restart

Step 3) Run these commands in the MySQL client
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
INSTALL PLUGIN rpl_semi_sync_slave  SONAME 'semisync_slave.so';

Step 4) Uncomment the three rpm_semi_sync options after the plugin-dir option
[mysqld]
plugin-dir=/usr/lib64/mysql/plugin
rpl_semi_sync_master_enabled
rpl_semi_sync_master_timeout=5000
rpl_semi_sync_slave_enabled

Step 5) Restart MySQL
service mysql restart

All Done !!! Now just setup MySQL Replication as usual.


ORA-01652: unable to extend temp segment by XXX

This isn't about your 120GB bigfile data tablespace at all.
Your TEMP tablespace isn't big enough to handle whatever sorts or temporary result sets are created by the queries that are being run in your database. If it can't autoextend, then it is probably because you have filled up the disk drive and you don't have room for more, or because the TEMP tablespace data file(s) have reached the maximum size allowed by the file system (typically 32GB each).
If you still have space available on the file system holding your TEMP tablespace, then add one or more more files to the TEMP tablespace. If the partition is completely full, then work with your system and/or storage administrator to determine how to enlarge the file system or where else you could place additional data files.


Azure SQL Db Linked Server - Secured Connection Methods?

The main difference between this answer and the previous one offered is the (what I find to be incredibly important) acknowledgement of MitM attacks rather than a blanket statement which overlooks caveats.
For posterity, from Securing your SQL Database in the documentation:

Important
All connections to Azure SQL Database require encryption (SSL/TLS) at all times while data is "in transit" to and from the database. In your application's connection string, you must specify parameters to encrypt the connection and not to trust the server certificate (this is done for you if you copy your connection string out of the Azure portal), otherwise the connection does not verify the identity of the server and is susceptible to "man-in-the-middle" attacks. For the ADO.NET driver, for instance, these connection string parameters  are Encrypt=True and TrustServerCertificate=False. For information about TLS and connectivity, see TLS considerations.

For other ways to encrypt your data, consider:

Cell-level encryption to encrypt specific columns or even cells of data with different encryption keys.

If you need a Hardware Security Module or central management of your encryption key hierarchy, consider using Azure Key Vault with SQL Server in an Azure VM.


Does making a field unique make it indexed?

--EDIT--

My original answer (below) is probably not useful to you at all because it does not address the question of unique constraints. As others have said, these constraints are usually implemented with an implied unique index. In special cases this might not be true (eg disable novalidate for Oracle).

The question could be: Is it possible to enforce uniqueness without an index? Generally speaking the answer is no though in some cases a Clustered Index will mean that the index and the table are the same object.

--END EDIT--

You said "I'd rather have a useless index than have an O(n) insert time.", but in general databases do not have O(n) insert time. There are two cases to consider:

A normal table with or without indexes:

New rows are dumped at the top of the heap. The RDBMS probably only looks at 1 block, so not just O(1) but very small O(1).

If the table has indexes, a pointer to the row will be added to each. This will usually be an O(log(n)) operation.
A table with some sort of clustering going on, eg an Index Organized Table or cluster for Oracle, or a Clustered Index for SQL Server and others:

New rows are inserted into a particular block, which may cause the block to split or overflow, but whatever happens it is still O(log(n)) or better, caused by the b-tree or similar structure used to find the block.


Using OFFSET in a large table by a simple sub-query

In your particular case, I would use LEFT JOIN instead. Why?
In a LEFT JOIN query, you can dictate what keys you want and the order you want to fetch and join, while INNER JOIN lets the MySQL Query Optimizer decide which is best. In some cases, the EXPLAIN plan may look horrible but still give good performance.
Instead of
SELECT *
FROM table
INNER JOIN (
SELECT id
FROM table
LIMIT 10 OFFSET 100000)
AS results USING(id);

flip the query to have the keys first
SELECT B.*
FROM (SELECT id FROM table LIMIT 10 OFFSET 100000) A
LEFT JOIN table B USING (id);

From there, you can control the order by inside in subquery A
SELECT B.*
FROM (SELECT id FROM table LIMIT 10 OFFSET 100000 ORDER BY ...) A
LEFT JOIN table B USING (id);

or after USING (id)
SELECT B.*
FROM (SELECT id FROM table LIMIT 10 OFFSET 100000) A
LEFT JOIN table B USING (id)  ORDER BY ...;

This way, you should be able to navigate (or paginate) through the keys before locating actual data. To show this using empirical evidence, see my StackOverflow post (Fetching a Single Row from Join Table) on using LEFT JOIN with primary keys.
Give it a Try !!!