How to reset slave without over write of master dump in mysql

Nice! Now the case is you have lost your datadir on master and some how you have the master log position!! Care to tell us how?

Anyways... In  such case we will have to reconstruct the master from slave. But because your slave is behind master (not caughtup with binary logs) we need to fix that first.

Backup your slave before working

The slave co-ordinates are mysql-bin.000040 and some-log-pos...

This means that when your datadir was getting "lost" slave was still lagging behind.

Now, if your slave has fetched all the required binlogs (relay logs) from master before master went down then just start sql thread and let it catchup.

On Slave:
Start slave sql_thread;

This will let the slave catch-up with latest available changes from master.

If your binary logs were not yet shipped (via replication) to slave, then you might want to  ship them manually and play it on slave. (Provided they're available - as you say you have co-ordinates)

mysqlbinlog mysql-bin.xxxx > mysql-bin.xxxx.sql

scp all sql files to slave (You may choose to compress them)

cat mysql-bin.xxxx | mysql -uroot -p

After all these slave will have the "latest" data.

Assuming that that's the only thing we have got:
- Restore your master from slave's backup.
- Reconfigure replication from master to slave afresh.

For next time onwards:

Configure backup on slave & binary log backups if PiT restores required.
Have log_slave_updates.
Don't delete datadir on master :P


What Microsoft DBA certificate should I acquire?

The MCSA certifications are being retired very soon.

This certification retires on January 31, 2021. You will no longer be able to earn this certification after this date.

I would not recommend starting to pursue any of them right now.
The "replacement" exam is Microsoft Certified: Azure Database Administrator Associate.
Notice that the exam related to this cert covers on prem and cloud based offerings:

Candidates for this exam are database administrators and data management specialists that manage on-premises and cloud relational databases built on top of Microsoft SQL Server and Microsoft Azure data services.

Acquiring Microsoft certifications won't give you the same benefits as practical experience.  However, they can be useful for exposing you to the breadth of features and offerings available as part of the Microsoft Data Platform.  And once you're aware of these things, you can dig into the ones that are most valuable to you and your current / potential employers.
There are other valid reasons for pursuing certifications as well: for example, if your employer is trying to maintain or upgrade their Microsoft Partner status


Is the everything-is-arithmetic MySQL specific?

Or perhaps booleans do not even exist in SQL and 0 / 1 represents them

boolean do exists in the SQL standard but as "real" booleans.

So valid boolean literals are false or true.

  Is this SQL standard or just MySQL is trigger happy about casting booleans and integers with wild abandon

MySQL does not actually support boolean values. It simply treats anything that can't be converted to a non-zero number as "false".

E.g.

select *
from some_table
where 'xxxx';

is accepted as valid (even though it is invalid SQL) and is equivalent to where false because xxxx can't be converted to a number and thus is assumed to be 0 which is treated as false.

On the other hand

select *
from some_table
where '42xxxx';

is treated as where true because 42xxx will be (implicitly) converted to 42 which in turn is treated as "true".

The query:

SELECT *
WHERE column1 - column2

is invalid standard SQL because it yields a number (assuming column1 and column2 are numbers) and a number is not a boolean expression.

You can see this behavior when doing:

select 1 > 0;

MySQL returns the number 1 whereas a database supporting real booleans (e.g. Postgres) will return true. Oracle does not support real booleans so select 1 > 0 from dual is actually invalid there.


Edit Application Name to Log Connection once for Logons on Pooled Connections

No, the connection properties should be immutable. However, even if you could edit them, there is still an easier (and better) way to correlate Logon Events to the same Connection: the connect_time field in sys.dm_exec_connections. You just look up that field based on the session_id (which correlates to SPID in the XML returned from the EVENTDATA() function). If your audit table does not already have fields for SessionID and ConnectionTime you will need to add one or both of them. And it probably would be wise to create a Non-Clustered Index on (ConnectionTime DESC, SessionID). Finally, in your Logon Trigger after you do the lookup for @ConnectionTime, do an IF NOT EXISTS to check your audit table for that combination of @ConnectionTime and @SessionID and only INSERT if not found.

The following is a basic example of how to capture this info, but does not include the Index or the IF NOT EXISTS logic:

Create the audit Table

--DROP TABLE Test.dbo.LogonEvent;
CREATE TABLE Test.dbo.LogonEvent
(
    LogonEventID INT NOT NULL IDENTITY(-2140000000, 1),
    SessionID INT NOT NULL,
    ConnectionTime DATETIME NOT NULL,
    EventInfo XML NULL
) ON [Tables];
GO

Create the Logon Trigger

ALTER TRIGGER CaptureLogonEvents
ON ALL SERVER
FOR LOGON
AS
SET NOCOUNT ON;

DECLARE @SessionID INT = EVENTDATA().value('(/EVENT_INSTANCE/SPID)[1]', 'INT');

INSERT INTO [Test].[dbo].[LogonEvent] (SessionID, ConnectionTime, EventInfo)
    SELECT  @SessionID AS [SessionID],
            sdec.connect_time AS [ConnectionTime],
            EVENTDATA() AS [EventInfo]
    FROM        sys.dm_exec_connections sdec
    WHERE   sdec.session_id = @SessionID;
GO


Finding matches where GROUP BY COUNT(*) is 1

This query should help you out.

It works with an delivered table named org_type what filters out double org_id.

That delivered table will be matched with where filter org_type.type_id = 2 to make sure that type_id = 2

SELECT
   org_type.type_id
 , org_type.org_id
 , organisation.name
FROM (
 SELECT
     type_id
   , org_id
 FROM
   org_type
 GROUP BY
   org_id
 HAVING
   COUNT(*) = 1
) AS
  org_type
INNER JOIN
  organisation
ON
  org_type.org_id = organisation.id
WHERE
  org_type.type_id = 2


what are the possible reasons for 'Arithmetic overflow error converting expression to data type int' in SQL server

-- casting using DECIMAL(38,2) throws exception, Size needs to be casted as bigint

  select [SizeInMB] = CAST( ((SUM(Size)* 8) / 1024.0) AS DECIMAL(38,2) ) from sys.master_files

-- This fix is sufficient, but cannot limit to 2 decimal digits

  select [SizeInMB1] = ((SUM(CAST(Size AS BIGINT))* 8) / 1024.0)  from sys.master_files

-- 2 decimal digits are not available in fix, as divide by 1024.0 converts to default 6 digits

  select [SizeInMB2] = ((SUM(CAST(Size AS DECIMAL(38,2)))* 8) / 1024.0)  from sys.master_files

-- so 2 casts are required to eliminate Arithmetic overflow exception and limiting 2 decimal digits

  select [SizeInMB3] = CAST( ((SUM(CAST(Size AS BIGINT))* 8) / 1024.0) AS DECIMAL(38,2) ) from sys.master_files


Why is the value 0 for the Max_data_length of my tables in my database

Table size limits are affected by the OS and the file system.

From MySQL (5.6) documentation, Limits on Table Size:

  The effective maximum table size for MySQL databases is usually determined by operating system constraints on file sizes, not by MySQL internal limits. The following table lists some examples of operating system file-size limits. This is only a rough guide and is not intended to be definitive. For the most up-to-date information, be sure to check the documentation specific to your operating system.

Operating System                      File-size Limit
-------------------------------------------------------------
Win32 w/ FAT/FAT32                    2GB/4GB
Win32 w/ NTFS                         2TB (possibly larger)
Linux 2.2-Intel 32-bit                2GB (LFS: 4GB)
Linux 2.4+  (using ext3 file system)  4TB
Solaris 9/10                          16TB
OS X w/ HFS+                          2TB

So there the problem lies.

Solution:

Take a backup.
Get another machine / VM with a 64-bit OS.
Install MySQL.
Restore your database there.
Profit ;)


Yes, 'W' will do that -- replace the file. Use 'A' to append. I don't think you can "modify in place"; you might have to copy the file to a new one making the changes you desire, delete the original and then rename the copy.

All of this begs the question why you're trying to do all of this in PL/SQL. It might be better -- certainly easier -- to do it in an external program written in eg Python and invoke that.


How to change the Write Ahead Log directory

This can be done according to the documentation [here]:(http://www.postgresql.org/docs/9.1/static/wal-internals.html)

  It is advantageous if the log is located on a different disk from the
  main database files. This can be achieved by moving the pg_xlog
  directory to another location (while the server is shut down, of
  course) and creating a symbolic link from the original location in the
  main data directory to the new location.


How to do an incremental refresh of a PostgreSQL Materialized View?

There is no INCREMENTAL, it's very simple.. From the docs

REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] name
    [ WITH [ NO ] DATA ]

Perhaps you're confusing it with CONCURRENTLY which is about concurrency (locking) and not minimizing updates.

  Refresh the materialized view without locking out concurrent selects on the materialized view. Without this option a refresh which affects a lot of rows will tend to use fewer resources and complete more quickly, but could block other connections which are trying to read from the materialized view. This option may be faster in cases where a small number of rows are affected.

If you need an incremental refresh, use a table to store the results and join it against the source inserting only rows that NOT EXISTS,

INSERT INTO materialized_resultset
SELECT foo,bar,baz
FROM src
WHERE NOT EXISTS (
  SELECT 1
  FROM materialized_resultset AS mr
  WHERE src.foo = mr.foo
);

or use the newer ON CONFLICT UPDATE or ON CONFLICT DO NOTHING on INSERT.


Can I do an impdp with a file on my local computer

Should be possible, provided the user that the DB service is running as can access the file share.

If the above is true, then you would have to create the directory object:

create directory MYIMPORTDIR as '\\mycomp\mydir';

then run your impdp like:

impdp [username]/[pass] directory=MYIMPORTDIR dumpfile=expdat_1234.dat


Same Join Constraints on Multiple Columns

This is an interesting example of where an (indirect) SELF-JOINs prove useful!
In order to answer your problem, I did the following (all the SQL below is available on the fiddle here):
I used your DDL and DML as supplied in the question - thanks for that (and a +1 - you've only asked two questions, so I'd consider you a New Contributor and it's great to see that some people take the trouble to supply DDL and DML - if only all OP's did the same!).
CREATE TABLE name_lookup
(
  id INT UNSIGNED AUTO_INCREMENT,
  contact_id INT,
  contact_name VARCHAR(32),

  PRIMARY KEY(id)
);

CREATE TABLE contact_info
(
  id INT UNSIGNED AUTO_INCREMENT,
  pri_contact_id INT,
  sec_contact_id INT,
  blah_blah VARCHAR(32),

  PRIMARY KEY(id)
);

Populate them:
INSERT INTO name_lookup(contact_id, contact_name)
VALUES
(1, "John Doe"), (2, "Mary Smith"),
(3, "Jose Garcia"), (4, "Larry Brown");

and
INSERT INTO contact_info(pri_contact_id, sec_contact_id, blah_blah)
VALUES(1, 3, "Team A"), (4, 2, "Team B"), (1, NULL, "Team A");

