Can I get pg_rewind to show me lost data after a Postgres failover?

If you had made a backup of the old master's pg_xlog content that was not applied on the new master after the point of divergence, as they call it, you can use pg_xlogdump (included in the PG distribution since 9.3) to examine the contents of transaction logs on the old master:

$ pg_xlogdump -p <pg_xlog_dir> <segment_file_name>

Unfortunately, WAL only contain the changes to the physical pages in the database cluster, so it is currently impossible to extract full SQL statements, but at least this should give you an idea of whether the number of changes made were significant and whether they hit any relevant relations.

You can use the output's xid field as an indication of how many transactions have been applied to the old master since the split, and the rel field's last number will tell you which relation a change has been applied to (look at pg_class.relfilenode to figure out what exact relation that would be):

SELECT  c.relname AS relname,
        n.nspname AS schema,
        c.relkind AS reltype,
        t.spcname AS tablespace
    FROM pg_class c
        LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
        LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
    WHERE c.relfilenode = '<number>';

In short, it is not going to be an easy task, but it is possible to get at least some information out of it.

Another, much easier way, would be to turn on statement logging, but that will probably cost you quite a bit unless you're storing logs on a separate device so that it doesn't affect server operation too much (log_min_duration_statement = 0).


Does Page verification require downtime?

Nope, no downtime for this change at all.

Here's the explanation of what/when things happen:

  Keep in mind that just changing the setting to CHECKSUM does not
  instantly add CHECKSUMs to your existing data pages in the database.
  In order for this to happen, you have to read each page into memory,
  make some sort of change and then write it back out to the storage
  subsystem. This can happen from normal INSERT/UPDATE/DELETE activity
  over time, or from rebuilding your indexes.

Source: http://www.sqlskills.com/blogs/glenn/setting-your-page-verify-database-option-to-checksum/


Direct values on predicate produce not-so-good plan

What's wrong with direct values being passed as part of predicate?

The Difference Between Variables and Parameters

The optimizer uses the statistics density vector when calculating estimates for variables.

When "direct" or "static" values are embedded in the query directly, the statistics histogram is used instead.  This is why you get different estimates, and thus different plans.

Here's my estimated plan: https://www.brentozar.com/pastetheplan/?id=SJCduTuKN

On my 2010 copy of the SO database, the density of the OwnerUserId column is .000003807058.  Multiplying that by 3,744,192 rows = 14.2544 rows.  Which is exactly the number of rows estimated to come out of IX_Posts_OwnerUserId.

You can get this information about the statistics for that index by running this DBCC command:

DBCC SHOW_STATISTICS('dbo.Posts', 'IX_Posts_OwnerUserId');

Here's the (abbreviated) output:

Name                    Updated             Rows
IX_Posts_OwnerUserId    Apr  8 2019  8:33AM 3744192

All density     Average Length  Columns
3.807058E-06    4               OwnerUserId

Since PostTypeId is also part of the WHERE clause, statistics are automatically generated for that column as well. That density vector comes out to be .25 x 3,744,192 rows = 936,048 rows.

DBCC SHOW_STATISTICS('dbo.Posts', '_WA_Sys_00000010_0519C6AF');

And the output:

Name                        Updated             Rows
_WA_Sys_00000010_0519C6AF   Apr  8 2019  9:04AM 3744192

All density Average Length  Columns
0.25        4               PostTypeId

Since this is an "AND" predicate, the estimate uses the lower of the two.

When you use static values instead of variables, it uses the statistics histogram.   This is in the third resultset of that SHOW_STATISTICS command.  For the key you're using, here is the histogram entry:

RANGE_HI_KEY    RANGE_ROWS  EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS
22656           13040       11371   305                 42.7541

This is where the estimate of 11,371 comes from in the "static values" plan.

The histogram can be a better estimate a lot of the time, as it deals with edge cases a bit better - since there will often be some outliers in a big table like this.

Costing Differences

In this specific case, the histogram produces an estimate that is exactly right.  The cost of the plan produced is (correctly) higher than the one that uses the density vector, because it has to process many more rows.

The "lower cost" plan thinks that 14 rows will be produced by that seek, when in fact 11,371 rows are produced.

Logical Reads

Logical reads are slightly higher in the parallel plan because of nested loops prefetching.  It doesn't seem to make a big difference on my machine - elapsed time for the queries was within 10 ms of each other.

The parallelism doesn't actually help anything, since all of the rows end up on one thread (on my machine anyway).  Adding OPTION (MAXDOP 1) helps with the execution time, but doesn't remove the extra logical reads.

One potential solution to the "extra reads" problem for this query would be to avoid the key lookup entirely by adding PostTypeId as an included column:

CREATE INDEX IX_Posts_OwnerUserId ON dbo.Posts (OwnerUserId)
INCLUDE (PostTypeId)
WITH (DROP_EXISTING = ON);


Should I create a separate table for geo-location points?

General recommendation

I am in favor of this:

  whether I should embed the locations in whatever table I need them

Do not create a separate table for storing geo-location points only. Geo location points doesn't really make sense themselves alone.

I would say keep the geo location points to the tables about events. Events such as pickup, drop-off, meet up, dine, and so on...

Albert Einstein once said it's about space-time. It's not about location only (space); also it's not about time only. It should be the binding of space and time. Consider these:

Where will the user be picked up? and when?
Where will the user be dropped off? and when?

There is always a question about when every time you talk about location. One good direction is to always think of it as an event.

Besides, would there be a part of your application where you would pin all the locations (pickups-and-drop-offs) on a single map and homogeneously as if they are all just locations? I think most likely, that will not be the case.

Instead, what you may want is to pin all the pickups, pin all the drop-offs, etc.

Again, for me, it would be better to store the locations with a mindset about events.

Specifically for your purpose, this would require you to hook your bookings table to pickups and dropoffs event tables. With this, you can have as many pickup locations and dropoff locations. Thus, extendable as what you've said.

Alternative

In favor of this:

  whether I should store the locations as a separate table

Unless though if you use the What 3 Words (W3W) service/application. W3W split the world into 3-meter blocks. Each block then was given a unique ID. The ID is in format word1.word2.word3.

With w3w though, you don't need to store geo-location points on a separate table. They already did that for you. All you have to do is use their IDs. I'm not so sure though if 3-meter resolutions are enough for most applications.

Credentials

By the way, I work on staple crop research. We have lots of GIS applications and we also use PostgreSQL.


Creating temp tables SQL Server 13.0.5426 with dynamic SQL doesn't work

This has never worked.

If you want to insert into a static temp table dynamically, you can do this:

CREATE TABLE #t(id INT);

DECLARE @sql NVARCHAR(MAX) = N'SELECT 1 FROM sys.databases;';

INSERT #t ( id )
EXEC sys.sp_executesql @sql;

SELECT * FROM #t AS t;

If you need to build a temp table dynamically, see this Q&A:

Creating temporary table dynamically using condition


Why do we add NO_TRUNCATE and NORECOVERY options while taking tail log Backup?

According to Tail-Log Backups (SQL Server) - (highlighting mine)

  If the database is online and you plan to perform a restore operation on the database, begin by backing up the tail of the log. To
  avoid an error for an online database, you must use the ... WITH
  NORECOVERY option of the BACKUP Transact-SQL statement.
  If a database is offline and fails to start and you need to restore the database, first back up the tail of the log. Because no
  transactions can occur at this time, using the WITH NORECOVERY is
  optional.

According to SQL Server expert Paul Randal regarding Disaster recovery 101: backing up the tail of the log

  Use the NO_TRUNCATE option, which allows the log backup even if the
  database files aren’t there

In your case, the required database files do not exist which would require you to use NO_TRUNCATE


Should I use SSIS to write programming logic?

To answer your question directly, if you know T-SQL and c# SSIS can do pretty much anything you need. My personal beef with it as a platform however stems from the fact the it tends to deal with data procedurally, instead of as a set. This is important because it's where RDMS's shine, so you're cutting off one of your best resources off at the knees. From my perspective, despite it's vast capabilities, it's more of a platform for people who aren't comfortable programming.

To answer your question less directly, I found myself in a very similar situation 12 months ago. I found it useful to start by asking myself these two questions:

What language am I most comfortable with?
And what environment am I most comfortable in?

After answering these questions, I chose to build my solution using those tools which enabled my skills.

In my case the answer was T-SQL and SSMS. Thus, I elected to remove the dependency on SSIS completely, and went the custom T-SQL/Agent route. In the end, I couldn't be happier with the decision. Maintaining & debugging the solution is a simple task, since I'm so familiar with the language an environment.


In case anyone reaches this, and to tag it as finished, the problem was a corrupted index file. MySQL threw the error a couple of hours after and after a REPAIR TABLE it was clear the data was exactly the same.


OLEDB provider for linked server reported change of schema version error

What build of 2008 r2?  Have you looked at this connect?
https://connect.microsoft.com/SQLServer/feedback/details/672153/the-ole-db-provider-sqlncli10-for-linked-server-x-reported-a-change-in-schema-version-between-compile-time-and-run-time
Have you tried the workaround listed?
Workaround (From above connect):

