Cannot remove orphaned InnoDB Temp Table - missing .frm file

You have a disconnect between what you have on disk and what you have on the index internal data dictionary. MySQL 8.0 has improved how the data dictionary is handled, but up to 10.4 mariadb has not implemented it.

You cannot just drop it, because it won't be found on disk, but innodb still see it. For older versions of MySQL and MariaDB you will find several guides online on how to synchronize the data dictionary and the disk. However, this is the way I found more effective and secure:

Create a temporary database and move all "good" tables, views, etc. from the original table to the new one (this is very fast as it only is a rename table, as long as the new database is on the same partition)
Drop the now empty database (so the innodb index will drop all its remaining contents. Drop content on disk if necessary too.
Recreate the old database, now completely empty in the InnoDB internal dictionary and data
Move back the tables through rename

I have used this several times and I think it is the easiest, fastest and more secure way to synchronize the partially deleted tables.

Important, #sql-4593_1e9 is the format for temporary tables, please don't drop those unless you think they are really leftovers- there could be transactions ongoing that need those internally for queries. In other words, I would not touch this unless they appear on a new server start without any connections.


How to use timestamp/rowversion data types in Visio?


Restore Verify Only w/ Transaction Log Backups

But this brings up a question about how to proceed when RESTORE VERIFY ONLY fails.Let's say a trx log backup completes successfully, but RESTORE VERIFY ONLY fails

Restore verifyonly(as per BOL)Verifies the backup but does not restore it, and checks to see that the backup set is complete and the entire backup is readable. However, RESTORE VERIFYONLY does not attempt to verify the structure of the data contained in the backup volumes. So even if backup set comes as verified clean by Verifyonly its not 100% guaranteed that backup set is consistent only a successful restore of a backup can guarantee that backup set is valid.

I have not see an scenario where verifyonly fails but restores succeeds(unless you succeed with using continue_after_error) it can only happen when verifyonly failed saying there is not enough space to check backup consistency because verifyonly also checks that enough space is there to restore the database

Checks performed by RESTORE VERIFYONLY include:

•That the backup set is complete and all volumes are readable.

•Some header fields of database pages, such as the page ID (as if it were about to write the data).

•Checksum (if present on the media).

•Checking for sufficient space on destination devices.

Please show me the message which you got when verifyonly failed

After Verifyonly fails

I would run a checkdb on my database to check for any inconsistency but point here to note is checkdb does not do consistency check for log file only when recovery on snapshot is run during checkdb log file is used so we 'can' say that it checks for log file as well but not complete like it does for data file. If checkdb comes out clean I would say my database is consistent.

  The backup spans LSNs X to Y. I can't simply take another trx log backup to "fix" the situation: the subsequent trx log backup would span a different range of LSNs (Y+1 to Z). At this point, the log chain is effectively broken, right?

Yes you are correct taking multiple backup does not fixes corruption in log backup but taking multiple log backups does not break log chain. Log files are inked internally with LSN number and taking multiple log backup does not breaks chain if log backups are restored in sequential manner

  My first thought is to immediately take a DIFFERENTIAL backup. What would you do?

It wont solve corruption issue in any way. Differential backups are means to lower the RTO and perform speedy recovery of database thats it.

EDIT AFTER USER UPDATED HIS QUESTION:

  the best I could do in a DR situation is to restore the FULL backup and six subsequent trx log backups

Yes you are correct.

  If I had taken a DIFFERENTIAL backup soon after #7 failed, in a DR situation I could:

Restore the FULL backup
Restore the DIFFERENTIAL backup
Restore transaction log backups 8, 9, and 10

Yes you can restore differential backup, after you cleared question with details I have updated the answer. You can restore differential backup and then restore log backups 8,9,10 as they are taken after diff backup


From a set of values, how do I find the values not stored in a table's column?

Your second solution using the LEFT JOIN is by far the best approach. I would not use a temporary table, I'd use a regular table and populate it with new values any time you wanted run the query.


Is there a way to Oracle expdp not inherit char type nls semantics on DDL?

I made a quick and dirty solution which worked for me today (Oracle 12.1), no reason to wait for the next 11 years.

create or replace trigger T_mutate_len_semantics after create on database
when (SYS.DICTIONARY_OBJ_TYPE = 'TABLE' and SYS.DICTIONARY_OBJ_OWNER not in ('SYS','SYSTEM'))
 declare
    stmt varchar2(2000);
begin
  --debug
  dbms_system.ksdwrt(2,'T_mutate_len_semantics: After creating table '||SYS.DICTIONARY_OBJ_NAME);
  for r in (select column_name, data_length, data_type
            from dba_tab_columns
            where table_name = SYS.DICTIONARY_OBJ_NAME
              and owner = SYS.DICTIONARY_OBJ_OWNER
              and data_type in ('VARCHAR2','CHAR')
              and DATA_LENGTH = CHAR_LENGTH
            )
    loop
        stmt := 'alter table '||SYS.DICTIONARY_OBJ_OWNER||'.'||SYS.DICTIONARY_OBJ_NAME||' modify ('||r.column_name||' '||r.data_type||'('||r.data_length||' CHAR))';
        dbms_system.ksdwrt(2,'T_mutate_len_semantics: '||stmt);
        execute immediate stmt;
    end loop;
end;
/


From http://www.dbasquare.com/2012/05/15/why-do-threads-sometimes-stay-in-killed-state-in-mysql/:

  What actually happens when you run KILL?

  The command sytnax is KILL [QUERY | CONNECTION] , where
  thread_id is the value from the first column of the process list
  output. The optional argument determines whether only running query or
  should the entire session be terminated. It defaults to the latter, so
  specifying CONNECTION is not required.

  Running the command doesn’t actually do anything except for setting a special flag inside the selected thread. Therefore the kill
  operation doesn’t happen synchronously with the corresponding request.
  In many cases it takes some time for a thread or a query to stop. The
  flag is checked at various stages of statement execution. When it
  happens exactly, or how frequently, depends on the work a thread is
  actually doing.

...

  No matter what the real cause is, it is impossible to get rid of
  such hanging threads without a database restart. They should,
  however, simply be allowed to go away on their own.

  There is also no way of forcing a kill that would execute instantly like kill -9  in Unix systems.


Can I update my Microsoft Enterprise SQL Server?

Given that you work for a company that is using Enterprise Edition I'm going to assume you aren't touching one of their servers.  If you are please don't. As mentioned in the comments this could be a very expensive mistake and if the work you are doing is on any company asset please contact the company's DBA. That's what they are there for.

That said, let me explain a few things.

SQL Server Management Studio is an interface used to connect to the SQL Server Database Engine (among other things).  It's version is completely separate these days from the database engine.  So you could very easily be on SSMS 18 while working with SQL Server 2008.
While some functionality is actually part of the GUI things like STRING_AGG are actually functions of the language T-SQL that is not part of the GUI and requires you update the database engine.

It looks like you got this far on your own but I just wanted to be sure.

Now, if you are trying to set yourself up a home lab, or are you a company workstation and have permission to install a single user development version of SQL Server then it's perfectly reasonable to want to upgrade SQL to try out new functionality. In this case you'll want the Developer Edition of SQL Server.  It's a free single user license that can only be used for development/experimentation. It's also has the same functionality as the Enterprise version of SQL Server so you'll have everything you want.

You can get it here: https://www.microsoft.com/en-us/sql-server/sql-server-downloads

If your home machine/workstation has the resources I would recommend installing more than one version of SQL on it. The version you are working on at work, and the latest edition that you want to play with.

Last thing, if you are working on a shared environment you really need to contact your DBA and make sure that nothing has been broken. Upgrading SQL can have implications on existing functionality and could potentially break an existing application.


Let's assume the value of @Parameter is dbA or dbB

SELECT * FROM dbA.User where @Parameter = 'dbA'
UNION ALL
SELECT * FROM dbB.User where @Parameter = 'dbB'


how to write queries properly inside of a postgres RLS policy

The USING clause of the CREATE POLICY command expects an expression inside the parentheses. An SQL query, by itself, is not an expression, but you can make it into one by writing it as a scalar subquery. So you just need another pair of parentheses:

CREATE POLICY ..
USING (
  (SELECT p.owner_id = ANY (app_pub.get_current_role_ids ())
   FROM app_pub.projects p
   WHERE p.id = project_id)
);


Getting generic Oracle error message 'missing right parenthesis'

This is not a "more generic error", it indicates that the order of some clause in your SQL statement is incorrect, and Oracle does not find a valid clause where it expects it. You should check the manual for the correct syntax for CREATE TABLE.
There you will find in particular this description of a column definition:

column [ datatype [ COLLATE column_collation_name ] ]
  [ SORT ] [ VISIBLE | INVISIBLE ]
  [ DEFAULT [ ON NULL ] expr | identity_clause ]
  [ ENCRYPT encryption_spec ]
  [ { inline_constraint }...
  | inline_ref_constraint
  ]

that tells you that the DEFAULT clause comes before any constraints, of which NOT NULL is one.


Does it make sense to put a unique constraint on a nullable soft-delete flag?

You don't mention what DBMS you are referring to, but your suggested unique constraint won't work for several out there, because null is not equal to null

create table t (x int not null, y int, unique(x,y));
insert into t (x) values (1);
insert into t (x) values (1);

Postgres12
Does not violate the constraint

Oracle18c
Violates constraint

Db2 Developer C 11.1
Does not accept nullable columns in unique constraint

Firebird 3.0
Violates constraint

MariaDB 10.4
Does not violate the constraint

MySQL 8.0
Does not violate the constraint

SQLlite 3.27
Does not violate the constraint

SQLServer 2017
Violates constraint

Tested at DB<>Fiddle

So, generally speaking, your UNIQUE constraint does not work. Consider changing ACTIVE to

ACTIVE SMALLINT NOT NULL,
CHECK (ACTIVE BETWEEN 0 AND 1),
UNIQUE (START, COMPANYID, ACTIVE)

Though I'm not convinced that a certain COMPANYID at a particular START can be both ACTIVE and NOT ACTIVE, but then I don't know your business.

EDIT:
Given the new info in the question, something like this might be possible (I don't have access to the docs for system i right now, so I don't know whether generated columns, or constraints on expressions exists)

create table t
( a int not null generated always as identity
, x int not null
, y int
, z int not null generated always as (
    case when y is null then -1*a else y end
)
, unique(x,z)
, check (y is null or y = 1)
);

insert into t(x) values (1); -- ok
insert into t(x) values (1); -- ok
insert into t(x,y) values (1,1); -- ok
insert into t(x,y) values (1,1); -- fails because of constraint violation.


Multi language full text search using postgresql

You should definitely use a different column per language.

The main reason is that different languages have different stop words and stemming rules, so if you index something with to_tsvector('spanish', ...), you will not always find it with a to_tsquery('english', ...) and vice versa:

SELECT to_tsvector('spanish', 'hembra azul') @@ to_tsquery('english', 'hembra');
 ?column?
----------
 f
(1 row)

Even better would be not to create a column per language, but only GIN indexes on to_tsvector('english', (tags->'tags'->'en')) and to_tsvector('spanish', (tags->'tags'->'es')). For example:

CREATE TABLE images (
   id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
   image bytea NOT NULL,
   tags jsonb NOT NULL
);

CREATE INDEX images_tags_en_idx ON images
   (to_tsvector('english', (tags->'tags'->'en')));

CREATE INDEX images_tags_es_idx ON images
   (to_tsvector('spanish', (tags->'tags'->'es')));

Then you can use the first index with

SELECT * FROM images
WHERE to_tsvector('english', (tags->'tags'->'en'))
      @@ to_tsquery('english', 'female');


Does TRY/CATCH properly handle this?

BEGIN TRY
    INSERT dbo.localtable SELECT ... FROM linkedserver...table;
END TRY
BEGIN CATCH
    PRINT 'this table was skipped';
END CATCH


Which Plan is Better? WHERE EXISTS Instead of INNER JOIN

Here are stats of two plans:

Old plan: You've got a 1,864 memory grant, estimated number of rows 400
New plan: You've got a 69,056 memory grant, estimated number of rows 43,017

So we have two options:

Either those are estimated execution plans and if so, you should run the queries and get the actual execution plan
Either those two queries don't return the same amount of rows and if so, they are not equivalent

In order to compare right, you should try checking statistics on your tables also, and run the queries with the option recompile to make sure it doesn't take an inappropriate plan in cache.

Old plan

New Plan

---------- EDITED TO EXPLAIN THE DIFFERENCE OF MEMORY GRANT --------------

Here is an important detail that I missed the first time I looked at your plans :

Old plan: The estimated number of rows is 400 but the actual number of rows is 29899. Since SQL only did a memory grant of 1864 for 400 rows, it probably spilled to TempDb.
New plan: The estimated number of rows is 43017 but the actual number of rows is 29904. So SQL did a memory grant of 69056 for 43017 rows, but only needed the memory for 29904 rows. So in this case, it took too much memory.

So in both case, the number of rows is almost the same. To know which one is best is a tricky question and will depend on your hardware and your server. If I/O is a problem and tempDB is under high utilization, then the second one could fix some problems for you. But if your server is under memory pressure, then the first one is better.
In order to better understand the two options, you should see this great blog by Brent Ozar about memory grant

Old plan filter

New plan filter


reading content of master database at a different server

Can I restore msdb/master databases at different server as regular user databases and make sense of those mentioned queries?

Couple of options you can use without restoring system database :

GUI - Registered Servers

PowerShell


user can select all structure from pg_catalog.*, but can't make a dump -s

DUMP pg_catalog

Here's simple script to dump objects' structures in pg_catalog.

pg_dump -p 5432 -U postgres -d postgres -s --table=pg_catalog.* > catalog.sql

And the result looks like that:

CREATE TABLE pg_authid (
    rolname name NOT NULL,
    rolsuper boolean NOT NULL,
    rolinherit boolean NOT NULL,
    rolcreaterole boolean NOT NULL,
    rolcreatedb boolean NOT NULL,
    rolcanlogin boolean NOT NULL,
    rolreplication boolean NOT NULL,
    rolbypassrls boolean NOT NULL,
    rolconnlimit integer NOT NULL,
    rolpassword text,
    rolvaliduntil timestamp with time zone
);
ALTER TABLE ONLY pg_authid REPLICA IDENTITY NOTHING;
ALTER TABLE pg_authid OWNER TO postgres;

UPDATE from Vao Tsun:

From pg_dump doc:

  pg_dump internally executes SELECT statements. If you have problems
  running pg_dump, make sure you are able to select information from the
  database using, for example, psql

From psql doc

  By default, only user-created objects are shown; supply a pattern or
  the S modifier to include system objects.

  Note: If \d is used without a pattern argument, it is equivalent to
  \dtvsE which will show a list of all visible tables, views, sequences
  and foreign tables

In terms of your case  "user without permissions". I guess the so15 table belongs to public schema. That's why so15 user can see table structure (\d+ so15).

For example: if you login admin user and create new table on new schema, then login so15 user to test ( remember to set search_path = new_schema before doing \d+). You cannot see this new table because of permission.

Besides, you cannot dump so15 table due to pg_dump will execute SELECT and it needs permission on this table.

Solution: to dump so15 table you should grant privilege to so15 user.


Pair of 2-tuple composite FKs pointing to same table (closure table pattern)

I don't think this will work in MySQL. It has to do with how MySQL checks constraints (after each and every row update) and not at the end of statement or at the end of transaction. In combination with the double cascade path from father to closure, make this impossible in MySQL with Referential Integrity constraints alone (again, I think it is, if someone posts a solution, I'll be happy to remove my answer.)