Notice the final record which has sec_contact_id = NULL - see the discussion in the EDIT below. I'm implicitly assuming that the team identity is determined by the pri_contact_id - define as necessary.
You'll also notice that I use snake_case or lower_case_with_underscores - personal preference - pick a style and stick to it!.
I've left extra fields in the SQL in the fiddle so that you can see the thought processes involved and how I arrived at a solution!
SELECT
  nl1.id, nl1.contact_id, nl1.contact_name,
  ci1.pri_contact_id, ci1.sec_contact_id, ci1.blah_blah
FROM name_lookup nl1
JOIN contact_info ci1
  ON nl1.contact_id = ci1.pri_contact_id;

Result:
id  contact_id  contact_name    pri_contact_id  sec_contact_id  blah_blah
 1           1      John Doe                 1               3     Team A
 4           4   Larry Brown                 4               2     Team B

So, now we join name_lookup back on itself using contact_info as the link between them.
SELECT
  nl1.id, nl1.contact_id, nl1.contact_name,
  nl2.id, nl2.contact_id, nl2.contact_name,
  ci1.pri_contact_id, ci1.sec_contact_id, ci1.blah_blah
FROM name_lookup nl1
JOIN contact_info ci1
  ON nl1.contact_id = ci1.pri_contact_id
JOIN name_lookup nl2
  ON ci1.sec_contact_id = nl2.contact_id
ORDER BY nl1.id;

Result:
id  contact_id  contact_name    id  contact_id  contact_name    pri_contact_id  sec_contact_id  blah_blah
 1           1      John Doe     3           3   Jose Garcia                 1   3    Team A
 4           4   Larry Brown     2           2    Mary Smith                 4 2      Team B

So, having obtained our result, we can now clean up the SQL (only SELECT required fields - reduce any network traffic as well as I/O on the server) as follows:
SELECT
  nl1.contact_name AS "Con_1 name",
  nl2.contact_name AS "Con_2 name",
  ci1.blah_blah AS "Team"
FROM name_lookup nl1
JOIN contact_info ci1
  ON nl1.contact_id = ci1.pri_contact_id
JOIN name_lookup nl2
  ON ci1.sec_contact_id = nl2.contact_id
ORDER BY nl1.id;

Result:
Con_1 name     Con_2 name     Team
  John Doe     Jose Garcia  Team A
Larry Brown    Mary Smith   Team B

Et voilà - result as desired!
EDIT (NULLs in sec_contact_id):
It was pointed out to me that my answer wasn't as comprehensive as I might have liked it to be. What if sec_contact_id is NULL which, after all, is possible - you might have made the first one but a follow-up hasn't been completed yet?
So, I changed the table slightly and it now contains (as you'll see above - a considerably changed fiddle is available here - I wanted to run it on PostgreSQL also):
 Con_1 name   Con_2 name      Team
   John Doe  Jose Garcia    Team A
   John Doe         NULL    Team B
Larry Brown   Mary Smith    Team B

so, now, you have to use INNER JOINs thus:
  nl1.contact_name AS "Con_1 name",
  nl2.contact_name AS "Con_2 name",
  ci1.blah_blah AS "Team"
FROM contact_info ci1
    LEFT JOIN name_lookup nl1
        ON nl1.contact_id = ci1.pri_contact_id
    LEFT JOIN name_lookup nl2
        ON nl2.contact_id = ci1.sec_contact_id
ORDER BY nl1.id, ci1.blah_blah;

Result:
  Con_1 name      Con_2 name      Team
    John Doe     Jose Garcia    Team A
    John Doe            NULL    Team B
 Larry Brown      Mary Smith    Team B

So, now the record with the sec_contact_id = NULL datum appears in your resultset.
A couple of words of advice:

You really should consider (strongly) consider upgrading from 5.6 to the current version of MySQL 8 - you'll get window functions, generated columns, CHECK constraints - it's at version 22 now, and I haven't heard many complaints, so it'd be a good option!

Many people consider NULLs to be undesirable and avoid them like the plague - I tend to fall into this category. Therefore, you might wish to consider having two contact tables - pri_contact_info and sec_contact_info. You can decide if you wish to do that and ask a new question - if you do so, let me know here also!

You might consider answering this question (a comment below your original post): Can there ever be more than two people on a team? I e. There would be 3 columns with team members names for the same row?!

the PRIMARY KEY of the name_lookup table should be contact_id - the surrogate key id adds nothing to the mix! I assume that contact_id is assigned more or less randomly to employees as they are hired? So, it's essentially a surrogate key itself. Surrogate keys have their place, but sometimes they're not the way to go!

MySQL is by far and away the worst of the major RDBMS's when it comes to standards support, plus it has a plethora of non-standard "extensions" - in future, maybe you could delimit your SQL strings with apostrophes ' and not double quotes "? My personal suggestion is that you use double quotes for aliases of field names for the final result (presentation, not content) as I have done - it will make your SQL more readable and portable!


Process was intentionally killed - now the process is still showing in "show processlist "

The process is undoing its changes after being killed (that is why killing write processes on a long running transaction can be dangerous- it can lead to a long undo process with blocking).

The rule of thumb is that InnoDB's UNDO can take multiple times the time it took to write. If it is unsustainable, you can stop the server when ther is no more write activity and restart it skipping pending UNDOs (so it doesn't start again). This is done with the start option:

innodb_force_recovery = 3

Not ideal as a normal start option, as it could make you lose data. Generating a backup before the unclean restart (or in general) is always a good idea, even if technically safe assuming no other writes or undos are ongoing.


Best way to automatically reset sequence/identity once a year

You could make a SQL Agent Job to use DBCC CHECKIDENT with the RESEED option at the right moment.


Identify records with one identical field, but different timestamp

Sample data:

create table offender
(
  fname varchar2(10),
  lname varchar2 (10),
  ssn number,
  chargeddate date
);

insert into offender values ('A', 'A', 1, date'2015-08-01');
insert into offender values ('B', 'B', 2, date'2015-08-02');
insert into offender values ('C', 'C', 3, date'2015-08-03');
insert into offender values ('D', 'D', 4, date'2015-08-04');
insert into offender values ('E', 'E', 1, date'2015-08-01');
insert into offender values ('F', 'F', 2, date'2015-08-05');
insert into offender values ('G', 'G', 3, date'2015-08-06');
insert into offender values ('H', 'H', 4, date'2015-08-04');
commit;

The query:

select
  fname,
  lname,
  ssn,
  chargeddate,
  decode(count(distinct chargeddate) over (partition by ssn), 1, 0, 1) as repeat_offender_bit
from
  offender;

FNAME      LNAME             SSN CHARGEDDATE         REPEAT_OFFENDER_BIT
---------- ---------- ---------- ------------------- -------------------
A          A                   1 2015-08-01 00:00:00                   0
E          E                   1 2015-08-01 00:00:00                   0
B          B                   2 2015-08-02 00:00:00                   1
F          F                   2 2015-08-05 00:00:00                   1
C          C                   3 2015-08-03 00:00:00                   1
G          G                   3 2015-08-06 00:00:00                   1
H          H                   4 2015-08-04 00:00:00                   0
D          D                   4 2015-08-04 00:00:00                   0


Numerical data with lots of NULLs: 6NF or document database?

JSONB might be a good option. If the TYPES of your flags and sensors are always the same (let's say they are all floats and ints), you could also use another strategy:

CREATE TABLE tb
    (id           serial primary key,
     timetag      timestamp default now(),
     sensor_nr    integer,
     sensor_value float,
     flag_nr      integer,
     flag_value   integer
    ) ;

If you have real values for sensors 2 and 1000, you would then do:

INSERT INTO
    tb
    (sensor_nr, sensor_value)
VALUES
    (2, 123.456),
    (1000, 0.123) ;

Or, if there are flags:

INSERT INTO
    tb
    (sensor_nr, sensor_value, flag_nr, flag_value)
VALUES
    (1000, 123.456, 1000, 0),
    (2, 234.567, 2, 1) ;

The NaN value would be represented by NULL; and the "no reading" would be represented by just the non-existence of the time/sensor_nr row.

If you need frequent range SELECTs of different types, you would index by timetag; and also by sensor_nr and sensor_value... The insert costs would be relatively high, but the SELECTs could be fast.

If the "new unexpected fields that pop at any type" are of different types (i.e.: you have some co-ordinate pairs (float,float) and not just simple floats) this approach won't be flexible enough. In that occasion, probably JSON(B) and the new indices is probably your best alternative; at the cost of losing (some) type safety.


"Percona-XtraDB-Cluster prohibits use of ALTER command on a table () that resides in non-transactional storage engine"

The Cluster can properly handle only InnoDB tables.  It sounds like some of your tables have ENGINE=MyISAM?
Plan A:  Edit the dump file to change to ENGINE=InnoDB for each table.
Plan B:  Remove the offending ALTERs; load the data (assuming the tables successfully convert to InnoDB); then manually run the lost ALTERs.
Plan C:  ALTER TABLE t ENGINE=InnoDB; for each table before dumping.
Each has potential hiccups.  Some are listed here:
http://mysql.rjweb.org/doc.php/myisam2innodb


let me share you my understanding on this.

question 1:****why is MongoDB trying rollback in the first place
When there is a node switch, documents which are not yet written on secondaries needs to be rollback. As M1 was primary and at the time of switching some documents were not sync they must be in your oplog but as your server was restarted in the mean time M2 become primary and rest of replica members starts syncing with new primary. Now your M1 came up and found that his last operation was not written on other members so it needs to be rollback.

Question 2:****where is operation with timestamp of Jul 28 07:45:12:2 is coming from
check this timestamp in oplog.rs you will find your answer as this would be the same document(rollback)


SSL connection to MySQL suddenly not working anymore

Checking the expiration date of the certificates helped a lot :)
Case closed.


Display multiple rows of one table into one row without using PIVOT

declare @t table (Id int, [TYPE] varchar(100), Amount int);
insert into @t values
( 1,  'Deposit',  50),
( 2,  'Withdraw',  10),
( 3,  'Withdraw',  30);

with cte as
(
select *, ROW_NUMBER() over(partition by [TYPE] order by id) n
from @t t
),

nums as
(
select distinct n
from cte
)

select n.n as row, c1.TYPE, c1.Amount, c2.TYPE, c2.Amount
from nums n
     left join cte c1
         on n.n = c1.n and c1.TYPE =  'Deposit'
     left join cte c2
         on n.n = c2.n and c2.TYPE = 'Withdraw';


Intentionally slow down importing of (Percona) MySQL 5.7 dump

Consider writing a script (in your favorite language) to read the dump file one line at a time and send that line to STDOUT, which will be piped into mysql.  And add a tiny sleep in the loop that does that.