I solved my problem with DBCC FREEPROCCACHE (ref http://msdn.microsoft.com/pt-br/library/ms174283.aspx)
Execute the first scenario where you delete the cache for the query that is causing the issue.


How to query/monitor index fragmentation?

Since you are using Ola's script - he has provided a script to check Index Fragmentation - see FAQ

In addition, fragmentation on very small tables does not affect performance. Microsoft has a white paper about this:
“Generally, you should not be concerned with fragmentation levels of indexes with less than 1,000 pages. In the tests, indexes containing more than 10,000 pages realized performance gains, with the biggest gains on indexes with significantly more pages (greater than 50,000 pages).”
By default, IndexOptimize does not reorganize or rebuild indexes with less than 1000 pages. If you want to change this setting, you can use the @MinNumberOfPages parameter.


Management tool for managing MongoDB sharded cluster

There is no current software for managing clusters and sharding for MongoDB using a GUI but you can use the likes of mtool's mlaunch from the command line to launch clusters.


SQL Server 2008 Version of rows between... preceding and current row

Here's one method (that works in versions 2012+ unfortunately):

with
  cte as
  ( select
        year(date) as year,
        month(date) as month,
        sum(sum(numbers)) over (order by year(date), month(date))
            as running_total,
        row_number() over (order by year(date), month(date))
            as rn,
    from t
    group by year(date), month(date)
  )
select
    t.year, t.month,
    t.running_total - coalesce(p.runinng_total, 0)
        as total_12_months
from
    cte as t
    left join cte as p
    on t.rn - 12 = p.rn ;

This will work only if there are no months with gaps (without any entry).

Another try, for older versions:

with
  cte as
  ( select
        year(date) as year,
        month(date) as month,
        sum(numbers) as sum_numbers
    from t
    group by year(date), month(date)
  )
select
    t.year, t.month,
    sum(p.sum_numbers) as total_12_months
from
    cte as t
    left join cte as p
    on p.year = t.year - 1 and p.month > t.month
    or p.year = t.year     and p.month <= t.month
group by
    t.year, t.month ;


Long delay before redirected restore "starts"

Between the RESTORE DATABASE ... REDIRECT and the RESTORE DATABASE ... CONTINUE command, you will execute all of the SET TABLESPACE CONTAINER commands.

If your database resides on a filesystem that does not support fast file allocation (for example: AIX JFS2), then these SET TABLESPACE CONTAINER operations can take quite a long time if you have large containers – DB2 is formatting the tablespace containers.

When this is happening you'll see lots of I/O on your system, but no apparent activity on the database restore.


MySQL when using WHERE BETWEEN, query hits all partitions

You have just found one of many reasons why PARTITIONing is mostly useless.

Even with your first SELECT, there is no performance benefit over what you could have with a non-partitioned table with a suitable index. j In particular:

INDEX(target_id, created_at)  -- in this order

The second SELECT shows how PARTITION BY HASH treats a range -- by looking in all partitions.  It has no choice but to do that.  Yeah, yeah, with a lot more smarts, it could notice that that date range should hit only two months.  Even so, non-partitioned with a suitable index would run faster and be simpler.

Other comments about the indexes:

KEY `index_created_at_target_id` (`created_at` desc, `target_id`)
KEY `index_on_created_at` (`created_at`)

has two issues:

INDEX(a,b), INDEX(a) -- you may as well toss the latter since the former can serve its purposes.
Until MySQL 8.0, DESC is ignored in INDEX definitions.  (Since you don't have an ORDER BY that depends on it, there is no harm done.)

If your goal is to understand what partitioning can/cannot do, I recommend my blog .

  even when it then checks all partitions, still cut the query time in half versus having 1 monolithic table with the same indexes

I dispute that.  Did you run with the Query cache off?  Did you run each test twice -- to avoid buffer_pool_ caching differences?  Actually, the key is probably "with the same indexes".  Almost always, when switching between partitioned and non-partitioned, the indexes need to be revised.

When partitioned, put the partition key later in each index where it is needed.  Or leave it out if the partition pruning suffices.
When not partitioned, non-range columns should be early in indexes.
When not partitioned, range columns (eg date range) should be late in indexes.

Why even a point query does not speed up with partitioning.  Notice how either way seems to be about the same amount of work:

Non partitioned:  BTree index that is used is, say, 3 levels deep.  Hop-skip-jump, there is the row you desire.
Partitioned:  First prune to the desired partition ("hop").  The, say, the BTree is only 2 levels deep ("skip-jump").

Ah.  Maybe you can discover a 5th use case for Partitioning.


Treating certain Arabic characters as identical

i did few tests and i guess it is a work around but can get your job done, since SQL it self isn't helping much.

if you notice that the unicodes of these characters are close to each other

select unicode(N'أ')
  = 1571

select unicode(N'ا')
  = 1575

select unicode(N'إ')
  = 1573

so between أ and ا , its from 1571 to 1575
or if you want to make sure you get every thing in between

make sure you include from 1569 to 1575

which are

Select NCHAR(1569) = ء
Select NCHAR(1570) = آ
Select NCHAR(1571) = أ
Select NCHAR(1572) = ؤ
Select NCHAR(1573) = إ
Select NCHAR(1574) = ئ
Select NCHAR(1575) = ا

So to make sure that you include every thing similar in your search you can use regular expressions

SELECT *
FROM TestTable
WHERE ArabicChars like '%[ء-ا]%'

so in this case you get all characters between ء and ا
which include all those between 1569 to 1575

so in this case if your table has

 CREATE TABLE [dbo].[TestTable]  (
    [ArabicChars] [nvarchar](50) COLLATE Arabic_CI_AI NOT NULL,
)
INSERT INTO TestTable values (N'احمد');
INSERT INTO TestTable values (N'أحمد');
INSERT INTO TestTable values (N'إحمد');

the query above will get them all.

but you will notice something funny

if you have your column as a primary key

CREATE TABLE [dbo].[TestTable]  (
    [ArabicChars] [nvarchar](50) COLLATE Arabic_CI_AI NOT NULL,

    CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
    (
       [ArabicChars] ASC
    )
) ON [PRIMARY];

you wont be able to insert these 2 records

INSERT INTO TestTable values (N'أحمد');
INSERT INTO TestTable values (N'إحمد');
INSERT INTO TestTable values (N'ءحمد');

because the ء,أ,إ
are all to SQL are part of hamza which is ء

So if you run the query

SELECT *
FROM TestTable
WHERE ArabicChars like 'ء%'

it will show you

أحمد
إحمد

so to get the long story short

to SQL أ is not = to ا because its 2 different letters
hamza and alefp

but ء = آ = أ = ؤ = إ = ئ

they are all Hamza  ء


I need to store count of ROWS in table to variable in which value of a column called 'procossed' =0, if value of variable >0 will execute next step

Using xp_cmdshell for this kind of tasks is a very bad idea in my opinion. However, assuming that you have a good reason to be doing things this way, this is a possible solution:

CREATE PROCEDURE [dbo].[tablepass12](@TableName nvarchar(20))
AS
BEGIN
    DECLARE @output TABLE (output nvarchar(max));
    DECLARE @SQL VARCHAR(8000);
    declare @sql1 VARCHAR(1000);
    declare @counts numeric(4);
    set @counts=0;
    SET @sql1='SQLCMD -S ICB3110\SQLEXPRESS -d teena -h -1 -E -Q "SET NOCOUNT ON; SELECT count(*) FROM '+@TableName+' where processed=0"';
    INSERT @output
    EXEC MASTER..XP_CMDSHELL @sql1;
    SELECT @counts = CAST([output] AS int) FROM @output WHERE [output] IS NOT NULL;

    IF @COUNTS>0
    BEGIN
        SET @SQL='SQLCMD -S SQLCLP01\SQL2012 -d tempdb -E -Q "SELECT * FROM '+@TableName+'" -o d:\processing\'+@TableName+'.csv';
        EXEC MASTER..XP_CMDSHELL @SQL;
    END
END;


MySQL replication: purge_logs giving error

ANALYSIS
When log-bin is enabled, MySQL will log completed SQL transactions into binary logs.
Binary Logs are maintained by a text file whose name would be the same as the binary log but have file extension of .index.
For example, say you had log-bin=/mysqllogs/mysql-bin and started mysqld.
There will be files placed in /mysqllogs
mysql-bin.000001
mysql-bin.index

The index file will contain the name of all the binary logs.
In this example, it would contain mysql-bin.000001
Over time, binary logs will accumulate and you will have something like this in /mysqllogs
mysql-bin.000001
mysql-bin.000002
mysql-bin.000003
mysql-bin.000004
mysql-bin.index

and mysql-bin.index will contain this
mysql-bin.000001
mysql-bin.000002
mysql-bin.000003
mysql-bin.000004

Based on the variables expire_logs_days (if it is greater than 0) and max_binlog_size, mysqld will do its own rotation and expiring of binary logs.
If you do the following
cd /mysqllogs
rm -f mysql-bin.000001
rm -f mysql-bin.000002

this will disable mysqld's log rotation mechanism. Why ?
The /mysqllogs folder will have
mysql-bin.000003
mysql-bin.000004
mysql-bin.index

but the mysql-bin.index will still contain this
mysql-bin.000001
mysql-bin.000002
mysql-bin.000003
mysql-bin.000004

mysqld actively uses the index file to verify that every file in /mysqlvar listed in mysql-bin.index exists before doing any rotation or expiration.
Therefore, you should never let the OS delete binary logs. Let mysqld do it.
I wrote about this before

May 23, 2016 : Error 1236 - "Could not find first log file name in binary log index file"
Apr 23, 2013 : Is it safe to delete mysql-bin files?

SOLUTION
Shutdown mysqld, edit your .index file so the contents matches the exist binary logs on disk, and start mysqld. I have suggested this before (Mysql binlogs exists, show binary logs is empty)
GIVE IT A TRY !!!


Grant access for any logged in user for common database

I believe you can create a user account in that database, using WITHOUT LOGIN. This will create it where you can grant execute as permissions to the other users for any particular stored procedure in that common database. They don't have to use a password for it so you control it by who can EXECUTE AS with it. Here is a pretty good write-up on how it could be used, near the end he talks on how it can be used outside the context of the database.


Does SSRS report user needs database level permission?

the data source login will need permission, the AD used by the user to run the report will not


Can I configure MySQL Replication as Master - Master - Slave?

M1 <-> M2 -> S is essentially the only configuration.  Pros/cons...

Even old versions of MySQL could be configured that way.  M1 and M2 are configured as slaves to each other; S is configured as a slave to M2 (and knows nothing about M1).  Each server needs a distinct server_id.
By having dual master, you have easier failover than if you had simply M->S.
It is probably safer to do what you plan, namely having different apps talking to different Masters, than to have all apps talk to either Master.  This is because of issues with auto_increment and duplicate keys.
The Slave is handy for backups, heavy reads, etc, but...
If M1 crashes and you failover to M2, use S to reconstruct M1, but...
If M2 crashes and you failover to M1, S cannot easily be connected to M1.  It would probably be best to assume that you lost S at the same time and rebuild both.  (There are issues of binlog positions, etc.  GTIDs, if turned on, may make this "not an issue"; this is something to research.)
Since you seem to have 3 servers, why not go with Galera?  That eliminates most of the issues mentioned -- you can write to any server; loss of any server is automatically repaired; etc.  Group Replication is another smart idea.


Two Databases in Standby/ Read-Only, Can Only Alter to SINGLE_USER One of Them

Microsoft disclosed an issue in 2015 related to SQL Server 2012 and 2014 that sounds very similar to your problem:

  Assume that you set a Microsoft SQL Server 2012 or SQL Server 2014
  database to read-only mode. When you then try to set it to single-user
  mode, you may receive the following error message:

  "Msg 3906, Level 16, State 1, Line 51 Failed to update database "SDP"
  because the database is read-only.

  Msg 5069, Level 16, State 1, Line 51 ALTER DATABASE statement failed."

You can read about the full issue and the fix in FIX: You cannot set a SQL Server 2012 or 2014 database to single-user mode if the database is read-only.

It looks like you're running SQL Server 2014 SP1 with a security update (from 08/2017) applied. The referenced issue would impact that version of SQL Server, and I believe the fix is in 2014 SP1 CU 2

I'm running SQL Server 2017. In that version, I was able to successfully put a database in single-user then read-only (case-1) and read-only then single-user (case-2):

/**CASE-1**/
--Setting single user then read-only
ALTER DATABASE [foo] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [foo] SET READ_ONLY;

--Setting single user then read-only
ALTER DATABASE [foo] SET READ_WRITE;
ALTER DATABASE [foo] SET MULTI_USER WITH ROLLBACK IMMEDIATE;

/**CASE-2**/
--Setting single user then read-only
ALTER DATABASE [foo] SET READ_ONLY;
ALTER DATABASE [foo] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

--Setting single user then read-only
ALTER DATABASE [foo] SET MULTI_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [foo] SET READ_WRITE;

I checked the documentation for SQL Server 2014, and it looks like this functionality works the same as in 2017 (at least there is no noted difference). So, I take back my earlier assertion that you can't place a read-only database in single-user mode.


Does Clustered Index on GUID create more fragmentation than Non Clustered Index?

Since you are indicating insert performance is the primary concern, I'd take the recommendation of the DBA and make the clustering key the identity column since it is a unique, monotonically ascending number, which is guaranteed to (almost) never cause page-splits on the table.

Also, don't store the GUID in an nvarchar(100) column, use the data type designed for them, which is uniqueidentifier.

Look carefully at the reporting requirements prior to adding indexes to this table.  You may find out you don't need an index on the GUID column at all.  If you determine that having an index on the GUID column is required, you may find it desirable to have the leading column of the index not be the GUID, perhaps it will be the Group or TestID column instead?

If you've determined, through a rigorous design process, that the majority of reporting queries against this table will be made using the session id as the primary component of joins or where clauses, you may actually benefit from the table being clustered on the session id.  You'd likely want to set the page fill factor at some point below 100%, perhaps start at 93% and monitor fragmentation levels.  Also, it's worth pointing out that fragmentation may not be such a massive problem if you store this table on SSD.  Will the session id be propagated throughout the other related tables?  If so, you'll likely need non-clustered indexes on the column in those other tables as well, compounding the fragmentation issue.  It may be worthwhile maintaining a single table where you insert the GUIDs to obtain a surrogate integer key to use in the other tables, something like:

IF OBJECT_ID('dbo.SessionSurrogate') IS NOT NULL
DROP TABLE dbo.SessionSurrogate;
CREATE TABLE dbo.SessionSurrogate
(
    SessionID int NOT NULL
        CONSTRAINT PK_SessionSurrogate
        PRIMARY KEY NONCLUSTERED
        IDENTITY(1,1)
    , SessionGUID uniqueidentifier NOT NULL
);
CREATE UNIQUE INDEX CI_SessionSurrogate --could be non-unique if required
ON dbo.SessionSurrogate(SessionGUID);

IF OBJECT_ID('dbo.UserTestGroups') IS NOT NULL
DROP TABLE dbo.UserTestGroups;
CREATE TABLE dbo.UserTestGroups
(
    UserTestGroupsID int NOT NULL
        CONSTRAINT PK_UserTestGroups
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , SessionID int NOT NULL
    , TestID bigint NOT NULL
    , [Group] tinyint NOT NULL
    , InsertDate datetime NOT NULL
);

GO
CREATE PROCEDURE dbo.InsertSession
(
    @SessionGUID uniqueidentifier
    , @TestID bigint
    , @Group tinyint
)
AS
BEGIN
    INSERT INTO dbo.SessionSurrogate (SessionGUID)
    OUTPUT INSERTED.SessionID, @TestID, @Group, GETDATE()
    INTO dbo.UserTestGroups (SessionID, TestID, [Group], InsertDate)
    OUTPUT INSERTED.SessionID
    VALUES (@SessionGUID);
END;
GO

You'd then insert data like:

DECLARE @SessGUID uniqueidentifier = NEWID();
DECLARE @TestID bigint = 42;
DECLARE @Group tinyint = 6;
EXEC dbo.InsertSession @SessGUID, @TestID, @Group;

The benefit here is you're only causing fragmentation on a single table with a very narrow row width (20 bytes), offering somewhere around 400 rows per page.  The stored procedure above is very compact and outputs the generated int for you to use later, if necessary.


MySQL metadata function to get projected column type in query?

Within MySQL, you can get this information by creating a temporary table, then using DESCRIBE on that temporary table:

CREATE TEMPORARY TABLE `temp`
SELECT ...
FROM ...
LIMIT 0;

DESCRIBE `temp`;

We cannot just use DESCRIBE on the original table because the column we want to know the type of is a calculated column, not something being directly pulled from a table.  Likewise, we cannot use DESCRIBE directly on the query, because DESCRIBE can only be used on tables.  Creating a temporary table solves both of those problems.


First impression I have, if you're needing your laptop for testing code you're running against a full version of SQL 2008, you'd want your laptop running a full version of SQL 2008.

You hit the nail on the head with setting the services to start manually. You could then have a simple .bat file to start and stop the SQL services when needed. This is exactly what I do on my home PC when needing to play with some databases.


InnoDB multicolumn fulltext search: SELECT MATCH requires single column index?

Alas that is one of a few differences between the two separate implementations of FULLTEXT.

Alas. the workaround is to add two more indexes:

FULLTEXT(text_1)
FULLTEXT(text_2)

But... Don't count on relevance numbers coming back from different indexes as being comparable.


Matching a column from a table to another table

Try this one:

SELECT  PRIV.id_extern_for_job
,       PRIV.id
,       PUB.id_extern_for_job
FROM    SOL_PUBLIC.SOL_SUBSOL_CHESTIONARE@RACDB     PUB
,       SOL.SOL_SUBSOL_CHESTIONARE                  PRIV
WHERE   PRIV.id_extern_for_job = PUB.id
AND     PRIV.id <> PUB.id_extern_for_job;


Header is added when exporting column of type image with BCP to files

The solution seem to be to first execute BCP manually, set prefix to 0 in the user dialog. And then use the generated .fmt-file as in this command:

EXEC master..xp_cmdshell 'BCP "SELECT my_column FROM my_table WHERE my_id = 11 "
queryout "C:\bcpdir\bcpout.xml" -T -f "C:\bcpdir\bcp.fmt'


You can't use CROSS JOIN if any of both sets has no rows for the output you want. You can simulate this behaviour with a FULL JOIN with an always true condition.

SELECT
    *
FROM
    T1
    FULL JOIN T2 ON true


Redundant to have index + full text on same column?

Not redundant, but rarely practical.

An INDEX has limitations, based on 255 characters.  (There are too many wrinkles for me to go into detail.)

FULLTEXT is often used for big blocks of text.

Would you be looking at 'words' and doing = comparisons on the whole column?  Seems strange.

Here's one trick for when I want to search for a phrase that messes up in FULLTEXT.  Let's say I am looking for the title of your question, but I remember only 'index + full' (including the plus sign):

WHERE MATCH(col) AGAINST('+index +full')
  AND col LIKE '%index + full%'

The MATCH will quickly find only a few rows, but too many.  Then the LIKE finish filtering.  No plain INDEX is involved.


How to set isolation to SERIALIZABLE DEFERRABLE for a whole Postgresql database?

You can set SERIALIZABLE as the default isolation level in postgresql.conf:

default_transaction_isolation = 'serializable'

This may confuse clients that assume READ COMMITTED isolation, so IMO it's better to set it explicitly in your sessions.

As for the rest, per the manual:

  The DEFERRABLE transaction property has no effect unless the transaction is also SERIALIZABLE and READ ONLY

... and given the properties of such transactions, I find it hard to imagine you wanting to make it the default. Are you perhaps confusing it with SET CONSTRAINTS DEFERRED?

You can make transactions read-only and deferrable by default with:

transaction_read_only = on
transaction_deferrable = on

in postgresql.conf but it's really a rather bad idea. Instead, just use an appropriate BEGIN, or SET TRANSACTION in your session before your first "real" SQL statement.


How to count three consecutive records with same values in a column based on the value of another column in PostgreSQL?

Another solution. Recursive part creates groups of rows of status "absent".
The solution uses recursive CTEs and window functions.

WITH RECURSIVE a(id, name, date, n)  as (
    SELECT id, name, q.date, 1 as n
    FROM (
        SELECT id, name, date,
            date-lag(date) OVER (PARTITION BY name ORDER BY date) as lag
        FROM  attendance
        WHERE status='absent'
    ) q
    WHERE lag >1 or lag is null
    UNION
    SELECT a.id, a.name, a.date, a.n + 1
    FROM a
    JOIN attendance at ON (
           a.id = at.id and at.name = a.name and at.date = a.date + n)
    WHERE at.status='absent'
)
SELECT id, name, sum(long_absences) FROM (
    SELECT id, name, count(*)/3 as long_absences
    FROM a
    GROUP BY id, name, date having count(*) >=3
    ) as absences
GROUP BY id, name;


Is it possible to create a MySQL slave from a MariaDB master?

I think it can work, see e.g. this question (with answers). Note that:

You won't be able to use GTID replication as this is implemented differently in MariaDB and MySQL
You need to use versions of MySQL and MariaDB which are somewhat compatible.
Avoid using any MariaDB-specific features.


RHEL 7.4, heavy workload(s) and SWAP usage

If you have 8GB swap, 256 GB physical memory and swappiness=1, with memory usage nearing 72Gb, it's acceptable to swap operations almost 700MB (72GB * 1%), then the 100MB swap usage that you had seen is pretty normal.

Be sure that your 1TB daily data load doesn't exhaust your physical memory and you can change swappiness to 0 without side effects.

But do you have a quite low swap rate in your setup. Do you really need to avoid swap?


It sounds like you've downloaded a copy of the full SQL Server 2016 (with SP1) installer. (Microsoft often distributes the installer with the latest service pack built in.)

Instead, download Service Pack 1 by itself, then apply the latest cumulative update.


Importing external data with different field format

It seems that you need to convert numbers in excel table to characters first, and adjust the data (spaces). Export to CSV, and then import into database. I suppose you have varchar datatype for Item and Reference columns in the table.


Are there any good and free tools for managing a PostgreSQL database?

pgAdmin and psql, those are the tools I use. A PHP-script isn't very usefull for database management, too slow and many problems with transactions: What to do when something goes wrong? Commit or rollback? This makes tools like phpmyadmin and phpPgAdmin pretty useless, when the script is done, the database connection will be closed. There is nothing to commit or rollback, your SQL script is already gone.

Learn SQL and any tool that can keep it's databases connections open, will do the job.


Can't create a table with the same name I deleted before

you can do something like this, it will create and drop a table called t1, schema dbo

use tempdb
go

--------------------------------------------------------------------------------
-- creates the table
--------------------------------------------------------------------------------

if exists (
              select *
              from sys.tables t
              inner join sys.schemas s
                      on t.schema_id = s.schema_id
              where t.name = 't1'
                and s.name ='dbo'
          )
          begin

              drop table dbo.t1
               print 'table dropped'
          end
else
          begin

               create table dbo.t1 (i int not null primary key clustered)
               print 'table created'
          end
go

--------------------------------------------------------------------------------
-- drops the table
--------------------------------------------------------------------------------

if exists (
              select *
              from sys.tables t
              inner join sys.schemas s
                      on t.schema_id = s.schema_id
              where t.name = 't1'
                and s.name ='dbo'
          )
          begin

              drop table dbo.t1
               print 'table dropped'
          end
else
          begin

               create table dbo.t1 (i int not null primary key clustered)
               print 'table created'
          end
go


How efficiently does iSeries DB2 use index with WHERE ?

Using System i Navigator, you can run your queries in there to see if there are more indexes that may be recommended. The system tries it best to create the right indexes before running and caches those access paths.

One thing I did in my last project with some larger queries was to create procedures. Then the system stores those access paths and speeds up your queries even more.


Re-initializing a transactional replication wihch was initially synced from backup

So I had a similar situation.  I had a HUGE publication that took up to 36 hours to initialize with a snapshot.  When I had to move the subscriber to new hardware this just wasn't ideal.  So I used a backup to initialize the sub.  Of course I knew that we'd have to add articles eventually and I did what this article said to do in Option 1 (option 2 not available as there was no way I was going to be able to stop all activity on the subscriber short of a disaster).

Add Articles to Subscription initialized with backup

So I added a new publication to the same publisher database and then added the subscription to the subscriber.  Since the initial snapshot is very small (only 4 tables) it sych'd up very quickly.  Subsequent table adds to the publication are done on the new publication and snapshots generally are only of new tables, not entire publication.  Is working very well so far .


Since it looks like you only want a data point every hour, one approach could be to use the durationSeconds column to figure out how many subsequent hour boundaries the data point should cover.  The following query performs this logic and yields your desired results on the test data.

-- Create demo data
CREATE TABLE #status (
    DateTime DATETIME NOT NULL,
    Status VARCHAR(20) NOT NULL,
    DurationSeconds INT NOT NULL
)
GO
INSERT INTO #status (DateTime, Status, DurationSeconds)
VALUES ('2015-07-08 12:00:00', 'Available', 10800),
        ('2015-07-08 15:00:00', 'Unavailable', 460)
GO

-- Extrapolate the gaps!
-- Tally table taken from http://www.sqlservercentral.com/blogs/never_say_never/2010/03/19/tally_2D00_table_2D00_cte/
; WITH t1 AS (SELECT 1 N UNION ALL SELECT 1 N),
        t2 AS (SELECT 1 N FROM t1 x, t1 y),
        t3 AS (SELECT 1 N FROM t2 x, t2 y),
        t4 AS (SELECT 1 N FROM t3 x, t3 y),
        TallyHours AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N FROM t4 x, t4 y)
