DISTINCT not using an index I have table (2139868 rows):

COLUMN1 NUMBER(20,0)
COLUMN2 XMLTYPE
COLUMN3 XMLTYPE
COLUMN4 VARCHAR2(50 BYTE)
COLUMN5 VARCHAR2(50 BYTE)
COLUMN6 VARCHAR2(50 BYTE)
COLUMN7 VARCHAR2(50 BYTE)
COLUMN8 VARCHAR2(50 BYTE)
COLUMN9 VARCHAR2(500 BYTE)
COLUMN10 VARCHAR2(100 BYTE)
COLUMN11 VARCHAR2(50 BYTE)
COLUMN12 VARCHAR2(20 BYTE)
COLUMN13 VARCHAR2(20 BYTE)
COLUMN14 TIMESTAMP(6)
COLUMN15 VARCHAR2(50 BYTE)
COLUMN16 PROPERTIES_ARRAY_TYPE
COLUMN17 TIMESTAMP(6)
COLUMN18 TIMESTAMP(6)

In a database

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

I'm trying to select all the distinct values of COLUMN4; unfortunately this takes roughly 18 seconds despite the COLUMN4 being indexed.  There are only 5 unique items.

How can I speed up my query?

(Work doesn't let me upload images so I'll type out the execution plan, use your imagination)

Operation             OBJECT_NAME CARDINALITY   COST
---------------------- ----------- ----------- ------
SELECT STATEMENT       NIL                    4 65463
+ HASH(UNIQUE)         NIL                    4 65463
++ TABLE ACCESS (FULL) TABLE1           2010857 65334

If I saw a data model where a table had 2.1 million rows, one of the columns had only 5 distinct values, and I knew that people wanted to get a listing of those distinct values on a somewhat regular basis, I would strongly suspect that the data model was missing a lookup/ dimension table.  Rather than trying to tune your current query, I would bet that the better answer would be to create a separate table that has just those 5 distinct values, create a foreign key relationship between your current table and the new lookup table, and then modify your query to hit the new lookup table instead.


Does a UNIQUE constraint implicity contain the primary key? I'm aware that INNODB includes an implicit primary key in an index such as:

INDEX (non_primary_column)

Does it apply to the UNIQUE constraint too?

UNIQUE (other_primary_column) -- equivalent to INDEX (other_primary_column, pkey)?

… and FOREIGN constraint too?

FOREIGN KEY (some_other_column) -- equivalent to INDEX (some_other_column, pkey)?

Yes and no.

Yes in that the PK column(s) are tacked onto each UNIQUE index and the INDEX implicitly generated by each FOREIGN KEY.

No in that the PK column(s) are not part of the uniqueness constraint implied by UNIQUE.

For example:

PRIMARY KEY(id),
UNIQUE(x)

is like

PRIMARY KEY(id),
INDEX(x,id) and a uniqueness constraint on only `(x)`.

FOREIGN KEY(y) will generate INDEX(y) unless it finds an existing index starting with y.


How do I monitor which Windows user is logging in as SA? Possible Duplicate:
  ProcessID Owner with third party app

Several of our applications use the sa account to login to our SQL Server 2008 instance. As these applications are poorly designed enough to use sa as the login, so are they poorly designed enough to use the .Net SQLQuery Data Provider for their application name.

Our system is also thin-client-ish. In essence, all requests show up from the same host, that most users RDP into.

Recently, one of them has been forcing the database server to 90% CPU for up to 15 minutes at a time. But, I can't figure out who is doing what to cause it.

So: is there a way for me to tell which Windows user the application that is logging in as sa is running under? If yes, I can just ask them what they're doing.

If you can identify the query that is causing the problem (e.g. via session_id in sys.dm_exec_requests), you should be able to resolve the host_name by checking the same session_id in sys.dm_exec_connections. In lieu of an actual Windows account name, you should be able to tell who it is by the host name they're connecting from (assuming they aren't all using Remote Desktop and running queries locally too).


How to increase performance in Cross Database operations? We currently have a SQL Server 2008R2 being accessed by a legacy ASP application.

Among different processes, one of them essentially SELECTs data from one database and INSERTs (or UPDATEs) them into another database, based on a set of business rules.  This process generally runs at night after business hours, but on occasions it must be run during business hours to avoid task backlog.

We are now facing an I/O contention issue and so far my best guess is that this process is hurting the server.

Facts that support this hypothesis:

When the process is NOT running concurrently with ordinary operation, the server runs smoothly;
We generally experience I/O contention early in the morning; severely increased number of Page Reads/sec (avg 2000 reads/s), decreased Page Life Expectancy, some increase in LazyWrites.  As far as my knowledge goes, this is generally a sign of SQL Server self-tuning to a different workload.  This behaviour usually goes away after 20-something minutes.

I am now considering adding two other partitions (one for datafiles, one for logfiles) and move the database which receives the INSERTs to them, effectively distributing the I/O load.

Is this procedure worthwhile?
Are there any other variables to look into before venturing into disk rearrangement?  While not 100%, indices are fairly well in place, for instance.

Thanks in advance!

QUICK UPDATE

Having reviewed some monitoring graphs, I noticed this contention on some other occasions other than early in the morning.  While I don't profile the server continuously, it's safe to say that the process that copies between database was NOT running during most of these occasions, which means there might be other variables behind the contention.

Again, this only adds up to the question on which other variables I should have a look before playing with disks.

How is the maintenance of these two database?

The first have a lot of reads and the second have a lot of writes
The first database needs update statistic before the process and the second database needs a dbcc dbreindex() to avoid prevent space alocation and page split


Running forms in Oracle Cloud Service Since few years ago I have worked and extending a application build by Forms Builder 10g and running via Oracle Application Service 10g. The application can be launched as:

http://portal.dev:7778/forms/frmservlet?config=db

Now, we want to take a new step migrating our forms to the cloud an be accessed from anywhere. My first task was finding some resource into Oracle services, and I found Java Cloud Service - SaaS Extension:

  This is the number of Oracle WebLogic Server Managed Server instances to which the applications are deployed (customer managed Java EE application container)
  Preconfigured Oracle WebLogic Server 12c or 11g cluster configured with Oracle Database Cloud Service
  This is the amount of RAM allocated to the Java heap for all of the service's Managed Servers combined to run the Oracle WebLogic Server Managed Servers and the objects consumed by your application code

So, I decided to try the free month there. Successfully the account was created and I can navigate between all services listed there. But the real question is, which of all services there should I need to open? My first impression is to start with the "Java":

But I would like to take your advice, because I am not sure at all. Please, let me know if more details are needed.

Forms is a Middleware product in and of itself - not a user-developed J2EE application. http://www.oracle.com/technetwork/developer-tools/forms/overview/index.html is the best start page here. This means that to deploy to the cloud, you will need to deploy to a supported stack with the application server and forms runtime in place. That, in turn, most likely means that you would need to be running a custom VM at your cloud provider.

You mention as a reason access from anywhere. This is not a unique ability of the cloud. https://docs.oracle.com/middleware/12213/formsandreports/deploy-forms/using-oracle-forms-services-http-listener-and-oracle-weblogic-server.htm#FSDEP228 covers routing to forms via the HTTP Listener which you could locate in your own DMZ or in the cloud.


How to monitor performance in application insights of an on prem SQL Server database? Application Insights has great ways to monitor databases. After some online investigation, I saw an explanation of how to monitor actual queries. Monitoring SQL performance is possible too but what I found is an explanation for Azure SQL databases. I use on prem SQL Server....
What I want is doing such monitoring for on prem databases. However, the data needs to be monitored by application insights.
What I am typically interested in is this:

The number of records returned by each select query
The number of queries executed per hour/day
The performance of individual queries.

How can I do this?

I don't know about application Insight but many things already exists inside of an SQL prem server to allow you to monitor your server. I'll put here what I use in case it can help you.
For the number of queries per hour/day I monitor perfmon counter "Requests batch per second". https://www.brentozar.com/archive/2017/02/what-is-batch-requests-per-second/
You can also turn on the query store which will help you to monitor performances of queries and query plans.
But to get the number of records returned by each select and the performance of individual queries, you would need to run a trace on your server at all time. It seems to me a little too much and seems like it's going to maybe impact performances of your Server.
What I do is, I monitor perfmon counter latches and when it goes higher than my above threshold, I get an alert and go check what queries are running and do performance check on them. It allows me to catch the problems as they happen. https://www.mssqltips.com/sqlservertip/3088/explanation-of-sql-server-io-and-latches
To monitor perfmon we use a monitoring tool, Zabbix.
You can also use Brent Ozar's sp_blitz to help you. https://www.brentozar.com/blitz/
It's a really useful tool that I use also and it's free.


Forcing correct execution plan when passing a table UDT to a table function I have a scalar function that returns a big XML, created over a bunch of invoices.

The exact list of invoices to feed to the function can be calculated using several different ways, but the function is same every time. For this reason, I declared a user-defined table type to contain primary keys from the eInvoice.Header table and pass it to the function. This way I can have several different functions to decide which invoices to process, and just one function that actually generates XML:

create function eInvoice.GetRelevantLinesInOneWay()
returns table ...

create function eInvoice.GetRelevantLinesInAnotherWay()
returns table ...

create function eInvoice.GetXML(@lines eInvoice.InvoicePrimaryKeys readonly)
returns xml
as
begin
    declare @x xml;

    with xmlnamespaces(N'important namespace' as pro)
    select @x = (
        select
            ...
        from
            eInvoice.Header h
            inner join @lines l on h.ST_PRIMARY = l.invoice_row_id
        for xml path(N'pro:Import'), type
    );

    return @x;
end;

Unfortunately this setup has proven to be very fragile.

Typically @lines contains about 150 rows (out of about 1m in eInvoce.Header). The correct execution plan is to use index seek on ST_PRIMARY, and that is what always happens when I execute the body of eInvoice.GetXML as an ad-hoc query.

However when I store it as a function, it works as expected for some little time, then something happens (too many rows in @lines, like about 300?), and it decides to change the execution plan to a full scan on eInvoice.Header and to keep it that way.

With the seek plan the function executes instantly, with the scan plan I don't know how much time it takes, I waited for 30 minutes and cancelled it.

I've tried various ways of forcing the seek plan on the function.

Adding with (forceseek) after eInvoce.Header seems to work when I click "Display estimated execution plan," but when I actually execute it, the hint is ignored and a scan is performed.
Adding option (recompile) inside the function or outside, in the calling code, does not seem to have an effect.
Capturing the execution plan and hardcoding it inside the function with option (use plan N'<plan>') does not work because @lines contains a primary key on its only column, and the name of that index is different for each instance of @lines, but the execution plan has to refer to the index by fixed name.
I tried deleting cached execution plans and recompiling the function several times. It sometimes helps for a very limited time and sometimes has no effect.

Is there a way to force the index seek?

Summary
The question does not provide execution plans or a full reproduction script, but based on the information given, you should use a FAST 1 hint. If you can, you should also consider converting the scalar function to the inline table-valued type.
AdventureWorks-based examples
Download the Microsoft sample database here.
Table type
CREATE TYPE dbo.PrimaryKeys AS TABLE
(
    PK integer PRIMARY KEY
);

Scalar function
You could try various combinations of INNER LOOP JOIN (with the written join order reversed, and FORCE ORDER added to suppress the warning), FORCESEEK(index_name(columns)) (without schemabinding) and even QUERYTRACEON(8690) to prevent a performance spool; but in my experience, FAST 1 is the most reliable way to get the right plan, and simpler as well.
CREATE FUNCTION dbo.GetXML
(
    @Lines dbo.PrimaryKeys READONLY
)
RETURNS xml
WITH SCHEMABINDING -- If possible
AS
BEGIN
    DECLARE @x xml;

    WITH XMLNAMESPACES (N'important namespace' AS pro)
    SELECT @x =
    (
        SELECT
            SOH.SalesOrderID,
            SOH.OrderDate,
            SOH.DueDate,
            SOH.ShipDate,
            SOH.[Status],
            SOH.PurchaseOrderNumber,
            SOH.AccountNumber,
            SOH.CustomerID,
            SOH.TotalDue
        FROM @Lines AS L
        JOIN Sales.SalesOrderHeader AS SOH
            ON SOH.SalesOrderID = L.PK
        ORDER BY
            SOH.SalesOrderID
        FOR XML PATH(N'pro:Import'), TYPE
    )
    OPTION (FAST 1);

    RETURN @x;