To get fancier, you could fetch Seconds_behind_master to tweak the sleep up or down.  (I think at least one of Percona's tools does this.)


SQL query to find all parent records where all child records have a given value (not just some)

I don't have a copy of PostgreSQL in front of me.  Does this compile in your version:

...
HAVING count(*) = (select count(*) from participatns as x where x.event_id = events.id)

An alternative approach would be to remove from this list of all events those which have a participant who's status is not 'present', using the EXCEPT clause:

select
    event.id
from events

EXCEPT

select
    event.id
from event
INNER JOIN participants
ON participants.event_id = events.id
WHERE (participants.status != present)

The EXCEPT syntax is usually more efficiently expressed as NOT EXISTS or as a LEFT JOIN with the child identifer ends up as null.  I'm sure you can re-factor the query to suit your needs.


declare @a table (Key_number int,  Country varchar(100));
insert into @a values
(123,         'Apple'),
(123,         'Orange'),
(456,         'Cherry'),
(456,         'Apricot');

declare @b table (Country varchar(100), Rating int);
insert into @b values
('Apple',   5),
('Orange',  4),
('Cherry',  8),
('Apricot', 2);

select top(1) with ties a.*, b.Rating
from @a a join @b b
        on a.Country = b.Country
order by row_number() over(partition by a.Key_number order by b.Rating);


What is making MySQL stay in strict mode

It turns out that the problem was ending the lines in my.cnf with semicolons. Leave them off, and /etc/my.cnf is read and stops strict mode.


How do you view PostgreSQL messages (such as RAISE NOTICE) in DBeaver?

On DBeaver 5.2.0 you can use Ctrl+Shif+O or the button Show server output console on the left side of the script window.


ORA-32000: write to SPFILE requested but SPFILE is not modifiable

Use the command:

create pfile ='loc' from spfile;

To create a pfile named loc. The edit this file and change the parameter in this pfile. Now create a new spfile from it:

create spfile from pfile ='loc';


Oracle 11gR2 (11.2.0.4) RMAN cold backup | Media recovery

SHUTDOWN

  IMMEDIATE

  Does not wait for current calls to complete or users to disconnect
  from the database.

  Further connects are prohibited. The database is closed and
  dismounted. The instance is shutdown and no instance recovery is
  required on the next database startup.

SHUTDOWN

  ABORT

  Performs an inconsistent shutdown of the target instance, with
  the following consequences:

  All current client SQL statements are
  immediately terminated.

  Uncommitted transactions are not rolled back until next startup.

  All connected users are disconnected.

  Instance recovery is performed on the database at next startup.

  IMMEDIATE

  Performs an immediate, consistent shutdown of the target
  database, with the following consequences:

  Current client SQL statements being processed by the database are allowed to complete.

  Uncommitted transactions are rolled back.

  All connected users are disconnected.

A cold backup is consistent already, requires no further recovery. All you need to do is open the restored database with:

alter database open resetlogs;

You will have your database as it existed when you took the cold backup. If you need a state newer than that, only then you need to recover further the restored database.


Find records with the same relationships

One way to achieve that would be using Group_Concat() to stuff all the products for each order into one line, then match up all of the orders that have the same products and use Group_Concat() again to stuff all of them into the same line. See example:

Select `cte`.`Order` As `Queried order`,
    Group_Concat(`cte2`.`Order` Order By `cte2`.`Order`) As `Returned Orders`
  From (
    Select `Order`,
        Group_Concat(`Product` Order By `Product` Asc) As `Products`
      From `Table1`
      Group By `Order`) As `cte`
  Join (
    Select `Order`,
        Group_Concat(`Product` Order By `Product` Asc) As `Products`
      From `Table1`
      Group By `Order`) As `cte2` On `cte`.`Products` = `cte2`.`Products`
  Group By `cte`.`Order`
;

This may be ugly, but it doesn't require multiple steps, or knowing the number of products up front.

Edit
Should be easier to get the output that you now describe. Just remove the final GROUP BY and GROUP_CONCAT:

Select `cte`.`Order` As `Queried order`,
    `cte2`.`Order` As `Returned Order`
  From (
    Select `Order`,
        Group_Concat(`Product` Order By `Product` Asc) As `Products`
      From `Table1`
      Group By `Order`) As `cte`
  Join (
    Select `Order`,
        Group_Concat(`Product` Order By `Product` Asc) As `Products`
      From `Table1`
      Group By `Order`) As `cte2` On `cte`.`Products` = `cte2`.`Products`
;


I see a tweet as an event happening, so I would model it as a new fact table FactTweet. More specifically as a factless fact.
The dimensions for FactTweet would be DimDate, DimCar (if you can relate a tweet to a car), DimAuthor and I would probably keep URL and Description as degenerated dimensions. You could potentially add the sentiment of the tweet as well (positive, negative or neutral).

DimCar and DimDate would be  conformed dimensions for both FactTweet and FactCarDetail so you would be able to report on both fact in a single query.


What does node eviction mean in the Oracle RAC world?

What is node eviction?

  Word Meaning(Merriam Webster Disctionary): to force(someone) to leave
  the place.

The process of removing the failed(due to various reasons) node from the cluster is known as eviction. Prior to 11gR2 Oracle tries to prevent from split brain situation by quickly rebooting the failed node . After 11gr2 Clusterware will attempt to clean up the failed resources . If the clusterware is able to clean up the failed resources, OHASD will try to restart the CRS stack. Once this task is done all the cluster resources on that node will be started automatically. This is called reboot less fencing(or eviction).
If clusterware can not stop or clean the failed resources then it will roboot the node.

Causes of node eviction

-Missing network heartbeat
-Missing disk heartbeat
-CPU starvation issues
-Hanging cluster processes
-May have more...

  Will that node always be rebooted?

Same applies as above mentioned.

  Can one configure this behavior?

No, Oracle Clusterware will decide on it.

If you want to learn more on it you can google the term 'Rebootless Node Fencing or Eviction' I promise you will have number of options to carry on.


Question About Large InnoDB Table with Deleted Rows

If you are worried about table locks caused by DDL operations, like ALTER TABLE... ENGINE=InnoDB (what optimize does for InnodB) you must know that in 5.6, that process can be done fully online, and alternatively, and for lower versions, you can use an online alter table tool like pt-online-schema-change. With proper care, the process can be done fully online and with minimal load increase (although you will need double the table size). Make sure also you are using innodb_file_per_table.

Assuming the table has been optimized and all rows purged, yes, you can obtain an improvement due to the several reasons:

Assuming you do not have 600GB of RAM, a larger part of your InnoDB pages will fit into the buffer pool, reducing IO requests. You can check that before and after by monitoring your physical IO or checking InnoDB buffer pool hit statistics, such as innodb_buffer_pool_read_requests and innodb_buffer_pool_reads
Having more free space on the buffer pool can also smooth the commit writing process by allowing more dirty pages on memory (this depends highly on the load)
If your queries require full table scans, reducing the total number of rows will make those operations faster
Index scans should also be faster, specially Index accesses and large ranges. Also InnoDB takes a huge impact when the hot parts of the indexes do not fit in memory (its trees are designed to be cached)
Temporary tables, sorts, etc. created could potentially be smaller, reducing the latency of its execution

So the actual improvement may depend a lot on the actual queries executed, usage or not of hashed searches, memory state, penalty of random IO reads, etc. But in general, working with smaller data sets is easier.

Beware also of the purge process, which can bite your performance if not done well.

Some of this advantages could be lower if there is a lot of fragmentation, but that depends a lot on the actual state of the table. You can see some thoughts about optimize on InnoDB here (more on comments):

How often should you use OPTIMIZE TABLE? http://www.xaprb.com/blog/2010/02/07/how-often-should-you-use-optimize-table/
How often should you use OPTIMIZE TABLE? Followup http://code.openark.org/blog/mysql/how-often-should-you-use-optimize-table-followup
Visualizing the impact of ordered vs. random index insertion in InnoDB: http://blog.jcole.us/2014/10/02/visualizing-the-impact-of-ordered-vs-random-index-insertion-in-innodb/


Enforcing maximum number of relations for a record

Think about
// Positions table - 3 spots for `xx-xx-xx` job, only 1 spot is free
position_id | job_id   | user_id
------------+----------+---------
aa-aa-aa    | xx-xx-xx | ee-ee-ee    -- used spot
bb-bb-bb    | xx-xx-xx | ff-ff-ff    -- used spot
cc-cc-cc    | xx-xx-xx | NULL        -- free spot

The amount of spot rows per a job is equal to jobs.num_positions for according job_id value. They're created with according amount when the row is inserted into jobs table or when jobs table is updated and amount value for some job(s) is increased.
While user filling a spot users's id inserts to the user_id column. When user frees a spot its user_id value is updated to NULL back.
All operations are performed only by UPDATE queries. Spot filling query includes WHERE job_id = @job AND user_id IS NULL LIMIT 1.
If two concurrent users try to fill a spot the user which do this earlier will fill whereas the user who tries to do this later will fail because there is no record which matches the conditions WHERE job_id = @job AND user_id IS NULL.


Can Anyone please review my database after I did the Normalization ?.Any suggestion is appreciated

Firstly, I must admit that I have been a little disingenuous, I'm afraid: My comment on your question was merely a ruse to trick you into explaining your schema to a degree which would minimize the amount of guesswork that I ( or any other member ) would have to shoulder in attempting to give you feedback of any reasonable quality on your work thus far.  You expertly evaded my trap however, leaving me with no recourse but to rebuild your schema from the ground up, along the way explaining what I think you've overlooked or otherwise omitted, in the context of what I perceive your problem to be.
Secondly, with little to no explanation of the notation you are using, it is difficult to immediately discern the relationships, either data-wise or real, between the entities in your table list: Attributes such as Position_ID show up in the Lecture and Admin tables using the ID notation, indicating a key of some kind, but with no obvious source;  the description for Course is littered with *'s without any indication as to why, further going on to include a User_ID attribute - This latter curiosity is particularly confusing and leads me to believe the model isn't normalized to any sufficient level at all, though I can hypothesize a ( short ) number of reasons why this relationship exists; the purpose of Co-Programme continues to be evasive; just looking at Consist of causes my brow to furrow violently - Unlike the User_ID in Course conundrum, I cannot even begin to fathom what is going on there.
So now that I've gotten all of that off my chest, we'll start by reverse engineering the structure you've provided into a word problem ( which is what I was trying to get out of you in the beginning ):

In the context of an e-learning system, we require a data model which can track our students as they participate in various courses, earning credits towards their programs.  Online courses are administrated by our staff, whom have expressed a desire to allow both themselves and students to upload various digital resources ( such as practice exams in the former case, or homework submission in the latter ) at varying levels of accessibility.  An adjudicator, for instance, would necessarily need to access the homework submissions of a class they preside over, whereas the fellow students of a submitter would necessarily not be able access such a resource.  We would also like to see some form of audit trail on the access of these resources ( though we don't really know why that's important ).

As @ETL points out in his comment, the very first thing to consider is that candidate keys make piss-poor primary keys, which are far better left to the database for auto-generation.  To be a little more clear on this point, despite frequent confusion between the two, there's no such thing as a primary key in relational algebra - this construct is instead referred to as a minimal superkey in model theory.  With that in mind, I'll be applying alternate keys ( also known as unique indexes or unique constraints ) on the minimal superkeys of our tables and letting the primary key be a nice, thin data type like INTEGER.  This practice will save you a lot of headache when foreign keys are involved and the data model needs updating.
"So, Mr. Words-A-Lot, are you going to help me or what?" or some such similar phrase may have indeed crossed your mind several times at this point.  Notice the description of your problem that I created above has a number of bold words.  These words, henceforth called nouns, in one way or another, are going to form the basic structure of your schema.  Some of them have already been realized in your original list, so some of this work has already been done, but for completeness ( and to illustrate good OOAD via identifying objects ), we will touch on them anyway.  Without further ado, here's the general idea of something relatively close to what you'd eventually end up with to solve our little blurb above:

Starting with relational model theory, meaning you can forget about things such as primary keys for now, "Noun-Verb" analysis is as follows:
A Student ( S ) accesses your online system with a ( has a ) Username ( UN ) and a Staff member ( which I'll be calling an Adjudicator ( A ), for my purposes ) has a Username as well.  Through this similarity, I can establish that there is a User ( U ) entity that has a Username ( UN ). ( A: B, C ), meaning entity A is a "container" ( or a collection, later called a table ) with B, C as "attributes" ( or atomic traits, later called columns ).  To make things a little easier on me, we'll expand this notation to also include the minimal superkey in the notation, denoting this ( eventually alternate key ) of a particular container with a subscript Un, where n is merely used to indicate attributes sharing an equal n are part of the same key in that container, separating them from the possible inclusion of an independently unique key, Um, should such a condition be necessary ( protip: It won't be, so don't worry about it ).

User ( U ) has a Username ( UN ), or ( U: UNU1 )

Based on the schema originally presented, I assume these Users ( U ) sign up through email correspondence, indicating there also is a Email ( E ) entity which must be accounted for.  While this is presented as attribute of the User ( U ) container, being a member of the information technology crowd, I can't help but wonder who these people are who only have one email address.  In my experience, it's generally much, much easier ( and preferable ) to simply under-use the capabilities of a robust data model than it is to work around the limitations of one that has been under-designed, so I find myself insisting on a one-to-many relationship between the two.  Email ( E ) thus becomes a container, with an Email Address ( EA ) as an attribute.  To illustrate the many-to-one relationship between Email ( E ) and User ( U ), the latter becomes a "compound" attribute of the former, meaning that the User ( U ) container is treated as an attribute of the Email ( E ) container.  This begins to illustrate where foreign keys will materialize in the final structure, and can be notated as:

( E: EAU1, ( U: UNU1 ) )

Students ( S ) and Adjudicators ( A ) are similar in other ways as well.  With attributes such as First Name ( FN ), Last Name ( LN ) and Date of Birth ( DOB ) in common, from a purely data-centric point of view, it becomes hazy as to whether they are indeed separate entities at all!  There is clearly a need to differentiate the two, or group them into their appropriate roles somehow, but the truth of this is as of yet unclear: Do the requirements dictate that a User ( U ) can not both be a Student ( S ) and a member of the Staff as well?  Does a Teaching Assistant not fill exactly this sort of Position, briefly alluded to in the original schema?
If a Student ( S ) can indeed also be an Adjudicator ( A ), it follows that there is a genuine one-to-one relationship between a User ( U ) and a Student ( S ) and also another genuine one-to-one relationship between a User ( U ) and an Adjudicator ( A ).  That is, a User ( U ) can be a ( is a ) Student ( S ) and a User ( U ) can be an ( is an ) Adjudicator ( A ).  A User ( U ) cannot be multiple of either however, barring clever online trickery.  Note than an Administrator could also be represented in this manner, through from the relative independence from the rest of the relationships in the model, I've omitted such an entity.

( S: ( U: UNU1 )U1 )
( A: ( U: UNU1 )U1 )

We know that both Students ( S ) and Adjudicators ( A ) have a First Name ( FN ), Last Name ( LN ) and a Date of Birth ( DOB ).  As such, we could do something silly, like the following:

( S: ( U: UNU1 )U1, FN, LN, DOB )
( A: ( U: UNU1 )U1, FN, LN, DOB )

But doing so would be in violation of, you guessed it, the Third Normal Form.  In case the reason a specific user shouldn't have two First Names ( FN ) isn't immediately obvious, since both Student ( S ) and Adjudicator ( A ) have a minimal superkey ( again, denoted by U1 ) which consists of the entirety of the minimal superkey of User ( U ), non-prime attributes First Name ( FN ), Last Name ( LN ) and Date of Birth ( DOB ) are transitively dependent on User ( U ).  The correct arrangement is then to instead associate those attributes with the User ( U ) entity, as follows:

( S: ( U: UNU1, FN, LN, DOB )U1 )
( A: ( U: UNU1, FN, LN, DOB )U1 )

The next piece of the puzzle to tackle involves Courses ( C ) and Programs ( P ).  From my understanding, a Program ( P ) is a curriculum offered by a Faculty, which despite probably being the correct word to use, I chose Department ( D ) in my model and am not about to go and change it now.  The Department ( D ) of Mathematics can offer a number of different Programs ( P ), such as a Bachelors, a Bachelors with Honors, a Doctorate in Philosophy or a certification for a Chartered Accountant.  A simple, one-to-many relationship.

( P: PCU1, PN, ( D: DCU1, DN ) )

I imagine that in order to complete a Program ( P ) and win yourself the associated expensive piece of paper, there will be a number of Program Requirements ( PR ) - at least, that's the way I had to do it.  These requirements arise in the form of Courses ( C ), each of which have a ( has a ) Credit Value ( CV ) towards a Program ( P ).  As an aside, I've added Course Level ( CL ) as a compound attribute as well, though it has little value in the context of the model relationships.

( C: CCU1, CN, CV, ( CL: CLCU1 ) )

As not all degrees are created equal, the Program Requirements ( PR ) may offer various choices and a particularly keen individual may opt to take a course in cryptology over one in data structures ( and subsequently get stuck listening to me ramble on the internet - not a desirable fate for the faint of heart ).  To keep Program Requirements ( PR ) flexible, we assume a one-to-many relationship between a Program ( P ) and Program Requirements ( PR ), with each of those requirements having a minimum number of Required Credits ( RC ) to meet the criteria.  The nature of this relationship is different from the other relationships we've seen thus far in that the Program Requirement ( PR ) deviates from the other entities by being a multiset ( there is no reason a Program ( P ) couldn't require 20 credits from two separate Course ( C ) lists ) and thus has no candidate key.  While this reality doesn't affect our model notation in any detrimental manner ( we simply leave the subscript off the attribute list ), in terms of implementation on a database, we'll be using a surrogate key.  If you noticed above, I'm using a surrogate key ( the respective <TableName>_PK attribute ) on every table anyway.

( PR: RC, ( P: PCU1, PN, ( D: DCU1, DN ) ) )

Using this Program Requirement ( PR ) multiset, we can then define Course ( C ) lists which are able to satisfy those requirements.  That is to say, there is a Program Requirement Course ( PRC ) which is able to contribute in some way to the Required Credits ( RC ) of the requirements of a Program ( P ).  This construct allows us to set up a list of courses ( in a three of five case, for example ) or specifically require a list of courses to be completed.  If for some reason a particular Program ( P ) wished to alter the Credit Value ( CV ) of a particular Course ( C ) for its own purposes, an Alternate Credit Value ( ACV ) could be added as an attribute to the Program Requirment Courses ( PRC ) entity, but it's omitted from the diagram above, pretty much because I just thought of it now.  Note that Program Course Requirement ( PRC ) is a many-to-many relationship, allowing a specific Course ( C ) to be a requirement of any number of Program Requirements ( PR ) and Program Requirements ( PR ) to involve the completion of any number of Courses ( C ).

( PRC: ACV, ( PR: RC, ( P: PCU1, PN, ( D: DCU1, DN ) ) )U1, ( C: CCU1, CN, CV, ( CL: CLCU1 ) )U1 )

With the bulk of the work out of the way, it's really a matter of just tying the Courses ( C ) back to the Students ( S ) and Adjudicators ( A ) and continuing on to assert some of the more trivial relationships.  Since the definitions are getting a little long, I'm going to stop showing the attributes of the compound attributes and focus on the final structures we'll need instead.
We create a couple of many-to-many relationships in the form of Adjudicator Course ( AC ) and Student Course ( SC ):

( AC: AU1, CU1 )
( SC: SU1, CU1 )

An Adjudicator ( A ) which can instruct / administrate a Course ( C ) can now perform Lectures ( L ) for those Courses ( C ), designating a Start Time ( T1 ) and an End Time ( T2 ).  This is a multiset in my schema, though it could certainly be instead constrained to time blocks if desired.

( L: AC, T1, T2 )

A Student ( S ) who logs in to attend a Lecture ( L ) can be noted through a many-to-many Student Attendance ( SA ) relationship.

( SA: SU1, LU1 )

A Resource ( R ) may be uploaded by a User ( U ) in the context of a specific Course ( C ).  An Adjudicator ( A ) should be allowed sufficient privileges to view the Resources ( R ) on their own Courses ( C ), whereas a Student ( S ) should only be allowed to see Resources ( R ) for a Course ( C ) as posted by an Adjudicator ( A ) of that course.  Such permissions are hand-waved by my data model as "business logic" to be handled through procedures, views and the application.  To expand on your comment, you could easily add a Resource Type ( RT ) here.

( R: U, C, RD, ( RT: RTCU1, RTN ) )

Finally, a ResourceUser ( RU ) table is added to simply keep track of the times ( Date Accessed ( DA ) ) which a particular User ( U ) accesses a specific Resource ( R ).

( RU: R, U, DA )

All in all, creating the following structure would solve the blurb:

( U: UNU1, FN, LN, DOB )
( E: U, EAU1 )
( S: UU1 )
( A: UU1 )
( D: DCU1, DN )
( P: D, PCU1, PN )
( CL: CLCU1 )
( C: CL, CCU1, CN, CV )
( PR: P, RC )
( PRC: PRU1, CU1, ACV )
( AC: AU1, CU1 )
( SC: SU1, CU1 )
( L: AC, T1, T2 )
( SA: SU1, LU1 )
( RT: RTCU1, RTN )
( R: RT, U, C, RD )
( RU: R, U, DA )


Paraphrasing from Explain Plan with Parallel Processing:

  The IN-Out column can take on the following values: PCWP, PCWC, P->S, S->P, and
  P->P.

  The values PCWP (Parallel Combine with Parent) and PCWC (Parallel Combine wth Child) indicate intra-operation parallelism.  For every PCWP there will be a PCWC. These pairs of operations are executed by the same set of parallel processes.

  The value P->P (Parallel to Parallel) indicates inter-operation parallelism.  This communication will between two different pairs of parallel processes. The value P->S (Parallel to Serial) always occurs in a SQL statement when the parallel processes send their data to the query coordinator.The value S->P (Serial to Parallel) occurs when a table is read in serial mode, such as a small table, and then its data is sent to an operation that is executed in parallel.

There are additional references available here and here.


Return all rows containing specific value in json array

You could opt to use TOP(1) in the CROSS APPLY

SELECT Id
FROM Journals journals
CROSS APPLY
(
SELECT TOP(1) Sensitive
FROM OPENJSON(journals.Data)
WITH (JournalType nvarchar(255) '$.type', Sensitive bit '$.sensitive')
WHERE Sensitive = 1
) as jsonValues;

Or, as you mentioned DISTINCT

SELECT DISTINCT Id
FROM Journals journals
CROSS APPLY OPENJSON(journals.Data)
WITH (JournalType nvarchar(255) '$.type', Sensitive bit '$.sensitive') as jsonValues
WHERE Sensitive = 1;

Main difference between the two

When using the TOP(1) the values will be filtered and the top operator will be applied earlier in the execution plan

When using the DISTINCT solution, the filtering and grouping is only done after the JSON function is (cross) applied

Which is why I would opt for the TOP(1) + CROSS APPLY solution.

As a side note, storing unique identifiers as nvarchar() is not ideal, especially when using a huge data type like nvarchar(450). Consider using the minimal or close to the minimal amount of space needed in your column datatypes & using the uniqueidentifier datatype.


SQL Server: Unable to save import export package, but am on Standard Edition

Community Wiki answer created from an answer in the question comments by billinkc

The version of the DTS Wizard that is launched is or isn't licensed based on the installation. Here you can see that I am licensed for SSIS on my 2008 instance but not for the 2014 (because I only installed client tools):

You can have a valid SSMS install and even a BIDS/SSDT install, and still not be licensed for creating SSIS packages. Also, you might be connecting to a licensed Standard Edition box but the tools on your local machine would need to be Dev/Standard/BI/Enterprise/DW edition.


Upgrade my server or migrate it to a new server

There's a good chance this question will be closed due to 'primarily opinion-based' and a lot depends on your individual circumstances, but here's mine...

I think you'll be better off going with fresh new servers with the latest OS and Sql Server versions you can tolerate.

You'll be able to do side by side testing between the old current environment and the new one in a more leisurely pace and minimize the risk of things going south due to a buggy upgrade.

Before you make your decision, I'd like to share an article by Michael K. Campbell entitled Reasons to Avoid SQL Server In-Place Upgrades.  Following are his arguments against in-place upgrades. (I'm basically copying the entire article because I think he's 'spot-on'.)

Gremlins

  Call me nuts, paranoid, or just plain crazy, but I simply don't trust
  Windows or SQL Server installations. Yes, most of the time, they go
  completely as expected. But upgrades commonly bump into ugly problems.
  And if we're talking about production workloads, running into one of
  the random problems that can crop up when trying to run an upgrade on
  a production server can be insanely expensive—not just in terms of
  additional downtime, but also potentially in terms of your
  credibility.

  Furthermore, even if you want to argue that an in-place upgrade where
  you reformat the server back to bare metal, spin up a new OS, and then
  install SQL Server (the new version) over the top of your old hardware
  (after grabbing db backups, etc.) can work, I'll still argue against
  such an approach. First of all, I've seen too many strange or weird
  cases where the installation of either Windows Server or SQL Server
  just doesn't take correctly. Yes, those instances are increasingly
  rare. But, when we're talking about a production workload, I almost
  never find the potential risk to be acceptable. Furthermore, even if
  you delude yourself into thinking that you can practice or rehearse
  the upgrade process, you're doing that in a controlled environment and
  not against the actual hardware or metal where a driver problem or
  issue might crop up.

  Instead, I find that it's much easier and effective to stand up a new
  replacement host, configure it without any stress or duress (i.e.,
  without worrying about a ticking clock counting down to when you need
  to be back online or running), and double-check all best practices and
  security configurations, and the like. Then, you can just copy
  security information (such as logins, etc.) on to the server and load
  on copies of your databases (i.e., via backups) to then start pointing
  application servers at this old/stale data to begin acceptance
  testing. Then, once you're sure everything is working correctly, you
  can just schedule a time to take the current production server down
  and switch over to the new server. And, if something goes wrong during
  that transition (as long as you haven't started writing data to the
  new location), then you can always abort your migration and switch
  back to the old server and try again another time. Whereas, if you try
  an in-place upgrade and something goes wrong, then, well, you're
  screwed and are stuck scrambling to get something back up and running.

Artifacts

  If we assume that gremlins represent show-stopper or fatal problems
  that completely thwart upgrades, then what about something even worse?
  Like, something that fails to upgrade or install correctly as part of
  your upgrade—but which you don't detect right away. Then, imagine the
  cost (in terms of pain/manageability) of having to workaround that
  issue more or less indefinitely. While such artifacts are rare, the
  costs they impose ensure that they're something you want to avoid like
  the plague. And while you CAN miss these kinds of issues or problems
  when you stand-up a brand new server to replace your old server, my
  experience is that you have much more time and ability to test and
  interact with this server (and hopefully, therefore, find such
  problems or issues) as opposed to being in a serious hurry to get core
  functionality up when doing an in-place upgrade.

Missing out on Hardware Improvements

  If you're upgrading to a new version of SQL Server, it's likely been
  about 2-3 years since your previous host was provisioned. Hardware
  capabilities have, effectively, doubled in that time. And the size and
  demands of your workloads have likely increased over that same time—so
  attempting to upgrade on the same/old hardware can and will work (each
  new version of SQL Server usually picks up around a 15-20 percent
  performance boost due to increased efficiencies in the underlying
  engine)—but just doesn't make as much sense as upgrading to new
  hardware.

  Of course, while that argument makes instant sense to tech heads, it
  doesn't always make great sense to bean-counters (i.e., if your CIO or
  manager comes from a background more related to bean counting. or, if
  funds are sparse, then hardware benefits and improvements alone might
  not be enough to justify). Still, I'll always argue that disruptions
  caused by anything listed above also come with costs (which can
  sometimes be unacceptably costly in terms of negative user
  interactions or linger maintenance costs), and that new hardware is
  almost always worth it. Besides, when you take this approach, it's not
  like you throw away your old production hosts when you're done. The
  old production hosts can easily be repurposed for other tasks (for
  other workloads) or can be reused as staging or dev servers, if
  they're still in decent-enough working order.