A solution in MySQL will probably include some procedure that handles the cascaded updates and deletes itself and with great care, so the constraints hold during every baby step (i.e. every row update.)

In Postgres, it just works though: SQL-Fiddle


Will installing a second SQL Instance cause an outage on existing instances?

You may require to restart before the install to pass prerequisites or after the install to complete the installation.
You should be prepared to reboot the server.


Why the WHERE clause is not pushed down in the view's query?

This query:
SELECT DISTINCT ON(recipient) * FROM messages
LEFT JOIN identities ON messages.recipient = identities.name
WHERE messages.timestamp BETWEEN timeA AND timeB
ORDER BY recipient, timestamp DESC;

says:

For all messages between timeA and timeB, find the recipients and for every recipient, find one message (the latest in between timeA and timeB).

This query (which you would get if you use the view):
SELECT *
FROM
  ( SELECT DISTINCT ON(recipient) * FROM messages
    LEFT JOIN identities ON messages.recipient = identities.name
    ORDER BY recipient, timestamp DESC
  ) AS myView
WHERE timestamp BETWEEN timeA AND timeB;

says:

For all messages, find the recipients and for every recipient, find one message, (the latest in all times) and then, show only those messages that are between timeA and timeB.

As a result, the first query will show messages that are between time A and
B which the second query will not show (because there may be one or more messages for the same recipient, later than time B).
So, the queries are logically different and the condition cannot (and shouldn't) be pushed down for your view.
If you want to have parameters passed to your view, look the two answers (by @a_horse_with_no_name and @Erwin Brandstetter) in this question for how to use a set returning function: Pass In “WHERE” parameters to PostgreSQL View?


11.2GB text file import MySQL or PostgreSQL

For PostgreSQL, I would recommend using pgloader.

pgloader leaves the rows that it cannot import in a separate text file, so you can take a look at what went wrong, fix the errors, and finish importing the data. COPY simply crashed when it encounters a row it didn't expect, which is likely to happen several times for very big files.

pgloader can also be set up to load data in parallel, but I'm not sure how much help it would be if you're just loading a single table.

For MySQL, the standard LOAD DATA LOCAL INFILE is as fast as you're going to get (as far as I know). It spits out a warning for each row that it cannot import, so you can go back and look at that data later.

Other than that, 11.2 GB isn't too bad. Be sure to remove all existing indexes on the table that you're importing, and recreate the indexes afterwards.

I've imported text files of comparable size into both databases without a problem. Loading data and creating indexes might take a few hours, so just leave it overnight.


Minimum Permission to Restart SQL Services

You can  grant the Service Start/Stop right for the SQL Server and Agent service to the Developers or their Windows group.

You can use

Use Group Policy
Security Templates or
Use Subinacl.exe

Above all are described in this KB article How to grant users rights to manage services

As a side note, you can use sysinternals tool - accesschk.exe. It will help you to find out -

  what kind of accesses specific users or groups have to resources including files, directories, Registry keys, global objects and Windows services.


Consolidated realtime mysql backup with master/slave setup or binlog

Till MySQL 5.6 released version, Multiple master to single slave replication is not possible. This type of replication is under development in MySQL 5.7 version.

you can refer below link:-

MySQL 5.7 Multi-Source-Replication


Modelling a database structure for multiple user types and their contact information

According to my interpretation of your description of the relevant business context, you are dealing with a supertype-subtype1 structure where (a) Actor, Director and Writer are entity subtypes of (b) Person, their entity supertype, and (c) said subtypes are not mutually exclusive.
In this way, if you are interested in building a relational database that mirrors such a scenario accurately —and hence are expecting that it functions as such—, your following comment clarifications are quite significant with respect to the previous points, because they have implications at both (1) the conceptual and (2) the logical levels of representation of the database in question:

[…] additional tables for each of the respective user types that each have their own unique set of columns.
[…] there are just four user types that are of relevance. There is a outside chance that this number may increase, but the probability is low - and in such a case would be by a very small number.

I will elaborate on all those aspects and several other critical factors in the sections below.
Business rules
In order to first define the corresponding conceptual schema —which can be used as a subsequent reference so that you can adapt it to make certain that it meets the exact informational requirements—, I have formulated some business rules that are of particular importance:

A Person may carry out one-two-or-three (i.e., one-to-all) Roles2. In other words, a Person may be

an Actor and/or
a Director and/or
a Writer.

A Person may log in via zero-or-one UserProfile.
An Actor provides one-two-or-three URLs3.
An Actor is grouped by one Ethnicity.
An Ethnicity groups zero-one-or-many Actors.
A Writer is based in one Location.
A Location is the base of zero-one-or-more Writers.

Expository IDEF1X diagram
Then, I created the IDEF1X4 diagram shown in Figure 1, which groups all the formulations above along with other rules that appear pertinent:

As demonstrated, the Person supertype (i) has its own box, (ii) possesses the properties or attributes that apply to all the subtypes, and (iii) presents lines that connect it with the boxes of every subtype.
In turn, every subtype (a) appears in its own dedicated box, and (b) holds only its applicable properties. The PRIMARY KEY of the supertype, PersonId, migrates5 to the subtypes with the role names6 ActorId, DirectorId, and WriterId respectively.
Also, I avoided coupling Person with the UserProfile entity type, which permits separating all their contextual implications, associations or relationships, etc. The PersonId property has migrated to UserProfile with the role name UserId.
You state in the question body that

And all actors will have to include at least one URL for any of their other online actor profiles; currently there are three that they can include, but this number may increase.

…so URL is an entity type in its own right, and is directly associated with the Actor subtype in accordance with this quote.
And, in comments, you specify that

[…] an actor will have a headshot (photo), while a writer will not […]

…then, among other features, I included Headshot as a property of the Actor entity type.
As for the Ethnicity  and the Location entity types, they of course may entail more complex organizations (e.g., an Actor may belong to one, two or more different ethnic groups in distinct proportions, and a Writer may be based on a place that requires recording country, administrative region, county, etc.) but it looks like that the needs of your business context are covered successfully with the structures here modelled.
Naturally, you can make as many adjustments as necessary.
Illustrative SQL-DDL logical design
Consequently, based on the IDEF1X diagram shown and described above, I wrote the logical DDL layout that is shown as follows (I have supplied notes as comments that explain some of the characteristics that I esteem particularly important with respect to the tables, columns and constraints declared):
-- You should determine which are the most fitting
-- data types and sizes for all your table columns
-- depending on your business context characteristics.

-- Also, you should make accurate tests to define the
-- most convenient INDEX strategies based on the exact
-- data manipulation tendencies of your business needs.

-- As one would expect, you are free to utilize
-- your preferred (or required) naming conventions.

CREATE TABLE Person ( -- Represents the supertype.
    PersonId        INT      NOT NULL,
    FirstName       CHAR(30) NOT NULL,
    LastName        CHAR(30) NOT NULL,
    BirthDate       DATE     NOT NULL,
    GenderCode      CHAR(3)  NOT NULL,
    TwitterProfile  CHAR(30) NOT NULL,
    PhoneNumber     CHAR(30) NOT NULL,
    EmailAddress    CHAR(30) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT Person_PK  PRIMARY KEY (PersonId),
    CONSTRAINT Person_AK1 UNIQUE ( -- Composite ALTERNATE KEY.
        FirstName,
        LastName,
        GenderCode,
        BirthDate
    ),
    CONSTRAINT Person_AK2 UNIQUE (TwitterProfile), -- ALTERNATE KEY.
    CONSTRAINT Person_AK3 UNIQUE (EmailAddress)    -- ALTERNATE KEY.
);

CREATE TABLE Ethnicity ( -- Its rows will serve a “look-up” purpose.
    EthnicityId     INT      NOT NULL,
    Name            CHAR(30) NOT NULL,
    Description     CHAR(30) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT Ethnicity_PK PRIMARY KEY (EthnicityId),
    CONSTRAINT Ethnicity_AK UNIQUE      (Description)
);

CREATE TABLE Actor ( -- Stands for one of the subtypes.
    ActorId         INT      NOT NULL, -- Must be constrained as (a) the PRIMARY KEY and (b) a FOREIGN KEY.
    Headshot        CHAR(30) NOT NULL, -- May, e.g., contain a URL indicating the path where the photo file is actually stored.
    EthnicityId     INT      NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT Actor_PK            PRIMARY KEY (ActorId),
    CONSTRAINT ActorToPerson_FK    FOREIGN KEY (ActorId)
        REFERENCES Person (PersonId),
    CONSTRAINT ActorToEthnicity_FK FOREIGN KEY (EthnicityId)
        REFERENCES Ethnicity (EthnicityId)
);

CREATE TABLE Director ( -- Denotes one of the subtypes
    DirectorId      INT       NOT NULL, -- Must be constrained as (a) the PRIMARY KEY and (b) a FOREIGN KEY.
    Bio             CHAR(120) NOT NULL,
    Etcetera        CHAR(30)  NOT NULL,
    CreatedDateTime DATETIME  NOT NULL,
    --
    CONSTRAINT Director_PK         PRIMARY KEY (DirectorId),
    CONSTRAINT DirectorToPerson_FK FOREIGN KEY (DirectorId)
        REFERENCES Person (PersonId)
);

CREATE TABLE Country (
    CountryCode     CHAR(2)  NOT NULL,
    Name            CHAR(30) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT Country_PK PRIMARY KEY (CountryCode),
    CONSTRAINT Country_AK UNIQUE      (Name)
);

CREATE TABLE Location ( -- Its rows will serve a “look-up” purpose.
    CountryCode     CHAR(2)  NOT NULL,
    LocationCode    CHAR(3)  NOT NULL,
    Name            CHAR(30) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT Location_PK PRIMARY KEY (CountryCode, LocationCode),
    CONSTRAINT Location_AK UNIQUE      (CountryCode, Name)
);

CREATE TABLE Writer ( -- Represents one of the subtypes.
    WriterId        INT      NOT NULL, -- Must be constrained as (a) the PRIMARY KEY and (b) a FOREIGN KEY.
    CountryCode     CHAR(2)  NOT NULL,
    LocationCode    CHAR(3)  NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT Writer_PK           PRIMARY KEY (WriterId),
    CONSTRAINT WriterToPerson_FK   FOREIGN KEY (WriterId)
        REFERENCES Person (PersonId),
    CONSTRAINT WriterToLocation_FK FOREIGN KEY (CountryCode, LocationCode)
        REFERENCES Location (CountryCode, LocationCode)
);

CREATE TABLE UserProfile (
    UserId          INT      NOT NULL, -- Must be constrained as (a) the PRIMARY KEY and (b) a FOREIGN KEY.
    UserName        CHAR(30) NOT NULL,
    Etcetera        CHAR(30) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT UserProfile_PK         PRIMARY KEY (UserId),
    CONSTRAINT UserProfile_AK         UNIQUE      (UserName), -- ALTERNATE KEY.
    CONSTRAINT UserProfileToPerson_FK FOREIGN KEY (UserId)
        REFERENCES Person (PersonId)
);

CREATE TABLE URL (
    ActorId       INT      NOT NULL,
    Address       CHAR(90) NOT NULL,
    Etcetera      CHAR(30) NOT NULL,
    AddedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT URL_PK        PRIMARY KEY (ActorId, Address), -- Composite PRIMARY KEY.
    CONSTRAINT URLtoActor_FK FOREIGN KEY (ActorId)
        REFERENCES Actor (ActorId)
);

This has been tested in this db<>fiddle that runs on MySQL 8.0.
Therefore, (1) every singular aspect of the logical layout above carries a very precise meaning from (2) a singular feature of the business environment of interest7 —in agreement with the spirit of the relational framework by Dr. Edgar Frank Codd—, because:

Each base table represents an individual entity type.

Each column stands for a single property of the respective entity type.

A specific data type is fixed for each column in order to ensure that all the values it contains belong to a particular and properly-delimited set a , be it INT, DATETIME, CHAR, etc (and let us hope that MySQL will finally incorporate DOMAIN support in near-future version).

Multiple constraints are configured (declaratively) in order to guarantee that the assertions in form of rows retained in all the tables comply with the business rules determined at the conceptual level.

Each row is meant to convey well-defined semantics, e.g., a Person row is read:
The Person identified by PersonId r is called by the FirstName s and the LastName t, was born on BirthDate u, has the GenderCode v, tweets on the TwitterProfile w, is reached through PhoneNumber x, is contacted via the EmailAddress y, and was registered on CreatedDateTime z.

Having a layout like this is decidedly favourable, as you can derive new tables (e.g., SELECT operations that gather columns FROM multiple tables with the help of the JOIN clause) that —in succession— carry a very precise meaning too (see the section entitled “Views” below).
It is woth to mention that, with this configuration, (i) a row representing a subtype instance is identified by (ii) the same PRIMARY KEY value that distinguishes the row denoting the complementary supertype occurrence. Thus, it is more than opportune to note that

(a) attaching an extra column to hold system-generated and system-assigned surrogates8 to (b) the tables standing for the subtypes is (c) entirely superfluous.

With this logical design, if new subtypes are defined as relevant in your business context, you would have to declare a new base table, but that happens as well when other kinds of entity types are deemed of significance, so said situation would be, in fact, ordinary.
Views
In order to “fetch”, e.g., all the information that corresponds to an Actor, Director or Writer, you can declare some views (i.e., derived or expressible tables) so that you can SELECT directly from one single resource without having to write the concerning JOINs every time; e.g., with the VIEW declared below, you can obtain the “full” Actor information:
--
CREATE VIEW FullActor AS

    SELECT P.FirstName,
           P.Lastname,
           P.BirthDate,
           P.GenderCode,
           P.TwitterProfile,
           P.PhoneNumber,
           P.EmailAddress,
           A.Headshot,
           E.Name AS Ethnicity
         FROM Person P
         JOIN Actor A
           ON A.ActorId     = P.PersonId
         JOIN Ethnicity E
           ON E.EthnicityId = A.EthnicityId;
--

Of course, you may follow a similar approach in order to retrieve the “full” Director and Writer information:
--
CREATE VIEW FullDirector AS

    SELECT P.FirstName,
           P.Lastname,
           P.BirthDate,
           P.GenderCode,
           P.TwitterProfile,
           P.PhoneNumber,
           P.EmailAddress,
           D.Bio,
           D.Etcetera
         FROM Person P
         JOIN Director D
           ON D.DirectorId = P.PersonId;

--
CREATE VIEW FullWriter AS

    SELECT P.FirstName,
           P.Lastname,
           P.BirthDate,
           P.GenderCode,
           P.TwitterProfile,
           P.PhoneNumber,
           P.EmailAddress,
           L.Name AS Location,
           C.Name AS Country
         FROM Person P
         JOIN Writer W
           ON W.WriterId     = P.PersonId
         JOIN Country C
           ON C.CountryCode  = W.CountryCode
         JOIN Location L
           ON L.LocationCode = W.LocationCode;
--

The DML views here discussed are as well included in this MySQL 8.0 db<>fiddle so that you can see and test them “in action”.

Endnotes
1 In some conceptual modelling techniques, supertype-subtype associations are referred to as superclass-subclass relationships.
2 Although you mention that there exist in fact more Roles that a Person may perform, but the three you revealed are good enough to discuss the scenario exposing several important ramifications.
3 But, as you noted, in the future an Actor might eventually provide one-to-many URLs.
4 Integration Definition for Information Modeling (IDEF1X) is a highly recommendable modelling technique that was established as a standard in December 1993 by the United States National Institute of Standards and Technology (NIST). It is based on (a) the early theoretical works authored by the sole originator of the relational model of data, i.e., Dr. E. F. Codd; on (b) the entity-relationship view, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown.
5 The IDEF1X standard defines key migration as “The modeling process of placing the primary key of a parent or generic [i.e., a supertype] entity in its child or category entity [i.e., a subtype] as a foreign key”.
6 In IDEF1X, a role name is a distinctive label assigned to a FK attribute in order to express the meaning that it holds within the scope of its respective entity type.
7 Except, naturally, for the hypothetical conceptual properties (and logical columns) Director.Etcetera and UserProfile.Etcetera, which are merely placeholders that I used to expose the posibility of adding more properties (and columns) that apply to the corresponding conceptual entity type (and logical table).
8 E.g., appending an additional column with the AUTO_INCREMENT attribute to a table of a database “running” on MySQL.


In case of aligning identity columns, run this on the source:

 SELECT
        IDENT_SEED(TABLE_SCHEMA+'.'+TABLE_NAME) AS Seed,
        IDENT_INCR(TABLE_SCHEMA+'.'+TABLE_NAME) AS Increment,
        IDENT_CURRENT(TABLE_SCHEMA+'.'+TABLE_NAME) AS Current_Identity,
        TABLE_SCHEMA+'.'+TABLE_NAME as table_name,
        'DBCC CHECKIDENT('''+TABLE_SCHEMA+'.'+TABLE_NAME+''', RESEED,'+CAST(IDENT_CURRENT(TABLE_SCHEMA+'.'+TABLE_NAME) +1  AS VARCHAR(10))+')' as Command
    FROM
        INFORMATION_SCHEMA.TABLES
    WHERE
        OBJECTPROPERTY(OBJECT_ID(TABLE_SCHEMA+'.'+TABLE_NAME),'TableHasIdentity') = 1
    AND TABLE_TYPE = 'BASE TABLE'
    AND TABLE_NAME not like 'Mspeer%'
    AND TABLE_NAME not in ('sysarticles','syspublications')
    ORDER BY TABLE_SCHEMA, TABLE_NAME

The code to run on the replica is in a column


Yes, there are several ways to authenticate, but you have to specify this per database user. Before MariaDB 10.4 each user could only use one authentication method. You can change the authentication method for a user if you can log in as the root or other sufficiently privileged user. Use the ALTER USER statement if you're on MariaDB 10.2+, otherwise use UPDATE mysql.user SET plugin = '...' WHERE user = 'the_user' AND host = 'the_host';.

Authentication is done by authentication plugins. Run e.g. this to see which plugins you currently use for your database users:

SELECT user, host, plugin FROM mysql.user;

Some common authentication plugins are mysql_native_password and unix_socket. The former requires a password and works over TCP whereas the latter works over the MariaDB socket file (by default it's at /var/lib/mysql/mysql.sock in Linux) and doesn't require a password, but it assumes your OS user has the same name as your database user, and obviously only works if you're on the same host as the MariaDB server. Perhaps this is something you could try to avoid SSL?

Relevant MariaDB Knowledgebase documentation

ALTER USER - authentication options
Pluggable Authentication Overview
Authentication Plugin - Unix Socket
Authentication from MariaDB 10.4


Finally got it working!!!

  Since the pg_hba.conf records are examined sequentially for each connection attempt, the order of the records is significant. Typically, earlier records will have tight connection match parameters and weaker authentication methods, while later records will have looser match parameters and stronger authentication methods.


Set up replication and migrate to InnoDB with low downtime

Ok,

Here a sample of config to setup your replication :

On Master in your my.cnf add :

server-id                       = 1
log-bin                        = /database/bin-log/mysql-bin
binlog_cache_size              = 16M
slave_compressed_protocol       = 1
binlog_format                  = MIXED
max_binlog_size                 = 100M

On your slave in my.cnf add :

server-id                       = 2
relay-log-space-limit           = 50G
relay-log                       = /database/relay-log/relay-bin
relay-log-index                 = /database/relay-log/relay-bin.index
skip-slave-start
read_only                       = 1

Now you have two solutions, dump or binary copy.

In both case you should made your transformation (myisam to InnoDB) on your slave  slave and switch your slave to master. Also you should active binlogs to catch the master position.

First solution, you can make a dump of your master ( with the master-data option to catch master position) On your .sql dump file, search and replace ENGINE=myisam to InnoDB and load thé dump to thé slave server. You have all tables on InnoDB engine :) after started réplication and  catching up your the late : "second behind master= 0 " it´s time to switch slave to master ( with a new dump (now from slave to master) and take care to switch the conf also).

The second solution is the same but with a cold backup, stop mysql, copy datadir (4gb Will probably done in ~ 10mins dépends on your solution : scp, usb keys...) start your slave and alter each table with engine=innodb. Like thé first one, when it´s done, you should switch master/slave and alter ex-master tables.

Caution with "alter table engine=InnoDB"
 Mysql Will create a InnoDB table and copy data in it so your table size is tablesize x 2 (when innodb table is dobe, mysql drop myisam table)

I'm on my phone so sorry if there are many strange word... :)

(I didn't detailed replication set up cause it's not the subject but we let me know if you want some help.)


SQL query, group by and number of occurrences

This should work:

select count(*), a.N FROM (SELECT count(*) as N FROM table1 GROUP BY x) a GROUP BY a.N;

SQLFiddle

Note that it gives you 3 for N = 3, because there are actually three number repeated thrice: 1, 3 and 7.


Application testing: using multiple users in Oracle instead of mem H2. How to make it as fast as possible?

Speed up 2:

If you create the tablespace with let's say 100M and extend it with 50M or 100M, your database is much quicker than with 500k extension. This way the database keeps extending the file over and over again.
You can also use ASM which is much faster in creating files than traditional file-systems.

  EDIT1: ASM has a different structure than normal filesystems. For example crating a 32G file in ext4 takes 3-8 minutes. In ASM it just takes a fraction of a second. The reason is, in ASM you don't have to update lots of filesystem records to allocate the space. I would say in general ASM is slightly faster than other filesystems because it is optimized for Oracle databases.

  Speed up 3:

How do you populate the database with test data?

impdp: If you have EE you can load with multiple threads
insert-statement file: there is not much you can do
sqlLoader: sqlLoader is pretty fast and has some options to tune the process
INSERT INTO AS SELECT... in EE you can use parallel query, in SE you have to split it up and start multiple external threads.

Optimize 5:

Like ibre5041 mentioned you don't have to remove the tablespaces all the time. If you still want to create a new tablesapce for each test then remove the tablespace with drop tablespace test_user including contents and datafiles;. This command will remove the datafiles as well.
You can use the default temporary tablespace. You don't need a temp tbs for each user.

  EDIT1:

  Each database has a default temporary tablespace which is being used by the database users if you do not specify one. So if you configure the database as follows:

  alter database default temporary tablespace temp;

  (this is the default configuration by the way)

  You can create a user with this command: create user <username> identified by <pw> default tablespace test_user_ts quota unlimited on test_user_ts;

  From performance aspects you don't have to create multiple temp tablespaces.

In Memory (EDIT1)

  Starting with Oracle 12c you can use the In-Memory option (keep in mind it's a extra cost option). It is a completely different memory structure than the normal data buffer cache. We did some benchmarks with it and it's very very fast.

  In Oracle 11g there is no In-Memory option but this does not mean you can't optimize the database performance. I would recommend the following action plan to figure out the optimal memory settings:

  use AWR report or statspack report (depends on whether you have diagnostic pack licensed)

  check the buffer hit ratio (should be above 90%)
  or check if the  /  = x. "x" should be below 10%
  check the buffer cache advisory listings (this is not really reliable but it gives you an idea)
  check the size of the buffer cache compared to shared pool, the shared pool should not be grater than the buffer cache

  Adjust the memory parameter

  use huge pages (use_large_pages=TRUE) (the os needs to be configured for it, does not apply on win)
  if the buffer cache hit ratio is smaller than 90% increase sga size (sga_target and sga_max_size)
  if the shared pool is overextended then set a minimum size for the buffer cache to at least 60% of the sga size (db_cache_size = sga_target/100*60)
  maybe(!!!) you can use KEEP and RECYCLE pool to optimize the data buffer cache usage.


How to store user activity that supports undo?

Create a TRIGGER on the table in which deletes are recorded and update related tables as required.

Relevant postgresql documentation

Info about triggers: http://www.postgresql.org/docs/9.5/static/triggers.html
How to create a trigger: http://www.postgresql.org/docs/9.5/static/sql-createtrigger.html


how to get table wise number of deadlocks in sql server?

This is pretty easy to do using the xml_deadlock_report in the system_health Extended Event session.

IF OBJECT_ID('tempdb..[#DeadlockData]') IS NOT NULL
    DROP TABLE #DeadlockData;

WITH xml
AS ( SELECT CAST(event_data AS XML) AS XMLDATA
     FROM   sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL) )
SELECT xml.XMLDATA
INTO   #DeadlockData
FROM   xml
WHERE  xml.XMLDATA.value('(/event/@name)[1]', 'varchar(255)') = 'xml_deadlock_report';

WITH death_indeed
AS ( SELECT ca.x.value('@objectname', 'VARCHAR(256)') AS object_name
     FROM   #DeadlockData AS dd
     CROSS APPLY dd.XMLDATA.nodes('//resource-list/*') AS ca(x) )
SELECT   death_indeed.object_name, COUNT_BIG(*) AS deaths
FROM     death_indeed
GROUP BY death_indeed.object_name

After creating some different deadlocks, I get this back:

object_name                deaths
StackOverflow.dbo.Users    9
StackOverflow.dbo.Posts    9


Use the DATE_FORMAT function
SELECT DATE_FORMAT(datetimeout,'%H:00:00') ExitHour,count(*) VehicleExitCount
FROM to_leaveveh
WHERE datetimeout >= ('2014-09-01' + INTERVAL 0 SECOND)
AND   datetimeout <= ('2014-09-01' + INTERVAL 1 MONTH - INTERVAL 1 SECOND)
GROUP BY DATE_FORMAT(datetimeout,'%H:00:00');

or
SELECT DATE_FORMAT(datetimeout,'%H:00:00') ExitHour,count(*) VehicleExitCount
FROM to_leaveveh
WHERE datetimeout >= ('2014-09-01' + INTERVAL 0 SECOND)
AND   datetimeout <  ('2014-09-01' + INTERVAL 1 MONTH + INTERVAL 0 SECOND)
GROUP BY DATE_FORMAT(datetimeout,'%H:00:00');

Give it a Try !!!
UPDATE 2014-09-22 08:26 EDT
The queries I gave break down each by our hour of the month. You get no more than 24 rows.
If you want group by date and hour, then try this
SELECT DATE_FORMAT(datetimeout,'%Y-%m-%d %H:00:00') ExitHour,count(*) VehicleExitCount
FROM to_leaveveh
WHERE datetimeout >= ('2014-09-01' + INTERVAL 0 SECOND)
AND   datetimeout <= ('2014-09-01' + INTERVAL 1 MONTH - INTERVAL 1 SECOND)
GROUP BY DATE_FORMAT(datetimeout,'%Y-%m-%d %H:00:00');

or
SELECT DATE_FORMAT(datetimeout,'%Y-%m-%d %H:00:00') ExitHour,count(*) VehicleExitCount
FROM to_leaveveh
WHERE datetimeout >= ('2014-09-01' + INTERVAL 0 SECOND)
AND   datetimeout <  ('2014-09-01' + INTERVAL 1 MONTH + INTERVAL 0 SECOND)
GROUP BY DATE_FORMAT(datetimeout,'%Y-%m-%d %H:00:00');

Give it a Try !!!
UPDATE 2014-09-22 15:07 EDT
This will group by date and hour with a summary by date and an overall summary
SELECT
    IF(
        ISNULL(dt)+ISNULL(hr)=2,'Monthly Total',
        IF(ISNULL(hr)=1,CONCAT('Total for ',dt),HOUR(hr))
    ) Statistic,VehicleExitCount
FROM
(
    SELECT
        DATE(datetimeout) dt,
        DATE_FORMAT(datetimeout,'%Y-%m-%d') hr,
        COUNT(*) VehicleExitCount
    FROM to_leaveveh
    GROUP BY dt,hr
    WITH ROLLUP
) A;

Give it a Try !!!


Getting error "Time-out occurred while waiting for buffer latch type 2 for page (1:3564879), database ID 7." But a database with Id 7 DOES NOT exist

run dbcc checkdb

CHECKDB creates a database snapshot behind the scenes. This is your DBID 7.

As for the timeout, perhaps KB919155 Error message when you run the DBCC CHECKDB statement on a database that contains one or more very large tables in SQL Server: "Timeout occurred while waiting for latch". "This behavior is by design.". Is page 3564879 an IAM chain page?


How to use to_regclass with uppercase table names?

Once you start using those dreaded double quotes, you have to use them always:

SELECT to_regclass('"FooBar"');

(another good example why you should avoid those dreaded quoted identifiers)


According to the SQL Standard, numeric values may only be compared with numeric values e.g. an INTEGER value can be compared with a NUMERIC value by coercing both to NUMERIC values.

It sounds like MySQL is not compliant with Standards in this regard. To be fair, most SQL product exhibit similar implicit type coercions and it is encumbent on users to avoid them (in contrast to a strongly type 3GL whose users might expect a compile error!)


When possible you should update actual execution plans to Paste The Plan. Without the XML we have to make guesses about the operators shown in the images. For the rest of this answer I'm going to assume that the clustered key of all of the tables is id.

As far as I can tell the problem isn't with the nested loop join but with the table access method on the [directcrm].[CustomerActions] table. It looks like SQL Server does a clustered index seek in order starting with [t7].[Id] > @p15. However, it's possible that the storage engine will need to seek many rows until it finds enough that match the filter requirements against the [DateTimeUtc] column. If you're on SQL Server 2016 you can check this by looking at "Number of Rows Read" in the actual query plan for the clustered index seek. It's theoretically possible for the clustered index seek on [directcrm].[ActionTemplates] AS [t1] to also be a problem. However, it looks like the filtering against that table only removes 30% of the rows so I doubt that's an issue.

Based on just how the query is written the biggest problem is that SQL Server is not aware of the values of the local variables. As a result the query optimizer makes cardinality estimates based on hardcoded results. For example, whenever you have an unknown expression in TOP the query optimizer uses a hardcoded guess of 100 for that value. I understand that you are constrained by your ORM but you're essentially fighting with the query optimizer.

I have a few ideas on how to improve performance listed below in rough order of preference. It's likely that you won't be able to implement some of them due to ORM restrictions but I thought I should include them all for completeness.

A good test is try is the RECOMPILE hint. That may be enough to get SQL Server to pick a better plan. I understand that you can't use that one but perhaps you could replace some of the variables with hardcoded values? A TOP statement with a variable is going to be really bad because the query plan will never change as you change the value of the variable. The optimizer will always set a row goal of 100 when creating the plan (based on testing I did tonight).

Since you got better performance when not using the clustered index on [directcrm].[CustomerActions] as the driving index you could try to make the clustered seek more expensive or unavailable. One way to make it more expensive is to introduce some doubt about the order of the rows. Right now you order by t0.id which means that plans that use that clustered index can avoid a sort. If you did something like the following that might be enough:

ORDER BY CASE WHEN @sort = 1 THEN [Discriminator] ELSE [Id] END

Just set @sort always equal to 0 so you always get the sort that you want when the query is executed.

Another trick is to add superfluous operators to parts of the WHERE clause to prevent index use. In some cases just adding 0 won't be optimized away and an index seek will be unavailable. You can strategically add things like that to the WHERE clause to encourage the index usage that you want. For example, if you don't want a clustered index seek on [directcrm].[CustomerActions] you can start with this and make it more complicated if that isn't sufficient:

[t7].[Id] + 0 > @p15

You may be able to create a covering index against [directcrm].[ActionTemplates] to encourage that table to be used as the driving table. Of course you could always drop the clustered index on [directcrm].[CustomerActions] as well but I imagine that will have side effects that you can't ignore.

The nuclear option is to use a plan guide. There are many ways to create plan guides. It's possible to force a query to use a certain plan without changing the query's text. For example, if you can get a good query plan in the cache (such as with your row goal trick) then you can use sp_create_plan_guide_from_handle to force a plan guide to the query. Keep in mind that your query text needs to exactly match what is stored in the plan guide. If you're on SQL Server 2016 you could also try freezing a query plan using the Query Store.


how do i recover an inconsistent backup after another full backup without restoring the new full backup?

No.

About RMAN Incremental Backups

  A level 1 incremental backup can be either of the following types:

  A differential incremental backup, which backs up all blocks changed after the most recent incremental backup at level 1 or 0.
  A cumulative incremental backup, which backs up all blocks changed after the most recent incremental backup at level 0.

Your recent incremental backups contain changes since your another full backup, not the previous full backup.

But you can still recover your database using archivelogs.

Edit: and here is a demo: https://pastebin.com/kn2R5FXa


limiting sql server backups inside .bak file

The easiest solution would be using a maintenance plan task to clean up old backup files. However, you could consider scripting your way out of this, using something like the following script:

--====================================================================
CREATE PROCEDURE [dbo].[usp_DeleteOldBackupFiles]
@path nvarchar(256),
@extention nvarchar(10),
@age_hrs int
AS
BEGIN
SET NOCOUNT ON;

DECLARE @DeleteDate nvarchar(50)
DECLARE @DeleteDateTime datetime

SET @DeleteDateTime = DateAdd(hh, -@age_hrs, GetDate())

SET @DeleteDate = (Select Replace(Convert(nvarchar, @DeleteDateTime, 111), '/', '-') + 'T' + Convert(nvarchar, @DeleteDateTime, 108))

EXECUTE master.dbo.xp_delete_file 0, @path, @extention, @DeleteDate, 1
END
--============================
-- xp_delete_file information
--============================
-- xp_delete_file actually checks the file header to see what type of file it is and will only delete certain types such
-- as database and log backups. I suppose they expanded this to certain types of log files as well but as you say this is
-- not documented by MS. Just be aware that it will not delete just any file type

-- First argument is:
-- 0 - specifies a backup file
-- 1 - specifies a report file
-- (I'm not sure what the difference between a "backup file" and a "report file" is, since you specify the extension of files
-- you're deleting with the third argument.)
--
-- Fifth argument is whether to delete recursively.
-- 0 - don't delete recursively (default)
-- 1 - delete files in sub directories
--====================================================================

Credit: http://www.sqlservercentral.com/Forums/FindPost1034057.aspx

There's also xp_delete_file:

master.dbo.xp_delete_file


SQL Server: How to get the responsiveness of a trigger, without blocking the transaction that fires the trigger?

You might be able to use the service broker, though that is probably overkill, as you state.

Alternately, if you don't want to install/manage an extra service for this one need, you could use xp_cmdshell or a CLR-based trigger to make an external call when needed, to a program that starts the desired process asynchronously (so, outside the transaction that called the trigger) and returns immediately. I would be more wary of this than simply putting some complex code in a trigger though – at least, with a trigger any good SQL Server DBA person would be able to support the code when you are not around rather than requiring some other specific programming experience.

When you say that polling simply isn't an option as an "up to 30 seconds" delay is unacceptable, is there a reason you can't poll more often? If the concern is the load the polling will impart on the server(s) then there are ways to keep the polling very light-weight. No matter what you are going to need the trigger (or something else) to decide if action is needed at the point rows are inserted - if you make this decision in a trigger and simply note it in a simple status table then you could poll every second-or-few to wait for that status marker being updated (a DB hit each second to perform a simple lookup like that once every few seconds (or even every second, or more often) is not going to impart significant load, unless you are using some cloud DB service that charges you by the query). Again, this is far from ideal, but it seems safer to me than the xp_cmdshell option.

Whatever you do, be careful to make sure failures in the process are flagged appropriately. As you are pulling the extra processing out of the main transaction, you can't rely on your existing exception handling to tell you that it failed for some reason – so, you might not know that you have a growing data consistency problem. The key advantage of the trigger here is that by staying in the same transaction you know your data is kept consistent, as one half won't be committed if the other half fails.


How to track down app name for query in query plan cache?

No, information about what sent the query to the server (user, application, IP, etc.) isn't stored in the plan cache or Query Store, or associated views.
You can catch that information while a query is running using sp_WhoIsActive, or logging it to a table.
If you have a good monitoring tool, it may have gathered that information. If you have a bad one, it probably didn't.


How to dynamically add/remove role members from SSAS 2016 Tabular model

Typically only the role definitions are part of the project.  The role memberships are different by environment (dev,test,prod), are usually not administered by the model developers, and so are maintained separately.  Then only row-level security is driven from a table in the model.


How to take undo datafile offline ,which is in recovery mode?

Shouldn't you be looking in v$datafile to see if a datafile is offline rather than v$tablespace?

Offline Datafile:

08:09:38 coredev>select status, name from v$datafile where name like '%UNDO4%';
STATUS  NAME
------- ----------------------------------------------------------------------
ONLINE  F:\ORACLE\ORADATA\CD\UNDO4.DBF

08:09:47 coredev>alter database datafile 'F:\ORACLE\ORADATA\CD\UNDO4.DBF' offline;
Database altered.

08:09:58 coredev>select status, name from v$datafile where name like '%UNDO4%';
STATUS  NAME
------- ----------------------------------------------------------------------
RECOVER F:\ORACLE\ORADATA\CD\UNDO4.DBF

08:10:02 coredev>select * from v$tablespace where name='UNDO';
       TS# NAME                           INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
        33 UNDO                           YES NO  YES

Online Datafile:

08:10:53 coredev>alter database datafile 'F:\ORACLE\ORADATA\CD\UNDO4.DBF' online;
alter database datafile 'F:\ORACLE\ORADATA\CD\UNDO4.DBF' online
*
ERROR at line 1:
ORA-01113: file 21 needs media recovery
ORA-01110: data file 21: 'F:\ORACLE\ORADATA\CD\UNDO4.DBF'

08:11:04 coredev>recover datafile 'F:\ORACLE\ORADATA\CD\UNDO4.DBF';
Media recovery complete.

08:11:13 coredev>alter database datafile 'F:\ORACLE\ORADATA\CD\UNDO4.DBF' online;
Database altered.

08:11:49 coredev>select status, name from v$datafile where name like '%UNDO4%';
STATUS  NAME
------- ----------------------------------------------------------------------
ONLINE  F:\ORACLE\ORADATA\CD\UNDO4.DBF


Why securityAdmin does not have enough permission?

The securityAdmin role should be treated as equivalent to the sysadmin
  role, according to Microsoft, but I failed to do below 2 things

  make myself as sysAdmin
  grant myself write right to a database. (currently in
  db_datadenywrite role )

All the above is wrong. Secutiry admin is not sysadmin, if it was so for what purpose 2 different server roles were created?

Security admin has these 3 permissions at server level:

CONNECT SQL
ALTER ANY LOGIN
VIEW ANY DATABASE

CONNECT SQL and VIEW ANY DATABASE permissions are granted to public, so there is only one additional permission, ALTER ANY LOGIN.

At the database level this role adds you the possibility to grant permissions to other users, but you don't have alter any user / alter any role, so you cannot add yourself or any other user to database role.

The phrase

  The ability to grant access to the Database Engine and to configure
  user permissions allows the security admin to assign most server
  permissions. The securityadmin role should be treated as equivalent to
  the sysadmin role.

in my opinion means the following: security admin can elevate his permissions to sysadmin in some way.

I can give you an idea of this "way".

If you are security admin you can create SQL Server login ( I'll call him super_login) and grant him CONTROL SERVER permission.

The next step is to log in as super_login and give all the permissions you want to your login. You can make yourself db_owner of the database where now you are db_datadenywriter, or drop yourself from db_datadenywriter or merely add your login to sysadmin fixed server role.


How can I implement a sequence for each foreign key value?

How about using a column in the table account called max_customer_id and updating that every time there's a new customer? That should be the simplest solution available that I can think of.

With this solution you have to take care of the concurrency. Take a look at the document PostgreSQL Concurrency issues:

  If two concurrent transactions try to
  insert rows having the same key value, then the second one will block until
  the first one finishes. If the first transaction commits, the second one must
  abort because of the uniqueness constraint; but if the first one aborts the
  second one can proceed.

  If you want to assign a serial number to each row in a table, you might try
  INSERT INTO mytable (id, ...)
  VALUES( (SELECT MAX(id) + 1 FROM mytable), ...);
  This will not work safely unless you take an explicit lock on the whole table,
  which will prevent concurrent insertions. (It’ll also be quite slow, because
  MAX scans the whole table in PostgreSQL.)

Alternatively you could place the value in a separate table. This table would contain the account_id and maximum account_specific_id in use for that account. That way you could handle the concurrency easier with UPDATEs. Same source:

  A variant is to use a single-row
  table to hold the next ID number to assign:
  SELECT next FROM mytable counter FOR UPDATE;
  UPDATE mytable counter SET next = $next + 1;
  INSERT INTO mytable (id, ...) VALUES($next, ...);
  This works (as long as you use FOR UPDATE), but you still have the
  problem that only one insertion transaction can proceed at a time. The
  implicit write lock on the counter row is the bottleneck.
  [The author goes on recommending sequences.]

The number 2 option with sequences is not however recommended in this case as new sequences are not meant to be routinely inserted in a production environment.


How to point Azure Hybrid Connection to Availability Group Listener ? Or redirect to secondary replica?

You should be able to connect to the AG listener  from Azure to On-premises or vice versa--though it's not as straight forward as you might think. Here's one of the best article's I've ready today on how to do it.

https://www.concurrency.com/blog/w/creating-a-listener-for-a-multi-subnet-hybrid-or-a

When the AG primary node is on-premises (or in Azure), you should be able to connect to the listener. So you're relying on Azure DNS to do it's job properly and resolve your SQL Azure node correctly. Haven't tried it myself--sounds awesome!


You are not limited to just $$ you can use anything enclosed in $, e.g. $_alex_delimiter_$

insert into foo
   (some_column)
values
  ($_alex_delimiter_$this is a long string that contains a single $ and two $$ and quotes '''''$_alex_delimiter_$);

