As per documentation your error is:

  Error: 1261 SQLSTATE: 01000 (ER_WARN_TOO_FEW_RECORDS)
  Message: Row %ld doesn't contain data for all columns

Thus it appears that your table have more column than the file. You will have to specify the column-names explicitly. Also the remaining columns should be nullable. (You might want to share table definition if below command doesn't work)

Try this:

LOAD DATA INFILE 'path/to/file' INTO TABLE your_table FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (id,date,date_,pow,ene,reac);

If you are interested you might want to refer this blog post which explains various use-cases for loading delimited data to mysql.


I have found the solution for this issue.

Simply go to
Start -> Microsft SQL Server 2008 R2 -> Configuration Tools -> SQL Server Installation Center (64-bit)

Then go to the Maintenance option -> Repair.

Done.


For a quick assessment, get the execution plan out of SSMS and in to Plan Explorer.

Review the most expensive operations for anything unexpected. Sorts, worktables, inappropriate join operators (e.g. nested loop where you expect a merge or hash).
Look at the rowcounts at each stage of the plan, are they broadly within the range you expected to see?
Look at the estimated vs actual rows. If you're actuals are close to the estimates, it's more likely you have a good plan. If there are big variations, find out why (missing and/or out of date statistics for instance).
Evaluate the potential for parameter sniffing issues. Look for areas where cardinality may vary and test against a range of input parameters.

Lots of freely available reference material out there, Grant Fitchley's SQL Server Execution Plans is a good start. I also found Joe Chang's blog posts and ebook on execution plan costs very useful.


Is it there anyway to restore updated records in Microsoft SQL Server 2008 R2?

If you've ever taken a backup, and your recovery model is BULK LOGGED or FULL, then you might have some luck. Unfortunately if never took a backup before you had your problem, then you are probably bang out of luck (as Martin Smith pointed out in his comments below).

You can check whether you've ever taken a backup by looking at:

SELECT db_name(database_id) as 'database', last_log_backup_lsn
FROM sys.database_recovery_status

If the database you've lost data from has a NULL for last_log_backup_lsn, then the database is still in auto-truncate mode and your changes have possibly be lost forever.

If this is a server that you can take offline, I'd suggest shutting down the server and taking a copy or two of the .mdf/.ldf files first, just in case you can get something useful out of them.

There are third party tools like Apex SQL Log or Toad for SQL Server which will allow you to interrogate the .ldf files directly and hopefully see what happened to the data, plus they can usually interrogate transaction log backup files as well. Unfortunately they're not cheap, but you maybe able to get the functionality that you need from a trial version (I don't use either so I can't be sure about this).

If you find another tool that allows you to look at the transaction log backups, but not .ldf files, you should be able to:

Take a full backup
Take your first a transaction log backup (probably uncompressed to maximise the chance that tools will work with it)

Then use the backup from step 2.

Unfortunately, your mileage may vary, so I'd suggest doing what you can with trial versions of tools before spending lots of money on a tool that might not get your data back.


Does adding page compression to a table also compress existing indexes?

From: http://msdn.microsoft.com/en-us/library/cc280449%28v=sql.105%29.aspx

The compression setting of a table is not automatically applied to its nonclustered indexes. Each index must be set individually.


Each Go statement lets SQL Server know that the preceding commands are meant to be a single batch. This works for the create statements, but I suspect that if you try to use the symmetric key in a command after the batch, it will not be open. Remove the Go statements, then try to use the symmetric key after the open statement.


pg_restoring into a table with a not null column

Temporarily put a trigger on your target table to set default values:

CREATE FUNCTION override_null()
RETURNS trigger AS $$
BEGIN
  IF NEW.failed_port IS NULL THEN
    NEW.failed_port := 0;
  END IF;
  RETURN NEW;
END
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER override_null_trigger
BEFORE INSERT ON c_a
FOR EACH ROW
EXECUTE PROCEDURE override_null();


quotename(object_schema_name(@objectId)) returns null when called as a synonym

You have to add a parameter which indicates a database:

create function [dbo].[GetFqn](@objectId bigint, @DatabaseId INT)
returns varchar(100)
as
begin
    declare @fqn varchar(100);
    select @fqn = quotename(object_schema_name(@objectId, @DatabaseId)) + '.' + quotename(object_name(@objectId, @DatabaseId));
    return @fqn;
end

Then call:

select Utility.dbo.GetFqn([object_id], DB_ID())
from sys.tables


All Microsoft Office applications use VBA.  Excel, Access, Word, Outlook, and Powerpoint can be scripted using VBA.  There are ways to extend this to shell scripting, ActiveX, etc and, by extension, a good number of other languages that those extensions can tap you into.  I embed SQL into Excel VBA macros.

VB has a slightly different syntax and a different IDE.


If function not exist then create function in SQL server

Unfortunately you can't use create function like that. It must be the first statement in a batch.

You can try dynamic SQL instead like:

If not Exists (Select * from sys.objects where name ='FNLX_getDate'  and type =N'FN')
    BEGIN
        DECLARE @sql NVARCHAR(MAX);
        SET @sql = N'CREATE FUNCTION ...';
        EXEC sp_executesql @sql;
    END

Or the classic way (like you can see if you generate scripts with SSMS) : drop and create:

If Exists (Select * from sys.objects where name ='FNLX_getDate'  and type =N'FN')
    drop function [dbo].[FNLX_getDate]
GO
Create function [dbo].[FNLX_getDate]() returns Datetime
as
Begin
.................
End

Notice the GO SSMS batch separator.


Mounting a database from database files copied from a previous installation

The information about database data files and online redo log files along with their paths is stored in the control file(s). The information about the control file path is stored in initialization parameter file (pfile) or in server parameter file (spfile).

So if you moved your data files, online redo log files and/or control file(s) you should update the paths in the appropriate files to reflect the changes before you can mount and open the database.

First, you alter you parameter file with new information after you successfully started the instance:

SQL> startup nomount;
SQL> alter system set control_files = '/oracle/data/control.dbf';

Then you mount the database so control file can be read and written:

SQL> alter database mount;

After database is mounted you can use RENAME FILE command to set the new pats to data files and online redo log files:

SQL> alter database rename file '/u01/data/sysaux.dbf'
  to '/oracle/data/sysaux.dbf';

SQL> alter database rename file '/u01/data/redo_1a.dbf'
  to '/oracle/data/redo_1a.dbf';

If your online redo is damaged – you didn't specify redo log files names in the list – and database was not shut down orderly, then data files and control file(s) are in inconsistent state and the only way to open your database is to restore and recover it from a previous backup.


How to fetch the string from the column of table in postgresql?

SELECT 'userid=' || Userid::text
     , 'dist_code(' || string_agg(District_code::text, ',') || ')'
FROM table
GROUP BY Userid
-- WHERE userid=1


Why does AttachDBFile make everything slower?

You are comparing two different servers. Likely different hardware specs, possibly different versions. Possibly even querying different data. Apples and oranges. The attach is probably a red herring (there is a cost in attach and querying cold data, but will amortize over many queries. You do measure hot cached and repeated runs, don't you?)

Read How to analyse SQL Server performance to root-cause the performance problem.


Some timeout in query store but get all SP:Starting and SP:Completed in profiler

If you capture queries when they finish with the SQL:BatchCompleted or SP:Completed trace running, aborting a query will show up as a finished query.

Lets say I run this query:

select * from master..spt_values spt1
CROSS APPLY master..spt_values spt2;

And kill it end it after 2 seconds of executing:

It will still show up in my profiler trace. What will be different is the error column. On normal executions it will show as: 0 = OK. When aborting the query, it shows as: 2 - Abort

The same is true for aborting a procedure, it will show up in sp:completed.

Avoiding aborted queries as they time out is finding why they took over 30 seconds.
One of the things you should read through is Why is my query suddenly slower than it was yesterday?.

You can post a new question to seek advice on improving the query runtime.

We would need the actual execution plan, table definition, ... (generally as much information as possible). I am sure that we would be able to help you with the reporting query.

Another band-aid solution could be increasing the time the queries are allowed to run.

  BTW: The canceled query used the same execution plan as the quick one.
  It should not be the execution plan issue

It could be running with different parameters, resulting in parameter sniffing.
Adding OPTION(RECOMPILE) to the query could disable parameter sniffing if you are willing to live with the cost of recompiling the query on each execution.

Other reasons could be waiting for resources, such as being blocked, cpu / memory pressure, ....


How to retrieve data from 3 tables joined by a record identifier?

I am assuming that the ID column is either the PK or otherwise declared as unique in all three tables.

Now, if each of these scenarios is equally possible:

all three tables have a row with the ID of 101,
only two (any two) of the three tables have a row with the ID of 101,
only one (any one) of the three tables has a row with the ID of 101,

and you want the requested data from every table that has a match – then you need to use only full joins.

This is one way how you could implement the request:

SELECT
  t1.Col AS t1Col,
  t2.Col AS t2Col,
  t3.Col AS t3Col
FROM
  t1
  FULL JOIN t2 ON t1.ID = t2.ID
  FULL JOIN t3 ON COALESCE(t1.ID, t2.ID) = t3.ID
WHERE
  COALESCE(t1.ID, t2.ID, t3.ID) = 101
;

As you can see, in order to match the third table's ID to that of either of the other two tables when only one of them has a match, you can use the COALESCE function. COALESCE will pick a non-empty (non-null) ID of the two specified, thus making sure that the third ID will be matched with an existing row in either table. The same condition will work correctly if the match is present in both tables.

The WHERE condition uses a similar technique for filtering the result set. Since each table's ID in every row of the joined set is supposed to be either the same value or a null, the COALESCE function will necessarily pick one that is not null to compare to the specified argument (101). Thus, only the row that has the matching data from all or any of the three tables will be selected.

You could also take a slightly different approach: separately select the row matching the ID = 101 condition from each table, use the results as derived tables and then join them:

SELECT
  t1.Col AS t1Col,
  t2.Col AS t2Col,
  t3.Col AS t3Col
FROM
  (
    SELECT ID, Col FROM t1 WHERE ID = 101
  ) AS t1
  FULL JOIN
  (
    SELECT ID, Col FROM t2 WHERE ID = 101
  ) AS t2 ON t1.ID = t2.ID
  FULL JOIN
  (
    SELECT ID, Col FROM t3 WHERE ID = 101
  ) AS t3 ON COALESCE(t1.ID, t2.ID) = t3.ID
;

Although you now have to repeat the same filtering condition, the resulting query might turn out to be more efficient – that will depend on how smart the query optimiser is in your database system.

Test both queries to determine which works better for you.


Delete duplicated rows by modified_date - PostgreSQL 9.2

You're mixing grouping criteria twice while creating junk_test, first in the GROUP BY subselect by having fewer conditions in the WHERE, and then in the PARTITION BY by having one extra partitioning field (time arrival).

If you can assume that older ids are older jobs, then you can identify your duplicates by joining grouped table with itself, like this:

SELECT jd.dup_group_no, j.id=jd.id AS to_keep, j.id INTO junk.test_table
FROM (
  SELECT time_job, recurrenceid, client, creatortype, deleted, MIN(id) AS id, row_number() over () AS dup_group_no
  FROM ja_jobs
  WHERE clientid = 33731 AND creatortype = 'legacyrec' AND deleted = false
  GROUP BY time_job, recurrenceid, client, creatortype, deleted
  HAVING count(*) > 1
  ) jd
JOIN ja_jobs j USING (time_job, recurrenceid, clientid, creatortype, deleted);

If there is no guaranteed correlation between older ids and older jobs by creation time, the query is trickier:

SELECT jm.dup_group_no, j.id=jd.id AS to_keep, j.id INTO junk.test_table
FROM (
  SELECT DISTINCT ON (jd.time_job, jd.recurrenceid, jd.clientid, jd.creatortype, jd.deleted) jd.time_job, jd.recurrenceid, jd.clientid, jd.creatortype, jd.deleted, jd.id, row_number() over () AS dup_group_no
  FROM (
    SELECT time_job, recurrenceid, client, creatortype, deleted
    FROM ja_jobs
    WHERE clientid = 33731 AND creatortype = 'legacyrec' AND deleted = false
    GROUP BY time_job, recurrenceid, client, creatortype, deleted
    HAVING count(*) > 1
    ) jd
  JOIN ja_jobs jm USING (time_job, recurrenceid, clientid, creatortype, deleted)
  ORDER BY jd.time_job, jd.recurrenceid, jd.clientid, jd.creatortype, jd.deleted, jd.created_date, jd.id
  ) jm
JOIN ja_jobs j USING (time_job, recurrenceid, clientid, creatortype, deleted);


SQL Server uses a round robin with proportional fill algorithm for TempDB and data files in general.  Thus if you have 1 TempDB file and add 2 more, only the other 2 will be filled until they reach the size of the first one and all will then grow proportionally; or when the next restart occurs and they all get emptied out.

Thus you will be using those files.  You can see them being used in the physical index stats DMV.  Naming doesn't matter, just do what works for you to manage them.


You can't use COUNT inside another aggregate. What you're trying to do is add 1 for each row that meets the criteria, so use the CASE to return 1 or 0 depending of whether the criteria is met or not. Then aggregate the results of that CASE with a SUM:

SELECT EmpID,
SUM((CASE WHEN Status1='Present' THEN 1 ELSE 0 END)) end as 'Present',
SUM((CASE WHEN Status1='Late-Absent' THEN 1 ELSE 0 END)) end as 'Late-Absent',
SUM((CASE WHEN Status1='Absent' THEN 1 end ELSE 0 END)) as 'Absent' from  @t1
where date1 between @fdate and @tdate
GROUP BY EmpID

I also removed Status1 from your aggregate, because it would create a separate row for each occurrence of employee and Status1 - what you want is one row per employee only.


Convert multiple databases into schemas SQL Server

Yes, this can be done.

In the open source community, it has been done for years. In most situations, they just prefixed the tables.

I am not aware of a tool to simplify the process.

You are aware that you introduce new challenges.

Permissions.
Queries are more complicated and may lose some performance.

An alternative approach is to move to multi-tenant design. Add a new field to all tables. Let's call it ClientId. Add filtered indexes. Now all queries need to be filtered on ClientId. You still need to deal with any auto increment columns.


What is this CASE statement doing exactly with the NVL function?

From the documentation:

  NVL lets you replace null (returned as a blank) with a string in the results of a query. If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1.

So for your specific example, if Q_IND is NULL then 0 is returned to the CASE statement. For such scenarios your CASE statement becomes this:

CASE WHEN 0 = 1 THEN 1 ELSE 0 END Q_IND

We all know that 0 is not equal to 1 and thus 0 is returned as Q_IND.


Unable to configure MongoDB replication without initial sync

Why do two sets of heavy lifting ??? Let MongoDB do it.

For this example, suppose IP of SECONDARY is 10.30.50.70 with hostname myslave

STEP 01 : Enable Replica Set on the PRIMARY only

Make the following changes in  /etc/mongodb.conf on the PRIMARY

enable authorization in /etc/mongodb.conf

authorization: enabled under the security YAML tag (MongoDB 2.6+)
auth=true (if you are using MongoDB 2.4)

Enable replication (suppose your name the replica set myreplica)

Put replSetName: myreplica under replication YAML tag (MongoDB 2.6+)
Put replSet = myreplica (MongoDB 2.4)

STEP 02 : Restart MongoDB on the PRIMARY

service mongod restart

STEP 03 : Initiate Replica Set on the PRIMARY

rs.initiate();

NOTE: At this point your have a one-node Replica Set

STEP 04 : Perform Steps 01 and 02 on your SECONDARY

NOTE: At this point, the SECONDARY is replica set enabled but not replicating

STEP 05 : NOW, add the SECONDARY to the PRIMARY

Goto the PRIMARY and run

rs.add("10.30.50.70:27017")

or

rs.add("myslave:27017")

STEP 06 : Check status of Replica Set

Run this repeatedly

rs.status()

If this command ever freezes, don't worry. It rebuilding all the indexes.

You can open another ssh session on the SECONDARY, run tail -f on the mongodb.log file and see the index rebuild progression.

WHY DO IT THIS WAY ???

MongoDB will do essentially start replication, copy the data, and rebuild indexes in a single operation. As shown, this process is in no way automatic when building an initial replica set. Doing it this way, is less error-prone.


How does SQL Server determine precision / scale?

Determining precision and scale resulting from expressions is a rat's nest and I don't think anyone understands the exact rules in every scenario, especially when mixing decimal (or float!) and int. See this answer by gbn.

You can of course tailor the expressions to give you what you want by making much more verbose explicit conversions. This is probably overkill but:

SELECT
   CONVERT(DECIMAL(15,6), CONVERT(DECIMAL(15,6), 0.15)
   * CONVERT(DECIMAL(15,6), 30)
   / CONVERT(DECIMAL(15,6), 360)),
   CONVERT(DECIMAL(15,6), CONVERT(DECIMAL(15,6), 0.15)
   / CONVERT(DECIMAL(15,6), 360)
   * CONVERT(DECIMAL(15,6), 30));

Neither result is rounded wrongly due to broken floating point math or wildly wrong precision/scale.

0.012500    0.012500


Distributed Transactions between SQL Server 2000 & MySQL Stopped Working

Someone posted a solution to a related problem on StackOverflow:

https://stackoverflow.com/questions/5945435/sql-server-2000-linked-server/6651704#6651704


How can I count all children & grandchildren until the 7th level?

I have bad news and good news
BAD NEWS
MySQL does not support recursive CTE queries
Like @RickJames, I don't have the energy to write it for you
GOOD NEWS
Once upon a time, I did have the energy and time to write stuff like this
Here are my posts I have written discussing Stored Procedures and Recursive Queries

Oct 24, 2011 : Find highest level of a hierarchical field: with vs without CTEs
Dec 10, 2012 : MySQL: Tree-Hierarchical query
Jul 11, 2013 : Recursive self joins
Jan 31, 2014 : Recursive Query in MySQL using stored proceedure and CURSOR
Nov 28, 2014 : Implement a Parent-Level Count in Binary Search Tree

From the first link, I addressed this table structure
CREATE  TABLE IF NOT EXISTS `testdb`.`observations` (
  `id` INT NOT NULL ,
  `parent_id` INT NULL ,
  `name` VARCHAR(45) NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;

that was holding this data

I made three Stored Procedures centered around them

GetParentIDByID
GetAncestry
GetFamilyTree

What's interesting about the code is that I do not use recursion. I use a String as a comma-separated list of node IDs and process it like a queue. This technique is known as PreOrder Tree Traversal except each tree node can have more than two children.
SUGGESTION
What you need to do is read the code from that first post and apply it with your fields and keys in mind.
GIVE IT A TRY !!!


Difference in size of table between InnoDB and MEMORY engines

InnoDB stores the data in a BTree, with the PRIMARY KEY clustered.  Your 8 bytes of data will expand to an average of about 30 bytes due to record overhead, block overhead, BTree characteristics, etc, etc.  The BTree blocks are 16KB each.  After some point in the growth of the table, chunks of 8MB are allocated, so you will find the table growing is big increments.

MEMORY stores data in a Hash.  So the PK is hashed to build the data structure.  I am not familiar with the details, but they are significantly different than InnoDB's BTree structure.  A common technique is to double the hash size when needed, thereby also leading to significant increments at seemingly random times.

What is the goal of this exercise?


Create pivot table from SQL query results

Here you go. This is for SQL Server (since you haven't tagged the DB) but I guess this is OK since you mention SCCM.

There are heaps of examples of dynamic pivots on here if the final number of [UserX] columns is unknown.

SELECT * INTO #Temp FROM (VALUES
 ('PC001', 'a1\user1')
,('PC002', 'a1\user2')
,('PC002', 'a1\user3')
,('PC003', 'a1\user4')
,('PC003', 'a1\user5')
,('PC003', 'a1\user6')
,('PC004', 'a1\user7')
,('PC005', 'a1\user8')
) A([Computer Name], UserName);

SELECT *
 FROM
(SELECT 'User'+CONVERT(VARCHAR,ROW_NUMBER() OVER (PARTITION BY [Computer Name] ORDER BY [Computer Name])) [User]
,*
FROM #Temp) Src
PIVOT
(MAX(Username) FOR [User] IN ([User1], [User2],[User3])
)Pvt

Computer Name User1    User2    User3
------------- -------- -------- --------
PC001         a1\user1 NULL     NULL
PC002         a1\user2 a1\user3 NULL
PC003         a1\user4 a1\user5 a1\user6
PC004         a1\user7 NULL     NULL
PC005         a1\user8 NULL     NULL

(5 row(s) affected)

Off the top of my head, yes, you can add this to your original query - put your query in place of my #Temp as in

SELECT *
   FROM
  (SELECT 'User'+CONVERT(VARCHAR,ROW_NUMBER() OVER (PARTITION BY [Computer Name] ORDER BY [Computer Name])) [User]
,*
  FROM (Your query goes here) Blah
) Src
etc

) or just select your query into a temp table - just add INTO #TempTableName into your query.

Hope this helps.


Will exporting tables in one db server to another db server release space in server?

Short answer: probaply not.
Longer answer: It depends. If they have their own tablespace and you drop the tablespace also, then yes.

If your test tables are in a tablespace that holds other data and you can‘t drop it then no. The Space is freed in the tablespace itself, but not on disk. To do that you need to reorganize the tablespace.

It depends on the database version and edition if you can do this online . Another option would be to create a new tablespace, copy all objects from the old tablespace into the new one.


Only local administrators can connect remotely

I found a solution to this problem.

I added the domain user to: Local Security Policy -> Local Policies -> User Rights Assignment -> Access this computer from the Network


Does dropping index on column A affect queries on column B?

Since none of the updated columns are in indexes or constraints, you could conceivably do a 'HOT' (Heap-Only-Tuple) update, which doesn't need to maintain the indexes. But for that to work you have to have room in each table block to hold both the old and the new data at the same time. Once it needs to migrate rows to different blocks (because there is no room in the current block) then it needs to maintain all the indexes to point to the new block. Since you are updating every row in the table in a single transaction, this is unlikely to be practical as space for the old tuples cannot be recycled that quickly. You could help promote the HOT updates by setting the table fillfactor to a low value (<50) before loading the table data, but this unlikely to be an optimal solution.  Better to just drop the index and constraints, or to delay creating them in the first place, as discussed in your related question.


Using Postgresql logical replication, how do you know that the subscriber is caught up?

I used replication on postgres 10 last year and I had same problem understanding how to monitor the process, as documentation is not clear enough. Anyway I'll try to give you an example as to check if monitoring is going on.

On PUBLISHER side there are few things you can check:
- pg_catalog.pg_publication;
- pg_catalog.pg_publication_tables;
- pg_current_wal_lsn();

I'll create a publication "test_publication" with two tables: t_1 and t_2. I wont cover the needed pre-requisites (user, roles and so on).

test_logical_replication=# create publication test_publication for table t_1, t_2;
CREATE PUBLICATION
test_logical_replication=# select * from pg_catalog.pg_publication;
pubname      | pubowner | puballtables | pubinsert | pubupdate | pubdelete
-----------------+----------+--------------+-----------+-----------+-----------
test_publication |       10 | f            | t         | t         | t
(1 row)

test_logical_replication=# select * from pg_publication_tables;
    pubname      | schemaname | tablename
    ------------------+------------+-----------
     test_publication | public     | t_1
     test_publication | public     | t_2
    (2 rows)

On SUBSCRIBER SIDE:

test_logical_replication_subscriber=# create subscription test_subscription CONNECTION 'dbname=test_logical_replication host=XXX user=repuser' PUBLICATION test_publication;
NOTICE:  created replication slot "test_subscription" on publisher
CREATE SUBSCRIPTION

Interesting information are on table pg_catalog.pg_stat_subscription.
Here important columns are:
- received_lsn: Last write-ahead log location received
- last_msg_send_time: send time of last message received from PUBLISHER
- last_msg_receipt_time: Receipt time of last message received from PUBLISHER
- latest_end_lsn: Last write-ahead log location reported to PUBLISHER
- latest_end_time: Time of last write-ahead log location reported to PUBLISHER

You have to check these columns to catch what is happening.
First check if two db are in sync;

PUBLISHER SIDE:

test_logical_replication=> select pg_current_wal_lsn();
 pg_current_wal_lsn
--------------------
 0/8EB83768     << this is the location on Wal file where we are now, before starting new insert

We can check on SUBSCRIBER that at moment the two database are in sync,because
value returned by function pg_current_wal_lsn() on publisher match the value in
columns received_lsn and latest_end_lsn on subscriber:

test_logical_replication_subscriber=# select received_lsn, latest_end_lsn from pg_catalog.pg_stat_subscription;

received_lsn    | latest_end_lsn
----------------+------------------
 0/8EB83768     | 0/8EB83768

I'll add 4000 rows on table t_1, and see what happens on PUBLISHER:

test_logical_replication=> insert into t_1 select id+1, txt||'--BB' from t_1;
INSERT 0 4000

test_logical_replication=> select pg_current_wal_lsn();
 pg_current_wal_lsn
--------------------
 0/8EC4B9D0             <<< this value in increasing
(1 row)

test_logical_replication=> select pg_current_wal_lsn();
 pg_current_wal_lsn
--------------------
 0/8EC4DE78             <<< this value in increasing
(1 row)

test_logical_replication=> select pg_current_wal_lsn();
 pg_current_wal_lsn
--------------------
 0/8EC4DEB0             <<< this value in increasing
(1 row)

test_logical_replication=> select pg_current_wal_lsn();
 pg_current_wal_lsn
--------------------
 0/8EC4DEB0            <<< same value, wal sending has finished
(1 row)

let's look how pg_catalog.pg_stat_subscription values change during replication on SUBSCRIBER:

test_logical_replication_subscriber=# select received_lsn,last_msg_send_time,last_msg_receipt_time,latest_end_lsn,latest_end_time from pg_catalog.pg_stat_subscription;
 received_lsn |      last_msg_send_time       |    last_msg_receipt_time     | latest_end_lsn |        latest_end_time
--------------+-------------------------------+------------------------------+----------------+-------------------------------
 0/8EC4B9D0   | 2018-12-17 11:39:56.014564+01 | 2018-12-17 11:39:56.07322+01 | 0/8EC4B9D0     | 2018-12-17 11:39:56.014564+01
(1 row)

test_logical_replication_subscriber=# select received_lsn,last_msg_send_time,last_msg_receipt_time,latest_end_lsn,latest_end_time from pg_catalog.pg_stat_subscription;
 received_lsn |      last_msg_send_time       |     last_msg_receipt_time     | latest_end_lsn |        latest_end_time
--------------+-------------------------------+-------------------------------+----------------+-------------------------------
 0/8EC4BA08   | 2018-12-17 11:39:56.737101+01 | 2018-12-17 11:39:56.736303+01 | 0/8EC4BA08     | 2018-12-17 11:39:56.737101+01
(1 row)

test_logical_replication_subscriber=# select received_lsn,last_msg_send_time,last_msg_receipt_time,latest_end_lsn,latest_end_time from pg_catalog.pg_stat_subscription;
 received_lsn |      last_msg_send_time       |     last_msg_receipt_time     | latest_end_lsn |        latest_end_time
--------------+-------------------------------+-------------------------------+----------------+-------------------------------
 0/8EC4DE78   | 2018-12-17 11:40:04.184765+01 | 2018-12-17 11:40:04.183937+01 | 0/8EC4DE78     | 2018-12-17 11:40:04.184765+01
(1 row)

 test_logical_replication_subscriber=# select received_lsn,last_msg_send_time,last_msg_receipt_time,latest_end_lsn,latest_end_time from pg_catalog.pg_stat_subscription;
 received_lsn |      last_msg_send_time       |   last_msg_receipt_time    | latest_end_lsn |        latest_end_time
--------------+-------------------------------+----------------------------+----------------+-------------------------------
 0/8EC4DEB0   | 2018-12-17 11:40:17.153797+01 | 2018-12-17 11:40:17.153+01 | 0/8EC4DEB0     | 2018-12-17 11:40:17.153797+01
(1 row)

As you can see, on subscriber the 4 columes shows how WAL is arriving from publisher and how it is applied. The difference in time in columns last_msg_send_time and last_msg_receipt_time can give information about lag between publisher and subscriber.
In this case the two server are on different subnets on the same datacenter.
Take in count that the two server I used are test servers and ARE NOT perfect in sync between them. (subscriber server has not an ntp server configured at all).

Hope this help. Regards
MarcoP.


MySQL - Get maximum Tuple without Grouping everything

If I've understood you (at last) correctly, I think you want something like this (see fiddle here):

Create your table:

CREATE TABLE test_tab
(
  first_name VARCHAR (25) NOT NULL,
  last_name  VARCHAR (25) NOT NULL,
  soft_name  VARCHAR (25) NOT NULL,
  soft_hours VARCHAR (25) NOT NULL
);

Populate it:

INSERT INTO test_tab VALUES ('Max', 'Huber', 'Word',  '25h');
INSERT INTO test_tab VALUES ('Max', 'Huber', 'Excel', '15h');

INSERT INTO test_tab VALUES ('Felix', 'TheCat', 'Outlook', '35h');
INSERT INTO test_tab VALUES ('Felix', 'TheCat', 'Word',  '25h');
INSERT INTO test_tab VALUES ('Felix', 'TheCat', 'Excel', '15h');

INSERT INTO test_tab VALUES ('Donald', 'Duck', 'Excel', '20h');
INSERT INTO test_tab VALUES ('Donald', 'Duck', 'Word',  '8h');
INSERT INTO test_tab VALUES ('Donald', 'Duck', 'Outlook', '3h');

And use the following premliminary SQL (included to demonstrate the thought processes that went into formulating the answer. I find that people learn better if they can see a "breadcrumb trail" (cf. Hansel and Gretel)):

SELECT
  first_name,
  last_name,
  MAX(CAST(soft_hours AS UNSIGNED)) AS use_duration
FROM test_tab
GROUP BY first_name, last_name
ORDER BY last_name;

The CAST is necessary because of the inclusion of the hin the string for the duration of usage (much better to use a plain INTEGER). If the CAST isn't present, 8h will be "greater than" 20h for Donald Duck when sorted as a character string - CASTing to UNSIGNED removes that problem.

Result:

first_name  last_name   use_duration
    Donald       Duck             20
       Max      Huber             25
     Felix     TheCat             35

Having obtained the first and last names and the use_duration, we are now in a position to JOIN back to our original table this way:

SELECT
  t1.first_name, t1.last_name, t1.soft_name,
  CAST(t1.soft_hours AS UNSIGNED) AS "usage"
FROM test_tab t1
JOIN
(
  SELECT
    first_name,
    last_name,
    MAX(CAST(soft_hours AS UNSIGNED)) AS use_duration
  FROM test_tab
  GROUP BY first_name, last_name
) AS t2
ON t1.first_name = t2.first_name AND t1.last_name = t2.last_name
AND CAST(t1.soft_hours AS UNSIGNED) = t2.use_duration
ORDER BY t1.last_name;

Result:

first_name  last_name   soft_name   usage
    Donald       Duck       Excel      20
       Max      Huber        Word      25
     Felix     TheCat     Outlook      35

Now, I would recommend that you keep the duration as just a pure INTEGER rather than use 25h. I hope this responds to your needs, - if not, then please expand on your explanation and I'll endeavour to revise my answer to your satisfaction. p.s. welcome to the forum! :-)


Independent aggregation of related entities, while also sorting by related (in a single statement)

I azm not sure do I undersgtand you correctly, but probably you need to group additional field from authors to have only one value for ORDER. It might be count of athours, or min / max for name or so:
WITH books AS (
  SELECT 1 AS id, 'The 1' AS title
   UNION ALL
  SELECT 2, 'The 2'
   UNION ALL
  SELECT 3, '3'
), authorships AS (
  SELECT 1 AS id, 1 AS book_id, 1 AS author_id
   UNION ALL
  SELECT 2, 1, 2
   UNION ALL
  SELECT 3, 1, 3
   UNION ALL
  SELECT 4, 2, 1
   UNION ALL
  SELECT 5, 3, 1
), authors AS (
  SELECT 1 AS id, 'name1' AS name
   UNION ALL
  SELECT 2, 'name2'
   UNION ALL
  SELECT 3, 'name3'
   UNION ALL
  SELECT 4, 'name4'
), filtered AS (
  SELECT book_id, title, ba.id, author_id, name
    FROM books AS b
    JOIN authorships AS ba ON ba.book_id = b.id
    JOIN authors AS a ON a.id = ba.author_id WHERE title LIKE 'The %'
)
SELECT (
         SELECT json_agg(b.* ORDER BY count)
           FROM (
                  SELECT book_id AS id, title, count(name)
                    FROM filtered AS f GROUP BY 1,2
                ) AS f
           JOIN LATERAL (SELECT id, title) AS b ON true)
       ) AS book

           json_agg
-----------------------------
 [{"id":2,"title":"The 2"}, +
  {"id":1,"title":"The 1"}]
(1 row)


The intervals for MySQL grants comes with different levels.

You simply extract the grants from the mysql schema.

Problem is: From which level do you need the extraction ???

You will need the following query to show you global, database, and table grants

SELECT
    IF(table_name='user','Global',IF(table_name='db','Database','Table')) grant_level,
    REPLACE(LEFT(column_name,LENGTH(column_name) - 5),'_',' ') grant_type
FROM (SELECT table_name,column_name FROM information_schema.columns
WHERE table_schema='mysql' AND table_name IN ('user','db','tables_priv')
AND column_name LIKE '%\_priv') AAA
ORDER BY table_name='user' DESC,table_name,column_name;

For MySQL 5.7.12, you will see this:

mysql> SELECT
    ->     IF(table_name='user','Global',IF(table_name='db','Database','Table')) priv_level,
    ->     REPLACE(LEFT(column_name,LENGTH(column_name) - 5),'_',' ') priv_type
    -> FROM (SELECT table_name,column_name FROM information_schema.columns
    -> WHERE table_schema='mysql' AND table_name IN ('user','db','tables_priv')
    -> AND column_name LIKE '%\_priv') AAA
    -> ORDER BY table_name='user' DESC,table_name,column_name;
+------------+-------------------+
| priv_level | priv_type         |
+------------+-------------------+
| Global     | Alter             |
| Global     | Alter routine     |
| Global     | Create            |
| Global     | Create routine    |
| Global     | Create tablespace |
| Global     | Create tmp table  |
| Global     | Create user       |
| Global     | Create view       |
| Global     | Delete            |
| Global     | Drop              |
| Global     | Event             |
| Global     | Execute           |
| Global     | File              |
| Global     | Grant             |
| Global     | Index             |
| Global     | Insert            |
| Global     | Lock tables       |
| Global     | Process           |
| Global     | References        |
| Global     | Reload            |
| Global     | Repl client       |
| Global     | Repl slave        |
| Global     | Select            |
| Global     | Show db           |
| Global     | Show view         |
| Global     | Shutdown          |
| Global     | Super             |
| Global     | Trigger           |
| Global     | Update            |
| Database   | Alter             |
| Database   | Alter routine     |
| Database   | Create            |
| Database   | Create routine    |
| Database   | Create tmp table  |
| Database   | Create view       |
| Database   | Delete            |
| Database   | Drop              |
| Database   | Event             |
| Database   | Execute           |
| Database   | Grant             |
| Database   | Index             |
| Database   | Insert            |
| Database   | Lock tables       |
| Database   | References        |
| Database   | Select            |
| Database   | Show view         |
| Database   | Trigger           |
| Database   | Update            |
| Table      | Column            |
| Table      | Table             |
+------------+-------------------+
50 rows in set (0.02 sec)

This should give you a good starting point.

You could probably create it as a table with something like this

CREATE TABLE grant_records ENGINE=MyISAM AS
SELECT
    IF(table_name='user','Global',IF(table_name='db','Database','Table')) grant_level,
    REPLACE(LEFT(column_name,LENGTH(column_name) - 5),'_',' ') grant_type
FROM (SELECT table_name,column_name FROM information_schema.columns
WHERE table_schema='mysql' AND table_name IN ('user','db','tables_priv')
AND column_name LIKE '%\_priv') AAA
ORDER BY table_name='user' DESC,table_name,column_name;


Best way to arrange a hairdresser database based on their skills

You can make a junction table that uses the ID from the professionals table and the ID from the procedure table. That way it solves your issue of Professional A only being linked to procedures 1 and 3.
For example:


Sybase ASE Auditing on sp_addlogin sp_droplogin sp_modifylogin

After trying hard on this without finding any workable solution, raised a case with Sybase support and they have provided solution.

Below are the commands that can be used to satisfy the mentioned requirement:

--Audit any change at procedure level for login/user/alias

use sybsystemprocs
go
sp_audit "exec_procedure", "all", "sybsystemprocs.dbo.sp_addlogin", "on"
go
sp_audit "exec_procedure", "all", "sybsystemprocs.dbo.sp_droplogin", "on"
go
sp_audit "exec_procedure", "all", "sybsystemprocs.dbo.sp_modifylogin", "on"
go
sp_audit "exec_procedure", "all", "sybsystemprocs.dbo.sp_adduser", "on"
go
sp_audit "exec_procedure", "all", "sybsystemprocs.dbo.sp_dropuser", "on"
go

sp_audit "exec_procedure", "all", "sybsystemprocs.dbo.sp_addalias", "on"
go

sp_audit "exec_procedure", "all", "sybsystemprocs.dbo.sp_dropalias", "on"
go

--Audit any change at table level for login/user/alias

sp_audit "delete", "all", "master.dbo.syslogins", "on"
go
sp_audit "insert", "all", "master.dbo.syslogins", "on"
go
sp_audit "update", "all", "master.dbo.syslogins", "on"
go

sp_audit "delete", "all", "master.dbo.sysusers", "on"
go
sp_audit "insert", "all", "master.dbo.sysusers", "on"
go
sp_audit "update", "all", "master.dbo.sysusers", "on"
go

sp_audit "delete", "all", "master.dbo.sysalternates", "on"
go
sp_audit "insert", "all", "master.dbo.sysalternates", "on"
go
sp_audit "update", "all", "master.dbo.sysalternates", "on"
go

sp_audit "delete", "all", "master.dbo.syssrvroles", "on"
go
sp_audit "insert", "all", "master.dbo.syssrvroles", "on"
go
sp_audit "update", "all", "master.dbo.syssrvroles", "on"
go

sp_audit "delete", "all", "master.dbo.sysloginroles", "on"
go
sp_audit "insert", "all", "master.dbo.sysloginroles", "on"
go
sp_audit "update", "all", "master.dbo.sysloginroles", "on"
go

I am sure this would help to anyone who wants to enable auditing at Sybase ASE level and is not sure how to achieve the same.


Are toast columns compressed also in shared_buffers?

Yes, they are.

shared_buffers is block-oriented, and stored in the same format as on-disk.

This is generally good for performance, since the decompression is very fast, and the compression means that more fits in shared_buffers. The only case it might hurt is if you have enough storage to fit the whole dataset in shared_buffers uncompressed too.


Create a New AWS RDS Instance of only one database from a snapshot

RDS Snapshots capture the entire server.

There isn't a way to restore only one database.


PostgreSQL Serialisation failure on different ids

The reason is that the table is completely empty.

See the following code in _bt_first in src/backend/access/nbtree/nbtsearch.c:

if (!BufferIsValid(buf))
{
    /*
     * We only get here if the index is completely empty. Lock relation
     * because nothing finer to lock exists.
     */
    PredicateLockRelation(rel, scan->xs_snapshot);
[...]
    return false;
}
else
    PredicateLockPage(rel, BufferGetBlockNumber(buf),
                      scan->xs_snapshot);

Normally, the index scan would put an SIRead lock on the index page where the index entry should be, but since the index is empty, PostgreSQL resorts to putting an SIRead lock on the whole table.

Now since both transactions do that, you get a serialization error at the end of one of them, because the writes conflict with the table-wide read lock.

If the table were not empty, you would notice that concurrent transactions like these sometimes succeed, because they affect different index pages. If the affected uids are sufficiently close together that they are on the same index page, you would still experience a “false positive” serialization error. This is documented:

  While PostgreSQL's Serializable transaction isolation level only allows concurrent transactions to commit if it can prove there is a serial order of execution that would produce the same effect, it doesn't always prevent errors from being raised that would not occur in true serial execution. In particular, it is possible to see unique constraint violations caused by conflicts with overlapping Serializable transactions even after explicitly checking that the key isn't present before attempting to insert it.

To see what kind of SIReadLock PostgreSQL uses, inspect pg_locks.


When you drop table data is still in the database but SQL Server is treating it as free space and it will overwrite it soon. You need to stop all database activities or make a copy of mdf and ldf files and see if you can find a third party tool that supports this. One tool I know is ApexSQL Recover but I guess there are probably others out there.


T-SQL script to check if index rebuild operations are online are off

Referencing the information in Capture Index Maintenance Operations, you might be able to use Extended Events to capture online index operations.

From that post:

Create the Extended Event

Note that this example is using the Ring Buffer, so you may want to pick a different target.  The example is also looking for database_id = 5, so adjust accordingly

-- Create the Event Session
IF EXISTS (
        SELECT *
        FROM sys.server_event_sessions
        WHERE NAME = 'OnlineIXOps'
        )
    DROP EVENT SESSION OnlineIXOps ON SERVER;
GO

CREATE EVENT SESSION OnlineIXOps ON SERVER
    --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    /* The online index operations */
    --ADD EVENT sqlserver.progress_report_online_index_operation
    --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    ADD EVENT sqlserver.progress_report_online_index_operation (
    ACTION(sqlserver.database_name, sqlserver.client_hostname, sqlserver.client_app_name, sqlserver.sql_text, sqlserver.session_id)
    --Change this to match the database in question,
    WHERE sqlserver.database_id = 5
    ) ADD TARGET package0.ring_buffer
    WITH (MAX_DISPATCH_LATENCY = 5 SECONDS)
GO

-- Start the Event Session
ALTER EVENT SESSION OnlineIXOps ON SERVER STATE = START;
GO

Now, run some kind of online index operation.  From the post:

ALTER INDEX IX_PolicyUserRole ON PolicyUserRole REBUILD PARTITION = ALL WITH (online = ON)

Having rebuilt that index online, you can then query the Event session with tsql using a query similar to the following:

SELECT event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name
    ,event_data.value('(event/@timestamp)[1]', 'varchar(50)') AS TIMESTAMP
    ,event_data.value('(event/action[@name="database_name"]/value)[1]', 'varchar(max)') AS DBName
    ,event_data.value('(event/data[@name="object_name"]/value)[1]', 'varchar(max)') AS ObjName
    ,event_data.value('(event/data[@name="index_name"]/value)[1]', 'varchar(max)') AS index_name
    ,event_data.value('(event/data[@name="partition_number"]/value)[1]', 'varchar(max)') AS PartitionNumber
    ,event_data.value('(event/action[@name="session_id"]/value)[1]', 'varchar(max)') AS SessionID
    ,event_data.value('(event/data[@name="build_stage"]/value)[1]', 'varchar(max)') AS Build_Stage
    ,event_data.value('(event/data[@name="build_stage"]/text)[1]', 'varchar(max)') AS BuildStage_Description
    ,event_data.value('(event/action[@name="client_hostname"]/value)[1]', 'varchar(max)') AS Client_hostName
    ,event_data.value('(event/action[@name="client_app_name"]/value)[1]', 'varchar(max)') AS Client_AppName
    ,event_data.value('(event/action[@name="sql_text"]/value)[1]', 'varchar(max)') AS sql_text
    ,event_data.value('(event/data[@name="duration"]/value)[1]', 'Decimal(18,2)') / 1000 AS Duration_ms
    ,event_data.value('(event/data[@name="rows_inserted"]/value)[1]', 'varchar(max)') AS rows_inserted
FROM (
    SELECT evnt.query('.') AS event_data
    FROM (
        SELECT CAST(target_data AS XML) AS TargetData
        FROM sys.dm_xe_sessions AS s
        INNER JOIN sys.dm_xe_session_targets AS t ON s.address = t.event_session_address
        WHERE s.NAME = 'OnlineIXOps'
            AND t.target_name = 'ring_buffer'
        ) AS tab
    CROSS APPLY TargetData.nodes('RingBufferTarget/event') AS split(evnt)
    ) AS evts(event_data)
ORDER BY TIMESTAMP
    ,Build_Stage

When  I tested a non-online rebuild, there we no Extended Events captured.


If the Queue table is MyISAM, I have just the thing for you.

OPTION #1

First, you should store task names in its own table

CREATE TABLE IF NOT EXISTS `Tasks` (
    `ID` int(11) NOT NULL AUTO_INCREMENT,
    `Task` varchar(255) NOT NULL,
    PRIMARY KEY (`ID`).
    UNIQUE KEY (`Task`)
) ENGINE=MyISAM;

Then, remove Task and TaskID from Queue and change the primary key to (TaskID,ID)

CREATE TABLE IF NOT EXISTS `Queue` (
    `ID` int(11) NOT NULL AUTO_INCREMENT,
    `TaskID` int(11) NOT NULL,
    `Client` varchar(30) NOT NULL,
    `AddedInfo` varchar(255) NOT NULL,
    `Status` varchar(20) NOT NULL,
    `Time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`TaskID`,`ID`)
) ENGINE=MyISAM;

Then, you would do something like this each time

INSERT IGNORE INTO Tasks (Task) VALUES ('Mow the Lawn');
SELECT ID INTO @GivenTaskID FROM Tasks WHERE Task = 'Mow the Lawn';
INSERT INTO Queue (TaskID,Client,AddedInfo,Status)
VALUES (@GivenTaskID,'Rolando Edwards','Valued Customer','Active');
INSERT INTO Queue (TaskID,Client,AddedInfo,Status)
VALUES (@GivenTaskID,'Rolando Edwards','Forgot to Pay','Suspended');
INSERT INTO Queue (TaskID,Client,AddedInfo,Status)
VALUES (@GivenTaskID,'Rolando Edwards','Has Not Paid in 90 Days','Deadbeat');
INSERT IGNORE INTO Tasks (Task) VALUES ('Plant New Flowers');
SELECT ID INTO @GivenTaskID FROM Tasks WHERE Task = 'Plant New Flowers';
INSERT INTO Queue (TaskID,Client,AddedInfo,Status)
VALUES (@GivenTaskID,'Pamela Edwards','First Time Customer','Active');
INSERT INTO Queue (TaskID,Client,AddedInfo,Status)
VALUES (@GivenTaskID,'Pamela Edwards','Made First Payment','Active');

OPTION #2

If you do not want to introduce a new table for tasks then redo the table like this

CREATE TABLE IF NOT EXISTS `Queue` (
    `ID` int(11) NOT NULL AUTO_INCREMENT,
    `Task` varchar(255) NOT NULL,
    `Client` varchar(30) NOT NULL,
    `AddedInfo` varchar(255) NOT NULL,
    `Status` varchar(20) NOT NULL,
    `Time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`Task`,`ID`)
) ENGINE=MyISAM;

Then, you would do something like this each time

INSERT INTO Queue (Task,Client,AddedInfo,Status)
VALUES ('Mow the Lawn','Rolando Edwards','Valued Customer','Active');
INSERT INTO Queue (TaskID,Client,AddedInfo,Status)
VALUES ('Mow the Lawn','Rolando Edwards','Forgot to Pay','Suspended');
INSERT INTO Queue (TaskID,Client,AddedInfo,Status)
VALUES ('Mow the Lawn','Rolando Edwards','Has Not Paid in 90 Days','Deadbeat');
INSERT INTO Queue (TaskID,Client,AddedInfo,Status)
VALUES ('Plant New Flowers','Pamela Edwards','First Time Customer','Active');
INSERT INTO Queue (TaskID,Client,AddedInfo,Status)
VALUES ('Plant New Flowers','Pamela Edwards','Made First Payment','Active');

EPILOGUE

In both cases, you will have ID of 3 for 'Mow the Lawn' and an ID of 2 for 'Plant New Flowers'

I have done this before. See my posts

Apr 21, 2012 : How can you have two auto-incremental columns in one table?
Feb 26, 2013 : How to use 2 auto increment columns in MySQL phpmyadmin

GIVE IT A TRY !!!


SQL Server System Versioned Table Execution Timing

You're sort of correct (although not in a good way, as it turns out). From the docs:

  The times recorded in the system datetime2 columns are based on the begin time of the transaction itself. For example, all rows inserted within a single transaction will have the same UTC time recorded in the column corresponding to the start of the SYSTEM_TIME period.

Normally though, unless these were explicit transactions, a DATETIME2 variable should have more than enough precision to split the results, but that's not what we're seeing...

Reproducing

This dbfiddle of 2017 matches OP's situation, but if I run locally on my 2017 instance I do not get results for @t0.

I was unable to reproduce locally on an RTM of 2017 (Express) as well as the latest CU 17. I thus began to assume my laptop is either way too fast, or way too slow, to make this discrepancy obvious.

However, after playing around with multiple fiddles I have found the same code is returning different results, which leads me to believe there is undocumented lag or perhaps a bug in the time stamping feature for temporal tables.

Example

dbfiddle

Conclusion

It took 50+ executions of the same code to trigger all of these differing results (sorry for spamming the site, Jack) but eventually it became clear that the time stamping for temporal tables cannot be trusted on a sufficiently fast enough system. And not only that, but even when the time stamps matched up the results are still not guaranteed to be the same.

I am assuming this is most apparent on faster servers, since my laptop isn't actually all that great, and the STATISTICS TIME on my local tests were consistently 5-6x higher than the ones dbfiddle was returning.

This is probably worth opening a bug with Microsoft about as it seems to be a pretty problematic behavior.


Change built-in default privileges in PostgreSQL?

The CONNECT privilege for PUBLIC looks somewhat special:

\l
template1   | postgres | UTF8     | Hungarian, Hungary | Hungarian, Hungary | postgres=CTc/postgres

That is, user test (which is the member of PUBLIC only) cannot connect to template1:

postgres=# \c template1 test
Password for user test:
FATAL:  permission denied for database "template1"
DETAIL:  User does not have CONNECT privilege.
Previous connection kept

Now let's create a database on top of this:

CREATE DATABASE test3 TEMPLATE template1;

And connect to it with test:

postgres=# \c test3 test
Password for user test:
You are now connected to database "test3" as user "test".
test3=> \c postgres postgres

Just a bit of context:

postgres=# SELECT version();
                           version
-------------------------------------------------------------
 PostgreSQL 9.1.4, compiled by Visual C++ build 1500, 32-bit
(1 row)

With this in mind and the fact that ALTER DEFAULT PRIVILEGES does not provide means the change the defaults on the database level, I would say there is no way to do this automatically.  If you have to create lots of databases, probably a setup script would come handy.


SQL Server: get meta-Info about LDF-File

This returns a String like '00000064:000000e9:0033'. But how can I convert this into a timestamp?

You can't.  An LSN is not a timestamp, nor does it store any portion of date and/or time in it's bits.

SQL Server does not log the datetime stamp for every logged operation.  It does keep track of these timestamps for transactions, but not for each and every single logged operation.

Note: You are trying to reference a Change Data Capture DMF with sys.fn_cdc_map_lsn_to_time.

I think you are taking the wrong approach to some sort of question that you are trying to answer.  Datetime stamps on transaction log records wouldn't really tell you much, even if it is possible.

Let's take a step back and let us know why you're trying to do this.


Can I change CITEXT columns to VARCHAR without encountering any surprise difficulties?

Will there be any space gains from moving in this direction?

No. citext and text (or varchar) occupy the same space on disk and in RAM.

  Case-insensitive queries are not necessary on these columns.

Then there is no point to use citext.

  I have counts against this table that are predicated on 2 columns, and these counts are taking over an hour. The table has 60 columns.

count() is not affected by citext vs. text (or varchar) at all. "60 columns" might be something to look into. No problem if you actually need all of them - but do you? And are you using proper data types etc.

  I'm primarily interested in whether indexes need to be rebuilt if they contain a column that has changed from CITEXT to VARCHAR.

All indexes involving any of the affected columns have to be rebuilt. (Drop index, change type, recreate index.)

Personally, I stay away from citext after mixed experiences.


With having QUORUM for both read & write requrests you achieve strong consistency per following equation R + W > N, where R - read replica count, W - write replica count, and N - replication factor.

If you write with QUORUM, then 2 nodes will get fresh data, and when you read with QUORUM, then one node may have the old data, and one will have fresh data - Cassandra will detect discrepancy & use the newest version.


How to display the number of days elapsed between two dates denoted in columns-specifically those exceeding 30 days?

Either restate the calculation in the predicate

SELECT order_id,
       (shipped_date - order_date) AS "Number of Days elapsed"
FROM orders
WHERE shipped_date - order_date > 30
ORDER BY (shipped_date - order_date) desc;

or nest the query

SELECT order_id, "Number of Days elapsed"
  FROM (SELECT order_id,
               (shipped_date - order_date) AS "Number of Days elapsed"
          FROM orders)
 WHERE "Number of Days elapsed" > 30
 ORDER BY "Number of Days elapsed" desc

If you are going to nest the query, though, it probably makes sense to pick a case-insensitive alias in the inner query and to do the case-sensitive aliasing later on

SELECT order_id, days_elapsed "Number of Days elapsed"
  FROM (SELECT order_id,
               (shipped_date - order_date) days_elapsed
          FROM orders)
 WHERE days_elapsed > 30
 ORDER BY days_elapsed  desc


If NoSQL stands for "Not only SQL", is SQL a subset of NoSQL?

TL;DR
The main distinction between database systems is not the language used to query the database, but rather the consistency model of the system. The Venn diagram should be two intersecting sets - SQL is not a proper subset of NoSQL, but rather its own data access language which may or may not be complemented by other techniques. SQL would be a proper subset of database query languages.

There is NoSQL, OldSQL and NewSQL.
NoSQL used to mean "Not SQL" but now is (more) likely to mean "Not only SQL" - as many of the providers of NoSQL systems are (trying to) bolt-on/graft SQL interfaces over their Key-Value (KV), Document or Graph stores.
OldSQL systems are essentially the main RDBMS providers' offerings (Oracle, SQL Server, Sybase, PostgreSQL, MySQL...). On page 13 of this presentation (.pdf), Michael Stonebraker purports to show that OldSQL only spends 4% of the time doing "useful" work - also to be found here:

His contention is that one should split OLTP work and OLAP work between different systems - OLTP should be done by shared nothing sharded architectures such as, suprise, surprise, his own system, VoltDB and that OLAP should be done by columnar store (also, see here) type architectures (such as Vertica, in which he also had a role). It is worth noting that Stonebraker, as well as being very successful in the commercial arena is also huge in academia and has won a Turing award - Computer Science's "Nobel prize"!
NewSQL is (IMHO) the most interesting of the systems to spring (figuratively speaking) like the Phoenix reborn from the ashes of OldSQL. Their USP is that they are HTAP systems (Hybid Transactional and Analytical Processing).
These are distributed systems which can support both OLAP and OLTP queries simultaneously due to the data being spread over several nodes - which can be on or in the same rack, data centre or continent and/or be globally distributed between and amongst cloud providers to increase resilience and redundancy - expect to add ~ a '0' to the cost for every fractional '9' you add to your uptime provision.
They use a consensus algorithm (usually Raft or Paxos) to coordinate nodes' data and sharding is transparent - even to the systems' DBA's. The three exemplars of such systems would be CockroachDB, TiDB and YugaByte.
It is interesting to note that, while having a certain level of success, these systems are not (yet?) household names. The "big boys" are fighting back with columnar store and KV offerings bolted/grafted on to their own systems. Of particular interest in this debate is that these systems themselves sit "on top" of a KV store (usually RocksDB - although CockroachDB is developing their own one in Go called Pebble). PostgreSQL also has documents (JSONB) and a KV store.
To answer the question, the real distinguishing feature between systems is not the interface one uses to query the data (which can and does range from direct C language programming (imperative) to SQL (declarative) - and flavours/adaptations thereof), but rather the transaction consistency model.
These models are either ACID (consistent) vs. BASE (available) and the key is where these systems fit with respect to the CAP theorem. Also, KV stores can support some or all ACID transaction characteristics.
OldSQL and NewSQL value consistency above all else ('CP' under the CAP system) - their argument is that, for example, a banking system with inconsistent results is a recipe for disaster (true!).
However the NoSQL aficionados would suggest that not all systems require cast-iron consistency. For example, ordering a book from Amazon (say) using a BASE database ('AP' in CAP terms) - might (incorrect stock level shown) be a few days late, or even cancelled - but the upside is faster queries and easier operation (no consensus to maintain).
This is the crux of the distinction between database systems - 'CP' or 'AP'! CP will always either give you the correct answer (majority of nodes still available) or no answer, whereas AP systems will normally respond (even if only one node is up) but your answer may not have taken other nodes' data updates into account when responding (i.e. network links between them down...).
I hope this is a satisfactory "first-pass" at an answer - it's a large topic and, for db nerds, absolutely fascinating. I would urge you to read around it (Wiki is a good start, but no substitute for primary sources). In particular, I would advise you to look in detail at the underlying architectures of CockroachDB and TiDB and see how they shard and move data from node to node while maintaining consistency.
There are comprehensive lists of various NoSQL systems here and here (best IMHO). There's also a popularity ratings site here (but avoid the comparisons - they just repeat the blurb from the systems' websites - often out-of-date).
Final word, there are "mixed-model" (or multi-model) databases (ArangoDB and OrientDB - both have F/LOSS and Enterprise versions - OrientDB now part of SAP), but there's a reason why they're not household names. ArangoDB doesn't support ANSI SQL, but rather its own flavour - AQL and neither does OrientDB support ISO SQL.
The best multi-model db that I've worked with is PostgreSQL which has KV and document stores as mentioned above. You can use SQL to query these and join with "ordinary" tables. Work is ongoing to add OpenCypher - an open graphical query language standard - to it (see here and here).
Final, final word: PostgreSQL also has a Columnar Store extension and a Time Series extension - both of which are very important (sub)-classes of database system. Both extensions are just that, extensions and not forks - you can use "normal" SQL (i.e. the full range of PostgreSQL's very standards compatible SQL) with these extensions.
So, we can see that while NoSQL is, in some cases, but not all (many systems designers are happy to remain as simple KV stores for example), adding SQL capabilities, SQL is fighting back, adopting and adapting to new and/or more sophisticated data storage and retrieval requirements - some of which I haven't even touched on (GIS systems...). So to say that NoSQL fully encompasses SQL would be an incomplete picture...
As pointed out in comments to the question itself, the SQL standard now tackles non-relational paradigms and this will only increase into the future! Also addressed by other answers - worth reading (1 & 2)!


Shell script - psql run multiple statements from a variable one by one in one transaction

Yes, we can do it.

echo the variable and then pipe it to psql.

echo "$analyze_query"  |  psql -h host -U user -d db

Use () to group the command output then pipe it to psql.

(for i in $analyze_query
do
 echo $i
done ) |  psql -h host -U user -d db


Is it possible for SQL statements to execute concurrently within a single session in SQL Server?

While Brent's answer is correct for for all practical purposes, and this is not something I've ever seen someone worry about, it is possible for multiple invocations of a stored procedure in a session to affect each other through a session-scoped #temp table.

The good news is it's extremely unlikely to happen in the wild because

1) #Temp tables declared inside a stored procedures or nested batches don't actually have session visibility (or lifetime).  And these are by far the most common case.