END;

In addition to all the usual reasons to avoid scalar functions, they do make plan analysis harder. First, you need to capture actual plans using Profiler or Extended Events - they do not appear in query tools like SSMS. Second, not all hints work as you would expect, for example RECOMPILE due to scoping and the separate plans.
DECLARE @Lines dbo.PrimaryKeys;

INSERT @Lines (PK)
SELECT TOP (300) SOH.SalesOrderID
FROM Sales.SalesOrderHeader AS SOH;

SELECT dbo.GetXML(@Lines);

The code above reliably produces the desired plan for me regardless of the number of rows in the table variable.
Without the FAST 1 hint in the function, the optimizer chooses a merge join and scan of both tables. This plan is cached and reused for a later function execution where TOP (300)is changed to TOP (1), reproducing the core of the issue.
Inline table-valued function
Unless there are absolutely compelling reasons to stick with the scalar function, you should probably rewrite it to inline table-valued form as follows:
CREATE FUNCTION dbo.GetXMLTable
(
    @Lines dbo.PrimaryKeys READONLY
)
RETURNS TABLE
AS
RETURN
    WITH XMLNAMESPACES (N'important namespace' AS pro)
    SELECT
        X.xml_result
    FROM
    (
        SELECT
            SOH.SalesOrderID,
            SOH.OrderDate,
            SOH.DueDate,
            SOH.ShipDate,
            SOH.[Status],
            SOH.PurchaseOrderNumber,
            SOH.AccountNumber,
            SOH.CustomerID,
            SOH.TotalDue
        FROM @Lines AS L
        CROSS APPLY
        (
            SELECT TOP (1) SOH.*
            FROM Sales.SalesOrderHeader AS SOH
                WITH (FORCESEEK(PK_SalesOrderHeader_SalesOrderID(SalesOrderID)))
            WHERE
                SOH.SalesOrderID = L.PK
            ORDER BY
                SOH.SalesOrderID
        ) AS SOH
        ORDER BY
            SOH.SalesOrderID
        FOR XML PATH(N'pro:Import'), TYPE
    ) AS X (xml_result);

The TOP (1) in the cross apply prevents the optimizer choosing anything other than nested loops join. The FORCESEEK hint is not required; I have included it to illustrate how it could be used if necessary.
DECLARE @Lines dbo.PrimaryKeys;

INSERT @Lines (PK)
SELECT TOP (300) SOH.SalesOrderID
FROM Sales.SalesOrderHeader AS SOH;

SELECT GXT.xml_result
FROM dbo.GetXMLTable(@Lines) AS GXT;

The actual execution plan (without using Profiler/XE) is:


Cannot restore SQL database backup to new database because new database is in use I am attempting to create a new database from a backup file of an old database that is still in use. At one point I was able to successfully restore the database, but had to delete it later after attempting to overwrite it with fresh data. Now when I try to restore the database I get an error that the database is in use. I am certain to specify a different name from the old and have even attempted to give it a different name than the one I was giving it before. I have also made certain to rename the data and log files to something other than the old database.

This is the stack trace for the error:

Restore of database 'Database' failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.RelationalEngineTasks.RestoreDatabaseTaskFormComponent.PerformTask(ITaskExecutionContext context)
   at Microsoft.SqlServer.Management.RelationalEngineTasks.RestoreDatabaseTaskFormComponent.Perform(ITaskExecutionContext context)
   at Microsoft.SqlServer.Management.TaskForms.TaskExecutionManager.ExecuteTaskSequence(ISfcScriptCollector collector)

===================================

System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use. (Microsoft.SqlServer.SmoExtended)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.5058.0+((SQL11_PCU_Main).140514-1820+)&LinkId=20476

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Smo.RestorePlan.Execute()
   at Microsoft.SqlServer.Management.RelationalEngineTasks.RestoreDatabaseTaskFormComponent.PerformTask(ITaskExecutionContext context)

We have tried restarting the SQL server to close any possible calls to the database, but this did not correct the issue. I have checked the 'overwrite' and 'close existing connections' boxes in the restore wizard, but these also have not corrected the issue. Any other tips on how to correct this issue?

You should offline the database and restore over it

Alter database db-name
Set offline with rollback immediate
Restore database .. with recovery

Ditch SSMS GUI and use tsql.


Single quote (') in MySQL console This is normal MySQL query

mysql> select database();
+------------+
| database() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

mysql>

I notice that when I accidentally put ' in it ... the console seems to stuck there.

mysql> '
    '>
    '> select database();
    '>
    '> '

SQL query such as select database(); doesn't work anymore.

The question is why is this happen with '? Is there any explanation for this behavior?

I notice that when I accidentally put ' in it ... the console seems to stuck there

It's not "stuck" at all.

It's patiently waiting for you to supply the closing quote for the string (or date) literal that you started when you typed in the opening quote!  It even goes as far as to give you a "hint" that it's doing so - notice the different prompt it offers you, complete with a quote character in it!

Of course, when you do provide that closing quote, it will complain bitterly because what you've entered doesn't make sense.


Stored Procedure taking longer than a normal query I have a stored procedure which returning single but taking 16+ seconds when I call that but only .5 second, if I run that by normal query.

Procedure

    DELIMITER $$
DROP PROCEDURE IF EXISTS `local_db`.`getAllMatchedStockByCompany` $$
CREATE
    PROCEDURE `local_db`.`getAllMatchedStockByCompany`(
    IN Company_ID BIGINT(20)
    )
    BEGIN
        SELECT t1.*
    FROM ((SELECT t2.SRTitle,t2.UrlPart,t2.ScheduledTime,t2.Photo,TRIM(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING_INDEX(REPLACE(fnStripTags(t2.SRDescription),'&nbsp;',''),' ',
            30), '\r\n\t\r\n\r\n\t', ''),'\r\n\t\r\n\t',''),'\r\n\t',''),'&rsquo;',''))AS Description, 'stock-research' AS from_tbl,t2.IsActive
           ,t2.CompanyID FROM t2
          ) UNION
          (SELECT t3.ReportTitle,t3.UrlPart,t3.CreatedDate,t3.ReportImage,TRIM(REPLACE(REPLACE(SUBSTRING_INDEX(REPLACE(fnStripTags(t3.Description),'&nbsp;',''),' ',
                30),'\r\n\t\r\n\t',''),'&#39;','`')),IF(t3.SpecialReport = 1,'special-report','report'),t3.IsActive
        ,t3.CompanyID FROM t3
          )
         ) t1
         WHERE CompanyID = Company_ID AND IsActive = 1
         ORDER BY ScheduledTime DESC;
    END$$

DELIMITER ;

The total execution time of this is 16 seconds with 1 row.

If I use below query:

SELECT t1.*
    FROM ((SELECT t2.SRTitle,t2.UrlPart,t2.ScheduledTime,t2.Photo,TRIM(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING_INDEX(REPLACE(fnStripTags(t2.SRDescription),'&nbsp;',''),' ',
            30), '\r\n\t\r\n\r\n\t', ''),'\r\n\t\r\n\t',''),'\r\n\t',''),'&rsquo;',''))AS Description, 'stock-research' AS from_tbl,t2.IsActive
           ,t2.CompanyID FROM t2
          ) UNION
          (SELECT t3.ReportTitle,t3.UrlPart,t3.CreatedDate,t3.ReportImage,TRIM(REPLACE(REPLACE(SUBSTRING_INDEX(REPLACE(fnStripTags(t3.Description),'&nbsp;',''),' ',
                30),'\r\n\t\r\n\t',''),'&#39;','`')),IF(t3.SpecialReport = 1,'special-report','report'),t3.IsActive
        ,t3.CompanyID FROM t3
          )
         ) t1
         WHERE CompanyID = 931 AND IsActive = 1
         ORDER BY ScheduledTime DESC;

It took only .5 to 1 seconds with one row.

Can anyone of you let me why this happening or how I can improve my SP execution time?

It is usually better to cleanse the data before inserting it into the database.
The query can be sped up in both cases by some rearranging; see below.
Is this the only difference? -- CompanyID = 931 versus CompanyID = Company_ID
Is CompanyID declared BIGINT?
Can you get EXPLAIN output, both in the SP and outside, to help verify the theory that it is easier to work with a number than a parameter passed in.
If you know that there will be no duplicates, use UNION ALL, which is faster.