Testing

  Unless you're just assuming that you can do an in-place upgrade and
  everything will work—which is just plain dumb—then you're going to
  need to spin up some sort of additional host or server to act as a
  testing server that you can use to see how your applications behave
  when they've been upgraded.

  In other words, if you're going to go through this effort anyhow (and
  you really would be stupid not to), then WHY test this on anything BUT
  the new hardware where you're going to actually be hosting your
  databases going forward? In other words, with an in-place upgrade,
  you're going to have to go 'fake' an upgrade/testing environment, and
  then, you'll just assume that everything you tested and validated
  there will work on your current/production hardware after you
  magically manage to upgrade that hardware without any issues. Whereas,
  if you perform a side-by-side upgrade, not only are you removing the
  guess work, but you can also actually put your new hardware under load
  (in many cases) to fully verify how it will behave.

Downtime

  Another great benefit of side-by-side upgrades is that you can
  decrease downtime. In fact, with enough preparation and staging (along
  with rehearsals when/where needed), you can actually upgrade in 5
  minutes or less. With in-place upgrades, you're forced to wait while a
  Wizard churns along and upgrades various components, databases,
  binaries, and the like. But with a side-by-side migration, you can
  verify that the new server is configured and working as needed, then
  load copies of recent FULL backups on to the box, restore (but not
  recover them), and then just keep applying T-Logs through the day (or
  use DIFFs + T-Log backups if that makes more sense). Then, you've got
  a warm spare configuration so that when your maintenance window
  arrives, you can just shut down the current production host, take
  tail-of-the log backups, and then apply them to your new server and
  then spin up your applications (with connection strings pointing at
  the new server) after recovering the database. All in all, very easy
  to handle, very easy to practice and rehearse, and very easy to pull
  off with minimal down-time.