SELECT sExtrapolated.DateTime, sExtrapolated.Status
FROM #status s
CROSS APPLY (
    -- The original status
    SELECT s.DateTime, s.Status
    UNION ALL
    -- Any extrapolated gaps; if the DurationSeconds pushes past the next hour boundary,
    -- we infer that we should fill in the gap.  If you need slightly different business logic,
    -- you can adjust this part accordingly
    SELECT DATEADD(hh, h.N, s.DateTime), s.Status
    FROM TallyHours h
    WHERE h.N < CEILING(s.DurationSeconds / 3600.)
) sExtrapolated
GO


Error with SQL stored procedure output type

This row causes your error:

SET @aux='SELECT '+@nmae

Since @nmae is DECIMAL(20,5) there is no way to make a sum without converting, and thanks to type precedence, it's string SELECT that should be converted to numeric, and this is impossible.

Here is quick repro:

declare @nmae DECIMAL(20,5);

DECLARE @aux VARCHAR(MAX)='Hello'

SET @aux='SELECT '+@nmae;

exec(@aux);

To see what is converted to what you can use this query:

select '2' + 2

this will not produce '22', but 4, as it's tring '2' to be converted to numeric, not viceversa.

UPDATE

You can use synonym to get rid of all your dynamic code like this:

declare @forecasttype VARCHAR(MAX) = 'DAH'
declare @sql varchar(8000) = 'create synonym dbo.MySyn for ['+@forecasttype+'_Errors]';
exec(@sql); -- this will create MySyn that points to your table

