Unable to replicate schema changes in transactional replication I have implemented a transactional replication on one of my dbs.

In the documents provided by Microsoftsql (here and here), I should be able to change the schema and that these changes will be replicated to the subscriber side as well. However, I haven't been able to do so.

Is there anything I am missing while setting up transactional replication?

I have used the following customized script in accordance to my requirements, basically replicating each and every column and everything associated(like a complete replica):

use [db_name]
exec sp_changearticle
    @publication = N'db_PUB',
    @article = N'table_name',
    @property =schema_option,
    @value= '0x000003454DFD7FDF',
    @force_invalidate_snapshot =0,
    @force_reinit_subscription =1
GO

The error I get while making changes is something like this:

  Cannot perform action on the table because it is published for replication.

I am thinking of a way to get over this issue, by removing the article from publication, make changes to it and then add it back to publication.

But every time I do this I have to generate a new snapshot and reinitialize the subscription, which doesn't seem to be much efficient.

What can I do to resolve this issue?
Any alternative?

exec sp_dropsubscription
        @publication =  'tTestFNames'
     ,  @article =  'tEmployees'
     ,  @subscriber =  'RSCOMPUTER'
     ,  @destination_db =  'testrep'

  exec sp_droparticle
        @publication =  'tTestFNames'
     ,  @article =  'tEmployees'

  alter table tEmployees alter column Forename varchar(100) null

  exec sp_addarticle
        @publication =  'tTestFNames'
     ,  @article =  'tEmployees'
     ,  @source_table =  'tEmployees'

  exec sp_addsubscription
        @publication =  'tTestFNames'
     ,  @article =  'tEmployees'
     ,  @subscriber =  'RSCOMPUTER'
     ,  @destination_db =  'testrep'

exec sp_startpublication_snapshot
     @publication = N'tTestFNames';
Go

You cannot change the table name or change the primary key of a table that is involved in replication.

you can see the scripts here

You can replicate the schema changes if your publication has @replicate_ddl on.
However

  The ALTER TABLE ... DROP COLUMN statement is always replicated to all
  Subscribers whose subscription contains the columns being dropped,
  even if you disable the replication of schema changes.

You want to avoid running a full snapshot - you want a snapshot just with the affected object(s).

As Brandon Williams said on the comments:
First you check your publication and make sure that immediate_sync and allow_anonymous are set to FALSE (0) to avoid the creation of the full snapshot and not just the snapshot of the article/s changed.

SELECT immediate_sync, allow_anonymous
FROM Your_Published_DBName.dbo.syspublications

Then you can change these properties as described here.

  when i change column name , what will be the effect on subscriber, do
  i have to change the name there as well? while adding subscription or
  will it be able to use the previous name as well

You will need to remove the table from the replication first. Then do your changes - like renaming a column or changing the primary key.
then you need to re-add the table to the publication. Generate a new snapshot - will only have the new version of the table. When you apply this snapshot to the subscriber database - the table there will be overwritten.

You can specify the destination table on the subscriber.

Therefore if you need different permissions, triggers, indexes, stats, or anything else to this table, you need to script all these and re-apply them on the subscription after the snapshot has been applied.

  how the changing table name works, if tableA was replicated and it
  resides at the subscriber, how will changes apply to it, after I
  change the name to tableB at the the publisher. will I have to remove
  it tableA from subscriber or will it be overwritten? or will new
  tableB will be crated at the subscriber?

tableA was part of publication1
you remove tableA from publication1
you rename tableA to tableB
you add tableB to publication1
you generate a snapshot of publication1 (hopefully with only tableB in it)
you add subscriber1 to publication1
you apply the publication1 snapshot on subscriber1
tableB is created on subscriber1


Why does sql server have a hard time rounding floats? if exists (select 1 from sys.tables where name = 'd_ratio') begin drop table d_ratio end
    if exists (select 1 from sys.tables where name = 'elrd') begin drop table elrd end

    CREATE TABLE [dbo].[d_ratio](
        [index] [bigint] NULL,
        [Class] [bigint] NULL,
        [Dratio] [float] NULL,
        [ELR-Act1] [float] NULL,
        [ELR-Act2] [float] NULL
    ) ON [PRIMARY]
    GO

    Insert into D_ratio
    select 0,2503,0.285,0.75,1.33

    CREATE TABLE [dbo].[ELRD](
        [RATE_EFFDT] [date] NOT NULL,
        [CLASS] [varchar](4) NOT NULL,
        [ELR] [decimal](5, 2) NULL,
        [D_RATIO] [decimal](5, 2) NULL,
        [EXPO_ACT] [char](2) NOT NULL
    ) ON [PRIMARY]
    GO

    truncate table elrd
    insert into elrd(Rate_effdt, class, elr, d_ratio, EXPO_ACT)
    select getdate(), right('0000' + cast(class as varchar(10)),4), round(cast([ELR-Act1] as decimal(10,3)),2),Dratio, 1
    from d_ratio

    select Dratio, * from d_ratio
    select D_ratio, * from elrd

It goes in as 0.285 and comes out as 0.28.

However if I cast it as a decimal with 3 points of precision like so:

    truncate table elrd
    insert into elrd(Rate_effdt, class, elr, d_ratio, EXPO_ACT)
    select getdate(), right('0000' + cast(class as varchar(10)),4), round(cast([ELR-Act1] as decimal(10,3)),2), cast(dratio as decimal(10,3)), 1
    from d_ratio

It goes in as 0.285 and comes out as 0.29 - it gets rounded properly.

Question What is SQL Server doing? Is it truncating floats and rounding decimals?

Reading the SQL Server manual, it appears that the database is using an IEEE float under the hood.

As such, "weird things" are known to happen.

See this FAQ from SQLite (I suspect it applies here also):

(16) Why does ROUND(9.95,1) return 9.9 instead of 10.0? Shouldn't 9.95 round up?


Oracle DB - Is it possible to distribute data of a table onto multiple servers? Bigtable-like systems (HBase for instance) allows a table to be distributed transparently across multiple servers. This works as follows:

Each server perform the read/writes operations on distinct partitions (shards) of the same table.
The DBA does not need to explicitely specify the server holding the partition in his query.
When the DBA run his query, the API will route the request to the proper server.
(The main benefit is scale-out scalability for write operations)

My assumption is that Oracle can not do so, but I am not an Oracle expert. References to confirm or correct this hypothesis are much appreciated.

The short answer is: NO, Oracle does not do this in the way you propose BUT:

there is a way to emulate this but don't forget that Oracle has a share everything architecture, meaning that all data is available for all instances on all nodes. Using services and application code that connects to specified services running on their own instances can use specified partitions of tables to partition the application. This is a big effort to create.

Another (BETTER) way is to use Active Data Guard and have your application perform all queries on a physical standby database. With Active Data Guard you can ensure that they always contain up to date data. If you need to do an update/insert/delete operation, do that on the single primary database. Doing so takes away the reading load from the primary and also enables the standby databases to use their own storage, even in their own Data Center. If you really want to scale out, this is the way to do that with Oracle. At any time you can exchange the role of a standby with the primary database allowing for maintenance, giving very good availability. There are several ways to select an instance on which to perform the queries, like round-robin but also based on location or load.


Skipping some data from Indexing in SQL Server I have a situation where SQL Server is giving error that index size exceeding 900 bytes. Its happening because one of the column in index definition is varchar(2000), and whenever its length goes over 800 it gives the error.

So, my question is,  is there any way to say in index definition to include only first 800 characters from the column?

Some people on blogs have suggested to add one additional column to the table which will have first 800 characters of other column, and add indexing on this new additional column rather than original column. But I think that will impact the overall performance because read queries are still querying on original column.

Please suggest if there is some other way to tackle this problem.

I wrote a post about this kind of situation here, which goes into more depth than this answer.

  is there any way to say in index definition to include only first 800 characters from the column?

This can be done by creating a computed column as LEFT(MyColumn, 800), and then indexing that column. Note that the column doesn't have to be materialized in the table using the PERSISTED clause -- creating an index on the column will materialize the values into only the index. Use caution with the computed column approach, though, because it may not behave as you might expect in some cases (issues with computed columns in general, not specific to this situation).

This approach allows you to do both exact matching and LIKE 'xyz%' directly as long as the search term is shorter than the indexed length.

  You can use CHECKSUM to generate a small hash

The result of the CHECKSUM function may be small, but it's not a very good hash function. Use HASHBYTES or a CLR function instead.

Same thing applies as above regarding computed columns. Or, this value is small enough (16 bytes) that it can be materialized in the table using a trigger or application code and indexed from there.

The downsides are that this method only allows for exact matching, because there's no way to match on a partial input, and of course the change management required to add a new non-NULL column to an existing table.

  read queries are still querying on original column.

You'll need to change how queries access the table to make one of these solutions work, so if that's not possible, you're out of luck.

Because we're talking about lossy compression (hashing or truncation), queries always have to also compare the original value against the search term, unless the index is made unique, which may open you up to users reporting weird errors (first 800 characters are identical, or an unlikely hash collision). If the index isn't unique and the original values aren't compared, incorrect results could be returned (note: this could be a security hole).

The additional comparison will likely effect the query plans, and I'm not getting into that without a sample query, but you can find some small examples in the blog post I linked to above. Using your existing table schema and queries, you should be able to make an educated guess about what's going to happen to the plans.


Oracle 11g RMAN restore without catalog I need to restore an Oracle 11.2.0.4 database from Netbackup on to a different host. Is it possible to duplicate the DB with RMAN into an Auxiliary DB but without a recovery catalog? If it’s not possible directly can I restore backup pieces to disk first and then duplicate? What would be the procedure? Many thanks.

Yes, that's perfectly possible.
The main benefit of a Recovery Catalog is when restoring / recovering a database in place.
All the information needed to do the recovery is mastered in the Control Files so if you recover a database "over" itself, then the information needed to do that recovery is also overwritten.  You get "one go" at doing the recovery and that's it.
So, if the Users look at the recovered data and say ...

Oh dear.  Can we go back another hour?

... then the answer is, very simply:

No.

A Recovery Catalog duplicates the information from the Control files, so you can have as many "goes" at an "in-place" recovery as you like.
When duplicating the database onto a new machine, there is no "overwriting" issue so yes, you can do this and, as long as you're prepared to trash the database and its files in between attempts, you can do so as often as you like.


