Oracle Custom Sequence of a partitioned group (Insert trigger) Please help, need an insert trigger to insert the group_ID (next sequential number) of a partitioned group.  Much like a dense_rank of repeating group of names.
When a new record is created, if the name exists, then the group_id will be this name's group_id.  If the name does not exist, then the group_id will be auto generated to a new number that hasn't been used.

Not sure what this is called.

Create table T1 (
Group_Name Varchar2(50)
,Group_ID Number
);
commit;

Insert into T1 (Group_Name, Group_ID) Values ('ABC', 1 );

Insert into T1 (Group_Name, Group_ID) Values ('ABC', 1 );

Insert into T1 (Group_Name, Group_ID) Values ('ABC', 1 );

Insert into T1 (Group_Name, Group_ID) Values ('EFG', 2 );

Insert into T1 (Group_Name, Group_ID) Values ('EFG', 2 );

Insert into T1 (Group_Name, Group_ID) Values ('HIJ', 3 );

Insert into T1 (Group_Name, Group_ID) Values ('HIJ', 3 );
COMMIT;

SELECT * FROM T1;

GROUP_NAME  GROUP_ID
----------  ---------
ABC         1
ABC         1
ABC         1
EFG         2
EFG         2
HIJ         3
HIJ         3

Need An insert trigger to insert the GROUP_ID based on this logic:

if the inserted value for GROUP_NAME already exists in T1, then GROUP_ID gets its already defined GROUP_ID for GROUP_NAME
if GROUP_NAME does not exist in T1, insert the next sequential number for a this new "Grouping" for GROUP_ID.

Insert examples and what GROUP_ID should be are below:

INSERT INTO T1 (GROUP_NM) VALUES('ABC');

*** Since 'ABC' is found in the table with Group_ID of 1, the insert trigger, should evaluate and update the table of GROUP_ID to 1.

INSERT INTO T1 (GROUP_NM) VALUES('PEP');

*** Since 'PEP' group name inserted is not found in the table T1, the trigger should auto generate the next value of 4 for the GROUP_ID.

Not Possible

Your suggested Implementation of your Business Requirement is not possible.

You cannot select from table T1 from within a trigger on T1.  There are hacks around such limitation.  But, that is what they are: hacks.  One of them has a side effect of preventing you from performing a rollback.

In all cases: you'll still want to define a sequence for group_id.

create sequence group_id_seq;

Work Around - Correct your schema design

If you want to ensure that each Unique group_name contains a Unique group_id, then you need to create a table that enforces such data logic.

create table unique_groups (
   group_id    int generate by default on null as identity
  ,group_name  varchar2(50) not null
  ,constraint unique_group_pk primary key (group_id)
  ,constraint unique_group_uq1 unique (group_name)
);

alter table t1
  add constraint t1_fk1 foreign key (group_id) references unique_group( group_id );
alter table t1
  add constraint t1_fk2 foreign key (group_name) references unique_group( group_name );

Then, and only then, you can use a trigger.  (I'd still not advise it.)

create or replace
trigger t1_tr
before insert
on t1
for each row
begin
  select a.group_id into :new.group_id
  from unique_groups a
  where a.group_name = :new.group_name;
exception
  when no_data_found then
    insert into unique_groups (group_name)
    values (:new.group_name)
    returning group_id into :new.group_id;
end;
/

Work Around - TAPI

One work is the use of Table APIs (TAPI).

This is a Package that performs all necessary tasks to perform an insert,update, or delete.  Instead of your application performing the DML, the application calls the TAPI to perform the DML.

If necessary, you can wrap the Package with a VIEW and an INSTEAD OF trigger.

Package Spec:

create or replace package t1_tapi_pkg as
    -- I like to customize known exceptions for the application.
    data_corruption_detected   exception;
    PRAGMA EXCEPTION_INIT (data_corruption_detected, -20000);

    table_in_use_try_again     exception;
    PRAGMA EXCEPTION_INIT (lock_timeout, -20001);

    lock_timeout               exception;
    PRAGMA EXCEPTION_INIT (lock_timeout, -54);  -- These are caught and re-raised as -20001

    function ins (
        group_name in t1.group_name%type
    ) return t1.group_id%type;

    -- update logic has not been defined.

    -- regular SELECT and DELETE statements can be used.

end;
/

Package Body:

create or replace
package body t1_tapi_pkg
as

    function ins (
        group_name in t1.group_name%type
    ) return t1.group_id%type as
        group_id   t1.group_id;
    begin
        -- You should change to "WAIT 3"
        lock table t1 in exclusive mode nowait;

        begin
            -- find existing
            select distinct a.group_id
                into ins.group_id
            from t1 a
            where a.group_name = ins.group_name;

            -- re-use that value
            insert into t1 a (group_name, group_id)
            values ( ins.group_name, ins.group_id );

        exception
            when no_data_found then
                -- create a new unique value
                insert into t1 a (group_name, group_id)
                values ( ins.group_name, group_id_seq.nextval )
                returning a.group_id
                    into ins.group_id;
            when too_many_rows
                -- the GROUP_NAME does not have a unique GROUP_ID
                then raise data_corruption_detected;
        end;

        return ins.group_id;
    exception
        -- not necessary.  I like to customize my exceptions.
        when lock_timeout then
            raise table_in_use_try_again;
    end;
end;
/


Why does Veritas backup take longer through CMDEXEC than cmd? "c:\...\dbbackex.exe" -f "C:\...\backup.bch" -p policy_name -s server_name -np

The above command, when run from command line takes something like 30sec to finish for a small db or transaction logs.  When I run the same command in a job step using CMDEXEC step type it takes right around 10 minutes.

In NetBackups I don't see the job in the status view until that last 30 seconds when the job is actually doing something, but I DO see a process kick off in the task mgr.

I've already talked to our NetBackups admin.  He showed me his log.  It clearly indicates the task is only taking the 30sec that it always does, so the slowdown is definitely local to the sql server.

I've already tried using cmd-xpshell in a tsql statement. Same results.
To be clear: the job does finish.  I don't get access denied errors or anything like that, it just takes much much longer.

Edit for future googlers

I talked with our NetBackups lead and she said she had seen it before but was never able to ping it down.  For now we are in research mode, so nothing super helpful, but at least I'm not crazy!

I can't tell you why with the information provided, but I can give you some guidance to figuring it out.

Monitor your wait statistics while the longer job is running. You should be able to figure out which session is running the backup, see what it is waiting on. sys.dm_exec_requests will tell you this (check the wait_type, and also look at blocking_session_id in case it is getting blocked), you'll just need to refresh often.

Or you could roll up your sleeves and run a targeted extended events session, or turn on trace flags that give more insight into slow backups (see here).


Do I have to pay a license fee for Berkeley DB? I'm planning to use Berkeley DB in a mobile application. Will I need a license even if the application is distributed freely in the iPhone and Android markets?

http://www.oracle.com/technetwork/database/berkeleydb/downloads/licensing-098979.html

Our open source license permits you to use Berkeley DB, Berkeley DB Java Edition or Berkeley DB XML at no charge under the condition that if you use the software in an application you redistribute, the complete source code for your application must be available and freely redistributable under reasonable conditions. If you do not want to release the source code for your application, you may purchase a license from Oracle. For pricing information, or if you have further questions on licensing, please contact us at berkeleydb-info_us@oracle.com.


How to use psql with no password prompt? I wrote a script to REINDEX indexes in a database. Here is one of them:

echo -e "\nreindex for unq_vbvdata_vehicle started at: `date "+%F %T"`" >> ${LOG_FILE}
psql -U ${USERNAME} -h ${HOSTNAME} -d ${DBNAME} -c "REINDEX INDEX scm_main.unq_vbvdata_vehicle;"
if [[ ${?} -eq 0 ]]; then
    echo "reindex for unq_vbvdata_vehicle finished at: `date "+%F %T"`" >> ${LOG_FILE}
else
    echo "reindex for unq_vbvdata_vehicle failed" >> ${LOG_FILE}
    exit 1
fi

The problem is I can not run this script in standalone mode. psql is prompting password every time it runs. There is also two limitations:

I can not create a user on database with no password.
Because REINDEX locks tables, I should use sleep <num> between each REINDEX.

Is there any automatic solution?

You have four choices regarding the password prompt:

set the PGPASSWORD environment variable. For details see the manual: http://www.postgresql.org/docs/current/static/libpq-envars.html
use a .pgpass file to store the password. For details see the manual: http://www.postgresql.org/docs/current/static/libpq-pgpass.html
use "trust authentication" for that specific user: http://www.postgresql.org/docs/current/static/auth-methods.html#AUTH-TRUST
use a connection URI that contains everything: http://www.postgresql.org/docs/current/static/libpq-connect.html#AEN42532


Error 10053 connecting with Management Studio While I try to connect to SQL Server, I am getting the following error:

  A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - An established connection was aborted by the software in your host machine.) (Microsoft SQL Server, Error: 10053)

How can this be solved? What may be the cause of this issue?

There are several causes for this. The ones I know of (at least)

max server memory set to 0
user connections set to 1 (limiting you to one only)
and possibly server side SSL enabled

Have you ever connected? Changed any settings? Have a backup of master? What other errors are there in the event log?

Anyhow, restart SQL Server with the -f option and ensure the the 2 settings above are sensible.


Best tool to optimize SQL Server (not the queries, but the database itself) Does anyone know what is the best tool to optimize an entire SQL Server database?

I'm not talking about how to write the queries but more how to manage the database file, indexes, paging and fragmentation.

PAINFUL TRUTH
The eternal fight between tools vs DBAs is still there ...but what I just wanted to know the basics till the product grows and then I can hire a non grouchy DBA :--)

I'd echo @Mark Storey-Smith's comment - a competent DBA is the best way to go here. You can't really automate a well tuned SQL Server but a good DBA can setup various maintenance items to keep it running well.

Sounds like you were asking a lot about maintenance so one great spot to look for some scripts to help setup a best of breed monitoring solution is Olla Hallengren's Maintenance Solution scripts explained on Olla's site here.

That will help ensure you are at least doing the important maintenance items (Index rebuilding/reorganizing, statistics updating, backups, updating statistics, checking database integrity, etc.)

As far as the ongoing optimization, I'd suggest picking up a copy of the Professional SQL Server 2008 Internals and Troubleshooting book. It has just the right amount of internals knowledge to help you understand the "why" behind best practices and contains plenty of practical examples for implementing the best practices.

Or I'd recommend the same Internals & Troubleshooting book but for SQL Server 2012. Contains some great chapters just for your question. Like how to perform a SQL Server Health Check, by Glenn Berry.


Oracle database backup and restore options We have one requirement that export data from two different source Oracle DBs and restore the data to another two different target Oracle DBs to do data comparison.  We care about the datafiles in these two source Oracle DBs.

The Oracle db are 11.2 RAC. Can we use Oracle data dump tools such like impdp and expdp to meet the above requirement? Or is there any limitation to use data dump tools for the above requirement?