Now you can use the created synonym instead of your table:

SELECT @nmae=AVG(ABS([MySyn].[Forecast]-[MySyn].[Metering])/CAST([MySyn].[Capacity] AS DECIMAL(20,5))) from dbo.[MySyn]

And it's even better create an alias to not rewrite table name every time in the select:

SELECT @nmae=AVG(ABS(s.[Forecast]-s.[Metering])/CAST(s.[Capacity] AS DECIMAL(20,5))) from [MySyn] s

At the end of sp just drop the synonym.

You should be sure that this sp is not executed at the same time by more than 1 user, if it's so you need other solution


How do I know if a AWS Server instance has license SQL Server Standard or SQL Server Web?

Run SELECT @@VERSION; in a query window, with results to text. You will see something like this (minor details will vary) in the Messages pane:

Microsoft SQL Server 2014 - 12.0.2402.0 (X64)
    Aug 13 2014 11:36:34
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.4 <X64> (Build 9841: )
----^^^^^^^^^ edition will be here

Help > About in Management Studio is just about the client tools, so may not even reflect the same version as the server, never mind that Management Studio no longer has any edition differentiations.


Howto use typeperf to store performance monitor values in a custom schema?

It seems like the schema used will be the default schema of the database user associated with the login you provide to the DSN.

How you set this up in detail depends on factors like whether SQL Authentication is allowed to this SQL Server instance. You can set the default schema with ALTER USER.

Regarding the edit (almost becoming a new question now), from Database-Level Roles:

  Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.

You could try granting more granular permissions (though having alter on the schema may already be sufficient). It might also be that the implementation hard-codes use of the dbo schema.

Another thing to check: make sure the login is not a member of the sysadmin fixed server role. The value of DEFAULT_SCHEMA is ignored if the user is a member of the sysadmin fixed server role. All members of the sysadmin fixed server role have a default schema of dbo. Grant CONTROL SERVER to the login instead; this gives exactly the same rights, without the schema side-effect.


That's because from version 10 it has been renamed to pg_resetwal. And the binary is located in /usr/lib/postgresql/<version>/bin.
The pg_xlog directory was renamed to pg_wal in version 10 and several functions, tools and options as well, to reflect that change. See Postgres 10 release notes:

Rename write-ahead log directory pg_xlog to pg_wal, and rename transaction status directory pg_clog to pg_xact (Michael Paquier)
Users have occasionally thought that these directories contained only inessential log files, and proceeded to remove write-ahead log files or transaction status files manually, causing irrecoverable data loss. These name changes are intended to discourage such errors in future.

Rename SQL functions, tools, and options that reference xlog to wal (Robert Haas)
For example, pg_switch_xlog() becomes pg_switch_wal(), pg_receivexlog becomes pg_receivewal, and --xlogdir becomes --waldir. This is for consistency with the change of the pg_xlog directory name; in general, the xlog terminology is no longer used in any user-facing places.


SQL Server does not exist or access denied from a VMWare guest

Be sure that the SQL Server IP address is listed in the IP Addresses page in TCP/IP protocol properties ( You can access it from SQL Server Configuration Manager )

Also be sure to restart SQL Server service after you do changes.


Does MySQL 8 ASCII vs utf8mb4_0900_ai_ci size differ when only using ASCII characters?

Unless MySQL does something strange, using only ASCII characters (i.e. only values 0 - 127) should be the exact same encoding, and hence the exact same size, between ASCII, UTF-8, and many other 8-bit code pages. It's only when you hit code points above 127 (or 0x7F) that UTF-8 starts to require additional space (though technically speaking, standard ASCII only includes values 0 - 127, thus there are no code points above 127, thus all ASCII code points are encoded identically in UTF-8, which after all, was the design goal of UTF-8: full ASCII compatibility).


What is the fastest way to fetch the last row from a table?

You'll need an index on product_id regardless of solution.

Provided you have an index on the updated_at column, and all you need is to fetch "a specific product" as you stated, then I would do:

select *
from Prices
where product_id = ?
order by updated_at desc
limit 1

But if I did not get the results I wanted or if I needed to get the current price for many products, then I would try the option of adding a active column, and setting it to N for all prices other than the new one when doing updates of the prices and then I would create a partial index where active as suggested by a_horse_with_no_name. I would go there only if I needed to as it adds a layer of complexity of updating previous price rows to not be active, etc.


Is fragmentation at non-leaf levels a problem?

No. Non-leaf index pages are particularly likely to stay in cache. In principle, it might slow down read-ahead a little (upper levels are used to drive this) the first time an index scan with read-ahead occurs, but meh. Some people regard fragmentation at any level a normal thing and not something to worry about if the data is usually already in memory or if the storage subsystem has low latency and good throughput.
Because SQL Server doesn't go out of its way to avoid it. Non-leaf pages can experience page splits too. Padding the index above the leaf (PAD_INDEX) is possible but rarely worthwhile.
The clustered index key(s) and any uniquifier.
This isn't implemented.
I believe it is a count of the number of contiguous areas at that level of the index, yes.


Add data to second table on UPDATE (PostgreSQL only)

get rid of FOR EACH ROW: If N rows get updated, it would be nice if only one INSERT gets executed

How would that work, you wouldn't have access to OLD or NEW, because it'd be a statement-trigger.

  Don't use plpythonu

Python natively doesn't support running as a trusted language. You can easily use plpgsql.

Code for a plpgsql function,

CREATE OR REPLACE FUNCTION add_ticket_to_transfere_table()
RETURNS TRIGGER
AS $$
    BEGIN
      INSERT INTO transfere_table (ticket_id) VALUES (new.id);
      RETURN new;
    END;
$$
LANGUAGE plpgsql;

This isn't how I would personally run this though. What I would do is

Create a boolean on the row for triaged or whatever the transfere_table represents.
When you insert, have that Boolean default to true. Effectively a column-trigger if no data is provided. When you update the ticket, have that boolean set in the update statement.

If you don't want to touch that the table itself, I'd run your process in reverse.

Create the table transfere_done

CREATE TABLE transfere_done (
  ticket_id int PRIMARY KEY REFERENCES ticket.ticket_id
)

Copy over all the ids to it.
Create the below view,

CREATE VIEW transfere_queue
AS
  SELECT * FROM ticket
  WHERE NOT EXISTS (
    SELECT 1
    FROM transfere_done
    WHERE transfere_done.id = ticket.id
  );

Now, when you insert into ticket, it automagically shows up under transfere_queue until you insert into transfere_done. You may or may not want to MATERIALIZE the view. Either way, I really hate triggers when not needed and I try to engineer around them.


SELECT `id_`,
       MAX(CASE WHEN `value1` = 'b' THEN 1 ELSE 0 END) as `is_b`
  FROM `t1`
 GROUP BY `id_`
 ORDER BY `id_`;

If you want that to go into result, slap an INSERT on top of it.


Cartesian product of two tables not working as expected in MySQL but fine with SQlitee?

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.15 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>  use test
Database changed
mysql> CREATE TABLE emp
    ->  (
    ->      id int primary key,
    ->      name varchar(20),
    ->      did int
    ->     );
Query OK, 0 rows affected (0.45 sec)

mysql>
mysql>
mysql>
mysql> CREATE TABLE dep
    ->  (
    ->      id int primary key,
    ->      name varchar(20)
    ->     );
Query OK, 0 rows affected (0.35 sec)

mysql>
mysql> INSERT INTO dep
    -> VALUES
    -> (1,'lakers'),
    -> (2,'spurs'),
    -> (3,'sixers'),
    -> (4,'pacers'),
    -> (5,'warriors')
    ->
    -> ;