2) It requires MultipleActiveResultsets and either some very strange async client programming, or for the stored procedure to return a resultset in the middle, and the client to call another instance of the stored procedure while processing the results from the first.

Here's a contrived example:

using System;
using System.Data.SqlClient;

namespace ado.nettest
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var con = new SqlConnection("Server=localhost;database=tempdb;integrated security=true;MultipleActiveResultSets = True"))
            {
                con.Open();

                var procDdl = @"
create table #t(id int)
exec ('
create procedure #foo
as
begin
  insert into #t(id) values (1);
  select top 10000 * from sys.messages m, sys.messages m2;
  select count(*) rc from #t;
  delete from #t;
end
');
";
                var cmdDDL = con.CreateCommand();
                cmdDDL.CommandText = procDdl;
                cmdDDL.ExecuteNonQuery();

                var cmd = con.CreateCommand();
                cmd.CommandText = "exec #foo";
                using (var rdr = cmd.ExecuteReader())
                {
                    rdr.Read();

                    var cmd2 = con.CreateCommand();
                    cmd2.CommandText = "exec #foo";
                    using (var rdr2 = cmd2.ExecuteReader())
                    {

                    }

                    while (rdr.Read())
                    {

                    }
                    rdr.NextResult();
                    rdr.Read();
                    var rc = rdr.GetInt32(0);
                    Console.WriteLine($"Numer of rows in temp table {rc}");

                }

            }

            Console.WriteLine("Hit any key to exit");
            Console.ReadKey();
        }
    }
}

