What index can I create to help a query using multiple datetime columns and datetime functions in its where clause? SQL Server 2016

I have the following query:

SELECT   [Id],
         [Descriptor]
FROM     [Networks]
WHERE    (   [ValidFrom] <= SYSDATETIMEOFFSET()
         AND SYSDATETIMEOFFSET() < COALESCE ([ValidUntil], DATEADD (DAY, 1, SYSDATETIMEOFFSET()))
         )
     AND (   [DataEffective] <= SYSDATETIMEOFFSET()
         AND SYSDATETIMEOFFSET() < COALESCE ([DataExpired], DATEADD (DAY, 1, SYSDATETIMEOFFSET()))
         )

I'm getting a table scan every time I run this query. I need an index on this table because this query will be run a lot. I've tried several different ones but I can't seem to get the server to use any of them.

I've tried indexing:

ValidFrom
ValidFrom, DataEffective
ValidFrom, ValidUntil, DataEffective, DataExpired

I've also tested against both an empty table and a table seeded with 10202 rows.

I've also tested with a restructured query:

SELECT   [Id],
         [Descriptor]
FROM     [Networks]
WHERE    [ValidFrom] <= SYSDATETIMEOFFSET()
     AND [DataEffective] <= SYSDATETIMEOFFSET()
     AND SYSDATETIMEOFFSET() < COALESCE ([ValidUntil], DATEADD (DAY, 1, SYSDATETIMEOFFSET()))
     AND SYSDATETIMEOFFSET() < COALESCE ([DataExpired], DATEADD (DAY, 1, SYSDATETIMEOFFSET()))

and like this:

SELECT   [Id],
         [Descriptor]
FROM     [Networks]
WHERE    [ValidFrom] <= SYSDATETIMEOFFSET()
     AND [DataEffective] <= SYSDATETIMEOFFSET()
     AND (   [ValidUntil] > SYSDATETIMEOFFSET()
         OR  [ValidUntil] IS NULL
         )
     AND (   [DataExpired] > SYSDATETIMEOFFSET()
         OR  [DataExpired] IS NULL
         )

and like this (to remove the non-deterministic SYSDATETIMEOFFSET() reference):

DECLARE @Now DateTimeOffset(7)

SET @Now = SYSDATETIMEOFFSET()

SELECT   [Id],
         [Descriptor]
FROM     [Networks]
WHERE    [ValidFrom] <= @Now
     AND [DataEffective] <= @Now
     AND (   [ValidUntil] > @Now
         OR  [ValidUntil] IS NULL
         )
     AND (   [DataExpired] > @Now
         OR  [DataExpired] IS NULL
         )

Table Definition

CREATE TABLE [dbo].[Networks](
[ValidFrom] [datetimeoffset](7) NOT NULL,
[ValidUntil] [datetimeoffset](7) NULL,
[DataEffective] [datetimeoffset](7) NOT NULL,
[DataExpired] [datetimeoffset](7) NULL,
[UserId] [nvarchar](128) NOT NULL,
[Id] [uniqueidentifier] NOT NULL,
[Descriptor] [nvarchar](255) NULL )
ON [PRIMARY]
GO

I think you can benefit from rewriting this query first, plunking it into a TVF and adding a non-clustered index on ValidFrom and DateEffective.

create nonclustered index ix_networks_validfrom_dateeffective
    on dbo.Networks (ValidFron, DateEffective)
    include (Id, Descriptor);
go

create function dbo.ValidNetworks (
    @dt datetimeoffset(7)
)
returns table
as
    return (
        with networks as (
            select
                Id
                ,Descriptor
                ,ValidFrom
                ,DateEffective
                ,isnull(ValidUntil, dateadd(day, 1, @dt)) as ValidUntil
                ,isnull(DateExpired, dateadd(day, 1, @dt)) as DateExpired
            from
                dbo.Networks
        )

        select
            Id
            ,Descriptor
        from
            networks
        where
            ValidFrom <= @dt
            and DateEffective <= @dt
            and ValidUntil > @dt
            and DateExpired > @dt
    );

You could also create computed columns for ValidUntil and DateExpired and eliminate the CTE from the TVF.

I would also be curious to see this split into two separate queries and union'd. Whereby in both queries you filter on ValidFrom and DateEffective. In one you filter for null values in ValidUntil and DateExpired, and the other compare against the @dt value.


mysql error code 1292 We are running kea DHCP server with MySQL DB and today we encounter following error and after googling everyone saying it could be MySQL bug so wanted to see if it's really a bug or implementation issue.

MariaDB-server-10.2.15-1.el7.centos.x86_64 (3 node galera cluster)

2020-03-07 01:59:16.484 INFO  [kea-dhcp4.leases/29796] DHCP4_LEASE_ALLOC [hwtype=1 00:0c:29:ae:77:cb], cid=[no info], tid=0x80b9c710: lease 10.10.10.22 has been allocated
2020-03-07 02:04:29.625 ERROR [kea-dhcp4.alloc-engine/29796] ALLOC_ENGINE_V4_ALLOC_ERROR [hwtype=1 00:19:85:f0:1c:89], cid=[no info], tid=0xd7790768: error during attempt to allocat
e an IPv4 address: unable to execute for <UPDATE lease4 SET address = ?, hwaddr = ?, client_id = ?, valid_lifetime = ?, expire = ?, subnet_id = ?, fqdn_fwd = ?, fqdn_rev = ?, hostna
me = ?, state = ? WHERE address = ?>, reason: Incorrect datetime value: '2020-03-08 02:04:29' for column 'expire' at row 1 (error code 1292)

This is likely to be a problem with daylight saving time: In the US (and possibly other places) the hour between 02:00am and 03:00am on 8th March 2020 does not exist because the clocks are moved forward one hour at 02:00am to 03:00am.


Oracle and execution plans I have used the following procedure to view the last execution plan

create or replace procedure display_plan
as
begin
  for c in (select plan_table_output t from table(dbms_xplan.display_cursor('', '', 'allstats advanced last'))) loop
    dbms_output.put_line(c.t);
  end loop;
end;

With dbms_xplan.display_cursor it is possible to specify the SQL_ID. I understand that you can get a list of SQL_IDs from the table V$SQL but how does one decide what ones belong to the current session?

Yes, you can specify the SQL_ID.  The usage would be something like this:

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('gwp663cqh5qbf',0));

To find the SQL IDs for the current session, you'll first need to get the SID for your session, and then search v$session for the related SQL_IDs:

select sys_context('USERENV','SID') from dual;
select SQL_ID from v$session where SID='yoursid';

If the SQL aged out of the cursor cache, you may be able to find it in v$active_session_history:

select SQL_ID from v$active_session_history where SESSION_ID='yoursid';

I would recommend reading the manual page for DBMS_XPLAN to get the full details.


Postgres CREATE OR REPLACE VIEW... can it block? I'm looking for a way for a table to be swapped while it's being used. Say we have a view
CREATE OR REPLACE VIEW my_data AS SELECT * FROM my_data_v1;

that is being used, e.g. lots of SELECT * FROM my_data. Then I ingest data into a new table, my_data_v2, and then run:
CREATE OR REPLACE VIEW my_data AS SELECT * FROM my_data_v2;

Would this block on any SELECT statements that are using the original view definition?

Yes, that would block, and there is no way around that.


T-SQL Calculate average value for Duration Field Can anyone please help me on this..

I have a column in my table that stores the duration in an nvarchar field formatted as '00:00:00'.

How do I calculate the average duration?  I read some other blogs and also used cast techniques like the one below but couldn't solve my problem...

cast(cast(avg(cast(CAST(Duration as datetime) as float)) as datetime) as time) AvgTime

Please help...

Store duration in seconds as an integer; then average is quite easy. Right now you're trying to take an average of a string converted to a float converted to a datetime converted to a time. If that doesn't sound wrong to you, read it again. Then consider that time represents a point time. What is the average of 3:12 AM and 4:57 PM? Meet in the middle?

Don't be tempted to change this from nvarchar to time. Again, time is a point in time, not an interval. You should be storing start and end values as datetime; you can always calculate duration from that (typically I see duration as an additional nonsense column that doesn't need to be stored in the first place).

The formatting as hh:mm:ss should not happen at storage time, only at presentation time. And no idea why you would ever use nvarchar for this in the first place - what Unicode characters do you expect to support in hh:mm:ss?

In the meantime:

SELECT CONVERT(TIME(0), DATEADD(SECOND, AVG(DATEDIFF(SECOND, 0, CONVERT(DATETIME,
  CASE WHEN ISDATE(Duration) = 1 THEN Duration END))),0)) -- in case of garbage data
FROM dbo.your_table_name;

See how messy that is? Wouldn't it be much easier as:

SELECT AVG(Duration) FROM dbo.your_table_name;

Or:

SELECT AVG(DATEDIFF(SECOND, [start], [end])) FROM dbo.your_table_name;

And then format as hh:mm:ss in the client language? Yes, I think so.


Why does the SQL Server build number sequence not correlate with the release date sequence? I'm looking at the Microsoft SQL Server 2016 Builds (see that section heading). The list of builds is given in build number order. That is, at the top of the list is the "highest" build number, and each subsequent row has lower build number than the one before.
However the release dates don't correlate with this. So here are 5 rows, for example:

Build 13.0.5081.1 - released 14/08/2018
Build 13.0.5101.9 - released 9/07/2019
Build 13.0.5102.14 - released 11/02/2020
Build 13.0.5149.0 - released 30/05/2018
Build 13.0.5153.0 - released 17/07/2018

The general sequence for builds around this time comes from 2018 - and then we have those 2 odd rows, from 2019 and 2020 and then back to the general sequence from 2018.
If a cumulative update is supposed to contain all prior cumulative updates, then how could that last build (in the 5 above) have contained the updates from the two builds from 2019 and 2020? (Oops - only just noticed the first line is from Aug, which is after the 4th and 5th lines too!)
If I had installed the 4th and 5th builds at the time of their releases (in May 2018 then July 2018), and then that 1st build gets released in Aug 2018 - would I automatically have had those changes incorporated already? If so, what was the point of issuing an older build number?
Another example
Here is another example that just makes no sense to me.

First, the RTM is dated June 2016. Then the first cumulative update - CU1 - is dated July 2017, which is 10 months after CU2 was released in Sep 2016. Huh?
And read the text at the bottom - "After certain Cumulative Updates, Microsoft released a Service Pack for SQL Server, which contains the entire previous fixes, Cumulative Updates, and hotfixes". Huh again? Note SP1 was released November 2016 and a full 7 of the 9 cumulative updates - correction, "previous ... Cumulative Updates", according to the caption - were released in 2017 after the Service Pack.
Table is from MSSQLTips.