Thanks a lot.

Data pump export & import are just fine for moving data around; I don't know of any limitation in what you described.


Use of HAVING without GROUP BY in SQL queries In order to use HAVING in SQL queries , must there be a GROUP BY to aggregate the column names?

Are there any special cases where it is possible to use HAVING without a GROUP BY in SQL queries?

Must they co-exist at the same time?

No.

They don't have to coexist, as proved by the fact that the following query in Oracle works:

select * from dual having 1 = 1;

Similarly, in PostgreSQL the following query works:

select 1 having 1 = 1;

So having doesn't require group by.

Having is applied after the aggregation phase and must be used if you want to filter aggregate results. So the reverse isn't true, and the following won't work:

select a, count(*) as c
from mytable
group by a
where c > 1;

You need to replace where with having in this case, as follows:

select a, count(*) as c
from mytable
group by a
having c > 1;

NB The following query form will also work:

select *
from (
  select a, count(*) as c
  from mytable
  group by a
)
where c > 1;

You can see that using having is simply a shorthand version of this last query.

In summary, having is applied after the group by phase whereas where is applied before the group by phase.


Mysql Config Suggestions - CPU too high My server (64GB,16CPUs) has mariadb 5.5.60.

The queries are around  500 selects /second and 300 inserts/seconds.

The queries are simple select of auth token and user details.

Both are properly indexed.

The insert query is also very simple.

However, the CPU usage is still high   %cpu usage is >600%.

The following is the mysql config.

Please suggest.

max_connections = 5000
max_user_connections=1000
key_buffer_size = 10024M
myisam_sort_buffer_size = 512M
read_buffer_size = 1G
table_open_cache = 9000
thread_cache_size = 900
wait_timeout = 300
connect_timeout = 300
tmp_table_size = 8G
max_heap_table_size = 4G
max_allowed_packet = 10G
net_buffer_length = 2048M
max_connect_errors = 100
read_rnd_buffer_size = 2048M
bulk_insert_buffer_size = 2G
query_prealloc_size = 512M
query_alloc_block_size = 128M
transaction_alloc_block_size = 128M
transaction_prealloc_size = 128M
max_write_lock_count = 16
log-error
external-locking=FALSE
open_files_limit=50000
character-set-server = utf8
innodb_buffer_pool_size=40G
table_cache=10G
innodb_file_per_table=1
skip-name-resolve

Two things:

As you posted 600% of CPU usage, I'll guess that you are using Linux, so no worries, actually this means that you are just using 100% of 6 processors, so you still have 10 processors totally free. When you start seeing a CPU usage above 1400% then you can start worrying.
More important than point number 1 is the crazy mess you have in your config file. You can't just grow those values with no knowledge of what are you doing. My recommendations is to hire a experienced DBA or learn how to tune a MySQL server properly.


SQL Server Logon Auditing and Reporting the data from a DB Table Is it possible to configure a SQL Server database to write audit information for logons (success and failure) to a database table that can be queried?

The requirement is to be able to generate a monthly report that captures:

Date / Time
Login Name
User Name
Source Hostname (i.e. the device where the connection is coming from)
Source Username (username on the device the connection is coming from)

Ideally we could capture that data, have it in a table somewhere and query it monthly for the report.

SQL Server Audit doesn't appear to let me capture that in a table - it seems to be a file, security log or application log and my Google Fu has not been strong enough to find anything so far to meet the requirement.

Short of a login trigger to interrogate the session and record that connection attempt in a table that can be queried later, are there any other options available?

Auditing of logons is set with options available through the SSMS studio or via T-SQL. You can log success or failure or both.  The recommended destination is the Windows event log which can be queried with PowerShell or exported.

This fills up your event log fast as many service accounts are connecting many times a minute.

An audit policy can be created which can be more selective. From here

Connect the SQL server instance via SQL Server Management Studio.
Navigate to Security → Right-click “Audits” and select “New audit” →
Type in an name for the audit and select the location where the SQL
Server audit logs will be stored → Click “OK” →
Right-click the newly created audit and select “Enable audit”.
Right-click “Server Audit Specification” and select “New Server Audit Specification” →
Type in an appropriate name → Select the new audit from the audit drop-down
menu →
In the “Audit action type” column, select “Failed Login Group”
and “Successful Login Group” → Click “OK” →
Right-click on the newly
created server audit specification and select “Enable server audit
specification”.
To view the SQL Server audit login trail, navigate to
Security | Audits → Right-click the newly created audit and select
“View Audit Logs”

You can also make a system trigger but why bother when an audit policy will do the job?


SQL Server 2012 Agent hangs at a particular time of the day There is a 3 node SQL Server Failover Cluster with 3 instances running on same node at the moment. SQL Server version is 2012 with SP3.

At a particular time of the day at 4 PM daily, SQL Agent hangs and following error comes in SQL Agent Log:

  2019-12-17 20:17:53 - + [260] Unable to start mail session.

  2019-12-17 20:17:53 - + [396] An idle CPU condition has not been
  defined - OnIdle job schedules will have no effect

  2019-12-17 20:17:53 - + [408] SQL Server XXXXXXXX is clustered -
  AutoRestart has been disabled

  2019-12-18 16:20:30 - ! [298] SQLServer Error: 65535, SQL Server
  Network Interfaces: Error Locating Server/Instance Specified
  [xFFFFFFFF]. [SQLSTATE 08001]

  2019-12-18 16:20:30 - ! [165] ODBC Error: 0, Login timeout expired
  [SQLSTATE HYT00]

  2019-12-18 16:20:30 - ! [298] SQLServer Error: 65535, A
  network-related or instance-specific error has occurred while
  establishing a connection to SQL Server. Server is not found or not
  accessible. Check if instance name is correct and if SQL Server is
  configured to allow remote connections. For more information see SQL
  Server Books Online. [SQLSTATE 08001]

  2019-12-18 16:20:30 - ! [382] Logon to server 'XXXXX\XXXXXXX' failed
  (ConnUpdateStartExecutionDate)

  2019-12-18 16:20:31 - ! [298] SQLServer Error: 65535, SQL Server
  Network Interfaces: Error Locating Server/Instance Specified
  [xFFFFFFFF]. [SQLSTATE 08001]

  2019-12-18 16:20:31 - ! [298] SQLServer Error: 65535, SQL Server
  Network Interfaces: Error Locating Server/Instance Specified
  [xFFFFFFFF]. [SQLSTATE 08001]

  2019-12-18 16:20:31 - ! [165] ODBC Error: 0, Login timeout expired
  [SQLSTATE HYT00]

  2019-12-18 16:20:31 - ! [165] ODBC Error: 0, Login timeout expired
  [SQLSTATE HYT00]

  2019-12-18 16:20:31 - ! [298] SQLServer Error: 65535, A
  network-related or instance-specific error has occurred while
  establishing a connection to SQL Server. Server is not found or not
  accessible. Check if instance name is correct and if SQL Server is
  configured to allow remote connections. For more information see SQL
  Server Books Online. [SQLSTATE 08001]

  2019-12-18 16:20:31 - ! [298] SQLServer Error: 65535, A
  network-related or instance-specific error has occurred while
  establishing a connection to SQL Server. Server is not found or not
  accessible. Check if instance name is correct and if SQL Server is
  configured to allow remote connections. For more information see SQL
  Server Books Online. [SQLSTATE 08001]

Also if I query sp_readerrorlog through ssms, following error comes:

  Msg 22004, Level 16, State 1, Line 0 Failed to open loopback
  connection. Please see event log for more information.

  Msg 22004, Level 16, State 1, Line 0 Error log location not found.

I have found the same symptoms in the support page of Microsoft:
https://support.microsoft.com/en-in/help/3185365/sql-server-2016-agent-fails-to-start-or-failed-to-retrieve-data-error

But the above issue in the Microsoft Support Page is for SQL Server 2016, where the resolution is to update the ODBC Driver 13.1

But in my case it is SQL Server 2012, and in ODBC Data Source > Drives - no ODBC version is to be found other than Native Client.

This issue is occurring only on one of the instance among the total 3 clustered instances on the server.

We had similar issues on one of our clusters and found that "Allow the computer to turn off this device to save power" was turned on for the primary NIC. Id try turning that setting  off on any/all NICs. I posted as "a possible" answer so I could post the picture:


MySQL SELECT blocking an INSERT? Is it possible for a  SELECT query to block INSERT / ALTER queries?

I have the following (taken from ENGINE INNODB STATUS) on my slave database:

---TRANSACTION 26DA7060, not started
mysql tables in use 5, locked 12
MySQL thread id 516232, OS thread handle 0x7fcf8304a700, query id 350388626 event_scheduler Waiting for table metadata lock
ALTER TABLE xxxxxxxxxxxx REORGANIZE PARTITION . . . .

---TRANSACTION 26DA705F, ACTIVE 201692 sec
5 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1
MySQL thread id 2, OS thread handle 0x7fc99aa91700, query id 350388642 Waiting for table metadata lock
INSERT INTO xxxxxxxxxxxx VALUES (. . . . . . . . . . . . .)

---TRANSACTION 2499F3C3, ACTIVE 1031443 sec fetching rows
mysql tables in use 11, locked 0
MySQL thread id 338078, OS thread handle 0x7fcf830dd700, query id 260018270 127.0.0.1 xxxxxxxxxxx Sending data
SELECT . . . .
Trx read view will not see trx with id >= 2499F3C4, sees < 2486B035

---TRANSACTION 2499EB0F, ACTIVE 1031500 sec fetching rows
mysql tables in use 11, locked 0
MySQL thread id 338037, OS thread handle 0x7fcf8307b700, query id 260008885 127.0.0.1 xxxxxxxxxxx Sending data
SELECT . . . .
Trx read view will not see trx with id >= 2499EB10, sees < 2486B035

---TRANSACTION 2486B035, ACTIVE 1091724 sec fetching rows
mysql tables in use 11, locked 0
MySQL thread id 330522, OS thread handle 0x7fcf8313f700, query id 255888600 127.0.0.1 xxxxxxxxxxx Sending data
SELECT . . . .
Tr  Trx read view will not see trx with id >= 2486B036, sees < 2486B036

They all use the same table.
The SELECTs are identical queries, and have a couple of joins to little used tables.
The INSERT is a simple INSERT with no joins.
THE ALTER is a simple ALTER TABLE REORGANIZE PARTITION.

The top one (ALTER TABLE) should have started about 48 hours earlier.
The INSERT about two minutes before the ALTER.

I can understand why the INSERT would block the ALTER, but can't see why the SELECT would block the INSERT.
And if it really had been running that long, it should have blocked previous INSERTs (we INSERT about 250 times a minute on average).

So I'm just trying to figure out what had happenned.

The output is only showing the current query in a transaction.  What else is in the BEGIN...COMMIT?  Or are you running autocommit=0?  Or =1?

SELECT grabs shared locks on the rows it is looking at, for the duration of the transaction.

