SELECT Func(PK),count(*)
FROM tab
GROUP BY Func(PK)
HAVING Count(*)>1 ;

Where Func() is whatever you're using to clean up the PK column spaces

Example:

SELECT LTRIM(RTRIM(PK)),count(*)
FROM tab
GROUP BY LTRIM(RTRIM(PK))
HAVING Count(*)>1 ;

Example2: (as suggested by Martin in the comments)

WITH cte AS
(SELECT ROW_NUM() OVER (PARTITION BY LTRIM(PK) ORDER BY LTRIM(PK)) as rn
FROM tab)
DELETE FROM cte where rn > 1;


Is there a more efficient way to search for a record but only if it has associations?

The way your query is structured, you can just write it using an EXISTS expression:

SELECT
    m.name, m.id
FROM
    machines m
WHERE
    m.name LIKE '%Name%'
    AND EXISTS (SELECT *
                  FROM parts rt
                 WHERE rt.machine_id = m.id
               ) ;

If you want the query to be efficient, take into account that the most difficult part will probably be checking the machine name. Consider indexing machines.name using a trigram index. Trigram indices allow for fast finding LIKE '%something%' conditions.

CREATE EXTENSION pg_trgm;  -- Only needed if extension not already installed
CREATE INDEX name_like_idx
    ON machines
    USING GIST (name gist_trgm_ops);

Most probably, the database planner will make an execution plan where it will first find the machines whose name is LIKE '%Name%' (using the name_like_idx index), then, for every machine in this subset, it will check if there are parts associated with them; if so, they will be part of the result.

For efficiency, it is also assumed that there is also an index on parts.machine_id, because this will then be how PostgreSQL will efficiently check for the existence of the part. If machine_id is a foreign key, it is always advised to index it.

This query doesn't need DISTINCT (assuming that the ids are already unique).


Since you have a functioning Slave, you can rebuild the bad Slave in two different methods
For these examples

MAS : Master:
SL1 : Slave (Good one)
SL2 : Slave (Bad one)

METHOD #1
This method requires that you stop using the good Slave for the duration of the recovery

STEP 01 : On SL2, Run STOP SLAVE;
STEP 02 : On SL1, Run STOP SLAVE;
STEP 03 : On SL1, Run SHOW SLAVE STATUS\G
STEP 04 : Record Read_Master_Log_File and Exec_Master_Log_Pos From STEP 03
STEP 05 : mysqldump data from SL1 to a SQL text file
STEP 06 : Load mysqldump into SL2
STEP 07 : ON SL2, CHANGE MASTER TO master_log_file='(Read_Master_Log_File From STEP 03)',master_log_pos=(Exec_Master_Log_Pos From STEP 03)
STEP 08 : On SL2, Run START SLAVE;
STEP 09 : On SL2, Run SHOW SLAVE STATUS\G and Look at Seconds_Behind_Master
STEP 10 : Repeat STEP 09 until Seconds_Behind_Master is 0

After this, you can use SL1 and SL2 for reads
METHOD #2
This method requires that you shutdown mysqld on SL1 and SL2

STEP 01 : On SL1, Run STOP SLAVE;
STEP 02 : On SL2, Run STOP SLAVE;
STEP 03 : On SL1, Run SHOW SLAVE STATUS\G
STEP 04 : On SL1, Run net stop mysql
STEP 05 : On SL2, Run net stop mysql
STEP 06 : Copy entire datadir of SL1 over to SL2. Make sure you copy master.info file because it contains replication coordinates needed of SL2 to continue replication on startup. Make sure you copied the relay logs as well.
STEP 07 : On SL1, Run net start mysql
STEP 08 : On SL2, Run net start mysql

CAVEAT
If every table is InnoDB, MAS does not need to be shutdown at all. SL1 just needs a stable place to stop replication before copying to SL2.
Give it a Try !!!
UPDATE 2014-03-14 15:51 EDT
If you are planning to use the Master (since you are moving the Master to another Data Center), here is some advice.

STEP 01 : Stop application writes to MAS
STEP 02 : On MAS, run RESET MASTER; (Deletes all binary logs)
STEP 03 : On MAS, net stop mysql
STEP 04 : Copy the MAS server's datadir to the Slave's datadir
STEP 05 : On the new MAS, net start mysql (DO not have any application write to MAS yet)
STEP 06 : ON the New MAS, SHOW MASTER STATUS;
STEP 07 : On the Slave, net start mysql
STEP 08 : On the Slave, run CHANGE MASTER TO master_log_file='binlog from STEP 06',master_log_pos=(Position from STEP 06);
STEP 09 : On the Slave, run START SLAVE;
STEP 10 : Start application writes to MAS

No need to copy binlogs doing it like this. All a Slave cares about is the latest binlog.


There is currently nothing in the Mobile Report editor itself that indicates a capacity to order parameters and most graphs have no ordering capabilities
(a couple of the bar charts allow for value or label based chart arrangement - part of the reason I was thrown off here!).

However, one can accomplish sorting from the data sets used in the report. The use of a single data set may cause issues in the parameters... so a standard ORDER BY x, x... approach will only go so far. Using the same values and a different data set, one can accomplish sorting and maintain filtering abilities.

To illustrate, the following query in Mobile Reports gives an oddly sorted State list, since it is actually sorted, in expected SQL fashion, after warehouse and business center lists.

SELECT
     WarehouseID
    ,PostalCode
    ,City
    ,County
    ,State
    ,BusinessCenter
FROM ...
ORDER BY WarehouseID, BusinessCenter, State

The ordering above solves some parameter list sorting and fixes legend color displays in the report, but doesn't address the State parameter sorting. Another data set with the exact same values contained in State can be used for the parameter, which enables correctly sorted and still functional parameter lists.

SELECT DISTINCT
State
FROM DIM.Geography
ORDER BY State

This resolves consistency of legend location and coloring across the dashboard and solves parameter selection list sorting.


Difference of two columns with possible NULL cells

Many ways of doing this, but I find this readable:

SELECT CASE WHEN first IS NULL OR second IS NULL THEN NULL
       ELSE first - second END AS dif
    FROM table1
ORDER BY 1 DESC


Using a GIN index on an integer array to model sets of tags

I think you already more or less answered your own question.

While a GIN index can speed up searching inside an array quite significantly, you have to be aware that relational database engines are designed to do joins very efficiently.  Also, when you need the actual tags, you will have to join the tag table regardless of you use the array approach or not.

Furthermore, storing the tags as an array of integer makes enforcing referential integrity (that it is impossible to insert a nonexistent tag ID into the array) quite complicated.  The only way I can imagine this is creating a trigger, which never makes changing the data faster.

The best way to choose among these alternatives is, though, to create a sufficiently big amount of test data and measure the performance yourself.


SQL Server 2012 Transactional Replication Latency Warning Not Working

I discovered the job "Replication monitoring refresher for distribution" was disabled on the distributor.  I enabled the job and started it.  The alert appears to working correctly.


SELECT   ref, AVG(quantity), AVG(price*quantity)
FROM     myTable
GROUP BY ref


How to trigger an action without using triggers

it depends on how up to date you want CAL2 to be.

If it immediately should reflect the values in CAL1 then a trigger is the way to go.

If it can be a few mnutes out of sync then you could have a job that runs every 5 mins and calls a stored procedure that syncs the tables.


MYSQL inner join , if not match try another table

Always outer join them, then just use a CASE statement to pick out the info from the relevant table:

SELECT ref_id, case when p.property_id is null then p2.property_id else p.property_id end
FROM invoices i
left outer join properties p on i.ref_id = p.property_id
left outer join properties2 p2 on i.ref_id = p2.property_id;


How to enable query store on the SSMS object browser with Sql Azure?

This fixed my problem:

Disabling the query store by command:

ALTER DATABASE <DBNAME> SET QUERY_STORE = OFF;

Then enabling it again fixed the problem.

ALTER DATABASE <DBNAME> SET QUERY_STORE = ON;


Too many VLFs - How do I truncate them?

What is to many VLF?

Google around a bit and you find a bunch of different opinions on what constitutes to many. I think the better question is:

What is the right amount of VLFs for my requried log size?

Optimally transaction logs should be on a drive separate from the datafiles.  There should be a single log file, that is sized correctly to meet reoccurring needs without auto growing. VLF should number between 4 and 50, with a maximum size of 500MB. For log files greater than 25GB special consideration is required. Other than during proactive maintenance they should not shrink or grow.

How do I tell how many I have and how big they are?

SQL Server writes logs sequentially. The logs are written into sub sections called Virtual Log Files (VLF).  You can get information about the VLFs of a database with the undocumented DBCC LOGINFO command.  The four fields to understand first are:

FileSize; The size of the VLF in bytes.  Optimally these should be about the same size.  Except for those with a CreateLSN of zero.
FSeqNo; The sequence order the VLFs were last written to.
Status; Has two values, 0 = VLF is available for reuse, 2 = VLF is not available.
CreateLSN; The Log Sequence Number (LSN) when the VLF was created.  If the Value is 0 it the VLF was created at the same time as the database (Original Equipment)

VLF are created when;