Just pick something that won't be part of the text.


SQL Server 2008 R2 Setup Client Tools Connectivity

The client tools installs things like sqlcmd, odbc, db-lib, SMO (SQL Server Management Objects), AMO (Analysis Server Management Objects) etc; the backwards compatibility tools includes wrappers for COM objects such as DTS, STrace, osql etc... These tools can help you in various tasks depending on your need - and in some cases may be mandatory depending on what you are doing.

They can also be required when applications have been written that use them to connect to SQL Server - as such they are available as redistributables. You may not need all of them installed and so you should think about what you really need before installing them as extra components increase the surface area of attack upon a system.

For more information about these options please see the following link:

http://technet.microsoft.com/en-us/library/ms143786.aspx

I hope this helps you.


How to find out SQL Server options updated

View errorlog or default trace if is ON

sp_configure'default trace enabled'

DECLARE @trc_path VARCHAR(500)
SELECT @trc_path = CONVERT(VARCHAR(500),value) FROM fn_trace_getinfo(DEFAULT)
WHERE property=2

SELECT @trc_path
SELECT
    TEXTData,
    HostName,
    ApplicationName,
    DatabaseName,
    LoginName,
    SPID,
    StartTime,
    EventSequence
FROM fn_trace_gettable(@trc_path,1) fn
WHERE TEXTData LIKE '%configure%'