INSERT grabs exclusive lock(s).  It sounds like the SELECT already has a lock that the INSERT desires.

Perhaps the SELECT involves a table scan?

Note also that it says table metadata lock.  This is something 'bigger' than a "row lock".

If you have an 11-table JOIN that it taking 12 days, I suspect you are missing an index.  Or perhaps missing an ON clause, thereby turning a INNER JOIN into a very costly CROSS JOIN.  Please provide the EXPLAIN, the SELECT and SHOW CREATE TABLE if you would like us to dig deeper into this possibility.


How do I design a database/query that queries users's answers to user tags My current setup in PostgreSQL:

CREATE TABLE "usertag" (
    "id" INTEGER NOT NULL DEFAULT E'nextval(\'usertag_id_seq\'::regclass)',
    "questionTitle" VARCHAR(255) NULL DEFAULT NULL,
    "factTitle" VARCHAR(255) NULL DEFAULT NULL,
    "type" VARCHAR(255) NULL DEFAULT NULL,
    "defaultAnswer" VARCHAR(255) NULL DEFAULT NULL,
    "priorityLevel" INTEGER NULL DEFAULT NULL,
    "created_at" TIMESTAMP NULL,
    "updated_at" TIMESTAMP NULL,
    PRIMARY KEY ("id")
)

CREATE TABLE "usertaganswer" (
    "id" INTEGER NOT NULL DEFAULT E'nextval(\'usertaganswer_id_seq\'::regclass)',
    "userId" INTEGER NULL DEFAULT NULL,
    "inactiveUserId" INTEGER NULL DEFAULT NULL,
    "tagId" INTEGER NULL DEFAULT NULL,
    "created_at" TIMESTAMP NULL,
    "updated_at" TIMESTAMP NULL,
    "numberAnswer" NUMERIC(8,2) NULL DEFAULT NULL,
    "textAnswer" VARCHAR(255) NULL DEFAULT NULL,
    PRIMARY KEY ("id")
)

CREATE TABLE "usertagansweroptions" (
    "id" INTEGER NOT NULL DEFAULT E'nextval(\'usertagansweroptions_id_seq\'::regclass)',
    "answer" VARCHAR(255) NULL DEFAULT NULL,
    "userTagId" INTEGER NULL DEFAULT NULL,
    "created_at" TIMESTAMP NULL,
    "updated_at" TIMESTAMP NULL,
    PRIMARY KEY ("id")
)

CREATE TABLE "usertaganswer_answeredoptions" (
    "id" INTEGER NOT NULL DEFAULT E'nextval(\'usertaganswer_answeredoptions_id_seq\'::regclass)',
    "userTagAnswerOptionId" INTEGER NULL DEFAULT NULL,
    "userTagAnswerId" INTEGER NULL DEFAULT NULL,
    "created_at" TIMESTAMP NULL,
    "updated_at" TIMESTAMP NULL,
    PRIMARY KEY ("id")
)

So if I'm query users based only on one userTag, that's fine. Here's an example of a query that works as expected:

select distinct "user"."id"
from "user"
inner join "usertaganswer" on "user"."id" = "usertaganswer"."userId"
left outer join "usertaganswer_answeredoptions" on "usertaganswer"."id" = "usertaganswer_answeredoptions"."userTagAnswerId"
where "allowedToInteractWith" = true
and "usertaganswer"."numberAnswer" between 5 and 30

but when I try adding a criterion, I can't wrap my head around it. This works:

select distinct "user"."id"
from "user"
inner join "usertaganswer" on "user"."id" = "usertaganswer"."userId"
left outer join "usertaganswer_answeredoptions" on "usertaganswer"."id" = "usertaganswer_answeredoptions"."userTagAnswerId"
where "allowedToInteractWith" = true
and (
    "usertaganswer_answeredoptions"."userTagAnswerOptionId" = '5'
    or "usertaganswer_answeredoptions"."userTagAnswerOptionId" = '6'
    or "usertaganswer_answeredoptions"."userTagAnswerOptionId" = 4
)

but I don't want an 'or' of all the criteria I want an 'and'. But if I do the following, it will return zero results even thought it should return 1

SELECT "user"."id"
from "user"
inner join "usertaganswer" on "user"."id" = "usertaganswer"."userId"
left outer join "usertaganswer_answeredoptions" on "usertaganswer"."id" = "usertaganswer_answeredoptions"."userTagAnswerId"
where "allowedToInteractWith" = TRUE
AND  (
    "usertaganswer_answeredoptions"."userTagAnswerOptionId" = '5'
    or "usertaganswer_answeredoptions"."userTagAnswerOptionId" = '6'
)
AND "usertaganswer_answeredoptions"."userTagAnswerOptionId" = 4

(
"usertaganswer_answeredoptions"."userTagAnswerOptionId" = '5'
or "usertaganswer_answeredoptions"."userTagAnswerOptionId" = '6'
or "usertaganswer_answeredoptions"."userTagAnswerOptionId" = 4
)

but I don't want an 'or' of all the criteria I want an 'and'. But if I do the following, it will return zero results even thought it should return 1

Zero is the correct result.
usertaganswer_answeredoptions.userTagAnswerOptionId can have the single value at the time. It cannot be at the same time equal to 4, 5 and 6. It can be (4 OR 5 OR 6) not (4 AND 5 AND 6).


What is the difference between PAGEIOLATCH_* and IO_COMPLETION? according to this blog:

SQL Server Books online defines IO_COMPLETION as “Occurs while waiting for I/O operations to complete. This wait type generally represents non-data page I/Os. Data page I/O completion waits appear as PAGEIOLATCH_ waits”*

What do non-data page IOs mean? or we can put the question in other way: What does actually “...waiting for I/O operations to complete“
mean?

PAGEIOLATCH_*

I am quoting from Paul Randal's blog post:

  SQL Server has two types of reads: logical I/Os and physical I/Os.
  When the Access Methods portion of the Storage Engine needs to access
  a page, it asks the Buffer Pool for a pointer to the page in memory
  (called a logical I/O) and the Buffer Pool checks through its metadata
  to see if that page is already in memory.

  If the page is in memory, the Buffer Pool gives the Access Methods the
  pointer, and the I/O remains a logical I/O. If the page is not in
  memory, the Buffer Pool issues a "real" I/O (called a physical I/O)
  and the thread has to wait for it to complete – incurring a
  PAGEIOLATCH_XX wait. Once the I/O completes and the pointer is
  available, the thread is notified and can continue running.

IO_COMPLETION

Again quoting from a webpage that belongs to sqlskills.com.

This wait type represents a variety of synchronous read and write operations in data files are not related to tables, plus reads from the transaction log.

What do non-data page IOs mean?

Few examples from Paul Randal's article will make it clear what nod-data page means.

Reading log blocks from the transaction log (during any operation that causes the log to be read from disk – e.g. recovery)
Reading allocation bitmaps from disk (e.g. GAM, SGAM, PFS pages) during many operations (e.g. recovery, DB startup, restore)
Writing intermediate sort buffers to disk (these are called ‘Bobs’)
Reading and writing merge results from/to disk during a merge join
Reading and writing eager spools to disk
Reading VLF headers from the transaction log


Reasons why batch generates more than one query plan? I know that a single batch can have more than one query plan but want to learn some more about the cases when more than one execution plans are generated. Is it because of a batch length or the use of some statements? Is there maximum limit for query plans for a single batch?

Thanks

Each query in the batch needs a plan. And furthermore, each procedural function (scalar function or multi statement table valued function) in the batch needs a plan too, which is often less obvious.


Do WHERE IN queries reindex the table after each set within the query? I have a basic WHERE IN query that takes too long (30+ secs) to process.

DELETE FROM `table1`
WHERE ( djID, localID )
IN (("128","26"),("110","32"))

The table I'm querying on has about 20 million rows, with two indices - the first is the auto-incremented PK named ID, the second index is over two columns - djID, localID

I'm wondering if MySQL re-indexes the table after each ( djID, localID ) in the query, therefore accounting for the long query time, and if so, is there a way to force it to wait until all the ( djID, localID ) sets have been executed in the query before re-indexing?

Or is there another reason jumping out that may cause the long query time?

No SELECT causes reindexing.  Only ALTER, OPTIMIZE, and a few other commands, may cause reindexing.
The construct (a,b) IN ((1,2), (3,4)) is not well optimized.

So,...  Make it two DELETEs, such as

DELETE FROM `table1`
    WHERE djID = 128
      AND localID = 26;

If you have lots of them, you could build a tmp table with the pairs of numbers, then do a multi-table DELETE.

To demonstrate that it is doing far more work than it ought to,

FLUSH STATUS;
DELETE ... WHERE ... IN ...;
SHOW SESSION STATUS LIKE 'Handler%';

You will probably see some numbers about 20M (or maybe 40M), indicating that it did a table scan in spite of having the index.

Then file a bug with http://bugs.mysql.com (unless there already is one).


How to have a fail-over resilient SQL Server view over linked mirrored database table Say I have a set of servers with mirrored application databases and a set of statistics servers where a couple of the tables are exposed from the application database as views (with some tenant specific filtering). The view looks something like

... as select * from [server].[table] where tenant_id = xyz

Is there a way to create the view such that it doesn't depend on which of the applications server is primary. The view stops working after every fail-over and someone has to fail-back the DB.

What is the best practice when it comes to situations like this? Any not-so-best solution?

Well, I haven't tested but how about openrowset? Native client is deprecated so there might be a bit of work needed to get it running, depending on what SQL server version youre're running, but then again so is mirroring I suppose. How about something like this:

sp_configure 'show advanced options', 1;
RECONFIGURE;

GO

sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

Create view MyView

AS

SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=Server1;FailoverPartner=Server2;Trusted_Connection=yes;',
                 AdventureWorks2012.HumanResources.Department) AS a;

GO


Summation query with complex grouping criteria I have a database of coded actions with this structure.

{User, Date, ActionCode}

I need to generate reports based on dates and action codes, and the action codes are not clean because we have to import them from multiple sources.

I can handle most of them (like the average number of times users perform ActionCode 13).

But, here's what I'm having trouble with: The average number of times users perform an action that can be defined by any one of the action codes 61, 62, 700, or 701. This action can also be defined by using both action codes 84 and 85 or by using the three action codes 10, 11, and 12.

Here is what I have that handles the first set of action codes:

select
  average(cnt)
from
(
  select
    count(distinct(date)) as cnt
  from
    codes
  where
    actioncode in (61, 62, 700, 701)
  group by user
)

I use distinct date because some users code to multiple places and we get back more than one code for the action. How do I add "or they had both 84 and 85 on the same date"?

One of many possible ways:

SELECT  avg(cnt) AS avg_per_user
FROM   (
   SELECT usr, count(DISTINCT date) AS cnt
   FROM   codes c
   WHERE  actioncode in (61, 62, 700, 701)

   OR     actioncode = 84
   AND EXISTS (
      SELECT 1 FROM codes c1
      WHERE  c1.date = c.date
      AND    c1.actioncode = 85
      )

   OR     actioncode = 10
   AND EXISTS (
      SELECT 1 FROM codes c1
      WHERE  c1.date = c.date
      AND    c1.actioncode = 11
      )
   AND EXISTS (
      SELECT 1 FROM codes c1
      WHERE  c1.date = c.date
      AND    c1.actioncode = 12
      )
   GROUP  BY usr
   ) sub

We don't need parenthesis, since operator precedence works in our favor.

DISTINCT is only still necessary if there can be duplicates. The cases for (10 + 11 + 12) and (84 + 85) only produce a single row.

I am using usr instead of user, since I am not comfortable with abusing reserved words as identifiers. date isn't a good idea either.


ER Diagram Design: Should every entity have a primary key? And can we have sub entity? Problem: Design a ER diagram such as:

An item has the attribute: description.
An item can be sold by a company or a person.
A person has the attributes: name, phone and email.
A company has the attributes: company name, address and a contact person who is one from the person entity set.
A contact person cannot sell the same item with the company he works for.

This is my design:

I'm learning and I know this is a stupid question. But please correct and point out what are wrong in my design.

I'm not sure that I should remove the primary key SellerID in Seller entity and add companyID to Company entity and personID entity.
Is the Contact Person entity connected to Person entity correctly?
How can I demonstrate the constraint: a contact person cannot sell the items (distinguished by item ID) his company is selling.

Thank you.

As far as primary keys, ALL TABLES HAVE THEM. It's a little over the top, but I've seen too many databases that lose their data integrity because the developers didn't want to lock down a PK ID field off the start. I would have a table for persons and a table for companies, with an attribute field to mark them as a seller.

For the company point of contact, just make it a foreign key relationship to the persons table (but only there will only ever be one POC). If there could be more than one POC, then make a join table between the two. Graphically, you look pretty good there from what I can tell. If I wanted to nit-pick, I would make Point Of Contact an attribute of the Company Table, with a "IS A" link to the Persons Table.

Your last question gets into a gray area of DB Design. You're asking about a "Business Rule." The best approach to this is to have something in the application to check that the Seller_ID does not match a Company_ID. It would probably end up being a bit of logic in the stored procedures. Trying to design that into the database any other way won't be easy, or maintainable.


How does one create a data base for a survey/questionnaire? I need to create a database for a wordy questionnaire. At the end I want to be able to graph my information. Where can I do this?


Force everyone to use snapshot isolation I have a scenario in which I don't want any readers to block writers. I don't want users using table hints or isolation levels to issue shared locks, which might block writers.

Is there a way for SQL Server to force everyone to use snapshot isolation or remove the explicit hints they added?

I have read Can I force a user to use WITH NOLOCK?

It's ok if user just uses RCSI or SI. Thus no S lock. However, user might issue ad-hoc queries with some table hints (e.g. REPEATABLEREAD) or change isolation level, which can block write. For example, if one user does:

SELECT * FROM X WITH (REPEATABLEREAD)

...if table X is big, likely the table will have S lock and writes to that table will be blocked, which means data loss to me in a real-time scenario.

For AlwaysOn Readable Secondary Replicas:

  All queries that run against the secondary databases are automatically mapped to snapshot isolation transaction level, even when other transaction isolation levels are explicitly set. Also, all locking hints are ignored. This eliminates reader/writer contention.

I want to do similar things, that is to limit the isolation level somehow. Is that possible?

Is there a way for SQL Server to force everyone to use snapshot isolation or remove the explicit hints they added?

There is no way to do that !

If you enable RCSI using alter database then DML statements against objects stored in that database start generating row versions even when no transaction uses snapshot isolation. Once this option is enabled, the transactions specifying the read committed isolation level use row versioning instead of locking. When a transaction runs at the read committed isolation level, all statements see a snapshot of data as it exists at the start of the statement.


Multi Master - Master Replication mysql I have mysql 5.7.24 mysql version. Can i implement Multi-master replication between three nodes without to create a ring topology? Like a graph

Thanks :)

While technically this is possible, as MySQL 5.7 supports multisource replication, so one instance can replicate from 2 other servers, it is unlikely you want to setup that.

Regular replication does not resolve write conflicts, so those would have to be solved at application layer or it would only serve work for trivial configurations (e.g. each server working with a separate database or just doing appends to a table).

If you want a proper MySQL cluster with multi-masters and conflict resolution, check Galera/Percona XtraDB Cluster, InnoDB Cluster or NDB Cluster.


SQL SERVER: How to get the stored procedure text EXCLUDING comments? I need to find all the stored procs that use Transactions, as I want to enable transaction abort to those procedures.  However, --I didn't do this; it's inherited-- many of  the stored procedures contain testing procedures with in comment blocks and most of the tests contain transaction blocks. I am only interested in changing stored procs that actually use transactions. AND I want to be able to monitor when stored procs are updated so that I can make sure that this flag is set.

SET XACT_ABORT ON;

Addendum: based on comments, here're some examples from my system.

/*
-- clean up after tests
BEGIN TRANSACTION
EXEC dbo.AR_Cleanup_MoveEqualAndOppositeSBPLiabilities
ROLLBACK TRANSACTION
*/

/*
Use case: 147
BEGIN TRANSACTION
....
*/

Below is a PowerShell example that uses the Microsoft.SqlServer.TransactSql.ScriptDom to parse procs and identify those with BEGIN TRAN statements. This version will download the assembly from NuGet if Microsoft.SqlServer.TransactSql.ScriptDom.dll doesn't already exist in the specified location. You could use a package manager instead for that task.

param (
    # Location of Microsoft.SqlServer.TransactSql.ScriptDom.dll assembly.
    $scriptDomAssemblyPath = "C:\Temp\Microsoft.SqlServer.TransactSql.ScriptDom.dll",
    # Url of Microsoft.SqlServer.DacFx.x64 in official Microsoft NuGet repository.
    $scriptDomNuGetUrl = "https://www.nuget.org/api/v2/package/Microsoft.SqlServer.DacFx.x64/150.4200.1"
)

$procQuery = @"
SELECT
      QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id)) + N'.' + QUOTENAME(p.name) AS ProcedureName
    , m.definition
FROM sys.procedures AS p
JOIN sys.sql_modules AS m ON m.object_id = p.object_id;
"@

# Add Microsoft.SqlServer.TransactSql.ScriptDom assembly, downloading if needed
Function Add-ScriptDomAssemblyType ($scriptDomAssemblyPath, $scriptDomNuGetUrl) {

    # if T-SQL script DOM assembly does not exist, download from NuGet and extract assembly from package to $scriptDomAssemblyPath
    if(![System.IO.File]::Exists($scriptDomAssemblyPath)) {
        if([String]::IsNullOrWhiteSpace($scriptDomNuGetUrl)) {
            throw "Script DOM assembly not found at $scriptDomAssemblyPath and NuGet package download Url not specified"
        }
        $response = Invoke-WebRequest -Uri $scriptDomNuGetUrl
        if ($response.StatusCode -ne 200) {
            throw "Unable to download Microsoft.SqlServer.TransactSql.ScriptDom NuGet package: $response.StatusCode : $response.StatusDescription"
        }
        $tempZipFilePath = [System.IO.Path]::Combine([System.IO.Path]::GetTempPath(), [System.IO.Path]::GetRandomFileName() + ".zip")
        [System.IO.File]::WriteAllBytes($tempZipFilePath, $response.Content)
        $response.BaseResponse.Dispose()
        $tempUnzipFolderPath = [System.IO.Path]::Combine([System.IO.Path]::GetTempPath(), [System.IO.Path]::GetRandomFileName())
        Expand-Archive -Path $tempZipFilePath -DestinationPath $tempUnzipFolderPath
        $tempZipFilePath | Remove-Item
        Move-Item "$tempUnzipFolderPath\lib\net46\Microsoft.SqlServer.TransactSql.ScriptDom.dll" "C:\Temp\Microsoft.SqlServer.TransactSql.ScriptDom.dll"
        $tempUnzipFolderPath | Remove-Item -Recurse
    }

    # load assembly into app domain so it can be used in this script
    Add-Type -Path $scriptDomAssemblyPath
}

# parse CREATE PROCEDURE script, returning true if it contains a BEGIN TRAN T-SQL statement
Function Test-HasBeginTrasaction ($procText) {

    # use TSql parser appropriate for targetted SQL Server version
    $parser = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.TSql140Parser($true)
    $parseErrors = New-Object System.Collections.Generic.List[Microsoft.SqlServer.TransactSql.ScriptDom.ParseError]
    $scriptReader = New-Object System.IO.StringReader($procText)
    $fragment = $parser.Parse($scriptReader, [ref]$parseErrors)
    if($parseErrors.Count -gt 0)
    {
        # parsing errors may occur for existing stored procs if the code is invalid due to obsolete T-SQL syntax
        throw "$($parseErrors.Count) parsing errors: $( ( $parseErrors | ConvertTo-Json ) )"
    }

    # evaluate each statement in stored procedure
    foreach ($statement in $fragment.Batches[0].Statements[0].StatementList.Statements) {
        # return true if a BeginTransactionStatement is found
        if ($statement.GetType().Name -eq "BeginTransactionStatement") {
            return $true
        }
    }

    # return false if no BeginTransactionStatement is found
    return $false

}

############
### main ###
############

try {

    Add-ScriptDomAssemblyType -scriptDomAssemblyPath $scriptDomAssemblyPath -scriptDomNuGetUrl $scriptDomNuGetUrl

    $connectionString = "Data Source=.;Initial Catalog=YourDatabase;Integrated Security=SSPI"
    $connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
    $command = New-Object System.Data.SqlClient.SqlCommand($procQuery, $connection)
    $connection.Open()
    $reader = $command.ExecuteReader()
    while($reader.Read()) {

        $hasBeginTransaction = Test-HasBeginTrasaction -procText $reader["definition"]

        if( $hasBeginTransaction ) {
            Write-Host "Has BEGIN TRANSACTION: $($reader["ProcedureName"])"
        }

    }
    $reader.Close()
    $connection.Close()

}
catch {
    throw
}

This can be easily extended to also check for SET XACT_ABORT ON in order to include only those procs that need attention, assuming SET XACT_ABORT ON is properly placed in the proc code. Below is an additional function and body code to do this.