Tell MySQL to start using utf-8 encoding without `convert to`ing it

The solution isn't precisely the same but this question is where I originally found direction for a similar issue and the concepts there should take you where you want to go. MySQL has a BINARY character set and from all appearances, by converting through it, you can prevent MySQL from realizing what you're actually doing and being "too helpful."

Test case with character_set_client = utf8:

mysql> select CONVERT(CONVERT(CONVERT('Mikeâ€™s' USING latin1) USING binary) USING utf8);
+--------------------------------------------------------------------------------+
| CONVERT(CONVERT(CONVERT('Mikeâ€™s' USING latin1) USING binary) USING utf8)     |
+--------------------------------------------------------------------------------+
| Mike’s                                                                         |
+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)

You could use that logic to populate a new column that MySQL believes to be utf8.


The SAMPLE database is usually created with the db2sampl command (documented here for DB2 LUW 10.5). You can tell db2sampl to create another SAMPLE database under a different name with the -name option (note: this command must typically be run by someone with SYSADM privilege in the instance, e.g. by the db2inst1 user). For example, on a Linux box:

someuser@myserver:~$ sudo su - db2inst1
[sudo] password for someuser:
db2inst1@myserver:~$ db2sampl -name SAMPLE2


Oracle Express edition on Ubuntu - control file missing

It seems that something went wrong on the oracle-xe configure step.  Since this is a new install, I would follow the instructions for uninstalling and then the instructions for installing.


The best (and IMO the only suitable) tool for backuping a MySql database is mysqldump

The PhpMyAdmin options you are referring to are meant only to alter the generated statements in your dump (but will not touch your actual data). You might want to refer to this FAQ at PhpMyAdmin.


Admin DB replicated in MongoDB replica set

There are a couple of separate questions/issues here:

  do you have to create that same user on nodes 2/3?

If they are members of the same replica set, then no.  The users will be written to the primary and then replicated to the secondaries - remember any secondary can become primary in a normal set, so you would have to have all the data necessary to do that, including users.  If the nodes are in the set when they are added, the users will replicate normally.  If you add them later, they will replicate the users as part of the initial sync process.

Note that for nodes that are members of different replica sets (say multiple shards) that is not the case.

  I'm having some trouble doing that if I bring up the other nodes with
  auth/keyfile turned on

Remember that the keyfile must be identical for all nodes in a set.  The keyfile is what the nodes will use to authenticate with each other (for the purposes of initial sync and replication for a start, so it is an absolute must).  If you are having issues when you add the nodes, there will be errors in the logs that will tell you why.  The common reasons would be:

Incorrect config (new nodes not configured with the replica set name)
Different key files (this must be identical on all nodes in the replica set)
Connectivity or hostname lookup issues

If you expand on the difficulties you have when you try to add (how you are adding, what error you get, and preferably the output of rs.status() and a sample config file) I can elaborate further.


How can I backup a large SQL Server database without impacting performance?