which outputs

Numer of rows in temp table 0
Hit any key to exit

because the second invocation of the stored procedure inserted a row, and then deleted all the rows from #t while the first invocation was waiting for the client to fetch the rows from its first resultset.  Note that if the first resultset was small, the rows might get buffered and execution could continue without sending anything to the client.

If you move the

create table #t(id int)

into the stored procedure it outputs:

Numer of rows in temp table 1
Hit any key to exit

And with the temp table declared inside the procedure, if you change the second query to

cmd2.CommandText = "select * from #t";

It fails with:

  'Invalid object name '#t'.'

Because a #temp table created inside a stored procedure or nested batch is only visible in that stored procedure or batch and in nested procedures and batches that it calls, and is destroyed when the procedure or batch ends.


The SQL-DBA told me that selection from table is faster because when
  you select from a View, all data will be selected first then filtered,
  but from table it will apply filtration directly.

As a general statement this is nonsense. The view definitions are expanded out during query optimization and the predicates are pushed down as if you had filtered against the table directly.

You can check the execution plans to confirm this.

If you are filtering on the result of a calculated expression then this may be problematic however (the same as any unsargable expression on a table) and there have been issues with SQL Server not pushing predicates down as far as it could do for views containing window functions.


Red Gate's SQL Prompt is what I use and has several dozen different settings. It also has a better auto suggestion and code snippet engine than many others I've tried. Well worth the money in terms of time saved.