# parse CREATE PROCEDURE script, returning true if it contains a SET XACT_ABORT ON
Function Test-HasSetXactAbortOn ($procText) {

    # use TSql parser appropriate for targetted SQL Server version
    $parser = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.TSql140Parser($true)
    $parseErrors = New-Object System.Collections.Generic.List[Microsoft.SqlServer.TransactSql.ScriptDom.ParseError]
    $scriptReader = New-Object System.IO.StringReader($procText)
    $fragment = $parser.Parse($scriptReader, [ref]$parseErrors)
    if($parseErrors.Count -gt 0)
    {
        # parsing errors may occur for existing stored procs if the code is invalid due to obsolete T-SQL syntax
        throw "$($parseErrors.Count) parsing errors: $( ( $parseErrors | ConvertTo-Json ) )"
    }

    # evaluate each statement in stored procedure
    foreach ($statement in $fragment.Batches[0].Statements[0].StatementList.Statements) {
        # return true if a SET XACT_ABORT ON is found
        if (($statement.GetType().Name -eq "PredicateSetStatement") -and
            ($statement.Options -band [Microsoft.SqlServer.TransactSql.ScriptDom.SetOptions]::XactAbort) -and
            $statement.IsOn ) {
            return $true
        }
    }

    # return false if no SET XACT_ABORT ON is found
    return $false

}

############
### main ###
############

try {

    Add-ScriptDomAssemblyType -scriptDomAssemblyPath $scriptDomAssemblyPath -scriptDomNuGetUrl $scriptDomNuGetUrl

    $connectionString = "Data Source=.;Initial Catalog=YourDatabase;Integrated Security=SSPI"
    $connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
    $command = New-Object System.Data.SqlClient.SqlCommand($procQuery, $connection)
    $connection.Open()
    $reader = $command.ExecuteReader()
    while($reader.Read()) {

        $hasBeginTransaction = Test-HasBeginTrasaction -procText $reader["definition"]
        $hasSetXactAbortOn = Test-HasSetXactAbortOn -procText $reader["definition"]

        if( $hasBeginTransaction -and !$hasSetXactAbortOn ) {
            Write-Host "Has BEGIN TRANSACTION without SET_XACT_ABORT ON: $($reader["ProcedureName"])"
        }

    }
    $reader.Close()
    $connection.Close()

}
catch {
    throw
}


How to Archive Only MySQL Master not the Slave.? Currently, I have a Master and Slave. Both of them have same set of data (I can say are in Sync). Replication is running fine.

What I need to accomplish is the following ...

Need to Archive the Master(Say I need Quarter(Only Last 3 months) of data in Master) but need the Full data on Slave.

How can we do it with having minimal downtime ? What strategy should we follow ?

If you are sure the Slave is sync'd with the Master, you could do the following on the Slave:

Activate Binary Logging
Perform a full mysqldump every three months
Run FLUSH LOGS; every midnight
After the FLUSH LOGS;, make a physical copy of new binary logs, except the last one
OPTIONAL Set expire_logs_days=180 in my.cnf and run SET GLOBAL expire_logs_days=180; (I used 180 because this give you 90 days to have access to changes from the previous two quarters. If you produce more than one binary log per day, make sure you have enough diskspace to hold 180G of binary logs or just reduce the number from 180.
The idea is to have the changes to the Master. They are stored already in the Master's Binary Logs. Those same changes can be on the Slave as well if you activate Binary Logging on the Slave. The Master never needs to feel anything throughout this process.

You could use pt-table-checksum and pt-table-sync to sync up the Slave from the Master. (I wrote a shell script to automate them using mk-table-checksum and mk-table-sync)

Doing these things will allow you to reload a snapshot of the beginning of a Quarter onto another server. Replay all logs on the Slave (using mysqlbinlog) to any datetime from those logs to view different days within the next Quarter.

If all your data is InnoDB, here is an Alternative from the Master

Binary Logging on the Master should already be enabled due to Replication
Perform a full mysqldump (using --single-transaction) every three months
Run FLUSH LOGS; every midnight
After the FLUSH LOGS;, make a physical copy of new binary logs, except the last one
OPTIONAL Set expire_logs_days=180 in my.cnf and run SET GLOBAL expire_logs_days=180; (I used 180 because this give you 90 days to have access to changes from the previous two quarters. If you produce more than one binary log per day, make sure you have enough diskspace to hold 180G of binary logs or just reduce the number from 180.

These are just some ideas you can try to organize using the Slave or the Master.


Locking in Postgres for UPDATE / INSERT combination I have two tables. One is a log table; another contains, essentially, coupon codes that can only be used once.

The user needs to be able to redeem a coupon, which will insert a row into the log table and mark the coupon as used (by updating the used column to true).

Naturally, there's an obvious race condition/security issue here.

I've done similar things in the past in the world of mySQL. In that world, I'd lock both tables globally, do the logic safe in the knowledge that this could only happen once at a time, and then unlock the tables once I was done.

Is there a better way in Postgres to do this? In particular, I'm concerned that the lock is global, but doesn't have to be — I really only need to make sure no one else is trying to enter that particular code, so perhaps some row-level locking would work?

I have heard of concurrency problems like that in MySQL before. Not so in Postgres.

Built-in row-level locks in the default READ COMMITTED transaction isolation level are enough.

I suggest a single statement with a data-modifying CTE (something that MySQL also doesn't have) because it's convenient to pass values from one table to the other directly (if you should need that). If you don't need anything from the coupon table you can use a transaction with separate UPDATE and INSERT statements just as well.

WITH upd AS (
   UPDATE coupon
   SET    used = true
   WHERE  coupon_id = 123
   AND    NOT used
   RETURNING coupon_id, other_column
   )
INSERT INTO log (coupon_id, other_column)
SELECT coupon_id, other_column FROM upd;

It should be a rare thing that more than one transaction tries to redeem the same coupon. They have a unique number, don't they? More than one transaction trying at the same moment in time should be much rarer, yet. (Maybe an application bug or somebody trying to game the system?)

Be that as it may, the UPDATE only succeeds for exactly one transaction, no matter what. An UPDATE acquires a row level lock on each target row before updating. If a concurrent transaction tries to UPDATE the same row, it will see the lock on the row and wait till the blocking transaction is finished (ROLLBACK or COMMIT), then being the first in the lock queue:

If committed, recheck the condition. If it's still NOT used, lock the row and proceed. Else the UPDATE now finds no qualifying row and does nothing, returning no row, so the INSERT also does nothing.
If rolled back, lock the row and proceed.

There is no potential for a race condition.

There is no potential for a deadlock unless you put more writes into the same transaction or otherwise lock more rows than just the one.

The INSERT is care-free. If, by some mistake the coupon_id already is in the log table (and you have a UNIQUE or PK constraint on log.coupon_id), the whole transaction will be rolled back after a unique violation. Would indicate an illegal state in your DB. If the above statement is the only way to write to the log table, that should never occur.


Query 2 tables one with 1 row and the other with multiple rows I have a part table:

ID,      Description
ABC-123  ABC
DEF-456  DEF

I have a User Defined Fields table that stores information in a single row.

ID,         DOCUMENTID, STRING_VAL,         DATE_VAL
UDF-000021, ABC-123,    TEXT123,            NULL
UDF-000022, ABC-123,    NULL,               6/10/2016
UDF-000023, ABC-123,    JOHN WORKING ON IT, NULL
UDF-000024, ABC-123,    NULL,               7/1/2016
UDF-000025, ABC-123,    YES,                NULL
UDF-000021, DEF-456,    TEXT123,            NULL
UDF-000022, DEF-456,    NULL,               6/10/2016

I need the information to look like this:

PART ID  DESCRIPTION    REVISION     REVISION DATE   NOTES    MARKUP DATE   IM
  -------------------------------------------------------------------------------
  ABC-123  ABC            TEXT123      6/10/2016       JOHN     7/1/2016      YES
  DEF0456  DEF            TEXT123      6/10/2016

I have looked at joins, pivot, etc. and I guess I just cant seem to wrap my head around it.  Any help is greatly appreciated.

You can use the old fashioned PIVOT approach as you need to aggregate two different columns.

WITH Pivoted AS
(
SELECT DOCUMENTID,
       REVISION = MAX(CASE WHEN ID = 'UDF-000021' THEN STRING_VAL END),
       [REVISION DATE] = MAX(CASE WHEN ID = 'UDF-000022' THEN DATE_VAL END),
       NOTES = MAX(CASE WHEN ID = 'UDF-000023' THEN STRING_VAL END),
       [MARKUP DATE] = MAX(CASE WHEN ID = 'UDF-000024' THEN DATE_VAL END),
       IM = MAX(CASE WHEN ID = 'UDF-000025' THEN STRING_VAL END)
FROM UserDefinedFields
GROUP BY DOCUMENTID
)
SELECT p.ID,
       p.Description,
       pv.REVISION,
       pv.[REVISION DATE],
       pv.NOTES,
       pv.[MARKUP DATE],
       pv.IM
FROM   Part p
       LEFT JOIN Pivoted pv
         ON p.ID = pv.DOCUMENTID

Online Demo


sp2-0667 message file sp2 lang .msb not found and sp2-0750 error is coming on windows While i am trying to connect with with database by the command
sqlplus /nolog

than the error message is coming

"sp2-0667 message file sp2 lang .msb not found and sp2-0750 error is coming on windows"

Earlier it was working fine i think some path issue is there so can any one tell me how to resolve the issue or how to set the path on windows machine

Setting the ORACLE_HOME environment variable incorrectly may cause these errors.

On Linux, you can set ORACLE_HOME as $ export ORACLE_HOME=path\to\oracle\home and on Windows you can set as C:\>set ORACLE_HOME=/path/to/oracle/home/.


Get column difference between same table in different schemas Suppose I have the following tables:

schema_a.products

+----+------+------+-------+
| id | code | name | price |
+----+------+------+-------+

schema_b.products

+----+------+------+
| id | code | name |
+----+------+------+

I'm trying to get the column differences between the two tables. So I'd like the query to return the price column (as it exists in schema_a.products but not schema_b.products)

I thought I could select the column names from INFORMATION_SCHEMA for schema_a.products and then omit any column names selected from schema_b.products, like so:

SELECT column_name
FROM information_schema.columns
WHERE table_name = "products"
AND table_schema = "schema_a"
AND column_name NOT IN (
  SELECT GROUP_CONCAT("\"", column_name, "\"")
  FROM information_schema.columns
  WHERE table_name = "products"
  AND table_schema = "schema_b"
);

However this just returns all of the column names. If I run the subquery separately, and then replace it with the result, it works fine.

SELECT column_name
FROM information_schema.columns
WHERE table_name = "products"
AND table_schema = "schema_a"
AND column_name NOT IN ("id", "code", "name");

What am I missing?

Find column names which exists in the table in schema_a.products and not exists in schema_b.products:

SELECT t1.column_name
FROM (SELECT column_name
      FROM information_schema.columns
      WHERE table_name = 'products'
        AND table_schema = 'schema_a') t1
LEFT JOIN (SELECT column_name
           FROM information_schema.columns
           WHERE table_name = 'products'
             AND table_schema = 'schema_b') t2 ON t1.column_name = t2.column_name
WHERE t2.column_name IS NULL

Find column names which exists in the table in one schema and not exists in another schema

SELECT column_name, table_schema
FROM (SELECT column_name, table_schema
      FROM information_schema.columns
      WHERE table_name = 'products'
        AND table_schema IN ('schema_a', 'schema_b')) both
GROUP BY column_name
HAVING GROUP_CONCAT(table_schema ORDER BY table_schema) != 'schema_a,schema_b'

If only_full_group_by is enabled, wrap table_schema field in output with some aggregate function applicable to string data type (MIN, MAX, GROUP_CONCAT, etc.).

If tablenames in schemas are not equal, replace WHERE condition with

WHERE (table_name, table_schema) IN (('tablename1', 'schemaname1'),
                                     ('tablename2', 'schemaname2'))

and add table_name field (maybe wrapped too) to output list.


Help with out-of-range value I have this query that is giving me an error

  The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Query:

select
    COUNT(*)
from
    dbo.patient
INNER JOIN
    dbo.study on dbo.patient.pk = dbo.study.patient_fk
              and dbo.study.study_datetime IS NOT NULL
              and dbo.patient.pat_birthdate IS NOT NULL
              and dbo.study.study_datetime <= DATEADD(D, -2192, GETDATE())
              and dbo.patient.pat_birthdate <= DATEADD(D, -7670, GETDATE());

I read where this might help to be converted to 'DATETIME2', but I can't quite figure out how to correctly convert the column 's.study_datetime' as this column more than likely has some bad values as entries.

Your varchar columns have bad data in them, and you can't really control when SQL Server will try to evaluate them (before or after any filtering occurs). So you can add where clauses that prevent them from being included (by checking for only rows where the value is a date), but there is still no guarantee of the order in which the where clauses will be evaluated (meaning you may still get an error). So in addition to those filters (which can logically replace your NOT NULL filters), you can also ensure that the predicates are only evaluated when they are, in fact, valid dates.

SELECT COUNT(*)
FROM dbo.patient AS p
INNER JOIN dbo.study AS s
  ON s.patient_fk = p.pk
WHERE ISDATE(p.pat_birthdate) = 1
AND ISDATE(s.study_datetime) = 1
AND CASE WHEN ISDATE(s.study_datetime) = 1 THEN s.study_datetime END
  <= DATEADD(DAY, -2192, GETDATE())
AND CASE WHEN ISDATE(p.pat_birthdate) = 1 THEN p.pat_birthdate END
  <= DATEADD(DAY, -7670, GETDATE());

Next, please fix the table so that random junk can no longer find its way into these columns. Identify the bad data first using:

SELECT * FROM dbo.study
  WHERE ISDATE(study_datetime) = 0;

SELECT * FROM dbo.patient
  WHERE ISDATE(pat_birthdate) = 0;

Then correct accordingly (they may all be "dates" in the same pattern, or it really may be a bunch of random junk that you just need to set to NULL). Once there are no bad values in there, you can change the data type to DATE or DATETIME, as it should have been from the start.

Please see the following posts:

Bad habits to kick : Using shorthand with date/time operations
Bad habits to kick : mis-handling date / range queries
Bad habits to kick : choosing the wrong data type
Bad habits to kick : using old-style JOINs


Trying to read a log file encrypted by TDE I have a full chain of full backups and log backups.  I am looking to investigate exactly what transaction was done by a certain user in a given period of time.

The database (thus all backups) are encrypted by TDE.  Tools such as ApexSQL Log does not work on TDE-encrypted data source.  Is there any way I can analyze the log file to find out the information I want?

I can run fn_dump_dblog which gives me some information, however I cannot retrieve the exact information on what was changed, such as what column was changed to what value.

Do you have database audit in place? temporary tables? If you have one of these solutions in place, it can be done relatively easy.

If no, you can use undocumented functions fn_dump_dblog() to analyze log backup contents.

hope this helps!


SQL server level permissions I am aware of the Microsoft provided code to transfer logins between SQL servers, however this only does the account and the password.

What if that particular account has various roles and permissions assigned to it at a server level, is there an equivalent piece of code to script these permissions also?

Thanks

I have not personally tried this, but have a look the the Idera Permission Extractor:

Free Tool SQL Permissions Extractor

If you need a sql solution, there is nothing built in. However, you can query the sys.server_permissions and sys.database_permissions tables. They contain the information needed to build the grant statements yourself. sys.server_role_members and sys.database_role_members contain the information needed to generate role membership scripts.


SQL SERVER Detailed Backup Report I need to create a script that gives the report for the following information:

Backup Job Name
Databases that have been backed up in the last 24 hours
Size of each DB backup
Start time of backup
End time of backup
Duration of backup
Location of the backup files

I need to still include Log backup information, backup size and the location of the backup files stored. Could someone point me in the right direction of how to better my query? Thanks in advance.
What I have tried:
SET @dbname = NULL --set this to be whatever dbname you want
SELECT
  bup.database_name AS [Database],
  bup.server_name AS [Server],
  bup.backup_start_date AS [Backup Started],
  bup.backup_finish_date AS [Backup Finished]
  ,CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))/3600 AS varchar) + ' hours, '
  + CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))/60 AS varchar)+ ' minutes, '
  + CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))%60 AS varchar)+ ' seconds'
  AS [Total Time]