Query OK, 5 rows affected (0.06 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql>
mysql> INSERT INTO emp
    -> VALUES
    -> (1,'rohit',1),
    -> (2,'amit',2),
    -> (3,'haris',3),
    -> (4,'eti',4)
    -> ;
Query OK, 4 rows affected (0.08 sec)
Records: 4  Duplicates: 0  Warnings: 0

I ran your query without aliases

mysql> select * from dep,emp;
+----+----------+----+-------+------+
| id | name     | id | name  | did  |
+----+----------+----+-------+------+
|  1 | lakers   |  1 | rohit |    1 |
|  1 | lakers   |  2 | amit  |    2 |
|  1 | lakers   |  3 | haris |    3 |
|  1 | lakers   |  4 | eti   |    4 |
|  2 | spurs    |  1 | rohit |    1 |
|  2 | spurs    |  2 | amit  |    2 |
|  2 | spurs    |  3 | haris |    3 |
|  2 | spurs    |  4 | eti   |    4 |
|  3 | sixers   |  1 | rohit |    1 |
|  3 | sixers   |  2 | amit  |    2 |
|  3 | sixers   |  3 | haris |    3 |
|  3 | sixers   |  4 | eti   |    4 |
|  4 | pacers   |  1 | rohit |    1 |
|  4 | pacers   |  2 | amit  |    2 |
|  4 | pacers   |  3 | haris |    3 |
|  4 | pacers   |  4 | eti   |    4 |
|  5 | warriors |  1 | rohit |    1 |
|  5 | warriors |  2 | amit  |    2 |
|  5 | warriors |  3 | haris |    3 |
|  5 | warriors |  4 | eti   |    4 |
+----+----------+----+-------+------+
20 rows in set (0.03 sec)

I ran your query as you gave it, with aliases

mysql> select * from dep a,emp b;
+----+----------+----+-------+------+
| id | name     | id | name  | did  |
+----+----------+----+-------+------+
|  1 | lakers   |  1 | rohit |    1 |
|  1 | lakers   |  2 | amit  |    2 |
|  1 | lakers   |  3 | haris |    3 |
|  1 | lakers   |  4 | eti   |    4 |
|  2 | spurs    |  1 | rohit |    1 |
|  2 | spurs    |  2 | amit  |    2 |
|  2 | spurs    |  3 | haris |    3 |
|  2 | spurs    |  4 | eti   |    4 |
|  3 | sixers   |  1 | rohit |    1 |
|  3 | sixers   |  2 | amit  |    2 |
|  3 | sixers   |  3 | haris |    3 |
|  3 | sixers   |  4 | eti   |    4 |
|  4 | pacers   |  1 | rohit |    1 |
|  4 | pacers   |  2 | amit  |    2 |
|  4 | pacers   |  3 | haris |    3 |
|  4 | pacers   |  4 | eti   |    4 |
|  5 | warriors |  1 | rohit |    1 |
|  5 | warriors |  2 | amit  |    2 |
|  5 | warriors |  3 | haris |    3 |
|  5 | warriors |  4 | eti   |    4 |
+----+----------+----+-------+------+
20 rows in set (0.00 sec)

mysql>

I ran your query with aliases in the SELECT clause as well as the FROM clause

mysql> select a.*,b.* from dep a,emp b;
+----+----------+----+-------+------+
| id | name     | id | name  | did  |
+----+----------+----+-------+------+
|  1 | lakers   |  1 | rohit |    1 |
|  1 | lakers   |  2 | amit  |    2 |
|  1 | lakers   |  3 | haris |    3 |
|  1 | lakers   |  4 | eti   |    4 |
|  2 | spurs    |  1 | rohit |    1 |
|  2 | spurs    |  2 | amit  |    2 |
|  2 | spurs    |  3 | haris |    3 |
|  2 | spurs    |  4 | eti   |    4 |
|  3 | sixers   |  1 | rohit |    1 |
|  3 | sixers   |  2 | amit  |    2 |
|  3 | sixers   |  3 | haris |    3 |
|  3 | sixers   |  4 | eti   |    4 |
|  4 | pacers   |  1 | rohit |    1 |
|  4 | pacers   |  2 | amit  |    2 |
|  4 | pacers   |  3 | haris |    3 |
|  4 | pacers   |  4 | eti   |    4 |
|  5 | warriors |  1 | rohit |    1 |
|  5 | warriors |  2 | amit  |    2 |
|  5 | warriors |  3 | haris |    3 |
|  5 | warriors |  4 | eti   |    4 |
+----+----------+----+-------+------+
20 rows in set (0.00 sec)

mysql>

I just ran these in MySQL 5.6.15 on my Windows 8 Laptop. It works fine with and without aliases. It might be SQL Fiddle that has the problem in this instance with a MySQL Cartesian Product.


One way to do the query would be:

with norm (device_id, element) as (
    select device_id, unnest(deals_sent) from modelA
)
select device_id
from norm·
group by device_id
order by count(element = 1)
limit 2;

The same query cannot be produced by Django ORM. You can only use raw. However there are some options to still keep the benefits of the ORM.

1) Using a view

You can create a VIEW and an unmanaged model in your django app pointing to that view.

CREATE VIEW modelA_norm AS (
   select device_id, unnest(deals_sent) as element from modelA
);

Then in Django:

class ModelA_norm(models.Model):
    device_id = models.IntegerField()
    element = models.IntegerField()

     class Meta:
         managed = False

The query would look like this:

ModelA_norm.objects.extra(
    select={'counter': 'COUNT(element=%s)'},
    select_params=(1, )
).order_by('counter')[:2]

2) Using subselect in where

ModelA.objects.extra(
    where=[
        'device_id IN ( ... here comes the select to get the device ids ...)'
    ]
)

Be aware in this case you will get the results back in arbitrary order not necessary the order of ids produced by the subselect.

3) Run the select directly

You can get the ids to a list from the database and use them to query from your model.

from django.db import connection
_c = connection.cursor()
_c.execute('... the query above with placeholders ...', [1])
_ids = [_d[0] for _d in _c.fetchall()]
objects = modalA.objects.in_bulk(_ids)
sorted_objects = [objects[id] for id in _ids]

I tend to use both 1) and 3) depending on the actual situation.

1) for situation where I can either inherit the model from the original or I don't actually need the original model I can just have a new one.

3) when I need the original model. I tend to put the queries into files in SQL directory in the app (just like templates). So I have a clear indication that I'm doing something database dependent and I can easily switch/rewrite them later if necessary.

I hope this helps.


hstore type does not exist with hstore installed postgresql

For anyone that runs into this question. I ran into this problem today and found a solution.

Depending on where you have the extension installed, it may not be in your search_path.
In my case, i had it installed into the schema 'shared_extensions' on the same db.

using SHOW search_path displayed $user, public, which means that it was never looking in shared_extensions for the extension and it's operator

To fix it run
SET search_path TO "$user", public,
followed by the schema where the extension is installed.

Hope that helps!


First let me say storing dates in a character column is a horrible horrible idea.  Sorts won't work correctly, any form of date calculations are going to require actually casting back to a date anyway, and best of all you probably have bad data in there.

But assuming your table isn't overly large (performance on this is going to kill you because of the cast) and you don't have any bad data.  This will work

CREATE TABLE #temp (chardate varchar(11))
INSERT INTO #temp values ('19-Aug-2016'),('19-Mar-2010'),('10-Feb-1927'),
        ('10-Dec-2013'),('19-Mar-2000'),('10-Jan-1958')

SELECT CASE WHEN datediff(year,CAST(chardate as date),getdate()) < 13 THEN '<13'
            WHEN datediff(year,CAST(chardate as date),getdate()) between 13 and 18 THEN '13-18'
            WHEN datediff(year,CAST(chardate as date),getdate()) between 19 and 35 THEN '19-35'
            WHEN datediff(year,CAST(chardate as date),getdate()) between 36 and 55 THEN '36-55'
            WHEN datediff(year,CAST(chardate as date),getdate()) > 55 THEN '>55' END AS [Range],
    COUNT(1)
FROM #temp
GROUP BY CASE WHEN datediff(year,CAST(chardate as date),getdate()) < 13 THEN '<13'
            WHEN datediff(year,CAST(chardate as date),getdate()) between 13 and 18 THEN '13-18'
            WHEN datediff(year,CAST(chardate as date),getdate()) between 19 and 35 THEN '19-35'
            WHEN datediff(year,CAST(chardate as date),getdate()) between 36 and 55 THEN '36-55'
            WHEN datediff(year,CAST(chardate as date),getdate()) > 55 THEN '>55' END

V2.0 You'll probably get a bit better performance with this:

WITH FixDate AS (SELECT CAST(chardate as date) AS realdate
                FROM #temp),
        MyRange AS (SELECT CASE WHEN datediff(year,RealDate,getdate()) < 13 THEN '<13'
                WHEN datediff(year,RealDate,getdate()) between 13 and 18 THEN '13-18'
                WHEN datediff(year,RealDate,getdate()) between 19 and 35 THEN '19-35'
                WHEN datediff(year,RealDate,getdate()) between 36 and 55 THEN '36-55'
                WHEN datediff(year,RealDate,getdate()) > 55 THEN '>55' END AS [Range]
                FROM FixDate)
SELECT [Range], count(1)
FROM MyRange
GROUP BY [Range]


My own hint with the Write Set Cache Size (aka Galera Cache Size) nailed it (I should have asked earlier, it seems some supernatural voice told me the answer.)

However I do not quite understand why 15 GBytes were too few for 45 KBytes/sec for an 18 hours transfer after a next try, obviously it was only a bit too short... (In my question I wrote about 10 hours, which I found out was not true).

Changing the transfer method to rsync finally fixed it as the transfer took only 1 hour.


SQL Server suspended queries - tempDb pagelatch_up 2:1:2

Is it an issue with table-valued parameters, and this is the reason for pagelatch_up as this is a select statement in SP?
Table-valued parameters create an activity on the tempdb database
for this reason, your stored procedure might be caused to tempdb allocation contention.
The main symptoms of this issue is to see the following wait types in the tempdb database;

PAGELATCH_UP
PAGELATCH_SH
PAGELATCH_EX

In general, you can solve this problem to create multiple tempdb data files because of the
your SQL Server version you might be used to
-T1117 and -T1118 flags.
Any other replacement for Table Value Parameters?
On the other hand, using the Memory-Optimized Table Variables can solve this type of problems
using the Memory-Optimized Table Types and Table Variables
Would you suggest increasing the number of tempDb files from 8 to 12, 16, ...32?
This is the best practice:
The number of the TempDB data files should match the number of logical processors, up to eight files, on the machine where the SQL Server instance is installed.
However, this value can change according to your requirements.


Bulk Insert into view and find missing values

For the purpose of this answer, I am assuming your INSERT statement looks something like this:

INSERT INTO
  dbo.vSubCategories (CategoryName, SubCategory)
SELECT
  CategoryName,
  SubCategory
FROM
  ...  /* reading rows either from the CSV directly or
          from a staging table into which the CSV has been imported */
;

So, the first thing your trigger needs to do is to insert the (new) categories into tbCategories:

INSERT INTO
  dbo.tbCategories (CategoryName)
SELECT
  CategoryName
FROM
  inserted
EXCEPT  -- do not insert already existing category names
SELECT
  CategoryName
FROM
  dbo.tbCategories
;

Now all the category names in the imported dataset can be mapped to their IDs using the tbCategories table. And that is what the trigger should do when inserting the subcategories:

INSERT INTO
  dbo.tbSubCategories (CategoryID, SubCategory)
SELECT
  c.CategoryID,
  n.SubCategory
FROM
  inserted AS n
  INNER JOIN dbo.tbCategories AS c ON n.CategoryName = c.CategoryName
EXCEPT  -- exclude existing sub-categories, just in case
SELECT
  CategoryID,
  SubCategory
FROM
  dbo.tbSubCategories
;

For reference, here is a complete trigger script:

CREATE TRIGGER dbo.vSubCategories_InsteadOfInsert
ON dbo.vSubCategories
INSTEAD OF INSERT
AS
BEGIN
  -- insert categories
  INSERT INTO
    dbo.tbCategories (CategoryName)
  SELECT
    CategoryName
  FROM
    inserted
  EXCEPT  -- do not insert already existing category names
  SELECT
    CategoryName
  FROM
    dbo.tbCategories
  ;
  -- insert sub-categories
  INSERT INTO
    dbo.tbSubCategories (CategoryID, SubCategory)
   SELECT
    c.CategoryID,
    n.SubCategory
  FROM
    inserted AS n
    INNER JOIN dbo.tbCategories AS c ON n.CategoryName = c.CategoryName
  EXCEPT  -- exclude existing sub-categories, just in case
  SELECT
    CategoryID,
    SubCategory
  FROM
    dbo.tbSubCategories
  ;
END;


I think you have anonymous users

Try running this:

SELECT user,host,password FROM mysql.user WHERE user='';

This will show what anonymous users exist. Most likely, you will see a line with a blank user, host %, and a blank password as shown below:

mysql> select user,host,password from mysql.user;
+-----------+-------------+-------------------------------------------+
| user      | host        | password                                  |
+-----------+-------------+-------------------------------------------+
| lwdba     | 127.0.0.1   | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| lwdba     | localhost   | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| lwdba     | %           | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| root      | localhost   | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| root      | 127.0.0.1   | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| vanilla   | localhost   |                                           |
| mywife    | %           |                                           |
|           | %           |                                           | <<<--- LOOK !!!
| replicant | 10.64.113.% | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| kumar     | %           |                                           |
+-----------+-------------+-------------------------------------------+

So, how did you login? Run this query:

SELECT USER(),CURRENT_USER();

What does this tell you?

USER() reports how you attempted to authenticate in MySQL
CURRENT_USER() reports how you were allowed to authenticate in MySQL

The second function CURRENT_USER() reveals how which anonymous user was used to log in.

What privileges did you have when you logged in?

Please run

SHOW GRANTS;

This will unveil what privileges you had at the time you logged in. The fact that you were blocked from creating a database shows you were not root but some lower-privileged user.

Please clean up your user grants.

Feb 17, 2012 : MySQL : Why are there "test" entries in mysql.db?
Feb 17, 2012 : What is the mysql.db table used for?
Jan 18, 2012 : MySQL error: Access denied for user 'a'@'localhost' (using password: YES)

As for resetting the root password, please do the following:

echo "SET PASSWORD FOR root@localhost=PASSWORD('password');" > /var/lib/mysql/rootpwd.sql
service mysql restart
rm -f /var/lib/mysql/rootpwd.sql

I learned this efficient method from @ShlomiNoach.

Give it a Try !!!


Optimising a MySQL script with 1.7 billion records

Several thoughts and requests.  (Not yet a complete answer.)

Please provide SHOW CREATE TABLE, at least for daily_stats_zone_unique_device_uuids_per_hour.  How many partitions in that table.

I assume it is practical and possible to migrate a few rows at a time, as long as we don't mess up the ids, either the old ids or the new ids.

We should walk through the main table, 1000 rows at a time, probably by the PRIMARY KEY. (I may choose to modify this advice after seeing the CREATE TABLE, especially the partition definition.)

It would involve finding all the ids and changing them for the batch.  Possibly add a "sleep" between iterations.

Do the batch as a single transaction. -- The time it takes for the transaction to run becomes the length of the "downtime".  Adjust the "1000" and the "sleep" time to avoid interfering too much with the rest of the system.

1.7 B rows in that table?  What about the other tables?

A technique that may be useful:  Adjust all the ids by N, where N is big enough to "shift" all the values to a currently-unused range of values in the ids.

I see UUIDs.  How pervasive are they?  How big is innodb_buffer_pool_size?  How much RAM?  How big (in GB) is the dataset?  I ask these because UUIDs are notoriously inefficient when the index becomes bigger than can be cached; I worry that you are already there.  The first thing to help with this will be to change the 1000 to, say, 100.  This is because (in the 'worst' case) nearly every read will have to go to disk because of the randomness of UUIDs.

You say there are a bunch of indexes; I assume they are not UNIQUE?

In the SELECT...

Isn't (CASE WHEN id!=0 THEN id ELSE 0 END) the same as IFNULL(id, 0)?

7 tables are mentioned, we can't see which tables some of the SELECT values come from (eg first_seen).  Do you really need all the LEFT JOINs?

What is the relevance of the SELECT to the merging of two tables?  Isn't there an issue of shifting some ids?

More discussion of chunking: http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks


Fastest key-value store for random disk reads?

Without context, this is a poor question.

Bound to a single machine your requirement is a function of IO performance, not platform. An Access mdb file on a FusionIO card could outperform Trinity on a 5400rpm drive in a narrow band of tests.

You'll have to be more specific if you want answers of any value.

Edit: following comment.

Context would be a description of what you're building. As I indicated, whichever k-v system you choose you will be IO bound when constrained to a single machine. On EC2 block storage the choice of k-v becomes even more irrelevant.

If you're building on EC2 look at the native products they already provide e.g. SimpleDB or Elasticache.


MySQL 5.5 - determining correct write[read]_io_threads on high-end system?

Back on March 15, 2012, I answered this question : MySql recommended hardware.

In my answer, I mentioned a client who is still in my employer's web hosting company to this  very day. They are currently running 3 DB Servers in Circular Replication. Each DB Server has:

MySQL 5.5.9
192 GB RAM
162 GB InnoDB Buffer Pool
Dual hexacore (that's right, 12 CPUs)
1.8TB Disk Volume
528 multitenant databases
500 GB of InnoDB Data
innodb_file_per_table enabled

I have them set with

innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_thread_concurrency = 0
innodb_buffer_pool_size = 162G
innodb_buffer_pool_instances = 2
innodb_log_file_size = 384M

Bottom line: You got the cores, and 128G RAM? What are you waiting for ? Crank it up and have some fun. My client has been all but flabbergasted with these settings against that hardware since March 2011. You will be blown away !!!

CAVEAT

All kidding aside, you should be careful when working with VMs(VMWare, AWS, any other cloud-based MySQL Instances) or commodity hardware. However, with that much firepower in bare metal, by all means, you should exploit those settings because any version of MySQL before MySQL 5.5 works with single threaded InnoDB. If you have MySQL 5.1, the InnoDB plugin (starting from MySQL 5.1.38) would have to be installed and then set. All Percona Server 5.1 binaries already have these in place. Since you have MySQL 5.5, these settings are natively installed and have been tested in many respects. You just have to take responsibility for tuning the new InnoDB Settings.

Please see my other posts on setting InnoDB's new settings:

Jan 11, 2012 : 16 Cores 12 GB RAM server MySql Configuration - my.cnf
Oct 05, 2011 : Query runs a long time in some newer MySQL versions
Sep 20, 2011 : Multi cores and MySQL Performance
Sep 12, 2011 : Possible to make MySQL use more than one core?
May 26, 2011 : About single threaded versus multithreaded databases performance
Nov 24, 2011 : Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)

UPDATE 2013-01-18 21:52 EDT

You need to take the bull by the horns and setup some benchmarks. Let's go back historically. innodb_read_io_threads and innodb_writes_io_threads did not come with MySQL 5.0 or 5.1. Someone created a plugin and made it available in MySQL 5.1.38. The defaults were 4. I also notice that Percona Server implemented these things early in MySQL 5.1 with defaults of 8. Most people who use Percona Server out-of-the-box swear by it and often say that it is better than MySQL. However, this comparison is usually done with tweeking InnoDB. So, of course, Percona Server, with double the number of threads, would be better.

Given this conservative way to get better performance (upgrading to Percona), you will never really know how good, bad, or ugly InnoDB will go unless you tune for it. Keep in mind my post from Nov 24, 2011. Multiple versions of MySQL were benchmarked and, in some cases, InnoDB under MySQL 4.1 outperformed 5.x in a single-threaded environment. That's given the same level playing field.

To tip the scales in your favor, you must tune InnoDB. Rather than give you any single definitive Pick-This-Pick-That guide, you need to read about the settings InnoDB ghas been given to make multiple cores come alive. Not every environment is like my client's dual hexacore servers or your dual hexadecacore.

If I had to give you a good starting point, it would have the be mysqlperformanceblog.com. Everything performance-centric about mysql is there for you to read, select, and benchmark.


Your format string is not correct.

HH:MM:SS should be HH:MI:SS.

Also, when using the 12 hour format, midnight is 12:00:00 AM, not 00:00:00 AM.


Why would you put Identity Increment other than 1 in SQL?

I have used identity increment other than one, especially in MERGE REPLICATION in different regions.

E.g. We have Publisher in NY and Subscriber in LD involved in Merge replication.

We set Publisher's Identity increment to ODD and LD server's identity increment to EVEN. This way you dont get conflicts where MERGE agent runs and tries to merge the changes.

I have talked here with a script that helps determine identity.


Soft delete field causes left join not to return my "left" side data

(Note: completely updated, when I realized your problems was a bit different than I initially thought).

As you probably realize (given the change you made), if you check for a non-NULL value on a LEFT JOIN column in your WHERE clause, it can make your LEFT JOIN act like an INNER JOIN.

There are two ways to handle this problem:

You can perform all your checks in the JOINs ON clause
You can check for the condition you're interested in within the WHERE clause, but OR that with a condition that is true when there are no rows found from the "right" table.

However, this can have two different effects.

When you check a condition in the ON clause, that becomes a part of the condition for joining the rows from the "right" table to the "left" table. So, if a row fails to meet your condition, it has no impact on the rows from the "left" table - it's as if it doesn't exist.

On the other hand, when you're checking in the WHERE clause, it is already tied to the data form the "left" table. So, if there are rows from the "right" table, but none of them match your condition, the rows from the "left" table do get excluded.

With that in mind, let's take a look at what you're trying to do. You want training classes where the trainees (if any) are in status 4, and you want to ignore any trainees marked as deleted.

So, try this instead:

SELECT
    Training.TrainingID AS TrainingID
FROM
    Training
LEFT OUTER JOIN
    TrainingAttendee ta
ON
    (ta.TrainingID=Training.TrainingID AND ta.Deleted = 0)
WHERE
    LOWER(Training.Title) = LOWER('Courtesy Dust Mask Training')
    AND DATEADD(d, DATEDIFF(d, 0, Training.StartDate), 0) >= DATEADD(d, DATEDIFF(d, 0, GetDate()), 0)
    AND (ta.TrainingAttendeeStatusID=4 OR ta.TrainingID IS NULL)
GROUP BY
    Training.TrainingID

This should treat any ta records marked as deleted as if they don't exist, but will only return Training records is they have no attendees, or if they have non-deleted attendees in the desired status.

NOTE: This does change your original query (which you said was working correctly), which was basically acting as an INNER JOIN. Try the new query with and without the OR ta.TrainingID IS NULL in the status check, to see if you really if the results are different, and which results you really wanted.

UPDATE: Further discussion with JonH revealed that he was trying to get a count of the number of seats in each class (where, logically enough, deleted attendees shouldn't be counted). He'd added the following to his query's SELECT list:

   ,ISNULL(COUNT(*),0) AS TakenSeats

However, he was still getting back 1 seat as taken, even if all attendees had been deleted. I explained that this was due to the fact that COUNT(*) was counting the number of rows returned - and even if no TrainingAttendee row was found, you'd still get one row from Training. I recommended changing this to:

   ,ISNULL(COUNT(ta.TrainingID),0) AS TakenSeats

which (since COUNT ignores NULL values) should return 0 when there are no TrainingAttendee rows found for a given Training row.


With a super-user, do a one-time GRANT to both logins and add WITH GRANT OPTION to the end. It sounds like the users have the ability to query their own schemas but are not allowed to grant it to others.

https://www.postgresql.org/docs/9.0/static/sql-grant.html


Web server accessing the database server in Always on availability group

Now my question is if my network team only allows only the Always on availability group listener port will that be a problem

You can configure an AG Listener to use whatever port you want, and you can configure your application to connect using a non-standard port.

what if the network team dont allow access to that port

A "normal" DMZ has external access to hosts in the DMZ and limited access from the DMZ to the internal network.  If the network team won't allow connectivity from the web server to the databse server, you can put the database server in the DMZ or use a cloud hoster instead of a DMZ.
For instance you can use Azure App Service for hosting, and use Azure App Services Hybrid Connections for secure access to on-prem resources


Update column with the value of two columns multiplied

Text values must be converted to numeric before any math operation.

UPDATE schema.table
SET    ColumnA * ColumnC::numeric  -- Use same data type as ColumnA

Have a look at this tutorial


Replicate only a few tables in PostgreSQL 10

To replicate some specific tables, you can use logical replication

Another option is to not replicate at all and access the remote tables through a foreign table


Is there a way to force inheritance of a procedure's schema to all procedures called within it

Honestly, the right way to do this is to fix the code. Schema designation should always be explicit. You can use synonyms in the meantime to mask the problem and buy you time:

CREATE SYNONYM dbo.proc2 FOR schema1.proc2;

However that doesn't help if you have two procedures in the database, schema1.proc1 and schema2.proc2, and they both say EXEC proc2, because they're both going to follow the path of the synonym.

The other thing I highly recommend is to not use schemas as a division for storing different objects with the exact same name. I can see the purpose in a development environment, where you want to easily switch schemas to test your code against a QA or test schema, but this should be wholesale, and schema1.Employees and schema2.Employees should not both be valid at the same time - a better way to have this type of independence would be databases rather than schemas (IMHO).

It shouldn't be "quite a chore" to find all the stored procedures that reference other stored procedures without schema references

You can easily find all the objects that are referenced without a schema:

SELECT CallerSchema = caller_sch.name, CallerName = callers.name,
  dep.referenced_entity_name, caller_mod.definition
FROM sys.sql_expression_dependencies AS dep
  INNER JOIN sys.procedures AS callers
  ON callers.[object_id] = dep.[referencing_id]
  INNER JOIN sys.schemas AS caller_sch
  ON callers.schema_id = caller_sch.schema_id
  INNER JOIN sys.sql_modules AS caller_mod
  ON callers.[object_id] = caller_mod.[object_id]
WHERE dep.referenced_schema_name IS NULL;

You can also do this with brute force parsing, though this is a little fuzzier, and assumes your schema names are not common enough to cause false positives (and that the schema isn't referenced anywhere - frankly the above is much more reliable):

SELECT CallerName = callers.name, CallerSchema = caller_sch.name,
    CalleeName = callees.name, CalleeSchema = callee_sch.name,
    Definition = caller_mod.definition
FROM sys.sql_dependencies AS dep
  INNER JOIN sys.procedures AS callers
  ON callers.[object_id] = dep.[object_id]
  INNER JOIN sys.schemas AS caller_sch
  ON callers.schema_id = caller_sch.schema_id
  INNER JOIN sys.procedures AS callees
  ON callees.[object_id] = dep.referenced_major_id
  INNER JOIN sys.schemas AS callee_sch
  ON callees.schema_id = callee_sch.schema_id
  INNER JOIN sys.sql_modules AS caller_mod
  ON callers.[object_id] = caller_mod.[object_id]
WHERE caller_mod.definition NOT LIKE N'%' + callee_sch.name + N'%';

Also sysdepends (you shouldn't be using this anymore, there are more modern views for this) just reports dependencies. It does not dictate optimizer access path.


How to detect whether a key is present in a JSON object using TSQL?

Try using OPENJSON instead. This returns a Type column that indicates a NULL value for a key. You can LEFT JOIN this to a source list of possible keys and check for a NULL Type value or a NULL return value to determine if the key is present or not. Example below:

declare @data nvarchar(max); set @data = N'{"a":"1","b":"","c":null}';

SELECT [Value],
    CASE
        WHEN [type] IS NULL THEN 'Not Present'
        WHEN [type] = 0 THEN 'Null Value'
        ELSE 'Non-Null Value'
    END AS [KeyPresent]
FROM
(
    SELECT 'a' AS [key]
    UNION
    SELECT 'b' AS [key]
    UNION
    SELECT 'c' AS [key]
    UNION
    SELECT 'd' AS [key]
) keys
LEFT JOIN OPENJSON(@data) jdata ON keys.[key] = jdata.[key]


Restored DB Server Using FQDN Causing Distribution Issues

1, once your run the sp_Drop/addserver, most likely the system needs a start up if there is no other issues. You may also put the downtime into your consideration.

2, If the problem is still there after restarting your server, you may disable the connection between your replica serves, restore each one individually, then set the replication on your new server .

3, You may also experience issues if your SP is old, refer to Microsoft for SP new release.

Thank you,


Querying Extended Events in SQL Server 2008

Try to play with this example:

CREATE EVENT SESSION
[statement_completed_file]
ON SERVER
ADD EVENT
    sqlserver.sp_statement_completed,
ADD EVENT
    sqlserver.sql_statement_completed
    (
        ACTION
        (
            sqlserver.sql_text
        )
        WHERE
        (
            sqlserver.session_id <> 53
        )
    )
ADD TARGET package0.asynchronous_file_target
    (
        set filename=N'C:\stats.xel',
        metadatafile=N'C:\stats.xem'
    )
WITH
(
    MAX_MEMORY=4096KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=1 SECONDS,
    TRACK_CAUSALITY=OFF,
    STARTUP_STATE=OFF
)
GO
ALTER EVENT SESSION [statement_completed_file]
ON SERVER
STATE=START
GO
SELECT event_data,
    event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name,
    event_data.value('(event/data/value)[1]', 'bigint') AS source_database_id,
    event_data.value('(event/data/value)[2]', 'bigint') AS object_id,
    event_data.value('(event/data/value)[3]', 'bigint') AS object_type,
    event_data.value('(event/data/value)[4]', 'bigint') AS cpu,
    event_data.value('(event/data/value)[5]', 'bigint') AS duration,
    event_data.value('(event/data/value)[6]', 'bigint') AS reads,
    event_data.value('(event/data/value)[7]', 'bigint') AS writes,
    event_data.value('(event/action/value)[1]', 'nvarchar(max)') AS sql_text
FROM
(
    SELECT CAST(event_data AS XML) AS event_data
    FROM sys.fn_xe_file_target_read_file('C:\stats*.xel', 'C:\stats*.xem', null, null)
) tbl
GO
DROP EVENT SESSION [statement_completed_file] ON SERVER
GO


Master ip has changed in postgresql replication

Easy, just change host in primary_conninfo and restart the standby.
This replication parameter can be found in recovery.conf before v12 and in postgresql.conf after that.


Query Performance Tuning on a huge table

We have plans to roll it up as well.

Cancel your other plans and do this now. Stop trying to optimise the evaluation of 3 billion rows and instead evaluate rollup + 300k rows. From your outline of the situation, anything else is a waste of time and someone's money.

A parody definition of insanity is doing the same thing over and over and expecting different results. Reading 3 billion rows that aren't changing fits that definition. Stop.


Postgres 12: Inserting array of composite types, composite type has an array field. Malformed array literal, unexpected end of input


mysql - query to fill in missing datetime values

Filling in 'missing' rows is done via an extra table with all possible values, plus a LEFT JOIN:

Create have a table all_minutes with all possible minutes (in some finite range).  (If using MariaDB, use a "Sequence" table.)  Then

SELECT a.time,
       IFNULL(s.data, 0)  AS `data`  -- assuming you want 0, not NULL
    FROM all_minutes AS a
    LEFT JOIN ( SELECT
            LEFT(stat_start,16) AS `time`,  -- eg, '2017-04-07 11:03'
            AVG(`stat_values`) AS `data`
        FROM stats
        WHERE stat_Start BETWEEN NOW() - INTERVAL 1 HOUR AND NOW()
        GROUP BY `time`
        ORDER BY `time`    -- note a change here
              ) AS s    ON s.time = a.time
    WHERE a.time BETWEEN ... AND ...; -- whatever range you need

Note:  Both tables should exclude or include the :00 for seconds.  Otherwise (I think) there could be some difficulty in doing the = in the ON.


MySQL Slave Relay Logging but not logging Binary Log

You forgot one thing
binlog-do-db=our_database
binlog-ignore-db=mysql
binlog-ignore-db=test
replicate-do-db=our_database
log-slave-updates

The option log-slave-updates takes SQL that was read from relay logs and records them in the Slave Binary Logs when the SQL is completed.
Please be very careful. You should never use binlog-do-db and binlog-ignore-db simultaneously. I wrote about this back on Jun 14, 2012 ( Configured MySQL replication but its not working ) which points to an old Percona article on binlog-do-db being dangerous.
SUGGESTION
Of the things you are using, you only need these lines
replicate-do-db=our_database
log-slave-updates

You really don't need binlog-do-db=our_database once you have log-slave-updates working properly.


As per your log statement it seems like that you are committing mistake in your authentication statement such as in your statement

mongo 127.0.0.1:27017 --authenticationDatabase admin -u admin -p

As per MongoDB BOL documentation here With access control enabled, ensure you have a user with userAdmin or userAdminAnyDatabase role in the admin database. This user can administrate user and roles such as: create users, grant or revoke roles from users, and create or modify customs roles.

Connect and authenticate as the user administrator

Using the mongo shell, you can:

Connect with authentication by passing in user credentials, or
Connect first withouth authentication, and then issue the  db.auth()
method to authenticate.

For example, the following creates the user myUserAdmin in the admin database:

use admin
db.createUser(
  {
    user: "myUserAdmin",
    pwd: "abc123",
    roles: [ { role: "userAdminAnyDatabase", db: "admin" } ]
  }
)

To authenticate during connection

Start a mongo shell with the -u , -p , and the --authenticationDatabase  command line options:

mongo --port 27017 -u "myUserAdmin" -p "abc123" --authenticationDatabase "admin"

To authenticate after connecting

Connect the mongo shell to the  mongod:

mongo --port 27017

Switch to the authentication database (in this case, admin), and use db.auth(<username>, <pwd>) method to authenticate:

use admin
db.auth("myUserAdmin", "abc123" )

As I am able to see that in your below mention mongod.conf file the authorization : enable . It's means your authentication is enable. So, there is no issue in your mongod.conf file.

systemLog:
  destination: file
  path: /Users/me/Web/blog/setup/logs/mongodb/mongo.log
  logAppend: true
storage:
  dbPath: /usr/local/var/mongodb
net:
  port: 27017
  bindIp: 127.0.0.1
security:
  authorization: enabled

For your further ref here and here


Is MySQL Community Server 5.7.11 compatible with SLES 11 SP3?

Nobody guarantees their software. The GPL doesn't and neither do the commercial companies - the IT industry is notorious for not actually guaranteeing their work as being fit for purpose in any way and indemnifying themselves against any damages that could be caused.

This is even though they're actually paid for software - at least the GPL doesn't charge money. To clarify, they can, but generally don't. For example, MySQL is available under the GPL and a commercial licence.

If you compile it yourself, you're on your own.
If you download community binaries you're on your own.
If you pay, you're pretty much on your own. What you're really paying for is support and monitoring/backup tools which are not available under the GPL.

Finally, and perhaps to put you at ease (and even answer the question :-) ), if you can compile it, it works. I've compiled many versions of MySQL, MariaDB and Percona Server (it's my preferred way of installing - multiple servers on the same machine are a piece of cake) and I've never had a problem with buggy software (apart from the car-wreck that is MySQL that is! :-) ).

Compiling an old version on a new OS will work - the reverse may not be, but I've never done that.


If I move my SQLdata file from my F drive, delete the F drive, and rename the new Drive F will I have any file path problems?

If I move data from the F drive to a new drive, delete the F drive,
  and rename the new drive, will I loss the file path?

If you're going to move everything on F:\ to a new underlying disk and rename that disk F:\ then you should be okay, but you need to ensure all permissions are correctly migrated to avoid issues during restart.

The basic process would be:

Stop SQL Server
Copy all data from F:\ to new drive using robocopy or similar tool that can bring permissions as well.
Remount F:\ as a new drive letter
Remount new disk as F:\
Restart SQL Server and verify health

Reverting should be as simple as reversing the drive letter changes if you run into issues.


Join is causing other column data to change?

The odds are that your problem is that, when you add in the snacks, you have more than one table that returns more than one row for the columns in your GROUP BY statement - probably Customer_Snacks and Customer_Ledger_LineItems.

When the DB engine performs a join, it matches however many rows in the first table with value ABC with however many rows in the second table have the same value, ABC (outer joins working a little differently, but let's ignore that for the moment).

I'll guess that for a given Customer_Ledger row, you tie to one Customer row, one MealPlan row, one (or zero) PromoCode row, and multipleCustomer_Ledger_LineItems` rows.

I'm also guessing for one Customer_Ledger row and its associated MealPlan row, you can bring back multiple Customer_Snacks rows.

So, when we put all the rows together, let's say we have:

1 Customer row
1 Customer_Ledger row
1 MealPlan row
1 PromoCode row
3 Customer_Ledger_LineItems rows

Match all those together, you get 1 * 1 * 1 * 1 * 3 = 3 rows.

Now, add in 2 Customer_Snacks rows. those are matched against all the rows we already have, so we get 1 * 1 * 1 * 1 * 3 * 2 = 6 rows.

When we go to take our sums, we'll get double the amount for sums from Customer_Ledger_LineItems rows, because each row now shows up twice. And, we'll get triple the amount for the sum from Customer_Snacks, because those rows each now show up three times. Basically, you're getting a CROSS JOIN between the relevant Customer_Ledger_LineItems rows, and the Customer_Snacks rows.

There are two ways to avoid this. You already employing the first option - use a subquery to get the sums from one of the tables returning multiple rows to our grouped values.

The other option is very similar: build a temporary table (or a CTE) with all relevant values that you need, so that you'll just have one row with the sums you need when you go to join the data in. In your case, this would look like:

;WITH SnackSums AS
      (SELECT cs.CustomerID
             ,cs.DeliveryDate
             ,SUM(cs.Quantity) as SnackQuantity
         FROM Customer_Snacks cs
        GROUP BY cs.CustomerID, cs.DeliveryDate
      )

and would go right before the SELECT for your query. Then, you just put ss.SnackQuantity where the subquery was, and add:

LEFT OUTER JOIN
  SnackSums ss
    ON (cl.CustomerId = ss.CustomerId AND ss.DeliveryDate = mp.DeliveryDate1)

right before your GROUP BY. Finally, you'll also need to add ss.SnackQuantity to the GROUP BY, since in the context of your main query this is a stand-alone, non-aggregated value.

Now, I make no promises that this will be faster than the subquery. However, it should be accurate - and it doesn't matter is something is blazing fast, if it isn't accurate.


Found the sollution for this after @TiborKaraszi 's tip about WMI.

Run wmimgmnt.msc on the DB-server
Open the properties of WMI-Control (RM-click)
Open the security tab.
Select CIMv2 en click the Security button below
Add or locate the account or group
In the permissions list, check Remote enable.


SQL Server Cannot Create Transactional replication Error 14013

You can follow these steps before setting up a Publication.
Tutorial: Prepare SQL Server for replication (publisher, distributor, subscriber)

Create Windows accounts for replication.
Prepare the snapshot folder.
Configure distribution.

I believe you skipped this. If not, please edit you question with the steps you already followed successfully.


Get records which doesn't have duplicate records

Assuming you just want to avoid the records that're bold:

Setup:

create table simplequestion
(
 p number,
 c number
);

insert into simplequestion values (23442242,3455);

insert into simplequestion values (53453453,3454);

insert into simplequestion values (23442242,3455);

insert into simplequestion values (23442242,4567);

insert into simplequestion values (23442242,4667);

insert into simplequestion values (53453453,5656);

insert into simplequestion values (53535354,3543);

insert into simplequestion values (34543545,4354);

Query:

select p,c,count(*)
from simplequestion
group by p,c
having count(*)=1;


As you're on 11gR2 and suitably licensed (make sure you check!), I would recommend taking a look at the SQL monitor. This gives a detailed breakdown of all SQL statements that took more ~5s to execute in the past 24 hours.

Using this, you can see the duration of each step in the execution plan, along with I/O and wait details. This will enable you to see why it's taking so long.

The quick SQL executions won't appear by default, but you can add the /*+ MONITOR */ hint to force them in.

You can get a graphical view of the plan using Enterprise Manager or SQL developer. You can also get a text version using SQL. Have a read of this oracle-base article for more details on using the SQL monitor.

It's possible that the reason for the differences is because the SQL is flipping between two execution plans. You can spot this by looking in the AWR to see this. To do this you'll need to find the sql_id for the statement(s) that are causing you issues. You can do this with:

select sql_id
from   v$sql
where  sql_text like '%your query here%'
and    sql_text not like '%not this%';

To find if there's changes, have a read of Kerry Osborne's unstable plans article and look for the "awr_plan_change" script.

If you are switching between plans, you can force it to keep the good plan using SQL profiles. Again, Kerry Osborne has a detailed article on this.

Note that it's possible that the SQL you're interested in isn't stored in the AWR, as only the worst performing statements are kept. We can force details of it to be kept by "coloring" it however, as discussed by Dion Cho.


You should still be able to create a linked server to an LDAP database and query the LDAP database directly via the linked server.

There's information here on setting up the linked server.

To test I setup the linked server based on this post.  Then I ran the following code and got back a recordset with various information in it (where bacon.lab is my AD domain).

SELECT * FROM OpenQuery (
  ADSI,
  'SELECT displayName, telephoneNumber, mail, mobile, facsimileTelephoneNumber
  FROM ''LDAP://bacon.lab''
  WHERE objectClass =  ''User''
  ') AS tblADSI
ORDER BY displayname


SSIS Script transform package won't compile when deployed to dev [Visual Studio 2017]

You need to make sure the package in Visual Studio has been created with the correct target SQL Version you are deploying to.
In Visual Studio right click on the project name and select properties.  Under the Configuration Properties -> General menu you will see the TargetServerVersion.  This must match the SQL version you are deploying to.
Once changed, build and deploy the packages again.


It will only delete the earlier backup if the current one is successful.
You can try doing a full backup, cancel it and do list backup.


Autosync master and slave when master goes offline

No.

Replication will survive the VPN going down, but a crash of the master host does not reliably flush the master's binary log, so on reboot there is no guarantee that the master's Binlog Dump thread does not send the same events to the slave's I/O thread.

You should find a solution that involves the master server staying online. Failing that, you might get away with having the slave run for short periods during which the master is less likely to crash, so that you reduce the possibility of the server crashing during log transfer, but it is not a reliable solution.


Multiple disks vs single disk on virtualized environment

what are the actual benefits of doing so on the above described architecture (if any)?

There are some management benefits, particularly:

You get separate disk performance counters in the VM for Data, Log, and TempDb

You can migrate any one of them to a different storage device without reconfiguring the VM or SQL Server.

You can manage Storage Quality of Service seperately for each virtual disk, capping or reserving IOPS.

You get seperate IO queues in the guest VM which may marginally improve performance, especially in a scenario where perf-critical log writes are intermixed with large volume data file IO.


Storing data in PostgreSQL: One table or two?

If you mix the history data in with the current like that in order to speed up queries over a time period, you do so at the expense of slowing down queries for current data. You can add an extra column to explicitly mark the relevant rows as the current prices (and have id+flag as the PK) but that adds extra work to your business logic to both keep it maintained and respect it in all reports.

Of course if you move the price completely out of the main table you have a similar problem in finding the latest price becoming more expensive unless you have a "latest" flag or do-normalise slightly and keep a copy of the current price in the main table as well as the price history table. Personally I would do the latter, and use a trigger on that product table to automatically update the price history table when a new product is added or the price updated (I'm assuming the products tables does no see massive write activity most of the time so the performance impact of using a trigger here should be minimal) in order to remove that auditing task from your other logic to avoid bugs caused by new code turning up that forgets to update the history/audit.

Caveat: I'm answering this from a general PoV as I've not used postgres much in anger, so do some benchmarks before taking anything I've said regarding performance as fact in that system!


MSSQL over the internet but we connect over RDP

I wrote a blog post about the perils of having your SQL Server exposed to the internet.

You should almost never allow internet access directly to SQL Server.  From your description, it sounds like you have no requirement for that, since you RDP into the machine for access.  I would immediately ensure the port used by the SQL Server is blocked at the firewall.


Go to your SQL Server Configuration Manager. You will find it in the SQL Server folder, under Configuration Tools.

Check the list of services that are currently running. At the minimum you should have started "SQL Server (MSSQLSERVER)" (if it's the default instance).

After you check that, go to the client and to the server protocols and see if the TCP/IP and Shared Memory protocols are enabled. If yes, then you should be set for the first time use of SQL Server.

Open Management Studio and try to connect to the local instance: use local machine name or IP.
If it won't work, please tell us any error that you might get.


How can I prevent/detect that a end user restored a backup?

If you want to prevent the table from being deleted, see here: http://pratchev.blogspot.com/2007/12/prevent-table-drop.html.  If you want to prevent the data from being altered by the end user, simply do not give your users modify permissions to that table.

If you set up your user accounts and permissions properly, changing out the MDF won't work, because the user accounts and credentials won't line up.


How can you view the Query Store settings for a database?

This can be found (near the end) in the sys.database_query_store_options DMV.

SELECT size_based_cleanup_mode, size_based_cleanup_mode_desc
FROM sys.database_query_store_options