Pre- and Post- Snapshot Replication Scripts Not Executing and No Errors

Just a quick summary of some of the things I would try, when analysing the various options....

Have you tried passing the four part object identifier in the scripts?
Instead of using:
... Database.Schema.Table ...

Try using:
... Server.Database.Schema.Table ...

Or in your case where you are using the Linked Server it would be:
... LinkedServerName.Database.Schema.Table ...

Where you define the values as:
LinkedServerName : The name of the linked server as you defined in the Linked Server connection.
Database ........: The name of the database
Schema ..........: The name of the schema
Table ...........: The name of the table

It depends on where the script has to modify data. That's not quite clear in your description, but seeing as you did mention Linked Servers I thought I'd mention it.

The script is executed on the Subscriber as is explained in:

You can specify scripts to execute at the Subscriber before or after the snapshot is applied. Scripts can be used for a variety of reasons, such as creating logins and schemas (object owners) at each Subscriber.

And the additional information provided here is pretty important:

The script is run by launching the sqlcmd utility. Before deploying a script, run it with sqlcmd to ensure it executes as expected. The contents of scripts that are executed before and after the snapshot is applied must be repeatable. For example, if you create a table in the script, you should first check for its existence and take appropriate action if it exists. The script must be repeatable because if you need to reinitialize a subscription for which the script has already been applied, the script will be applied again when the new snapshot is applied during reinitialization.