FROM msdb.dbo.backupset bup
WHERE bup.backup_set_id IN
  (SELECT MAX(backup_set_id)
   FROM msdb.dbo.backupset
   WHERE database_name = ISNULL(@dbname, database_name) --if no dbname, then return all
   AND type = 'D' --only interested in the time of last full backup
   GROUP BY database_name)
/* COMMENT THE NEXT LINE IF YOU WANT ALL BACKUP HISTORY */
AND bup.database_name IN (SELECT name FROM master.dbo.sysdatabases)
ORDER BY bup.database_name```

I was able to fix my own problem. Below is the script I used to complete my report:
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
CAST((CAST(DATEDIFF(s, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date) AS int))/3600 AS varchar) + ' hours, '
  + CAST((CAST(DATEDIFF(s, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date) AS int))/60 AS varchar)+ ' minutes, '
  + CAST((CAST(DATEDIFF(s, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date) AS int))%60 AS varchar)+ ' seconds'
  AS [Duration],
CASE msdb..backupset.type
WHEN 'D' THEN 'Full'
WHEN 'L' THEN 'Log'
END AS backup_type,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.physical_device_name as 'Physical Location'
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 1)
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date ```


Error while creating index in SQL Azure I'm trying to create an index on table [Children] in SQL Azure but get the following error:

  The operation failed because an index or statistics with name
  'IX_Children_ParentID' already exists on table 'Children'.

As far as I can see there's no index or statistics with this name. The following command returned zero results so there shouldn't be any indexes or statistics. Is that correct?

SELECT object_id, name FROM sys.stats WHERE name = 'IX_Children_ParentID'
UNION ALL
SELECT object_id, name FROM sys.indexes WHERE name = 'IX_Children_ParentID'

UPDATE: Changed the query in response to @Shark's anwer.

My SQL command for creating the index:

CREATE INDEX IX_Children_ParentID
ON [myschema].[Children]
(
    [ParentID] DESC
)

Before this error occurred I had problems with timeouts while creating the index. Could this be caused by some remnants?

The catalog view sys.all_objects won't show nonclustered indexes.  Here's the query you should be using:

select
    object_name(object_id) as table_name,
    name as index_name
from sys.indexes
where name = 'IX_Children_ParentID'

Or to get all of the indexes for that table:

select
    object_name(object_id) as table_name,
    name as index_name
from sys.indexes
where object_name(object_id) = 'Children'


Importing SQL Server database from a .sql file I'd like to import a database without knowing its name from a .sql file.

In MySql server it is easy because there is tools like heidSQL in which I can import easily my database from a sql file but in SQL Server I can't find the method to do it.

I tried to read the file in notepad but the encoding makes the content unreadable.

When i execute this query RESTORE HEADERONLY FROM DISK = N'C:\Planning\bdd.sql' i have this

How can I do it with SQL server?
Can SQL Server Management Studio do this task or do I have to install a new tool?

Create an empty database or if you have one already target that one.

Open CMD with elevated privilege and run:

sqlcmd -S SERVERNAME -d MYDATABASE -U USERNAME -P PASSWORD -i C:\path\mysqlfile.sql -o C:\path\results.txt

basically:

-S: is your servername or localhost
-d: is the database you are targeting
-U: is the username
-P: is the password
-i: is the path to your .sql file
-o: is where the logs file will be saved so if you had problem during the importation you can debug them


DISCONNECTION (bad value of SQLNET.EXPIRE_TIME) We start a database without sqlnet.ora file.
So as  SQLNET.EXPIRE_TIME is not configured, some process has been disconnected.

We had sqlnet.ora with a SQLNET.EXPIRE_TIME=1, restart the listener but have still the same problem (somme disconnected process after an idle time).
It seems that the database ignore this new parameter.

We use shared server for this db (11.2).