Build 13.0.5101.9 - released 9/07/2019 is
https://support.microsoft.com/en-us/help/4505220/kb4505220-security-update-for-sql-server-2016-sp2-gdr-july-9-2019
Build 13.0.5102.14 - released 11/02/2020 is
https://support.microsoft.com/en-us/help/4532097/kb4532097-description-of-the-security-update-for-sql-server-2016-sp2-g
These are General Distribution Release (GDR) builds, intended to be installed by all customers running the affected Service Pack level.
Basically, those are critical patches for SQL 2016 SP2 which don't require installing any of the Cumulative Updates.
The relevant fixes would also have been included in the next CU, and probably available as a hotfix for the current CU.
As for the build numbers, GDR is a seperate branch from CU.  It's tricky to apply a single build number system to a branched codebase, but the strategy appears to be that the GDR build numbers are placed between the common root build number (SP2) and the first CU (SP2 CU1).

if I upgrade my system to 13.0.5149.0,

That's CU1, and once you are on the "CU Train" you must apply subsequent CUs to get fixes.  That's why the KB's say

To apply this update, you must have SQL Server 2016 SP2 or any SQL
Server 2016 SP2 GDR release through this SQL Server 2016 SP2 GDR
installed.


SQL Server Database size - doubled running DBCC I was running a dbcc command using ola hallengren checkdb on a large database (775Gb) when half way through the process the tempdb ran out of space. I have run this in other environment on different databases in the past and no problems. This time though the database went from 775 GB to 1.6 TB, doubling in size. I have never had this issue in the past and anything I do increase the size by 100 GB. What can be causing this issue like that? SQL server 2016.

CHECKDB by design will use tempdb space for temporary storage and it will fail if you run out of space. Make sure you presize your tempdb. You can check how much space it needs by issuing WITH ESTIMATEONLY command:
DBCC CHECKDB ([WideWorldImporters]) WITH ESTIMATEONLY;

You will be able to get an estimate space requirement for tempdb:
DBCC results for 'WideWorldImporters'.
Estimated TEMPDB space (in KB) needed for CHECKDB on database WideWorldImporters = 1.
Estimated TEMPDB space (in KB) needed for CHECKDB on database WideWorldImporters = 261556.
Object ID 1179151246 (object 'Warehouse.ColdRoomTemperatures'): The operation is not supported with memory optimized tables. This object has been skipped and will not be processed.
Object ID 1211151360 (object 'sys.memory_optimized_history_table_1179151246'): The operation is not supported with memory optimized tables. This object has been skipped and will not be processed.
Object ID 1243151474 (object 'Warehouse.VehicleTemperatures'): The operation is not supported with memory optimized tables. This object has been skipped and will not be processed.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Since you have close to 800GB, what you can do is breakdown the CHECKDB into multiple DBCC commands. You can follow the recommendation here.


Restarting a Publisher involved in Merge replication Our hosting provider would like restart the SQL Server that a publisher in a merge replication topology.

I'm not able to clearly understand best practice for doing this from the Microsoft docs.

Our topology consists of ~40 Pull subscribers. The synchronisation is executed on a schedule ever 5 minutes via a Windows Scheduled task that runs an App that calls RMO.

Some of my concerns include: Will transactions be safely cued on the Subscribers - I imagine this will potentially cause transaction log growth? What is a reasonable time period for the Publisher to be offline - do I need to worry about expiry?

If someone could outline what steps would be involved in safely shutting down the Publisher that would be a great help.

Many thanks.

Will transactions be safely cued on the Subscribers - I imagine this will potentially cause transaction log growth?

Transactions will be safely cued on the subscribers and transaction log growth will be negligible.  Merge Replication utilizes DML triggers along with change tracking tables in the publication and subscription databases that contains metadata to determine which changes need to be propagated.  So you might see some growth in the Merge metadata tables while the publisher is offline.

What is a reasonable time period for the Publisher to be offline - do I need to worry about expiry?

Subscriptions will expire if the they do not synchronize with the publisher within the publication retention period.  The default retention period is 14 days.  You can check what your current publication retention period is by executing sp_helpmergepublication at the publisher on the publication database and inspecting retention and retention_period_unit in the result set.  Another way to check your publication retention period is to right-click the publication in SSMS -> Properties.  On the General page there is a section labelled Subscription expiration.

If you restart the publisher and it comes back online within the publication retention period then Merge Replication will pick up where it left off.


Designing a data model for a survey; building the relationships Let's say I have a survey that will be handed out to many people. I have a hypothetical diagram with User, Question, and Response entities. Should my relationship be one-to-one from question to answer? My thinking is that each question can only have one response. I could argue, however, that since this is a survey with many users, each question can have multiple Responses.

How do I approach this situation, and where is my confusion stemming from?

Table 1 - Questions

Table 2 - Users

Table 3 - UserResponses

If your answers could be more than just yes or no 1 or 0, you might also want a question_answers as a 4th table.  With this you could then use multiple choice with more than 2 choices.

UserResponses will be a many to many relationship or a junction table because a user might answer more than one question and a question will be answered by more than one user.

The UserResponses table will reference the Users table primary key and the Questions table primary key as foreign keys.
In the case where you want multiple choice, not just yes/no, the UserResponses table will also have a foreign key relationship to the Question_Answers table.


Simple Search on 13 Terabyte Oracle Database to return relevant rows (.net app? Java?) I am sure there are some easy ways to do this... I have a simple flat Oracle table which is 13 terabytes or so in size due to a large number of rows accumulating over the years.  I need to do a search on it (can be looking for matches on any of a number of fields), and would like to return the most relevant records in a list, like top 50 or top 100 which I can then click on for more detail (will then query specifically one field in that particular record).

What .net or Java - library or package would you recommend for this kind of search in Oracle?  Any chance there might be an example open source application or example like that out there that does this exact thing?  I am hoping to find something that allows me to prioritize matches in certain columns first, and finds matches quickly despite the large table size, and can easily be integrated into the second part of the requirement which is a regular query based on a "thread" to which that record belongs.

Thank you.

There are some useful hints in this SO post.

If you are looking for alternatives to Oracle Text, have a look at this.


Insert data into oracle table with loop? How to use sql to insert line detail data into header with the output like below.

line detail
MO_ID  SEQ_NO  QTY   LEVEL
121M   2        3
121M   3        9
125M   2        6
125M   3        7
125M   4        1

Header
MO_ID  LEVEL SEQ_NO   QTY SERIAL_NO
121M    T      1           101
121M    T      1           102
125M    T      1           201
125M    T      1           202
125M    T      1           203

Insert line detail into header so output will be:

MO_ID  LEVEL SEQ_NO   QTY SERIAL_NO   Counter Seq
121M    T      1           101         001     1
121M           2       3               001     2
121M           3       9               001     3
121M    T      1           102         002     1
121M           2       3               002     2
121M           3       9               002     3
125M    T      1           201         003     1
125M           2       6               003     2
125M           3       7               003     3
125M           4       1               003     4
125M    T      1           202         004     1
125M           2       6               004     2
125M           3       7               004     3
125M           4       1               004     4

... and so on until the last serial is reached in header

This is only judging from the very little information you provide:

SELECT h.MO_ID, h.LEVEL, l.SEQ_NO, l.QTY, h.SERIAL_NO, h.Counter,
       ROW_NUMBER() OVER (PARTITION BY h.MO_ID, h.SERIAL_NO ORDER BY l.SEQ_NO)
FROM (
  SELECT h.*, SUBSTR(CAST(1000 + ROW_NUMBER() OVER (ORDER BY MOD_ID, SERIAL_NO) AS char(4)), 2, 3) AS Counter
  FROM "Header" h
  ) h
JOIN (
  SELECT MO_ID, SEQ_NO, QTY, CAST(NULL AS varchar(100)) AS LEVEL
  FROM "line detail"
  UNION
  SELECT DISTINCT MO_ID, SEQ_NO, NULL AS QTY, LEVEL
  FROM "Header"
  ) l ON (l.MO_ID = h.MO_ID)
ORDER BY h.MO_ID, h.SERIAL_NO, l.SEQ_NO


Unable to attach SQL Server database with shared folder in docker on Linux The problem I'm having is that my SQL Server 2017 Linux container is not able to read my database files. I guess it has something to do with permissions and I'm sure I've overlooked something but I can't fugure it out.

I have created a clone with the PowerShell module called dbaclone.
This module creates and attaches a differencing disk based on another VHD making it easier to provision large databases.

The databases are available using a partition access path which will look something like this

With in the folder, in example "AW2017-C1" is a folder called "Data" and "Log" containing the data and the log files respectively.

The permissions on the folder are "Everyone" to have "Full Control".

I have a virtual machine in VMWare Workstation which is a Linux VM. The Linux VM has Docker installed. I shared the clone folder in the VM

To make it easier to use I added the share to /etc/fstab to mount it automatically when the VM starts.

The permissions are all set that everyone should be able to read it

I have the following script to run the docker container

sudo docker run -e 'ACCEPT_EULA=Y' \
    -e 'SA_PASSWORD=Myp@ssw0rd' \
    -p 1433:1433 --name sql1 \
    -v /home/sander/databases:/databases \
    -v /home/sander/shares/dbaclone:/var/opt/mssql/data/dbaclone \
    -d mcr.microsoft.com/mssql/server:2017-latest

The docker container will mount the directory and make it accessible for the container.

The file permissions from within the container look correct

Everything looks ok, but when i run the following script

USE master;

CREATE DATABASE AdventureWorks2017
ON PRIMARY
       (
           FILENAME = '/var/opt/mssql/data/dbaclone/AW2017-C1/Data/AdventureWorks2017.mdf'
       ),
       (
           FILENAME = '/var/opt/mssql/data/dbaclone/AW2017-C1/Log/AdventureWorks2017_log.ldf'
       )
FOR ATTACH;

This error comes back

  Msg 5120, Level 16, State 101, Line 3
  Unable to open the physical file "/var/opt/mssql/data/dbaclone/AW2017-C1/Data/AdventureWorks2017.mdf". Operating system error 2: "2(The system cannot find the file specified.)".
  Msg 1802, Level 16, State 7, Line 3
  CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
  Completion time: 2020-02-19T13:07:36.1675676+01:00

This obviously has something to do with permissions but it all looks good and maybe someone has else has some pointers how to get this fixed.

I figured out the problem with shares.

One thing I did was remove the share in VMWare because for some reason, the mount with vmhgfs did not work well with SQL Server. It just could not get the right privileges even if I tried setting them with chmod.

Instead I installed CIFS and mounted the share as

sudo mount.cifs //10.100.1.1/dbaclone/clone /data/dbaclone -o credentials=/home/sander/.dbaclonecredentials,dir_mode=0777,file_mode=0777

This mounted the share as needed.

SQL Server was able to connect to it without any permissions issues.


Install JVM for Oracle 11g XE I am trying to configure JavaMail in my Oracle 11g XE(11.2.0.2.0) database, which requires loadjava utility. But after some research I came to know that XE version of Oracle doesn't provide Java/JVM as some restriction in Free version of database.
I can not install Licence Version of Database, but want to install/configure Java service in my XE database if possible.
I was following this post How To Install Oracle JVM, but not able to locate /javavm/install/initjvm.sql in my oracle directory.
if anyone knows some solution, help would be appreciable.

No way how to do that. XE version does not support Java Stored Procedures.
You have to upgrade at least to "Standard Edition One" or "Oracle Personal Edition".