The database is created for the first time (CreateLSN = 0) generally it will have between 2 and 8 original equipment VLFs
The logfile is manually grown. i.e. (ALTER DATABASE [MyDB] MODIFY FILE (NAME = N' MyDB_log', SIZE = 51200KB) using T-SQL or the GUI (Graphical User Interface)
The logfile auto grows because all the current VLFs have a “Status” of 2 and more transactions need to be written to the logs.

Status changes;

To 2, when the content is not fully committed and/or in the case of full recovery the transaction has not been backed up.
To 0, when the VLF is available to be overwritten.  All the content is committed and/or the transactions are backed up.

FSeqNo changes when:

The VLF is written to. The value indicates the sequence the VLF was written to. A FSeqNo value of 0 indicates a VLF that has never been used.
The highest value FSeqNo will always have a Status Value of 2, it is the VLF currently being written to.

How do I get the right number of VLF for my required log size?

First it depends on what version of SQL you are running. The DBA can not directly create VLFs.

Files size and number of VLF created per LSN:

The size and number of VLF created is handled by an algorithm based on growth size and SQL version.
SQL 2008 R2 and earlier, same as below but avoid growing in increments or multiples of 4GB

https://connect.microsoft.com/SQLServer/feedback/details/357502/transaction-log-file-size-will-not-grow-exactly-4gb-when-filegrowth-4gb

SQL 2012 and earlier

Grow 1 to 64MB, creates 4 new VLF, each about 1/4 of the growth
Example; 50MB grow gives 4 VLF of 12.5MB
Grow 64MB to 1GB, creates 8 new VLF, each about 1/8 of the growth
Example; 250MB grow gives 8 VLF of 31.25MB
Grow 1GB or larger, creates 16 new VLF, each about 1/16 of the growth
Example; 1GB grow gives 16 VLF of 62.5MB

SQL 2014 and newer

If the growth is less than 1/8 of the current log file (ldf) creates 1 VLF of the growth size
Example: 10GB ldf growing 1GB gives 1 VLF of 1GB
If the growth is more than 1/8 of the current log file uses the SQL 2012 algorithm
Example: 10GB ldf growing 2GB gives 16 VLF of 125MB

What Grow means:

Each grow is resetting the current allocated size (shown as Initial size in the GUI).  If the current size is 500MB and you change it to 1GB (1000MB) you are growing 500MB. This will create 8 new VLFs at 62.5MB each.  All the existing VLF remain unchanged.  If you had 76 VLF you will now have 84 VLF.

Ok, Now I am ready to see what I have and start changing them.  How do I do it?

You can examine VLF on a single database, an entire server, or against a group in Central Management Servers (CMS).

For a single database, run DBCC LOGINFO to get a picture of the current VLF.  The number of rows = the number of VLF.

USE [MyDB]
GO
DBCC LOGINFO

You can use the GUI (Database Properties > Files) or the query below to see file size and location information.  This query also gives used & free space information which is not available in the GUI

select file_id
, type_desc
, name
, substring([physical_name],1,3) AS [Drive]
, physical_name
, state_desc
, size / 128 as 'AllocatedSizeMB'
, FILEPROPERTY([name],'SpaceUsed') /128 AS 'SpaceUsedMB'
, (1- (FILEPROPERTY([name],'SpaceUsed') / CAST (size AS MONEY))) *100 AS 'PercentFree'
, growth / 128 as 'GrowthSettingMB'

 from sys.database_files
 order by type_desc Desc, name

To examine all the databases on server or in a CMS group this query at github from the Microsoft tiger team, provides details on all database with greater than 50 VLF along with code and directions to make improvements.

https://github.com/Microsoft/tigertoolbox/tree/master/Fixing-VLFs

As of August 2017 it does not take into account the SQL 2014 VLF growth considerations outlined in Paul’s post https://www.sqlskills.com/blogs/paul/important-change-vlf-creation-algorithm-sql-server-2014/ Nor does it consider Orginal Equipment VLF. The Tiger solution offers generic solutions. On most occasions after looking at all available information I have chosen a slightly different solution than offered by the Fixing-VLFs query.

What size the log file should be, is to much to include here. For now let's assume you know what the best size is.  The following should be done, off peak or during scheduled down time.

1.Set the auto grow to your first new allocated size

Why? If/when the shrink gets you 4 VLF with a total allocated size of 1MB, and anything happens on the database, the logs will auto grow.

.

USE [master]
GO
ALTER DATABASE [MyDB] MODIFY FILE ( NAME = N'MyDB_log', FILEGROWTH = 4000MB )
GO

Shrink the log

Why? Other than the original equipment VLF the goal is to create, new VLF that are properly sized.  Using SHRINKFILE with a size of 1, will eventually clear all except the original equipment

.

USE [MyDB]
GO
DBCC SHRINKFILE (N'MyDB_log' , 1)
GO

See how many VLF are left

Why? There are several scenarios, you may still have several hundred VLF or only the original equipment VLF left, you may have got down to 1MB and had an immediate auto

.

USE [MyDB]
GO
DBCC LOGINFO

Cycle the t-logs, if you have not cleared off all the old VLF.

Why? If anything other than original equipment VLF and/or brand new auto grows remain you need to cycle to get the currently active VLF to the top of the list.
Code: (varies) Run on demand t-log backups or CHECKPOINT

Return to Step 2 and repeat, until all the old growth VLFs are gone.

Why? Depending on multiple variables, it may take 3 or 4 runs through to get all the old grows off.

First Grow

Why? Create your first set of planned VLFs

.

USE [master]
GO
ALTER DATABASE [MyDB] MODIFY FILE ( NAME = N'MyDB_log', SIZE = 4000MB )
GO

See how many VLF you have now

Why? See if you got what you expected

.

USE [MyDB]
GO
DBCC LOGINFO

Second Grow

Why? If you decided, you need more, repeat as needed

.

USE [master]
GO
ALTER DATABASE [MyDB] MODIFY FILE ( NAME = N'MyDB_log', SIZE = 4000MB )
GO

Set a reasonable Auto Grow

Why? If everything goes perfect, and you have taken everything into account it will never auto grow.  You need to decide what is going make the best sense on your system.

.

USE [master]
GO
ALTER DATABASE [MyDB] MODIFY FILE ( NAME = N'MyDB_log', FILEGROWTH = 250MB )

SQL 2014+

Important Reminder: on SQL 2014 and newer “If the growth is less than 1/8 of the current log file (ldf) creates 1 VLF of the growth size” Example an 8GB Log file growing 1000MB is not going to create 8 new VLF at 125MB, it is will create 1 VLF at 1000MB, if it grows 1100MB it will create 16 new VLF at 68.75MB, Huge differences here (where 1GB = 1024MB).

Log File (ldf) greater than 25GB

Optimal configuration is often described as not more than 50 VLF at not more than 500MB each.  Three grows of 8GB, give us about 48 VLF with an allocated space of 24GB.  What happens if your log file needs to be 50 or 100GB? Do you increase VLF size or count?  This document does not attempt to address those scenarios, I have found a few suggestions of allowing VLF to be up to 1GB, but when you get over the 24 or 25GB allocated file size there are just to many variables to offer a generic path to solution.

References:

https://www.sqlskills.com/blogs/paul/multiple-log-files-and-why-theyre-bad/
http://sqlblog.com/blogs/kalen_delaney/archive/2009/12/21/exploring-the-transaction-log-structure.aspx
https://technet.microsoft.com/en-us/library/ms190411(v=sql.105).aspx
https://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/
https://www.sqlskills.com/blogs/paul/important-change-vlf-creation-algorithm-sql-server-2014/
https://www.sqlskills.com/blogs/kimberly/8-steps-to-better-transaction-log-throughput/
https://www.brentozar.com/archive/2015/05/how-to-see-recent-autogrowth-events-on-databases-sql-server/


How to do this in SQL, using only DDL (FOREIGN KEY and UNIQUE constraints):

CREATE TABLE author
( author_id INT NOT NULL,
  author_name VARCHAR(100),
  CONSTRAINT author_pk
    PRIMARY KEY (author_id)
) ;

CREATE TABLE book
( book_id INT NOT NULL,
  book_title VARCHAR(100),
  CONSTRAINT book_pk
  PRIMARY KEY (book_id)
) ;

CREATE TABLE book_author
( book_id INT NOT NULL,
  author_id INT NOT NULL,
  CONSTRAINT book_author_pk
    PRIMARY KEY (book_id, author_id),
  CONSTRAINT book__book_author_fk
    FOREIGN KEY (book_id) REFERENCES book (book_id),
  CONSTRAINT author__book_author_fk
    FOREIGN KEY (author_id) REFERENCES author (author_id)
) ;

So far this has implemented only a common many-to-many relationship between books and authors. Which means that a book can have 0, 1 or many authors and an author can have 0, 1 or many books.

If we want to have the strict "a book must have at least 1 author", we can add another column in book and the additional constraint:

ALTER TABLE book
  ADD COLUMN author_id INT NOT NULL,
  ADD CONSTRAINT book_must_have_at_least_1_author_fk
    FOREIGN KEY (book_id, author_id)
    REFERENCES book_author (book_id, author_id) ;

Notes:

For the above constraints to work we need a DBMS that has implemented deferrable constraints (eg. Postgres, Oracle) or can insert into / delete from multiple tables in one statement (Postgres). Without any of these, we can relax some of the constraints, for example lifting the NOT NULL from book (author_id) and doing the insert (say of a new book) in 3 steps:

insert into book (with author_id null)
insert into book_author
update book (setting author_id accordingly).

We can then wrap the above steps in a transaction/procedure and then restrict access to the tables only via these transactions/procedures (one procedure for each operation, insert new book and its authors, delete book and its authors, update author, remove author from book, etc).

If not entirely obvious these procedures may get a bit complicated, for example when the "primary" author of a book has to be removed, as the procedure has to search the remaining authors and update book.author_id accordingly or fail/deny the removal if there in no other author left.
Another option - since we mentioned procedures - is to use them from the beginning for all insert/update/delete operations. If designed and tested correctly, they can enforce all the constraints, allowing to not declare any foreign keys.


List all Oracle procedures/functions (including in packages)


Simple scenario needs to go from Unnormalized Data to 3NF - Review Please

Yes, it's in 1NF.

You can't side-step the often hard work of determining all the candidate keys by hanging a number off the end of the table and saying, "There. I've got a primary key."  One natural candidate key for this table is {Name, Bought from, Date bought}.  Consider using "Time bought" instead of "Date bought".

Your definition of 2NF is wrong. Instead of

  Second Normal Form: A relation that is in First Normal Form and every
  non-primary-key attribute is fully functionally dependent on the
  primary key.

you need something more like this.

Second Normal Form: a relation that is in First Normal Form, and every non-prime attribute is fully functionally dependent on every candidate key.

The term non-prime attribute doesn't mean quite what non-primary-key attribute means.

Your definition of 3NF is wrong, and it's wrong for the same reasons as your definition for 2NF was wrong.

Instead of this

  Third Normal Form: A relation that is in First and Second Normal Form
  and in which no non-primary-key attribute is transitively dependent on
  the primary key.

you need something closer to this.

Third Normal Form: A relation that is in Second Normal Form and in which every non-prime attribute is nontransitively dependent on every candidate key. (There isn't a really good way to express all those negative in one sentence.)


You don't, initially you want to create your table relationships and infrastructure without the indexes.

This is because until you know what how often you query a table, and the various queries, how would you define the BEST index? Indexes only speed up queries that intend to retreive data, more specifically for dealing with large volumes of data.

I would revise them, only when things begin to "slow down".

Primary Keys - define your Primary unique index for a table. Auto Increment fields are generally your primary keys, sometimes you may have a joiner table with a Many to Many relationship, where you don't want duplicate records so you'd make ALL fields the primary key.

For Example: parent_id is 1 and child_id is 1, but you don't want more than 1 record in the joiner table with 1 relating to 1.

Foreign Key indexes (keys that relate to another table), which are for integrity. I would recommend using these when creating your tables. Cascade updating and deleting is used to ensure when you delete a parent record, the children records associated are automatically deleted.

Although you assign your foreign key constraints to a "child" table referencing it's parent, when executed MySQL recognises "When item X is deleted from Parent_Table, we shall delete ALL records from Child_Table with a parent_id of X" - I hope this makes sense.

Index keys - are used to speed up queries which collect records, or even joining records from related tables, and sub-queries (Although I discourage the use of these).

FYI - InnoDB is a transactional database, thus unlike MyISAM, will ONLY lock an individual ROW when modifying a table (inserting, deleting, updating records). MyISAM locks the entire table when modifying records within it (hense why data importing large volumes of data to a MyISAM is discouraged).

IMO - You should use foreign key constraints, in INNODB tables. Ensure your tables have primary keys where necessary at most. Then when you're done, look at your data and keep a constant eye on how slow the queries become, then consider the best index forward from there.


You want information based information that is in the Epoch field. Not even the whole field but just on DATE(FROM_UNIXTIME(Epoch)) and HOUR(FROM_UNIXTIME(Epoch)). Unless you have an index on that information the database will always need to read every row of the table to see if the row fits the criteria.

Adding an index on Epoch as a whole will not help too. The DATE(FROM_UNIXTIME(Epoch)) can be bypassed with (if it exists) TO_UNIXTIME(CURDATE()) but the HOUR(FROM_UNIXTIME(Epoch)) needs a specific index.


"Covering" is the wrong term to start with.  I'll get back to that in a minute.

The optimal index for your queries can be summarized:

The first columns in the index must be all the = columns in the WHERE, in -any_ order.
The last columns in the index must be the ORDER BY columns in the same order, and either be all ASC or all DESC.  (MySQL 8.0 has an exception here.)

Your index 1 is necessary and sufficient for query 2.
Your index 2 is necessary and sufficient for query 1.
Anything different would be sub-optimal.

Note that they would also be "covering" in that all the columns anywhere in the SELECT are found in the respective indexes.

Index 2 "covers" both queries, but it is not very good for Query 2.

Devise the optimal index for each important query.
Eliminate redundancy.  Example: Given INDEX(a), INDEX(a,b), toss the former.
Then think about adding some columns on the end to make an index "covering".

For those tips, plus more, see http://mysql.rjweb.org/doc.php/index_cookbook_mysql

This is "covering", but not efficient because col99 in first in the index, but not used for filtering or sorting:

INDEX(col99, col1, col2, col3)

It might be used, but only because it is covering.  It can't be used for filtering or sorting.

This is "covering" and might be used to avoid sorting because the ORDER BY column(s) are first:

INDEX(col3, col2, col1)

Query 1 would be just as happy with this regardless of the cardinality of the individual columns:

INDEX(col2, col1, col3)

Back to the title question:

  Does the order of columns in a covering index matter?

Answer:

For the sake of "covering":  No.
For speeding up the query:  It depends.  And that is why I recommend starting with other 'rules' for building a good index.

An analogy

A table is an unordered set of things.  Analog:  a textbook.
An index is an ordered list, referencing those things.  Analog:  the index in the back of the textbook.
When everything you need to answer a question is sitting in the index (and you don't need to leaf back into the body of the textbook), then the index is "covering".

A B+Tree has two important properties:

It is quick to jump into the middle at any specific word.
It is fast to scan forward from that word.


The documentation states

  When a single write operation modifies multiple documents, the modification of each document is atomic, but the operation as a whole is not atomic

Multi-row rollback is one of those things which are foregone in the NoSQL world, sadly.

That same link mentions the $isolated operator. This may be of some help, depending on the use case and topology.

Also documented is the so-called "two phase commit" pattern. This may require application changes, however, so may not be practical.

Five million rows isn't a silly-big number. Would it be possible to arrange an application outage until the writes succeed, with continuous retries? Maybe run the write application on the same hardware as Mongo so the network is no longer an issue.


Based on your log file, you are using MongoDB version v2.2.3. This version is not officially supported by MongoDB, so I'd recommend you plan to upgrade to a newer version. The latest stable version, MongoDB 4.0.9.

As per the log message, the journal file j._0 in /var/lib/mongodb/journal/ folder is corrupted.

Try, removing the j._0 file and do the recovery process.

Disclaimer: you may lose some data that present in the journal file.


Date model for commenting system stored in a key-value NoSQL DB?

In Cassandra you need to take a query-based modeling approach.  In your case, I would create a similarly-structured query table like this:

CREATE TABLE topicbyuser (
    topic_id text,
    user_id text,
    event_time timestamp,
    data text,
    PRIMARY KEY (user_id,event_time),
) WITH CLUSTERING ORDER BY (event_time DESC);

And then, if you wanted to query for the last 10 comments made by John, this would work:

SELECT * FROM topicbyuser WHERE user_id='John' LIMIT 10;

You could also add an ORDER BY, but since you have that in your column family definition, you shouldn't need it.  Also, you should note that this query table does not replace the original topic table.  It works in conjunction with it.


Unexpected result from PostgreSQL information schema

The only reason I can think of, is if my_schema is not part of your search_path.
As you pass a table name without schema qualifying it to the function pg_relation_size() the table is searched in the default search_path. If it's not found, you'll get that error.
Use a fully qualified name instead:
pg_relation_size(format('%I.%I', table_schema, table_name))


return first record after sum of specific value

A cumulative sum can be reached with a Window function and a CTE, the difficult part is to stop it.

sum(amount) over (partition by user_id order by deposit_id)

create table foo(deposit_id int,user_id int,amount int);
insert into foo values
(1,1,10),
(2,1,5),
(3,1,5),
(4,1,-15),
(5,1,-3),
(6,1,-3),
(7,1,-5);

with CumSum as
(
    select deposit_id, user_id, amount,
           sum(amount) over (partition by user_id order by deposit_id) as total
    from foo
)
select deposit_id, user_id, amount, total
from   CumSum
order by user_id, total desc;

deposit_id | user_id | amount | total
---------: | ------: | -----: | ----:
         3 |       1 |      5 |    20
         2 |       1 |      5 |    15
         1 |       1 |     10 |    10
         4 |       1 |    -15 |     5
         5 |       1 |     -3 |     2
         6 |       1 |     -3 |    -1
         7 |       1 |     -5 |    -6

To get the first element where total <= 0

with CumSum as
(
    select deposit_id, user_id, amount,
           sum(amount) over (partition by user_id order by deposit_id) as total
    from foo
)
select   deposit_id, user_id, amount, total
from     CumSum
where    total <= 0
order by user_id, total desc
limit 1;

deposit_id | user_id | amount | total
---------: | ------: | -----: | ----:
         6 |       1 |     -3 |    -1

dbfiddle here


Your way would seem fine. Older versions of MySQL do not have DROP USER IF EXISTS.

Here is another way that would get around that issue with older versions

DELETE FROM mysql.user WHERE user='${domain}' AND hostname='localhost';
DELETE FROM mysql.db   WHERE user='${domain}' AND hostname='localhost';
FLUSH PRIVILEGES;
DROP DATABASE IF EXISTS '${domain}';
CREATE DATABASE '${domain}';
GRANT ALL PRIVILEGES ON ${domain}.* TO '${domain}'@'localhost';


lsnrctl: cannot execute binary file: Exec format error

First, don't worry about the comments. Your question or problem is total valid.

Second, there is a comment about "ubuntu isn't supported so your question should be deleted" which isn't right. If ubuntu isn't "supported", that means you will have probably more problems if you try to install it, and Oracle won't help you. But Oracle never can be really easy installed (I am thinking some like so easy as typing as an apt-get install oracle-11.2-server or like, which is easy. Sucking with a monstrous and buggy java installer, which Oracle DB has, isn't easy), not for the so-named "supported" distributions/systems and not for the "unsupported" ones. Second, your problem is still a valid database administration problem, even if you use ubuntu or not.

About your problem: it is really clear. lsnrctl had to be runnable. This error message has the very clear meaning: your lsnrctl has an invalid binary format in your architecture. For example, you are trying to run a 64-bit binary on a 32-bit system. Or you try to run an i386 binary on a ia64 system. Or your lsnrctl binary is somehow damaged.

The command file -szL /u01/app/oracle/product/12.1.0/dbhome_1/bin/lsnrctl could make clear, which is the real problem, thus what is happened to your lsnrctl. But it is very probable, that simply you have a bad distribution installed, and so you need a reinstall, give a try to this command before.


PostgreSQL: Find records with single quotes

'%''%'  will look for entries with a one single quote (as a single quote is escaped by doubling it). If you want to search for two single quotes, you need 4 quotes (because of the escaping), e.g.:
where the_column like '%''''%'

alternatively you can use dollar quoting to avoid the escaping
where the_column like $$%''%$$


Optimizing a query that retrieves five rows for each letter of the alphabet

You have to use the REGEX_LIKE or briefly RLIKE function:

. . . . . .
WHERE RLIKE(table1.name, '^[:alpha:].*', 'i')
. . . . . .

Here ^ mean "from the very beginning of the string",
[:alpha:] is any alphabetical character
.* is any number of any characters and
'i' is a flag that mean case-insensitive comparison.

RLIKE() function returns TRUE if matched and FALSE otherwise.

Further reading can be found here: https://dev.mysql.com/doc/refman/8.0/en/regexp.html

UPDATE

SELECT table1.id
     , table1.name
     , table1.url
     , IF(@tname <> @tname := table1.name, @cnt := 1, @cnt := @cnt + 1) AS cnt
  FROM table1
  LEFT JOIN table2 ON table1.aid = table2.aid
 WHERE RLIKE(table1.name, '^[:alpha:].*', 'i')
   AND table1.rank > 0
   AND table1.view = 1
 ORDER BY table1.name ASC
        , table2.important DESC
        , table1.rank ASC
HAVING cnt <= 5


Transaction basics. What is the result of 2 transactions running concurrently?

There is no implicit shared lock in REPEATABLE READ isolation level.
You need to change the transaction sequence as follows:
ISOLATION LEVEL
You could switch to the SERIALIZABLE isolation level and disable autocommit. That way, the row is locked in shared mode implicitly.
If you want to stay with REPEATABLE READ or READ COMMITTED, you will have to call SELECT ... FOR UPDATE manually before doing the UPDATE.
QUERY (Optional Suggestion)
You should also experiment with
UPDATE ACCOUNT SET amount = amount - 100 WHERE id = 123;

along with disabled autocommit and SERIALIZABLE isolation level.
I suggested this because you are doing two queries (SELECT and UPDATE) to change the amount. You could just do it with one query.
Your Question
Since locking reads and not implicit with REPEATABLE READ, there is a possibility of 500 being used twice.
You asked

Is it possible to set the isolation level at a connection level?I.e. not touch the global isolation level?

Yes, you can set the isolation level within the session:
SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL   {
    REPEATABLE READ
  | READ COMMITTED
  | READ UNCOMMITTED
  | SERIALIZABLE    }


I don't see how it is possible for tom to have sysadmin rights and not see a database.
Does tom still have sysadmin rights with with the new version of SSMS?  Normally refreshing or closing and re-opening SSMS is all that is required
Are you sure he is connecting to the correct server?  Sometimes there is a dev or test server with a similar name that the user also has rights on, but is not the same as the one you are working on.
The normal cause of the situation you describe is that 'tom' (assuming 'SQL Server login' as apposed to a windows account) has a different SID in one scenario than in the other.  This is corrected by using
SP_CHANGE_USERS_LOGIN
sp_change_users_login (Transact-SQL)
If you can go to his desk and look over his shoulder OR if he will screen share; you should see where he is doing something different then you are doing when you log on with what you believe is the same account.
Another test is you can use sp_who to see if tom is actually logged on to the server you think he is.


Database maintenance rebuilding indexes daily

Firstly you should take into account the page_count of the index. If the page_count is less than 1000 (or whatever you decide) then you should ignore the index.

We have a maintenance script which analyses the page_count and fragmentation of the indexes and follows the following guideline:-

less than 10% logical fragmentation, don’t do anything
between 10% and 30% logical fragmentation, reorganize it (using ALTER INDEX … REORGANIZE)
more than 30% logical fragmentation, rebuild it (using ALTER INDEX … REBUILD)(ONLINE= ON for Enterprise Editions)

This is a starter to find out where you are at present:-

SELECT dbtables.[name] AS 'Table'
    ,dbindexes.[name] AS 'Index'
    ,indexstats.avg_fragmentation_in_percent
    ,CASE
        WHEN indexstats.avg_fragmentation_in_percent < 10
            THEN 'NOTHING'
        WHEN indexstats.avg_fragmentation_in_percent >= 10
            AND indexstats.avg_fragmentation_in_percent < 30
            THEN 'REORGANIZE'
        WHEN indexstats.avg_fragmentation_in_percent >= 30
            THEN 'REBUILD'
        END
    ,indexstats.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables ON dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas ON dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
    AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
    AND page_count > 1000
    AND dbindexes.NAME IS NOT NULL
ORDER BY indexstats.avg_fragmentation_in_percent DESC

It all depends on your requirements. How is performance effected when you only rebuild once a week? Could you send the report once a week straight after a rebuild? I would base line the performance in a test environment (if possible), and if it doesn't degrade too much, only perform the index maintenance once a week.

An agent job could be something like:-

DECLARE @Indexes AS TABLE (
    ID INT IDENTITY(1, 1) NOT NULL
    ,TableNm VARCHAR(500) NOT NULL
    ,IndexNm VARCHAR(500) NOT NULL
    ,FragPerc DECIMAL(16, 3) NOT NULL
    ,RecAction VARCHAR(50) NOT NULL
    ,PageCount INT NOT NULL
    )
INSERT INTO @Indexes
SELECT dbtables.[name] AS 'Table'
    ,dbindexes.[name] AS 'Index'
    ,indexstats.avg_fragmentation_in_percent
    ,CASE
        WHEN indexstats.avg_fragmentation_in_percent < 10 --change all levels to your requirement
            THEN 'NOTHING'
        WHEN indexstats.avg_fragmentation_in_percent >= 10
            AND indexstats.avg_fragmentation_in_percent < 30
            THEN 'REORGANIZE'
        WHEN indexstats.avg_fragmentation_in_percent >= 30
            THEN 'REBUILD'
        END
    ,indexstats.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables ON dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas ON dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
    AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
    AND page_count > 1000
    AND dbindexes.NAME IS NOT NULL
ORDER BY indexstats.avg_fragmentation_in_percent DESC

DELETE FROM @Indexes WHERE RecAction = 'NOTHING'

DECLARE @sql AS VARCHAR(MAX)
DECLARE @whilecount AS INT = 1
DECLARE @rowcount AS INT = (
        SELECT MAX(ID)
        FROM @Indexes
        )
DECLARE @IndexNm AS VARCHAR(500)
DECLARE @TableNm AS VARCHAR(500)
DECLARE @RecAction AS VARCHAR(50)

WHILE @whilecount <= @rowcount
BEGIN
    SET @IndexNm = (
            SELECT IndexNm
            FROM @Indexes
            WHERE ID = @whilecount
            )
    SET @TableNm = (
            SELECT TableNm
            FROM @Indexes
            WHERE ID = @whilecount
            )
    SET @RecAction = (
            SELECT RecAction
            FROM @Indexes
            WHERE ID = @whilecount
            )

    IF @RecAction = 'REBUILD'
    BEGIN
        SET @sql = 'ALTER INDEX [' + @IndexNm + '] ON [' + @TableNm + '] REBUILD WITH (FILLFACTOR = 98, ONLINE = ON);' --change depending on SQL version
    END
    ELSE IF @RecAction = 'REORGANIZE'
    BEGIN
        SET @sql = 'ALTER INDEX [' + @IndexNm + '] ON [' + @TableNm + '] REORGANIZE;'
    END

    EXECUTE (@sql);

    SET @whilecount += 1
END


Unable to start oracle database after trying to enable archive log mode on

If there are a bad value in the spfile, you can create a temporary init file and use that.

Login using sqlplus:

C:\>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Feb 14 09:31:21 2018

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

Create a temporary pfile:

SQL> create pfile = 'c:\temp\init.ora' from spfile;

File created.

SQL>

Then you edit that file (c:\temp\init.ora) and remove/fix the parameter for archive destination.  Then you do:

startup pfile=c:\temp\init.ora

to get your database started, and then issue:

create spfile from pfile = 'c:\temp\init.ora';

and you should be good to go.


What's the point in mapping users in SQL Server Data Collection?

When you create the management data warehouse, the current login will be added to all three roles, making it an administrator of the database.

The dialog above lets you add other logins to the roles, to make those people readers, writers or administrators of the database.


Query sharded collection returns all the results from the primary shard AND from the other shards

I finally tested again to follow exactly the example at https://docs.mongodb.com/v3.6/tutorial/convert-replica-set-to-replicated-shard-cluster/ . And this time it did work... It took some time (don't know how much exactly) for rs0 to delete the moved documents, but after a few minutes (or tens of minutes) they were not there anymore.

I have tried to remember what I did differently on the first time. I'm wondering if I did insert the test data after rs0 became a part of the shard directly on the rs0 replica set. And from what I understand we must not insert/update data on shard replica sets directly.


Try this

R CMD INSTALL --configure-args='--with-oci-lib=/usr/lib/oracle/12.1/client64/lib
  --with-oci-inc=/usr/include/oracle/12.1/client64'  ROracle_1.1-11.tar.gz


How do I show the binlog_format on a MySQL server?

To see the current binlog_format value:

mysql> show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)

To change it:

mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 'MIXED';

Source: http://dev.mysql.com/doc/refman/5.1/en/binary-log-setting.html


MySql/MariaDB cannot change default datadir on Debian 9.1 server

You must set ProtectHome=false in the systemd config file in order to move your datadir to the /home directory.

According to Debian policy rules, you should set this option value in a custom file like /etc/systemd/system/mariadb.service.d/YOUR_CUSTOM_FILE.conf containing:

[Service]

# Prevent accessing /home, /root and /run/user
ProtectHome=false

Then reload systemctl daemon:

systemctl daemon-reload

After that you should be able to change MariaDB datadir from /var/lib/mysql to /home/mysql.


Error 1242(21000) after creating following trigger and performing delete operation

The easiest way to do this, is(for example undo_no 1)
UPDATE undostack SET
id=(SELECT id FROM (SELECT * FROM undostack) u WHERE undo_no=2),
descp=(SELECT descp FROM (SELECT * FROM undostack) u WHERE undo_no=2),
cat=(SELECT cat FROM (SELECT * FROM undostack) u WHERE undo_no=2),
dat=(SELECT dat FROM (SELECT * FROM undostack) u WHERE undo_no=2),
amt=(SELECT amt FROM (SELECT * FROM undostack) u WHERE undo_no=2)
WHERE undo_no=1;

By using (SELECT * FROM undostack) u Mysql generates a table which it uses to select your data.
usully i would say make this better in your pplication code, but five rows is very samll


Multiple inserts of batch insert over the wire?

I'm not saying this is the best way, but here is one way.

My solution uses xml which, I think, has a max size of 2gb.  Table Valued Parameters would have been ideal, but they are not supported in remote calls.

Since you are only generating around 500 records, you probably won't run into any size limitations.

On the source instance, create/load a table to play with. This will be the source data to send to the remote process.

DROP TABLE IF EXISTS dbo.customer
CREATE TABLE [dbo].[Customer] (
    [CustomerID] [int] NULL
    ,[Name] [varchar](30) NULL
    ,[RecordCreated] [datetime] NULL
    ,[RecordUpdated] [datetime] NULL
    ,
    ) ON [PRIMARY]
GO

INSERT [dbo].[Customer] ([CustomerID], [Name], [RecordCreated], [RecordUpdated]) VALUES (1, N'James', CAST(N'2017-11-01T16:16:21.297' AS DateTime), CAST(N'2017-11-01T16:52:02.427' AS DateTime))
GO
INSERT [dbo].[Customer] ([CustomerID], [Name], [RecordCreated], [RecordUpdated]) VALUES (2, N'John', CAST(N'2017-11-01T16:41:52.347' AS DateTime), CAST(N'2017-11-01T16:41:52.347' AS DateTime))
GO
INSERT [dbo].[Customer] ([CustomerID], [Name], [RecordCreated], [RecordUpdated]) VALUES (3, N'Sam', CAST(N'2017-11-01T16:50:25.430' AS DateTime), CAST(N'2017-11-01T16:50:25.430' AS DateTime))
GO
INSERT [dbo].[Customer] ([CustomerID], [Name], [RecordCreated], [RecordUpdated]) VALUES (1, N'James', CAST(N'2017-11-01T16:16:21.297' AS DateTime), CAST(N'2017-11-01T16:52:02.427' AS DateTime))
GO
INSERT [dbo].[Customer] ([CustomerID], [Name], [RecordCreated], [RecordUpdated]) VALUES (2, N'John', CAST(N'2017-11-01T16:41:52.347' AS DateTime), CAST(N'2017-11-01T16:41:52.347' AS DateTime))
GO
INSERT [dbo].[Customer] ([CustomerID], [Name], [RecordCreated], [RecordUpdated]) VALUES (3, N'Sam', CAST(N'2017-11-01T16:50:25.430' AS DateTime), CAST(N'2017-11-01T16:50:25.430' AS DateTime))
GO

Here is some TSQL to run on the source instance, but before you can fully execute it, you'll have to define the receiving stored procedure on the remote server.  I'll get to that.

--Declare XML variable and populate by selecting from a table using FOR XML AUTO
DECLARE @x XML = (
        SELECT *
        FROM Customer
        FOR XML AUTO
            ,TYPE
            ,ROOT('Root')
        );

--You cant ship XML via remote call, so declare a Varchar(max) variable and convert the XML
DECLARE @XVarchar VARCHAR(max)
SET @XVarchar = convert(VARCHAR(max), @x)

--Call the remote stored procedure and pass the xml
--My linked server is called sqlcompare

--NOTE: you cannot execute this until you have defined the
--receiving stored procedure on the remote server.
EXEC sqlcompare.scutility.dbo.[ProcessXmlRows] @xVarchar

Now, on the remote server, create a stored procedure to receive the passed xml.  In the following example, I'm using OPENXML to basically shred the xml and inserting directly into the remote table. You'd probably need a cursor to help you weed out the good and bad rows. Note, that I have also created the customer table on the remote server (this allows me to reference that table in the OPENXML using the WITH clause.

CREATE PROCEDURE [dbo].[ProcessXmlRows] (@Xml xml)
AS
BEGIN
DECLARE @intXMLDocID INT -- xml document handle

--Prepare the XML that was passed into the SP
EXECUTE [master].dbo.sp_xml_preparedocument @hdoc = @intXMLDocID OUTPUT
    ,@xmltext = @xml;

--Using OPENXML, and referencing the existing customer table, insert the rows
insert into Customer
SELECT *
FROM OPENXML(@intXMLDocID, '/Root/Customer') WITH customer

select * from Customer

END
GO

Note, that I'm using with customer on the OPENXML.  From the documentation on OPENXML

  The WITH clause provides a rowset format (and additional mapping
  information as required) by using either SchemaDeclaration or
  specifying an existing TableName.

At this point, you should be able to successfully execute the source T-SQL to call the remote stored procedure and process the data.


DBCC CHECKDB firing every 20 - 60 seconds

The reason you are seeing this:

Starting up database 'dbname'
CHECKDB for database 'dbname' finished without errors

Is because you have your database option set for AutoClose.

To turn off AutoClose, do this:

alter database YourDatabase
set auto_close off
go

What AutoClose does is shut down the database after the last user process disconnects.  And then database automatically "reopens" when the next user connection attempts to connect.

Typically it is best practice to keep AutoClose OFF, because of the obvious latency to spin up the database again.  I understand from your question that this is a development environment, but you shouldn't need it there either (and you definitely don't want that in your production environment).

Furthermore DBCC CHECKDB is not actually "firing every 20 - 60 seconds" per the question title, it just appears that way!

Since SQL Server 2005 the dbi_dbccLastKnownGood value (if present) is reported in the error log every time the database is started. So you are just seeing the same historic information presented repeatedly.

This topic is covered in the article Why is SQL Server running DBCC CHECKDB against my databases at Server Startup?


It depends on the database size.
You can run
dbcc checkdb ('master') WITH ESTIMATEONLY

To get an estimation of the space requirement. Usually, this is not really accurate but I guess it's better then nothing.


Postgres 10 Backup: Should I run the backup in the master or in a slave?

You can run pg_basebackup against the slave server just as well as against the master.


Postgresql - sequences bigger than bigint

As pointed out by @a_horse_with_no_name, bigint is good enought. I think 29 million years is reasonable (hehe).

The math checks. Should have done it before.


Modifying legacy database tables that use composite primary keys

Adding new columns to tables might break the legacy app, because DML might be expecting a certain number of columns of certain types in a certain order in each table.

There aren't really any elegant solutions for this kind of problem. You can make new tables for the new apps and duplicate the data with triggers, but that creates unneccessary complexity and of course duplication. You can make new keys in new tables and use views to present the data to the new apps as one table, but then you're stuck with only using procedures for inserts and updates from the new apps.

In the long run you will probably be better off throwing out the legacy app.


Whenever you are trying to concatenate a string with NULL, NULL is always returned, and for some reason @ is interpreted as NULL in MySQL. That is why the query returns NULL.

Since you wrap the string in the shell with " and you use it inside the query, the ones inside the query will be removed (literally, it is 3 strings that is being passed to MySQL).

You need to escape the quotation marks inside the query.

Like this:

# mysql test -e "SELECT CONCAT(mail_name,\"@\",name) AS email_address,accounts.password FROM mail LEFT JOIN domains ON domains.id=mail.dom_id LEFT JOIN accounts ON accounts.id=mail.account_id LIMIT 2;"

or

# mysql test -e "SELECT CONCAT(mail_name,'@',name) AS email_address,accounts.password FROM mail LEFT JOIN domains ON domains.id=mail.dom_id LEFT JOIN accounts ON accounts.id=mail.account_id LIMIT 2;"


How to improve simple update statement causing blocking issues, inneficient index scan in query plan

There are a few easy things to try:

Stop passing in GUIDs as NVARCHAR

It looks like the log table has id stored as a UNIQUEIDENTIFIER or VARCHAR. You should take the N prefix off the GUID literal string.

The idx_mail_dispositionindex might not be ideal

It's unclear what the index definition is, but if id isn't the leading column, you may not get a seek to the id you're looking for anyway. You could try just creating an index on id, if one doesn't exist already. If it's truly unique, create a unique index on it. The single row estimate makes it attractive.

The reason this plan is slow is because you need to read the entire index because of one or both of the reasons above.

For more related information, check out my recent blog post about this: Index Key Column Order And Locking


HA - MySQL Master Master replication - Duplicate entry errors

This is not a hack. It is the intention of them:
Use auto_increment_increment and auto_increment_offset

http://dev.mysql.com/doc/refman/5.6/en/replication-options-master.html#sysvar_auto_increment_increment


Postgresql Two-phase Commit Transaction Identifier

So my solution was to simply not use the same transaction identifier across all databases.  The Transaction Manager can keep track of the various identifiers and commit the databases accordingly.

Seems ridiculously obvious so please correct me if I'm wrong.


You also have a table holding unique stations with identifiers, Could look like this:

CREATE TABLE uniq_stations (station_id text);
INSERT INTO uniq_stations VALUES
('001'),
('002'),
('003');

There will be more columns, which are irrelevant for us.

This should be much faster then:

SELECT station_id, s.value, date
FROM   uniq_stations u
CROSS  JOIN (
   SELECT generate_series (timestamp '2014-01-01'
                         , timestamp '2014-01-04'
                         , interval  '1 day')::date
   ) d(date)
LEFT   JOIN stations s USING (station_id, date)
ORDER  BY station_id, date;  -- optional

db<>fiddle here

You do not need a LATERAL join at all, the date series is the same for every station. Only a CROSS JOIN to build the complete Cartesian product of stations and days, then a LEFT [OUTER] JOIN to existing combinations in table stations (an unfortunate table name for its content, btw.). LATERAL joins are great, when needed. But plain joins are faster.

Also, this fills in stations with all days missing, which would not work at all without uniq_stations. You may or may not have such cases.

One of the expensive pieces in this puzzle is identifying unique stations. A task we can skip completely if we can use the added uniq_stations providing what we need. Else we might use DISTINCT ON or a recursive CTE to make use of a matching index. See:

Optimize GROUP BY query to retrieve latest row per user
Select first row in each GROUP BY group?

Still more expensive than reading unique rows from a table, but much faster already than what you had. Which was a grand waste of CPU cycles, frankly.

Finally, a multicolumn index on stations (station_id, date) should deliver top notch performance even with a big table stations. (Using a higher percentage of rows from that table makes the index less important.)

There is a reason I use generate_series (timestamp, timestamp, interval):

Generating time series between two dates in PostgreSQL

Related:

Filling in missing dates in record set from generate_series()
Join a count query on a generate_series in postgres and also retrieve Null-values as “0”

Aside: Your station_id should probably type integer. Faster than text, and smaller, too if numbers go beyond 999.


SQL query that concatenates values from duplicate rows in a single table

Group_concat as in MySQL is not available in SQL Server up to SQL Server 2016. vNext will introduce STRING_AGG however that provides equivalent functionality.

You have some manual options though:

build a scalar function that takes an employee name/id as parameter and shows concatenated role values and apply this function to each employee
use xml functions
use an already built aggregate (similar as first option)

A solution for your current case (just took Brad's answer from SO and customized for your table) would be:

SELECT name, LEFT(roles , LEN(roles )-1) AS roles
FROM employee AS extern
CROSS APPLY
(
    SELECT role + ','
    FROM employee AS intern
    WHERE extern.name = intern.name
    FOR XML PATH('')
) pre_trimmed (roles)
GROUP BY name, roles;

These details are gathered from different sources:

Simulating group_concat MySQL function in MS SQL Server 2005? - on StackOverflow.com
Emulating MySQL’s GROUP_CONCAT() Function in SQL Server 2005 - blog
GROUP_CONCAT in SQL Server - blog
T-SQL Equivalent For Group_Concat() Function - MSDN


How to create a database or role in pgAdmin4?

This works for me if the user I am logged in as has either the superuser attribute, or the createrole and/or createdb attribute.

When you create a server (probably a poor wording, you are't initializing a server, but connecting to an existing one), pgAdmin4 definitely asks you for more information than just the master password.  Like what user you want to connect as.

If you want to connect as different users at different times, you need to "create server..." for each one.


Running reports across multiple MySQL databases with the same schema

Not everything can be done in standard commands, or in complicated combinations of SQL.

I would write in my application coding language (PHP, Java, ...) SQL to fetch the list of databases from information_schema, then iterate through that list, doing the analytics.


csv file with fields having commas in them

You just need to add OPTIONALLY ENCLOSED BY '"':

LOAD DATA INFILE 'filename.csv' INTO TABLE table_name
  FIELDS TERMINATED BY ','
  OPTIONALLY ENCLOSED BY '"'
  IGNORE 1 LINES;

Documentation link.

File:

n1,s1,s23,n2,s4,s56
1,string1,"str2,str3",2,string4,"str5, str6"

Table and test:

mysql> create table tbl_name (
                               n1 integer,
                               s1 varchar(10),
                               s23 varchar(10),
                               n2 integer,
                               s4 varchar(10),
                               s56 varchar(10)
                               );
Query OK, 0 rows affected (0.06 sec)

mysql> LOAD DATA INFILE 'c:\\temp\\data.txt' INTO TABLE tbl_name
    ->   FIELDS TERMINATED BY ','
    ->   OPTIONALLY ENCLOSED BY '"'
    ->   IGNORE 1 LINES;
Query OK, 1 row affected (0.01 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0


Mysql performance for a nullable unique id column in a large table that references a record in another table

Perhaps a cross-reference table would be the most prudent way to go.  One that has the ID from contacts, and the ID from members.  This way you know who has converted, and can keep the table as small as necessary.

As an example (I use SQL Server, not MySQL):

CREATE TABLE Members
(
    MemberID INT
    , MemberData varchar(255)
);

CREATE TABLE Contacts
(
    ContactID INT
    , ContactDate datetime
    , ContactData varchar(255)
);

CREATE TABLE MembersContactsXRef
(
    MemberID INT
    , ContactID INT
);

I would insert rows into MembersContactsXRef each time a conversion occurred for a given member.  Both fields in MembersContactsXRef could be foreign keys.


What is likely to break?
How risk-adverse are you?
Do you need to repair the failure, or just discover it?

A "parity" check can only discover failure, hence it is not fault-tolerant.

Many people decide that the main failure point is the disk subsystem, so they use RAID.  In that context, you can assume that parity is can be used to repair -- but only because there is some other mechanism to say "this drive failed".

But what if the motherboard dies?

So you use Replication and Master-Slave with the two servers sitting next to each other.

But what if the power to the building goes out?  Or a tornado hits the two servers?  Or ...

So you put the Slave in another data center.

(I could continue this silly story, but I won't.)

You can put two "instances" of MySQL on the same server, then have one replicate to the other.  Then put a lot of spin on how great your solution is.  (Never mind that practically any failure will take out both copies.)

Or you could spend a few bucks and rent Amazon space for the other server.  Then you can honestly brag about the "fault-tolerance".

After Bounty Edit

Use Engine=InnoDB; this gives you simpler recover from server crash.

After loading the data, take a dump (mysqldump or other) of the static table, and store it elsewhere.  This is for "disaster recovery" from floods, meteorites, software glitches, disk failure, etc.  Reloading would be manual and take some time (but you have not put limits on that).

Those are simple measures, and effectively cover virtually all disasters.  If I were to list other things that can go wrong with a mysql setup, "parity" does not show up as a part of any solution.

To finish the task you have, set up your disk with RAID-5.  Three drives is the minimum.  You could probably fake it with software raid and partitions of a single drive.  However, this would make useless for recovering from any kind of failure; instead it would show the use of "parity".

"Checksums" are more often used for catching (but not correcting) errors.  This is typically 4-8 bytes overhead for 512-16KB bytes of data.  That is not, technically, "parity", but it is more efficient.

One parity bit per byte gives you error detection, but not error correction.  See SECDED for correction.  That needs, for example, 8 bits on a 64-bit 'word'.  Seymour Cray said "parity is for farmers", but eventually he implemented SECDED in 'core' memories.  (This was in the '70s.  Does your prof date back that far?)


Excessive CPU use in vendor SQL Server DB

Indexes haven't been maintained or rebuilt so there are hundreds sitting at 30%+ fragmentation... this is my initial suspect of massive and constant CPU use...
and
Can out of date statistics and highly fragmented indexes throughout the DB cause the excessive CPU use?

This is partially true. Index fragmentation wont cause HIGH CPU. Internal Fragmentation means that you have lots of free space on the pages and it will take more time to scan the index. This will incur more disk IO and will require more memory to store the index (due to free spaces in the index pages) which means more wasted space in buffer pool.
Bad statistics will cause the query optimizer to generate inefficient(bad) plans causing a degraded performance e.g. queries that took 2 secs to complete will take 2 mins or 2 hours, etc as sql server will make a bad guess (e.g. will estimate 1 row as opposed to actual 2M rows) and may choose an inapproprate join performing high number of reads or can choose a bad join e.g. nested loop where a hash or merge join would have been a better choice. Bad (outdated or old) stats will peg your CPU at a much higher level.
So keeping your Statistics and Indexes defragmented will definitely help. Instead of crafting your own solution, I would recommend to use Ola's Index Maintenance solution.
Refer to Kendra's excellent post : Why Index Fragmentation and Bad Statistics Aren’t Always the Problem (Video) ?
Remus Rusanu has a really good blog post on : The Bizzaro Guide to SQL Server Performance (caution : Don't follow it !)

Does the combination of wait stats listed below from this system discredit the index fragmentation explanation?

I believe that there might be more things to address at your sql server configuration level than just to worry about Index fragmentation. Also CXPACKET wait in itself is not a problem.
Things to check :

MAXDOP - set it away from default to a more sensible value.
Make sure you have TF 1117 and 1118 turned on for addressing tempdb contention. and have multiple tempdb data files as per kb-2154845
Start looking at your queries - Top 20 CPU Consumers to see if they are using proper indexes , not doing any implicit conversions, etc.
Turn ON optimize for ad hoc workloads
Refer to MS KB 2964518: Recommended updates and configuration options for SQL Server 2012 and SQL Server 2014 with high-performance workloads
Make sure that the server is using  High Performance power plan option.

You can use Glenn Berry's diagnostic queries - 2012 version
-- Signal Waits for instance  (Query 27) (Signal Waits)
SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2))
AS [% Signal (CPU) Waits],
CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2))
AS [% Resource Waits]
FROM sys.dm_os_wait_stats WITH (NOLOCK) OPTION (RECOMPILE);

-- Signal Waits above 15-20% is usually a sign of CPU pressure

and
-- Top Cached SPs By Total Worker time (SQL Server 2012). Worker time relates to CPU cost  (Query 44) (SP Worker Time)
SELECT TOP(25) p.name AS [SP Name], qs.total_worker_time AS [TotalWorkerTime],
qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.execution_count,
ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second],
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count
AS [avg_elapsed_time], qs.cached_time
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);

-- This helps you find the most expensive cached stored procedures from a CPU perspective
-- You should look at this if you see signs of CPU pressure

Additionally Joe Sack talks about Troubleshooting Methodology for SQL Server CPU Performance Issues


Directly import a csv gzip'ed file into SQLite 3

I found that sqlite3 custom init script can have meta-command as well as SQL statement:

#!/bin/sh

commandfile=$(mktemp)

# create temporary init script
cat <<EOF > $commandfile
.mode csv tablename
.import /dev/stdin tablename
EOF

# import
bzip2 -d -c huge_compressed.csv.bz2 | sqlite3 --init $commandfile dbname.db


Insert Only first Occurence in Oracle table

MERGE INTO PEOPLE e using
(SELECT
DISTINCT p.NAME,
p.TIME,
l.NICKNAME,
pt.STATE
from PBO p
LEFT JOIN PASST pt ON pt.NAME = p.NAME
LEFT JOIN LIEN l ON l.NAME = p.NAME
WHERE not exists (
    select * from PEOPLE f
    where  f.NAME= pt.NAME
  )
AND
p.TIME> '25/05/20 00:00:00') d
on (e.name = d.name)
WHEN NOT MATCHED THEN
  INSERT  (NAME, TIME,NICKNAME, STATE) VALUES (d.name, d.time, d.nickname, d.state);

Or use the ignore_row_on_dupkey_index hint.
INSERT /*+ ignore_row_on_dupkey_index(people(name)) */
INTO PEOPLE( NAME, TIME,NICKNAME, STATE)
SELECT
DISTINCT p.NAME,
p.TIME,
l.NICKNAME,
pt.STATE
from PBO p
LEFT JOIN PASST pt ON pt.NAME = p.NAME
LEFT JOIN LIEN l ON l.NAME = p.NAME
WHERE not exists (
    select * from PEOPLE f
    where  f.NAME= pt.NAME
  )
AND
p.TIME> '25/05/20 00:00:00'
;


Should I switch my database LOG volumes from IO1 to ST1

(First two paragraphs are repeats of my comments.)

When Amazon specifies "log processing" as a use case, I'd suspect they're talking about infrequently-flushed IIS logs and the like, not database logs. And although ST1 is specified as not suited for "small, random" workloads, my reading of http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/EBSVolumeTypes.html#inefficiency would be that it's not suited for "small and/or random" workloads. But this, and your questions of how the limits are enforced/calculated, are probably something you need to get clarified from AWS.

"Will SQL 2014 automatically increase the size of units it writes to the log in response to an increase in log file latency?" I'm pretty sure it won't by default. But if you can afford some data loss risk, then delayed durability may be just what you need: https://sqlperformance.com/2014/04/io-subsystem/delayed-durability-in-sql-server-2014

If you do end up moving to ST1, AWS's online conversion option sounds far riskier than "Add a new ST1 volume to the mirror, take the mirror offline, move the log file from the old volume to the new one, then bring the mirror back up, failover, and repeat with the principal", which should work well for this. (Moving or dropping the primary log file with the database online is not an option.)


Syncing data from primary member to secondary after election

It depends how far behind it fell and what status you have it in your config file at the time of resync.

Basically if you've been down so long that it can't catch up with the OpsLog or the OpsLog no longer has the transactions it needs due to it being removed over time you'll want to resync yourself.

Check out the docs for your version:

Restart the mongod with an empty data directory and let MongoDB’s normal initial syncing feature restore the data. This is the more simple option but may take longer to replace the data.

See Procedures.

Restart the machine with a copy of a recent data directory from another member in the replica set. This procedure can replace the data more quickly but requires more manual steps.

See Sync by Copying Data Files from Another Member.

Let us know if you have more questions. Good luck.  Test test test!


SQL Server Backup Database not .bak file format

Take a look at the command that is generating the backups (it could be a job, a maintenance plan, 3rd party tool, etc.).

You can give the file any extension you like, including no extension. Windows Explorer might not know what on earth it is, but SQL Server will still be able to restore it just fine.


Not even close to comprehensive, but I'd start with:
1.  Pull the ethernet cable(s) for your public ip interface on your primary/active node.  Confirm the failover.
2.  Pull the SAN fibre cable(s) for your active node. Confirm the failover.
3.  Pull the power cable(s) for you active node.  Confirm the failover.

Those represent the main types of failure that MS Clustering will compensate for in the first place...

I think I'd have my real/prod db detached or offline while I played these games.*


SQL queries break our game! (Back-end server is at capacity)

What idexes do you have on this table? If the dbms engine is doing a full table scan, it will completely load a cpu and take a long time. If you have a 2 cpu system and all your data is in RAM, then you'll see 50% cpu usage.

Please create and index, such as

CREATE INDEX BlueBoxEngineDB.TransferIdx1
ON BlueBoxEngineDB.Transfer(Amount,FromUserId,Status);

and then run again your query. It should complete in a glimpse.


Mysql config for my.cnf make sofware not response

max_connections=20000  -- too large; make it only 200
query_cache_size=1G    -- REALLY BAD! -- make it only 50M
table_open_cache=10048  -- too high; try 3000
long_query_time=7  -- Lower to 2, turn on slowlog, keep an eye on it
innodb_buffer_pool_size=10G  -- OK with 16GB of RAM; not ok with 5GB of RAM

On 100

It is not practical, nor efficient, to try perform 100 queries simultaneously.  Because of that, there is no way to "configure" it into existence.  It is better to do...

Combine all the information into a single AJAX call -- the back and forth for AJAX is non-trivial, possibly even more costly than the database queries themselves.
If all the INSERTs are into the same table, then use a single "batch" INSERT with 100 rows.  This will run 10 times as fast as a 100 1-row INSERTs.

If it is not 100 similar inserts, please provide more details about the queries.


Where can I get the installation software for Oracle 8i DB?

If you have 9i or 10g, you can try starting the database with either of those provided you do a startup upgrade. You should then do a manual upgrade. Of course you need to make sure that the endian is the same, and ideally the same type. ie. Linux/Windows/Unix.


Random characters after table name in FROM clause of SELECT query

As @dezso points out it's a table alias. You can also use as which makes it a bit more comprehendable:

SELECT * FROM client_messages as c

Why use an alias? It makes it easier to refer to the table. Assume the following query:

SELECT *
FROM client_messages
JOIN client_address
    ON client_messages.client_id = client_address.client_id

Using table aliases:

SELECT *
FROM client_messages as cm
JOIN client_address as ca
    ON cm.client_id = ca.client_id

so it's a bit shorter which is nice. When using self joins it becomes necessary to use aliases:

SELECT *
FROM client_messages as cm1
JOIN client_messages as cm2
    ON cm1.x = cm2.y
WHERE cm1.... = ...

Otherwise there is no way to determine which one of the two tables one is referring to.

As a side-note, avoid using SELECT * other than for ad-hoc querying.


Oracle 11g XE to 18c XE - What is the difference? Is 18c can be used like 11g XE?

Not sure why they force us to download 18c XE?

They're not forcing you to download 18c XE.  They're just making it harder for you to download older versions

  Is it okay to download and use like 11g XE?

Yes.
I suggest you still read the License Agreement for 18c XE.

  What are the differences between them?

Resource Limits have been increased (information can be found in License Agreement section License Rights)

2GB of RAM (up from 1)
12GB of user data (up from 11)
2 CPU Threads (I don't know if this is the same as 2 CPU cores.  11g was limited to 1 CPU core)

New database features

The ability to handle SCD Type 4 data via Flashback Data Archive (11.2.0.4+)
The ability to handle SCD Type 2 data via Temporal Validity (12c+)
MATCH_RECOGNIZE (12c)
other 12c features
other 18c features

"more or less all EE Features" (list from 18c XE Licensing Information)

EE feature Virtual Private Database (VPD)
Enterprise Manager (missing in 11g XE)
Java inside the database (missing in 11g XE)
Partitioning (EE add-on)
Multitenet (EE add-on - XE is limited to 3 PDBs)
Advance Compression (EE add-on)
In-Memory column store (EE add-on)
Advanced Analytics (EE add-on)

  (I can't find any solid evidence why I should use 18C??)

Because 11.2.0.2 (11g XE) is known to be buggy and full of security holes.

anti-upgrade reasons:  Minimum supported Oracle APEX version is 5.1.4


DECIMAL and NUMERIC datatype in Postgres

From the documentation

  The types decimal and numeric are equivalent. Both types are part of
  the SQL standard.

float, real, ... and the rest of the floating point types are inexact. This happens in all programming languages. As you have a type like BigDecimal in Java to represent exact numbers, in PostgreSQL you have decimal and numeric.

They are commonly used to represent currency.


How do I use the `COPY` command with JDBC

It works perfectly when I use a buffered reader but i'm trying to see if there's a way i can get i to work using the databases COPY FROM method. It works perfectly on the computer where the database is located but my other computer gets an error.

No you have to use a BufferedReader, or InputStream

COPY opens a file handle on the server. It just needs a path, because it can open and manage its own file handle, and that's more efficient.
For client-side COPY, you have to use libpq PQputCopyData. Internally, client-side COPY is COPY FROM STDIN. The client has to send the data. This is what psql's \COPY does internally. In Java, the function that calls PQputCopyData is copyIn which requires a BufferedReader, or InputStream.


Performance Monitoring for Prepared SQL?

You would most likely need to trap statement-level events since the "completed" RPC call for sp_execute isn't going to show the batch itself.

Also, you need more than just the SPID. Session IDs get re-used, hence it is not granular enough of a key. You will also need the Connection ID since that is more unique and is what the cached array of prepared statements is tied to.

Finally, what is the impetus for moving to prepared statements? The only benefit of them is saving some number of bytes in the network request if your app is frequently sending over the same parameterized queries. It is generally better to call Stored Procedures so that you are not sending over the queries each time anyway. I did recently here someone suggest that prepared statements could be faster than Stored Procedures, but it was a very specific use-case: simple DML statements executed at least 1 - 2 BILLION times per day (i.e. the same INSERT INTO dbo.Table (@1, @2, @3); executed 2 billion times with different values).

If you are executing non-parameterized ad hoc queries, then using prepared statements is worthless at best, and at worst it is a step backwards given how much harder they are to monitor.

Please also see my answer, also here on DBA.SE, to the following question:

What is the sense and benefit of using SqlCommand.Prepare()?


#1093 - You can't specify target table 'R' for update in FROM clause

Based on the previous question and this, the following should do what you want.  As with Pieter's update it doesn't assume that the ID's are in sequential order, rather it uses the ReqTime to determine order.

UPDATE Visits R
  INNER JOIN (
    SELECT ID,
      TIME_TO_SEC(TIMEDIFF(
        (SELECT B.ReqTime
         FROM Visits B
         WHERE B.ReqTime > A.ReqTime
         ORDER BY B.ReqTime
         LIMIT 1
         )
        ,A.ReqTime)) Duration
    FROM Visits A
   ) N ON R.ID = N.ID
SET R.Duration = N.Duration
WHERE Duration IS NULL;


How to join 2 tables when calling a count on the second table

select coaches.user_id, coalesce(counted.cnt,0) cnt
from coaches
left join (select count(coach_id) cnt, coach_id
           from coach_assignments
           where deleted_at is not null
           group by coach_id) as counted
     on coaches.user_id = counted.coach_id
order by 2 asc


Firstly, the correct term for this is an audit table (or maybe audit history). Transaction logging is something entirely different (that's a core part of the DBMS which logs all active transactions to guarantee the ACID properties) - the DBMS transaction log (which is in a binary, very-hard-to-read format) is reused once it's no longer needed ("once it's no longer needed" is a bit vague, but a full dissertation on transaction log semantics is a bit beyond the scope of this answer), where an audit history stays forever, as long as you don't clear the table.

To answer the actual question - it should be done inside the database (triggers being the easiest way). The basic reason for that is you want anything that's integral to the data (i.e. constraints, security rules, audit history etc.) to happen, no matter which application is using the database.

Additionally:

What data type are you using for the Old_Value and New_Value fields?
The style of audit table you're using there is going to lead to one row in the table for each field of each row you insert/update/delete, which means your audit history table is going to grow as fast as all of your other tables put together (and then some). You'll need to keep an eye on it to make sure it doesn't spiral out of control (I'd recommend clearing the table periodically - only after you've backed it up, of course).
Another option (which I'd probably use if you need to refer to audit history frequently) is to have an audit table for each table in your database - so if you have Person(ID, Name), you'd have audit_Person(whoChanged, whenChanged, ID, Name), and then on update or delete, store the state of the row before the change (you don't need to do insert because you can just look in the table for the current state), along with the login of the user that changed it and the time.

This means no more audit table with 2 billion rows and makes querying for audit history significantly more intuitive (you get entire rows back in one piece, instead of having to go field-by-field) but does have the downside of duplicating schema changes (if you add a column to Person, you also need to add it to 'audit_Person'), and quite possibly being larger overall than the single-table approach.


We insert rows, not columns, in a table.

You need a query that returns both results, combined in 3 rows. Assuming that the term2_id values should match:

INSERT INTO temp_table
  (dist0, dist1)
SELECT
  min(term1) FILTER (WHERE distance = 0),
  min(term1) FILTER (WHERE distance = 1)
FROM graph_path
WHERE distance IN (0, 1)
GROUP BY term2_id
ORDER BY term2_id ;


sql query to calculate a difference between cols in two different tables, grouped by common identifiers on the row

To get results even if only one of the two tables has data, then you need FULL JOIN. The COALESCE() function can be used for the result when there are umatched rows (and you want the produced NULL to become 0 for example).

Assuming that (id) is UNIQUE in each table, I don't see why you think you need GROUP BY at all.

The query:

select
    coalesce(t1.id, t2.id) as id,
    coalesce(t2.value, 0) - coalesce(t1.value, 0) as diff
from table1 as t1
     full join table2 as t2
     on t1.id = t2.id ;

and slightly simpler, with USING:

select
    id,
    coalesce(t2.value, 0) - coalesce(t1.value, 0) as diff
from table1 as t1
     full join table2 as t2
     using (id) ;

Test at dbfiddle.uk.


Where is the information about what files each database stored for SQL Server 2008 R2

SQL Server maintains this in sys.master_files, after you have restored the master database
When SQL Server starts up, you'll see a lot of suspect databases.

You can attach the MDFs and LDFs using the info in sys.master_files


Connect to postgres with different privileges

From the arch guide

sudo pacman -S postgresql
su - postgres
initdb --locale $LANG -E UTF8 -D '/var/lib/postgres/data'
exit
Start postgres service sudo systemctl start postgres.service

Check admin stuff

Set postgres system user pass passwd postgres PASS
su - postgres
Optional, you could set connection host rules vim data/pg_hba.conf
psql
Set admin DB pass \password PASS

Create db create database dbone;
Add users, grant privileges

create user writer --interactive -P;
create user reader --interactive -P;
give all privileges to writer user grant all privileges on database "dbone" to writer;
Quit \q
Connect to your db psql dbone
For tables in the public schema of your db, automatically give select privileges to reader user ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO reader;
\q,   exit


Installing SQL Server Integration Services on Server Core

This is from the Administering Microsoft SQL Server 2012 Database book. Make sure you have the initial SQL Server engine installed. Your syntax is exactly right though. I went through it again 10 minutes ago.

Also your ISO is from SQL Server 2014. Try mounting the SQL Server 2012 version.


What are the consequences of enabling slow_query_log and flushing the cache on mysql?

When the slow query log is enabled, the server has to check the time after each executed query (thats quite trivial) and if needed, write the query and some info to the log file - if you have really many queries, that might need some IO capacity, but you can enable it temporarily and monitor the situation, to quickly disable it if needed. It should not be a problem unless your IO is already maxed out.

Flushing the query cache (the flush query cache; command) actually means "defragmenting" of the allocated space. It may take even few seconds depending on the cache size (hopefully it is not bigger than 128MB?) and it might block some queries for that time.

For "clearing" the cache there is a reset query cache; command, that will imho take similar amount of time as the flush.

Usual advice is to disable the query cache altogether (would need a config change and a restart), because it scales badly in parallel environments in its current implementation.


How to copy a PostgreSQL database at a filesystem level between servers

This is the script I ended up having to write to transfer all the files, meant to be run from the destination server.

#!/bin/bash
set -i

# Ensure databases on both servers are stopped.
sudo service postgresql stop
ssh -t myuser@oldserver "sudo service postgresql stop"

# Ensure our user owns postgres's files so our rsync call can access them.
ssh -t myuser@oldserver "sudo chown -R myuser:myuser /etc/postgresql"
ssh -t myuser@oldserver "sudo chown -R myuser:myuser /var/lib/postgresql"

# Copy down all the files.
sudo rsync --progress -azv --delete myuser@oldserver:/etc/postgresql/9.5/main/ /etc/postgresql/9.5/main
sudo rsync --progress -azv --delete myuser@oldserver:/var/lib/postgresql/9.5/main/ /var/lib/postgresql/9.5/main

# Fix permissions.
ssh -t myuser@oldserver "sudo chown -R postgres:postgres /etc/postgresql"
ssh -t myuser@oldserver "sudo chown -R postgres:postgres /var/lib/postgresql"
sudo chown -R postgres:postgres /etc/postgresql
sudo chown -R postgres:postgres /var/lib/postgresql

# Fix a missing ssl cert that pg uses for connections.
cd /etc/ssl/certs
sudo make-ssl-cert generate-default-snakeoil --force-overwrite

# Bring the server back up.
sudo service postgresql start

Rsync reported some errors, but I'm browsing my databases via psql and pgadmin3 and so far everything looks correct.

The fix for the SSL error I found here.


Using the examples from SQL Geography data type column in GROUP BY clause, I was able to group by a geography location.  See if this works for you.

--demo set up
Declare @Locations table (ID int, StaticLocation GEOGRAPHY)
Declare @Capacity table (LocationId int, SpareCapacity int)
insert into @Locations(id, StaticLocation) values
(1,geography::Point(41.878899,-87.636297, 4326)),
(2,geography::Point(40.749014,-73.985317, 4326))

insert into @Capacity(LocationId,SpareCapacity) values
(1,100),
(1,1000),
(2,200),
(2,2000)

--Solution
SELECT GEOGRAPHY::STGeomFromText(L.StaticLocation.STAsText(), 4326) as StaticLocation
    ,sum(SpareCapacity) as SumSpareCapacity
FROM @Capacity UC
JOIN @Locations L
    ON L.Id = UC.LocationId
--WHERE u.StaticLocation.STDistance(geography::Point(53, -2, 4326)) / 1609.344 < 10
GROUP BY L.StaticLocation.STAsText()

| StaticLocation                                 | SumSpareCapacity |
|------------------------------------------------|------------------|
| 0xE6100000010C821FD5B0DF5F444057CC086F0F7F52C0 | 2200             |
| 0xE6100000010C59BE2EC37FF04440BF620D17B9E855C0 | 1100             |


Access SQL Server Configuration Manager on a remote server

All I wanted to do was add a startup parameter to SQL Server and to do that, I can use this script.

Copy the script to the remote server
Connect to the remote server on powershell

Enter-PSSession -ComputerName some_server_with_sql  -Credential some_user
Add the Traceflag startup parameter

[some_server_with_sql]: PS C:\Scripts> .\AddStartupParameters.ps1  '-Tsome_trace_flag_number'

And you are done,

Then to check, you can run this SQL query

SELECT * FROM sys.dm_server_registry WHERE registry_key LIKE '%Parameters'

Et Voilà as the French would say.


Use single table with foreign key or two twin tables for text entries?

Personally I would avoid the twin table approach most, but not all, the time.  It leads to duplication of information and if you find that a wording of a question is bad in the db, it takes a bit to clean it up.  The cleaner solution would be to have the answer table just join user and question and then you only have one update/insert required.

This being said there can be some valid reasons for the twin table approach.  The major one is that if the questions are relatively free-form, frequently changing, etc. and you do not need to track answers across a given form of the question, then a twin table approach forms essentially an "answer log."  It isn't great for analytics if you are just looking at questions and answers, but it might be in some other fields.  I could imagine linguistic research doing well with a twin table approach.   For example it makes it easier to look into questions relating to effects of wording on response (rather than substantive answers to substantive questions).

So in general, I would recommend against a twin table approach unless you know you need it.  A simple answer table joining both the question table and the user table is sufficient for the most common use cases and it is far more useful in most cases.


How to grant all privileges to a user from root user in MySQL on command line

This worked for me:

mysql test -e "grant all privileges on \`test\` to 'testy'@'%' identified by 'pass\!word' with grant option"

Name a database to connect to. In this example I named test before the -e flag. Many SQL statements won't run if you don't "use" a default database.
If you grant the privileges to a database that has special characters (e.g. yours has a dash - character, you need to delimit the database name in back-ticks, not single-quotes.
Back-ticks also have meaning to the shell, that is command-substitution. You need to use backslash to escape the back-ticks so the shell won't do command substitution. The shell will strip off the backslashes before it passes the SQL command to mysql.
If you grant to all the tables in a database, you don't need to use database.*. You can just name the database.
If the password string contains some characters that are special to the shell, you need to backslash those too. For example, ! is the shell character for history substitution.


Understanding & Resolving Deadlock in SQL Server 2008 R2

Looks like multiple statements in a transaction.  One row is the target of the first statement of the first transaction and the second statement of the second transaction.  And vice-versa: the same row is targeted by the second statement of the first transaction and the first statement of the second transaction.

Is there any reason why you are using multi-statement transactions?

If so, can you "order" the statements within the transaction so that they always affect rows in a predefined order?  In a row with an id identity key, if consecutive statements affect higher id transactions, the chance for conflict is greatly reduced.


Changing the partitioning column in SQL Server

Changing the partitioning column on a table with hundreds of millions of rows is likely to be a bit painful, since all rows in the table, and perhaps any associated indexes, will need to be physically moved by the operation.  You need to make sure you have enough storage space available to rebuild each partition - perhaps you'll want to move the table to a set of new filegroups, then drop the old unused filegroups afterwards.

If the table has a primary key or any unique indexes defined, they must be dropped and recreated so they include the new partitioning column in the index key.

If the table has a primary key, and that primary key is referenced in other tables, you'll need to break the referential integrity in order to change the partitioning key.  Since you are currently using the InsertedDate column as the partitioning key, and want to move that to use the CompletedDate column, which are presumably both date data-types, I doubt this affects your situation - it's just worth being aware of.

For a table with a primary key, the actual task of moving a table from one partitioning key to another consists of dropping the primary key constraint, then rebuilding the table with the new partitioning key as part of the primary key.

I've put together a little test-bed with 10,000,000 rows spread over 24 partitions so we can see this in action.

We'll do the work in tempdb, so make sure you have enough room for some growth.  First, we'll create the partition function, partition scheme, and finally the table itself:

USE tempdb;
GO
IF OBJECT_ID(N'dbo.Tab', N'U') IS NOT NULL
BEGIN
    DROP TABLE dbo.Tab;
END
IF EXISTS (SELECT 1 FROM sys.partition_schemes ps WHERE ps.name = N'PartScheme')
BEGIN
    DROP PARTITION SCHEME PartScheme;
END
IF EXISTS (SELECT 1 FROM sys.partition_functions pf WHERE pf.name = N'PartFun')
BEGIN
    DROP PARTITION FUNCTION PartFun;
END

CREATE PARTITION FUNCTION PartFun (datetime)
AS RANGE LEFT
FOR VALUES (
      N'2012-01-01T00:00:00'
    , N'2012-04-01T00:00:00'
    , N'2012-07-01T00:00:00'
    , N'2012-10-01T00:00:00'
    , N'2013-01-01T00:00:00'
    , N'2013-04-01T00:00:00'
    , N'2013-07-01T00:00:00'
    , N'2013-10-01T00:00:00'
    , N'2014-01-01T00:00:00'
    , N'2014-04-01T00:00:00'
    , N'2014-07-01T00:00:00'
    , N'2014-10-01T00:00:00'
    , N'2015-01-01T00:00:00'
    , N'2015-04-01T00:00:00'
    , N'2015-07-01T00:00:00'
    , N'2015-10-01T00:00:00'
    , N'2016-01-01T00:00:00'
    , N'2016-04-01T00:00:00'
    , N'2016-07-01T00:00:00'
    , N'2016-10-01T00:00:00'
    , N'2017-01-01T00:00:00'
    , N'2017-04-01T00:00:00'
    , N'2017-07-01T00:00:00'
    , N'2017-10-01T00:00:00'
);

CREATE PARTITION SCHEME PartScheme
AS PARTITION PartFun
ALL TO ([PRIMARY]);

IF OBJECT_ID(N'dbo.Tab', N'U') IS NOT NULL
DROP TABLE dbo.Tab;
CREATE TABLE dbo.Tab
(
    TabID int NOT NULL
    , CreateDate datetime NOT NULL
    , UpdateDate datetime NOT NULL
    , Data1 varchar(100) NOT NULL
    , Data2 varchar(100) NOT NULL
    , Data3 varchar(100) NOT NULL
    , CONSTRAINT PK_Tab
        PRIMARY KEY CLUSTERED
        (CreateDate, TabID)
) ON [PartScheme](CreateDate);

This fills the table with 10,000,000 rows somewhat evenly spread over each partition:

;WITH Ten AS
(
    SELECT v.Num
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))v(Num)
)
, TenMillion AS
(
    SELECT Num = (t7.Num * POWER(10, 6))
        + (t6.Num * POWER(10, 5))
        + (t5.Num * POWER(10, 4))
        + (t4.Num * POWER(10, 3))
        + (t3.Num * POWER(10, 2))
        + (t2.Num * POWER(10, 1))
        + (t1.Num)
    FROM Ten t1
        CROSS JOIN Ten t2
        CROSS JOIN Ten t3
        CROSS JOIN Ten t4
        CROSS JOIN Ten t5
        CROSS JOIN Ten t6
        CROSS JOIN Ten t7
)
INSERT INTO dbo.Tab (TabID, CreateDate, UpdateDate, Data1, Data2, Data3)
SELECT m.Num, DATEADD(DAY, m.Num % 1825, N'2012-01-01T00:00:00')
    , DATEADD(DAY, m.Num % 1825, N'2012-02-02T00:00:00')
    , CONVERT(varchar(100), CRYPT_GEN_RANDOM(100))
    , CONVERT(varchar(100), REPLICATE('A', 100))
    , CONVERT(varchar(100), REPLICATE('B', 100))
FROM TenMillion m;

The distribution of rows in the table:

SELECT ObjectName = s.name + '.' + o.name
    , p.partition_number
    , p.rows
FROM sys.partitions p
    INNER JOIN sys.objects o ON o.object_id = p.object_id
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE s.name = 'dbo'
    AND o.name = 'Tab'
    AND p.index_id = 1
ORDER BY p.partition_number;

╔════════════╦══════════════════╦════════╗
║ ObjectName ║ partition_number ║ rows   ║
╠════════════╬══════════════════╬════════╣
║ dbo.Tab    ║ 1                ║ 5480   ║
║ dbo.Tab    ║ 2                ║ 498680 ║
║ dbo.Tab    ║ 3                ║ 498680 ║
║ dbo.Tab    ║ 4                ║ 504160 ║
║ dbo.Tab    ║ 5                ║ 504160 ║
║ dbo.Tab    ║ 6                ║ 493200 ║
║ dbo.Tab    ║ 7                ║ 498680 ║
║ dbo.Tab    ║ 8                ║ 504160 ║
║ dbo.Tab    ║ 9                ║ 504160 ║
║ dbo.Tab    ║ 10               ║ 493200 ║
║ dbo.Tab    ║ 11               ║ 498592 ║
║ dbo.Tab    ║ 12               ║ 504068 ║
║ dbo.Tab    ║ 13               ║ 504068 ║
║ dbo.Tab    ║ 14               ║ 493110 ║
║ dbo.Tab    ║ 15               ║ 498589 ║
║ dbo.Tab    ║ 16               ║ 504068 ║
║ dbo.Tab    ║ 17               ║ 504068 ║
║ dbo.Tab    ║ 18               ║ 498589 ║
║ dbo.Tab    ║ 19               ║ 498589 ║
║ dbo.Tab    ║ 20               ║ 504068 ║
║ dbo.Tab    ║ 21               ║ 487631 ║
║ dbo.Tab    ║ 22               ║ 0      ║
║ dbo.Tab    ║ 23               ║ 0      ║
║ dbo.Tab    ║ 24               ║ 0      ║
║ dbo.Tab    ║ 25               ║ 0      ║
╚════════════╩══════════════════╩════════╝

This rebuilds the table on the new partitioning key after dropping the original primary key.  The effect here is we move the table from being a clustered index to a heap and back into a clustered index.  I'm doing this in a transaction to ensure I don't accidentally leave the table as a heap.

BEGIN TRANSACTION;

BEGIN TRY
    ALTER TABLE dbo.Tab
    DROP CONSTRAINT PK_Tab
    WITH (MOVE TO PartScheme(UpdateDate));

    ALTER TABLE dbo.Tab
    ADD CONSTRAINT PK_Tab
    PRIMARY KEY CLUSTERED
    (UpdateDate, TabID)
    ON PartScheme(UpdateDate);

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION
END CATCH

When we look at the sys.partitions query, we can see row counts have changed since some rows have been moved into different partitions:

SELECT ObjectName = s.name + '.' + o.name
    , p.partition_number
    , p.rows
FROM sys.partitions p
    INNER JOIN sys.objects o ON o.object_id = p.object_id
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE s.name = 'dbo'
    AND o.name = 'Tab'
    AND p.index_id = 1
ORDER BY p.partition_number;

╔════════════╦══════════════════╦════════╗
║ ObjectName ║ partition_number ║ rows   ║
╠════════════╬══════════════════╬════════╣
║ dbo.Tab    ║ 1                ║ 0      ║
║ dbo.Tab    ║ 2                ║ 328800 ║
║ dbo.Tab    ║ 3                ║ 498680 ║
║ dbo.Tab    ║ 4                ║ 504160 ║
║ dbo.Tab    ║ 5                ║ 504160 ║
║ dbo.Tab    ║ 6                ║ 493200 ║
║ dbo.Tab    ║ 7                ║ 498680 ║
║ dbo.Tab    ║ 8                ║ 504160 ║
║ dbo.Tab    ║ 9                ║ 504160 ║
║ dbo.Tab    ║ 10               ║ 493200 ║
║ dbo.Tab    ║ 11               ║ 498624 ║
║ dbo.Tab    ║ 12               ║ 504068 ║
║ dbo.Tab    ║ 13               ║ 504068 ║
║ dbo.Tab    ║ 14               ║ 493110 ║
║ dbo.Tab    ║ 15               ║ 498589 ║
║ dbo.Tab    ║ 16               ║ 504068 ║
║ dbo.Tab    ║ 17               ║ 504068 ║
║ dbo.Tab    ║ 18               ║ 498589 ║
║ dbo.Tab    ║ 19               ║ 498589 ║
║ dbo.Tab    ║ 20               ║ 504068 ║
║ dbo.Tab    ║ 21               ║ 504068 ║
║ dbo.Tab    ║ 22               ║ 158891 ║
║ dbo.Tab    ║ 23               ║ 0      ║
║ dbo.Tab    ║ 24               ║ 0      ║
║ dbo.Tab    ║ 25               ║ 0      ║
╚════════════╩══════════════════╩════════╝

FYI, modifying the partitioning key on my workstation with limited ram, and all the partitions on the same disk, took over 14 minutes for 10 million rows.  You'll want to practice this on a non-production system to ensure you understand the implications and to get an idea of how much time this might take.  Understand that no client activity can take place while the rebuild is happening, so you'll need to plan for some downtime.


Index maintenance operations that are ONLINE in Standard Edition: Is table Read-Write or Read-Only?

During an ALTER INDEX ... REORGANIZE operation, is the table available for read and write, or are writes blocked?

REORGANIZE is always a online operation and single threaded operation but does not holds blocking locks. When you reorganize there is option to allow_page_locks =ON or OFF if you keep this value off you might get error like index cannot be reorganized as it cannot take page level lock so its better to reorganize index with option allow_page_locks =ON. So yes table is available for read write operation locks are taken as pointed in the option but as I said it does not holds blocking locks

As per Microsoft Books Online

Turning off page and row locking might or might not be acceptable because the weekly batch update will block the concurrent readers from accessing the table while the update runs. If the batch job only changes a few rows or pages, you can change the locking level to allow row or page level locking, which will enable other sessions to read from the table without blocking. If the batch job has a large number of updates, obtaining an exclusive lock on the table may be the best way to ensure the batch job finishes efficiently.

  ALTER INDEX ... REBUILD
  Is a BLOCKING operation, unless you have SQL Server Enterprise Edition.... (and even with the Enterprise Edition, is blocking when rebuilding clustered indexes)

Even index rebuild in enterprise edition do takes short term locks as per Paul Randal Article Online index operations need to take two very short-term table locks. An S (Shared) table lock at the start of the operation to force all write plans that could touch the index to recompile, and a SCH-M (Schema-Modification – think of it as an Exclusive) table lock at the end of operation to force all read and write plans that could touch the index to recompile.