Isolate backup I/O from the rest of the I/O on your system. While it may take longer and be more prone to hiccups, backing up over the network instead of to a local disk may help alleviate the direct impact on the instance. Even in a virtual machine you should be able to expose other storage so that SQL Server can write to a different I/O subsystem.
It will be unlikely to make any difference whatsoever but, if this is run from a job, why do you need to use the STATS option? Are you sure you need the other options (NOUNLOAD, NOSKIP, NOFORMAT)? I haven't done any extensive performance testing on the entire matrix of options, but IMHO you should only use the options that you know you need.
Run your full backups at a different time that doesn't interfere with regular user activity. This isn't always possible, but even in 24/7 operations there are peak and lull times.
If you move to 2008+ you can take advantage of backup compression (Enterprise on 2008, Standard or Enterprise in 2008 R2+). If you can't move to 2008 or don't have the adequate edition, there are 3rd party backup tools that will do compression for you, and they're all pretty good at it. Since 2008 I've been able to use native compression, and I have been quite satisfied with both speed and compression % there with no need to further invest in 3rd party. But with 2005 I remember having good success with Red-Gate SQL Backup; Quest LiteSpeed is also quite good, but I have no idea what kind of effort is being put into it since the Dell acquisition.
If your databases are in full recovery, you could go longer between full backups, trading that for the knowledge that if you have to recover to a point in time, you will potentially have more logs to restore. You could combine this option with Ali's suggestion below of taking diffs throughout the week - if you had to recover on Thursday, you would need to restore 1 full, 1 diff, and the logs since the diff.
If you have a lot of data that is stale and unchanging, you could consider hosting that in a different database with a different recovery plan, or at least on different filegroups, and split up your backup operations that way. If you have reference or archive data that can be made read-only, and if you are in simple recovery, you can move it to its own read-only filegroup, back it up once, and not have to include it in your nightly backups ever again. See these MSDN topics:

Performing piecemeal restores

Example: Piecemeal Restore of Only Some Filegroups (Simple Recovery Model)


Suddenly a massive amount of binlog files

Check server-id on in my.cnf on each server in your replication topology.  Probably there is a duplicate.  Server ids must be different.


how to remove/deal with locks in a MariaDB Galera cluster

The only way that I found to fix this issue is to restart the whole cluster in the original order as restarting a single node just fails. I'm not sure why this is a thing in Galera, it seems to be very sensitive to errors like this. In my opinion a database cluster should just work no matter what queries you throw at it.

I guess it's something of a free add-on when you go the Galera road, unfortunately.


SQL Server clustered index high fragmentation

FILLFACTOR only applies when you build or rebuild an index, not during normal operation. Normal operations always try to fill the pages to 100%.

If you insert a row that has a variable width, then update the row to be longer, that row will no longer fit on the page if there isn't enough extra space to store the after-image on the same page. If there isn't enough space, this will cause a page split, which is the process that creates the necessary space.

What's a bit misleading about page splits is that there are "good splits" and "bad splits," even though the performance counter counts all of them.

A good split is when new rows are added to the end of the index, like what happens when you do your initial batch of INSERTs. The new row doesn't fit on the last page, so the storage engine must allocate a new page and logically hook it up to the last index page. The new page will probably exist physically after the old-last index page.
A bad split is when a page must be inserted in the middle of an index: the new page is attached to the index structure logically on both sides, but may (likely) not exist in contiguous physical order to those pages.

Fragmentation is the discrepancy between the logical and physical order, and for the most part, only the bad type of page splits cause fragmentation.

Because you're inflating the size of already-existing rows, this causes the bad type of page split, which is why you're seeing high fragmentation numbers.

It's unclear what your exact process is, and how many rows are ultimately going to end up in this table. If it's something like a one-time population, do the full population process, then turn around and rebuild the clustered index with 100% FILLFACTOR.

If this process happens continuously, you could "pre-allocate" the space by doing the following: add a dummy variable-width column to the table, populate it to the max length on INSERT, and then on the first UPDATE, set the dummy value to NULL while updating the real value. This method will probably add overhead to the logging mechanism because of all the data values flying around.

In general, though, you only need to concern yourself with fragmentation when all of these factors are true:

The index is large enough that randomly reading the pages (extents) is too slow (choose your own definition of what "too slow" means)
The index will be scanned
The index pages (extents) will be physically read from disk (i.e., they are not already in the buffer pool)
The storage subsystem is poor at handling random reads (i.e., spindle-based, and the bytes won't come out of cache)

I would recommend, however, putting in place a solid index maintenance solution to keep things more or less in check. You can search this site for recommendations with regards to that.


How to migrate data between "all-surrogate" databases?

We came across this problem (or at least a similar one) not long ago at work, and our solution is to divide the migration process into steps:

Insert the new data to a temporary staging table(s)
Update the surrogate keys in the staging table(s) so that there are no conflicts
Insert the data into the target table(s)
Delete the staging table(s)

The second part is the tricky part, but in our case we were able to follow this algorithm for each table:

If there are any foreign keys against this table, set up foreign keys in the staging table with ON UPDATE CASCADE
Update the primary keys of all rows to a new id range. We used negative ids. E.g. UPDATE STAGINGTABLE SET ID=-ID-1; Now there is no risk of id clashes in the next step.
Now, for each row in STAGINGTABLE, generate and assign a new id

Repeat for each staging table. When all staging tables are processed, INSERT the data into the target tables.

One assumption this relies on is that all data is new and independent from the data already in the database.


Recommendations for MS SQL Server monitoring software or service?

You've got lots of options.  The venders to look at that are SQL specific are:

Quest Software - Spotlight
Red Gate Software - SQL Monitor
Confio - Ignite
Idera - SQLdm
Microsoft - System Center Operations Manager
Microsoft - SQL Server 2005 Performance Dashboard
SentryONE - Performance Advisor
ApexSQL Monitor

They will all do the job nicely, it just really depends on which one works best for you in your shop.  They all have free trials so you can try all of them and make an informed decision.


What you have is a full table scan with no way to speed it up.

Look into FULLTEXT(bussiness_name, trading_name) indexing as a much faster way to search for words.  You would need MATCH (bussiness_name, trading_name) AGAINST("$data['searchstring']" IN BOOLEAN MODE).

If status is rarely 1 and is_approved is rarely 1, then this would help: INDEX(status, is_approved).

You should also protect yourself from SQL injection by escaping the searchstring.


Importing files with a mix of decimal separators

Maybe you can try to edit (with notepad++) the file before import and clean (with a carefull find and replace) the separator you don't like.


Can not connect to MySql server remotely

A MySQL user has a "from host" field. This is probably set to some IP and those mismatch, giving you that error.
This field allows for extra security and can be set to any IP/hostname.
If this field is set for the user then you can only login to the MySQL server with that user from the IP/hostname specified.
If you want to test your connection you can just use a wildcard for the "from host" field % so that any IP can connect using that user, I wouldn't recommend this in a product environment though.


Why is PostgreSQL 9.5 not using my newest index for ORDER BY, even though it uses similar indices just fine?

In PostgreSQL, an index which is DESC NULLS LAST cannot be used to satisfy an ORDER BY which is DESC NULLS FIRST (which includes ordering by simply DESC because that implies NULLS FIRST).  This is the case even if the column is defined to be NOT NULL.

You could either rebuild the index, or (since you know the column is not null) you can add NULLS LAST to your query's ORDER BY to make it match the existing index.

Note that PostgreSQL does know how to follow an index backwards, so a default index (which is implicitly ASC NULLS LAST) would also be able to satisfy your DESC NULLS FIRST query.  Because of this, it is rarely important to specify DESC in an index, but it can be important to specify which end the NULLS sort to.


Downgrade SQL Server Enterprise Edition to SQL Server Standard Edition

Kendra Little did a great article on this: http://www.brentozar.com/archive/2014/08/sql-server-edition-change-standard-edition-enterprise-evaluation/

Microsoft article on supported upgrade paths:
http://msdn.microsoft.com/en-us/library/ms143393.aspx

First, note that not all downgrade scenarios are supported (You didn't provide the exact version you are running).

Second, run this query to check if there are any Enterprise features in use (those features will not work when you downgrade).

SELECT * FROM sys.dm_db_persisted_sku_features

Third, if your downgrade isn't supported (it doesn't seem like many are), then you are essentially going to have to do a fresh install with standard edition, then migrate to the new server.


Query to retrieve aggregated data using variables

Something like this?

declare @EarliestDate datetime
declare @LatestDate datetime
declare @NumberOfEvents int
declare @EventInfo nvarchar(100) = 'Summary of events'

SELECT   @EarliestDate = (SELECT MIN(Dtm) FROM [YourTable])
        ,@LatestDate = (SELECT MAX(Dtm) FROM [YourTable])
        ,@NumberOfEvents = (SELECT COUNT(*) FROM [YourTable])

SELECT   @EventInfo [Title]
        ,@EarliestDate [Earliest Date]
        ,@LatestDate [Latest date]
        ,@NumberOfEvents [Number of Events]


Every XML database will have its own solutions to these problems. You could do worse that explore the source code of an open-source XML database.

The main aspects of any solution will be (a) the ability to navigate from a node to its children or parent by following some kind of pointer structure rather than by parsing raw XML and searching sequentially, (b) the availability of indexes that enable you to find nodes quickly given properties of the node, e.g. indexing elements by name.


How to return all the unique column values on a per column basis for all metadata tables?

I'd use DENSE_RANK() to match values from distinct rows.

And MAX() OVER() to detemine which column has the biggest number of values (only for the ORDER BY purposes, you can skip this if the order doesn't matter).

Here is a working example:

create table test2 (
colone char(1), coltwo char(1), colthree char(1));

insert into test2 values
('a','d','j'),
('a','e','k'),
('a','e','m'),
('b','e','o'),
('c','e','o'),
('d','e','o'),
('f','e','o')
;

WITH cte AS(
    SELECT
        colone, DENSE_RANK () OVER (ORDER BY colone) as c1rn,
        coltwo, DENSE_RANK () OVER (ORDER BY coltwo) as c2rn,
        colthree, DENSE_RANK () OVER (ORDER BY colthree) as c3rn
    FROM test2
) , cte2 as (
    SELECT
        t1.colone, t2.coltwo, t3.colthree,
        MAX(t1.c1rn) OVER() as max_c1rn,
        MAX(t2.c2rn) OVER() as max_c2rn,
        MAX(t3.c3rn) OVER() as max_c3rn
    FROM cte  t1
    FULL OUTER JOIN
    cte t2 ON t1.c1rn = t2.c2rn
    FULL OUTER JOIN
    cte t3 ON
    t1.c1rn = t3.c3rn
    OR
    t2.c2rn = t3.c3rn
)
SELECT
colone, coltwo, colthree
FROM cte2
GROUP BY colone, coltwo, colthree
ORDER BY
    CASE WHEN colone IS NULL THEN 1 ELSE 0 END, colone,
    CASE WHEN coltwo IS NULL THEN 1 ELSE 0 END, coltwo,
    CASE WHEN colthree IS NULL THEN 1 ELSE 0 END, colthree
;

Hopefully your table is not very big, it will probably be scanned as many times as many columns you have, so it may take quite a lot of time.


DB2 SQL error: SQLCODE: -805, SQLSTATE: 51002

Sounds like you are not properly deallocating objects like Statement, ResultSet, etc., so your application is holding open statement handles in the database.  This will eventually exhaust "CLI Packages" in the database, resulting in the SQL0805N error.

The proper solution is to make sure that you close these as soon as you're done reading them.

You may also find documents suggesting that you rebind to increase the number of CLI packages at the database level, but keep in mind that this is just a band-aid.


Fallback to known existing records when a record is missing

You need something similar to

SELECT m.module,
       COALESCE(c_esp.content, c_eng.content) content
FROM `modules` m
LEFT JOIN ( contents c_esp,
            languages l_esp ) ON (     m.id = c_esp.module_id
                                   AND c_esp.language_id = l_esp.id
                                   AND l_esp.tag = 'es-ES' )
LEFT JOIN ( contents c_eng,
            languages l_eng ) ON (     m.id = c_eng.module_id
                                   AND c_eng.language_id = l_eng.id
                                   AND l_eng.tag = 'es-ES' )


How to format a date field into diferente languages in the same command? (multiple locales)

Sorry, I've looked into the source code around lc_time_names and there is no solution around getting multiple lc_time_name translations with a single query.

MariaDB has an information_schema table called locales however it doesn't got as far as exporting the lc_time_names of days/months etc. All the names are stored in the server (code, not a database), and it would be moderately easy to export these to an information_schema table, however noone has written the code to do this.


These are the steps of the SQL Agent Job:  detach the database, copy MDF and LDF files to the destination, then attach both source and destination to their respective instances.

It is possible that there was something preventing the detach.
You cannot detach a database that is replicated/published.
A database snapshot exists on the database.
It is being mirrored.
It is suspect.
It is a system database.

There could have been an issue with permissions copying the MDF and LDF files (not your login, but the SQLAgent service account login).

There could have been an issue with the attach.
A database created by a more recent version of SQL Server cannot be attached in earlier versions.

For the non-existent log file:  Ensure write permissions for the account that runs SQLAgent service on the folder where the log of the error would be written.


You are doing a string comparison, not a time comparison. Moreover, the string you return does not include the leading 0.

You might use something like below:

IF CONVERT(TIME,GETDATE()) > '08:45:00'

Here's further proof your expression returns a string:

sp_describe_first_result_set  N'SELECT LTRIM(RIGHT(CONVERT(VARCHAR(20), GETDATE(), 100), 7))'

For me this shows an output of varchar(7).


This is the offending code causing your query to take hours to return results:

           and not exists (
                        select null
                        from pa q
                        where q.PROJect_NBR = p.project_nbr
                          and q.status <> p.status
                          and q.aud_timestamp < p.aud_timestamp
                          and q.aud_timestamp > a.aud_timestamp
                           )

You should really convert that to a join instead, because as it stands you are generating a new lookup for each record in the 2million source rows.


You can use Ola Hallengren's - SQL Server Maintenance Solution

This is much flexible than maintenance plans.

We have 300+ servers and we are using it for all our backups. It just works flawless.

To get you started :

Automate and Improve Your Database Maintenance Using Ola Hallengren's Free Script


Basically, you have 3 options, using regular indexes (b-trees):

Create a clustered index on DAY_DATE. But chances are that you already have a clustered index on something else, and you cannot, for some reason, change that.

Create a non-clustered index on DAY_DATE. Whether or not this will be used by the optimizer depends on how many rows you return for that condition (2,066,381) compared to number of rows in the table (unknown to us). For each row, SQL Server would need at least one page access to go fetch that page.

Create a non-clustered index that covers the query with DAY_DATE as the key column and the other columns that you query refers to as included columns. Since you have to do SELECT *, then you would need all columns in that index, making it as big as the data it self (a bit bigger, even). Quite likely not doable.

A columnstore index might help, depending on the compression level you achieve in the end. But, again, that index would need all columns!

Bottom line: Do you really need that many rows and that many columns?


Why sys.columns.is_nullable can be null?

You are right that it is not possible for the outcome to ever be NULL. From quick investigation, take a look at the definition of sys.columns. If I run:

SELECT OBJECT_DEFINITION(OBJECT_ID(N'sys.columns'));

I see this:

...
sysconv(bit, 1 - (c.status & 1)) AS is_nullable,        -- CPM_NOTNULL
...
FROM sys.syscolpars c
...

If I change to a DAC connection, I can look at the columns here:

SELECT name, is_nullable
  FROM sys.all_columns
  WHERE [object_id] = OBJECT_ID(N'sys.syscolpars')
  AND name = N'status';

Results:

name    is_nullable
------  -----------
status  0

So the source is definitely not nullable, right? Yet if you do the same for sys.columns, columns involving expressions around c.status are marked as is_nullable = 1. That's more a reflection of the expressions / data types involved than the actual possibility of a NULL value.

I initially thought maybe SYSCONV() did something differently from CONVERT() (we can't use the former to check, but I thought maybe it might internally work more like TRY_CONVERT()). This is not the case, in fact this is quite easy to reproduce even without any internals knowledge:

CREATE TABLE dbo.foo(bit_column bit NOT NULL);
GO

CREATE VIEW dbo.bar
AS                                                      -- nullable?
  SELECT bit_col1 = CONVERT(bit, 1 - (bit_column & 1)), -- YES
         bit_col2 = bit_column & 1, -- no convert       -- NO
         bit_col3 = 1 - bit_column & 1                  -- YES
  FROM dbo.foo;
GO

SELECT name, is_nullable
  FROM sys.dm_exec_describe_first_result_set
       (N'SELECT * FROM dbo.bar', NULL, 1);
GO

DROP TABLE dbo.foo;
DROP VIEW dbo.bar;

Results:

name      is_nullable
--------  -----------
bit_col1  1
bit_col2  0
bit_col3  1

All that said, I don't know what advice to give you. You could conditionally hard-code that this specific column is not nullable in spite of what the metadata says, or you could play it safe and use what the metadata tells you (which will future-proof you in the event the underlying definitions change in future versions).


There seems to be some confusion over terminology. A user exists at the database level and is removed when you drop a database. A login exists at the server level and is mapped to a database user to get access to the database.

I'm assuming you want to find all logins that are not mapped to a database user and are therefore potentially no longer in use. The below query will achieve that:

CREATE TABLE ##UnusedLogins (
    [Login] NVARCHAR(255),
    [InUse] BIT
)

INSERT INTO ##UnusedLogins ([Login], [InUse])
SELECT [login].[name] AS [Login],
    0 AS [InUse]
FROM sys.server_principals [login]
WHERE [login].[name] NOT LIKE 'NT SERVICE\%'
    AND [login].[name] NOT LIKE 'NT AUTHORITY\%'
    AND [login].[name] NOT LIKE '##%'
    AND [login].[type_desc] <> 'SERVER_ROLE'
    AND [login].[name] <> 'sa'

EXEC sp_msforeachdb 'UPDATE ul
SET [InUse] = 1
FROM ##UnusedLogins ul
INNER JOIN sys.server_principals [login] ON [login].[name] = ul.[Login]
INNER JOIN [?].sys.database_principals [user] ON [user].[sid] = [login].[sid]'

SELECT * FROM ##UnusedLogins

DROP TABLE ##UnusedLogins

Any login in the output that has a 1 in the InUse column is mapped to a database user. Any login with a 0 is not mapped. This does not mean the login is not in use, simply that it is not mapped to any database user.


SQL Server: How to add all dates of the month?

Use a function like this one, from my answer to Get a list of dates between two dates using a function:

CREATE FUNCTION dbo.ExplodeDates(@startdate datetime, @enddate datetime)
returns table as
return (
with
 N0 as (SELECT 1 as n UNION ALL SELECT 1)
,N1 as (SELECT 1 as n FROM N0 t1, N0 t2)
,N2 as (SELECT 1 as n FROM N1 t1, N1 t2)
,N3 as (SELECT 1 as n FROM N2 t1, N2 t2)
,N4 as (SELECT 1 as n FROM N3 t1, N3 t2)
,N5 as (SELECT 1 as n FROM N4 t1, N4 t2)
,N6 as (SELECT 1 as n FROM N5 t1, N5 t2)
,nums as (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as num FROM N6)
SELECT DATEADD(day,num-1,@startdate) as thedate
FROM nums
WHERE num <= DATEDIFF(day,@startdate,@enddate) + 1
);

...and query it like:

SELECT *
FROM YourTable t
CROSS APPLY dbo.ExplodeDates(
    DATEFROMPARTS(t.[Year], t.[Month], 1),
    EOMONTH(DATEFROMPARTS(t.[Year], t.[Month], 1))
) AS d;


How to Backup/Restore in PostgreSQL pgAdmin 4?

You may find the docs useful
It sounds to me like pg_dump is "The Backup Dialog" and pg_dumpall is "The Backup Server Dialog".

The Backup Dialog

Using the pg_dump utility, pgAdmin provides an easy way to create a backup in a plain-text or archived format. You can then use a client application (like psql or the Query Tool) to restore a plain-text backup file, or use the Postgres pg_restore utility to restore an archived backup. The pg_dump utility must have read access to all database objects that you want to back up.

The Backup Server Dialog

Use the Backup Server dialog to create a plain-text script that will recreate the selected server. You can use the pgAdmin Query Tool to play back a plain-text script, and recreate the server.


Time taken by VACUUM FULL to reclaim space

It's a busy server of a local social networking website. No time is actually "downtime".

So VACUUM FULL is going to be a problem since it takes an exclusive lock on each table it processes. Consider the community tool pg_repack instead which achieves the same without exclusive locks.
Related:

VACUUM returning disk space to operating system

None of this affects the size of backups, since those do not include dead rows to begin with.


Work Arround

I used PuTTY to connect to the gear and form there I could connect to the mongo shell, and thus  solved the issue.

Some Articles that might be helpfull to others.

Remote Access to Your Application.
connecting-to-mongodb-gear-using-your-local-mongo-client-and-rhc-port-forwarding.


What should be the structure for a person-to-person store database

There could be many way possible to store data for such scenario, as with your design attempt it seems that you are looking to have unique Id to identify each product and if that is the case the you are right with having a productstable and make that product_id an auto increment key.

Now the next thing to make sure that what ever the attributes of all these bugs, Animals, Cars, Shirts, Shoes, Computers which are common can be added in products table, attributes related to sales can be a good choice to save in this table.

Now Let say for car you will have different brand, design, Engine(Diesel/Petrol), seats, Color and so on you may need other look up tables to save these attributes similar for all the other product like Shoes, Computers etc.


Concurrency / Transaction Concerns with PostgreSQL Copy (bulk insert)

Can I use COPY in normal transactions as I would other statements?

Yes, You can. This will invoke the trigger, check constraint and other
constraints as well. But it will not invoke the rules. It supports
MVCC and ACID.

Can mass COPY loads happen while the database is still live with normal production inserts and selects still running against other tables?

Yes, But its based on your workload. Do a test in Dev infra.

Can multiple connections concurrently perform COPY operations?

Yes it'll. But keep an eye on the resource utilization.

Am I crazy for thinking to treat this like any other PG command? Is it designed to be used against live, production databases or is it more of a housekeeping tool.

You can trust and use it in production. No issues.

References:

Read the Notes section from PG documentation

Someone implemented the same in Production with 10m rows file


How to list connections in Secondary Nodes

Go to the command line tools

Copy mongostat command under Set Up Diagnostics section

Update password and add --discover option at the end
Run it in any command-line tool
You should be able to see the number of connections as below for all primary and secondaries


In a previous role at a different Organisation to where I am now employed, we faced a similar decision whereby the GUID issue (as pointed out in the link) was a factor for us (both Width and poor choice regarding Index fragmentation) - but we needed to move to Transactional Replication (Peer-to-Peer) to allow our Sites to be Geographically separated.

In our Dev environment we worked through converting most of the GUID's to Identity columns, set up the replication to match the intended Production system and ensured that the Identity columns had separate ranges per node (as per http://technet.microsoft.com/en-us/library/ms146907(v=sql.105).aspx - this also gives suggestions for Automatic Identity Range Management, which may work for you).

Sounds good so far? NO...

The Identity columns were a nightmare to manage, through our Dev testing and working on automating the separation of the ranges, we gave up on them (never made it into production) - in my opinion, it is more trouble than it's worth - we settled back on the GUID's that were already in the database and settled with the known issues of a bit of wasted memory and Index Fragmentation.

For us, this wasn't that detrimental a solution - as almost all of the usage are Write operations with fairly low reads - obviously we have a maintenance plan that tries to take care of the Fragmentation as best it can (there are some Update operations - so Read and then Write but the delay is within threshold).

My 2 pence are - avoid Identity Columns (unless you already have a great plan for them - or you can add a static unit (i.e. Server 1 always has a preceding letter A1, A2 etc. Server 2 = B1, B2, etc)) when replicating and test the life out of it - assuming you can set up a replica Dev system or whatever. Of course it depends on the business whether you can put up with the poor read access that GUIDs provide because of their Index issues!

Good Luck.


creating stored procedure to populate empty table using pl/sql

You can do it with simple SQL statements and DBMS_RANDON as demonstrated below:

CREATE TABLE BOOKING
(
  BOOKING_ID VARCHAR2(7) PRIMARY KEY, BOOKING_DATE DATE
, BRANCH_ID VARCHAR2(7)
, ROOM_PRICE NUMBER(15)
, IS_BOOKED VARCHAR(5));

--You may create a sequence object to generate IDs.
CREATE SEQUENCE booking_id_seq
 START WITH     1
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;

INSERT INTO booking
SELECT booking_id_seq.nextval, TRUNC(SYSDATE + DBMS_RANDOM.value(0,366)),
dbms_random.string('U',trunc(dbms_random.value(1,7))), trunc(dbms_random.value(1000,100000)), dbms_random.string('U',trunc(dbms_random.value(1,5)))
FROM  dual
CONNECT BY level <= 12;-- Here you can define the number of rows that you want to insert.

SQL> INSERT INTO booking
SELECT booking_id_seq.nextval, TRUNC(SYSDATE + DBMS_RANDOM.value(0,366)),
dbms_random.string('U',trunc(dbms_random.value(1,7))), trunc(dbms_random.value(1000,100000)), dbms_random.string('U',trunc(dbms_random.value(1,5)))
FROM  dual
CONNECT BY level <= 12;

12 rows created.

SQL> select * from booking;

BOOKING BOOKING_D BRANCH_ ROOM_PRICE IS_BO
------- --------- ------- ---------- -----
158     06-NOV-16 ULQXM        81615 SH
159     18-DEC-16 RTG          99655 XDY
160     15-JUN-17 UF           45335 MCY
161     01-JUL-17 I            23249 FGS
162     13-MAY-17 KLLL          2177 T
163     07-AUG-17 HFC          10259 UKD
164     12-FEB-17 TVIZQY       98038 PJL
165     10-JUN-17 BIP          94314 ZSRS
166     22-JUL-17 ERQO         45985 QYC
167     27-AUG-16 EJKYB        39139 NJO
168     12-APR-17 AASMB        86838 NY

BOOKING BOOKING_D BRANCH_ ROOM_PRICE IS_BO
------- --------- ------- ---------- -----
169     24-FEB-17 NCQPMS       38296 Z

12 rows selected.

SQL>

Here are some good examples of using DBMS_RANDOM PL/SQL Package.


You want something like this (see below for DDL and DML). This sort of query would be easier with the likes of EXCEPT and MINUS, but we'll have to wait for those...

-- first query gets price difference between tables
SELECT p1.* FROM product1 p1, product2 p2
WHERE p1.pname = p2.pname
AND p1.price <> p2.price
-- second query finds extra products in product2 table - unions both of them.
UNION
SELECT p2.* FROM product2 p2, product1 p1
WHERE p2.pname NOT IN(SELECT pname FROM product1);

+----------+-------+
| pname    | price |
+----------+-------+
| Product1 |    10 |
| Product4 |    13 |
+----------+-------+

Or - for generality, note where I've added 'Product5', 17 to the product1 table? To get the differences both ways you need to perform the UNION of 3 queries

SELECT p1.* FROM product1 p1, product2 p2
WHERE (p1.pname = p2.pname
AND p1.price <> p2.price)
UNION
SELECT p1.* FROM product1 p1, product2 p2
WHERE p1.pname NOT IN (SELECT pname FROM product2)
UNION
SELECT p2.* FROM product2 p2, product1 p1
WHERE p2.pname NOT IN(SELECT pname FROM product1);

which gives

+----------+-------+
| pname    | price |
+----------+-------+
| Product1 |    10 |
| Product5 |    17 |
| Product4 |    13 |
+----------+-------+

CREATE TABLE product1
(
  pname VARCHAR(15),
  price int,
  PRIMARY KEY (pname)
);

INSERT INTO product1 VALUES('Product1', 10);
INSERT INTO product1 VALUES('Product2', 8);
INSERT INTO product1 VALUES('Product3', 9);
INSERT INTO product1 VALUES('Product5', 17);

CREATE TABLE product2

(
  pname VARCHAR(15),
  price int,
  PRIMARY KEY (pname)
);

INSERT INTO product2 VALUES('Product1', 11);
INSERT INTO product2 VALUES('Product2', 8);
INSERT INTO product2 VALUES('Product3', 9);
INSERT INTO product2 VALUES('Product4', 13);


How do you move a database from one server to the other using SQL Server Management Studio?

If you use "Detach/Attach" function you'll be able to transfert the database with all triggers and objects that are inside the DB. You must know that only the database will be tranfert. If you need to have all the logins for the old server, you can use this link from Microsoft. After you run the procedure, you can have some orphaned users. If you read carefully the Miscrosoft KB, you will find a link to solve this issue.

If you tranfer from 2005 to 2008 you must use this link to tranfer logins.

Hope this help you.


I found TDSProxy useful for this purpose. This proxy handles and redirects on TDS protocol used for SQL auth.
Code change to enable my scenario: https://github.com/tech-software/TDSProxy/pull/3


SQL Server 2008 Convert String to Datetime question

Use CONVERT and specify 1 as the date time style 1 = mm/dd/yy.

CONVERT(datetime, RIGHT('CXL P/D 08/15/13', 8), 1)


What normal forms are these two relations in?

The relation is actually in 3NF (so that it is also in 1NF and 2NF). The reason is that each attribute of the relation is prime, that is, it belongs to a (candidate) key (the are four keys in this relation: (A X), (A Z), (X Y), (Y Z)).

The definition of the 2NF (which has only an historical interest), is the following (Database System Concepts, 6th edition, Korth, Silberschatz, Sudharshan):

  A functional dependency X → Y is called a partial dependency if there is a proper subset Z  of  X such that Z  → Y . We say that Y  is partially dependent on X. A relation schema R is in second normal form (2NF) if each attribute A in R meets one of the following criteria:

  • It appears in a candidate key.

  • It is not partially dependent on a candidate key.

so the relation respects this definition, since each attribute appear in a key.

While the definition of Third Normal Form is (same source as above):

  A relation schema R is in third normal form with respect to a set F of functional dependencies if, for all functional dependencies in F+ of the form  X → Y, where  X ⊆ R and Y  ⊆ R, at least one of the following holds:

  • X  →  Y is a trivial functional dependency.

  • X is a superkey for R.

  • Each attribute A in Y − X is contained in a candidate key for R.

so the relations respects this definition too.

For your second question, the relation has two keys, A and Y, and it is already in BCNF as well as in 3NF.

Note that the analysis algorithm to decompose a relation in BCNF should return the original relation, since no dependency violates its definition (each determinant is a superkey).


Insert from Another Table + Removing Duplicate Entries in Postgres

Why not just use distinct on like this:

INSERT INTO entries(username, date, time, x0, x1, x2, x3, x4, x5, x6)
SELECT DISTINCT ON (username, date, time, x0, x1, x2) username, date, time, x0, x1, x2, x3, x4, x5, x6
FROM mytesttable;

?

But beware that this will retain the first row for every distinct combination -- which might not be the right thing without an order by clause this. But since you don't order in your question I presume you either don't care or haven't thought that far yet.


How do I monitor which Windows user is logging in as SA?

If you can identify the query that is causing the problem (e.g. via session_id in sys.dm_exec_requests), you should be able to resolve the host_name by checking the same session_id in sys.dm_exec_connections. In lieu of an actual Windows account name, you should be able to tell who it is by the host name they're connecting from (assuming they aren't all using Remote Desktop and running queries locally too).


Oracle Database 12c: Unable to resize system tablespace

DBA_TABLESPACE_USAGE_METRICS shows units in database blocks, not bytes. 4 MB is not enough for a SYSTEM tablespace. Most likely what you saw was 4M blocks, which is 32 GB with a 8K blocksize tablespace, and that is the maximum size of a datafile in a 8K smallfile tablespace. That is why your attempts had no effect.

You can add a new datafile to the tablespace, for example:

alter tablespace system add datafile '/home/user/oracle/orcl/system02.dbf' size 10m autoextend on next 10M maxsize unlimited;


Make Edition Upgrade in a SQL cluster scenario

You aren't able to "downgrade" SQL Server Editions from Enterprise (including Evaluation) to Standard Edition. The Supported Version & Edition Upgrade article details supported upgrade paths.

In the upgrade matrix in the above link, you will notice that the section for Evaluation Edition includes the following note:

 Upgrading from Evaluation Enterprise (a free edition) to any of the
 paid editions is supported for stand-alone installations,
 but is not supported for clustered installations.

On a Failover Cluster Instance, in order to change from Evaluation Edition to Standard Edition, you will need to uninstall SQL Server, and re-install using Standard Edition. The same is true in all cases (clustered or standalone) if you are using Enterprise Edition, or Evaluation Edition with any Enterprise-only features.


Same postgres query in two different instances with the same data but with different times

The difference is shared hits.
Shared hit means it read the blocks from ram (shared buffers).
Check for shared_buffers parameter on both servers.


Perhaps you can try server_uuid

Other have addressed using it

Beware of MySQL 5.6 server UUID when cloning slaves
Resolving Error: master and slave have equal MySQL server UUIDs

Some may resort to copying the auto.cnf to another server, which would then have the same generated server_uuid. You would have to code something that would scan a network on port 3306, sniff out the auto.cnf file, and check the UUID inside the file.

You could probably make the following requirements and coalesce them

Require the presence of the auto.cnf
Use Unique Identifier from Linux and imbed it yourself into auto.cnf as server_uuid
Match the UUID in auto.cnf with the machine as well as mysqld
Somehow broadcast that UUID so that your app can detect the presence of more than one occurrence of that UUID in the network

Doing these things may require taking the UUID your app generated and writing that UUID back into the installation software. That way, subsequent installations could compare the UUID of the machine with the now updated UUID from the first installation. Any match would have to deny installation thereafter.