SSRS max upload size limitations We are working on a WiX project for deploying our large application (the server side of the application, that is.) The customer has the choice to have the application server, the SQL Server and the Reporting Server all on the same box, on 2 boxes or on 3 separate boxes (or VM's.)

Here is one of our current issues that we are working through. For the SSRS side, we have a main model that we need to deploy that is about 7mb. With the default install of SSRS, the max is 4mb. I know that we need to manually manipulate the two web.config files to add the 'maxRequestLength' string, but we don't really want to do this on their servers and that is assuming that this meets the clients security policies as well as that they aren't already setting that to some other value for another application. (We can't assume that they are using that SSRS instance only for our application.)

Our customers are local City and County governments.

Is there any way to accomplish deploying the model file manually, bypassing the max upload size limitations?

-WC

From my understanding, This is a restriction specified on the server piece. So you would have to modify it's web.config to change the max upload size.  I'm not aware, although I could be wrong, of a way to change this setting through options within the reporting services management site.

You could ask your client to temporarily modify this.  Or ask them if they would be able to increase the max upload limit to 10mb.


How to install SQL Server 2008 R2 Profiler SQL Server Management Studio doesn't have it on the Tools menu in my case, as is reported in some blogs and forums. Apparently you somehow either got it or not, but how did you get it, if you did, is a luck of the draw, or something? I am a MSDN Subscriber.

Is is possible to install the Profiler using some well-defined steps? Like download X, press menu item Y and click button Z sort of prescription?

[Edit additional info]
Upgrading from SQL Server 2005 Enterprise Edition to Server 2008 R2 Enterprise Edition didn't help (previously, of 2008 R2 editions I had only Express Edition). I am trying Setup program's various options but am unable to upgrade from Basic Tools (originally installed with 2008 R2 Express Edition) to Complete Tools, which I hope would contain the Profiler. Complete Tools option can't be checked in any variation of Setup options that I am trying.

if you allready have a setup of MS SQL Server 2008 R2, try to uninstall the tools only:

setup.exe /ACTION=UNINSTALL /FEATURES=TOOLS /QUIETSIMPLE

And the install it again:

setup.exe /ACTION=INSTALL /FEATURES=TOOLS /QUIETSIMPLE /IAcceptSQLServerLicenseTerms


How do I change the text in the export format dropdown on SQL Reporting server I am looking for a way to change the text in the export format dropdown of a report on the SQL Reporting server (2005). For example, currently, one of the dropdown items is named: Web archive. I would like to change that to say: Web archive (.mhtml).

I've done some searching on google, but couldn't find any satisfactory answer. I have also searched through both config files in the Report Services folder of the instance in question, but found nothing.

Any assistance is appreciated.

Found the answer.

You can use the OverrideName xml tag to rename the display name of an export format. See this Microsoft article.


Cube Designer Aggregation Wizard Optimization Zero Percent Every Time I am pretty new to SSAS.

I have a cube (SSAS 2008 R2) that once had aggregations that has been created using the Aggregation Design wizard. When changing schema a few dimensions, i encountered an issue that indicated a field I had removed was being used in aggregations. Thus, my project would not longer build. I searched (even in View Code) for this field and could not find it. Then, perhaps a rookie mistake, I removed the aggregations, thinking I would create new ones once I was able to build the project. However, all attempts to create aggregations fail. The Agg design wizard either creates 0 aggregations for 0% optimization, or it creates some (if i make specific selections in the wizard instead of keeping default choices), but still with 0% optimization. I get the same behavior when running the wizard from Management Studio.

All of the same settings on attributes for OptimizedState, etc, are in place from then the original aggregations were created. I am just now getting back to this after many more changes so simply rolling back is not an easy option.

I even tried creating some manually that I was confident would help. But using Profiler has shown that those aggregations are ignored.

How can I determine why the Agg Wizard is behaving this way? And how can i get some aggregations created?

Thanks in advance.

Alrighty. So, I had inherited this cube from the original architect. I ended up adding a couple data dimensions, adding to the several that were already there. The is data with many different kind of dates so the Date and Time dimensions play a lot of roles. Apparently, i reach a tipping point. Today, after a co-worker created a smaller version of the cube using the measure group I was having issues with and was able to create aggregations without difficulty, i tried something i had not before. In the aggregation design wizard, i set the all but the most commonly used dimensions to None. Thus, I was asking the wizard to create fewer aggregations. I was able to create aggregations with solid optimization without any difficulty.


Unable to drop database user without login One of our developers with blanket privilege on a database wanted to create a regular user mapped to a login when what he mistakenly created was a user without login and now I'm unable to remove the user. DROP USER XX command runs forever with no result nor an error. The user doesn't own any schemas as well. How can I remove this user?
Thank you

Check for any blocking SPIDs when you have issued the DROP USER XXX command.

Then kill.


How to "warm up" a cold DB server? 1st setup was a DB slave that was used by customers. That was sufficiently fast.
2nd setup (now) is replication of a DB slave that is just used by me. The current setup is slower on query execution (even though I don't have to share CPU and memory) and the theory is that this is because the DBMS is not "warm" by use but f.x. has to reload the index every time I use it. This leads to weaker performance. The conclusion would be to run scheduled queries that keep the DB "warm".

I would like to know three things which are directly connected:

What is the primary reason for this effect? Is it because after every small write access to the slave which gets replicated onto my server, indexes are loaded into the memory first before a query is executed? My guts tell me there something more complex going on.
How would a query have to look like that optimally uses the DB (scheduled) to keep it warm and ready? I am aware of this post: MySQL warm procedure - but I am not so much interested in a generic query but in understanding the logic behind it.
What determines how frequently such a keep-warm-query (or set of) should be run?

SHOW VARIABLES LIKE "%version%";

innodb_version          5.6.13-rel61.0
protocol_version        10
slave_type_conversions  -
version                 5.6.13-rc61.0
version_comment         Percona Server (GPL), Release 61.0
version_compile_machine x86_64
version_compile_os      Linux

Database "warm up" is a real thing... but then again, people making excuses for under-performing systems is also a real thing.  An important take-away here is that once a server is warmed up, it doesn't generally spontaneously cool back down again.

Warm-up is needed because the best performance of a database is achieved when its various caches are populated with the most relevant data, since reading from memory is generally substantially faster than reading from disk.

InnoDB has the InnoDB buffer pool.  MyISAM has the key cache and the OS cache.  Then there's also the MySQL query cache.  MySQL has the open table cache.  Some or all of these structures contribute to the ideal performance of a server.  When a server is handling traffic that is characteristic of its workload, it tends to have the most frequently used -- and therefore presumably the most relevant data -- already cached.

Take the load off of an active MySQL server and you will not see it start closing tables and evicting data from its caches.  If you leave it running, then come back to it tomorrow, you should find that it's still as warm as you left it.

The need for a warm-up is not typically something that is a relevant consideration needed unless the server process is restarted.  Percona Server has a feature that can pre-warm a server's InnoDB buffer pool on restart, by storing the pointers to the pages that were in-memory before shutdown and reloading those pages again into the pool on startup.

However, you can also undo a well-warmed system to some extent by taking a full backup from the client-side of the server, such as with mysqldump and most graphical tools, but not backup utilities that understand native tablespaces, like innobackupex (if my understanding of how that utility works is correct), since a large portion of the data being backed up may be data that is older or irrelevant to the primary workload but may still cause the eviction of other data from the various buffers and caches as it is loaded in, on its way back out for the backup.

The logic in the question you referenced is that reading from the tables and their indexes brings them into memory -- warms things up -- before the "real" queries jump in to start doing their work.

Generally speaking, though... a single query, run once, will have warmed up whatever structures were needed to execute that particular query.  If the same query, executed again immediately, is still slow, then warm-up is not the issue.  Indexes are not reloaded every time you use them, unless there is competition for space in the buffer and cache structures.


Why does year 0 become year 2000? select date '0-1-1';

2000-01-01

Year 0 doesn't exist according to Wikipedia, so shouldn't Postgresql give an error instead of jumping to 2000?

  Year zero does not exist in the anno Domini system usually used to
  number years in the Gregorian calendar and in its predecessor, the
  Julian calendar. In this system, the year 1 BC is followed by AD 1

https://en.wikipedia.org/wiki/Year_zero

edit: Even though inputs like date 123-1-1 work as expected, somehow they made it so anything less than 100ad would require leading 0's to prevent this weird behavior, ex: date 0001-01-01. Given this information, I tried date 0000-01-01 and it does indeed give an error, specifically "22008 out of range".

Because that is how it was designed and implemented. See here

  If BC was not specified, and if the year field was two digits in length, then adjust the year to four digits. If the field is less than 70, then add 2000, otherwise add 1900.

(although two digits should probably be "one or two")

People often use the last digits of a recent past or near future year to represent the year, dropping the 19 or the 20.  This heuristic was designed to accommodate that raw fact.

That the year 0 doesn't exist has nothing to do with it.  The years 1, 2, and 69 do exist, and yet the heuristic applies to that as well.


GRANT CONTROL SERVER when user may be an entity owner I have a login called user1. He only has the role of public. I want to grant CONTROL SERVER to him. So from sa I run this query

USE [master]

GRANT CONTROL SERVER TO [user1]

Which gives me this error

  Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.

The only reason I can think of that this error is getting hit, is the entity owner condition. user1 may be an entity owner.

My question is, what can I do to give this CONTROL SERVER permission to user1?

If you suspect the user is an entity owner, you can confirm that by running this query, which shows all objects in the current database, along with their respective owner.  By default, objects are owned by the schema owner, however objects can have their ownership changed to any database principal.

SELECT ObjectName = s.name + N'.' + o.name
    , SchemaOwnerName = dps.name
    , ObjectOwnerName = dpo.name
FROM sys.objects o
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
    LEFT JOIN sys.database_principals dpo ON o.principal_id = dpo.principal_id
    LEFT JOIN sys.database_principals dps ON s.principal_id = dps.principal_id
ORDER BY s.name, o.name;

To show how this works, I've setup a quick MCVE:

--create a test user
CREATE USER OwnerTest WITHOUT LOGIN;

--create a table, and change the ownership to OwnerTest
CREATE TABLE dbo.Test (id int NOT NULL);
ALTER AUTHORIZATION ON dbo.Test TO OwnerTest;

--check the list of owners
SELECT ObjectName = s.name + N'.' + o.name
    , SchemaOwnerName = dps.name
    , ObjectOwnerName = dpo.name
FROM sys.objects o
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
    LEFT JOIN sys.database_principals dpo ON o.principal_id = dpo.principal_id
    LEFT JOIN sys.database_principals dps ON s.principal_id = dps.principal_id
WHERE (dps.name <> s.name OR dpo.name IS NOT NULL)
ORDER BY s.name, o.name;

The results:

╔════════════╦═════════════════╦═════════════════╗
║ ObjectName ║ SchemaOwnerName ║ ObjectOwnerName ║
╠════════════╬═════════════════╬═════════════════╣
║ dbo.Test   ║ dbo             ║ OwnerTest       ║
╚════════════╩═════════════════╩═════════════════╝

Attempt to drop the user results in an error:

DROP USER OwnerTest;

  Msg 15183, Level 16, State 1, Line 18
  The database principal owns objects in the database and cannot be dropped.

If we drop the table first, then the user, the operation succeeds:

DROP TABLE dbo.Test;
DROP USER OwnerTest;

  Commands completed successfully.


Syntax error with concat in trigger near signal statement Please help me with syntax for MySQL 5.6.

For some reason I get the error:

ERROR 1064 (42000): You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version
for the right syntax to use near '('WRONG SOCIAL: ', new.social);
        end if;
        if not (new.photo is nul'

When trying to run this SQL code:

create table table_social (
        sid varchar(250) primary key,
        auth char(32) not null,
        social integer,               -- to be checked by triggers
        female boolean,
        given varchar(250) not null,
        family varchar(250) null,
        photo varchar(1000) null,     -- to be checked by triggers
        place varchar(250) null,
        ip varchar(250) not null,
        uid integer not null references
            table_users(uid) on delete cascade
);

delimiter //

create trigger social_insert_trigger before insert on table_social
for each row begin
        if new.social < 0 OR new.social > 6 then
                signal sqlstate '45000'
                set message_text = concat('WRONG SOCIAL: ', new.social);
        end if;
        if not (new.photo is null OR new.photo regexp '^https?://') then
                signal sqlstate '45000'
                set message_text = concat('WRONG PHOTO: ', new.photo);
        end if;
end
//

create trigger social_update_trigger before update on table_social
for each row begin
        if new.social < 0 OR new.social > 6 then
                signal sqlstate '45000'
                set message_text = concat('WRONG SOCIAL: ', new.social);
        end if;
        if not (new.photo is null OR new.photo regexp '^https?://') then
                signal sqlstate '45000'
                set message_text = concat('WRONG PHOTO: ', new.photo);
        end if;
end
//

I tried removing concat, replacing single quotes by double quotes, splitting signal line in two (as above) and back - and still can not find out, what is wrong.

Test this code:

drop table if EXISTS table_social;
create table table_social (
        sid varchar(250) primary key,
        auth char(32) not null,
        social integer,               -- to be checked by triggers
        female boolean,
        given varchar(250) not null,
        family varchar(250) null,
        photo varchar(1000) null,     -- to be checked by triggers
        place varchar(250) null,
        ip varchar(250) not null,
        uid integer not null references
            table_users(uid) on delete cascade
);

delimiter //

create trigger social_insert_trigger before insert on table_social
for each row begin
        if new.social < 0 OR new.social > 6 then
                set @message_text = concat('WRONG SOCIAL: ', new.social);
                signal sqlstate '45000'
                set MESSAGE_TEXT = @message_text;
        end if;
        if not (new.photo is null OR new.photo regexp '^https?://') then
                set @message_text = concat('WRONG PHOTO: ', new.photo);
                signal sqlstate '45000' set message_text = @message_text;
        end if;
end
//

create trigger social_update_trigger before update on table_social
for each row begin
        if new.social < 0 OR new.social > 6 then
                set @message_text = concat('WRONG SOCIAL: ', new.social);
                signal sqlstate '45000'
                set message_text = @message_text;
        end if;
        if not (new.photo is null OR new.photo regexp '^https?://') then
                set @message_text = concat('WRONG PHOTO: ', new.photo);
                signal sqlstate '45000'
                set message_text = @message_text;
        end if;
end
//

it not understand CONCAT() in SIGNAL SQLSTATE SET message_text


An SQL Server 2008 R2 based program fails when updating stored procedures? I'm trying to update MIP software and one of the "companies" fails while it is updating a stored procedure.

Is there any place in SQL Server 2008 R2 / Windows where I could see an error message about what might be causing this?  When I did the installation I gave it the sa password, and it worked for all the other "companies" that we have in the system just fine.

Considering that you're running the upgrade, this SQL Server instance must not be in production, right? If so, why don't you fire up the SQL Profiler tool to see what's happening behind the scenes. The trace should show you where the troublesome spot is. Happy troubleshooting! Hope you solve the problem.


Mongo assigning user permsions issue on docker I'm using docker image mongo and I extend it to create a user and assign permissions on image build with such a line COPY init.sh /docker-entrypoint-initdb.d/

Here is the init.sh:

#!/bin/bash
set -e;

mongo -u mongo -p asdfasdf --authenticationDatabase admin <<-EOJS
    use admin;
    db.createUser({
      user: "mongouser",
      pwd: "asdfasdf",
      roles: []
    });

    db.grantRolesToUser(
      "mongouser",
      [
        "root",
        {role: "readWrite", db: "translations"},
        {role: "readWrite", db: "notifications"}
      ]
    );

    exit;
    EOJS

all, looks good, when I check in the terminal I can see that user exists and has all permissions

root@8f26d6da46a7:/# mongo -u mongo -p asdfasdf  --authenticationDatabase admin
> use admin
switched to db admin
> db.system.users.find();
{ "_id" : "admin.mongo", "userId" : UUID("7197ece0-014c-44ea-a5d9-cbb6ef683582"), "user" : "mongo", "db" : "admin", "credentials" : { "SCRAM-SHA-1" : { "iterationCount" : 10000, "salt" : "hRtMnmboNWoZMK0uzbftpQ==", "storedKey" : "AoFg3PCSQpcT/mcl94w8Kb1C0kg=", "serverKey" : "V1gll09EhlqAnk3kVoWn3s+0X5c=" }, "SCRAM-SHA-256" : { "iterationCount" : 15000, "salt" : "phs+erLhT7CwBHqo9ruK5eN7JO2GVC9t8ovutQ==", "storedKey" : "Rv8CTc/b1hY6xFAdyXjC5nFdvwbvxFzeln065rEMjyI=", "serverKey" : "VkkZ1BNBf5gfduuHMXGQlGgrumMct1nvbC8AEfFJXvw=" } }, "roles" : [ { "role" : "root", "db" : "admin" } ] }
{ "_id" : "admin.mongouser", "userId" : UUID("74ad059c-57de-4a75-93c4-3d3bb00d5a14"), "user" : "mongouser", "db" : "admin", "credentials" : { "SCRAM-SHA-1" : { "iterationCount" : 10000, "salt" : "hh2dgliZhjl/1Is97rwiCg==", "storedKey" : "nkD+K7dbnI1PMks/gae4EuADm+Q=", "serverKey" : "696PKwhA+8Aib6QEfWRxkH0HXgE=" }, "SCRAM-SHA-256" : { "iterationCount" : 15000, "salt" : "4SE8LG8dOP07QJME8dwhTyEicmQtcIlRZjPnsQ==", "storedKey" : "7LPWRkX1U0MVfKfvQ2Pf7fMz7T5MntMV6tS5JfgFTmU=", "serverKey" : "nTk38FwgZPPf9Tlv5/Ow5C+SFvS01ciTkjqwzRp5HWs=" } }, "roles" : [ { "role" : "readWrite", "db" : "notifications" }, { "role" : "root", "db" : "admin" }, { "role" : "readWrite", "db" : "translations" } ] }

but when web application trying to run some query it's throwing error Authentication failed. for both databases.

It worked before when I use only one database (translations), but after I add permissions for the notifications database it stops working for both databases.

What I'm doing wrong, it seems that permissions set correctly?

okay, I make it work like this

#!/bin/bash
set -e;

mongo -u mongo -p asdfasdf --authenticationDatabase admin <<-EOJS
    use notifications;
    db.createUser({
      user: "mongouser",
      pwd: "asdfasdf",
      roles: [  {role: "readWrite", db: "notifications" }  ]
    });
    use translations;
    db.createUser({
      user: "mongouser",
      pwd: "asdfasdf",
      roles: [  {role: "readWrite", db: "translations" }  ]
    });

    exit;
    EOJS

but why I need to create separate users, why I can't assign the same usr to multiple databases?


DBCC CHECKDB REPAIR_REBUILD taking too long I'm administrating a database that became corrupt and I tried to do a REPAIR_REBUILD.

The server on this installation is a bit weak but the database isn't that big (roughly 2GB).

It's been running for 48 hours and still not completed. Is it possible ? And is there any way to check the current state other than just seeing "Executing query" ?

You could always run the following script to check the running tasks:

SELECT
    start_time,
    DATEADD(ms,estimated_completion_time,GETDATE()) AS 'EstimatedEndTime',
    percent_complete,
    --sqltext.TEXT,
    req.session_id,
    req.status,
    req.command,
    req.cpu_time,
    req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle)
where req.command like '%DBCC%'

Comment out the sqltext.TEXT column if you wish to view the whole statement.


MySQL 5.7: Scope of SET TRANSACTION ISOLATION LEVEL for SELECT The documentation states that without the GLOBAL or SESSION keyword, the isolation level will be valid for the next transaction and will be reverted to the session default after the transaction was commited.

With autocommit enabled, what is the scope of an transaction isolation level for a SELECT statement?

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT * FROM mytable WHERE id = 1;

Is a SELECT statement a complete transaction, meaning the isolation level will be reverted to session default after the SELECT was executed, or does a SELECT somehow not represent its own transaction, meaning that the isolation level will not be restored after a SELECT statement?

(To answer question in comment)

There are three ways to build a transaction:

(1)

BEGIN;
any number of statements  -- all in one transaction
COMMIT;

(2)

SET AUTOCOMMIT = ON;
...
statement  -- This is a transaction
...
statement  -- This is a different transaction

(3)

SET AUTOCOMMIT = OFF;
...
statement
statement
COMMIT;  -- 2 statements in a transaction

statement
statement
COMMIT;  -- another 2 statements in a different transaction


'Row Goal' different behavior - why? This is a heap with 400+ Million rows and 16 non clustered indexes, 146 columns. Please do not focus on why I cannot create a clustered index. That is not in my control and not in scope to my question.

SQL Server:

  Microsoft SQL Server 2012 (SP4-GDR) (KB4057116) - 11.0.7462.6 (X64)   Jan  5 2018 22:11:56   Copyright (c) Microsoft Corporation  Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3  (Build 9600: ) (Hypervisor)
  Database Compatibility Level:SQL Server 2008(100)

When I run following query (anonymized), I get this actual execution plan:

SELECT TOP ? *
FROM Schema1.Object1 Object2
WHERE Object2.Column1 LIKE ?
ORDER BY Object2.Column2 DESC

Same database restored to SQL 2017 CU13 and change compatibility mode to SQL Server 2012 (110) (following is the full version name), and I get this actual execution plan.

  Microsoft SQL Server 2017 (RTM-CU13) (KB4466404) - 14.0.3048.4 (X64)   Nov 30 2018 12:57:58   Copyright (C) 2017 Microsoft Corporation  Enterprise Edition (64-bit) on Windows Server 2016 Standard 10.0  (Build 14393: ) (Hypervisor)

I totally understand that 'ROW GOAL' is in play here. You cannot see it in the first plan as it was not exposed in the query plan before. To be sure I can run the SQL Server 2008 version with Trace Flag 4138 and SQL Server 2012 version with DISABLE_OPTIMIZER_ROWGOAL hint. Both cases I get significant performance gain. Gain happens because optimizer pick the right index and does not choose a nested loop join.

Question:

While both compatibility level is prior to SQL Server 2014 version why EstimatedRowsWithoutRowGoal (I understand you cannot see it in 2008) is different?
Why Index Scan qualify 1.1 Million rows in SQL 2008 vs 8.7 Million rows in SQL 2012? What heuristics changed (even I am not using new optimizer launched with Compatibility 120)?

I read this article by Paul White and all 8 links given in the article.
Also read More Showplan enhancements – Row Goal by Pedro Lopes

Your 2 queries (and tables?) don't exactly match.

The sql server 2008 compatibility level query has 143 columns returned while
the sql server 2012 compatibility level query  has 146 columns returned.

Please validate any other differences in these tables.

  Why Index Scan qualify 1.1 Million rows in SQL 2008 vs 8.7 Million
  rows in SQL 2012?

Your index scan is returning rows, ordered by Object2.Column2
to then push their bookmark

(bookmark=column144 / Column147 in your anonomized plans)

to the RID lookup and filter on Object2.Column1.

Initial guess: Ties?

This leads me to believe that Object2.Column2 has ties, resulting in different rows to be read first.

Both are executing ordered prefetching

  <NestedLoops Optimized="0" WithOrderedPrefetch="1">

  ... An ordered prefetch preserves key order on the inner side of the
  nested loops join,  so SQL Server ensures that asynchronous I/Os are
  processed in the correct order. ... Our test query features an ORDER BY
  clause that the optimizer can avoid sorting for by using ordered
  prefetch...

Source

I might be wrong, but what I take from this is that, as long as the ordering is preserved, different rows could be fed to the RID lookup operator, supporting the ties claim.

You could try running the query with a unique order and adding these query plans to the question.

  While both compatibility level is prior to SQL Server 2014 version why
  EstimatedRowsWithoutRowGoal (I understand you cannot see it in 2008)
  is different?

We would need detailed statistic information here. The statistics show different estimates between the tables, resulting in a difference in EstimatedRowsWithoutRowGoal

You could try running a full scan update before running both queries while selecting the same amount of columns.

Testing

When doing tests between sql server instances (2008) & (2017 with compat 2012)

I keep on getting the same amount of rows returned:

2008 (compatibility):

Query plan

2012 (compatibility):

Query Plan

Even when changing Column2 to hold many duplicates, the estimates & actuals remain the same.

UPDATE dbo.Object1
SET Object1.Column2
= Object1.Column2 % 400;

As expected, estimates remain the same when adding the extra columns to the table in the compat 2012 db

ALTER TABLE dbo.Object1
ADD ExtraColumn1 int default(1) with values;
ALTER TABLE dbo.Object1
ADD ExtraColumn2 int default(2) with values;
ALTER TABLE dbo.Object1
ADD ExtraColumn3 int default(3) with values;

Same story with enabling the traceflags

Conclusion

This kind of rules out my previous statement about ties in your data, but also shows that I cannot replicate your problem with my dataset (much smaller).

Even if I set the entire column2 to the same value, there is 0 difference in the amount of rows returned. Data differences seem like what should cause this.

Dataset used

CREATE DATABASE TEST
GO
USE [master]
GO
ALTER DATABASE [TEST] MODIFY FILE ( NAME = N'TEST', FILEGROWTH = 1048576KB )
GO
ALTER DATABASE [TEST] MODIFY FILE ( NAME = N'TEST_log', FILEGROWTH = 1048576KB )
GO
alter database test set recovery simple
GO
backup database test to disk = 'NUL'

GO

use TEST
go

CREATE TABLE dbo.Object1(
Column1 int,
Column2 int,
Column3 int,
Column4 int,
Column5 int,
Column6 int,
Column7 int,
Column8 int,
Column9 int,
Column10 int,
Column11 int,
Column12 int,
Column13 int,
Column14 int,
Column15 int,
Column16 int,
Column17 int,
Column18 int,
Column19 int,
Column20 int,
column1_varchar varchar(20));

CREATE INDEX Index1
on dbo.Object1(column15,Column16,Column17)
 CREATE INDEX Index2
on dbo.Object1(Column2,Column10,Column19)

SELECT top(1000000)  --1M
row_number() over(order by (select null)) as rownum
INTO #temp
FROM master..spt_values spt1
cross apply master..spt_values spt2
cross apply master..spt_values spt3

INSERT INTO dbo.Object1
WITH(TABLOCK)
SELECT
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
'ABC'+ CAST(rownum as varchar(20))
FROM #temp
GO 3


MySQL Case Sensitive Join on Varchar I have two tables, both with identical data types, character sets, and collation explicitly specified.

CLERK CHAR(3) CHARACTER SET latin1 COLLATE latin1_bin NULL

For a case-sensitive join on the CLERK field, do I need to specify the collation in the join clause as well, or does the fact that it is specified at the DDL level mean that it isn't needed in the join?

FROM
    CUSTOMER S JOIN
    CLERK C ON S.CLERK = C.CLERK COLLATE latin1_bin

For a case-sensitive join on the CLERK field, do I need to specify the collation in the join clause as well, or does the fact that it is specified at the DDL level mean that it isn't needed in the join?

Simple answer: No need to specify the collation in the query. DDL level collation (especially when it's the same on both sides and both sides are columns) will be used (which is why we specify it at the DDL level in the first place).

Detailed answer: there is a hierarchy of precedence for which collation to use in a given operation (concatenation, predicate, etc). The collation used not only depends on whether it's a column vs a literal, etc, but also whether it's Unicode vs non-Unicode, and even binary vs non-binary. The full description can be found here, Collation Coercibility in Expressions, which is the MySQL 5.7 documentation since you are using that version. One rather interesting rule is:

  For an operation with operands from the same character set but that mix a _bin collation and a _ci or _cs collation, the _bin collation is used. This is similar to how operations that mix nonbinary and binary strings evaluate the operands as binary strings, except that it is for collations rather than data types.

All of that being said, I need to point out that the statement "binary collations are case-sensitive", while extremely common, is definitely incorrect.

On a very basic level, sorting is different. Case-sensitive collations will sort "a" with "A" (though which one comes first can depend on the culture), "b" with "B", and so on. Binary collations will sort according to the underlying value / Code Point of each character, which becomes very apparent when uppercase and lowercase versions of a character separated by others according to their value.
Being case "sensitive" means that you can also be "INsensitive" to other properties of characters, the main one being accents. That is also the only other property when it comes to non-Unicode character sets, but Unicode allows for Kana type sensitivity, width sensitivity, and SQL Server (as of version 2017) even allows for variation selector sensitivity. Binary collations do not allow for a character to equal anything other than itself, even if other forms of it exist. Again, this doesn't really happen so much in non-Unicode character sets, but in Unicode there can be several versions of a character, including wide, superscript, subscript, italics, upside down (referred to as "turned"), etc. MySQL, starting in version 8.0 (as far as I can tell), is adding more variations to the sensitivity options (at least for the ut8mb4 character set and collations):

New collations in MySQL 8.0.0 (accent and case INsensitive)
MySQL 8.0.1: Accent and case sensitive collations for utf8mb4 (accent and case sensitive)
The MySQL 8.0.2 Milestone Release is available (accent sensitive, case INsensitive)

To illustrate both of these points, I have set up a demo on the awesome db<>fiddle. I had to use MySQL 8.0 not only to get the _ai_ci collation, but also because the COLLATE latin1_general_ci clause (2nd to last query, #5) had no affect (for some odd reason; documentation states that when collation name has only _ci, then _ai is implied, yet for both MySQL 5.6 and 5.7 on db<>fiddle, the behavior is still _as_cs or _bin).

AND, there are even other ways in which binary collations are not "case-sensitive". They cannot account for:

combining diacritics
expansions
contractions

I did not list or provide examples for these earlier because they do not pertain to 8-bit encodings, and latin1 is an 8-bit encoding. These are features of Unicode, and so should apply to any Unicode collation (though I have not tested them on MySQL, but they are implemented correctly in SQL Server).

P.S. I have a detailed explanation of all of this (including the Unicode-specific behaviors noted directly above) in the following post: No, Binary Collations are not Case-Sensitive. That is currently framed only in the context of SQL Server, but I can work in the example that I came up with for this answer when I have time. What's important is that the concept is the same across RDBMSs.

For reference (just in case db<>fiddle is inaccessible), the queries are:

Query 1

CREATE TABLE CLERK (
  CLERK_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  CLERK CHAR(3) CHARACTER SET latin1 COLLATE latin1_bin NULL
);

CREATE TABLE CUSTOMER (
  CUSTOMER_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  NAME VARCHAR(10) NOT NULL,
  CLERK CHAR(3) CHARACTER SET latin1 COLLATE latin1_bin NULL,
  CLERK_CI_AI CHAR(3) CHARACTER SET latin1 COLLATE latin1_general_ci NULL
);

INSERT INTO CUSTOMER (NAME, CLERK, CLERK_CI_AI) VALUES('John Doe', 'Ü', 'Ü');

INSERT INTO CLERK (CLERK) VALUES('Ü');
INSERT INTO CLERK (CLERK) VALUES('ü');
INSERT INTO CLERK (CLERK) VALUES('U');
INSERT INTO CLERK (CLERK) VALUES('u');
INSERT INTO CLERK (CLERK) VALUES('Ù');
INSERT INTO CLERK (CLERK) VALUES('ù');
INSERT INTO CLERK (CLERK) VALUES('Û');
INSERT INTO CLERK (CLERK) VALUES('û');

INSERT INTO CLERK (CLERK) VALUES('Y');
INSERT INTO CLERK (CLERK) VALUES('y');
INSERT INTO CLERK (CLERK) VALUES('Ý');
INSERT INTO CLERK (CLERK) VALUES('ý');

Query 2

SELECT 0 AS "ver", C.*
FROM   CLERK C;

Query 3

SELECT "bin sort" AS "ver", C.*
FROM   CLERK C
ORDER BY C.CLERK;

Query 4

SELECT "cs sort" AS "ver", C.*
FROM   CLERK C
ORDER BY C.CLERK COLLATE latin1_general_cs;

Query 5

SELECT 1 AS "ver", S.*, C.*
FROM CUSTOMER S
JOIN CLERK C
  ON S.CLERK = C.CLERK;

Query 6

SELECT 2 AS "ver", S.*, C.*
FROM CUSTOMER S
JOIN CLERK C
  ON S.CLERK = C.CLERK COLLATE latin1_bin;

Query 7

SELECT 3 AS "ver", S.*, C.*
FROM CUSTOMER S
JOIN CLERK C
  ON S.CLERK_CI_AI = C.CLERK;

Query 8

SELECT 4 AS "ver", S.*, C.*
FROM CUSTOMER S
JOIN CLERK C
  ON S.CLERK_CI_AI = C.CLERK COLLATE latin1_bin;

Query 9

# is accent-sensitive even though documentation states it should be
# accent-INsensitive
#
SELECT 5 AS "ver", S.*, C.*
FROM CUSTOMER S
JOIN CLERK C
  ON S.CLERK_CI_AI = C.CLERK COLLATE latin1_general_ci;

Query 10

SELECT 6 AS "ver", S.*, C.*
FROM CUSTOMER S
JOIN CLERK C
  ON CONVERT(S.CLERK_CI_AI using utf8mb4)
       = CONVERT(C.CLERK USING utf8mb4) COLLATE utf8mb4_0900_ai_ci;


why does it shows truncated incorrect integer values when using char(salary,'$99,999) I tried the following query:

select
    concat(e.first_name,"  ",e.last_name) as 'employee name',
    char(s.salary*15/100,'$99,999') as 'No.Of Dollors'
from
    employees e
join
    salaries s
    on e.emp_no=s.emp_no
group by
    e.emp_no,s.emp_no;

It raises:

  truncated incorrect integer vale:'$99,999'

You are receiving a warning as '$99,999' is not an integer. The CHAR() function expects integer(s) as input.

As suggested by McNets: it seems you are trying to use the CHAR() function for something it is not intended to do. The CHAR() function accepts integers as input and will interpret each integer as a character code, returning corresponding, concatenated character(s) as a string.

The function you seem to be looking for is the FORMAT() function, which will format a numerical value and returns a string. This will however not prepend a currency symbol, so that should then be added via the CONCAT() function.

As suggested by Kumar: the following will produce the result (I expect) you wish to achieve:

SELECT
    CONCAT(e.first_name,"  ",e.last_name) AS 'employee name',
    CONCAT('$', FORMAT(s.salary*15/100, 2)) AS 'No.Of Dollors'
FROM
    employees e
JOIN
    salaries s
    ON e.emp_no = s.emp_no
GROUP BY
    e.emp_no, s.emp_no;


Psycopg2 Errors on SQL statement when trying to copy data from CSV file into PostgreSQL database I am not a developer or PostgreSQL DB admin, so this may be basic questions.
Logistics: Windows 10 server / pgAdmin 4 / Postgres 10 / Python 2.7.13

I'm using a python script to ingest external data, create a CSV file and copy that into Postgres 10. I keep getting the following error:
Psycopg2.ProgrammingError: syntax error at or near "VALUES"

I have a two part question - 1) I can not see the syntax error in the following sql statement

def insert_csv_data(sqlstmt):
with get_conn('pg') as db:
    cur = db.cursor()
    sqlcopy = "COPY irwin (fire_id,name,type,acres,date_time,state,county,admin_unit,land_cat,commander,perc_cntnd,cont_date,gacc,lat,long,geom,updated,imo) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,ST_SetSRID(ST_MakePoint(%s, %s),4326)%s,%s) FROM STIN DELIMITER ',' CSV HEADER"

    with open(csv_file, 'r') as f:
        #next(f)# Skipping the first line header row
        cur.copy_expert(sqlcopy, f, size=8000)
        db.commit()
        cur.close()

And 2) Once that is resolved I'm expecting to get an error about the geometry column in postgres. If someone would also peek at the code snippets and let me know if anything jumps out I would SO APPRECIATE IT!

This snippet pulls the external data in order but I don't think I've coded this correctly to pull the lat/long into the geom field.

            # Lat 15 - double
        if not attributes['InitialLatitude'] is None:
            lat = str(attributes['InitialLatitude']).replace('\n', '')
        else:
            lat = '0'

        #Long 16 - double
        if not attributes['InitialLongitude'] is None:
            long = str(attributes['InitialLongitude']).replace('\n', '')
        else:
            long = '0'

        # geom is not defined - script is dumping the geometry into the IMO field
        geom = str(attributes['InitialLatitude']) + ' ' + str(attributes['InitialLongitude'])

I added a Geom header to the csv data. Please help - thanks!

sqlcopy = "COPY irwin (fire_id,name,type,acres,date_time,state,county,admin_unit,land_cat,commander,perc_cntnd,cont_date,gacc,lat,long,geom,updated,imo)  FROM STDIN WITH FORMAT CSV, HEADER")

copy doesn't do values..., the columns in the input must be correctly formatted for the columns you list, also format CSV is usually a better choice than delimiter ','

what copy expert actually does is open a channel to the postgresql copy command. and then squirt the file contents through that channel.
so you need to format the query for what postgres wants.

https://www.postgresql.org/docs/10/sql-copy.html


Getting ERROR 1045 (28000): Access denied for user even with proper setup and on different connections I am getting this error:  ERROR 1045 (28000): Access denied for user 'ipcuser'@'10.0.0.26' (using password: YES)

The database is Percona MySQL 5.6 and is on the 10.0.0.201 IP

We have servers in the 10.0.0.x range.  Testing from different servers we get different results using the exact same command: mysql -u ipcuser -pPASSHERE -h 10.0.0.201 -D ipc --protocol=tcp
The issue came up over night when nothing has changed and no one was working.  No code changes at all.  No database changes.  The database was last restarted 4 days ago before attempting today.

The results of trying to connect from:

10.0.0.200 Connects
10.0.0.70 Connects
10.0.0.26 Access Denied
10.0.0.20 Access Denied
10.0.0.101 Access Denied

Output from: SELECT user, host FROM mysql.user;

ipcuser     10.0.0.%
ipcuser     10.0.0.101
ipcuser     localhost
ipcuser     127.0.0.1
root        10.0.0.201
root        127.0.0.1
root        ::1
root        localhost

Already tested:
 - Restarting MySQL
 - Adding in the specific IP of the server (that would be the 10.0.0.101 address in the above output, removing it has no change)
 - Restarting the network on both the DB and failed connections
 - Checking the error log.  Nothing.
 - Disabling firewalls.  None of the connecting servers nor the DB had iptables running.
 - Running FLUSH HOSTS
 - Changing the bind

Also for some extra weirdness, I was able to connect once from the 10.0.0.101 IP, but after issuing a query it disconnected me and on attempting to reconnect gave access denied.

After more testing, I shut the MySQL server down and the servers failed to connect were still getting a specific access denied error.  The ones that had successful connections got could not connect.

The problem has been fixed.  It was not due to anything MySQL.

We have multiple DB servers, another one was assigned the same IP (10.0.0.201) and neither server picked up on it when doing an ifup.  This would explain why some worked and others didn't, as well as when we got MySQL responses when it was supposedly down.


Force MATCH to return a certain number of results I am using MATCH in a query but sometimes the query returns no results. What can I do to force MATCH to return results, even if the results are not a match? I want the query to return four results. If the results are not a match then they should be random.

e.g. I want to get the two matches for the word 'Foo' plus two more random rows, making four results in total:

ID  TITLE
1   Carl
2   I am a title
3   Foo
4   Bar
5   Foobar
6   Title

The query should return something like:

ID  TITLE
3   Foo
5   Foobar
2   I am a title
6   Title

I tried:

SELECT
    *
FROM
    Entries
WHERE
    MATCH(Title) AGAINST("Foo")
UNION
    (
    SELECT
        *
    FROM
        Entries
    ORDER BY
        RAND()
    )
LIMIT 4

I feel like this query should work, and it does, except the results that are not a match are not random for some reason, they are the same results every time.

Full Text Indexes don-'t work that way.

They find whole Words and combinations of those
Use instead the normal comparison LIKE to find your rows

The ORDER BY and the LIMIT have to be in the (),because mysql interprets them as belonging to the complete query (SELECT UNION SELCT)

SELECT
    *
FROM
    Entries
WHERE
    `Title` LIKE ('foo%')
UNION
(SELECT
    *
FROM
    Entries
WHERE
    ID NOT IN (SELECT ID
               FROM
                 Entries
               WHERE
                `Title` LIKE ('foo%'))
ORDER By Rand()
LIMIT 4)
;

ID | TITLE
-: | :-----------
 3 | Foo
 5 | Foobar
 2 | I am a title
 4 | Bar
 1 | Carl
 6 | Title

SELECT
    *
FROM
    Entries
WHERE
    `Title` LIKE ('foo%')
UNION
(SELECT
    *
FROM
    Entries
WHERE
    ID NOT IN (SELECT ID
               FROM
                 Entries
               WHERE
                `Title` LIKE ('foo%'))
ORDER By Rand()
LIMIT 2)
;

ID | TITLE
-: | :-----
 3 | Foo
 5 | Foobar
 4 | Bar
 1 | Carl

db<>fiddle here


Removing duplicate rows and changing related foreign keys in PostgreSQL I have found all duplicate rows and now want to delete them after I update related foreign keys in other tables so they point to the correct row.

How do I get a list of foreign keys related to my table?

I have seen a number of old posts with heavy queries and wonder if there is an easier way nowadays?

Update: I have added an answer with a working query and would be happy if there is something more simple.

From http://www.postgresql.org/message-id/200811072134.44750.andreak@officenet.no

SELECT
  confrelid :: REGCLASS,
  af.attname AS fcol,
  conrelid :: REGCLASS,
  a.attname  AS col
FROM pg_attribute af, pg_attribute a,
  (SELECT
     conrelid,
     confrelid,
     conkey [i]  AS conkey,
     confkey [i] AS confkey
   FROM (SELECT
           conrelid,
           confrelid,
           conkey,
           confkey,
           generate_series(1, array_upper(conkey, 1)) AS i
         FROM pg_constraint
         WHERE contype = 'f') ss) ss2
WHERE af.attnum = confkey
  AND af.attrelid = confrelid
  AND a.attnum = conkey
  AND a.attrelid = conrelid
  AND confrelid :: REGCLASS = 'my_table' :: REGCLASS
  AND af.attname = 'my_referenced_column';

It produces

   confrelid   |         fcol         |   conrelid    |     col
---------------+----------------------+---------------+----------------
   my_table    | my_referenced_column | another_table | some_column

Update: RhodiumToad on freenode #postgres channel gave a simpler query which however needs some parsing and filtering if only specific column is needed.

SELECT
  conrelid :: REGCLASS,
  conname,
  pg_get_constraintdef(oid)
FROM pg_constraint
WHERE confrelid = 'my_table' :: REGCLASS;

producing

  conrelid  |       conname        |         pg_get_constraintdef
------------+----------------------+----------------------------------------------------------------------------------------------------
 some_table | some_constraint_name | FOREIGN KEY (some_column) REFERENCES my_table(my_referenced_column) DEFERRABLE INITIALLY DEFERRED


Print variable of same datatype I am looking for to print two variables  of same datatype I have something like this

declare @one int ,@two int
set @one=1
set @two=2
print @one+@two

obviously it will give a result 3 but
I need result 1 2

On search I got to convert the any one of these variable and come up this query.

print convert(varchar(10),@one)+@two

but the result was same 3!!
What should I do,to get the result?
NB :I am using sql server

You can use RAISERROR instead of PRINT. With a severity of 0 the two are equivalent but there is some primitive string formatting functionality available as below.

RAISERROR('%d %d',0,1,@one, @two)


Convert SQL Server .ldf + .mdf into MySQL scripts I recently took a SQL Server backup and extracted an .ldf and .mdf from it successfully. I'm curious if there is a trivial way to take both of these files/backups and convert them to a .sql that I can use to import into a MySQL database?

Nope.

Your best bet is to not do that.

Your next best bet is to restore the backup to a SQL Server installation and use SSIS to migrate your objects.

If you like pain and punishment ( you are going to MySQL from MSSQL, so I'm assuming "yes" ), you can use the generate scripts wizard on the restored DB and try to adjust for all of the syntax / feature differences.

An .mdf ( or .ndf ) is a data file, complete with compression options and the like.  There's pretty much not even a non-trivial way to turn the storage file into scripts, much less for an entirely different engine.


What's the use of assigning foreign keys on MySQL Aside from indexing the value of the foreign key, what is/are the advantage of assigning foreign keys to databases? I've seen a lot of databases that does not assign foreign keys, but is relational by their schema. One example is the database of the wiki (mediawiki). I've studied their schema and although their tables are interconnected using the primary key of one table, they aren't assigning any foreign keys.

A foreign key is a type of constraint.  The purpose of constraints is to make sure that data in tables follows certain rules.

You use foreign key constraints to ensure that child data has parent data.  You can use it to either prevent orphaning child records or to facilitate cascade deletes or updates.


How to display all the dates between multiple two dates in a table? I want to display all the dates between two dates for a particular record

And this is the table :

ID Start_Date  End_Date
-------------------------
1  2013-01-14  2013-01-18
2  2013-02-01  2013-02-04

and now i want to get all the dates between from date and to date.

Expected output

ID Date
-------------
1  2013-01-14
1  2013-01-15
1  2013-01-16
1  2013-01-17
1  2013-01-18
2  2013-02-01
2  2013-02-02
2  2013-02-03
2  2013-02-04

guide me to write query for that with out creating any extra tables.

I have already tried this following query

select * from
(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date from
 (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where selected_date between '2012-02-10' and '2012-02-15'

its working fine for single record. but i want get all the date intervals from my table

Updated

i have 6 qty of chairs for all days. So one user book 3 chars on 2013-01-14 to 2013-01-18 another person book 2 chars on 2013-01-17 to 2013-01-20. So my expected output shown below.

ID Date        Available
------------------------
1  2013-01-14          3
1  2013-01-15          3
1  2013-01-16          3
1  2013-01-17          5
1  2013-01-18          5
1  2013-01-19          2
1  2013-01-20          2
1  2013-01-21          2

The easiest way is to have a calendar table, defined in the following way:

CREATE TABLE calendar
(
    a_day date PRIMARY KEY
) ;

... and filled with all the relevant dates (i.e.: all days from 1990-1-1 to 2100-12-31). For the sake of simplicity, we will fill it only with year 2013:

INSERT INTO
     calendar (a_day)
VALUES
    ('2013-01-01'),
    ('2013-01-02'),
    ('2013-01-03'),
    ('2013-01-04'),
    ('2013-01-05'),
    -- everything up to
    ('2013-12-31') ;

At this point, you can just have a JOIN with the two tables; with the join condition not being an equality, but a range condition:

SELECT
     t.id, c.a_day
FROM
     t
     JOIN calendar c ON c.a_day BETWEEN t.start_date AND t.end_date
ORDER BY
     t.id, c.a_day ;

... and get

id | a_day
-: | :---------
 1 | 2013-01-14
 1 | 2013-01-15
 1 | 2013-01-16
 1 | 2013-01-17
 1 | 2013-01-18
 2 | 2013-02-01
 2 | 2013-02-02
 2 | 2013-02-03
 2 | 2013-02-04

You can see all the setup at dbfiddle here


How to find port using Get-DbaTcpPort How to find whether the port used by SQL Server is static or dynamic using Get-DbaTcpPort dbatools command?

Below command doesn't show whether it's static/dynamic:

Get-DbaTcpPort -sqlinstance TEST

And below one shows too many rows:

 Get-DbaTcpPort -sqlinstance TEST -ALL

As you already mentioned below command should work

 Get-DbaTcpPort -SqlInstance ComputerName\InstanceName -All

  -All

  If this switch is enabled, an object with server name, IPAddress (ipv4
  and ipv6), port and static ($true/$false) for one or more SQL
  Servers is returned.

Documentation on Get-DbaTcpPort -All switch

This can be validated on the github code as well.

If you want specific port info for the connections using ipv4 then you could run:

$ipV4 = Test-Connection -ComputerName ComputerName -Count 1  | Select IPV4Address
Get-DbaTcpPort -SqlInstance ComputerName\InstanceName -All |  Where-Object {( $_.IPAddress -eq $ipV4.IPV4Address )}

If you then want specific columns such as port number and if it is static, you could  add select-object port, Static

$ipV4 = Test-Connection -ComputerName ComputerName -Count 1  | Select IPV4Address
Get-DbaTcpPort -SqlInstance ComputerName\InstanceName -All |  Where-Object {( $_.IPAddress -eq $ipV4.IPV4Address )}  | select-object port, Static

If you want specific port info for the connections using ipv6

$ipV6 = Test-Connection -ComputerName ComputerName -Count 1  | Select IPV6Address
Get-DbaTcpPort -SqlInstance ComputerName\InstanceName -All |  Where-Object {( $_.IPAddress -eq $ipV6.IPV6Address )}

If you are on  an older version of dbatools
 then the -detailed flag is used instead of

-All


SQL Server 2012 Always On, during a massive DB change As the topic says, I was wondering if you guys have any advice on potential issues with Always On availability during a massive update?

We're in need of making several massive datamodel changes, removing & adding tables and moving around large amounts of data (some 300+ GB), including creating and rebuilding hundreds of indexes and statistics in a procedure that will take well over 24 hours in total, and schedule-wise can not take more than 30 hours before bringing the DB's back online. And I'm not sure whether the always on availability mode's synchronization is the best option to replicate these changes to the other availability groups. Would it be better to just backup the changes once they're done, restore that to the other groups and re-enable synchronization from there?

Like I said, I don't really have experience working with it, so this is an open question.  And apologies for any misunderstandings or misconceptions here, again since my experience is limited. Please correct me where appropriate. :)

Thanks!

If the database has a long scheduled maintenance you don't need high availability or do you?
Keep in mind that every change is written to the transaction log and than transmitted to the secondaries. I wouldn't use the full recovery model for such a maintenance. If you don't do such a thing each week/month, I would turn off HADR for these databases, change the recovery model and set up the AG afterwards again. Transfering the complete backup after the changes is a lot faster than transfering each change. And the primary server has less work to do.


How to store number of years, months and days in PostgreSQL? How to store number of years, months and days in PostgreSQL ?

Example: the life time of bulb is => 3 years, 4 months, 23 days

Is there any data type in PostgreSQL

You could use the interval data type here you can find a guide to start with: http://www.postgresqltutorial.com/postgresql-interval/

CREATE TABLE components
(
  id SERIAL PRIMARY KEY,
  name CHARACTER VARYING(30),
  lifetime INTERVAL
)
INSERT INTO components(id, name, lifetime) VALUES(1,'bulb','3 years 4 months 23 days 3 hours 2 minutes 1 second');


Random and constant "Log Growths" Alerting, how to make it stop? I've set up alerting on all my database servers and every once in awhile I'll get the following error, sometimes it goes on and on and on, other times it'll be a few times here and there.

The SQL Server performance counter 'Log Growths' (instance '_Total') of object 'SQLServer:Databases' now equals the threshold of 1.00.

Are there any configurations that I can change to stop this?  It only appears to be coming from two servers.

This is telling you that a or several transaction logs are growing repeatedly, which means it is set to FULL or BULK LOGGED recovery mode with no or insufficient transaction log backups, or someone has written a really bad or long query that is keeping the transaction log active.

You should look for all FULL recovery model databases on that server and when was the last time a transaction log backup took place first, as that's the most dangerous.  The tlog could grow to where it takes up all the disk stopping all writes to that DB or any other DB with data or logs on that disk assuming there isn't a max size set.  If there isn't, moving fwd, you'll probably want to set a max size so 1 DB doesn't take down all DBs tlogs.

SELECT name, recovery_model_desc
FROM sys.databases

See any that are full?  If so, when was the last transaction log backup? Here is a script to help you on that:

SELECT   d.name,
         d.recovery_model_desc,
         MAX(b.backup_finish_date) AS backup_finish_date
FROM     master.sys.databases d
         LEFT OUTER JOIN msdb..backupset b
         ON       b.database_name = d.name
         AND      b.type          = 'L'
GROUP BY d.name, d.recovery_model_desc
ORDER BY backup_finish_date DES

If that doesn't give you the info you need here is a way to find the longest running top 10 transactions from msdn:

SELECT DISTINCT TOP 10
t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / 1000 / NULLIF(s.execution_count, 0), 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / 1000 / NULLIF(DATEDIFF(s, s.creation_time, GETDATE()), 0), 0) AS FrequencyPerSec
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
ORDER BY s.max_elapsed_time DESC, ExecutionCount DESC
GO

Finally once you see which DB has growing log files you can see what is holding the log file open.  It might even be something like replication setup or other HA solutions that weren't fully removed.  This will tell you:

SELECT [log_reuse_wait_desc]
    FROM [master].[sys].[databases]
--  WHERE [name] = N'DBNAME';
GO


This should do the trick for you:

DELIMITER $$
DROP TRIGGER IF EXISTS `employee_INSERT` $$
CREATE TRIGGER `employee_INSERT`
AFTER INSERT ON `employee`
FOR EACH ROW
BEGIN
    INSERT INTO employee_tools (Id, Tool)
    SELECT new.Id, tools.Tool_Name
            FROM tools
            WHERE tools.Division = new.Division;
END $$
DELIMITER ;


Oracle: Viewing settings for DBMS_NETWORK_ACL_ADMIN ACL? How do I view the contents of a network ACL? For example, if I create this ACL, how can I view what settings have been applied to it?

  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
     acl => 'www.xml',
     description => 'WWW ACL',
     principal => 'SCOTT',
     is_grant => true,
     privilege => 'connect'
  );

In order to see also implicit privileges you can use this query:

SELECT PRINCIPAL, HOST, lower_port, upper_port, acl, 'connect' AS PRIVILEGE,
    DECODE(DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, PRINCIPAL, 'connect'), 1,'GRANTED', 0,'DENIED', NULL) PRIVILEGE_STATUS
FROM DBA_NETWORK_ACLS
    JOIN DBA_NETWORK_ACL_PRIVILEGES USING (ACL, ACLID)
UNION ALL
SELECT PRINCIPAL, HOST, NULL lower_port, NULL upper_port, acl, 'resolve' AS PRIVILEGE,
    DECODE(DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, PRINCIPAL, 'resolve'), 1,'GRANTED', 0,'DENIED', NULL) PRIVILEGE_STATUS
FROM DBA_NETWORK_ACLS
    JOIN DBA_NETWORK_ACL_PRIVILEGES USING (ACL, ACLID);


SQL Server: No Guid of a database I built a powershell script for collecting database information with the help of the class "Microsoft.SqlServer.Management.Smo.Server". My script
run into an error because one database has no DatabaseGuid. How could that happen?
I thought every database has a database guid?!
This database is in state "offline". I set an other database to state "offline" but the database has still a guid.

I thought each database reaceives a persistent guid, or?!

Thanks

From this post: Obtaining a unique database identifier for SQL Server 2005 and later
 it seems that

  probably SMO reads the database_guid value from
  sys.database_recovery_status:

  Used to relate all the database files of a database together. All
  files must have this GUID in their header page for the database to
  start as expected. Only one database should ever have this GUID, but
  duplicates can be created by copying and attaching databases. RESTORE
  always generates a new GUID when you restore a database that does not
  yet exist.

And if you open the documentation sys.database_recovery_status (Transact-SQL) you'll find that

  NULL= Database is offline, or the database will not start.

I tried to put some my databases offline and to query database_guid from  sys.database_recovery_status and it is NULL while it's not NULL when bring these databases online again.


Recovering credential data from a master DB backup On a SQL Server 2008 R2 instance, a misconfigured credential used as a SSIS proxy for an SSA job was dropped before I could look at the system views to figure out when it was originally misconfigured.

I restored a backup of the master DB under an alternate user DB name to see if I can extract the data but it appears the sys.credentials view is looking at the live master DB instead of the restored DB even with the DB context set to the restored DB.

Is there an alternate method to retrieve the data from this and other system views from a backup without the usual DR methodology?

I don't have a restored master DB to try this, but running the statement of the view definition (with the references to master. changed to your user db name) should be a step in the right direction.
You can get the statement by running
sp_helptext [sys.credentials]


To solve this problem I did the following:
"Easy" explanation:
For this part I slightly added to the table definition provided by the OP. I'm firmly of the belief that DDL should be used to the maximum extent possible to "guide" the whole database programming process and could be much more powerful - an example of this would be SQL in CHECK constraints - so far only provided by Firebird (example here) and H2 (see reference here).
However, that's all very well and good, but we have to deal with PostgreSQL's 9.6 capabilities - the OP's version. My adjusted DDL for the "simple" explanation (see the entire fiddle here):
CREATE TABLE sessions
(
        id serial NOT NULL PRIMARY KEY,
        ctn INT NOT NULL,
        f_day DATE NOT NULL,
        f_time TIME(0) NOT NULL,
        l_time TIME(0) NOT  NULL,
        CONSTRAINT ft_less_than_lt_ck CHECK (f_time < l_time),
        CONSTRAINT ctn_f_day_f_time_uq UNIQUE (ctn, f_day, f_time),
        CONSTRAINT ctn_f_day_l_time_uq UNIQUE (ctn, f_day, l_time)
        -- could put in a DISTINCT somewhere if you don't have these constraints
        -- maybe has TIME(2) - but see complex solution
);

Indexes:
CREATE INDEX ctn_ix ON sessions USING BTREE (ctn ASC);
CREATE INDEX f_day_ix ON sessions USING BTREE (f_day ASC);
CREATE INDEX f_time_ix ON sessions USING BTREE (f_time ASC);

Just a point to note: do not use SQL keywords as table or column names - day is such a keyword! It can be confusing for debugging &c - it's simply not good practice. I've changed your original fieldname of day to f_day - note all lower and python case! Whatever you do, have a standard method of naming variables and stick to it - there are many coding standards documents out there.
The change to 'f_day' has no effect on the rest of the SQL as we don't take account of sessions which span midnight. Taking account of these can be done relatively easily by doing to the following (see fiddle).
SELECT (f_day + f_time)::TIMESTAMP FROM sessions;

Now the advent of GENERATED columns, you don't even have to worry about this - just have a GENERATED field as above!
If a constraint to the second is unworkable - logins at the same time, you could possibly use TIME(2) (or 3..6) for ensuring uniqueness. If [you don't want | can't have] UNIQUE constraints, you could put in DISTINCT in your SQL for identical login and logout times - though this is unlikely.
The fact remains that some simple DDL like this enormously simplifies your subsequent SQL (see discussion at the end of the "complex" explanation below).
You might also want to put ctn and/or day in your DDL UNIQUE constraints as shown? I've also added what I think may be good indexes! You might also want to investigate the OVERLAPS operator?
As for the sample data, I also added a few records for testing my solution as follows:
INSERT INTO sessions (id, ctn, day, f_time, l_time)
VALUES
( 1, 707, '2019-06-18', '10:48:25', '10:56:17'),
( 2, 707, '2019-06-18', '10:53:17', '11:00:49'),
( 3, 707, '2019-06-18', '10:54:31', '10:59:43'),  -- record 3 is completely covered
                                                  -- by record 2

( 4, 707, '2019-06-18', '11:03:59', '11:10:39'),
( 5, 707, '2019-06-18', '11:04:41', '11:08:02'), -- GROUP 2 record 6 completely
                                                 -- covers record 7

( 6, 707, '2019-06-18', '11:11:04', '11:19:39'), -- GROUP 3

( 7, 707, '2019-06-18', '12:15:15', '13:13:13'),
( 8, 707, '2019-06-18', '13:04:41', '13:20:02'),
( 9, 707, '2019-06-18', '13:17:17', '13:22:22'), -- GROUP 4

(10, 707, '2019-06-18', '14:05:17', '14:14:14'); -- GROUP 5

I'll go through my logic step by step - good for you perhaps, but also for me as it helps me to clarify my thinking and will ensure that the lessons which I have learnt from this exercise will stay with me - "I hear and I forget. I see and I remember. I do and I understand." - Confucius.
All of the following is included in the fiddle.
/**

So, the desired result is:

Interval 1 - start: 10:48:25 - end 11:00:49
Interval 2 - start: 11:03:59 - end 11:10:39
Interval 3 - start: 11:11:04 - end 11:19:39
Interval 4 - start: 12:15:15 - end 13:22:22
Interval 5 - start: 14:05:17 - end 14:14:14

**/

The first step is to use the LAG function (documentation) as follows:
SELECT
  s.id AS id, s.ctn AS ctn, s.f_time AS ft, s.l_time AS lt,
  CASE
    WHEN LAG(s.l_time) OVER () > f_time THEN 0
    ELSE 1
  END AS ovl
FROM sessions s

Result:
id  ctn     ft  lt  ovl
1   707     10:48:25    10:56:17    1
2   707     10:53:17    11:00:49    0
3   707     10:54:31    10:59:43    0
4   707     11:03:59    11:10:39    1
5   707     11:04:41    11:08:02    0
6   707     11:11:04    11:19:39    1
7   707     12:15:15    13:13:13    1
8   707     13:04:41    13:20:02    0
9   707     13:17:17    13:22:22    0
10  707     14:05:17    14:14:14    1

So, whenever there's a new interval, there's a 1 in the ovl (overlap) column.
Next, we take the cumulative SUM of those 1's as follows:
SELECT
  t1.id, t1.ctn, t1.ft, t1.lt, t1.ovl,
  SUM(ovl) OVER (ORDER BY t1.ft ASC ROWS BETWEEN UNBOUNDED PRECEDING
                                          AND CURRENT ROW) AS s
FROM
(
  SELECT
    s.id AS id, s.ctn AS ctn, s.f_time AS ft, s.l_time AS lt,
    CASE
      WHEN LAG(s.l_time) OVER () > f_time THEN 0
      ELSE 1
    END AS ovl
  FROM sessions s
) AS t1
ORDER BY lt, id

Result:
id  ctn     ft  lt  ovl     s
1   707     10:48:25    10:56:17    1   1
3   707     10:54:31    10:59:43    0   1
2   707     10:53:17    11:00:49    0   1
5   707     11:04:41    11:08:02    0   2
4   707     11:03:59    11:10:39    1   2
6   707     11:11:04    11:19:39    1   3
7   707     12:15:15    13:13:13    1   4
8   707     13:04:41    13:20:02    0   4
9   707     13:17:17    13:22:22    0   4
10  707     14:05:17    14:14:14    1   5

So, we have now "split out", and have a way of distinguishing between, our intervals - each interval has a different value of s - 1..5.
So, now we want to get the lowest value of f_time and the highest value of l_time for these intervals. My first attempt using MAX() and MIN() went as follows:
SELECT
  ROW_NUMBER() OVER (PARTITION BY s) AS rn,
  MIN(ft) OVER (PARTITION BY s ORDER BY ft, lt) AS min_f,
  MAX(lt) OVER (PARTITION BY s ORDER BY ft, lt) AS max_l,
  s
FROM
(
  SELECT
    t1.id, t1.ctn, t1.ft, t1.lt, t1.ovl,
    SUM(ovl) OVER (ORDER BY t1.ft ASC ROWS BETWEEN UNBOUNDED PRECEDING
                                           AND CURRENT ROW) AS s
  FROM
  (
    SELECT
      s.id AS id, s.ctn AS ctn, s.f_time AS ft, s.l_time AS lt,
      CASE
        WHEN LAG(s.l_time) OVER () > f_time THEN 0
        ELSE 1
      END AS ovl
    FROM sessions s
  ) AS t1;
  ORDER BY id, lt
)AS t2
ORDER BY s, rn ASC, min_f;

Result:
rn  min_f   max_l   s
1   10:48:25    10:56:17    1
2   10:48:25    11:00:49    1
3   10:48:25    11:00:49    1
1   11:03:59    11:10:39    2
2   11:03:59    11:10:39    2
1   11:11:04    11:19:39    3
1   12:15:15    13:13:13    4
2   12:15:15    13:20:02    4
3   12:15:15    13:22:22    4
1   14:05:17    14:14:14    5

Note how we have to get rn = 3 for the first interval, rn = 3 for the fourth and different values of rn for different intervals - if there were 7 sub-intervals making up one interval, then we would have to retrieve rn = 7 - this stumped me for a while!
Then the power of Window functions came to the rescue - if you sort the MAX() and MIN() differently, the correct result drops into our lap:
SELECT
  ROW_NUMBER() OVER (PARTITION BY s) AS rn,
  MIN(ft) OVER (PARTITION BY s ORDER BY ft, lt DESC) AS min_f,
  MAX(lt) OVER (PARTITION BY s ORDER BY ft DESC, lt) AS max_l,
  s
FROM
(
  SELECT
    t1.id, t1.ctn, t1.ft, t1.lt, t1.ovl,
    SUM(ovl) OVER (ORDER BY t1.ft ASC ROWS BETWEEN UNBOUNDED PRECEDING
                                           AND CURRENT ROW) AS s
  FROM
  (
    SELECT
      s.id AS id, s.ctn AS ctn, s.f_time AS ft, s.l_time AS lt,
      CASE
        WHEN LAG(s.l_time) OVER () > f_time THEN 0
        ELSE 1
      END AS ovl
    FROM sessions s
  ) AS t1
  ORDER BY id, lt
)AS t2
ORDER BY s, rn ASC, min_f;

Result:
rn  min_f   max_l   s
1   10:48:25    11:00:49    1
2   10:48:25    11:00:49    1
3   10:48:25    10:59:43    1
1   11:03:59    11:10:39    2
2   11:03:59    11:08:02    2
1   11:11:04    11:19:39    3
1   12:15:15    13:22:22    4
2   12:15:15    13:22:22    4
3   12:15:15    13:22:22    4
1   14:05:17    14:14:14    5

Note that now, rn = 1 is always our desired record - this is the result of:
  MIN(ft) OVER (PARTITION BY s ORDER BY ft, lt DESC) AS min_f,
  MAX(lt) OVER (PARTITION BY s ORDER BY ft DESC, lt) AS max_l,

Note that for MIN(), the sort is by lt DESC and for the MAX() (partitioned by interval - i.e. s) it is by ft DESC. This matches up the smallest ft with the largest lt which is what we want.
This is essentially our desired result - just add a bit of tidying up and formatting as per the OP's requirements and we're good to go. This part also demonstrates the use of another very useful Window function - ROW_NUMBER().
SELECT
  ROW_NUMBER() OVER () AS "Interval No.",
  ' Start time: ' AS " ",
  t3.min_f AS "Interval start" ,
  ' End time: ' AS " ",
  t3.max_l AS "Interval stop",
  ' Duration: ' AS " ",
  (t3.max_l - t3.min_f) AS "Duration"
FROM
(
  SELECT
    ROW_NUMBER() OVER (PARTITION BY s) AS rn,
    MIN(ft) OVER (PARTITION BY s ORDER BY ft, lt DESC) AS min_f,
    MAX(lt) OVER (PARTITION BY s ORDER BY ft DESC, lt) AS max_l,
    s
  FROM
  (
    SELECT
      t1.id, t1.ctn, t1.ft, t1.lt, t1.ovl,
      SUM(ovl) OVER (ORDER BY t1.ft ASC ROWS BETWEEN UNBOUNDED PRECEDING
                                             AND CURRENT ROW) AS s
    FROM
    (
      SELECT
        s.id AS id, s.ctn AS ctn, s.f_time AS ft, s.l_time AS lt,
        CASE
          WHEN LAG(s.l_time) OVER () > f_time THEN 0
          ELSE 1
        END AS ovl
      FROM sessions s
    ) AS t1
    ORDER BY id, lt
  )AS t2
  ORDER BY s, rn ASC, min_f
) AS t3
WHERE t3.rn = 1;

Final result:
Interval No.        Interval start      Interval stop       Duration
1    Start time:    10:48:25     End time:  11:00:49     Duration:  00:12:24
2    Start time:    11:03:59     End time:  11:10:39     Duration:  00:06:40
3    Start time:    11:11:04     End time:  11:19:39     Duration:  00:08:35
4    Start time:    12:15:15     End time:  13:22:22     Duration:  01:07:07
5    Start time:    14:05:17     End time:  14:14:14     Duration:  00:08:57

I can make no guarantees about the performance of this query if there is a large number of records, see the result of EXPLAIN (ANALYZE, BUFFERS) at the end of the fiddle. However, I'm assuming that since it's in a report style format, it might be for a given value of ctn and/or day - i.e. not too many records?
"Complex" explanation:
I won't show every step - after eliminating the duplicate f_times and l_times, the steps are identical.
Here, the table definition and data are slightly different (fiddle available here):
CREATE TABLE sessions
(
        id serial NOT NULL PRIMARY KEY,
        ctn INT NOT NULL,
        f_day DATE NOT NULL,
        f_time TIME(0) NOT NULL,
        l_time TIME(0) NOT  NULL,
        CONSTRAINT ft_lt_lt CHECK (f_time < l_time),
        -- CONSTRAINT ft_uq UNIQUE (f_time),
        -- CONSTRAINT lt_uq UNIQUE (l_time)
        CONSTRAINT ft_lt_uq UNIQUE(f_time, l_time)
        -- could put in a DISTINCT somewhere to counter this possibility or
        -- maybe have TIME(2) to ensure no duplicates?
);

The only constraints that I've kept are CHECK (f_time < l_time) (couldn't be any other way) and UNIQUE f_time, l_time (maybe add day and/or ctn to that - the advice about TIME(2) or (3...6) above also applies.
I'll leave it up to the reader to apply UNIQUE to combinations of ctn and f_day as applicable!
INSERT INTO sessions (id, ctn, day, f_time, l_time)
VALUES
( 1, 707, '2019-06-18', '10:48:25', '10:56:17'), -- note - same l_times
( 2, 707, '2019-06-18', '10:48:33', '10:56:17'), -- need one with lowest f_time
( 3, 707, '2019-06-18', '10:53:17', '11:00:49'),
( 4, 707, '2019-06-18', '10:54:31', '10:59:43'), -- note - same f_times
                                                 -- need one with greatest l_time
( 5, 707, '2019-06-18', '10:54:31', '10:57:37'), -- GROUP 1

( 6, 707, '2019-06-18', '11:03:59', '11:10:39'),
( 7, 707, '2019-06-18', '11:04:41', '11:08:02'), -- GROUP 2, record 6 completely
                                                 -- covers record 7
( 8, 707, '2019-06-18', '11:11:04', '11:19:39'), -- GROUP 3

( 9, 707, '2019-06-18', '12:15:15', '13:13:13'),
(10, 707, '2019-06-18', '13:04:41', '13:20:02'),
(11, 707, '2019-06-18', '13:17:17', '13:22:22'), -- GROUP 4

(12, 707, '2019-06-18', '14:05:17', '14:14:14'); -- GROUP 5

I've added a couple of potentially "troublesome" records (2 & 4) with the same f_time and l_time within the same interval. So, in the event of an identical f_time, we want the sub-interval with the greatest l_time and vice-versa for the case of an identical l_time (i.e. smallest f_time).
So, what I did in this case was to eliminate duplicates by chaining CTE's (aka the WITH clause) as follows:
WITH cte1 AS
(
  SELECT s.*, t.mt, t.lt
  FROM sessions s
  JOIN
  (
    SELECT
      DISTINCT
      ctn,
      MIN(f_time) AS mt,
      l_time AS lt
    FROM sessions
    GROUP BY ctn, l_time
    ORDER BY l_time
  ) AS t
  ON (s.ctn, s.f_time, s.l_time) = (t.ctn, t.mt, t.lt)
  ORDER BY s.l_time
),
cte2 AS
(
  SELECT
    DISTINCT
    ctn,
    f_time AS ft,
    MAX(lt) AS lt
  FROM cte1
  GROUP BY ctn, f_time
  ORDER BY f_time
)
SELECT * FROM cte2
ORDER BY ft;

Result:
ctn     ft  lt
707     10:48:25    10:56:17
707     10:53:17    11:00:49
707     10:54:31    10:59:43
707     11:03:59    11:10:39
707     11:04:41    11:08:02
707     11:11:04    11:19:39
707     12:15:15    13:13:13
707     13:04:41    13:20:02
707     13:17:17    13:22:22
707     14:05:17    14:14:14

And then I treat cte2 as the starting point for the process in the "easy" explanation.
The final SQL is as follows:
WITH cte1 AS
(
  SELECT s.*, t.mt, t.lt
  FROM sessions s
  JOIN
  (
    SELECT
      DISTINCT
      ctn,
      MIN(f_time) AS mt,
      l_time AS lt
    FROM sessions
    GROUP BY ctn, l_time
    ORDER BY l_time
  ) AS t
  ON (s.ctn, s.f_time, s.l_time) = (t.ctn, t.mt, t.lt)
  ORDER BY s.l_time
),
cte2 AS
(
  SELECT
    DISTINCT
    ctn,
    f_time AS ft,
    MAX(lt) AS lt
  FROM cte1
  GROUP BY ctn, f_time
  ORDER BY f_time
)
SELECT
  ROW_NUMBER() OVER () AS "Interval No.",
  ' Start time: ' AS " ",
  t3.min_f AS "Interval start" ,
  ' End time: ' AS " ",
  t3.max_l AS "Interval stop",
  ' Duration: ' AS " ",
  (t3.max_l - t3.min_f) AS "Duration"
FROM
(
  SELECT
    ROW_NUMBER() OVER (PARTITION BY s) AS rn,
    MIN(ft) OVER (PARTITION BY s ORDER BY ft, lt DESC) AS min_f,
    MAX(lt) OVER (PARTITION BY s ORDER BY ft DESC, lt) AS max_l,
    s
  FROM
  (
    SELECT
    t1.ctn, t1.ft, t1.lt, t1.ovl,
    SUM(ovl) OVER (ORDER BY t1.ft ASC ROWS BETWEEN UNBOUNDED PRECEDING
                                           AND CURRENT ROW) AS s
    FROM
    (
      SELECT
        c.ctn AS ctn, c.ft AS ft, c.lt AS lt,
        CASE
          WHEN LAG(c.lt) OVER () > ft THEN 0
          ELSE 1
        END AS ovl
      FROM cte2 c
    ) AS t1
    ORDER BY t1.lt
  ) AS t2
  ORDER BY s, rn ASC, min_f
) AS t3
WHERE t3.rn = 1
ORDER BY t3.rn;

Result:
Interval No.        Interval start      Interval stop       Duration
1    Start time:    10:48:25     End time:  11:00:49     Duration:  00:12:24
2    Start time:    11:03:59     End time:  11:08:02     Duration:  00:04:03
3    Start time:    11:11:04     End time:  11:19:39     Duration:  00:08:35
4    Start time:    12:15:15     End time:  13:22:22     Duration:  01:07:07
5    Start time:    14:05:17     End time:  14:14:14     Duration:  00:08:57

As you can see, it's pretty hairy stuff - not having the UNIQUE constraints in the DDL has doubled the length of the SQL and the time taken for the planning and executions stages, and made it pretty horrible into the bargain.
See the end of the fiddle for the plans for both queries! Lessons to be learnt there! As a rule of thumb, the longer the plan, the slower the query!
I'm not sure that the indexes can play any role here since we're selecting from the entire table and it's very small! If we were filtering a large table by ctn and/or f_day and/or f_time, I'm pretty sure that we would start to see differences in the plans (and timings!) if there were no indexes!


How to get all non deleted messages from one user I have two tables one for messages called default_messages and the other for recipients called default_recipient. This is how SQL for those table looks like:

CREATE TABLE IF NOT EXISTS `default_messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `subject` varchar(150) COLLATE utf8_unicode_ci NOT NULL,
  `message` text COLLATE utf8_unicode_ci NOT NULL,
  `sender_user_id` int(11) NOT NULL,
  `reply_to_message_id` int(11) NOT NULL,
  `thread_root_message_id` int(11) NOT NULL,
  `date` datetime NOT NULL,
  `deleted` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5 ;

And this is the DATA for this table:

INSERT INTO `default_messages` (`id`, `subject`, `message`, `sender_user_id`, `reply_to_message_id`, `thread_root_message_id`, `date`, `deleted`) VALUES
(1, 'asdasdadasdasdasdasd', 'sdasdasdad', 1, 0, 0, '2012-05-11 09:29:00', 1),
(2, 'sadasdasd', 'asdasd', 2, 0, 0, '2012-05-11 09:44:23', 0),
(3, 'Re: asdasdadasdasdasdasd', 'asdadadadadadad', 2, 1, 0, '2012-05-11 09:44:41', 1),
(4, 'Re: sadasdasd', 'asdadad respuesta', 1, 2, 0, '2012-05-11 09:46:22', 1);

Now for the second table this is the SQL:

CREATE TABLE IF NOT EXISTS `default_recipient` (
  `message_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `read` int(11) NOT NULL DEFAULT '0',
  `recipient_read_date` datetime DEFAULT NULL,
  `deleted` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

And the DATA is this one:

INSERT INTO `default_recipient` (`message_id`, `user_id`, `read`, `recipient_read_date`, `deleted`) VALUES
 (1, 2, 1, NULL, 0),
 (2, 1, 1, NULL, 1),
 (3, 1, 1, NULL, 1),
 (4, 2, 1, NULL, 1);

Now I have this query for get all messages for user_id=x where deleted columns in both tables default_recipient and default_messages is not equal to 1 but it's not working because I get two results instead of one when user_id at table recipient or sender_user_id at table messages takes value equal 2:

SELECT p.*, rcp.*, msg.*, msg.id as message_id FROM default_messages msg
        LEFT JOIN default_recipient rcp ON (msg.id = rcp.message_id)
        LEFT JOIN default_profiles p ON (p.user_id = msg.sender_user_id)
        WHERE msg.sender_user_id = 2 OR rcp.user_id = 2
        AND msg.deleted = 0 AND rcp.deleted = 0
        ORDER BY msg.date DESC

What I'm doing wrong?

EDIT: The right question here was: how to get all messages for one user "X" let says where rcp.deleted = 0 OR msg.deleted = 0

AND has higher precedence than OR. Your condition is parsed and executed as:

    WHERE (msg.sender_user_id = 2)
       OR ( rcp.user_id = 2
        AND msg.deleted = 0
        AND rcp.deleted = 0
          )

Perhaps you mean to write:

    WHERE (msg.sender_user_id = 2 OR rcp.user_id = 2)
      AND msg.deleted = 0
      AND rcp.deleted = 0


My MySQL server time is not the same as my Server So a month ago I had purchased a server from a hosting company.

The info are;

Linux Centos (latest version).

I installed ZPanel which installs phpMyAdmin, MySQL and all the other good stuff. Anyway, I have managed to change my SERVER time to my local time which is GMT. However when I try to set it on my MySQL server it shows the right time, but when I use the

now()

function in PHP, the time stores +1 hour ahead of my current time, which pretty much messes up my system.

Does anyone know how to fix this? Please It's been a few weeks, yet nothing to fix it :/

Thank you.

I had this exact same problem after I had updated my server time. I just had to restart the MySql server then it reset the time to match my server time.

/etc/init.d/mysqld restart

Reference - https://major.io/2007/07/01/mysql-time-zone-different-from-system-time-zone/


Why are these T-SQL jobs from different SQL Server instances executed on the same instance (AlwaysOn Availability Groups) Recently, our blocked processes dashboard has been reporting blocked processes around the time when we do our statistic update.

The reason was quickly found: an update statistics job step (T-SQL) that is starting on both the secondary and the primary SQL Server instance.
The job updates several statistics on the same database, that is part of an AlwaysOn Availability Group. I would expect this to fail on the secondary instance.

A quick rundown of the failover history:

Server A, which should stay active due to licensing (will be named Active Server), failed over unexpectedly to Server B (Passive Server) on 20/02 at 9PM.

After the unplanned failover, we did another (but this time planned) manual failover back to the Active Server on 21/02 at 12PM.

Job history

Before the first failover all was good, and the active server is the only one running the job.

One job running.
We see the stat updates running on the active side. (which is primary replica at the time)

During the short time that the passive server was the primary replica, we don't have any monitoring and the job history was cleared.

After the failover, back to the 'normal' state, after being on primary on the passive node for less than 24 hours, the job step on the passive instance has also been starting and running on the active instance.

(I killed the sessions).

Now the interesting part to me, is that both jobs are running on the active server, seeming like the job is using the listener to get to the database. But it could be an entirely different reason.

There is a copy job PowerShell task running nightly at 01 AM (dbatools):

powershell.exe Copy-DbaAgentJob -ExcludeJob "CopyJobs,CopyLogins" -Source INDCSPSQLA01  -Destination  INDCSPSQLP01     -Force

My guess is now aimed at the one time, the job copy happened from the active, secondary node --> primary passive node with -Force. This happened at 21/02 01 AM.

The Question

Why is the job step on the passive instance executing on the active instance's database?

Checklist

On both instances, the job target is local:

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

The servername is correct

select name from sys.servers
select @@SERVERNAME

both return the passive server.

The job IDs of active and passive are different:

--08C63F07-0853-41DA-BC88-8FDF44AE491F -- passive
--E8C88965-C581-4E06-B651-CC10637FCEEF -- active

Both jobs use the database in question in their step:

@database_name=N'DB1',

--> Should not be accessible on the passive instance, resulting in failure.
No readable secondaries

The database is not accessible on the passive instance:

Version of both servers: 14.0.3030.27

T-SQL Job step command example

 @subsystem=N'TSQL',
        @command=N'update statistics dbo.table with fullscan ...'

Nothing is running on the passive instance when the job starts.

EDIT:

Restarting the agent on the passive node 'fixes' this, resulting in a new error on executing:

  Unable to connect to SQL Server 'INDCSPSQLP01'.  The step failed.

As a result, it no longer updates statistics on the primary instance

Hostnames = Passive & Active server, jobs where visibly running.

Application info

SQLAgent - TSQL JobStep (Job 0x9D358B2EF6C53C4BAD6A61CA87D51BF5 : Step 1)
SQLAgent - TSQL JobStep (Job 0x6589C8E881C5064EB651CC10637FCEEF : Step 1)

I don't have a diagnosis for why this problem occurred, but if you have jobs running on databases that are in availability groups, it's best to include a check in step 1 that uses the fn_hadr_is_primary_replica function to check whether it is running on the primary or secondary.

IF (sys.fn_hadr_is_primary_replica('DB1') <> 1)
  BEGIN
    RAISERROR('%s is secondary', 11, 1, @@servername );
  END

Configure this step to quit the job on failure.  This is better than trying to run something that fails because it's hitting a secondary.