By moving the WHERE into each SELECT but leaving the `ORDER BY outside, the query is likely to be faster:

( SELECT ... WHERE ... )
UNION [DISTINCT|ALL]
( SELECT ... WHERE ... )
ORDER BY ...

Of the main problem is 931 versus Company_ID, then the solution may involved constructing the query and using prepare and execute.


postgresql does not seem to respect the pg_hba.conf config - how can I diagnose this? I have the following pg_hba.conf:
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

With the above I assume host    all             all             127.0.0.1/32            trust would allow TCP connections from localhost. However this does not seem to be the case
[root@XenonKiloCranberry:~]# psql -U postgres
psql (11.7)
Type "help" for help.

postgres=# \q

[root@XenonKiloCranberry:~]# psql -U postgres -h 127.0.0.1
psql: FATAL:  no pg_hba.conf entry for host "127.0.0.1", user "postgres", database "postgres", SSL off

Where am I going wrong?
Output of select * from pg_hba_file_rules:
line_number | type  | database | user_name | address |                 netmask                 | auth_method | options | error
-------------+-------+----------+-----------+---------+-----------------------------------------+-------------+---------+-------
           1 | local | {all}    | {all}     |         |                                         | trust       |         |
           2 | host  | {all}    | {all}     | ::1     | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | trust       |         |
(2 rows)

If you have restarted the database and the contents of the view pg_hba_file_rules doesn't match your pg_hba.conf file, then you probably have more than one such file, and edited the one which is not being used.  If you can connect via another means, you can use show hba_file; to get PostgreSQL to tell you which file is the one that is being used.


Migrate database from SQL Server 2008 Enterprise to SQL Server 2014 Standard I need move database with "data compression" from SQL Server 2008 Enterprise to SQL Server 2014 Standard.

But Standard edition does not support "data compression".

How can I disable "data compression" in database?

I tried this:

ALTER INDEX [_Acc13_ByCode_SR] ON [dbo].[_Acc13]
   REBUILD PARTITION = ALL
   WITH (DATA_COMPRESSION = NONE)

but still see that compression is enabled:

I need move database with "data compression" from MS SQL 2008 Enterprise to MS SQL 2014 standard.

Downgrading is always a pain !

If you have a source control for your database, I would suggest you to create the database shell (schema, objects - tables, views, indexes) on the new 2014 standard server.

BCP out and BULK INSERT from the old server (Enterprise edition) to New 2014 (standard edition) server.

I would say, rely on T-SQL rather than GUI. From Disable Compression on a Table or Index

-- To disable compression on a table

USE AdventureWorks2012;
GO
ALTER TABLE Person.Person REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = NONE);
GO

-- To disable compression on an index

USE AdventureWorks2012;
GO
ALTER INDEX AK_Person_rowguid ON Person.Person REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
GO


Insert query in mysql I am using MySQL. I have table like below:

CREATE TABLE test_table
(
    first_name VARCHAR(20),
    last_name VARCHAR(20),
    full_name VARCHAR(40)
);

Now I want to insert values like below:

INSERT INTO test_table
VALUES('raju','pydi',CONCAT(first_name,last_name));

I know this will not work, but it illustrates what I want to achieve.

How can I do this with a query? (not a procedure or function or some other).

You could do something like:

INSERT INTO test_table
    (first_name,last_name,full_name)
SELECT first_name,last_name
     , CONCAT(coalesce(first_name,''),coalesce(last_name,''))
FROM (
    SELECT 'raju' as FIRST_NAME
          ,'pydi' as LAST_NAME
) AS T;

I still think it's a pretty bad idea to store full_name though. You could create a view like:

CREATE VIEW TEST_VIEW AS (
    SELECT first_name, last_name
         , CONCAT(coalesce(first_name,''),coalesce(last_name,'')) as full_name
    FROM test_table
);


MYSQL search for each name in 2 columns and add values associated for each player I have a database "game_results".

It contains the following:

PlayerID (int not null primary key auto increment)
player1 (varchar)
p1destroyed (int)
player2 (varchar)
p2destroyed (int)
winner (varchar)

It's not the most efficient database (multiple repeats of data in several columns), but it's meant to be a simple way of tracking game results.

After each game, the players input their names, the point amount of ships they killed of the opposing player and then who won.

I'm trying to get the data from both players and add up the for and against points destroyed for each player.

I know this is where the poor design has bitten me, but I'd rather try and figure this out for other things I have planned.

I want to be able to get a result set that gets each unique player with both the amount of points they have destroyed and the amount they have had destroyed by their opponents.

I have figured out how to do it in one way:

SELECT
player1, SUM(p1destroyed) AS Fmov, SUM(p2destroyed) AS Amov
FROM game_results
GROUP BY player1;

But I'm stuck getting the unique values from both p1 and p2 to merge.

I'm thinking a union is probably the right place to look, but I just can't seem to make it work.

Any ideas please?

Glorfindel's answer is good within the parameters of your question, but I'll talk a bit about how else you could have designed it, in case that's helpful.

Basically, having a single table with player1 and player2 isn't great because the two players have the same relationship to the game so it should be represented the same way. If it's important to keep track of which one was #1 and which was #2, then it's probably better to think of that as an attribute of the player's membership in the game.

CREATE TABLE `player` (`id` PRIMARY KEY, ...);

CREATE TABLE `game_results` (`id` PRIMARY KEY, ...);

CREATE TABLE `game_player` (
  `game_id`,
  `player_id`,
  `destroyed`,
  PRIMARY KEY (`game_id`, `player_id`),
  FOREIGN KEY (`game_id`) REFERENCES `game_results` (`id`),
  FOREIGN KEY (`player_id`) REFERENCES `player` (`id`),
  ...
);

Some open questions remain from there.

Where should the results be recorded?

You could just have a nullable foreign key winner from game_results to game_player (id).
You could have an attribute result of game_player. This could be an enum if you're in MySQL 5.7, or a foreign key to a small table of possible results. I'd prefer this because it would better accommodate draws and annulments.

The join-table design naturally allows for games with more than two players. Whether or not that's good depends on the situation. Assuming we want to limit the number of players, how would we do that?

Add another enum or foreign key to an enum table representing the idea of "player 1" and "player 2". Add a UNIQUE KEY game_player (game_id, player_number). This is what I would suggest.
Add a FOREIGN KEY game_player(partner) REFERENCES game_player(id). This would work for your current situation, but isn't an open-ended solution.
You can't use a CHECK constraint in MySQL. This is one of the reasons I dislike MySQL. Here's the reference.

In any case, the final query you're trying to build would look something like

SELECT gp1.player_id, SUM(gp1.destroyed) AS Fmov, SUM(gp2.destroyed) AS Fmov
  FROM `game_player` gp1
  [LEFT | INNER] JOIN `game_player` gp2
    ON gp1.game_id = gp2.game_id
      AND gp1.player_id <> gp2.player_id
  [WHERE gp1.result in (`win`, `loose`, `tie`)]
  GROUP BY gp1.player_id;


Cassandra 3.11 commit logs nightmare Clean installs of Cassandra 3.11 nodes are working fine for me for a few days, and then ‘nodetool flush’ and ‘nodetool compact’ are starting to throw exceptions. After this, restarts after a ‘nodetool drain’, fail. forcing deletion of commitlogs. Is this a problem with 3.11 in general? If What version do we need to downgrade to, to resolve this?

(I recognise that the causes might not be related to 3.11, ie perhaps or something about the key space I have, or perhaps an issue with Ubuntu 16? But I am just asking about 3.11 specifically and if it has some sort of known flaw threat I can’t see in JIRA)

There is a bug in Cassandra 3.11.x series that means nodetool flush and nodetool compact fail when using a 32 bit install of Java. Switching to 64 bit version solves the problem.

https://issues.apache.org/jira/browse/CASSANDRA-13897


How to compute number of concurrent application sessions using SQL Server table storing session data? I have a vendor application which stores session data in a SQL Server 2008 database table. Columns it includes are things like the sessionId, the user's IP address, the datetime stamp when the session was created (i.e. user logs in), and the datetime stamp of when the session was destroyed (i.e. user logs off or system logs off user).

My objective is to analyze all the records in this table and figure out the average number of concurrent sessions across all records.

Now, unfortunately, the session destroy date is not accurate, for reasons outside the scope of this question. Therefore, I'm using a very rough estimate for the duration of the session: 1 hour. And I can always change the number later after I have the design phase sorted out.

I'm certain I can put together a stored procedure to get the average number of concurrent sessions, but I was hoping I can accomplish it with a query.

To simplify things here, let's assume there are 5 records in the table and all were created on the same day and are in GMT time:

sessionId  sessionStart  sessionEnd  Accumulative # of Concurrent Sessions
1          12:00         13:00       1
2          12:15         13:15       2
3          12:30         13:30       3
4          12:45         13:45       4
5          13:00         14:00       4

At 13:00, the first session is destroyed. The number of concurrent sessions stays at 4, since sessions 2 through 5 still exist.

The question is how I can write a query which will output the average number of concurrent sessions? Can it be done? I imagine it would involve multiple joins on the same table, but I haven't quite figured out yet where to start.

The table has under a million records. I have access to a 2012 box and can copy the table there if it helps.

I find SQL Server 2012 to be a much better fit for this kind of problem because it supports ORDER BY in the OVER clause for the SUM window aggregate. Throwing your data into a temp table:

CREATE TABLE #my_sessions (sessionId INT, sessionStart DATETIME);

INSERT INTO #my_sessions VALUES
(1, '20180413 12:00:00'),
(2, '20180413 12:15:00'),
(3, '20180413 12:30:00'),
(4, '20180413 12:45:00'),
(5, '20180413 13:00:00');

I'll break the query into three parts so it's easier to understand. The first trick uses a running total to get the number of concurrent sessions at each time it changes. Imagine taking your data, assigning a 1 for the rows that create a session, and making a second copy of it for the destroyed rows and assigning a -1 for those rows. If you calculate a running total ordered by time you end up with the number of active sessions at each time the value changes.

SELECT DISTINCT
  event_time
, SUM(event_change) OVER (ORDER BY event_time RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) active_sessions
FROM #my_sessions
CROSS APPLY (
    VALUES
    (sessionStart, 1),
    (DATEADD(HOUR, 1, sessionStart), -1)
) ca (event_time, event_change);

Here are the results:

╔═════════════════════════╦═════════════════╗
║       event_time        ║ active_sessions ║
╠═════════════════════════╬═════════════════╣
║ 2018-04-13 12:00:00.000 ║               1 ║
║ 2018-04-13 12:15:00.000 ║               2 ║
║ 2018-04-13 12:30:00.000 ║               3 ║
║ 2018-04-13 12:45:00.000 ║               4 ║
║ 2018-04-13 13:00:00.000 ║               4 ║
║ 2018-04-13 13:15:00.000 ║               3 ║
║ 2018-04-13 13:30:00.000 ║               2 ║
║ 2018-04-13 13:45:00.000 ║               1 ║
║ 2018-04-13 14:00:00.000 ║               0 ║
╚═════════════════════════╩═════════════════╝

Now we need to take the average of that value. I assume that you want a weighted average by time, so what's missing is the number of minutes that each measurement should count for. SQL Server 2012 introduces the LEAD function which makes this pretty easy. Now the query is as follows:

SELECT
  active_sessions
, DATEDIFF(MINUTE, event_time, LEAD(event_time) OVER (ORDER BY event_time)) minutes_until_change
FROM
(
    SELECT DISTINCT
      event_time
    , SUM(event_change) OVER (ORDER BY event_time RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) active_sessions
    FROM #my_sessions
    CROSS APPLY (
        VALUES
        (sessionStart, 1),
        (DATEADD(HOUR, 1, sessionStart), -1)
    ) ca (event_time, event_change)
) active_sessions

The intermediate result set:

╔═════════════════╦══════════════════════╗
║ active_sessions ║ minutes_until_change ║
╠═════════════════╬══════════════════════╣
║               1 ║ 15                   ║
║               2 ║ 15                   ║
║               3 ║ 15                   ║
║               4 ║ 15                   ║
║               4 ║ 15                   ║
║               3 ║ 15                   ║
║               2 ║ 15                   ║
║               1 ║ 15                   ║
║               0 ║ NULL                 ║
╚═════════════════╩══════════════════════╝

We need to calculate the average which is the easiest part. Putting it all together:

SELECT 1.0 * SUM(active_sessions * minutes_until_change) / SUM(minutes_until_change)
FROM
(
    SELECT
      active_sessions
    , DATEDIFF(MINUTE, event_time, LEAD(event_time) OVER (ORDER BY event_time)) minutes_until_change
    FROM
    (
        SELECT DISTINCT
          event_time
        , SUM(event_change) OVER (ORDER BY event_time RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) active_sessions
        FROM #my_sessions
        CROSS APPLY (
            VALUES
            (sessionStart, 1),
            (DATEADD(HOUR, 1, sessionStart), -1)
        ) ca (event_time, event_change)
    ) active_sessions
) average_me
WHERE minutes_until_change IS NOT NULL;

The final result is 2.5.

Apparently, this can also be done in SQL Server 2008.


FullText Index on VARBINARY column: how to find string with dot On a SQL Server 2012 SP3 CU8 server instance, we have PDF files that have been inserted into a table called OBJECT_FILE created as follows:

CREATE TABLE [dbo].[OBJECT_FILE](
    [FILE_ID] [int] NOT NULL,
    [FILE_FIN] [int] NOT NULL,
    [OBJ_FILE_IDX_DOCTYPE] [varchar](3) NULL,
    [FILE_TYPE] [smallint] NOT NULL,
    [FILE_TXT] [varbinary](max) NULL,
    [FILE_TXT_SIZE] [int] NULL DEFAULT ((0)),
    CONSTRAINT [PK_DM_OBJECT_FILE] PRIMARY KEY CLUSTERED (
        [FILE_ID] ASC
    )
    WITH (
        PAD_INDEX = OFF,
        STATISTICS_NORECOMPUTE = OFF,
        IGNORE_DUP_KEY = OFF,
        ALLOW_ROW_LOCKS = ON,
        ALLOW_PAGE_LOCKS = ON
    ) ON [INDEXFG]
) ON [INDEXFG] TEXTIMAGE_ON [BLOBFG]

We also created a FullText Index that is built on FILE_TXT column, which is a VARBINARY(MAX), created as follows:

CREATE FULLTEXT INDEX
ON OBJECT_FILE (
    FILE_TXT TYPE COLUMN OBJ_FILE_IDX_DOCTYPE
)
KEY INDEX PK_DM_OBJECT_FILE
WITH STOPLIST=OFF;

In that table, we are willing to insert the contents of old documents. In these contents, we will find a unique identifier inserted in the past which follows a naming convention:

CORP-MMM-<three_digits_0_to_9>.<three_digits_0_to_9>

When I try to do a lookup using CONTAINS built-in function on a particular identifier that I know it exists, it doesn't give me back any results. I use following WHERE clause:

WHERE CONTAINS(FILE_TXT,'CORP-MMM-456.245')

although a lookup with following WHERE clause provides me multiple results:

WHERE CONTAINS(FILE_TXT,'CORP-MMM-456.*')

and the record in question can be found with LIKE:

WHERE FILE_TXT LIKE '%CORP-MMM-456.245%'

My question is:

Why can't I get a specific record regarding my WHERE clause and how could I make it work ?

Sorry this might not be an answer but I cannot put this in comment.  Based on the comment by OP @Jefferson (as an answer to my comment) I could not reproduce the behavior.  I can get a record back with where clause exactly matching the value.

I tested this with following code (copied and modified from here)

CREATE TABLE Department
   (DepartmentID INT IDENTITY CONSTRAINT DepartmentPK PRIMARY KEY,
    NAME NVARCHAR(50),
    File_txt NVARCHAR(50));
GO
INSERT INTO Department (NAME, File_txt) VALUES ('OP1', 'CORP-MMM-456.245');
INSERT INTO Department (NAME, File_txt) VALUES ('OP2', 'CORP-MMM-456.246');
INSERT INTO Department (NAME, File_txt) VALUES ('OP3', 'CORP-MMM-456.247');
INSERT INTO Department (NAME, File_txt) VALUES ('OP4', 'CORP-MMM-456.245.246');

Go
CREATE FULLTEXT CATALOG ComplexFTS AS DEFAULT;
GO
CREATE FULLTEXT INDEX ON Department(File_txt LANGUAGE 1033 /* 0 = Neutral, 1033 = American English  */)
KEY INDEX DepartmentPK WITH (STOPLIST = OFF); /* Or use (stoplist = Off) for no stoplist */
GO
WAITFOR DELAY '00:00:05';
GO
SELECT * FROM Department WHERE CONTAINS (File_txt,'"CORP-MMM-456.*"');
SELECT * FROM Department  WHERE CONTAINS(File_txt,'CORP-MMM-456.245')
GO

This is what I get as my result set.

Clean up code.

DROP FULLTEXT INDEX ON Department;
GO
DROP FULLTEXT CATALOG ComplexFTS;
GO
DROP TABLE Department;
GO


Would querying from a partitioned table be faster than from multiple tables with the same table definition? I have a table that contains about 50 million rows that I have partitioned by 5 million rows per partition. I have manually partitioned it into Table_1, Table_2, Table_3, etc., not using MySql's built-in partition feature using the PARTITION keyword because I didn't know of this feature when I partitioned my table.

A lot of the times, I'll have to retrieve 10 million worth of rows, which would mean I'll have to query 2 partitions worth of data.

It may take anywhere from 2-10 seconds to retrieve the entire data (5 million rows) from just 1 table, which is pretty slow. Though after the initial query, the same query would be very fast (a few hundred ms).

Would partitioning my table(s) using the built in MySql partition feature help querying faster in my case?

Run the same queries twice.  Did they run faster the second time?  Guess what.  The first time the data was sitting on disk and had to be pulled from disk.  The second time, it was all sitting in RAM.  The difference might be 10-fold.

Why would you ever fetch 5M rows at once?  And, if you split 50M rows into 10 'partitions', why would that 5M be relevant?  Might you need a different slice of the data?

Partitioning, either manually, or with PARTITION, rarely provide any performance benefit for real tasks.

If you often need to fetch 10M rows, let's discuss ways to avoid fetching that many rows, yet still get the same answer.  Using Summary Tables is a common technique in Data Warehousing.

Provide more details, and I will explain in more detail why I am throwing cold water on your Question.


CREATE VIEW as join between tables with identically named columns As the title says, I'd like to create a view from a join between tables which have columns with identical names (surprisingly "id").

Postgres doesn't prepend the table name to the result, but for some reason the query works fine, returning multiple columns with the same name and different data.

CREATE TABLE is less forgiving, though, and  returns:

ERROR: column "id" specified more than once

Others have suggested SELECTing the columns under a different name (e.g. here), but there are many columns to both tables.

Do I need to somehow automatically rename / remove the id columns (such as this way), or is there a better way?

There was a bug in my answer to the question you referenced. While being at it, I improved some other details:

Prepend table name to each column in a result set in SQL? (Postgres specifically)

If you have many columns and / or for repeated use, I would use dynamic SQL as outlined over there.

If you'd been using a proper naming convention, you could avoid most of these cases. Never use id as column name, it's too ambiguous as you are just finding out the hard way. I would use tbl_id instead, "tbl" being the table name.
Unfortunately, some ORMs work with this anti-pattern.

How to implement a many-to-many relationship in PostgreSQL?

And you do not get any exception for a plain SELECT because Postgres does not require distinct column names in a plain SELECT (although the usefulness of this is limited beyond ad-hoc queries). Distinct names are required for columns of a table or view, though - even derived tables in subqueries. The manual:

  Just as in a table, every output column of a SELECT has a name. In a
  simple SELECT this name is just used to label the column for display,
  but when the SELECT is a sub-query of a larger query, the name is seen
  by the larger query as the column name of the virtual table produced
  by the sub-query.

Postgres has no need for distinct labels. But actual column names must be distinct to avoid ambiguities.


These queries may have slightly different semantics, from the query parser point of view.

select ... where field3 IS FALSE means "evaluate expression field3 IS FALSE and return rows for which the evaluation result is TRUE". The expression matches that of your partial index, so it's a natural choice.

select ... where not field3 means "evaluate expression NOT field3 and return rows for which the evaluation result is TRUE". Here the expression does not match the partial index.

We know that these expressions are logically equivalent, particularly in the presence of the NOT NULL constraint on the column in question, but the parser (or the optimizer) apparently cannot see that.

I suspect if you change your partial index condition to WHERE NOT field3 you'll see that the plans for your two queries switch places.


Load text column to postgres I use the following to load data into postgres
\copy tab FROM /tmp/file DELIMITER E'\t' QUOTE E'\b' NULL AS '\N' CSV

Usually I select the data from a source db
echo "select * from tab" | mysql --batch --quick --skip-column-names .... > /tmp/file

It generates a file with tab delimiter fields. It works well for the most part. Up until I try to import multi line text column.
The error comes on:
ERROR:  unquoted carriage return found in data

Any recommendations how to overcome this?

The mysql command you show seems (experimentally, not from actual knowledge) to generate output which is compatible with PostgreSQL's TEXT format, not the CSV format.  Newlines are represented by the two characters \n, tabs by \t, and literal backslashes by \\.
So try to import it in that format, which is the default:
\copy tab FROM /tmp/file


`select OBJECT_ID(N'X')` takes "forever" I have encountered strange state of a SQL Server. In one particular database in this SQL Server select OBJECT_ID(N'X') takes "forever". How to diagnose what is going on?

EDIT: Problem disappeared. However, I am still curious what was going on.

OBJECT_ID() (and several other metadata functions) have to observe locks taken against objects, and wait for them to be released. I generally recommend against their use because, unless you actually want to wait for the lock to release (which is totally valid, but uncommon), you end up getting blocked. This is what I suspect happened here, though there is no way to prove it now.

I blogged about this here:

Bad habits : Using (certain) metadata "helper" functions

The workaround is, instead of using the functions, pull the values from the catalog views. It's more code, but it will obey isolation semantics, unlike the functions, which won't. So instead of:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT OBJECT_ID(N'dbo.foo');
-- actually won't obey isolation level

You say:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- will obey isolation level
SELECT o.[object_id]
  FROM sys.objects AS o
  INNER JOIN sys.schemas AS s
  ON o.[schema_id] = s.[schema_id]
  WHERE s.name = N'dbo'
  AND o.name = N'foo';

Again, it's more code, but you've found the price of convenience here. It's a shame schema name isn't exposed as a computed column; I wrote a tip about creating your own views to get around this and other complexities here:

Making SQL Server metadata queries easier with these new Views


If a user is created in a primary postgres database, will the user be copied to a follower? Say a user is created in postgres 9.6 with some specific privilege set:

CREATE USER foo_user WITH ENCRYPTED PASSWORD 'foo_password';
GRANT CONNECT ON DATABASE bar_db TO foo_user;
GRANT USAGE ON SCHEMA public TO foo_user;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO foo_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public to foo_user;

(Copied and modified from here.)

If that user is created on a primary database, and there is a follower synced with WAL streaming replication, will that user be created with an identical set of privileges on the follower automatically?

It looks like the answer is yes, because users and permissions are represented with tables, and those special tables are probably treated the same as any other table for write ahead logging.  But I can't confirm this in any of these docs pages.  Can confirm or debunk my thinking?  And ideally point me at some related documentation that I can't seem to find?

If you're doing WAL streaming, you're dealing with physical replication. That means your secondary data cluster is a total clone of the primary.

Your data cluster is made of files. With physical replication your files will be totally identical bit by bit between the primary and the secondary node.

So yes, roles, permission, etc are replicated.


How does mysql optimiser chooses a composite Index I have a table with the following structure.
 -id (bigint|pk)
 -funnel_id (bigint|fk with funnels table)
 -affiliate_id (bigint|fk with affiliates table)
 -created_timestamp (bigint)

Observations

funnel_id has 10,000 unique records
affiliate_id has 15,000 unique records

I have various combinations of SELECT query on the table like:
1) SELECT * FROM tbl WHERE affiliate_id = 300;
2) SELECT * FROM tbl WHERE funnel_id = 300;
3) SELECT * FROM tbl WHERE affiliate_id = 300 AND funnel_id = 300;
4) SELECT * FROM tbl WHERE affiliate_id = 300 AND funnel_id = 300 AND created_timestamp BETWEEN 600 AND 700;

So as per the rule, I should create a composite index with the below statement. Also, it gives me a (100 score) when I checked with the EXPLAIN keyword on the query.
CREATE INDEX ON tlb index_name(affiliate_id, funnel_id, created_timestamp)

But as soon as I create a foreign_key on funnel_id and on affiliate_id the optimizer starts picking up the foreign_key index and the (100 score) also reduces (to 10).
But then I created a composite index on (affiliate_id, funnel_id). Guess what happend next. The query optimizer again stated to pick the (affiliate_id, funnel_id, created_timestamp) index.
So my question here is, why the optimizer didn't pick the (affiliate_id, funnel_id) index. Guys, please let me know your views on this as it is getting a bit confusing.

1) SELECT * FROM tbl WHERE affiliate_id = 300;
2) SELECT * FROM tbl WHERE funnel_id = 300;
3) SELECT * FROM tbl WHERE affiliate_id = 300 AND funnel_id = 300;
4) SELECT * FROM tbl WHERE affiliate_id = 300 AND funnel_id = 300 AND created_timestamp BETWEEN 600 AND 700;

INDEX(affiliate_id, funnel_id, created timestamp) handles queries 1,3,4, but not 2.  It is slightly suboptimal for 3 and 1 because it is bulkier than INDEX(affiliate_id, funnel_id) or INDEX(affiliate_id)
Depending on how/when you added the FKs, MySQL is sometimes smart enough to not add a new index because it can see that an adequate one exists.  Do SHOW CREATE TABLE to see the collection of indexes that exist now.  If you see some redundant indexes, DROP them.
If you are creating the table fresh, then this order might be best:

Create this table with INDEX(affiliate_id, funnel_id, created timestamp) and INDEX(funnel_id).  Create other tables and their optimal indexes.
Then add the FKs.
Get rid of any shorter indexes which are a prefix of a longer one.  The FKs should remain happy that there is some index starting with the column it needs to check.

If you provide CREATE TABLEs for the various tables, plus a few INSERTs.  I may play around with it.
Back to your question of what the Optmizer does...
When only x is needed, and given a choice between INDEX(x) and INDEX(x,y), the optimizer will pick the "smaller" index, namely (x).  This is not always optimal.
An example is INDEX(lat), INDEX(lat, long) and WHERE lat BETWEEN ... AND long between ....  In this case the query execution would take advantage of the 2-column index for ICP (Index Condition Pushdown), but it seems to be too dumb to realize it.  It picks the 1-column index and has to work harder to check long.  (This issue may vary with versions of MySQL and MariaDB; there have been a lot of changes in the area.)


Stop long running scheduled job I need to launch a scheduled job at 12 o'clock at night and keep this job running for a period of 8 hours. What happens now is that the job is running for more than 12 hours and I really need to avoid running for so long. I have used the Max Run Duration option but it does not have the desired effect for my purpose. I would appreciate if someone could give me an example of how it should be. Database is Oracle 12c. Here I put the script:

BEGIN
  SYS.DBMS_SCHEDULER.CREATE_JOB
    (
       job_name        => 'SYS.POB_TB_JOB'
      ,start_date      => TO_TIMESTAMP_TZ('2019/02/10 00:05:00.000000 America/New_York','yyyy/mm/dd hh24:mi:ss.ff tzr')
      ,repeat_interval => 'FREQ=DAILY;BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN'
      ,end_date        => NULL
      ,job_class       => 'DEFAULT_JOB_CLASS'
      ,job_type        => 'STORED_PROCEDURE'
      ,job_action      => 'SELLER.FILL_TB_S1'
      ,comments        => NULL
    );

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'SYS.POB_TB_JOB'
     ,attribute => 'MAX_RUN_DURATION'
     ,value     => TO_DSINTERVAL('+000 05:00:00'));

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'SYS.POB_TB_JOB'
     ,attribute => 'number_of_arguments'
     ,value     => 4);

  SYS.DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE
    ( job_name             => 'SYS.POB_TB_JOB'
     ,argument_position    => 1
     ,argument_value       => '30');

  SYS.DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE
    ( job_name             => 'SYS.POB_TB_JOB'
     ,argument_position    => 2
     ,argument_value       => 'N');

  SYS.DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE
    ( job_name             => 'SYS.POB_TB_JOB'
     ,argument_position    => 3
     ,argument_value       => 'N');

  SYS.DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE
    ( job_name             => 'SYS.POB_TB_JOB'
     ,argument_position    => 4
     ,argument_value       => '1');

  SYS.DBMS_SCHEDULER.ENABLE
    (name                  => 'SYS.POB_TB_JOB');
END;
/

Attribute MAX_RUN_DURATION does not terminate the job, it only raises an event.

I created a procedure like this:

procedure SchedulerWatchdog(jobName in varchar2) is

cursor Jobs is
select job_name
from user_scheduler_jobs
where JOB_NAME = jobName
   AND STATE = 'RUNNING'
   AND systimestamp - LAST_START_DATE > MAX_RUN_DURATION;

begin
   for aJob in Jobs loop
      DBMS_SCHEDULER.STOP_JOB(JobName, FORCE => TRUE);
   end loop;
end;

Run this procedure by scheduler job, perhaps every Minute.


building structure for employee office relation I have a table defined like this

  empid   fromdate        todate       office_ID
    1     2012-03-01      2014-04-2      1
    1     2014-04-03      ----           2
    2     2012-03-01      2014-04-2      2
    3     2012-03-01      2014-04-2      1

If the employee is currently working at some office then todate will be not known hence it is null. This logic can be changed if you suggest something better.

From this table I want to fetch an employee's location(s) for a given month. For example, for May 2005 where was employee 1? For that I have designed this query

select *
from tbl_emp_office
where (year(From_date)*100 + month(From_date)) < '201505'

This structure is not giving the exact solution to my problem. Can you suggest more suitable structure for this?

EDIT
fromdate has data type Date.

What "datatype" is From_date?  Assuming it is DATE or DATETIME or TIMESTAMP, then this will find records before May, 2015:

select * from tbl_emp_office where From_date < '2015-05-01';

This will find records for only May, 2015:

select * from tbl_emp_office
    where From_date >= '2015-05-01'
      and From_date  < '2015-05-01' + INTERVAL 1 MONTH;

If you are using VARCHAR, well don't.

Edit to answer "Where was emp#1 working during May, 2015?"

SELECT  office_id
    FROM  tbl_emp_office
    WHERE  empid = 1
      AND  From_date < '2015-05-01' + INTERVAL 1 MONTH -- Started before/during May
      AND  ( To_date IS NULL          -- Still working there
         OR  To_date >= '2015-05-01'  -- Left during/after May
           );

Note:  If he worked in multiple offices during May, this will produce multiple rows.


Under what conditions could restore of a huge database get delayed, in SQL server 2016, hosted on VM? I have system where my database gets restored every night, and off late the restore is taking a lot longer from past few days.

Here is server config:

ES 2637 XEON @ 3.5 Ghz
Windows virtual server 2012 R2 hosted in a Hyper V environment
RAM: 128 GB
8 CPUs
Ethernet: 500 Kps Throughput
Virtual Memory

Max Size:132,096 MB
Available: 39,395 MB
In Use:   92,701 MB

Version:

  Microsoft SQL Server 2016 (SP2-CU1) (KB4135048) - 13.0.5149.0 (X64)   May 19 2018 09:41:57   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows Server 2012 R2 Standard 6.3  (Build 9600: ) (Hypervisor)

Diff : RESTORE DATABASE successfully processed 3897077 pages in 825.165 seconds (36.896 MB/sec).
Full: RESTORE DATABASE successfully processed 51085865 pages in 3174.184 seconds (125.735 MB/sec).

Additional info:

Restores were completing in 3 hours; now, they are taking anywhere from 5 - 9 hours, about 2-3 times as long.
The size of the backup files hasn't changed.
We are currently backing up to a single file.
To the best of my knowledge, there have been no changes to the system that would explain this.

Any ideas how to improve the restore speed ?

When I see someone complaining of backups "suddenly" changing, there are usually three things I look at first. In my experience, one of these three things is usually the cause.

1: Oops, there was a hardware change

Maybe a VM was v-motioned to a different host. Maybe there was a change in the storage configuration on the NAS/device that you're backing up to. Maybe there was a network configuration change.

Pinpoint the date that backup performance changed, and work with your server/hardware team to figure out if anything changed here.

2: Change in DB size

This one is usually pretty easy to find. If you're using database monitoring software of some sort, you should be able to go back and look at the historical data, and see that there was a significant change in the database size. (Full disclosure, I work for SentryOne, who makes database monitoring software.)

If you don't have monitoring data, you can query msdb on the source server to look at the database backup size over time, and see if backup growth correlates to the time you noticed RESTORE scripts slowing down:

SELECT database_name, backup_finish_date, backup_size
FROM msdb.dbo.backupset
WHERE [type] = 'd'
AND database_name = 'AdventureWorks2014' --Your database name here
ORDER BY backup_finish_date;

3: Change in transaction log number of VLFs

If it's not the first two, it's probably this one.
SQL Server breaks up the transaction log into "Virtual Log Files" -- these are just logical buckets inside your .ldf file. On restore, SQL Server processes each of these VLFs in sequence, which means more VLFs can slow down your recovery.  A high VLF count is bad.

Here's how you can reproduce the problem using the AdventureWorks demo database. I restore the clean database, and test backup/restore. Then increase the VLF count and repeat the same backup/restore:

--
-- RESTORE AdventureWorks from a pristine copy from Github
--      https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks
--
USE master;
RESTORE DATABASE AdventureWorks2014
FROM DISK = 'C:\SQL\AdventureWorks2014.bak'
WITH MOVE 'AdventureWorks2014_Data' TO 'C:\SQL\AdventureWorks2014_Data.mdf'
    ,MOVE 'AdventureWorks2014_Log' TO 'C:\SQL\AdventureWorks2014_Log.ldf'
    ,REPLACE;
GO
--
-- Now do a backup & restore.
--
PRINT '----- BACKUP  1 -----';
BACKUP DATABASE AdventureWorks2014 TO DISK = 'C:\temp\ADVW2014.bak'     WITH INIT, COMPRESSION;
PRINT '----- RESTORE 1 -----';
RESTORE DATABASE AdventureWorks2014_RestoreTest FROM DISK = 'C:\temp\ADVW2014.bak'      WITH REPLACE, NORECOVERY
        , MOVE 'AdventureWorks2014_Data' TO 'C:\SQL\AdventureWorks2014_RestoreTest_Data.mdf'
        , MOVE 'AdventureWorks2014_Log' TO 'C:\SQL\AdventureWorks2014_RestoreTest_Log.ldf' ;
GO
--
-- Lets really jack up the VLFs on this DB. Grow log file to 2GB in 1MB increments.
--
GO
USE AdventureWorks2014
DBCC SHRINKFILE (N'AdventureWorks2014_Log' , 1)
GO

DECLARE @i int = 51;
DECLARE @sql nvarchar(max);
WHILE @i <= 2000
BEGIN
    SET @sql = N'ALTER DATABASE [AdventureWorks2014] MODIFY FILE ( NAME = N''AdventureWorks2014_Log'', SIZE = ' + CONVERT(nchar(4),@i) + N'MB );';
    EXEC sp_executesql @sql;
    SET @i +=1;
END;
DBCC LOGINFO;
GO
--
-- Now do a backup & restore.
--
PRINT '----- BACKUP  2 -----';
BACKUP DATABASE AdventureWorks2014 TO DISK = 'C:\temp\ADVW2014.bak'     WITH INIT, COMPRESSION;
PRINT '----- RESTORE 2 -----';
RESTORE DATABASE AdventureWorks2014_RestoreTest FROM DISK = 'C:\temp\ADVW2014.bak'      WITH REPLACE, NORECOVERY
        , MOVE 'AdventureWorks2014_Data' TO 'C:\SQL\AdventureWorks2014_RestoreTest_Data.mdf'
        , MOVE 'AdventureWorks2014_Log' TO 'C:\SQL\AdventureWorks2014_RestoreTest_Log.ldf' ;
GO

The first backup/restore will generate output like this. Notice the speeds on backup (299.826 MB/sec) and restore (403.587 MB/sec):

----- BACKUP  1 -----
Processed 24328 pages for database 'AdventureWorks2014', file 'AdventureWorks2014_Data' on file 1.
Processed 4 pages for database 'AdventureWorks2014', file 'AdventureWorks2014_Log' on file 1.
BACKUP DATABASE successfully processed 24332 pages in 0.634 seconds (299.826 MB/sec).
----- RESTORE 1 -----
Processed 24328 pages for database 'AdventureWorks2014_RestoreTest', file 'AdventureWorks2014_Data' on file 1.
Processed 4 pages for database 'AdventureWorks2014_RestoreTest', file 'AdventureWorks2014_Log' on file 1.
RESTORE DATABASE successfully processed 24332 pages in 0.471 seconds (403.587 MB/sec).

The second backup/restore will generate output like this. Notice the speeds on backup (144.220 MB/sec) and restore (89.114 MB/sec):

----- BACKUP  2 -----
Processed 24328 pages for database 'AdventureWorks2014', file 'AdventureWorks2014_Data' on file 1.
Processed 3 pages for database 'AdventureWorks2014', file 'AdventureWorks2014_Log' on file 1.
BACKUP DATABASE successfully processed 24331 pages in 1.318 seconds (144.220 MB/sec).
----- RESTORE 2 -----
Processed 24328 pages for database 'AdventureWorks2014_RestoreTest', file 'AdventureWorks2014_Data' on file 1.
Processed 3 pages for database 'AdventureWorks2014_RestoreTest', file 'AdventureWorks2014_Log' on file 1.
RESTORE DATABASE successfully processed 24331 pages in 2.133 seconds (89.114 MB/sec).

To fix this, you essentially need to shrink & re-grow your log file in more appropriate chunks. You can see more information on this here(my employer's site), here, here, and here.


Randomly failing SQL Server 2008R2 used by Access 2010 front-end via ODBC From time to time (sometimes days apart, sometimes several times a day) users of a multi-user Access front-end, which has ODBC connections to a SQL Server back-end, see a variety of problems. Most users have Access Runtime, but a few use full Access. At most 15 users will be active at any one time, and their activity is mostly retrieve/amend/save, working on one record (and associated sub-records) at a time, usually for a minute or two.

The problems can be any of:

an Access form has #Error in all its bound fields.
an Access form has #Name? in all its bound fields.
an Access form has #Field! in all its bound fields.
front-end error handler reports "[ODBC SQL Server Driver][DBNETLIB]General network error
front-end error handler reports "[ODBC SQL Server Driver][DBNETLIB]Communication link failure

Sometimes (particularly with the first 3 types) the errors can be eliminated for a while by restarting the instance.
I'm not a DBA, but I am the closest that the company has.
Any suggestions for possible solutions, or at least some informed diagnostic tactics?
TIA.

As Max says it sounds similar to a problem we had under a similar situation, especially for those working remote to the main company network.

Ensure that under the Access database Options Advanced tab that the OLE/DDE timeout is set to something like 300 and not the default of 30.

Also in any VBA requests to the database adjust the ADODB command.CommandTimeout value to a high value of at least the same 300, this will stop early timeouts and lost connections. Since we did this we have had no dropouts, it only uses the extra time as needed so do not worry about system locks from this.


MariaDB - MySQL - ALL-DB Import - Table 'user' already exists I am attempting to migrate from local MariaDB to Docker version which should in essence be as simple as migrating to a new SQL Server. I have setup the Docker container fine via but can't seem to import my "all-databases" dump.

This is what I get:

mysql -u root -p  < mariadb_alldb_*.sql
Enter password:
ERROR 1050 (42S01) at line 8022: Table 'user' already exists

Dump generated via :

mysqldump -u root -p --all-databases --skip-lock-tables > mariadb_alldb_"$(date '+%F')".sql

Update: This is run on a fresh docker container each time and I have created backups in a directory that I am importing from.

ls * | grep mariadb_alldb_
mariadb_alldb_2020-05-04.sql

Update2: Perhaps it's related to my docker setup?

Here is my docker cmd:

docker stop mariadb && docker rm mariadb
docker run -d --name="mariadb" \
-p 3306:3306 \
-e TZ="America/Whitehorse" \
-v "/opt/mariadb/conf/conf.d":"/etc/mysql/conf.d" \
-v "/opt/mariadb/backups":"/mnt/" \
--mount type=volume,dst=/var/run/mysqld,volume-driver=local,volume-opt=type=none,volume-opt=o=bind,volume-opt=device=/var/run/mysqld \
mariadb:latest

I am doing this import from the local machine, I have reproduced results from inside the container.

As someone already mentioned, this has to do with the mysql.user table being changed to a view in 10.4. The problem and solution is documented on the MariaDB website in MDEV-22127.
The solution is to simply add the following two lines to the top of your all-dbs.sql dump file:
DROP TABLE IF EXISTS `mysql`.`global_priv`;
DROP VIEW IF EXISTS `mysql`.`user`;


Duplicate key violates unique constraint When I use this query everything is fine:

UPDATE users
   SET f_name='Mike',
       l_name='MyLastName',
       username='blabla',
       "password"='asdfsdaf',
       "class"=12,
       lang='en'
 WHERE id=50;

I created a view which combines two tables:

CREATE OR REPLACE VIEW students_data AS
SELECT students.id,
       users.f_name,
       users.l_name,
       users.username,
       users."password",
       users."class",
       users.permission,
       users.sessionid,
       users.lastlogin,
       users.lang
  FROM students
  LEFT JOIN users ON students.id = users.id;

And I also created this rule:

CREATE OR REPLACE RULE students_data_update AS
    ON UPDATE TO students_data DO INSTEAD
       UPDATE users
          SET f_name = new.f_name,
              l_name = new.l_name,
              username = new.username,
              "password" = new."password",
              "class" = new."class",
              permission = new.permission,
              sessionid = new.sessionid,
              lastlogin = new.lastlogin,
              lang = new.lang;

When I execute this query I get an error:

UPDATE students_data
   SET f_name='Mikaa',
       l_name='MikeL',
       username='blabla',
       "password"='asdfsdaf',
       "class"=12,
       lang='en'
 WHERE id=50;

  ERROR: duplicate key violates unique constraint "username" SQL
  status:23505

I have no idea why I get this error, username is a unique column but I shouldn't give any problems when I update this column.

-- Table: users

-- DROP TABLE users;

CREATE TABLE users
(
  id serial NOT NULL,
  f_name character varying,
  l_name character varying,
  username character varying NOT NULL,
  "password" character varying NOT NULL,
  "class" integer NOT NULL,
  permission integer NOT NULL,
  sessionid character varying,
  lastlogin integer,
  lang character varying(5),
  CONSTRAINT users_pkey PRIMARY KEY (id),
  CONSTRAINT "class" FOREIGN KEY ("class")
      REFERENCES "class" (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT username UNIQUE (username)
)
WITHOUT OIDS;

Your rule does not have a WHERE clause and any update is trying to modify the whole users table.

You can/should add WHERE id = old.id:

CREATE OR REPLACE RULE students_data_update AS
    ON UPDATE TO students_data DO INSTEAD
UPDATE users
SET f_name = new.f_name,
    l_name = new.l_name,
    --- ...
WHERE id = old.id ;               --- added

Note: I had never used the RULE system before in Postgres. But the above suggestion worked when tested at SQL-Fiddle.


How to automate insertion of new column in Mysql table? I have a legacy database, and several tables need a new column added that will act as the primary key for the table.  The trick is there are several tens of thousands of existing rows, and the data for the new column will be the concatenation of two field values within the table.

Specifically, the new column can be temporarily created via

SELECT *, CONCAT(ContractNum, '-', PlanId) AS ConPlanId FROM ContractPlans

Using a batch script, how can I populate the exising column fields which are currently null?

primary key...will be the concatenation of two field values within the table.

no NO NO!!!!!

This breaks all the rules of database normalization (well, rule 1 and rule 2 actually) and it's totally unnecessary! It uses more storage, makes your queries less efficient, and may cause bugs in your processing.

Use the existing columns as the primary key.

Presumably it doesn't have a primary key declared already.

Check that you've got no duplicates in the table:

SELECT ContractNum, PlanId, COUNT(*)
FROM ContractPlans
GROUP BY ContractNum, PlanId
HAVING COUNT(*)>1;

Fix the data if you need to, then

ALTER TABLE ContractPlans
ADD PRIMARY KEY (ContractNum, PlanId);


SQL: Pivot + Group by in one go I wish to pivot INFO2 into 2 columns.
INFO3 data should adjusted accordingly after the PIVOT.

The challenge is I also have a date column, which I want to take the MAX of.
The MAX(Date) should also be grouped by (lot_no, mcid).

Is there a way of doing this in one fell swoop?
Without resorting to temporary tables?

You can easily do that without using PIVOT. Your data:

with data as
(
  select 1 as lot_no, 1 as mcid, 1 as info2, 111 as info3, date'2015-01-01' as dat from dual union all
  select 1, 1, 2, 222, date'2015-01-02' from dual union all
  select 2, 2, 1, 333, date'2015-01-03' from dual union all
  select 2, 2, 2, 444, date'2015-01-04' from dual
)
select * from data;

    LOT_NO       MCID      INFO2      INFO3 DAT
---------- ---------- ---------- ---------- -------------------
         1          1          1        111 2015-01-01 00:00:00
         1          1          2        222 2015-01-02 00:00:00
         2          2          1        333 2015-01-03 00:00:00
         2          2          2        444 2015-01-04 00:00:00

Now the result:

with data as
(
  select 1 as lot_no, 1 as mcid, 1 as info2, 111 as info3, date'2015-01-01' as dat from dual union all
  select 1, 1, 2, 222, date'2015-01-02' from dual union all
  select 2, 2, 1, 333, date'2015-01-03' from dual union all
  select 2, 2, 2, 444, date'2015-01-04' from dual
)
select
  lot_no,
  mcid,
  max(decode(info2, 1, info3, null)) as info21,
  max(decode(info2, 2, info3, null)) as info22,
  max(dat)
from
  data
group by
  lot_no,
  mcid
;

    LOT_NO       MCID     INFO21     INFO22 MAX(DAT)
---------- ---------- ---------- ---------- -------------------
         1          1        111        222 2015-01-02 00:00:00
         2          2        333        444 2015-01-04 00:00:00

This is simply a GROUP BY and picking the need value for INFO21 and INFO22 by transforming unneeded values into NULL, then keeping the needed value with MAX (because we need to use an aggregate function here).

(Section below added by question originator)

While trying to understand the above answer, I found it useful to study the query below as an intermediate step.

with data as
(
  select 1 as lot_no, 1 as mcid, 1 as info2, 111 as info3, date'2015-01-01' as dat from dual union all
  select 1, 1 , 2, 222, date'2015-01-02' from dual union all
  select 2, 2 , 1, 333, date'2015-01-03' from dual union all
  select 2, 2 , 2, 444, date'2015-01-04' from dual
)
select
  lot_no,
  mcid,
  decode(info2, 1, info3, null) as info21,
  decode(info2, 2, info3, null) as info22,
  --max(dat)
  dat
from
  data
;

This gives,

LOT_NO  MCID    INFO21  INFO22  DAT
1   1   111     1 Jan 15
1   1       222 2 Jan 15
2   2   333     3 Jan 15
2   2       444 4 Jan 15

This is before the "group-by" used in the answer above.

It's also useful that null values are automatically excluded, by ORACLE aggregate functions like MAX. Makes it easy to ignore them when merging rows.


MySQL select with join table I have 3 tables, one of them is join table:

user table:

CREATE TABLE `user` (
`id`,
`name`)

media table:

CREATE TABLE `media` (
`id`,
`value`,
`type` enum('M', 'T'))

user_media table:

CREATE TABLE `user_media` (
`user_id`,
`media_id`)

I want to create a select which lets me search through user.name and media.value and return formated values.
Ex:

INSERT INTO `user` (`id`, `name`) VALUES
(1, 'Luke'),
(2, 'Vader'),
(3, 'Gregory');

INSERT INTO `media` (`id`, `value`, 'type') VALUES
(1, 'luke@mail.com', 'M'),
(2, 'skywalker@mail.com', 'M'),
(3, '123456789', 'T'),
(4, '123', 'T');

INSERT INTO `user_media` (`user_id`, `media_id`) VALUES
(1, 1),
(1, 2),
(1, 3),
(2, 4);

search: luke@mail.com
output:
userId    |email          |tel
1         |luke@mail.com  |123456789

search: 123
output:
userId    |email          |tel
1         |luke@mail.com  |123456789
2         |null           |123

search: Gregory
output:
userId    |email          |tel
3         |null           |null

UPDATE

SELECT u.id AS user_id,
   mm.value AS mail,
   mt.value AS tel
FROM USER u
LEFT JOIN user_media umt ON u.id = umt.user_id
LEFT JOIN media mt ON mt.id = umt.media_id
LEFT JOIN user_media umm ON u.id = umm.user_id
LEFT JOIN media mm ON mm.id = umm.media_id
WHERE mm.value LIKE "%luke@mail.com%"
  OR mt.value LIKE "%luke@mail.com%"
  OR u.name LIKE "%luke@mail.com%"

Result:

user_id mail               tel
1       luke@mail.com      luke@mail.com
1       skywalker@mail.com  luke@mail.com
1       123456789          luke@mail.com
1       luke@mail.com      skywalker@mail.com
1       luke@mail.com      123456789

II

SELECT u.id AS user_id,
   mm.value AS mail,
   mt.value AS tel
FROM USER u
LEFT JOIN user_media umt ON u.id = umt.user_id
LEFT JOIN media mt ON mt.id = umt.media_id AND mt.type="T"
LEFT JOIN user_media umm ON u.id = umm.user_id
LEFT JOIN media mm ON mm.id = umm.media_id AND mm.type="M"
WHERE mm.value LIKE "%luke@mail.com%"
  OR mt.value LIKE "%luke@mail.com%"
  OR u.name LIKE "%luke@mail.com%"

Result:

user_id mail               tel
1       luke@mail.com      null
1       luke@mail.com      null
1       luke@mail.com      123456789

III

SELECT u.id AS user_id,
   mm.value AS mail,
   mt.value AS tel
FROM USER u
LEFT JOIN user_media umt ON u.id = umt.user_id
LEFT JOIN media mt ON mt.id = umt.media_id
LEFT JOIN user_media umm ON u.id = umm.user_id
LEFT JOIN media mm ON mm.id = umm.media_id
WHERE mt.type="T"
  AND mm.type="M"
  AND (mm.value LIKE "%Gregory%"
   OR mt.value LIKE "%Gregory%"
   OR u.name LIKE "%Gregory%")

Result: 0

You want 3 different searches and the results combined. I'd use UNION:

SELECT
  u.id     AS user_id,
  u.name   AS name,
  mm.value AS mail,
  mt.value AS tel
FROM `user` AS u
  LEFT JOIN user_media AS umt
         JOIN media AS mt ON  mt.id = umt.media_id
                          AND mt.type = 'T'
    ON u.id = umt.user_id
  LEFT JOIN user_media AS umm
         JOIN media AS mm ON  mm.id = umm.media_id
                          AND mm.type = 'M'
    ON u.id = umm.user_id
WHERE
  u.name LIKE CONCAT('%', ?, '%')

UNION ALL

SELECT
  u.id     AS user_id,
  u.name   AS name,
  mm.value AS mail,
  mt.value AS tel
FROM media AS mt
  LEFT JOIN user_media AS umt ON mt.id = umt.media_id
  LEFT JOIN `user` AS u ON u.id = umt.user_id
  LEFT JOIN user_media AS umm
        JOIN media as mm ON  mm.id = umm.media_id
                         AND mm.type = 'M'
    ON umt.user_id  = umm.user_id
WHERE
  mt.type = 'T'
  AND mt.value LIKE CONCAT('%', ?, '%')

UNION ALL

SELECT
  u.id     AS user_id,
  u.name   AS name,
  mm.value AS mail,
  mt.value AS tel
FROM media AS mm
  LEFT JOIN user_media AS umm ON mm.id = umm.media_id
  LEFT JOIN `user` AS u ON u.id = umm.user_id
  LEFT JOIN user_media AS umt
         JOIN media AS mt ON  mt.id = umt.media_id
                          AND mt.type = 'T'
    ON umm.user_id = umt.user_id
WHERE
  mm.type = 'M'
  AND mm.value LIKE CONCAT('%', ?, '%') ;

Tested at SQLfiddle. (replace the ? with the value you want to search for.)


Add '%' sign to MySQL results I am using a query to calculate total percentage but I want a '%' symbol to appear next to it. Any ideas that could help me

My query so far is

SELECT
student.StudentID,
student.`Name`,
ROUND (Count(attendance.AttendanceID)*100 / 6  ) AS Total,
student.WorkshopID
FROM
    student
LEFT JOIN attendance ON student.StudentID = attendance.StudentID
GROUP BY
    student.StudentID,
    student.`Name`

CAST() might not be needed, try with and without it.

SELECT student.StudentID
    ,student.NAME
    ,CONCAT (
        cast(ROUND(Count(attendance.AttendanceID) * 100 / 6) AS CHAR(20))
        ,'%'
        ) AS Total
    ,student.WorkshopID
FROM student
LEFT JOIN attendance ON student.StudentID = attendance.StudentID
GROUP BY student.StudentID
    ,student.Name``

The ROUND function returns integer data and CAST is needed to use the result as a string parameter of CONCAT function. CHAR(20) is really arbitrary - you can use any other string data type.


Which database is designed for storing and incrementing values? We're trying to build a stats server that stores visits for each document on a section for a specific date, so we need a table that has the following parameters:

documentID
sectionID
date
visits

The plan is then to increment the visits field.

Now, we tried to do this using MongoDB (albeit a very simple instance of it) and it turns out this is too write intensive for it. Is there another database system that's better suited for this task? Or should we just start growing the MongoDB infrastructure that we're using?

To be honest I think NoSQL would be a bad match here.  The eventual consistency traps may be an issue with very frequently updated information and this has problems here.

A better approach IMO would be to start with a standard SQL db (PostgreSQL is a good choice) and do things by logging visits and, asynchronously (separate process in the background) occasionally set counters at roll-forward points.  This avoids lock contention.

I doubt your problem is actually a matter of write intensiveness so much as it is lock contention.  What's probably happening is that you have several processes trying write to the same counter and only one can be allowed to autoritatively do this at a time.  By doing a log, aggregate, and snapshot approach you can avoid this lock contention because the high writes will occur mostly in sequential disk I/O (at least in PostgreSQL).

If I am right and the issue is lock contention I don't really see a viable alternative.  You have to have some way of incrementing a counter atomically and you don't want several concurrent attempts to be treated as a single attempt, incrementing by 1.


AG listener creation failed SQL Server I'm getting the error below while creating an AG listener in SQL Server.

  Ensure that the network adapters for dependent IP address resources have access to at least one DNS server. Alternatively, enable NetBIOS for dependent IP addresses.

The user has full privileges and it can create a computer object and a DNS entry successfully.

Please help me to fix this issue.

First, make sure the cluster's user account can create computer objects in Active Directory. See this link for the specific steps you need to do in Active Directory Users and Computers to grant the permissions. By default, the cluster does NOT have the rights it needs and the AG wizard doesn't really do anything to warn you or to grant those rights for you.


SQL session that is 'never end', always stay in a running status but does not return a result set (Until I kill it) I am a beginner SQL DBA and I experienced a weird thing in SQL server.

Symptom:

Session is in Running state (not runnable, not suspended).
CPU time is zero, or if it is not, after a while it stops growing.
Reads\Writes\Logical Reads are zero, or if is not, after a while they stop growing as well.
No result set that is returned.
They will run until I kill all these sessions. (These can be days, but these queries run within 3 mins usually)

More info:

This is static database.
Indexes are not fragmented.
Statistics are updated
CPU is not under stress, used 10% only
Memory is more than enough
SQL Server 2016 is used

Is there any idea what can be the reason for this behaviour?

I'm very grateful for all the answers.

I would check the following

is the session you normally kill, waiting on something
is the session you normally kill, being blocked
is the session you normally kill, in a transaction that someone forgot to end?

you can use the below query to check the first 2. You may be able to check the third if the query gets picked up in the last column.

SELECT [spid]
--      ,[kpid]
      ,[blocked]
      ,[open_tran]
--      ,[waittype]
      ,[waittime]
      ,[lastwaittype]
      ,[waitresource]
      ,[name]
      ,CAST([cpu] AS NUMERIC) cpu
      ,[physical_io]
      ,[loginame]
      ,[hostname]
      ,[program_name]
--      ,[hostprocess]
      ,[cmd]
--      ,[nt_domain]
--      ,[nt_username]
--      ,[net_address]
      ,[net_library]
--      ,[uid]
      ,[memusage]
      ,[login_time]
      ,[last_batch]
--      ,[ecid]
      ,[status]
--      ,[sid]
--      ,[context_info]
      ,[sql_handle]
--      ,[stmt_start]
--      ,[stmt_end]
--      ,[request_id]
        ,[text]
FROM [master].[sys].[sysprocesses] p
JOIN SYS.databases d
    ON p.dbid = d.database_id
CROSS APPLY sys.dm_exec_sql_text(p.sql_handle)
--WHERE status = 'runnable'
--WHERE name = 'runnable'
--ORDER BY blocked DESC
--ORDER BY cpu DESC
ORDER BY waittime DESC


MongoDB schema for a news service I would like to create a MongoDB database that would be very similar to how various news services store and display their data. The database would be queried by an android phone.

Let's have a look at the BBC Android app:

You can see that the news stories are broken up into their various continents, Africa, Asia with a little picture and a text description of the story next to it.

I would like to find the best schema one for this.

There are 2 scenarios of DB schema I can think of:

Scenario 1

I would store all the information used to render the view within one document in a collection called "NewsGroup". The phone would need to make one database call to get all the information and this is quick and efficient.

If the user clicks on a story, the storyId could be used to pull the story out of the "Story" collection to get further details on the story, like the actual text of the story, readers comments etc.

The disadvantage of this scenario is that the information seems to be duplicated (title and picUrl are the same in both collection "Story" and "NewsGroup") and I'm scared this might lead to redundancy.

Scenario 2

I only store the storyId in the "NewsGroup" collection. The phone needs to make 2 calls to the database, one to the "NewsGroup" collection to get the list of all storyIds per continent and then one to the "Story" collection to get the story title and picUrl so that the list can be displayed. This might cause delays in the user experiences however it would improve the integrity of the databases as there is no redundant information.

General Comment:

It would be easier if MongoDB has a join operation like SQL to combined both collection when the phone queries the database hence one single operation on the server side, but MongoDB cannot perform joins.

I can also use MapReduce to simulate a join but I'm concerned about the slow performance of MapReduce in MongoDB: https://stackoverflow.com/questions/3947889/mongodb-terrible-mapreduce-performance

Actually, you can do this with a single collection (news items) and a simple query. No need for references (continent names aren't going to change any time soon) or strange data models here:

{
  _id: new ObjectId(),
  continent: "Asia"
  title: "whatever",
  date: yesterday
},
{
  _id: new ObjectId(),
  continent: "Africa",
  title: "other title",
  date: today
}

The according query would be

db.news.find({}).sort({ "continent": 1, "date": -1 })

In order to speed this query up, you should create an according index

db.news.createIndex({ "continent": 1, "date": -1 })

With that approach, you can easily do things like "For each continent, count the number of news for last week":

var date = new Date()
var last_week = date.setDate( date.getDate() - 7 );
var result = db.news.aggregate([
  { $match:{ "date":{ $gte: last_week } } },
  { $group:{ "_id": "$continent", "newsitems":{ $sum: 1 } } }
])


Set values conditionally in Postgres UPSERT We have an UPSERT feature achieved through on conflict do set. How can I set the values conditionally in the set statement, like

if(excluded.col1 is null) col1=table.col1 else col1=excluded.col1

We are using Postgres 9.5.

...
SET   col1 = COALESCE(excluded.col1, table.col1)
...

Of course, both tables must be visible.

If you are updating table1 and that's the only change you make it's much more efficient to use a WHERE clause:

...
SET   col1 = excluded.col1
WHERE excluded.col1 IS NOT NULL
AND   excluded.col1 IS DISTINCT FROM table1.col1 -- to avoid empty updates
...

About the added second condition:

How do I (or can I) SELECT DISTINCT on multiple columns?


dbatools, DbcCheck LastBackup Last week I started testing dbccheck on my test system. I found one strange thing, and I have no idea what is the problem, last fullbackup is few hours old, but dbccheck print an error.

Following you can see my steps:

installation dba checks

based on the blog from chrissy lemaire (thank you chrissy,for that great job)

change configuration

Set-DbcConfig -Name policy.backup.fullmaxdays   3
Set-DbcConfig -Name policy.backup.logmaxminutes 720

first check

$server = "ALET21"
Invoke-DbcCheck -SqlInstance $server -Check LastBackup

here the result:

result DbcCheck

The dbccheck print an error

I did the same test with PS Command, following you can see the results:

result PS command

any idea what is the problem?

AdminDB DB is not new, creation date was on 23.11.2016. I also updated the policy.backup.newdbgraceperiod to 1, but I have the same problem, as you can see below:

It looks like the dbacheck module is up to date:

I am going to assume that this database (AdminDB) is newly created and newly backed up?

There was an issue created for this as new databases were failing these tests.
https://github.com/sqlcollaborative/dbachecks/issues/207

A new configuration was created "policy.backup.newdbgraceperiod" (defaulting to value 0 for 0 hours) that excludes new databases created.
For example, it defaults to 0 so it includes all new databases e.g. (Get-Date).AddHours( - (policy.backup.newdbgraceperiod) )

As you can see, the new database QuickBackup fails.

Now when we allow a grace period of 1 hour for new databases:

Set-DbcConfig -Name policy.backup.newdbgraceperiod -Value 1

Our new database is excluded now:


Can a PostgreSQL Trigger be used to ignore an insert? I've got a situation where there are dozens of various triggers in our database that trigger a record being inserted into another (single) table within the database.

I was hoping that while testing our replacement server, that there was some easy way that I could set up a trigger on that single table to effectively ignore all of the other triggers.  (rather than haing to remove dozens of triggers and then re-enable them when we go into production).

I was hoping to be able to use an INSTEAD OF INSERT trigger set to a function that did nothing, but you can only use those on views, not tables.  I tried a BEFORE INSERT  trigger that called RAISE EXCEPTION, but that results in the whole transaction failing, so the modifications to the other tables get rejected as well.

Is there some easy way to ignore inserts to that table, or should I just do an AFTER INSERT that clears the record out afterwards?

Yes, use a BEFORE INSERT trigger, and return null.

Live example here.

create table stuff (
  stuff_id int primary key,
  thing text
);

create or replace function stuff_inserting() returns trigger language plpgsql as $$
begin

  return null;

end $$;

insert into stuff values (1, 'asdf');

select * from stuff; /* returns 1 row */

create trigger inserting before insert on stuff for each row execute procedure stuff_inserting();

insert into stuff values (2, 'fdsa');

select * from stuff; /* still returns only 1 row */


How to get host server CPU info from SSMS Help please!
I need to get number of CPU's and cores per cpu for each host server running SQL Server. Querying master.sys.dm_os_sys_info is not producing the same result as what is seen when checked on the host server.

kindly point me in the direction of a code I can use please.

What is the difference between physical_cpu and logical_cpu and which one do I need? I'm trying to calculate total cores on each machine for licensing purposes.

Can i take the number of cores returned by the query pasted below as what I need to calculate my license requirement?

In performance monitor on the server, I have sockets =2, Cores = 16, logical processors = 32. Is 16 cores what I need for my SQL 2012 and 2016 licence calculations?

Here the query that  I'm using right now:
USE master;

SET NOCOUNT ON

SELECT 'physical_cpu' = cpu_count / hyperthread_ratio
    ,'cores' = CASE
        WHEN hyperthread_ratio = cpu_count
            THEN cpu_count
        ELSE (cpu_count / hyperthread_ratio) * ((cpu_count - hyperthread_ratio) / (cpu_count / hyperthread_ratio))
        END
    ,'logical_cpu' = CASE
        WHEN hyperthread_ratio = cpu_count
            THEN cpu_count
        ELSE ((cpu_count - hyperthread_ratio) / (cpu_count / hyperthread_ratio))
        END
FROM master.sys.dm_os_sys_info

Anything better than that?

I found this query here:
----------------------------------------------------------------------------------------------------------------
-- CPU VISIABLE ONLINE CHECK
----------------------------------------------------------------------------------------------------------------
DECLARE @OnlineCpuCount int
DECLARE @HiddenCpuCount int
DECLARE @LogicalCpuCount int

SELECT @OnlineCpuCount = COUNT(*) FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE'
SELECT @HiddenCpuCount = COUNT(*) FROM sys.dm_os_schedulers WHERE status != 'VISIBLE ONLINE'
SELECT @LogicalCpuCount = cpu_count FROM sys.dm_os_sys_info

SELECT @LogicalCpuCount AS 'ASSIGNED ONLINE CPU #', @OnlineCpuCount AS 'VISIBLE ONLINE CPU #', @HiddenCpuCount AS 'HIDDEN ONLINE CPU #',
   CASE
     WHEN @OnlineCpuCount < @LogicalCpuCount
     THEN 'You are not using all CPU assigned to O/S! If it is VM, review your VM configuration to make sure you are not maxout Socket'
     ELSE 'You are using all CPUs assigned to O/S. GOOD!'
   END as 'CPU Usage Desc'
----------------------------------------------------------------------------------------------------------------
GO

And if you want you can also check each CPU one by one with this:
SELECT scheduler_id,
       cpu_id,
       STATUS,
       is_online
FROM sys.dm_os_schedulers;
GO

Now, if you are trying to calculate the licensing you can use several websites:

MS SQL 2019 Std Licensing Calculator (unofficial)
MS SQL 2019 Ent Licensing Calculator (unofficial)
https://www.dxc.technology/SLMS/flxwd/148445-dxc_sql_calculator
https://www.sqlgovernor.com/en/calculator/

I hope this helped.


Error at line 1, column 25: ORA-00923: FROM keyword not found where expected I have some code that works in Access but now I want to forward the code to our IT department to make a view in our SQL database.

Could anyone please help me convert it so it is correct for normal SQL and not Access SQL if that makes sense?

I have tried to test it in ORACLE SQL Developer but am getting the error:

  Error at line 1, column 25: ORA-00923: FROM keyword not found where
  expected

SELECT T1.*,
       (SELECT TOP 1 T3.[SMN_DATEC]-1
        FROM
            (SELECT T2.[DET_NUMBERA], T2.[SMN_DATEC]
             FROM [CHRISCS_EMSAL] AS T2
             ORDER BY [DET_NUMBERA],[SMN_DATEC]
            ) AS T3
        WHERE T3.[DET_NUMBERA] = T1.[DET_NUMBERA]
          AND T3.[SMN_DATEC] > T1.[SMN_DATEC]
       ) AS EndDate,
       T1.[DET_NUMBERA],
       T1.[SMN_DATEC]
FROM CHRISCS_EMSAL AS T1
ORDER BY T1.[DET_NUMBERA],
         T1.[SMN_DATEC];

As mentioned in the comments, Oracle doesn't like the square brackets around identifiers, and it also doesn't support TOP n clauses, however, you can work around that with a couple of simple changes:

SELECT T1.*,
   (SELECT T3.SMN_DATEC-1
    FROM
        (SELECT T2.DET_NUMBERA
              , T2.SMN_DATEC
              , row_number() over (ORDER BY DET_NUMBERA,SMN_DATEC) AS top
         FROM CHRISCS_EMSAL T2
        ) T3
    WHERE T3.TOP=1
      AND T3.DET_NUMBERA = T1.DET_NUMBERA
      AND T3.SMN_DATEC > T1.SMN_DATEC
   ) AS EndDate,
   T1.DET_NUMBERA,
   T1.SMN_DATEC
FROM CHRISCS_EMSAL T1
ORDER BY T1.DET_NUMBERA,
         T1.SMN_DATEC;

First off remove the square brackets, then move your ORDER BY clause in T3 to a ROW_NUMBER analytic function aliased as TOP, then in the outer query of your scalar valued subquery (aliased as EndDate) change the TOP 1 clause in the select statement to a TOP=1 predicate in the where clause.

Also remove the AS from table aliases, Oracle doesn't allow it (but it's OK in column aliases.)

On closer inspection of your code it looks like your scalar valued subquery is just getting the next chronological DET_NUMERA's SMN_DATEC and subtracting 1 day from it.  That's something really easy to do with Oracle analytic functions without all the scalar valued subqueries:

SELECT T1.*
     , LEAD(T1.SMN_DATEC-1) OVER (PARTITION BY T1.DET_NUMBERA
                                      ORDER BY T1.SMN_DATEC) EndDate
     , T1.DET_NUMBERA
     , T1.SMN_DATEC
  FROM CHRISCS_EMSAL T1
 ORDER BY T1.DET_NUMBERA,
       T1.SMN_DATEC;


mysqldump - select table in database only if it contains a specific string (I'm new to Database Administrators, and am not a Database Administrator. However I originally asked this question on Stack Overflow (here) but didn't get any answers. Sorry if this is not the right site to ask this question, but I really need an answer).

I have a pretty big local WordPress Multisite installation, and this is mirrored on a  live server, so I can develop a site locally and upload it for a client to see (it's sub-domain based). I have written a custom shell script to upload the database from my local machine to the live server, which uses sed to change all instances of the local domain name to the live domain name. It then commits and pushes it by git and the live server automatically pulls it, takes a backup and applies the new file.

However, this has become problematic in the last month or so. As I've got more client work, and they have been editing their sites live on the server, and I've been pushing new work to the server for other clients to take a look at, I've been overwriting changes on the live database.

What I need is to be able to add a flag or something when I call the shell script file (sh push.sh) that indicates what tables in the database need to be pushed live. Each site in the WordPress Multisite database has a number, e.g network_17_posts. So if I could a table number state when I called my shell script, like sh push.sh --table=17 and it would only upload site 17's data, and not overwrite anything else, that would be awesome. As a bonus, if I could specify multiple numbers, so I could upload multiple sites at a time, that would be amazing!

As a reference, here is my current shell script for pushing the database live (it could probably be 10x better, but I'm primarily a front-end/PHP dev, not shell script!):

rm -rf db_sync.sql
mysqldump -u root -ppassword db_name > db_sync.sql
sed 's/localdomain.dev/livedomain.com/g' db_sync.sql > new_db_sync.sql
rm -rf db_sync.sql
mv new_db_sync.sql db_sync.sql
git add db_sync.sql
read -p "Enter Commit Message: " commit_message
git commit -m "$commit_message"
git push -u web master

Thanks for any help :)

What you need is a tool for controlling migrations and upload them to git a.k.a. a version control for MySQL. While you can deploy your own, this is a very well known problem and there are many tools available, some of them free and open source. I am not going to recommend you anything in particular, as they are different on the goals/features/integration with frameworks, but here it is a list of options:

Liquibase
Flyway
c5-db-migration
dbdeploy
MyBatis
Autopatch
Sqitch
online-migration

Some recommended readings: http://en.wikipedia.org/wiki/Schema_migration, http://www.liquibase.org/quickstart and http://www.percona.com/live/mysql-conference-2013/sites/default/files/slides/PLMCE13_online-migration.pdf

If you still want to deploy your own tool (not recommended), have a look at least at mysqldbcompare from mysqlutils and some of the percona-toolkit tools.


The problem having different vms with the same sql server Licence key I was hired in a new company 1 year ago. After doing all important stuff, i'm now hunting for problems and etc.
Using a powershell that can give me the licence key using to install the SQL Server, I could find that we have 5 different vms, using the same SQL Server 2012 with the same licence key.
probably the old people here just used the same EXE and the same KEY to install all sql servers in all vms created because they didn't know about it ( just clicking next next next next)
is there a way to fix this and is it possible to edit to elicence key to use a valid one ( a diferent one for each instance on each vm?

That's not a "License Key" it's an "Activation Key", which simply controls the Edition of SQL Server that is running.  Its only relationship to licensing is that it may help you determine what licenses need to be assigned to the server.
Licenses are assigned to servers using external bookkeeping, like in a Software Asset Management Solution or an Excel Spreadsheet.
For instance, SQL Server on Docker Containers and the Visual Studio Subscription Downloads all come with the key already embedded in the installer.  Both of these can be used for production deployments by simply assigning the appropriate licenses to the servers without changing the key.