Changing the target schema on export or import?

There is no direct way to do this.
You could for example restore the dump into a temporary database, drop the public schema there, rename your schema to public and then dump again.


Why create an "oracle" user for installing Oracle 10g or 11g?

PostgreSQL refuses to run under root, and the reasons there may be of interest in this question.

This approach allows you to essentially separate UNIX and db permissions as Phil mentions above, but there are a number of reasons why this is important especially when you have a programmable server like Oracle.

The first is that generally speaking you want to be able to contain the behavior of the system in the event of a bug in your stored procedures or otherwise.  Bugs in stored procedures that allow the db to overwrite arbitrary files are really bad in a database, but they become far worse when you have the ability to overwrite operating system binaries.  So this allows you to lock down and contain damage in a way you can't do if the db runs as root.

The second is that you can essentially give the DBA a smaller area of responsibility.


How to create comment on table from query result

While I am not sure what you want to achieve (why it makes sense to store a count as a comment, especially if your table will get new rows (or lose some to DELETEs)), but here is how what your problem is and how you can solve it.

First, check what psql thinks you have in the variable:

\echo :list
selectcount(*)fromdepartment.master_data

Clearly not the result of your query.  To store that, you'll have to use \gset:

> SELECT count(*) AS master_data_count FROM department.master_data;
 master_data_count