Reference: Modify Snapshot Initialization Options for SQL Replication (Microsoft | SQL Docs)
If the script doesn't work together with the account that is used for the subscription agent and in conjunction with sqlcmd, then it isn't going to work in the subscription.

Hint
Use ; at the end of every line in the scripts.
Add GO in between lines.

Then you might want to have a look at the article Troubleshooter: Find errors with SQL Server transactional replication. I'm not saying you havent't done that yet, but you didn't say you had. Who knows, maybe you can find the issue in the replication agent's job history...


How to get the last 10 query run on MongoDB?

On the database side, you can enable Auditing, but at this time, only available on MongoDB Enterprise.


We have millions of tables deployed running ARIA in production. It is  definitely a better choice over MyISAM as far as crashing and recovering from a crashed table. If you have high concurrent write neither are a option as they both only support table level locks. For tables with more write intensive operations stick with InnoDB, and with heavy write tables checkout TokuDB.


Partitioning / indexing an extremely large table

My approach is to disable all the nonclustered indexes [...] then build a clustered index for the table using this partition scheme.

Creating a clustered index on a heap automatically rebuilds all nonclustered indexes (even disabled ones). The nonclustered indexes are rebuilt but not partitioned. Assuming the desired end state is a partitioned clustered table with aligned indexes, rebuilding the nonclustered indexes to be non-aligned is entirely wasted effort.

  What worries me slightly is that tempdb is now pushing nearly 1 TB and steadily climbing, despite the current table being around half that size. The MS docs I've read suggest the tempdb space usage should be about the size of the final table/clustered index.