See edition comparsion.


How to SUM previous sum, e.g N = (N-1) + (N-2) + ... + 1? I have a table name "TABLE_A (id integer, no integer)" .

I want to sum "no" with group by "id" and current "sum of no" = previous "sum of no"

Here is my code :

1/ Create table & insert data:

create table table_a (id int, no int);

insert into table_a values(1, 10);
insert into table_a values(1, 20);
insert into table_a values(1, 30);
insert into table_a values(2, 100);
insert into table_a values(2, 200);
insert into table_a values(2, 300);
insert into table_a values(3, 1);
insert into table_a values(3, 2);
insert into table_a values(3, 3);
insert into table_a values(3, 3);

2/ Desired result:

id | sum_of_no
--------------
1  | 60
2  | 660
3  | 669

3/ My solutions (ok):

with t_report_code_temp as
(
select id, sum(no) as t_code
from table_a
group by id
)
select a.id, sum(b.t_code)
from t_report_code_temp a
join t_report_code_temp b on b.id <= a.id
group by a.id
order by 1

My question:

Could you give me the better way to solve ?

This looks like a candidate for window functions.

First calculate the per-id sums, then do a running sum ordered by ID to get the desired final result.

with t_report_code_temp(id, t_code) as
(
select id, sum(no)
from table_a
group by id
)
SELECT
  id,
  sum(t_code) OVER (ORDER BY id ASC)
FROM t_report_code_temp;


Running Triggers on Slave with Row-based Events in MySQL >=5.6 We would like to have a setup where a slave has triggers that are not present on the master for the purpose of updating some summary tables or run a specific udfs.sys

If one uses statement-based replication, they can just create the required triggers on the slave. The slave will run the statements from the binary log, which will cause the triggers to be invoked. With (the suggested) row-based, quote:

  If under row-based replication the slave applied the triggers as well
  as the row changes caused by them, the changes would in effect be
  applied twice on the slave, leading to different data on the master
  and the slave.

https://dev.mysql.com/doc/refman/5.6/en/replication-features-triggers.html

Is there a way to run triggers on slave only with row-based replication, similar to what mariaDB have?

Perhaps instead, you require a Stored Procedure on your slave that you can run from a cron job mysql  -e "call sp_udfs" to do the processing across the tables to alter that data. Events disable by default in a slave You could do a lot of work with that approach. This way you can control its behaviour much better than triggers. Other benefit, you can run any form of replication you wish, row, statement or mixed


How to verify if given user@host with 'password' exists in MySQL 8.0+? I write sanity check script for an application. One of the tests is supposed to verify that a given user@host with 'password' exists in MySQL. I'm using simple native hash passwords. In MySQL5.7- there is the PASSWORD() function that allows me to query mysql.user and verify that the user exists and the passwords match. Is there a way to accomplish this in MySQL8.0+? Documentation page for PASSWORD() in MySQL 5.7 does not provide any alternatives and the function is removed in MySQL 8.0.

I could simply try to add the user again, but I feel my script should know and register an event of missing user with expected credentials.

Please note that I do not necessarily need to generate password hash, I just want to verify that given user/password exists. This needs to be automated so solutions like 'try to log in with user/password from host machine' do not count as I may not have access to the host machine

Actually, there is an alternative that MySQL (really Oracle) does not mention.

Back on Apr 22, 2015, I posted the Algorithm for the Password Function
MySQL 8.0 will still store the PASSWORD function output in the authentication_string column if you create the user using the mysql_native_password plugin.

Here is the algorithm

SET @plaintextpassword = 'whatever password you want';
SELECT CONCAT('*',UPPER(SHA1(UNHEX(SHA1(@plaintextpassword)))));

GIVING CREDIT

I first learned this from a blog by a company called PalominoDB. They changed their name to blackbird. Pythian purchased blackbird. Pythian now has it as a blog post with this algorithm : Hashing Algorithm in MySQL PASSWORD()


Issue with Identifiers in Postgres greater than Oracle's limit while connecting over ODBC (DBLINK) We have a database link from Oracle 12.1 to Postgres via psqlODBC Driver. There is a problem while running few queries/statements when the Postgres identifiers, like the length of a column name exceeds 30 characters, which is the maximum limit of column names until Oracle 12.1.

  ORA-04052: error occurred when looking up remote object
  myschema.mytesttable@ORA_TO_PGLINK ORA-01948: identifier's name
  length (32) exceeds maximum (30) ORA-06512: at line 13
  04052. 00000 -  "error occurred when looking up remote object %s%s%s%s%s"
  *Cause:    An error occurred when trying to look up a remote object.
  *Action:   Fix the error.  Make sure the remote database system has run
             CATRPC.SQL to create necessary views used for querying or looking up
             objects stored in the database.

This error occurs even if  the statement does not refer the column explicitly. The statement we use basically updates another column which has a normal length(<= 30 characters) through an update statement over PL/SQL.

BEGIN
  UPDATE "mytesttable"@ORA_TO_PGLINK
     SET "col" = 10
   WHERE "col" = 1;
END;

Here the col is not the one whose length is greater than 30 characters in Postgres.
Also, the statement works fine while running as a normal SQL statement, ie. without an anonymous block or inside a procedure.

Now, of course we can fix this issue by renaming the column name in Postgres to have a length <= 30 characters. But, we do not want to do that because it has several other dependancies. As far as Oracle db is concerned, the update is part of a PL/SQL procedure  and can't be run as a single statement directly. What is the solution for this? Is there a workaround or some settings of the driver  be modified?

Creating a view in the Postgres database that assigns shorter names to the columns in question might be an option. It will not affect dependencies on the Postgres side while providing comfortably short column names to Oracle.

dbfiddle


PostgreSQL functions inside where clause I have table like this:

table a (
   id
   --
   p1 text,
   p2 text,
   p3 text
)

When I write some function, which use this table, I need to write code like this

create or replace function ...

select * from a
   where a.p1 is not null
     and a.p2 is not null
     and a.p3 is not null

I want to create a function,

 create function my_function(??? val) returns boolean as -- what I need to place instead of ???
 $$
 begin
     return val.p1 is not null
        and val.p2 is not null
        and val.p3 is not null;
 end;
 $$ language plpgsql