Do you have any suggest (of course, it's impossible to restart the database)?

thanks

It seems that the settings is inside the dispatcher.
So we have shutdown the dispatchers (and of course start others) and it solve our problem


Only Filtered condition gets deleted from index after datatype change using SSMS In SSMS if I create a filtered index we can see filtered condition in index properties. But if I change column type of a column involved in filtered index using SSMS , then after column type changes filtered index condition will be deleted .

For example i created a filtered index for column of datatype varchar(50) . I changed datatype to varchar(100) by using SSMS. After changing datatype I check index properties for that column. Now only filtered condition was missing in properties.

Then I tried to check what may be the reason behind this error. Now when I changed datatype I used "save change script window" and checked what SSMS really does. As we all know the SSMS recreates table index etc , but the index creation query here was missing the filtered condition. Below is part of "save change script window" query showing index creation which does not have filtered condition!!

CREATE NONCLUSTERED INDEX filtered ON dbo.Table_1
(
t
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

So is this bug in SSMS? Or is there anything i am missing like settings in SSMS or anything else?

When you script the index you need to make sure the scripting option is not set to SQL Server 2005.

  Tools / Options / SQL Server Object Explorer / Scripting / Script for server version

I suspect this is the problem (and it happened to me, too).


Is it ok to create many logins in SQL Server I created a register module for my web app.
The user that have access on the web app should also be able to access the data in autocad using fdo and input their credentials. To do that, I also create a login and user for SQL Server.
The web app will have many users in the future.
Is there a problem creating many login in SQL server?
The application will be use by different users. Already attached role to login. But every user have different login.

SQL Server Security Doesn't Scale Well as a Login Store
While you "can" use many SQL Server logins for a web app doesn't mean you should. At the hospital where I work, we use a legacy application called Fastrack. It uses many SQL logins, one for each user, and it's a nightmare to maintain. Users leave and you don't know they've gone. There's no password table and folks forget their passwords. SQL Server security wasn't really built to be a front end for application security. Additionally, providing web users with an authentic SQL login poses security risks to your DBMS. You'll need to assign object security. There will most likely be more manual maintenance to these logins. No DBA will willing want to manage this. And, I've never seen this work well--ever.
A Simple Approach
A better approach is to build a login table or collection of tables within SQL Server for your app. Then, login through ONE application login to SQL Server to take advantage of connection pooling. Next, branch out with your own application login in the app. You'll want to obfuscate the passwords accordingly and possibly store them in an encrypted column within SQL Server. For example, create a simple login page with ASP.NET using C#. It doesn't have to be complex.
https://www.c-sharpcorner.com/article/how-to-create-login-page-in-asp-net-web-application-using-c-sharp-and-sql-server/
ASP .NET Core Identity
And Microsoft is also pushing Identity framework to build scaffolding for logins that are both internal to your app or external from places like Google and Facebook. This looks way more involved and in depth for what you "might" need at first. Especially if you're just experimenting. But you'd probably be able to find some baked code that you can use/copy to get you started.
https://docs.microsoft.com/en-us/aspnet/core/security/authentication/identity?view=aspnetcore-5.0&tabs=visual-studio#create-a-web-app-with-authentication


Optimal placement of tempdb, mdf and ldf files in SQL Server 2012 on SSD's? I realize this is probably a very open ended question and the answers could vary, but what is the optimal placement for the tempdb, mdf and ldf files in SQL Server 2012 when talking SSD's?

Pre-new-purchase, I had an existing SSD with the SQL Server 2012 core files and tempdb installed on and had both the mdf/ldf on a 7200rpm HDD. I then bought 2 SSD's with the original intention of putting mdf on one and ldf on the other.

But, from reading into it more, separate physical disks for mdf and ldf files don't really apply when it comes to SSD's. Correct?

So, I was thinking of the following:

SSD 1 - SQL Server 2012 Core Files and Windows
SSD 2 - tempdb
SSD 3 - mdf and ldf

If it makes a difference, this will be dedicated to just one database so there won't be any contention between multiple databases.

Is my "thinking" setup good or just simply a waste (i.e. no reason to separate out tempdb) where I now have an extra SSD to make use of elsewhere?

But, from reading into it more, separate physical disks for mdf and ldf files don't really apply when it comes to SSD's. Correct?

The original reason for splitting log and data files off onto seperate disks was 2 fold - latency and bandwidth on the drives.

SSDs don't remove these restrictions, but they do decrease/increase the limits quite significantly (7.9ms for a read with a single HDD vs 0.1ms for a read in a single SSD, roughly).

So ultimately yes and no - it doesn't apply AS MUCH as with HDDs, but those limits are still there and can still be met.  It all depends on your workload.

  Is my "thinking" setup good or just simply a waste (i.e. no reason to separate out tempdb) where I now have an extra SSD to make use of elsewhere?

Assuming that

You have 3 physical SSD's
You have 1 physical HDD
You need the data to be redundant, but not necessarily the system itself

Your proposed setup would have a few issues (as mentioned before), and a single drive failing is the main one.

You could go for something like this.

Single 7200rpm drive - Windows OS
RAID 5 array (3 SSDs) - Broken down into 4 drives (D for Data, L for Logs, S for Swap and T for Temp)

OR

Single 7200rpm drive - Windows OS
Single SSD - Temp and Swap
RAID 1 array (2 SSDs) - Data and Logs

It's personal preference of mine offloading Windows onto a non-SSD drive when you only have a limited number, but this entirely depends on what the server is doing and how much of a risk you're willing to take.


Problem with SSIS Variable Expression containing getDate() Brief Architecture

My SSIS package is generating dynamic excel files through Data flow. The file name format is "XYZ_Year_Month_Day_Hour_Minutes.xlsx". So I am using variable with expression which uses getDate() to generate above mentioned format.

Problem

I am writing file, using variable name generated through getDate(). Example: "XYZ_2014-8-1_03_30". In control flow I want to access same file in File System task to copy it to FTP.
But by time the variable changes its value to "XYZ_2014-8-1_03_32", so unable to locate file.

I tried with System::StartTime, but it stays same throughout package execution and all files get same name.

Is there any way we can store DateTime in a variable & it won't change further?

You could use a script task to set the variable property:

Dts.Variables["uVarDateStamp"].Value = DateTime.Date;


datatypes in sql server , difference between similar dataypes, numeric, money , decimal ,float What datatypes could be given to this value --> 45.58

Could these be given:

numeric
money
decimal
float

What's the difference then? Which one would be the best to use?

Like everything else in SQL Server, it depends.

Numeric is the same as Decimal.  These let you specify precision and are good if you will be having more than 2 decimal places or need more precision than what you indicated.

Money is used for, you guessed it, money.  If your field is a currency this is appropriate.

Float is kind of special and is NOT suited for exact numbers.  If you need to represent floating point numbers, this is a way to go.  If you need to always store exactly what you put in the field, float is a bad choice.

So, what do you want to use the field for?  What does your data actually represent?  That should be the determining factor in what datatype you use.


Azure Elastic Pool - is it supported for MySQL? I know that Elastic Pool in Azure is supported for SQL Server.
But I could not find any information if it is supported for MySQL.
I could not find any information in documentation at all what types of databases are supported...
Can you please assist me since I need to provide this info ASAP?
THANKS

Essentially, yes.  An Azure Database for MySql is similar to an Azure SQL Database Elastic Pool or Azure SQL Database Managed Instance.

Within an Azure Database for MySQL server, you can create one or
multiple databases. You can opt to create a single database per server
to use all the resources or to create multiple databases to share the
resources. The pricing is structured per-server, based on the
configuration of pricing tier, vCores, and storage (GB). For more
information, see Pricing tiers.

https://docs.microsoft.com/en-us/azure/mysql/concepts-servers
An Azure SQL Database Elastic Pool is similarly:

Azure SQL Database elastic pools are a simple, cost-effective solution
for managing and scaling multiple databases that have varying and
unpredictable usage demands. The databases in an elastic pool are on a
single server and share a set number of resources at a set price.
Elastic pools in Azure SQL Database enable SaaS developers to optimize
the price performance for a group of databases within a prescribed
budget while delivering performance elasticity for each database.

https://docs.microsoft.com/en-us/azure/azure-sql/database/elastic-pool-overview
Only Azure SQL Database has the feature to have lots of databases with separate physical resources in the same logical server.  With Azure Database for MySql if you want two databases to have their own dedicate resources, you need two seperate Azure Database for MySQL servers.


SSIS Failed to start project I've created sereral SSIS packages in Visual Studio 2012 and they can be executed without any problems.

For a project I'm working on I generate packages using BIML. In my BIML script I create a package for each databaseview in two defines databaseschemas. For example:

[crm].[contact]
[crm].[account]
[navision].[vendor]
[navision].[customer]

The name of the packages will be [schemaname] viewname, e.a. "[crm] contact".

When I start a package in Visual Studio I get the message "Failed to start project" see below.

"Exception deserializing the package. "Specified part does not exist in the package".

Why is this error occurring?

When troubleshooting the error I've searched the internet for possible solutions. The main solution to this error is the 32bit / 64bit excecution of SSIS.

Because I can run other packages without any problems all posible solutions I found regarding configuration of packages, SSIS and Visual Studio didn't apply.

Maybe I did something wrong when I generated the packages with BIML, so I started with an empty package and see if it ran.... Still the same error.

Eventually i changed the name of the package to "crm contact" and it ran. It seems that using blockquotes ( [ ] ) in the name gave the error.


Why delete with "where in" condition is slow, while looping & using single where is fast? I have a table with ~5 million rows. Here is create table statement:
CREATE TABLE `monitor_checks` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `monitor_id` int unsigned NOT NULL,
  `node_id` int unsigned NOT NULL,
  `url` varchar(2048) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `ip` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `request_method` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `request_body_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `request_body_raw` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `request_body_json` json DEFAULT NULL,
  `request_body_form_params` json DEFAULT NULL,
  `request_headers` json DEFAULT NULL,
  `response_protocol` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `response_code` int DEFAULT NULL,
  `response_reason_phrase` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `response_size` int DEFAULT NULL,
  `response_headers` json DEFAULT NULL,
  `is_successful` tinyint(1) NOT NULL,
  `is_failed` tinyint(1) NOT NULL,
  `fail_reason` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `response_time` double(8,6) DEFAULT NULL,
  `time_namelookup` decimal(8,6) DEFAULT NULL,
  `time_connect` decimal(8,6) DEFAULT NULL,
  `time_appconnect` decimal(8,6) DEFAULT NULL,
  `time_pretransfer` decimal(8,6) DEFAULT NULL,
  `time_starttransfer` decimal(8,6) DEFAULT NULL,
  `time_total` decimal(8,6) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `monitor_checks_monitor_id_created_at_index` (`monitor_id`,`created_at`),
  KEY `monitor_checks_node_id_foreign` (`node_id`),
  KEY `monitor_checks_monitor_id_is_failed` (`monitor_id`,`is_failed`),
  KEY `monitor_id_node_id_is_successful` (`monitor_id`,`node_id`,`is_successful`),
  KEY `monitor_checks_monitor_id_index` (`monitor_id`),
  CONSTRAINT `monitor_checks_monitor_id_foreign` FOREIGN KEY (`monitor_id`) REFERENCES `monitors` (`id`) ON DELETE CASCADE,
  CONSTRAINT `monitor_checks_node_id_foreign` FOREIGN KEY (`node_id`) REFERENCES `nodes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=224279831 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

I won't pretend that I know much about indexes, so maybe it's a complete mess. But still, I have the index: KEY monitor_id_node_id_is_successful (monitor_id,node_id,is_successful)
Now, when I execute this statement:
DELETE from monitor_checks where monitor_id in (~200 ids) and node_id = 1 and is_successful = 1
It takes a lot of time, a few minutes.
Now, if instead, I do 200 delete queries, looks like it takes less than a second.
I'm not even sure, I use Laravel framework and all I did is changed my code so that it loops through all values and deletes one-by-one.
Now if the show processlist I barely can see that the statement takes more than a second, while logs say that the whole loop for ~200 ids took a second.
Why so? I thought that if I use batch delete it should be faster?
UPD:
explain statement for where in returns:

Type: range

Possible indexes:

monitor_checks_monitor_id_created_at_index,monitor_checks_node_id_foreign,monitor_checks_monitor_id_is_failed,monitor_id_node_id_is_successful,monitor_checks_monitor_id_index

key:

monitor_checks_monitor_id_created_at_index

key len: 4

Rows: 73485

When building an index, start with the things that are tested with =.  That is, the index should be
 INDEX(node_id, is_successful,   -- in either order
       monitor_id)               -- last

Another tip:  When you have both INDEX(a) and INDEX(a,b), the former is unnecessary since the latter can handle searching just by a.  (But not just b.)  That is, currently your single-column index (monitor_id) can be DROPped.
More on building indexes:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql


Difference Between SELECT and select in oracle sql The algorithm used to find SQL in the library cache is based on the
  ASCII values of the characters that make up the statement. Means The
  SELECT instead of select statement will be parsed again in library
  cache.

  But Some where i read that the oracle treat's lowercase select as
  upper SELECT automaticaly in a statement

Which on is true above those two...

There is nothing specific about SELECT here. There is no semantic difference between SELECT, select and SeLeCt.

The issue being described is that if you issue these three semantically identical statements:

 SELECT thing FROM mytable;

 select thing from mytable;

 Select Thing From MyTable;

the database treats them as three entirely different entities, that will each occupy some space in the library cache. They will each have been parsed individually, costing CPU each time, even if the resulting execution plan are are identical.

Same thing if you have two identical queries but one with a comment and another without - they will be treated as different statements entirely by the engine, and each consume resources. (I believe even spacing matters.)

(This is actually helpful for debugging. If you're trying different settings to optimize a query without changing the query text, add a comment describing the current settings each time you run it. You're guaranteed a re-parse, and can find the individual runs in [g]v$sql easily with those comments. This wouldn't work if the engine "normalized" the query text before parsing.)

What this means for you is that you should be consistent with your query syntax. Choose a "coding" style and stick with it to avoid littering the various caches with semantically identical queries.

Example: notice the three different SQL IDs.

select a from foo;
Select a From foo;
SELECT a FROM foo;

select * from v$sql where sql_text like '%foo%';

SQL_ID        SQL_TEXT
------------- -------------------
dxdhrwqjpn6b7 Select a From foo
0dqb3y3bask98 select a from foo
1y13x9j2rwzuz SELECT a FROM foo
...


MySQL 5.1 - long running select locking the table and not allowing inserts Platform - MySQL 5.1, MyISAM engine
We reached max on int (id, auto increment) in our Prod system. Changed the type to BigInt. Even since this change was made, long running selects are locking the table and inserts wait in the queue forever. Any suggestions are appreciated.
Thanks!

MYISAM do not provide row-level locking so the whole table become locked on every DML or DDL statement. INNODB allows to lock only rows involved into the query so multiple concurrent queries can be performed. Therefore migration from the MYISAM to INNODB can solve the problem. The other way (not always possible) is to split the table by partitioning. Since mysql v5.7 each query locks only partitions involved while other are still free to be queried.


Can I restore an uncompressed differential backup after a compressed full backup? If we enable compression on database backups (because the database backup size is huge) but don't enable it on differential which are relatively small in size, will a restore work? Because the full backup is compressed but the differential isn't compressed.

This is really a question for the DBA site but yes, it will work. Compression is transparent when restoring, and the RESTORE command doesn't need any parameter to tell it that a backup is compressed.

Of course you can easily test this yourself.


Restart the active node services without failover On a 2 node Windows Server Failover cluster configuration, how do I restart the SQL Server services without failing over to the other node?

Use the Failover Cluster Manager application to manually stop the services.

This tells the cluster you are intentionally shutting down the service, so it won't attempt to bring it back up on another node.

The Failover Cluster service will wait until you manually bring the service back online using Failover Cluster Manager, where you can decide which node to use to bring the service up.


HA - MySQL Master Master replication - Duplicate entry errors I am running Percona MySQL 5.5.39 on two masters with keepalived monitoring a VIP which will failover to the standby master if the primary master goes dark. While testing the fault tolerance of my setup in the lab I noticed that I am getting duplicate key entries on both masters.

Basically I run siege to emulate user load, for a duration of five minutes, within that time duration I shutdown the primary master, and after about 10 seconds for keepalived to detect the outtage, the VIP is switched the standby master, at which time everything is working as expected. Then after a minute or so, while siege is still running, I power on the primary master, and when online, it assumes the VIP.

It is during the swapping of the VIP that I think the duplicate entry issue is occurring. I followed this post explaining how to implement 'crash resilient' replication, but after implementing the suggested my.cnf changes and rerunning the user simulation, I ended up with the same broken replication.

There is also the ability for the standby master to increment it's indices by 2 instead of 1 to avoid collisions with the primary master, but I feel this is more of a hack than a solution.

Is there any better way to avoid primary key collisions in an HA environment?

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

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


When was a table trigger disabled and re-enabled? I need to know if the enable/disable history of a table trigger is natively tracked by SQL Server.

I've reviewed the system views:
 • [sys].[triggers] contains a modify_date field
 • [sys].[trigger_events] focuses on the trigger INSERT/UPDATE/DELETE events

Can you recommend any other sources of information about trigger history?

SQL Server doesn't track this information (I half expected to see it in the default trace, but it's not there either). Enable/disable will update modify_date, but you won't be able to distinguish that from a rename or code modification. Also, it will only show you when the last change took place. If you want any other tracking (such as who did it), you'll need to implement auditing or trace. I thought perhaps a DDL trigger could be used as well, but this seems like an intentional functionality gap:

http://connect.microsoft.com/SQLServer/feedback/details/509242/fire-a-ddl-trigger-when-the-new-syntax-disable-trigger-is-executed (abandoned)
https://connect.microsoft.com/SQLServer/feedback/details/434951/ddl-trigger-still-not-working-disable-trigger-is-this-not-an-event (abandoned)
https://feedback.azure.com/forums/908035-sql-server/suggestions/32898904-fire-a-ddl-trigger-when-the-newer-syntax-disable

Here is how you can capture this information going forward using an audit:
USE master;
GO

CREATE SERVER AUDIT MyAudit
  TO FILE (FILEPATH = 'C:\temp\', MAXSIZE = 1 GB)
  WITH (ON_FAILURE = CONTINUE);
GO

ALTER SERVER AUDIT MyAudit WITH (STATE = ON);
GO

USE [your_database];
GO

CREATE DATABASE AUDIT SPECIFICATION schema_db_change
  FOR SERVER AUDIT MyAudit
  ADD (SCHEMA_OBJECT_CHANGE_GROUP),
  ADD (DATABASE_OBJECT_CHANGE_GROUP);

ALTER DATABASE AUDIT SPECIFICATION schema_db_change WITH (STATE = ON);
GO

Now go and disable your trigger, and then run:
SELECT event_time, succeeded, server_principal_name, [object_name], [statement]
FROM sys.fn_get_audit_file ('C:\Temp\MyAudit*', NULL, NULL);

If you can't use audit due to edition or other issues, you could do this relatively inexpensively using a server-side trace. Just capture SQL:BatchCompleted and optionally filter on TextData LIKE '%disable%trigger%' (You'll have to test if it's better in your scenario to pay the cost for the filter to avoid collecting too much, or just collect more and whittle it down later. Filters can be quite expensive but it depends on the system.)
I'm sure there is also a way to do this with extended events. But XEvents and audit both require 2008+ and you didn't specify version...
Better yet, remove the ability to modify triggers for users who bypass change management. Ideally, you should be able to determine who enabled or disabled a trigger without ever looking at the database, because nobody should be doing that without documenting it.


If using a function in a case, does it run on each condition and result or is cached? In this example:
select case when someFunction() is null or someFunction() = 0 then 999 else someFunction() end

someFunction() runs three times or just one?

It runs every time you call it, in your case three times.
So rewrite the function that makes the case superfluous.


Mysql - lock wait timeout while updating 70 million records i have deployed my website and mysql server on a single server.

Disk space - 20 GB;

RAM - 16GB;

Currently there are 70 million records in the database and i am writing update query to change the "status" attributes of all this 70 million records. after executing the update query i am getting error as "mysql lock wait timeout". below is my sql  query

update authentication_codes set status=true;

I think i am getting this error because mysql is not able to execute this query within lock timeout specified in mysql cofig file. increase lock timeout value will work but in the future database will go on increasing so i think this is not good solution.

Please tell me what mysql settings i have to change  to increase the mysql performance. Do i have to increase the ram for processing 70 million records?? Do i have to increase hard disk size??

Redesign.  Relational databases let you update every row in a table, but it is poor design to need to do such.  Please elaborate on what you are really doing; we can probably discuss another way to do the task.

Compute the flag as needed instead of needing the massive update.
Avoid the flag completely.
Have a parallel table with nothing but the id and flag.
Walk through the table 1K rows at a time -- takes longer to finish, but is less invasive.  (No, do not use OFFSET.)

A disk of only 20GB is very tiny by today's standards.  How much of that is your 70M table?  It sounds like you have no room to maneuver.  So, we may have to take that into account when redesigning.

Please provide SHOW CREATE TABLE; perhaps we can shrink some of the datatypes to conserve space.


What schema does this mean 'mysql - schema's default character set: utf8'? While upgrading mysql from 5.6 -> 5.7 -> 8.0.23 in step 5.7 -> 8.0.23 I got a warning:
The following objects use the utf8mb3 character set. It is recommended to convert them to use utf8mb4 instead, for improved Unicode support.
More Information:
https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8mb3.html

common_schema - schema's default character set: utf8
mysql - schema's default character set: utf8
common_schema._global_qs_functions.function_arguments - column's default character set: utf8
common_schema._global_qs_variables.value_snapshot - column's default character set: utf8
common_schema._global_script_report_data.info - column's default character set: utf8
...

So what means 'mysql - schema's default character set: utf8'? how can I change it to utf8mb4?

common_schema is a tool that someone added to your server?  Sounds like it has not been updated to utf8mb4.
CHARACTER SET utf8 (aka utf8mb3) is old; utf8mb4 is new.  The latter can handle Emoji and more of the Chinese character set, plus some less common languages.
If you don't know anything about common_schema, ignore the warning.
In your tables, consider using utf8mb4.  This is the default in 8.0; you would have to take deliberate action not to use it.


SQL Server 2008 R2 - The request failed or the service did not respond in timely fashion SQL Server failed to start and throwing error - "The request failed or the service did not respond in timely fashion. Consult the event log or other applicable error logs for details."

I have tried the solution given here
I have tried multiple solutions but neither have worked\

Tried to built-in account to Local System, Local Service and Network service.
Reinstalled the SQL Server.
Disabled the VIA in SQL Server network configuration.

Any solution to solve my problem?
Window Error Log:

SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.
Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
TDSSNIClient initialization failed with error 0x80090331, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. The client and server cannot communicate, because they do not possess a common algorithm.
TDSSNIClient initialization failed with error 0x80090331, status code 0x80. Reason: Unable to initialize SSL support. The client and server cannot communicate, because they do not possess a common algorithm.

SQL Server error log:

The server could not load the certificate it needs to initiate an SSL connection. It returned the following error: 0x80090331. Check certificates to make sure they are valid.
Unable to initialize SSL encryption because a valid certificate could not be found, and it is not possible to create a self-signed certificate.
Error: 17182, Severity: 16, State: 1.TDSSNIClient initialization failed with error 0x80090331, status code 0x80. Reason: Unable to initialize SSL support. The client and server cannot communicate, because they do not possess a common algorithm.

When the server cannot start, the first thing to consult is SQL Server error log.
There can be different reasons, for example, banally the port assigned is occupied. But in OP case it was

  The server could not load the certificate it needs to initiate an SSL
  connection

The error message itself suggested what to do:

  Check certificates to make sure they are valid

Another option in this case was to remove encryption at all if it was configured by mistake