The question of sort space is very complex. To understand all the details (including the effect of parallelism) you would need to carefully read an entire series of posts by the SQL Server Query Processing Team. Converting a heap to a partitioned clustered table with parallelism enabled is probably pretty close to worst case.

At it's most basic (neglecting most of the important information in the QP Team's posts), you are asking SQL Server to run a query like:

SELECT *
FROM DailyTable
ORDER BY
    $partition.monthly_on_primary(LoadDate),
    LoadDate,
    SeqNumber;

This query will not execute quickly, regardless of where you choose to write the sort runs that do not fit in memory to. Add to that the work of actually building a complete new copy of the entire data set in separate rowsets, and the work involved in rebuilding the nonclustered indexes pointlessly...

Advice

There are many considerations in getting this change to work efficiently. The important ones are to avoid sorting at all where possible, and to use parallel minimally-logged bulk load wherever possible.

The details of that depend on details not contained in the question, and a full solution is beyond an answer here. Nevertheless, the outline of an approach that has worked well for me personally in the past is:

Extract the existing data using bcp to one file per final partition
Drop the existing table and create the new one
Load the new table using parallel minimally-logged bulk load

The per-partition data extract needs be ordered on (LoadDate, SeqNumber). Ideally, you would avoid a sorting operation. If you have an existing nonclustered index on (LoadDate, SeqNumber) you can extract data in the right order without sorting if you construct the query correctly.

Once per-partition data has been extracted to separate files (this can be done in parallel if your hardware is up to it), the source table can then be dropped, freeing space. A new partitioned heap or clustered table is then created, and bulk loaded with the pre-sorted data, possibly also in parallel.

Done right, the entire process requires no more than 1x data size and achieves the fastest possible data transfer rates in both directions, with the least amount of log use.


Create a new schema in a particular database

Lets say the database we will be using called test, then we have to connect to the database first then create schema, example below

\connect test;
CREATE SCHEMA sch1;


PostgreSQL extending a variable-width bit string type?

Using overlay

From the docs

  The following SQL-standard functions work on bit strings as well as character strings: length, bit_length, octet_length, position, substring, overlay.

PostgreSQL supports overlay on Bit Strings in two forms

overlay(bit,bit,int)
overlay(bit,bit,int,int)

You can use it like this,

CREATE FUNCTION type_test(_x bit)
RETURNS bit AS $$
  SELECT overlay(B'00' PLACING _x FROM 2 FOR 0);
$$ LANGUAGE sql;
SELECT type_test(X'ff');

 type_test
------------
 0111111110

Using "bit"

You can can also use the internal and undocumented "bit"

CREATE FUNCTION type_test(_x bit)
RETURNS bit AS $$
  SELECT ('0' || _x::text || '0')::"bit";
$$ LANGUAGE sql;

SELECT type_test(X'55');
 type_test
------------
 0111111110


On checking the validity of a large set of identifiers

Enter the names into table x.

The list of all "New names":

SELECT x.name
    FROM x
    LEFT JOIN gene  ON x.name = gene.name
        WHERE gene.name IS NULL;

gene should have an index on name.

I don't think it can be done with just an IN.


Duplicate table names after restoring a backup

You problem is incredibly straightforward.

In Linux Systems, the file extensions for a MyISAM table tablename must be

tablename.frm
tablename.MYD
tablename.MYI

The file extensions are case-sensitive

Windows does not check case-sensitivity for .myd and .myi file extensions, so a MyISAM table in a Windows MySQL instance will allow for this. Such is not the case with Linux.

In your case, let's look at the UsersInGroups table. You evidently have two tables with two different filenames and/or extensions:

UsersInGroups.frm
UsersInGroups.MYD
UsersInGroups.MYI
usersingroups.frm
usersingroups.myd
usersingroups.myi

You will have to rename the files in the OS so that there are only .MYD and .MYI extensions. You can then query each table using the case-sensitive name UsersInGroups and usersingroups.

If you simply want to replace one with the other. delete the old files, keep the new files, and make sure you only have .MYD and .MYI extensions. BTW .frm must always be lower case.

If you are using dbForge to make a backup of MyISAM tables from a Windows machine and you restore that backup on a Linux machine, you must always remember to change the file extensions after restoring from backup.

I talked about this 5 years ago (How can I move a database from one server to another?)


Before you try to change the configuration file, the first thing to check is index use. This part:
JOINS
Current join_buffer_size = 4.00 M
You have had 1117666 queries where a join could not use an index properly
You have had 207168 joins without keys that check for key usage after each row