in which I will pass "this" instance of current row (I don't know, how it called) and my functions will be like this:

select * from a
   where my_function(???) -- what I need to place instead of ???

My questions are:

Can I create function my_function like above?
If point 1 is true, what type I need to write for parameter? And what I need write on where clause?

For every table that is created a type with the same name is created. So you you can define your function to accept the table's type.

Such a function is also better written as a SQL function to avoid the overhead of PL/pgSQL.

create function my_function(val a) returns boolean as
 $$
 select val.p1 is not null
    and val.p2 is not null
    and val.p3 is not null;
 $$
 language sql
 stable;

The a in (val a) is the name of your table.

You can use that function in the following way:

select *
from a
where my_function(a);


Query execution showing error as login failed I want to retrive the table values from other server

Query

EXEC sp_addlinkedserver @server = 'OPSERVER', @srvproduct = 'SQL Server'

EXEC sp_addlinkedsrvlogin @rmtsrvname = 'OPSERVER', @rmtuser = 'SA', @rmtpassword = 'SA'

SELECT * FROM OPSERVER.VENUS.dbo.TABL1

The above query is showing error as login failed for user 'SA'

Treid Query

EXEC sp_addlinkedserver @server = 'OPSERVER', @srvproduct = 'SQL Server'

sp_addlinkedsrvlogin
  @rmtsrvname = 'OPSERVER',
  @locallogin = 'sa',
  @rmtuser = 'sa',
  @rmtpassword = 'sa'

SELECT * FROM OPSERVER.VENUS.dbo.TABLE1

Showing error as "Login failed for user 'sa'"

What wrong in my query.

You need to provide password for remote sa account, like this:

EXEC sp_addlinkedsrvlogin @rmtsrvname = 'OPSERVER', @rmtuser = 'SA', @rmtpassword = 'RemoteSaPassword'

Or if you run under local sa account

sp_addlinkedsrvlogin
  @rmtsrvname = 'OPSERVER',
  @locallogin = 'sa',
  @rmtuser = 'sa',
  @rmtpassword = 'RemoteSaPassword'

FOr windows auth:

sp_addlinkedsrvlogin
  @rmtsrvname = 'OPSERVER',
  @locallogin = 'domain\username',
  @rmtuser = 'sa',
  @rmtpassword = 'RemoteSaPassword'

Or

sp_addlinkedsrvlogin
  @rmtsrvname = 'OPSERVER',
  @useself = 'TRUE'


Invalid create index syntax I cannot create index with the where clause.

Refer the below query:

CREATE INDEX c_bplocation_isdefault
ON c_bpartner_location
  (ad_client_id,c_bpartner_id,isdefault)
WHERE isdefault = 'Y';

You cannot have a WHERE clause on an index in Oracle.

You can create a function-based index (I'm eliminating the isdefault third column from the index since we know that must be Y so it's not adding any selectivity to the index)

CREATE INDEX c_bplocation_isdefault ON c_bpartner_location (
  case when isdefault = 'Y' then ad_client_id else null end,
  case when isdefault = 'Y' then c_bpartner_id else null end
)

This takes advantage of the fact that Oracle does not index entries where all the columns in the index are NULL in order to reduce the size of the index.  In order for your queries to use the index, however, you'd need to use the same expression that you used to create the index.  Something like

SELECT *
  FROM c_bpartner_location
 WHERE (case when isdefault = 'Y'
             then ad_client_id
             else null
         end) = <<some value>>
   AND (case when isdefault = 'Y'
             then c_bpartner_id
             else null
         end) = <<another value>>
   AND isdefault = 'Y'

Realistically, you're often better off creating an actual function that you use in both your index definition and in your query so that you're not coding the CASE statement in multiple locations.


Communication between DB servers outside Active Directory Dear SQL Server / Windows PROs:

I am trying to set up a solution here (based on SQL Server 2012 Std + Windows Server 2012 R2).
Currently the setup is based on a Cluster (two servers, 1 SAN) and stand alone server.
They all under one single domain so I have no problem with them to talk to each other. Because of business decision I was asked to set up
another two servers outside that domain.
One as mirroring / log shipment recipient, the other one has to communicate with the cluster to keep several objects in sync (few tiny tables total under 10mb) and send some data back (here it will be more data, but we can accept longer delays).

Whatever I am trying I am hitting the wall during the authentication setting as being outside the same Active Directory is just killing the whole concept.
Can you show me a direction of how can I pass it? Doesn't have to be detailed I will do my homework. Thanks!

Because the machines are on different domains, you cannot use Windows/Integrated authentication. For mirroring, you can use certificates instead. The place to start is here:

https://docs.microsoft.com/en-us/sql/database-engine/database-mirroring/setting-up-database-mirroring-sql-server.

In that article, there is a specific section that links to the details of setting up certificates for outbound and inbound traffic.

https://docs.microsoft.com/en-us/sql/database-engine/database-mirroring/use-certificates-for-a-database-mirroring-endpoint-transact-sql.


Can't retrieve data of countries and regions Goal:
Improve my sql ability by doing some coding and reviewing existing code.

Problem:
I have spent lots of hour to find a solution based of this question
"3c. Some countries have populations more than three times that of any of their neighbours (in the same region). Give the countries and regions. "

I tried finding different solution but unfortunately I can't solve this problem :(

The link can be found "http://sqlzoo.net/1a.htm"

Again, the purpose is to improve my ability in SQL!

// Fullmetalboy

Since you're trying to get into the SQL way of thinking, I'll try to break this down to explain how I got to the conclusion.

To find the answer behind this search, I tried to examine the data to see what I had to work with.

SELECT * FROM bbc

I found that what we really had to deal with was Population, Name (country name) and Region.  The question stated that we need to find countries larger than others in the same region.  So, I picked a random region and examined the data there:

SELECT * FROM bbc
WHERE region = 'South Asia'

Then I sorted the data by population so that I could try to understand what we're going to compare.

SELECT * FROM bbc
WHERE region = 'South Asia'
ORDER BY population DESC

If you're following from that page, you can see that India is dramatically larger than the next sized country, Pakistan.

Next, I (misunderstanding the question), found all countries that were three times the size of any other country in that region.  However, I realized that question was really "three times the size of the smallest".  So, I came up with

SELECT * FROM bbc
WHERE region = 'South Asia'
    AND population >
        (SELECT MIN(b.population) * 3
         FROM bbc AS b
         WHERE b.region = bbc.Region)
ORDER BY population DESC

There I'm finding the population where the size is greater than three times the minimum of that region.  The correlated subquery (go research those) will limit the comparison to only select the populations in that region.

Finally, I realized that we're supposed to find the one that was three times larger than all other countries in the region (not just three times larger than ANY country in the region), so I changed my query to:

SELECT * FROM bbc
WHERE region = 'South Asia'
    AND population >
        (SELECT MAX(population) * 3
         FROM bbc AS b
         WHERE b.region = bbc.region
           AND b.name <> bbc.name)
ORDER BY population DESC

That changed the "MIN" to "MAX" and excluded countries from the subquery that are the same country as the one we're comparing.  So, when we're comparing India in the outer query, we're ignoring India in the inner query--thus finding the largest country that's Not India.

Finally, remove the "RegioN" from the outer where clause and only pull the two columns that they want (since they're picky):

SELECT name, region FROM bbc
WHERE population >
        (SELECT MAX(population) * 3
         FROM bbc AS b
         WHERE b.region = bbc.region
           AND b.name <> bbc.name)
ORDER BY population DESC


Copy a view from one database to another database on the same server I want to copy a view from one database to another database, on the same server. Both databases are structured exactly the same, except for about 8 views added in one.

Basic method:

Right click on the view in SQL Server Management Studio
Save as script to new query window
Change context to target database
Execute script

This is basically correct, but this will not preserve permissions on the views. A better way is to:

Right-click the database
Choose Tasks -> Generate Scripts -> Choose Objects -> Select specific database objects
Choose your views -> next
Click the [Advanced] button and then [Script Object-Level Permissions] should be set to True (default is False)


Import SQL dumps (tables) in folder from shell Im trying to import SQL tables (actually this is files, one file for 1 table), and this is files are too big for importing in phpMyAdmin. It needs to be imported from shell. I know the command "source " to import whole DB. But I'm need to import all *.sql files in folder into one DB. Does mysql have query for this?

P.S. I can't "cat " this file because I'm getting troubles with encoding after this.

Assuming a bash shell, (and for some reason you cannot concat the files together) that is a simple for loop:

for file in *.sql; do mysql **put here your connection parameters** database_name < $file; done


SQL Datatypes reccomendations I have these columns and their datatypes,
Could someone give me any advice whether they are right, and if not, suggest what is more appropriate?

Thanks

NOTE: I'm assuming that you are using SQL Server, from the type names listed, as you've not specified anything else.

You should allow for non-ANSI characters in names by using NVARCHAR instead of VARCHAR though of course to properly support extended character sets the rest of your stack might need work also...
You should allow for significantly longer names then 20 characters. Don't use MAX due to potential indexing and other performance concerns, as you are likely to want to search and/or sort by names.
You might need to extend the VAT number column a bit too, depending on the territories that you need to support. See https://en.wikipedia.org/wiki/VAT_identification_number for a list of formats a number of which are longer then 11.
I would be inclined to widen phone number too.
Spaces, punctuation, and anything else that might require escaping, are a bed idea in column names. I would stick with letters, numbers, and underscore.

What is "client name" used for and how (if it does) does it related to the other name columns? There maybe specific advice about that depending on how it is intended to be used.


Don't know why MySQL is not creating table with primary key constraint I can't understand why MySQL showing this error:

  #1064 - 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 'CONSTRAINT (uname_pk) PRIMARY KEY, pwd Varchar(50), fname
  Varchar(50), lname ' at line 1

And this is the query:

create table uinfo_master(uname Varchar(50) NOT NULL CONSTRAINT (uname_pk) PRIMARY KEY,
pwd Varchar(50),
fname Varchar(50),
lname Varchar(50),
email Varchar(50),
phno Varchar(50),
address Varchar(200),
city Varchar(50),
state Varchar(50),
pin Varchar(50),
country Varchar(50));

What is wrong with my query, kindly show my mistake.

Correct syntax, with minimal changes to your style:

create table uinfo_master(
uname Varchar(50) NOT NULL,
CONSTRAINT uname_pk PRIMARY KEY (uname),
pwd Varchar(50),
fname Varchar(50),
lname Varchar(50),
email Varchar(50),
phno Varchar(50),
address Varchar(200),
city Varchar(50),
state Varchar(50),
pin Varchar(50),
country Varchar(50));

You cannot use the CONSTRAINT syntax for the PK on the same line as the column definition.

See the CREATE TABLE syntax:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    { LIKE old_tbl_name | (LIKE old_tbl_name) }

create_definition:
    col_name column_definition
  | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
      [index_option] ...

Defining the column itself is done on the col_name column_definition version of the syntax. The | that follows that stands for OR in the documentation, meaning an alternate CREATE TABLE (comma separated) "line" can use the syntax that starts with CONSTRAINT, which optionally includes a "symbol" (that's what brackets mean: optional), then PRIMARY KEY, optionally an index_type, and then the column itself, in parentheses.

From the look of it, you tried to do all that on one line, which was where your problem was. You can declare a column to be a PRIMARY KEY on the line the column is defined, but you cannot use the CONSTRAINT syntax to do so.


Display list of SSRS reports in a dropdown list, and allow users to select which report to display Our company has about 10 different SSRS reports. We would like to allow users to navigate from one report to another simply by selecting the report from a dropdown list. Is this possible?

Reports should be stored in the Catalog table in the SSRS database, you should be able to create a report based off the contents of the Catalog table...


How to handle HUGE 16GB SQL file? I've abc.sql file exported from MySQL Table.
This abc.sql file is 16 GB in size with around 28 million insert statements.

I'm looking for option to store it in SQL Server Database.

Couple of questions:

How to open 16GB file?
Is it possible to load 28 Millions Insert statements from MySQL to SQL Server?

I have come across a similar problem once when importing a 5GB SQL file. Most editors max out at 256mb or 512mb, the only editor I know of (on windows) that can open files in the GBs without issues is emEditor (there's a trial version). My old rig with 8GB of RAM (twice as much as yours but most of it was used up to 80% the entire time) and ~20-25 GB of free space on C drive could open it in ~2minutes (keep in mind my hardware was extremely old DDR3 with gen2 or gen3 i7 processor and slow drives).
The biggest selling point (even if you have to wait for it to load) is being able to view the schemas and the code in case the export didn't include DDL for the tables (which was the case in my scenario).
Depending on the exporting tool used, your insert statements might have ` for strings instead of ', thankfully using the emEditor you can replace all of them with '. This will allow you to import your code into SQL Server without issues.
The way I solved it was by modifying the file so it can be natively imported into SQL Server and use SQL CMD to run the script file.


sql server removing service_queues When I query select * from sys.service_queues in SQL Server 2008 sp3, I  am getting:

InternalMailQueue
ExternalMailQueue
_LakeSide_DbTools_DeadlockQueue
QueryNotificationErrorsQueue
EventNotificationErrorsQueue
_LakeSide_DbTools_LockQueue
syspolicy_event_queue
ServiceBrokerQueue

I need to remove _LakeSide_DbTools_DeadlockQueue and _LakeSide_DbTools_LockQueue from MSDB database permanently. I installed that lakesidetool and manually removed all procedures, now stuck in removing this services. I used their uninstall tool, but it didn't help.
http://www.sqlsolutions.com/products/sql-deadlock-detector/Uninstall.html

Queues are schema bound objects and as such they may belong to a different schema than your current default schema (likely dbo). If that's the case the DROP statement must qualify the queue with a two part name:

DROP QUEUE <schema>.<queue>;

You can get the schema name from sys.service_queues:

SELECT SCHEMA_NAME(schema_id), name FROM sys.service_queues;


How to group records under certain conditons in mysql I would like to join 2 tables and group user records based on user id and return its overall status.

Example. Lets say a user applied for a loan, he uploaded multiple documents to bank website which are required to approved by the bank.

if all docs are approved by bank then the status must verified ,

If at least one document is "verified", the overall status is "verified".

If no document is "verified" but at least one is "in progress", the overall status is "in progress".

If no document is "verified" and "in progress", but at least one is "expired", the overall status is "expired".

If no document is "verified", "in progress" and "expired", but at least one is "rejected", the overall status is "rejected".

If no document is uploaded at all, the status is "no xyz uploaded".
 (for documents without expiration date the status is "expired")

Two tables : customer and customerDocs

user has id, name, phone email

customerDoc has id, customerID(reference key), verificationStatus, description, expDate

SELECT
    cu.id CustomerID,
    CASE WHEN cd.verification_status = 'PAID'
     THEN 'PAID'
     WHEN cd.verification_status = 'PENDING'
     THEN 'PENDING'
     WHEN cd.verification_status <> 'PAID' && cd.verification_status = 'PENDING'
     THEN 'PENDING'
     WHEN cd.verification_status <> 'PAID' && cd.verification_status <> 'PENDING'
          && cd.verification_status = 'CLOSED'
     THEN 'CLOSED'
     WHEN cd.verification_status <> 'PAID' && cd.verification_status <> 'PENDING'
          && cd.verification_status <> 'CLOSED'
     THEN 'REJECTED'
     WHEN cd.expire_date IS NULL
     THEN 'CLOSED'
     ELSE 'No status'  END STATUS
FROM customer cu
    JOIN customer_details cd ON cu.id = cd.customer
GROUP BY cu.id

I tried the above query but not getting expected output. So any help here

Schematically:

SELECT user.id
       CASE
       -- if all docs are approved by bank then the status must 'verified'
            WHEN SUM(document.status = 'approved') = COUNT(document.id)
                THEN 'verified'
       -- If at least one document is 'verified', the overall status is 'verified'
            WHEN SUM(document.status = 'verified')
                THEN 'verified'
       -- If no document is 'verified' but at least one is 'in progress', the overall status is 'in progress'.
            WHEN SUM(document.status = 'in progress')
                THEN 'in progress'
       -- If no document is 'verified' and 'in progress', but at least one is 'expired', the overall status is 'expired'.
            WHEN SUM(document.status = 'expired')
                THEN 'expired'
       -- If no document is 'verified', 'in progress' and 'expired', but at least one is 'rejected', the overall status is 'rejected'.
            WHEN SUM(document.status = 'rejected')
                THEN 'rejected'
       -- If no document is uploaded at all, the status is 'no xyz uploaded'
                ELSE 'no docs uploaded'
       END AS `overall_status`
FROM user
JOIN document ON (joining conditions)
GROUP BY user.id

  (for documents without expiration date the status is 'expired')

I don't understand the place of this condition.


Import indexes with SQL Server If I have two tables that have identical constraints, datatypes and primary keys is there a way to 'import' the index from one table to another if they're on the same server, but in separate databases?

The only option will be to script out the index and create it on the new table in the other database.


Keep ROW = NULL or DELETE it? in this scheme

id  uid     content
1   1       A
2   1       B
3   1       C
4   1       D
5   1       E

If the User decided that he doesn't need the rows with id = 3, 4, 5 Is it better to make him set their content to NULL or just delete the row?

This process occur many time And he maybe use the NULL slots again. Do I still use UPDATE and set it to NULL in case he will change it in the future? Or just DELETE the entire row?

The question is like: is it better to

DELETE the Row? INSERT| -> DELETE -> INSERT .. DELETE -> INSERT

or

UPDATE the Row? INSERT| -> UPDATE to NULL -> UPDATE to value .. UPDATE to NULL -> UPDATE to value

If the rows will be re-used frequently, it is likely better to set the values to NULL, or simply add a column is_deleted to indicate that the row has been soft-deleted.  Continuously deleting and inserting rows can lead to a heavily fragmented table, and can be a cause of performance degradation.  This is typically used for queues where there are a known number of slots (rows) in the queue, and those slots are allocated ahead of time.

If the rows will not be re-used, or will be re-used very infrequently, then it's better to deallocate the space used by those rows.


Copying table subset from one database to another with same schema I am trying to subset of a table from one table to another

  users(id, name, referrer_id, country_id)
  this table have self referential association with refferer_id as foreign key to itself.
  I tried following query

psql source_table -c 'COPY (SELECT * FROM users where country_id = 2) TO stdout' | psql target_table -c 'COPY users FROM stdin'

Issue with above approach is reffer may not be in the same subset, so it will show the error

ERROR:  insert or update on table "users" violates foreign key constraint "users_referrer_id_fk"
DETAIL:  Key (referrer_id)=(123) is not present in table "users".

I tried to use ON CONFLICT but it seems that ON CONFLICT syntax is not available in COPY TABLE syntax. Is there a better way to transfer subset of tables between databases

It's not very clear what your goal is.
 If you want to copy data and ensure its' referential integrity, then you'll need to modify your SELECT so it picks up all dependent rows (check recursive CTE for details).

If want to just copy data , and don't care about referential integrity, you can either drop or disable FK constraints during import.  Since FK in Postgres enforced with triggers, you can ALTER table disable trigger all , import, and enable them .
Another way to disable all triggers would be SET session_replication_role = replica;, import data, then reset to default SET session_replication_role = DEFAULT;


How To Exclude Queries Returned From mysqldumpslow Command I would like to ignore queries in the mysql-slow log that originate from running the command mysqldumpslow by using SQL_NO_CACHE as the trigger word.
My experience using regex are limited at best and I'm unable to find any examples in the docs.
I've tried a few variations of the following:
mysqlslowdump -g '!(SQL_NO_CACHE)' /path/to/my/log
mysqlslowdump -g '^!(SQL_NO_CACHE)$' /path/to/my/log

... but always get back:

Died at /usr/bin/mysqldumpslow line 162

What is wrong in my regex?

OK - you should check out the page here,

    (Due to a limitation in the parser, a space character must precede
and follow the SQL_NO_CACHE keyword; a nonspace such as a newline
causes the server to check the query cache to see whether
the result is already cached.)

Now, it is possible that the syntax that you are using doesn't correspond to the bit about "newlines" above - I'm not sure. Maybe you could try running your queries directly from the command line?

[EDIT] in response to OP's comment.

mysqlslowdump -g '!(SQL_NO_CACHE)' /path/to/my/log
mysqlslowdump -g '^!(SQL_NO_CACHE)$' /path/to/my/log

I've never come across this problem, but is it possible that rewriting your code with spaces surrounding "SQL_NO_CACHE" could have some effect?


MySQL : Stored Procedure from Trigger giving duplicate results I have created a stored procedure to insert data into an audit table. The procedure accepts an argument that takes table name and same value is inserted into the audit table along with PK.

When i'm calling this stored procedure from AFTER DELETE trigger, from multiple tables it is inserting duplicate values. Basically, same PK value is getting inserted for all table names in the audit table. So, if i delete a record from a table, number of inserts in audit table for single delete = number of triggers.

CREATE PROCEDURE sp_ins_log(
trigger_name VARCHAR(64),
column_name VARCHAR(64),
column_value VARCHAR(255),
requested_by VARCHAR(100))
INSERT INTO log (request_id,reference_schema, reference_table, reference_field, reference_id, action_performed, requested_by)
SELECT
(SELECT trx_id FROM INFORMATION_SCHEMA.INNODB_TRX WHERE trx_mysql_thread_id = CONNECTION_ID()),
EVENT_OBJECT_SCHEMA,
EVENT_OBJECT_TABLE,
column_name,
column_value,
CONCAT(ACTION_TIMING,'_',EVENT_MANIPULATION),
IFNULL(requested_by,CURRENT_USER())
FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME = trigger_name AND TRIGGER_SCHEMA = SCHEMA();

CREATE TRIGGER tr_table_ad
AFTER DELETE
ON table_name FOR EACH ROW
CALL sp_ins_log('table_name',old.id);

But, if i put the same insert query individually in each trigger, it works fine.

Reproduced the issue DB Fiddle

Compare this with direct execution of INSERT query inside SP.

The reason is simple - inaccuracy. Variable name and column name interference. You type WHERE TRIGGER_NAME = trigger_name. Both TRIGGER_NAME and trigger_name are treated as input variable name, and this condition is always true. You have 2 triggers matched the rest condition TRIGGER_SCHEMA = SCHEMA(), so query returns 2 rows.

Simply rename input variables. Or add table alias and allow the server to distinguish the column name and variable name: WHERE INFORMATION_SCHEMA.TRIGGERS.TRIGGER_NAME = trigger_name AND TRIGGER_SCHEMA = SCHEMA();.


the best place for save binary file Possible Duplicate:
  Files - in the database or not?

anyone can tell me, what is the best place for save binary file? database or just in a file system? and what is plus and minus for both? :D

actually, I want to save my image file, but I don't know what is the place for save my image

thanks :D

This question is similar to: Files - in the database or not?. Click the link to see the answers provided.


Is there a progress indicator for OPTIMIZE TABLE progress? MySQL 5.1.4x (Windows) | Innodb

I recently purged data from a mySQL DB (a few hundred thousand rows) and I'm planning to use

OPTIMIZE TABLE LOGTABLEFOO1,LOGTABLEFOO2,LOGTABLEFOO3;

to reduce the data space footprint the empty whitespace is taking up in the filesystem.

My concern is that once I start this command, I won't know how long it's going to take or where it is in the process. Is there anyway I can determine this information? As far as I know, there's no progress indicator.

There is nothing MySQL has that will indicate the progress of OPTIMIZE TABLE;.
You may have to go the OS using Windows Explorer and look for a growing tmp table. Hint: Temp table are MyISAM tables that do not have a .frm file with the name #sql-9999.MYD and #sql-9999.MYI. While these two fle exist, the query would be considered in progress. To know how far, one would have to know how many rows times the average row length to estimate the percentage done or left to be done.
MariaDB has some progress metering for the following:

ALTER TABLE
ADD INDEX
DROP INDEX
LOAD DATA INFILE
CHECK TABLE
REPAIR TABLE
ANALYZE TABLE
OPTIMIZE TABLE

I have discussed subject before in terms of MySQL operations

May 02, 2012 : How can I monitor the progress of an import of a large .sql file?
Jan 17, 2012 : Adding an index very slow...is there a mysql cmd to get an ETA or show progress?

If you are trying to reduce the size of ibdata1 for InnoDB, dropping tables simply leaves big gaps in ibdata1. You must perform a full Cleanup of InnoDB Infrastructure.

Apr 01, 2012 : Is innodb_file_per_table advisable?
Mar 25, 2012 : Why does InnoDB store all databases in one file?
Mar 29, 2011 : Maximum table size for MySQL database server running on Windows (NTFS) Table type InnoDB
Feb 04, 2011 : MySQL InnoDB - innodb_file_per_table cons?
Oct 29, 2010 : Howto: Clean a mysql InnoDB storage engine?

UPDATE 2012-12-21 19:30 EDT

Since this database uses innodb, will there be a growing temp table I can look for?

Let's say you want to run
OPTIMIZE TABLE mydb.mytable;

Look at two scenarios involving innodb_file_per_table and this OPTIMIZE TABLE
SCENARIO #1 : innodb_file_per_table disabled
Running OPTIMIZE TABLE will cause all the data and index pages for mydb.mytable to be appended contiguously to ibdata1. This will leave the space previously occupied to add further to the fragmentation madness.
In this scenario, the temp table would exist as InnoDB, and it would materialize in ibdata1. Thus, there would be nothing to visually monitor.
SCENARIO #2 : innodb_file_per_table enabled
For each InnoDB table mydb.mytable you would have

/var/lib/mysql/mydb/mytable.frm
/var/lib/mysql/mydb/mytable.ibd

The .ibd file contains the data and index pages for the table.
Running OPTIMIZE TABLE will cause all the data and index pages for mydb.mytable to be written to an external .ibd file that you can see in Windows Explorer.
CAVEAT
Enabling innodb_file_per_table and running OPTIMIZE TABLE will never reclaim the space formerly occupied within ibdata1. You will have to carry out the InnoDB Cleanup to recreate ibdata1 in such a way that data and indexes never, ever again reside in ibdata1.
CONCLUSION
As stated earlier, MariaDB can monitor OPTIMIZE TABLE;
If you want something you can monitor and you have innodb_file_per_table enabled, you can replace
OPTIMIZE TABLE LOGTABLEFOO1,LOGTABLEFOO2,LOGTABLEFOO3;

with the following mechanical equivalent:
CREATE TABLE LOGTABLEFOO1_NEW LIKE LOGTABLEFOO1;
INSERT INTO LOGTABLEFOO1_NEW SELECT * FROM LOGTABLEFOO1;
ALTER TABLE LOGTABLEFOO1 RENAME LOGTABLEFOO1_OLD;
ALTER TABLE LOGTABLEFOO1_NEW RENAME LOGTABLEFOO1;
DROP TABLE LOGTABLEFOO1_OLD;
ANALYZE TABLE LOGTABLEFOO1;

CREATE TABLE LOGTABLEFOO2_NEW LIKE LOGTABLEFOO2;
INSERT INTO LOGTABLEFOO2_NEW SELECT * FROM LOGTABLEFOO2;
ALTER TABLE LOGTABLEFOO2 RENAME LOGTABLEFOO2_OLD;
ALTER TABLE LOGTABLEFOO2_NEW RENAME LOGTABLEFOO2;
DROP TABLE LOGTABLEFOO2_OLD;
ANALYZE TABLE LOGTABLEFOO2;

CREATE TABLE LOGTABLEFOO3_NEW LIKE LOGTABLEFOO3;
INSERT INTO LOGTABLEFOO3_NEW SELECT * FROM LOGTABLEFOO3;
ALTER TABLE LOGTABLEFOO3 RENAME LOGTABLEFOO3_OLD;
ALTER TABLE LOGTABLEFOO3_NEW RENAME LOGTABLEFOO3;
DROP TABLE LOGTABLEFOO3_OLD;
ANALYZE TABLE LOGTABLEFOO3;

Please keep in mind the OPTIMIZE TABLE is nothing but ALTER TABLE ... ENGINE=InnoDB; followed by ANALYZE TABLE.
With these steps, you know in advance what the temp tables are

LOGTABLEFOO1_NEW.ibd
LOGTABLEFOO2_NEW.ibd
LOGTABLEFOO3_NEW.ibd

Simply Monitor Each File's Existence and Size Until it Disappears.


Trying to combine one row from one table with mulitple rows from another table but not sure if I'm doing it right? The Situation

I have two tables unit and unit_translations defined as:

CREATE TABLE `unit` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(255) NOT NULL COMMENT 'Title of unit',
  `description` longtext NOT NULL COMMENT 'Description of the unit',
  `idLevel` int(11) default NULL COMMENT 'Foreign key referencing level id',
  `position` smallint(6) NOT NULL COMMENT 'List sort order',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `title` (`title`),
) ENGINE=InnoDB;

Table has this data:

#id, title, description, idLevel, position
'1', 'Unit 001', 'Unit 001', '1', '1'

and

CREATE TABLE `unit_translations` (
  `id` int(11) NOT NULL auto_increment,
  `idUnit` int(11) default NULL COMMENT 'Foreign key referencing unit id',
  `locale` varchar(8) NOT NULL COMMENT 'Translation locale',
  `field` varchar(32) NOT NULL COMMENT 'Field in object being translated',
  `content` longtext COMMENT 'Translated value',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `lookup_unique_idx` (`locale`,`idUnit`,`field`),
  KEY `IDX_14213810232D562B` (`idUnit`),
  CONSTRAINT `FK_14213810232D562B` FOREIGN KEY (`idUnit`) REFERENCES `unit` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

That holds this data:

# id, idUnit, locale, field, content
'1', '1', 'en', 'title', 'A Day at the Beach'
'2', '1', 'en', 'description', 'How a typical day at the beach could be'

(Don't get caught up in the data it is silly right now)

The Task

Create a Select that selects everything from unit but replaces the content in unit.title and unit.description with the translated value from unit_translations. So the result would like like:

# id, title, description, idLevel, position
'1', 'A Day at the Beach', 'How a typical day at the beach could be', '1', '1'

I've managed to accomplish this with the following SQL:

SELECT u.id, trans1.trans as title, trans2.trans as description, u.idLevel, u.position
    FROM unit u
    JOIN (SELECT t.idUnit as trans_id, t.content as trans FROM unit_translations t JOIN unit u ON t.idUnit = u.id WHERE t.field = "title" AND t.locale ='es') AS trans1 ON u.id = trans1.trans_id
    JOIN (SELECT t.idUnit as trans_id, t.content as trans FROM unit_translations t JOIN unit u ON t.idUnit = u.id WHERE t.field = "description" AND t.locale ='es') AS trans2 ON u.id = trans2.trans_id

The Question:

Is there a build-in way to fetch the two rows/values form unit_translations and merge them into the one row from unit? Or is what I have come up with the only way to do this?

It's not the only way to do it, but the other solutions won't perform any better.  You could create a function and use that for the values of your column, which might make your query easier to read.  But it won't perform better and depending on your scale, it may perform quite a bit worse.


scaling options for mysql on linux vm (ubuntu) : how to scale when the RAID 0 is out of space I was asked to give a scaling road map for our mysql database.

Currently, I have a single mysql instance v5.6.28 running on an ubuntu VM on azure. It has a (software) RAID 0 disk made with two 1TBs.

My question is, when this 2TB disk gets filled up, what am i supposed to do?
I am thinking that my options would be

add another RAID 0.
add another mysql VM to existing infrastructure
migrate to mysql cluster
???

I want to know what is the recommended method to scale when the tables grow to an extent that the existing RAID runs out of space.

You have to ask yourself the question "How much is my data worth to me/the company?".

Cheap solution:

You could buy 2 2TB disks to replace the 2 1TB ones you already have. That would be a stop gap IMHO. RAID0 really doesn't cut the mustard these days with bit-rot and 24x7 operation. If one of your disks goes down, you'll place a lot of extra strain on the other disk (possibly provoking its failure - then you're bunched :-( )

More expensive, but worth it (IMHO):

Personally, I'd use a FreeNAS box with RAID-Z also striped and mirrored - or possibly Z-3. If you have the expertise in-house, great, but if not, I'd recommend iXsystems (I am not associated with this company, just have heard good reports from people who work in this area - plus they develop FreeNAS).


Relation does not exist for some users As a user with admin privileges in postgres, I can psql database_name and then invoke \dt to see a list of relations.  As the same (unix) user, I execute psql -h localhost -U non-privileged-user table_name, and \dt reports NO relations found.  The only host entries in pg_hba.conf are of the form host    analytics             all             127.0.0.1/32            md5 (where 127.0.0.1 is the first entry).

Where are the relations?  Why are they not visible to the alternate user.  What schema tables should I look in to debug.  Also, note that grant select on table_name to non-privileged-user works just fine.

What you see typically happens when the non-privileged user doesn't have schema level privileges.  In this case, they cannot list the objects inside the given schema - with the command you tried.  See the below example:

-- as a superuser, for example
CREATE DATABASE test;
\c test
CREATE TABLE public.blah (id integer);
REVOKE ALL ON SCHEMA public FROM PUBLIC;

CREATE USER u WITH PASSWORD 'u'; -- don't do this in production

-- connect as u
\c test u
\dt
No relations found.

-- but:
\dt public.*
       List of relations
 Schema │ Name │ Type  │ Owner
────────┼──────┼───────┼────────
 public │ blah │ table │ dezso

See what the documentation says:

  USAGE

  For schemas, allows access to objects contained in the specified
  schema (assuming that the objects' own privilege requirements are also
  met). Essentially this allows the grantee to "look up" objects within
  the schema. Without this permission, it is still possible to see the
  object names, e.g. by querying the system tables. [...]

\dt does the latter, namely queries pg_class and pg_namespace to find matching objects.  Apparently, when the schema name is not specified, the system wants to hide objects from view.


MongoDB Balancer:move data inside replication set or across replication set? After reading the document of mongodb on the balancer topic , I have two questions:

1.If I set Balancer on , will the db operation efficiency will be
    influenced ?

2.Still ,I cannot understand what data is moved from where to where by balancer.I know that for each shard ,mongodb has a replication set to back up data.Then ,what does balancer do?It move data inside a replication set(from one replication set member to another ,but both belongs to the same replication set) or it move data from one shard to another
 shard(from one replication set to another)?

1 - Yes, the balancing operation can affect the performance, that's why you can schedule the balancing window. By default, the balancer is enabled and may run at any time.

2 - The balancer moves data chunks between shards.

Just to clarify, each shard is a replica set. All members of the replica set (the primary and N secondaries) have the same data (replicated). Therefore, each shard has a different subset of the total data.


64 Availability groups max on SQL STD : Failure when attempting to create a new NetBIOS interface while bringing a resource online I was patching our DB Servers this morning and encountered a really strange error when failing over some Availability Groups :

In our environment, we are running a somewhat unconventional setup, but it works well.
We have 85 Databases, running SQL Server Standard, so each DB has its own Availability Group.
What we do as a default, is to actually "Load Balance" the availability groups between the 2 servers. This means by default, we only have about 42 databases on each node, until patching where I failed all the DB's over to one node, and that when I received the error on about 24 databases.

In initial investigations, I found that you can only have 64 NetBIOS names per resource. Are Availability Groups not their own resource?
In another article, they talk about ensuring you have no duplicate IPs or DNS names. I can confirm that we have no duplicates of either.

Is it a possibility, that on SQL Standard, you can only have 64 availability groups per database?

Please help with any potential resolutions.
Thank you very much for your time.

The Microsoft EnableNetBIOS documentation states:

  There is a limit of 64 NetBIOS devices in the cluster.

You should be able to just ignore this--disabling NetBIOS on clusters is recommended and SQL Server doesn't need it.  See Speeding Up Failover Tips-n-Tricks.


Is this SP safe to SQL Injection? CREATE PROCEDURE [sp_Test] (
     @param nvarchar(Max)
) AS BEGIN

DECLARE @Output nvarchar(Max) =
N’Select ‘ + @param

Select @output
Return

Intended Use
exec sp_test ‘5’
Returns “select 5”
Malicious Use
exec sp_test ‘5; drop database’
Returns(would be safe):
“select 5; drop database“
—-OR—-
Returns(not safe):
“select 5”
...but also actually dropping the database
MS SQL Server

All your stored procedure is doing is to put “select” in front of whatever is passed in. It doesn’t run anything.
But presumably you’re going to then ask something else to run whatever comes back, and that could be unsafe. If you’re wanting a very dynamic querying environment, then read what Erland Sommarskog has to say on the matter at http://www.sommarskog.se/dynamic_sql.html


Dynamic result in select statement mysql I want to get max value as maxvalue column of a column, table returned by the following query.

IE: if the following query returns table name test_tab and column name as
test_col then the following query should return max value of the table test_tab and column  test_col .

which would give me the maximum value of auto incremented columns in my database with table name and column name.

SELECT c.table_name,c.column_name ,'' maxvalue
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE  EXTRA = 'auto_increment'

I could not understand your requirement. Hope it works :

select t.table_schema,t.table_name,t.AUTO_INCREMENT
from information_schema.TABLES t,information_schema.COLUMNS c
where c.EXTRA = 'auto_increment' and t.table_schema=c.table_schema and t.table_name=c.table_name


Is Index causing timeouts? Recently, while fine tuning the db for better perf, I changed a couple of indexes on tables which were hit very often.

I am using the following query to keep tab on index stats

SELECT DISTINCT Object_name(sis.OBJECT_ID) TableName,
                si.name                    AS IndexName,
                sc.Name                    AS ColumnName,
                sic.Index_ID,
                sis.user_seeks,
                sis.user_scans,
                sis.user_lookups,
                sis.user_updates
FROM   sys.dm_db_index_usage_stats sis
       INNER JOIN sys.indexes si
         ON sis.OBJECT_ID = si.OBJECT_ID
            AND sis.Index_ID = si.Index_ID
       INNER JOIN sys.index_columns sic
         ON sis.OBJECT_ID = sic.OBJECT_ID
            AND sic.Index_ID = si.Index_ID
       INNER JOIN sys.columns sc
         ON sis.OBJECT_ID = sc.OBJECT_ID
            AND sic.Column_ID = sc.Column_ID
WHERE  sis.Database_ID = Db_id('mydb')

Earlier, there was hardly any user_seeks and most of the queries did a user_scan. As I changed the indexes, the user scans has gone down to zero and all the queries are using user_seek. Now I have read many places that index seeks are desired over index scans, so with that thought my db perf should be better.

But I am getting a lot of SQL Timeouts on the Asp.NET web app. Given that the index I defined contains 11 columns and a larger number of included columns, I was wondering whether maintaining the index itself has affected the perf?

After creating the new index, the user seeks to user updates ratio is 4489587/111656.

You must get execution plan of each slowly query first, and survay required Index.

for change timeout time use below address in SSMS:

Tools\Options\Designers\Trasnaction time-out after

Default value for timeout is 30s and you change this value to about 6000s and then create own index.
The above path is in SQL Server Management Studio 2008 R2


MySQL dump tables from remote server I'm trying to dump all tables of a specific MySQL database into each file(s) per table from a remote server.

I tried to use mysqldump with option --tab=dir_name as the output directory, but it seems only work locally. When I do it with option --host=remote_db_ip to connect to remote DB server, it only produces .sql files (with only table structure inside) on my server, and throws the following errors because it is using SELECT INTO OUTFILE which is trying to find that output path on the remote server machine.

mysqldump: Got error: 1: Can't create/write to file '/output/path/table_name.txt' (Errcode: 2 - No such file or directory) when executing 'SELECT INTO OUTFILE'

P.S. I know one workaround that is to dump to the remote DB server first then transfer back to my server, but the target database is huge and there is not enough extra disk space for storing dump output on the remote DB server.

MySQL version: 5.6

You could temporarily set up a sshfs mount of the target directory and use --tab=dir_name on that mounted directory.


Recover MySQL database from data folder without ibdata1 from ibd files My WAMP directory accidentally get deleted by another user. Only data folder in MySQLis available. And, in that only database-folders (folders in "\bin\mysql\mysql5.6.12\data\" with name of databases) are available. All files including "ibdata1" in root of "\bin\mysql\mysql5.6.12\data\" are also deleted.

The database folders contains files with below extensions only.

*.frm, *.ibd

and "db.opt" file.

How the databases can be recovered?

I already have tried to recover bdata1. But, unable to get it back. And, some database contains MYISAM also.

MyISAM

For a MyISAM table mydb.mytable, you should have three files

\bin\mysql\mysql5.6.12\data\mydb\mytable.frm
\bin\mysql\mysql5.6.12\data\mydb\mytable.MYD
\bin\mysql\mysql5.6.12\data\mydb\mytable.MYI

They should already be accessible as a table since each file contains needed data, metadata, and index info. Collectively, they form the table. There are no external storage engine mecahnisms to access.

InnoDB

Take a look at this Pictorial Representation of InnoDB

The only thing that attaches ibdata1 to the .ibd files is the data dictionary.

Your mission, should you decide to accept it, is to create each table and swap in the .ibd

Before you do anything, make a full copy of "\bin\mysql\mysql5.6.12\data" to another

Here is a sample

Suppose you have a database mydb with the table mytable. This means

You have the folder \bin\mysql\mysql5.6.12\data\mydb
Inside that folder, you have

mytable.frm
mytable.ibd

You need the .frm. If you look at my post How can extract the table schema from just the .frm file?, you can download a MySQL utility that can generate the SQL needed to create the table.

You should now do the following

Move mytable.ibd to \bin\mysql\mysql5.6.12\data
Run the SQL to create the InnoDB table
Login to mysql and run ALTER TABLE mydb.mytable DISCARD TABLESPACE; (This will delete \bin\mysql\mysql5.6.12\data\mydb\mytable.ibd)
Copy \bin\mysql\mysql5.6.12\data\mytable.ibd into \bin\mysql\mysql5.6.12\data\mydb
Login to mysql and run ALTER TABLE mydb.mytable IMPORT TABLESPACE; (This will register \bin\mysql\mysql5.6.12\data\mydb\mytable.ibd into the data dictionary)

After this, the table mydb.mytable should be fully accessible. You can test that accessibility by simply running:

SELECT * FROM mydb.mytable LIMIT 10;

Give it a Try !!!

DRINK (Data Recovery Incorporates Necessary Knowledge) Responsibly


Select query slow in the big table because of expired stats in Microsoft SQL Server I have an application to select/search records from the database (application and database in separate servers).
In the database, one table only is fast growing table (more than 2 Millions per day).
There is no update will happen in this table all are insert only.
Created index for this table and everything is working fine.
I try to install another SQL Server instance in the database server.
After that, I realized that the select is very slow in the big table.
I uninstall the another instances of SQL Server.
Still the select is very slow in the particular table.
Search in the some forum, they mentioned that need to update stats (EXEC sp_updatestats).
After executed this query EXEC sp_updatestats the select query is very fast.
But after 10 days, the same issue (slowness in select query) occurred again.
I created daily jobs to update the stats, after 10 days, the issue observed again.
After update the stats manually, it is very fast.

The whole issue observed after I installed the second instance of SQL server in the same server.
Is my observation correct?
Even I uninstall the seconds instance, the slowness issue observed frequently.
Kindly give me your valuable suggestion.  Thanks in advance.

Just looking at the details you have provided, there could be quite some reasons. we can't just predict the exact possible reasons unless you share those select query or queries running slow or you share the execution plans.
Looking at first glance it looked you might have issue with skewed stats. But if you are saying update stats is running daily may be you can try with better sampling rate to see if that helps.
However based on what you are saying that things looked better after manual update stats, this is very much possible that process gets recompiled or bad plan might be getting evicted from cache from that update stats.
My personal experience says you might also been seeing something called parameter sniffing-. There are lot of great articles over the web in case you are experiencing the same.
If you can add more details what is slow with execution plans for pre and post changes can confirm above possibilities.


Lock a Database Record For a User I have a application development requirement to lock a database record for a user while the user is inside the record. Then unlock it after they are done. Is there a way to do this at the database level instead of the code level? Should this be done at the database level or code level?

When I say lock I mean other will be able to view but not edit.

Please see this article for details.  Level of concurrency is controlled by isolation level you chose in SQL server or in your connection. Unless specified or changed Read committed is the default isolation level when you install SQL server and do not overwrite in your transaction.

  Transactions specify an isolation level that defines the degree to
  which one transaction must be isolated from resource or data
  modifications made by other transactions. Isolation levels are
  described in terms of which concurrency side effects, such as dirty
  reads or phantom reads, are allowed.

That being said despite any isolation level you chose data modification is always protected.

  Choosing a transaction isolation level does not affect the locks that
  are acquired to protect data modifications. A transaction always gets
  an exclusive lock on any data it modifies and holds that lock until
  the transaction completes, regardless of the isolation level set for
  that transaction. For read operations, transaction isolation levels
  primarily define the level of protection from the effects of
  modifications made by other transactions.

You can still encounter lost updates if you not at least at Repeatable read isolation level.  See this article for details about repeatable read.

This is a good article explaining isolation level with examples.


SQL Availability Groups: Can clients use cluster name instead of listener name(s)? When using Availability Groups, is it possible to simply just configure all clients/applications to connect to the database(s) by using the cluster name/IP instead of the AG listener for the AG that the database resides in?

If it is possible, would it be advised against?

Example:

3 node cluster with cluster name of "SQLCLUSTER2014" and cluster IP of 10.0.1.10
2 nodes located at main site, one node off site at a DR site
2 AG's, with listeners called "AG1_LISTENER" and "AG2_LISTENER"
Create a DNS A record for "SQLCLUSTER" that points to 10.0.1.10 (the cluster IP)
Configure all clients/application to point to "SQLCLUSTER" in their connection string

So when you upgrade to SQL 2016 and move your databases to the new server, you can just point your A record to the new cluster IP and not have to change connection strings in applications.

It seems like this would be a better setup because it allows you to utilize DNS CNAME's or A records to mask server moves, which I've done to great success on systems that don't use AG's.

Is there any reason not to do this?
Any potential issues reaching the DR site if the 2 main nodes go offline?

Thanks.

First, when using AGs, you generally are not using a clustered instance of SQL Server, you are using multiple standalone SQL Servers. You are able to technically connect to each individual SQL Server in the Windows Cluster Separately. Each having their own configuration and resources.

Secondly, each group has its own name in AD as a computer object and its own IP address. These are registered in DNS. These are different than the cluster name. The listener name is a special object to route traffic based on incoming query type. For instance, if you configured your AG to use a secondary node for read only queries, the listener name is what routes that query to the secondary node, not the cluster group. Backups are also attached to that listener name for routing traffic from the primary to the secondary node.

If you are monitoring AG health, it will be based off of the listener name/object, not the cluster name. It is possible to have a 3 node windows cluster, but only have an AG involving 2 nodes. The cluster name would not properly handle that.

Adding further complexity to this question. With SQL 2016, you have distributed availability groups which uses separate clusters on different networks or domains. Those separate clusters are clusters all to them selves and are only joined via the listener name for failover purposes.

Lastly, if you chose to extend your AG to Azure, you will need to use the listener when connecting to the "active" database so your query can route to the appropriate node. The cluster resource name does not know about the Azure secondary node.


Disk block size for mongodb deployment Why does Mongodb production notes have no recommendations about the disk block size? AFAIU, disk block size would have a considerable impact on a throughput intensive application. Does disk block size have any impact on mongodb performance in practice?

Why does Mongodb production notes have no recommendations about the
  disk block size?

As per MongoDB documentation Configure Block Size in a Blockstore When you back up your deployment to a Blockstores, the Backup Daemon first takes a snapshot of the head database. It then breaks this snapshot into blocks and writes these blocks to the blockstore.

The different blockstores have different default sizes and ranges of sizes for blocks.

Blockstore Type Block Size Range    Default Size
MongoDB          64 KB to 15 MB          64 KB
S3               64 KB to 16 MB          16 MB

In general, increasing block size results in faster snapshots and restores, but requires more disk space. These competing factors should be considered when determining if you wish to tune the block size.

There are two exceptions where performance improves as the block size increases without requiring additional disk space:

Workload                      Impact
Update- and Delete-Intensive  No matter how small you make the block size, the entire block file is rewritten. Since the entire file is always rewritten, there is no difference in storage space if you change the block size.
Insert-only                   The existing blocks never change. As you increase block size, block management is simplified. This enables the best possible performance on snapshot and restore.

The range of minimum block sizes include:

64 KB
128 KB
256 KB
512 KB
1 MB
2 MB
4 MB
8 MB
15 MB (MongoDB) or 16 MB (S3)

  Does disk block size have any impact on mongodb performance in
  practice?

As per Blog from @Colt McAnlis, here  “The default block size on volumes is 4K. For throughput-oriented workloads, values of 256KB or above are recommended.”

It’s important to observe the following formula wrt disk performance:

Throughput (M/sB) = IOPS * Blocksize.

How this evens out is that IOPS and block Size tend to have an inverse relationship to each other. As block size increases, it takes longer latency to read a single block, and thus the # of IOPS decreases. Inversely, smaller block sizes yield higher IOPS.

For further your ref here


Azure Failover Clustering vs On Premise Failover Clustering Azure AlwaysOn FCI Failover Clustering IaaS VMs seem to have a different diagram compared to On-Premise Failover Clustering.

Azure has two storage for 2 Vms; Traditional On-Premise contains 1 Shared storage.

(a) Does this mean Azure Failover Clustering not only provides server level protection, but also database level protection, and Availability Groups are no longer required?

(b) Additionally, should Azure Failover Clustering server nodes be in the same zone, or would putting them in different zones, affect performance/server speed?

MSDN Configure SQL Server Failover Cluster Instance on Azure Virtual Machines

https://blogs.technet.microsoft.com/askcore/2015/06/24/windows-server-failover-cluster-on-azure-iaas-vm-part-1-storage/

Azure has two storage for 2 Vms; Traditional On-Premise contains 1 Shared storage.

Did you read The link you shared in the question, BTW I cannot see the diagram you posted in the question when I referred the link. I will quote from the same link.

  This article explains how to create a SQL Server Failover Cluster Instance (FCI) on Azure virtual machines in Resource Manager model. This solution uses Windows Server 2016 Datacenter edition Storage Spaces Direct (S2D) as a software-based virtual SAN that synchronizes the storage (data disks) between the nodes (Azure VMs) in a Windows Cluster. S2D is new in Windows Server 2016.

So the concept here is the two machines are using 2 different disk which is made as one disk and presented to FCI as single storage via Storage Direct Spaces technology.. Azure does not have concept of shared storage so you need some tool to present disks as shared storage to FCI. What it does is

S2D synchronizes the data on the data disk and presents the synchronized storage as a storage pool.
The storage pool presents a cluster shared volume (CSV) to the failover cluster.
The SQL Server FCI cluster role uses the CSV for the data drives.
An Azure load balancer to hold the IP address for the SQL Server FCI.
An Azure availability set holds all the resources.


Problem with LISTEN - NOTIFY mechanism in PostgreSQL I'm struggling with PostgreSQL LISTEN - NOTIFY mechanism.
I open two consoles and connect to my database using psql client:

psql -U postgres test_db

In the first console I subscribe to some notification:

test_db=# listen event;
LISTEN

In the second console I'm trying to send a notification:

test_db=# notify event;
NOTIFY

And nothing happens in the first console. But if I send a notification from console 1, I get it and also I get all previous notifications from console 2.

I use PostgreSQL 9.2.1 under CentOS 6.5 x64.

This is a psql issue.  psql is waiting for you to enter something on its command line. It only checks for notifications from the server when it has a reason to contact the server, and you haven't given it a reason.

They could have implemented it differently, so that it uses a select loop to wait on either the keyboard or the server, whichever becomes interesting first.  But that would make things much more complicated and it is not the way it was done.

This is the same reason that, if you bring down the server, psql doesn't notice until you try to do something that would try to talk to the server.

Note that the same issue will exist with clients/libraries other than psql, and different libraries solve (or fail to solve) it in different ways.


MS SQL Server 2016 - Single Partitioned Table vs Multiply Separated Tables We need to store a lot of logs in MS SQL Server 2016 Database for 1-1.5 year.
Income: 50 Gb of logs per month (~ 50 millions rows per month), we store all data in 6 months and shrink 50% of this data for older records.
Prerequisites:

Required different indexes for last 1st month, 2nd month and others.
Required data compression for big XML/Text data older than 2 months.
For now - Required data shrink for data older than 6 months (just remove 50-60% records with big data (XML/Text) from table), but it will be better just heavily compress rows and don't remove it.

Main Use-Case: search for 100-200 (sometime 1000) rows by some IDs or string filters in SQL IN manner (sometime LIKE).

Exist two approaches for partition:

"Old style" - Separated Tables for each period (I think without common View in our case), month as example (Log_0117, Log_0217, ...).

Pros: Fully independent management and schema (for evolution), independent indexes and query/plans caching, as expected - more performance and efficiency, simple to implement, less magic and more controlable.
Cons: Many-many tables (8 for every period), complex queries (especially for cross-period queries).

"Modern style" -
Partitioned tables and indexes.

Pros: Easy management, just one table for all periods, in theory simpler queries.
Cons (as I found by some articles): Strong requirements for partitioned indexes (by normal, all indexes must contain partition-column (therefore more memory required), such indexes require rebuild after partition switching, hard to change schema/indexes for different periods, more magic and less controlable.

My questions for community:

Is it really builtin Partitioned tables/indexes make developing more simple?
Does it have really inconvenient limitations now in MS SQL Server 2016 (as Enterprise, as or everything not so bad?

UPD1:
Columnstore Indexes - another interesting option from @DavidBrowne, but as described at MSDN:

  Columnstore indexes give high performance gains for queries that use full table scans, and are not well-suited for queries that seek into the data, searching for a particular value.

Unfortunately, we mostly search for 100-200 records in logs, rather than calculate complex statistics, but I save it in the mind for future, maybe will be helpfull.

UPD2: @DanGuzman, I meant Aligned indexes when said about some requirements - https://technet.microsoft.com/en-us/library/ms187526(v=sql.105).aspx:

  Partitioning NonClustered Indexes

  When partitioning a unique nonclustered index,
  the index key must contain the partitioning column. When partitioning
  a nonunique, nonclustered index, SQL Server adds the partitioning
  column by default as a nonkey (included) column of the index to make
  sure the index is aligned with the base table. SQL Server does not add
  the partitioning column to the index if it is already present in the
  index.

It's also heavily recommended not to have Non-Aligned indexes 1) partitioning big tables - indexes , 2) Performance Impacts of Partitioned Aligned Indexes.

It means - if we want to make queries faster just for last month, we need to: 1) cover all table by this index (but it requires really more disk/memory) or 2) organize long maintenance window for partition switching and indexes rebuilding.

Is it really builtin Partitioned tables/indexes make developing more
  simple?

Compared to developing against independent tables that do not have view abstraction, partitioned tables are much easier to query because partitioning is transparent to application queries.

Partitioned table sliding window maintenance involves partition TRUNCATE (or SWITCH in the pre-SQL 2016 world) plus a partition function SPLIT/MERGE. Older data/index partitions can be compressed during the maintenance. One should also update partition stats after a TRUNCATE or SWITCH. DDL scripts for these operations are easily automated and less complex than scripts that create/drop/compress separate tables and indexes dynamically.

  Does it have really inconvenient limitations now in MS SQL Server 2016
  (as Enterprise, as or everything not so bad?

The biggest limitation with table partitioning is that the partitioning column must be part of all unique indexes and constraints.  As long as the tables you plan to partition table fit within these considerations, partitioning provides a number of manageability benefits without changes to app code. Note that Microsoft recommends partitioned tables over partitioned views when possible. Beginning with SQL Server 2016 SP1, partitioned tables are available in Standard Edition.

Columnstore compression often provides much higher compression that page or row compression on a rowstore. You can also create partitioned non-clustered indexes (compressed or not) on a partitioned columnstore to improve performance of queries that return relatively few rows. I suggest you consider both table partitioning and columnstore.

I'm not sure I fully understand why you would want to make queries faster only for last month. I'm guessing you just don't want the space overhead for indexes on data that are infrequently used. Given the low cost of storage nowadays, I wouldn't bother introducing additional complexity to save some index space. I suspect compression, especially columnstore, will eliminate the need to jump through such hoops.

If you are still unsure of the path forward, I suggest you develop prototypes of the candidate designs and choose the one that best addresses your needs with the least complexity.


Memory is not released after the execution stored procedure followed by a process end I am running a stored procedure in query builder and closing query builder. However, the memory allocated doesn't get released. The stored procedure uses temp tables and drops them immediately after its use. These tables get created in a loop, get truncated, and dropped immediately. Any table that is created gets dropped immediately. The purpose of the stored procedure is only to make updates after a calculation using these temp tables.
After each execution, about 1.4 GB memory increases and never get released.
QUESTIONS

What factors should be considered when creating temp tables in a stored procedure ?
How can I claim memory after the stored procedure gets executed ?

SQL Server doesn't release the memory it consumes unless it gets an order from the operating system to do so.

If you need, you can set the maximum amount of memory SQL Server will consume.


Schema size [PG TOAST] - PostgreSQL 9.2 I've got the following query to measure the schema's size on my DB:

WITH schema_size AS (
SELECT
tab.table_catalog AS database_name,
tab.table_schema AS schema_name,
tab.table_name,
pg_total_relation_size(table_schema || '.' || tab.table_name) AS table_size_total,
pg_relation_size(table_schema || '.' || tab.table_name) AS table_size
FROM information_schema.tables tab
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
), pretty_size AS (
SELECT
database_name,
schema_name,
pg_database_size(database_name) AS database_size_bigint,
pg_size_pretty(pg_database_size(database_name)) AS database_size,
sum(table_size_total) AS schema_size_bigint_total,
pg_size_pretty(sum(table_size_total)) AS schema_size_total,
sum(table_size) AS schema_size_bigint,
pg_size_pretty(sum(table_size)) AS schema_size
FROM schema_size
GROUP BY database_name, schema_name
)
SELECT
database_name,
schema_name,
database_size,
schema_size_total,
schema_size,
((schema_size_bigint_total * 100) / database_size_bigint) AS perc_total,
((schema_size_bigint * 100) / database_size_bigint) AS perc
FROM pretty_size;

However, when running it I get this information:

My DB is 2 TB and that schema is 1.7 TB.

Question:

Is that related to the toast tables? How can I decrease the size of it?

The function pg_total_relation_size() returns

  Total disk space used by the specified table, including all indexes and TOAST data

Given the disproportionate size between the tables and total relation size (100 to 1), it is probably mainly due to TOAST data. TOAST data is already compressed (LZ), so it will be unlikely to be able to compress it any further.

If you have a lot of duplication, or want to store blobs outside the database, you could resort to this: Storing blobs outside the database.


Max damage from an unlogged table? If my database goes down (let's say memory maxed out) while i'm creating an unlogged table, is the damage limited to just losing that unlogged table or could it prevent recovery of the rest of the database?

While operations on the data in an unlogged table are unlogged the creation of the unlogged table is actually logged as this is an operation on the system catalog. Thus normal recovery would happen. So depending on how far it actually was it would complete creation of the table or discard the action during recovery.