───────────────────
        1234567890
(1 row)

> \gset
> \echo :master_data_count
1234567890

If you would try to set the table description now, you'd get another error:

ERROR:  syntax error at or near "1234567890"
LINE 1: ...ABLE department.master_data IS selectcoun...

This is because the comment must be a (quote-delimited) text.  To get one out of your variable, you have two options.  One is adding escaped quotes to the variable using psql (using the same feature you misused setting :list:

> \set master_data_count '\'' :master_data_count '\''
> \echo :master_data_count
'1234567890'

Or you can add the quote already in your query, so SELECT count(*) turns into

SELECT $$'$$ || count(*) || $$'$$

(I used dollar quoting above to avoid typing things like ''''.)

The result you can already use as a comment, as it contains the necessary quotes.


What's the low-down on MariaDB JSON support?

No plans that I know about.

Regarding constraints, I assume you mean for JSON validation: See Changes and Improvements in MariaDB 10.4:

  The JSON_VALID function is automatically used as a CHECK constraint
  for the JSON data type alias in order to ensure that a valid json
  document is inserted (MDEV-13916)

... so you don't have to set the constraint explicitly.


Query results not as expected - Jsonb Postgres

Try this.. Just out of curiosity..

CREATE INDEX idx_btree_ig_follower_count
  ON public.influencers
  USING BTREE (CAST(ig->>'follower_count' AS int));

Then in your query, cast to int.

SELECT count(*)
FROM "influencers"
WHERE ((ig ->> 'follower_count')::int >= '1000')
  AND ((ig ->> 'follower_count')::int <= '10000');

You don't need to specify IS NOT NULL if you specify >=, and <=

Test case

I created some test data with 10 million rows of jsonb, with btree index over it.

CREATE TABLE influences
AS
  SELECT
    jsonb_build_object('follower_count', trunc(random()*1e7)::int) AS ig
  FROM generate_series(1,1e7);

CREATE INDEX ON influences( CAST( (ig->>'follower_count') AS int) );

I'm getting the same query plan you provided,. You can see mine here.

EXPLAIN (ANALYZE,BUFFERS)
SELECT count(*) FROM influences
WHERE (ig->>'follower_count')::int BETWEEN 0 AND 854780;
                                                                     QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=130124.36..130124.37 rows=1 width=0) (actual time=482.615..482.615 rows=1 loops=1)
   Buffers: shared hit=85674
   ->  Bitmap Heap Scan on influences  (cost=18483.87..127946.94 rows=870969 width=0) (actual time=206.182..426.880 rows=855570 loops=1)
         Recheck Cond: ((((ig ->> 'follower_count'::text))::integer >= 0) AND (((ig ->> 'follower_count'::text))::integer <= 854780))
         Heap Blocks: exact=83334
         Buffers: shared hit=85674
         ->  Bitmap Index Scan on influences_int4_idx  (cost=0.00..18266.13 rows=870969 width=0) (actual time=182.158..182.158 rows=855570 loops=1)
               Index Cond: ((((ig ->> 'follower_count'::text))::integer >= 0) AND (((ig ->> 'follower_count'::text))::integer <= 854780))
               Buffers: shared hit=2340
 Planning time: 0.341 ms
 Execution time: 482.665 ms
(11 rows)

I'm aggregating far more rows, 855570 in my example, and it's over 10x faster.

I clustered it

CLUSTER influences USING influences_int4_idx ;

And, that halfed the time further to 200ms

EXPLAIN (ANALYZE,BUFFERS) SELECT count(*) FROM influences WHERE (ig->>'follower_count')::int BETWEEN 0 AND 854780;
                                                                    QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=130124.66..130124.67 rows=1 width=0) (actual time=211.880..211.881 rows=1 loops=1)
   Buffers: shared hit=4 read=9466
   ->  Bitmap Heap Scan on influences  (cost=18483.94..127947.22 rows=870976 width=0) (actual time=69.503..155.670 rows=855570 loops=1)
         Recheck Cond: ((((ig ->> 'follower_count'::text))::integer >= 0) AND (((ig ->> 'follower_count'::text))::integer <= 854780))
         Heap Blocks: exact=7130
         Buffers: shared hit=4 read=9466
         ->  Bitmap Index Scan on influences_int4_idx  (cost=0.00..18266.20 rows=870976 width=0) (actual time=68.150..68.150 rows=855570 loops=1)
               Index Cond: ((((ig ->> 'follower_count'::text))::integer >= 0) AND (((ig ->> 'follower_count'::text))::integer <= 854780))
               Buffers: shared hit=3 read=2337
 Planning time: 0.412 ms
 Execution time: 211.929 ms
(11 rows)


Netezza migration to Azure Synapse - Stored Procedures?

Azure Synapse Pathway is a new tool currently in preview which can look at existing Netezza code for you, attempt to convert it and flag up errors.
Download the tool and run it against your Netezza installation.  Start here:
https://docs.microsoft.com/en-us/sql/tools/synapse-pathway/azure-synapse-pathway-overview?view=azure-sqldw-latest
As a wizard, it's probably at best going to give you 80/20, but will at least offer a start.


Here's one possibility:

with departures(departure_id, cnt) as (
    select f.departure_id, count(t.id_passenger)
    from flight f
    join takes t
        on f.id = t.id_flight
    where year(departure_date) = ?
    group by f.departure_id
),
arrivals as (arrival_id, cnt) as (
    select f.arrival_id, count(t.id_passenger)
    from flight f
    join takes t
        on f.id = t.id_flight
    where year(arrival_date) = ?
    group by f.arrival_id
)
select position, max(cnt)
from (
    select arrival_id as position, cnt
    from arrivals
    union
    select departure_id as position, cnt
    from departures
) as t;


Many-to-many relationship with attributes

I would use The Party Model, so that you can associate a book with an Individual or Organization, not just an Individual. For example, you might want Random House in the role of Publisher. Or Mötley Crüe as the author of the book, not just Tommy Lee.
Otherwise, looks good. You might want to consider associating the book and the PartyRole, instead of Party and Role separately, as you might have extra info pertaining to a Party in a particular Role. Perhaps author pseudonyms.

Postgres-y SQL, but close enough to SQLite:

create table parties (
  party_id int primary key,
  party_type smallint not null references party_types(party_type_id), --1=individual,2=org
  name text not null
);

create table party_roles (
  party_role_id int primary key,
  party_id int not null references parties(party_id),
  role_id smallint not null references roles(role_id),
  ...
  extra information about a party pertaining to a role
  ...
);

create table book_roles (
  book_id int references books(book_id),
  party_role_id int references party_roles(party_role_id),

  primary key (book_id, party_role_id)
);


As per MongoDB BLOG Here In certain situations, count can be avoided for better performance Counting things can be hard on a database. The obvious things to count, number of items in a collection or index, are easy to keep track of, but as soon as you start trying to count records based on a query, things get slower and slower. This is because the database has to do more work scanning practically every item in the database to see if it matches.

MongoDB does have an optimisation for a fast count where all the queried fields are indexed and the query is based around equivalence, but only in that circumstance.


Debugging table permissions for a windows user

Please run this code to find out if you have a deny on that user under the column state_desc.

SELECT permission_name,
   state_desc,
   type_desc,
   U.NAME,
   Object_name(major_id)
FROM   sys.database_permissions P
   JOIN sys.tables T
     ON P.major_id = T.object_id
   JOIN sysusers U
     ON U.uid = P.grantee_principal_id


What is the best way to handle a join / group_concat query in MySQL

Simplicity is beautify. Multiple JOINs are allowed. There is no need to the JOINs to be LEFT JOINS as you want data that maches always.
SELECT tasks.task, GROUP_CONCAT( users.name SEPARATOR ',' ) AS names,
FROM assignees
JOIN tasks ON assignees.task_id = tasks.id
JOIN users ON assignees.user_id = users.id
WHERE tasks.completed IS NULL
GROUP BY assignees.task_id


Make MySQL server terminate when shell exits or on ctrl-c

You could try --gdb. That will make it respond to SIGINT, though I'm not sure if that has potential to corrupt the database. --one-thread may also be of some help.


What is the best approach to managing daily excel flat file imports into SQL Server with varying column counts per file?

You're dealing with unstructured data here I'm afraid and honestly, I feel your pain. I don't think you can really do what you want in SSIS - partially because it will expect your Excel format to be fixed. Even if you get it fixed, with SSIS and Excel - you'll have problems with how it guesses the metadata types for columns based on a sample of the data in the sheet - with the Excel provider, you only have 2 choices for text data for example - NVARCHAR(255) and NVARCHAR(MAX). It can get ugly pretty fast if you try to enforce data-typing at a data level even with fixed columns but varying lengths.

As such, I think you'll need to embrace the unstructured nature of the data and store it as such - storing it as an XML/JSON data types and converting the data from the spreadsheet and letting the MVC application parse the XML. The only other way I can suggest is using something like a key-value store. There's huge drawbacks to this from a relational perspective though. This is commonly known as EAV (Entity-Attribute-Value)

You might also want to consider non-SQL Server alternatives that can take unstructured data and search on it as well.


your database is running in archivelog mode. An initial backup + archives allow you to recover your database to any point in time between the backup and the last available archivelog file.
If this is a big import you are most likely going to make a backup afterwards. If you have no need to recover the database to a state between now and the previous backup, you could use rman to delete the archived log files, freeing space so the archiver can continue.
An other option is to increase the db_recovery_file_dest_size parameter that could be in place since you claim to have plenty of space available.

sqlplus / as sysdba
show parameter db_recovery_file_dest_size
alter system set db_recovery_file_dest_size = {A_BIGGER_SIZE}

or

rman target=/
delete archivelog all;

don't forget to make a backup, deleting the archives makes recovery impossible.


You did not mention your workload, but I run log backups every minute in our OLTP environment without issue (granted, we don't process thousands of transactions per second).  If a log backup happens to take over 1 minute, there is no failure.  The job will merely run again on the minute mark after the current job completes.

As Brent says in What happens to transaction log backups during full backups?

  Backups are beautiful things. You should take full ones, and probably
  differential ones, and if you’re in full recovery model, definitely
  log ones. How often you take them is up to you and your boss. Or maybe
  their boss. But it’s definitely not up to you. Unless you’re your
  boss.

  Log backups during full backups won’t hurt anything, and may end up
  helping things if your full backup fails, and you need to restore
  something.

  Log backups during full backups will not truncate the log. That has to
  wait until the first log backup after the full finishes.

Some additional information regarding Understanding Schedules and Long-Running Jobs, which I believe still holds true today:

  What happens when a scheduled job runs longer than the scheduled
  interval time period? For example, if a job is scheduled to execute
  every 10 minutes and takes 35 minutes to complete, does the job
  execute again while it's still running?

  On the SQL Server Most Valuable Professional (MVP) newsgroup, a
  private forum for SQL Server MVPs, Microsoft published two design
  facts about the SQL Server Agent that can help answer this question.
  First, the SQL Server Agent runs only one instance of a job at a time.
  Second, the SQL Server Agent calculates the Next Run Date at the end
  of the job. So, if a job execution takes longer than its scheduled
  interval, the job will rerun at the next recurrence point. For
  example, imagine that Job A is scheduled with a 1-minute recurrence
  interval. Job A starts at 1:25 p.m. and runs for 1 minute 45 seconds.
  Job A is still running at 1:26, when the next scheduled recurrence
  should happen, and the SQL Server Agent will run only one instance of
  a job at a time—so nothing happens at 1:26 p.m. When Job A ends at
  1:26:45 p.m., the SQL Server Agent resets the Next Run Date. Based on
  the specified 1-minute recurrence interval, the next time the job will
  run is 1:27 p.m.


modeling advice for table which may have 2 father tables

I would Simply add a receipt_id to the collection and payment table.

This is a better design (objectively normalised) that allows your model to grow should your requirements change

This would solve the problem for your document model also - activity, reciept, project would contain a document_id, and you could expand your model to include any number of tables containing a document_id.

If you need to differentiate between documents stored in the table itself, you could add a document_type to make that distinction.


Progressive child tables scan in PostgreSQL?

The problem is that it scans all child tables and only then does the
  LIMIT 10. Indexes for all these tables are quite big and don't fit
  into memory, so it takes up to 20 seconds to complete this query.

If you have the correct indexes, which in this case would be on events (device_id,event_time), then it will only hit a tiny part of each index (the path leading to the leaf page containing the highest event_time for the specified device_id for each partition), and that part will probably be readily cached, depending on how many distinct device_id exist and are routinely queried.

When version 12 is released, then if you use declarative range partitioning rather than partitioning by inheritance the system will be smart enough to hit partitions in appropriate order and not startup the index scan on more partitions once the LIMIT is already satisfied.  However, this might only be a minor improvement in your case.  If you have the right index, it should be so fast already that no improvement is necessary, and if you have the wrong index it might be annoyingly slow even with this improvement.

If you can't wait for version 12 and refactor your partitioning, and can't build the appropriate indexes, then a set returning function which manually iterates through the partitions in order might the way to go, despite being tedious, ugly, and error prone.


Getting locked out of PostgreSQL databases

The documentation should cover that pretty well. See in particular pg_hba.conf. It's hard to make "simple" guides on security, because if you simplify in the wrong way you make it wrong.
Trivially so long as you have the ability to edit pg_hba.conf and reload the server. Just add an pg_hba.conf entry with trust authentication, reload PostgreSQL (pg_ctl reload, /etc/init.d/postgresql reload, whatever), change the password, and put pg_hba.conf back how it was.
Disabling hostbased and password login security in PostgreSQL can be done by setting trust authentication in pg_hba.conf. It's usually much saner to use peer authentication for local connections instead, but this only works on unix systems.
Users are just roles with the LOGIN attribute. Roles are global across the server. Most GRANTs of rights to users/roles are specific to particular databases or objects within databases. See the documentation for the GRANT command.
"trick?". I think you'll want to post a new, more detailed and specific question on https://dba.stackexchange.com/ with the details, including exact commands run, exact error messages, etc.

I'm kind of surprised by your expectation that dropping then re-creating a user would retain their settings. This would create the opportunity for colossal security holes. The sysadmin joe leaves, you drop their account. New data entry joe starts, you create their account. Oops! The new Joe has all the rights of the completely different old Joe.

If you drop a table then re-create it, does it still have its contents? Why would users/roles be any different?

If you don't want to drop a user account, don't drop the user account. Disable it  by REVOKEing CONNECT rights on the database(s), changing their password, etc.


ERROR! MySQL is running but PID file could not be found - MariaDB 10.1 - CentOS 7 - Fresh VM

Seems the issue was how it is started.

Using the following, all is well!!

[root@sql ~]# systemctl start mariadb.service
[root@sql ~]# systemctl enable mariadb.service
[root@sql ~]# /etc/init.d/mysql status
SUCCESS! MySQL running (920)

So don't use /etc/init.d/mysql start as stated in the install guide...

https://mariadb.com/kb/en/mariadb/yum/


I have no idea how it happened, but:
1) mysqld was not using /etc/mysql/my.cnf file.

2) I could not find any other my.cnf files on the machine.

3) So i changed /etc/mysql/my.cnf to point to my data files

4) And I created a symlink for socket:

ln -s /var/run/mysqld/mysqld.sock /tmp/mysql.sock

5) And then I run the deamon:

/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/mysql/my.cnf


How to create a new instance of PostgresSQL on OS-X 10.8 Server

"all others databases and roles created by my own are lost!"

I can't speak to the best practices for creating a new database, but as part of the Mountain Lion Server upgrade all of the "old" Postgres data should have been backed up to a subdirectory under /Library(/Server?)  Unfortunately as soon as I discovered this I moved-and-renamed the directory for safekeeping so I don't have the exact name!

I have a completely separate install of Postgres that I built myself for webapp purposes, but I certainly wouldn't call what my approach a "best practice". Largely what I did was build a "stock" install using a very similar set of configure parameters as Apple did (discovered by running pg_config) but with a different install location and default port setting. Following that I coptied-and-tweaked /Applications/Server.app/Contents/ServerRoot/System/Library/LaunchDaemons/org.postgresql.postgres.plist and that's worked fine for me so far.


Strategies for "checking out" records for processing

I am not a huge fan of either the extra "lock" table or the idea of locking the whole table to grab the next record. I get why it is being done, but that also hurts concurrency for operations that are updating to release a locked record (surely two processes can't be fighting over that when it is not possible for two processes to have locked the same record at the same time).

My preference would be to add a ProcessStatusID (typically TINYINT) column to the table with the data being processed. And is there a field for LastModifiedDate? If not, then it should be added. If yes, then do these records get updated outside of this processing? If records can be updated outside of this particular process, then another field should be added to track StatusModifiedDate (or something like that). For the rest of this answer I will just use "StatusModifiedDate" as it is clear in its meaning (and in fact, could be used as the field name even if there is currently no "LastModifiedDate" field).

The values for ProcessStatusID (which should be placed into a new lookup table called "ProcessStatus" and Foreign Keyed to this table) could be:

Completed (or even "Pending" in this case as both mean "ready to be processed")
In Process (or "Processing")
Error (or "WTF?")

At this point it seems safe to assume that from the application, it just wants to grab the next record to process and won't be passing anything in to help make that decision. So we want to grab the oldest (at least in terms of StatusModifiedDate) record that is set to "Completed" / "Pending". Something along the lines of:

SELECT TOP 1 pt.RecordID
FROM   ProcessTable pt
WHERE  pt.StatusID = 1
ORDER BY pt.StatusModifiedDate ASC;

We also want to update that record to "In Process" at the same time to prevent the other process from grabbing it. We could use the OUTPUT clause to let us do the UPDATE and SELECT in the same transaction:

UPDATE TOP (1) pt
SET    pt.StatusID = 2,
       pt.StatusModifiedDate = GETDATE() -- or GETUTCDATE()
OUTPUT INSERTED.RecordID
FROM   ProcessTable pt
WHERE  pt.StatusID = 1;

The main problem here is that while we can do a TOP (1) in an UPDATE operation, there is no way to do an ORDER BY. But, we can wrap it in a CTE to combine those two concepts:

;WITH cte AS
(
   SELECT TOP 1 pt.RecordID
   FROM   ProcessTable pt (READPAST, ROWLOCK, UPDLOCK)
   WHERE  pt.StatusID = 1
   ORDER BY pt.StatusModifiedDate ASC;
)
UPDATE cte
SET    cte.StatusID = 2,
       cte.StatusModifiedDate = GETDATE() -- or GETUTCDATE()
OUTPUT INSERTED.RecordID;

The obvious question is whether or not two processes doing the SELECT at the same time can grab the same record. I am pretty sure that the UPDATE with OUTPUT clause, especially combined with the READPAST and UPDLOCK hints (see below for more details), will be fine. However, I have not tested this exact scenario. If for some reason the above query does not take care of the race condition, then adding the following will: application locks.

The CTE query above can be wrapped in sp_getapplock and sp_releaseapplock to create a "gate keeper" for the process. In doing so, only one process at a time will be able to enter in order to run the query above. The other process(es) will be blocked until the process with the applock releases it. And since this step of the overall process is just to grab the RecordID, it is fairly quick and won't be blocking the other process(es) for very long. And, just as with the CTE query, we are not blocking the entire table, thereby allowing other updates to other rows (to set their status to either "Completed" or "Error"). Essentially:

BEGIN TRANSACTION;
EXEC sp_getapplock @Resource = 'GetNextRecordToProcess', @LockMode = 'Exclusive';

   {CTE UPDATE query shown above}

EXEC sp_releaseapplock @Resource = 'GetNextRecordToProcess';
COMMIT TRANSACTION;

Application locks are very nice but should be used sparingly.

Lastly, you just need a stored procedure to handle setting the status to either "Completed" or "Error". And that can be a simple:

CREATE PROCEDURE ProcessTable_SetProcessStatusID
(
   @RecordID INT,
   @ProcessStatusID TINYINT
)
AS
SET NOCOUNT ON;

UPDATE pt
SET    pt.ProcessStatusID = @ProcessStatusID,
       pt.StatusModifiedDate = GETDATE() -- or GETUTCDATE()
FROM   ProcessTable pt
WHERE  pt.RecordID = @RecordID;

Table Hints (found at Hints (Transact-SQL) - Table):

READPAST (seems to fit this exact scenario)

  Specifies that the Database Engine not read rows that are locked by other transactions. When READPAST is specified, row-level locks are skipped. That is, the Database Engine skips past the rows instead of blocking the current transaction until the locks are released...READPAST is primarily used to reduce locking contention when implementing a work queue that uses a SQL Server table. A queue reader that uses READPAST skips past queue entries locked by other transactions to the next available queue entry, without having to wait until the other transactions release their locks.

ROWLOCK (just to be safe)

  Specifies that row locks are taken when page or table locks are ordinarily taken.

UPDLOCK

  Specifies that update locks are to be taken and held until the transaction completes. UPDLOCK takes update locks for read operations only at the row-level or page-level.


Using a Parameter Query to generate Charts in MS Access Reports

You have have to try something a little loopy, such as the following:

Reports have a property called Tag
Open the Report in Design View
Open the Property Sheet and Select the 'Other' Tab
The Tag property is on the bottom of the list

You can access that Tag as follows:

Reports("Report Name").Tag

Simply replace the [Identifier] with either Reports("Report Name").Tag or Me.Tag

That way, if you open the query, you will still be prompted to input the UserID

When you open the Report, set Me.Tag to whatever number you want. How to you set it in the first place ?

On the property sheet, Click Event Tab. In the OnLoad Event, click the three dots (ellipses). Menu pop ups. Click Code Builder. VBA opens on with :

Private Sub Report_Load()

End Sub

Now put that setting in (say the UserID is from a form):

Private Sub Report_Load()

   Me.Tag = Forms("Form Name")("Field Name")

End Sub

Give it a Try !!!


Should I drop a foreign key if I use a soft delete?

Keep it.  A foreign key exists primarily to ensure that the value given in the child table really does exist in the parent (foreign) table.  Cascading deletes or updates are, generally, a distant second.

That's the general case; your specific business rules may be different.


1) The format with dashes are for command line and with underscore in my.cnf and as global variable. Any of the following has to work (tested on Percona Server 5.6.16):

log-bin-trust-function-creators=1
log-bin-trust-function-creators
log_bin_trust_function_creators=1
log_bin_trust_function_creators

(since it's a boolean you don't need to specify =1)

2) You can use G, M, k just fine. MySQL will pick that up. If you have a wrong config it will complain won't just ignore it silently.

3) Variable order doesn't matter with the obvious exception if you specify something twice under the same section. In that case the one coming later will override the previous value:

[mysqld]
innodb_buffer_pool_size = 1G
innodb_buffer_pool_size = 2G

With this the buffer pool size will be 2G.

From what you described it seems your server is not picking up your my.cnf at all. Check if you have the files at the right place:

[root@server ~]# $(which mysqld) --verbose --help | grep -A 1 "Default options"

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

Or another alternative:

[root@server ~]# timeout 3 sudo -u mysql strace $(which mysqld) 2>&1 | grep stat|grep cnf
stat("/etc/my.cnf", 0x7fff9e681280)     = -1 ENOENT (No such file or directory)
stat("/etc/mysql/my.cnf", {st_mode=S_IFREG|0644, st_size=5454, ...}) = 0
stat("/usr/etc/my.cnf", 0x7fff9e681280) = -1 ENOENT (No such file or directory)
stat("/var/lib/mysql/.my.cnf", 0x7fff9e681280) = -1 ENOENT (No such file or directory)
stat("/var/lib/mysql/.mylogin.cnf", 0x7fff9e681280) = -1 ENOENT (No such file or directory)