suggests that many queries cannot use indexes, probably because there are no indexes to use.
Ask for the slow query log, check which queries are slow and check/test if indexes can be added to improve performance.
A general advice is first, to have indexes on all columns that are used in Joins and second, indexes on columns that are used in WHERE, GROUP BY and ORDER BY clauses. But for this, you may need compound indexes and of course you can't create an index for every column combination. That would take too much space and would make Insert, Delete and Update statements much slower. So, you'll have to check what are the most common queries and optimize those first.
There are some tools/services that can help you identify better and faster where the bottlenecks are. One such tool is Percona Toolkit (also known as mk-query-digest, from Maatkit).
A slow query that needs 10 seconds to complete is not good. But it doesn't really affect performance if you are executing it once per hour. A query that needs 60ms can be really bad if it is a simple UPDATE and is executed several times per second. These tools can help you identify those because you can analyze the logs and find total running time, number of times a query has been run and various other figures.

For your example query:
SELECT menu_links.link_path AS link_path
     , menu_links.mlid AS mlid
     , menu_links.router_path AS router_path
     , menu_links.updated AS updated
FROM
    dr_menu_links menu_links
WHERE (  (updated = '1')
      OR (  (router_path NOT IN  ( 'rss.xml'  , 'node'         , 'checkout'
                                 , 'import'   , 'image_captcha', 'my-favourites'
                                 , 'my-orders', 'print'        , 'search'
                                 ) )
        AND (external = '0')
        AND (customized = '1')
         )
      )

possible good indexes are an index on (updated) and one on (external, customized, router_path)


After 5 years of working, SQL Server 2k5 Table now locking without any changes made to DB

Use SQL Profiler to trace your deadlocks and find a few example queries that are the root cause.

Then copy that SQL to SSMS and view the execution plan for it.

Look to see what kind of locks are being held when the query runs.

It's almost certain that you are experiencing table locks - actually, as you state, someone removed row-level locks and page-level locks.

You have options:

Modify your SELECT statement to use READ UNCOMMITTED or (nolock). This means this query can read dirty data. Only you will know whether this will be acceptable with your data set - very often, it is fine to have dirty reads and doesn't impact the functionality of the system.
Use row-level locks. You can force these using a query hint.
Use row-versioning - this option means the updates operate on a different version of the rows to the ones that the SELECT statement is reading from avoiding deadlocks altogether.

You can find out more details about row versioning and other deadlock reducing techniques here.


Cannot release unused space in SQL Server mdf file

Relase unused space frees only the space after the last allocated extend in the data file. Since 95% of the space in the data file is not in use you need to perform file reorganization. You can do it from SSMS by choosing Reorganize pages before releasing unused space and setting the suggested Shrink file size to 2490 MB or higher value.
As an alternative you can execute command (I recommend to do it this way):
DBCC SHIRINKFILE ( Res )


Acceptable to use Antelope and Barracuda simultaneously?

I wrote a post 3 years ago that discusses this very subject : innodb_file_format Barracuda

In terms of storage, using Antelope and Barracuda is possible. However, in my post, I discuss a residual effect on the InnoDB Buffer Pool. You need a larger Buffer Pool because Barracuda compressed pages in the Buffer Pool have to be decompressed to the read in the Buffer Pool.

You should dig deeper through Percona's Performance Blog site for any additional insights.


Improve SQL Performance with LIKE '%ABC%' QUERY

Without full-text search, no, there's no magic to making string parsing faster within SQL Server, other than pre-calculating results or throwing more resources at the problem.

If you have a narrow set of search patterns that are repeated over and over again, it's possible you could maintain a skinnier materialized portion of the table that meet those criteria (e.g. a table of just the PK columns representing the rows in the main table that match '%ABC%' - you could maintain these through triggers). This will reduce the amount of reads required, but may not have a serious impact on duration.

If people are entering arbitrary search strings in a non-repeatable and unpredictable way, that may not help anyway.

10 seconds seems like a long time for a table with 160K rows. If you are on V12 (and can run this query in relative isolation), you should be able to determine the waits that changed during that query, using sys.dm_db_wait_stats - it may be that you can't keep 300MB of data in memory and the wait time is all disk churn. In this case it may just be that you are sharing an overwhelmed server, so one consideration would be to move up to a better tier that provides for better performance.

Another option you can consider is application-side caching (think memcached, redis, etc.), where you have a copy of the data in your application's memory, and perform the searching there instead of within SQL Server.


PostgreSQL: windowing to replace multiple sub-queries ... hopefully

If I understand correctly:

SELECT DISTINCT
     last_value(fd.fund_val) OVER (ORDER BY fe.fiscal_prd_end_date)
FROM
     fund_data AS fd
     JOIN fund_entries AS fe ON (fd.fund_entries_id = fe.id)
WHERE
     fd.bbg_pulls_id = 20
     AND fe.company_id = 858

UPDATED:

Or just:

SELECT
     fd.fund_val
FROM
     fund_data AS fd
     JOIN fund_entries AS fe ON (fd.fund_entries_id = fe.id)
WHERE
     fd.bbg_pulls_id = 20
     AND fe.company_id = 858
ORDER BY fe.fiscal_prd_end_date DESC
LIMIT 1


Restoring SQL_ASCII dumps to a UTF8-encoded database

I suspect your database content may be in iso8859 or cp1252.  If it were ascii, you would not run into problems importing it.  You may be able to determine the coding by opening your dump with python.  The following python3 tries encodings until it succeeds.  It can be used to determine the file encoding.

for enc in ('cp1252', 'utf8'):
    print('Encoding', enc)
    try:
        file = open(fileName, 'r', encoding=enc)
        return file.read()
    except Exception as e:
        print(e)
        return None

Python also can also be used to access the database and audit the data.  It could also be used to transcode the data to UTF-8 while copying it if necessary.


SQL rolling back all iterations of a while loop when each iteration has its own explicit transaction

The key here is this:

What's worse, if I cancel this query and do a ROLLBACK to release the
table, it rolls back everything. Not just the last iteration, as one
would expect, but the whole thing.

So apparently your work was already transaction protected. Breaking it up to several DELETE did noting for you, since apparently they are all still one transaction. You need to find out why. You don't say from where you are executing your code.
If you use SSMS, then perhaps somebody turned on SET IMPLICIT_TRANSACTIONS ON. This turns off autocommit. Can be done in a config option in SSMS or using above command. But then you would have to commit your work when you are done, or SSMS would catch that you have an open transaction and ask if you want to commit or rollback.
Perhaps you use some other environment that will protect your work and submit rollback if SQL Server throws an exception. this is where I would start. that would explain everything - but there's some more digging from your side to do.


Since sp_releaseschemalock is an internal stored procedure, under normal circumstances, seeing many of it is not an issue.


Best practices for history/temporal tables?

For practical data access reasons, you should use the structure from your first option, but instead keep all versions of your tracked column values including the current version in your history table.

The reason for this is that in general, when you want to look at history, you want to include the present and all past versions.  When you don't want to look at history, you want it out of the way.  In many cases this means going so far as to segregate the history into a separate schema or database altogether.  Even if you keep your history in the same schema as your current data, any queries that look at historical data (including the current values) will be much more complex since they have to essentially union two sources.


Oracle create user with privileges to grant references

You don't need to grant SYSDBA privileges, and shouldn't unless really necessary. You should follow the principle of least privilege. From Oracle's security guidelines:

  Do not provide database users or roles more privileges than are
  necessary. (If possible, grant privileges to roles, not users.) In
  other words, the principle of least privilege is that users be given
  only those privileges that are actually required to efficiently
  perform their jobs.

You just need to grant with admin option:

grant references on <schema>.<table> to <user> with admin option;

<user> can then grant references on to others, but can't pass on the admin option.


how to count distinct value per day with start time and end time

For MySQL 8+:

WITH RECURSIVE dates AS (
SELECT MIN(DATE(starttime)) `date`
  FROM table1
UNION ALL
SELECT `date` + INTERVAL 1 DAY
  FROM dates
  WHERE `date` < ( SELECT MAX(DATE(endtime))
                   FROM table1 )
),
users AS (
SELECT DISTINCT username
  FROM table1
)
SELECT /* DISTINCT */ users.username, dates.`date`, 1 `count(*)`
FROM users
JOIN dates
JOIN table1 ON table1.username = users.username
           AND dates.`date` BETWEEN DATE(table1.starttime)
                                AND DATE(table1.endtime)


Insert on conflict return false otherwise true in single query in stored function

You can handle exceptions.

create table test (id int primary key, data text);

insert into test values (1,'a'), (2,'b'), (3, 'c');

create or replace function test_error(_id int, _data text)
returns boolean as $res$
declare res boolean;
begin
    res = true;
    begin
        -- uncomment to check other exception
        --select 1 / 0;
        insert into test values (_id, _data);
    exception
    when unique_violation then
        res = false;
    when others then
        raise exception 'Other exception';
    end;
    return res;
end;
$res$ language plpgsql;

select test_error(4, 'd');

| test_error |
| :--------- |
| t          |

If you try to insert a duplicate key:

select test_error(1, 'n');

| test_error |
| :--------- |
| f          |

db<>fiddle here


A trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. The trigger is mostly used for maintaining the integrity of the information on the database.
There are three different strategies to
ensure execution of data integrity code when a window on data application (WoD) attempts to execute a transaction:

Declarative : Table constraint implementation, using primary & unique keys, check constraints, foreign
keys.
Triggered procedural: Using triggers.
Embedded procedural: Implementing data integrity logic in applications business layer. This is probably by far the most used procedural strategy for implementing DI code in WoD applications.

You can state only two types of table constraints declaratively: uniquely identifying
attributes (keys) and subset requirements referencing back to the same table, in which case a subset requirement is a table constraint (foreign key to the same table).
Implementing all other types of table constraints (like a multi-tuple constraints or transition constraints) requires you to develop procedural DI code.
So the answer is No, not without a heavy cost to implement the logic in application level.
As I know, triggers will affect performance in insert, update and delete operations. but doesn't affect read performance.
To tune your RDBMS first see what are the DB connection threads doing, and what are the most costly waits.


Validating the GST Identification Number (GSTIN)

To supplement Aaron's completely valid answer, you might decide to do a more in-depth validation of the GSTIN number by validating the correctness of the check-digit, which appears as the last digit in the number.

The code below creates a number of functions that will be used by a CHECK CONSTRAINT to validate new rows, and any modifications to the GSTIN number.

The first two functions provide arbitrary mapping of the ASCII characters contained in the GSTIN number into base-36; that is 0 maps to 0, 9 maps to 9, A maps to 10 and Z maps to 35:

IF OBJECT_ID(N'dbo.map_char', N'FN') IS NOT NULL
DROP FUNCTION dbo.map_char;
GO
CREATE FUNCTION dbo.map_char(@c char(1))
RETURNS int
AS
BEGIN
    DECLARE @val int;
    SET @c = UPPER(@c);
    IF ASCII(@c) >= 48 AND ASCII(@c) <= 57
        SET @val = ASCII(@c) - 48;
    IF ASCII(@c) >= 65 AND ASCII(@c) <= 90
        SET @val = (ASCII(@c) - 65) + 10;
    RETURN @val;
END
GO
IF OBJECT_ID(N'dbo.unmap_char', N'FN') IS NOT NULL
DROP FUNCTION dbo.unmap_char;
GO
CREATE FUNCTION dbo.unmap_char(@v int)
RETURNS char(1)
AS
BEGIN
    DECLARE @c char(1);
    IF @v >= 0 AND @v <=9
        SET @c = CHAR(@v + 48);
    IF @v >= 10 AND @v <= 90
        SET @c = CHAR((@v + 65) - 10);
    RETURN @c;
END
GO

I've been unable to determine for certain if the government of India uses this same encoding, however it seems to work for the values provided.

This code uses the above dbo.map_char function to validate the check-digit in the given GSTIN number.

CREATE FUNCTION dbo.fn_validate_gstin
(
    @inp char(15)
)
RETURNS tinyint
AS
BEGIN
    DECLARE @return tinyint;
    DECLARE @i int = LEN(@inp);
    DECLARE @factor int = 1;
    DECLARE @char char(1);
    DECLARE @codepoint int;
    DECLARE @addend int;
    DECLARE @sum int = 0;
    IF @i <> 15 /* GSTIN MUST be 15 characters to be valid */
    BEGIN
        SET @return = 0;
    END
    ELSE
    BEGIN
        WHILE @i > 0
        BEGIN
            SET @codepoint = dbo.map_char(SUBSTRING(@inp, @i, 1));
            SET @addend = @factor * @codepoint;
            SET @addend = (@addend / 36) + (@addend % 36);
            SET @sum += @addend;
            IF @factor = 2 SET @factor = 1 ELSE SET @factor = 2;
            SET @i -= 1;
        END
    END
    DECLARE @remainder int = @sum % 36;
    IF @remainder = 0 SET @return = 1 ELSE SET @return = 0;
    RETURN @return;
END
GO

Note, there is absolutely no error-checking in this code; I leave that as an exercise for the reader.  The function returns 1 if the GSTIN contains a valid check-digit as the last digit.  If the check-digit is invalid, it returns 0.

Here I create a table that implements the dbo.fn_validate_gstin function:

CREATE TABLE dbo.t
(
    i char(15) NOT NULL
        CONSTRAINT CK_t_valid_gstin
        CHECK (dbo.fn_validate_gstin(i) = 1)
);

Here we insert several "test" GSTIN numbers:

INSERT INTO dbo.t (i) VALUES ('123456789012345');
INSERT INTO dbo.t (i) VALUES ('27AAFFM5744C1ZE');
INSERT INTO dbo.t (i) VALUES ('27AAACE7932L1ZC');

The first insert fails.  The second and third inserts succeed.  Attempting to insert an invalid GSTIN numbers result in this error:

  Msg 547, Level 16, State 0, Line 80
  The INSERT statement conflicted with the CHECK constraint "CK_t_valid_gstin".
  The conflict occurred in database "tempdb", table "dbo.t", column 'i'.

Note, the presence of a scalar function as part of a constraint prevents queries using parallelism.  This may or may not be problematic for your system.  If you need parallelism, you might consider using an INSTEAD OF trigger on the table to check the GSTIN numbers at insert or update.  For example:

IF OBJECT_ID(N'dbo.t_with_trigger', N'U') IS NOT NULL
DROP TABLE dbo.t_with_trigger;
GO
CREATE TABLE dbo.t_with_trigger
(
    i char(15) NOT NULL
);

GO
CREATE TRIGGER t_validate
ON dbo.t_with_trigger
INSTEAD OF INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION
    DECLARE @bOk bit = 1;
    IF EXISTS (
        SELECT TOP(1) 1
        FROM inserted i
        WHERE dbo.fn_validate_gstin(i.i) = 0
        UNION ALL
        SELECT TOP(1) 1
        FROM deleted d
        WHERE dbo.fn_validate_gstin(d.i) = 0
        )
    BEGIN
        SET @bOk = 0;
    END
    IF @bOk = 1
    BEGIN
        DELETE
        FROM dbo.t_with_trigger
        FROM dbo.t_with_trigger t
            INNER JOIN deleted d ON t.i = d.i;
        INSERT INTO dbo.t_with_trigger(i)
        SELECT i.i
        FROM inserted i;
        COMMIT TRANSACTION;
    END
    ELSE
    BEGIN
        ROLLBACK TRANSACTION;
        DECLARE @msg varchar(1000);
        SET @msg = 'Attempted to insert/update using an invalid GSTIN number.';
        RAISERROR (@msg, 14, 1);
    END
END
GO

Inserting an invalid GSTIN number:

INSERT INTO dbo.t_with_trigger (i)
VALUES ('123456789012345')
    , ('27AAFFM5744C1ZE')
    , ('27AAACE7932L1ZC');

results in this error:

  Msg 50000, Level 14, State 1, Procedure t_validate, Line 37 [Batch Start Line 129]
  Attempted to insert/update using an invalid GSTIN number.
  Msg 3609, Level 16, State 1, Line 130
  The transaction ended in the trigger. The batch has been aborted.

I based my implementation around the LUHN algorithm.


As pointed out, this is better solved by Gaps and Islands.

WITH T
 AS (SELECT *,
            DENSE_RANK() OVER (PARTITION BY Target ORDER BY PollTime) -
            DENSE_RANK() OVER (PARTITION BY Target, Value ORDER BY PollTime) AS Grp
     FROM   [schema].[Table]
     )
SELECT Target,
       MIN(PollTime) AS Start,
       MAX(PollTime) AS Finish,
       DATEDIFF(mi, MIN(PollTime), MAX(PollTime)) AS Duration
FROM   T
GROUP  BY Target,
          Grp
HAVING MAX(Value) is null
ORDER  BY Start

Original source for code:  Getting each status change in a table


Why don't you just omit the WHERE... clause altogether? And then just JOIN all relevant tables as in:

SELECT * FROM Product
    LEFT JOIN Packaging
        ON Product.PackagingID = Packaging.ID
    LEFT JOIN LabelsForPackaging
        ON LabelsForPackaging.PackagingID = Packaging.ID
    INNER JOIN Label
        ON LabelsForPackaging.LabelID = Label.ID


MySQL: "FROM_UNIXTIME(0)" gives "1970-01-01 01:00:00", which is 1 hour off

At time 0, "Time zone: Europe/London" was DST (or not DST?); you are currently not in DST (or you are?).  That is probably the 1-hour discrepancy.

Europe/London is not the same as UTC.


MySQL Installation: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock'

I know there are a lot of descriptions about this error on the internet, but there seems to be many causes for that, and all of them seems linked to installation.
In my case, I found out my "sudo" access had some issues. After I've corrected them, I've uninstalled everything and installed again, and everything seems to work fine now.


Last occurance of time relative to now()

The logic is similar to what @Daniel already posted, but this is a bit faster (twice as fast in my tests on pg 9.3):

SELECT CASE WHEN now()::time > time '16:05'
            THEN now()::date + time '16:05'
            ELSE (now()::date - 1) + time '16:05' END AS ts;

now() is the Postgres implementation of the SQL standard CURRENT_TIMESTAMP (which you can use as well). I use now(), because it is shortest and fastest. CURRENT_TIME and CURRENT_DATE are implemented with now() as well.

Note that you get a timestamp [without time zone] that is interpreted according to your current time zone setting.


some tips on setting up sql server database server

The standard SQL port is 1433. Though, the provider could be using another port. So, you'll need to ask them what port they're exposing.

Once you know the port, you can connect to the database instance using the following connection string (substituting placeholders with appropriate values):

"Data Source=111.222.333.444,1433;
Network Library=DBMSSOCN;
Initial Catalog=[databasename];
User ID=[username];Password=[password];"

Substitute 111.222.333.444,1433 with the appropriate ip address and port.

  The other question I have is that I have a number of databases on
  other servers, I will like to "copy" these databases to the new
  server. I need to copy both structure and data. What is the best way
  to do such?

You can either backup and restore the database or you can generate scripts to create the schema and insert data. To generate the schema and data scripts go to:

object explorer => [right-click] database => select tasks => "generate scripts"

Then, under the "advanced options" make sure schema and data is selected for "Types of data to script":


Why is the SELECT timeline different though the WHERE clause & EXPLAIN plan are same?

Without more information, we can only guess why these two almost identical queries run with so much different efficiency (about 500x).

But we can make a few guesses based on the facts:

There is no index on SID and UPDATE_STAMP so both queries do a table scan.
The difference must lie in the mediumtext type.
This difference in speed suggests that the first query can do the table scan relatively quickly (the table is small) and find that no rows satisfy the condition. But the second query is very slow compared to the first one. My guess is that the mediumtext column has some long texts in it. As text and long varchar column values are not stored in the table/index pages but in different locations, this would explain the difference. In the 2nd query, the engine fetches these values as well while running the full table scan (so many more pages have to be read from disk) and thus the slow performance.

A suggestion to improve efficiency. Find first the primary key of the wanted row, using a subquery, then fetch the whole row:

select file_data
from bb_table
where pk = ( select pk            -- where pk is the PRIMARY KEY
             from bb_table
             where update_stamp > '0'
             limit 1
           ) ;


You can begin from reading on MSDN Query Tuning.

Initially to Begin with , find the Queries which needs to be tuned on basis of how bad they perform in terms of resource consumption or time they are taking to execute and then analyse their execution plans accordingly.

There are many free tools available out there apart from SQL server inbuilt tools like DTA etc, which makes the analysis go easy.

The best that I feel comfortable while doing query tuning is using SQL Sentry Plan Explorer.

You can use SQL Sentry’s Plan Explorer to get faster performance and tuning information from SQL Server query plans.

Also please read this excellent article Finding the causes of poor performance in SQL Server

Initially above should get you started. Apart from that you can always refer to SQL server Books Online


How can I monitor running queries in Oracle?

Start the old application, and log in. Find your session in the database (let's say: sid=12, serial#=3456), and enable SQL tracing for that session, for example:

begin
  dbms_monitor.session_trace_enable
  (
    session_id => 12,
    serial_num => 3456,
    binds => true
  )
end;
/

There are several ways of enabling SQL tracing, it is just one of them. It may be useful to enable capturing bind values (as above), so you can match your report input parameter values to the query bind variables.

You can find the name of the trace file as:

select
  p.tracefile
from
  v$process p
  join v$session s on (p.addr = s.paddr)
where
  s.sid = 12
;

You can start running your report from the application, and follow the contents of the trace file (even tail -f, but it will not be human-friendly). After you have finished, disable tracing:

begin
  dbms_monitor.session_trace_disable
  (
    session_id => 12,
    serial_num => 3456
  )
end;
/

You can read the raw trace file (if you are patient enough and interested in more details), but if you want only the query texts, you can use tkprof on the trace file (e.g: orcl_ora_6789.trc):

tkprof orcl_ora_6789.trc orcl_ora_6789.tk sys=no aggregate=no

With aggregate=no added, tkprof will not aggregate the SQL statements and it will generate an output where the order in which the statements were executed is preserved. Without that, you can aggregate multiple executions of the same SQL and sort them, a typical use case would be to sort by elapsed time, so you the output starts with the longest running queries:

tkprof orcl_ora_6789.trc orcl_ora_6789.tk sys=no sort=prsela,exeela,fchela