Creating Sort Index takes 96% of query time even without any ORDER BY I have this query:

SELECT itr.recipe_id,
   SUM(itr.weight),
   SUM(aprice_weight),
   SUM(itr.weight+aprice_weight) AS score
FROM
  (SELECT itfin.id AS flyer_item_id,
          itfin.flyer_id,
          MAX(itfin.max_weight) AS aprice_weight,
          itfin.ingredient_id
   FROM
     (SELECT MAX(price_weight) AS max_weight,
             flyer_items.id,
             flyer_items.flyer_id,
             ingredient_to_flyer_item.ingredient_id
      FROM flyer_items
      JOIN ingredient_to_flyer_item ON flyer_items.id = ingredient_to_flyer_item.flyer_item_id
      WHERE flyer_items.flyer_id IN (2)
      GROUP BY ingredient_to_flyer_item.flyer_item_id) AS itfin
   JOIN ingredient_to_flyer_item ON itfin.id = ingredient_to_flyer_item.flyer_item_id
   GROUP BY itfin.ingredient_id) AS itf
INNER JOIN `ingredient_to_recipe` AS `itr` ON `itf`.`ingredient_id` = `itr`.`ingredient_id`
GROUP BY `itr`.`recipe_id`
ORDER BY `score` DESC
LIMIT 12

But even without the ORDER BY clause, execution time is still the same, around 350ms. I tried looking at the indexes to see what I can do, but nothing worked so far. Not even putting a bigger value for SORT_BUFFER_SIZE in MySQL settings.

The profiler tells me that a single Create Sort Index (out of 3) takes 96% of the query time.

Here is the EXPLAIN result:

| id | select_type | table                    | partitions | type   | possible_keys           | key           | key_len | ref                      | rows | filtered | extra                                        |
|----|-------------|--------------------------|------------|--------|-------------------------|---------------|---------|--------------------------|------|----------|----------------------------------------------|
| 1  | PRIMARY     | <derived2>               | NULL       | ALL    | NULL                    | NULL          | NULL    | NULL                     | 583  | 100.00   | Using temporary; Using filesort              |
| 1  | PRIMARY     | itr                      | NULL       | ref    | recipe_id,ingredient_id | ingredient_id | 4       | itf.ingredient_id        | 35   | 100.00   | NULL                                         |
| 1  | PRIMARY     | r                        | NULL       | eq_ref | id                      | id            | 4       | metadata2.itr.recipe_id  | 1    | 10.00    | Using where                                  |
| 2  | DERIVED     | <derived3>               | NULL       | ALL    | NULL                    | NULL          | NULL    | NULL                     | 246  | 100.00   | Using temporary; Using filesort              |
| 2  | DERIVED     | ingredient_to_flyer_item | NULL       | ref    | flyer_item_id           | flyer_item_id | 4       | itfin.id                 | 2    | 100.00   | Using index                                  |
| 3  | DERIVED     | flyer_items              | NULL       | ALL    | id_2,id,flyer_id        | NULL          | NULL    | NULL                     | 104  | 100.00   | Using where; Using temporary; Using filesort |
| 3  | DERIVED     | ingredient_to_flyer_item | NULL       | ref    | flyer_item_id           | flyer_item_id | 4       | metadata2.flyer_items.id | 2    | 100.00   | NULL                                         |

Any help to speed this query up would be so appreciated. Thank you!

UPDATE

Link to .sql demo tables: https://nofile.io/f/F4YSEu8DWmT/meta.zip

Each row in the EXPLAIN's output having NULL in the 'key' column should be investigated. Those row marked as (derivedN) aren't the culprits but rather the victims. In your case flyer_items table need the indexes: (id), (flyer_id), (id, flyer_id) and (flyer_id, id). I can't predict which one of the last two will be choosed by optimizer.

Then derived tables produced by JOINs will get the suitable inherited indexes for further proceeding.


Data modeling for employee has multiple managers I've referred this link for modelling the table for employee-manager relationship. However, in my use case, an employer can have multiple managers. I am wondering what would be the best solution for this. Any suggestions for modelling this use case?

That sounds like a many to many relationship where you need a bridge table. You would store everyone in an employee table with an ID and then have a separate hierarchy table with manager and employee combinations.


Postgres pg_basebackup reports directory exists but is not empty I have postgreSQL 9.4.1 installed on a Win 2012 Server.

I am looking to setup replication between this and another server of the same spec. So I am following guidance on preparing the master database server for standby servers as per warm standby section 25.2.3 This involves using pg_basebackup.

The PostgreSQL instance on the master contains a database for one of our apps. This database has 3 schemas each of which have their own dedicated tablespace. On windows the tablespace locations were created by using the MKLINK command (as I believe the LOCATION parameter has to point to a symbolic link on windows) so - for example, :

mklink /j C:\tbs1 D:\app\tablespace\tbs1
mklink /j C:\tbs2 D:\app\tablespace\tbs2

So, using the above as an example a tablespace in postgreSQL was created using the following command:

CREATE TABLESPACE app_ops OWNER the_user LOCATION 'C:\tbs1';

Which is all fine. However when I wish to use the pg_basebackup tool, i received the following error/warning - with nothing being written to the backup location:

pg_basebackup -h localhost -U postgres -D C:\master_backup
pg_basebackup: directory "C:\tbs1" exists but is not empty

I don't understand why I am receiving this specific message.

The main point in many of the answers I have seen talks about ensuring that the tablespaces didn't exist within PG_DATA. However they don't in my case  - PG_DATA is set in the executable path of the windows service as -D C:\Program Files\PostgreSQL\9.4\data, as can been seen:

"C:\Program Files\PostgreSQL\9.4\bin\pg_ctl.exe" runservice -N "postgresql-x64-9.4" -D "C:\Program Files\PostgreSQL\9.4\data" -w

I had looked at the tablespace-mapping parameter 'old=new', but this didn't change the result.

I appreciate that there are similar questions and guidance, however I do not seem to be able to find anything additional that may help. I have looked on dba/stackoverflow and www.postgresql.org I would list more of the places I have seen helpful info - but alas my reputation doesn't allow me to add any more

If there are any thoughts or ideas it would be greatly appreciated.

regards.

In my opinion it is not related to mklink or Windows issues.

Per pg_basebackup docs, the main data directory will be placed in the target directory, but all other tablespaces will be placed in the same absolute path as they have on the server.

-F format
--format=format

    Selects the format for the output. format can be one of the following:

    p
    plain

    Write the output as plain files, with the same layout as the current data directory and tablespaces. When the cluster has no
    additional tablespaces, the whole database will be placed in the
    target directory. If the cluster contains additional tablespaces, the
    main data directory will be placed in the target directory, but all
    other tablespaces will be placed in the same absolute path as they
    have on the server.

    This is the default format.

Solution: do not run it on the same server, or change backup format.


Is it safe to stop the shrinking of MSDB? In a production server (Microsoft SQL Server 2014), the file MSDBData.mdf ballooned up to more than 450 GB, eating almost all the space in the server (only 38 GB remaining).

First thing, I created a backup of MSDB on a network share (via SSMS -> activities -> backup). Then I tried to reduce the size of the file (via SSMS -> activities-> Shrink -> file), but the shrinking is stalled and no disk space has been released. The shrinking dialog window is frozen since hours.

In the activities monitoring of SSMS, in processes monitoring the shrinking appears to be in SUSPENDED status.

In Event viewer there are several events

i.e.

(source MSSQLSERVER, eventid 847) beginning with

 1. Time-out occurred while waiting for latch: class 'FGCB_ADD_REMOVE'
 2. Time-out occurred while waiting for latch: class 'FCB'

The question: is it safe to close the dialog window of SSMS to stop the shrinking of MSDB, in order to carry out further investigation?

Yes, it is safe to cancel that operation (but you need to be patient and let it finish rolling back - do not panic and stop the SQL Server service or reboot the server; all that will do is make the rollback start over).

While you're waiting, let's address your underlying problem.

Outside of a scenario where you manually increase the size to prepare for more data (or just get dibs on space before someone else takes it), data files increase in size because you added data to the file, and it wasn't already big enough to store that data. Telling SQL Server to shrink a file that had to grow to accommodate more data is unlikely to accomplish anything unless you remove some of the data that caused it to grow in the first place.

This could be from a rogue backup job - maybe 3rd party - that is running every second and filling the backup history tables. Or a job run amok that is generating job history and filling that table with errors. Or an infinite loop that is filling database mail tables with junk. Or dozens of other things, especially if you've put user tables for your own processes into msdb.

To find out what is taking up the space, you can use a query like this and another like this (many others exist). It could be that the tables are massive due to fragmentation, or lots of LOB data, or really bad fill factor, or oversized but underpopulated fixed-width columns, or just sheer number of rows. But you're going to have to find this and clear it up before shrinking has any hope of accomplishing anything.

Remove the data you no longer need (and take whatever steps necessary to ensure that whatever process did that is not going to do it again). Rebuild the indexes with a proper fill factor, and set up some kind of maintenance routine (like Ola's scripts) to manage indexes, and possibly an alert on too many file growth events for the msdb database (or all databases), so you can catch this problem earlier. You can see all file growth events that happened recently, for example, using the default trace.

Then, use DBCC SHRINKFILE (not some UI hinkiness in SSMS, or DBCC SHRINKDATABASE, which will also try to shrink the log, and will try to shrink everything at once), a little bit at a time, to bring the file size back down. If the file is currently 450 GB, and you free up 300 GB (you can use sp_spaceused to see the result of the above), I would get to a 200 GB file, let's say (leaving room for future growth), by first shrinking to 425 GB, then 400 GB, then 375 GB, and so on... you may need to do that in smaller chunks, say starting with 2 GB or 5 GB at a time, depending on the capabilities of the underlying disk subsystem.


Update foreign key from csv file I am trying to set up a simple database for the first time using MySQL Workbench. I'd like to update the foreign keys of the child table based on the values of parent_child.csv. Here are the current MySQL tables I have right now.

parent table

id_num  parent_id
------------------
33      parent_1
29      parent_2
46      parent_3
17      parent_4
...     ...

child table

id_num  child_id  parent_id_num
--------------------------------
22      child_1   NULL
13      child_2   NULL
52      child_3   NULL
76      child_4   NULL
...     ...       ...

And here is what parent_child.csv looks like:

parent_child.csv

parent_id  child_id
--------------------
parent_1   child_3
parent_1   child_4
parent_2   child_1
...         ...

Here is what I'd like the updated child table to be:

child table

id_num  child_id  parent_id_num
--------------------------------
22      child_1   29
13      child_2   NULL
52      child_3   33
76      child_4   33
...     ...       ...

Assume that not all parent_id and child_id's are in parent_child.csv. So not all child_id's get updated.

How would I go about doing this? Would it require me to create a parent_child table in MySQL?

Here's what I want to do in a nutshell:

for every child_id in parent_child.csv:

get child_id's parent_id
find parent_id's id_num using parent table
replace child_id's foreign key (referred to as parent_id_num, set currently to NULL) with the found parent id's id_num

You need to bring the CSV table into MySQL, then you can:

update child
left join parent_child on child.child_id = parent_child.child_id
left join parent on parent_child.parent_id = parent_id
set child.parent_id_num = parent.id_num


Recommendations for more than one tempdb file I would like to split tempdb into two files with same size, to improve performance of the database.

I wonder if these two data files can be on the same disk, separately from the logs and users data files?

We have various installations at our shop and we tend to start low with four tempdb files and then add additional files if there is tempdb contention as pointed out by @Kevin3nfs comment, where he references an SQLSkills.com search.

Microsoft has a knowledge base article: Recommendations to reduce allocation contention in SQL Server tempdb database

  [...]
  As a general rule, if the number of logical processors is less than or equal to 8, use the same number of data files as logical processors. If the number of logical processors is greater than 8, use 8 data files and then if contention continues, increase the number of data files by multiples of 4 (up to the number of logical processors) until the contention is reduced to acceptable levels or make changes to the workload/code.
  [...]

SQLSKills.com has a good article The Accidental DBA (Day 27 of 30): Troubleshooting: Tempdb Contention which has some general information about tempdb contention and a bit further down a script which will retrieve information about tempdb contention from some DMVs.

  [...]
  One of the most common performance problems that exists in SQL Server instances across the world is known as tempdb contention. What does that mean?

  Tempdb contention refers to a bottleneck for threads trying to access allocation pages that are in-memory; it has nothing to do with I/O.

  Consider the scenario of hundreds of concurrent queries that all create, use, and then drop small temporary tables (that by their very nature are always stored in tempdb). Each time a temp table is created, a data page must be allocated, plus an allocation metadata page to keep track of the data pages allocated to the table. This requires making a note in an allocation page (called a PFS page – see here for in-depth info) that those two pages have been allocated in the database. When the temp table is dropped, those pages are deallocated, and they must be marked as such in that PFS page again. Only one thread at a time can be changing the allocation page, making it a hotspot and slowing down the overall workload.
  [...]

Below is a script we used for very large database server configurations. It creates a tempdb with 8 files of 11GB size with no autogrowth and a tempdb transaction log file of 11GB. This script can be adapted to your requirements.

USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev',FILENAME = N'G:\tempdb.mdf' , SIZE = 12288000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0 )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev2', FILENAME = N'G:\tempdb2.ndf' , SIZE = 12288000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0 )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev3', FILENAME = N'G:\tempdb3.ndf' , SIZE = 12288000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0 )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev4', FILENAME = N'G:\tempdb4.ndf' , SIZE = 12288000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0 )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev5', FILENAME = N'G:\tempdb5.ndf' , SIZE = 12288000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0 )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev6', FILENAME = N'G:\tempdb6.ndf' , SIZE = 12288000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0 )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev7', FILENAME = N'G:\tempdb7.ndf' , SIZE = 12288000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0 )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev8', FILENAME = N'G:\tempdb8.ndf' , SIZE = 12288000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0 )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', FILENAME = N'G:\templog.ldf' , SIZE = 12288000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0 )

As you can see these files were all located on the same drive.

If you still notice contention in the tempdb database, then splitting the tempdb database files over various disks might be a possible solution, but than depends on your configuration (SAN, SSD, ...) and would have to be verified with testing.


Prepare statements plan invalidation in postgresql Postgresql caches plans for prepared statements and their subsequent executes. see:
https://www.postgresql.org/docs/current/sql-prepare.html
https://stackoverflow.com/questions/7142335/how-does-postgresql-cache-statements-and-data

The question I have is what will destroy/re-write/clear postgres has cached? Naturally, any DDL statement (such as drop, alter etc.) would do. But are there other things?

Plans are invalidated if any object that they use suffers “cache invalidation”. Compare this source comment:

 * Currently, we track exactly the dependencies of plans on relations and
 * user-defined functions.  On relcache invalidation events or pg_proc
 * syscache invalidation events, we invalidate just those plans that depend
 * on the particular object being modified.  (Note: this scheme assumes
 * that any table modification that requires replanning will generate a
 * relcache inval event.)  We also watch for inval events on certain other
 * system catalogs, such as pg_namespace; but for them, our response is
 * just to invalidate all plans.  We expect updates on those catalogs to
 * be infrequent enough that more-detailed tracking is not worth the effort.

In the case of tables:

git grep CacheInvalidateRelcache

on the PostgreSQL source, you can find which changes involving tables cause plans to be invalidated:

contraints are added, removed, modified or renamed
attaching or detaching a partition
an index is added or dropped
REINDEX
a table is added to or removed from a publication
CLUSTER and VACUUM (FULL)
a row level security policy is added, modified or dropped
an inheritance child is added
ALTER TABLE
a trigger is added, dropped, renamed, disabled or enabled
a query rewrite rule is added or removed
TRUNCATE

Other actions that lead to any such action will of course also invalidate plans.


PostgreSQL: hstore trgm search on fixed key I have a table of products with custom properties for each product. So I have props::hstore column in my table. I want the user to be able to search products by key/value by first selecting the key and then entering the value with autocomplete feature. So I need two steps here:

Get all the keys in props field. SELECT DISTINCT with skeys should work here, but I don't understand how to create index.
Find most relavent values for fixed key (autocomplete feature). But seems like gin_trgm_ops can create index only on value not on value for fixed keys.

Also, maybe it can be a good idea to change the hstore with jsonb, but I don't see why it can be more perfomant.

With only 10-20 distinct keys, I would urgently consider one separate column per key in the same row. Unused keys stay NULL. Related case:

JSONB vs BRIN Index

That said, there is a way to get the list of distinct key names from a document type column like jsonb or hstore comparatively quickly:

Create a btree expression index on the sorted array of key names (or a concatenated string), traverse it with a recursive CTE to emulate a loose index scan and condense the distinct set of keys from the distinct set of combinations of keys.

That will read 1000 index tuples for 1000 combinations (ideally in an index-only scan) instead of all 10kk of rows. A difference of factor 10k.

You need an IMMUTABLE function extracting the array or list of keys for the index - like akeys() for hstore.

A btree index on an array is rarely useful. This is the rare case.

Related code examples:

GROUP BY one column, while sorting by another in PostgreSQL


SQL - prevent a function from being executed in a short period of time I'm using a lot of functions to make a website up and running, almost every action in that website leads to executing a SQL functions.
Imagine it this way, almost all data processing and website logic is handled by database and partly web server, crazy right? not my fault.

But there's a problem here, there are multiple event handlers in front-end which send requests to database and in return ask for huge amount of data, like by one click asking for big JSON strings, so there's a chance that users mistakenly call these SQL functions by simultaneously clicking on these events in a short period of time, where it can lead to drop database connection or bring the server down.

So, all I need is to know is whether I can prevent SQL functions from being executed multiple times in a short period of time? or at lease to be executed once before it returns data? and how?

You can prevent a stored procedure from running more often than every "x" seconds using a table to keep track of the last run-time of the given procedure.

Create a table to store the last-run-time:

CREATE TABLE dbo.ProcControl
(
    ProcName sysname NOT NULL
        CONSTRAINT PK_ProcControl
        PRIMARY KEY
        CLUSTERED
    , LastExecution datetime NOT NULL
);
INSERT INTO dbo.ProcControl (ProcName, LastExecution)
VALUES (N'dbo.TestProc', '2017-01-01 00:00:00')
GO

Create a test stored procedure:

CREATE PROCEDURE dbo.TestProc
AS
BEGIN
    SET NOCOUNT ON;
    IF NOT EXISTS (
        SELECT 1
        FROM dbo.ProcControl pc
        WHERE pc.ProcName = N'dbo.TestProc'
            AND pc.LastExecution >= DATEADD(SECOND, -5, GETDATE())
            )
    BEGIN
        /*
            run the code you want to run at most every 5 seconds.
        */
        SELECT Result = 'This is a result'
            , [Timestamp] = GETDATE();
        UPDATE dbo.ProcControl
        SET LastExecution = GETDATE()
        WHERE ProcName = N'dbo.TestProc';
    END
END
GO

Here, we run the proc twice in quick succession; the first iteration returns a valid result, the second iteration does not.  Then we wait for 6 seconds, and run the procedure again, which in-turn returns a result :

DECLARE @returnVal int;
EXEC @returnVal = dbo.TestProc;
PRINT @returnVal;
EXEC @returnVal = dbo.TestProc;
PRINT @returnVal;
WAITFOR DELAY '00:00:06';
EXEC @returnVal = dbo.TestProc;
PRINT @returnVal;

The results:

Cleanup:

IF OBJECT_ID(N'dbo.ProcControl', N'U') IS NOT NULL
DROP TABLE dbo.ProcControl;
IF OBJECT_ID(N'dbo.TestProc', N'P') IS NOT NULL
DROP PROCEDURE dbo.TestProc;
GO

Would I suggest doing this?  Absolutely not!

What if you have two customers using the site at the same time?  The first customer will get results, the second customer may not.  Also, writing into the dbo.ProcControl table unnecessarily may reduce performance, and might become a central bottleneck.

Re-architect your website to use caching and only make calls to the database when data is needed from it.


Would it be sensible to create a MySQL user for each user account of a web application? The thought occurred to me recently, but I have a suspicion the idea might be insane. A couple of benefits I'm perceiving are:

facilitate automated logging via triggers
control mysql connections on a per-(web-application-)user level

Obviously this would yield me a ridiculously long table of users, but any penalties of that aren't clear to me yet: the User table is properly indexed for O(Log(n)) single-user lookups.

What type of logging would you be looking at gaining? What I do in my application is an audit table that tracks application user, application command, and result (among other things). This, along with another audit table that tracks individual table changes, provides all the logging we need per user.

You could also set up a connection pool in your application to control connections per application.

At first blush, I think trying to manage so many users is a burden that outweighs any possible benefits.


Can I get an equivalent query for this Not in Query SELECT ID FROM dbo.SomeTable
            WHERE ID not in
            (
                SELECT MIN(ID)
                FROM dbo.SomeTable
                GROUP BY SystemNo
            )

As this query is taking very long (more than a minute) to get executed. I guess "IN" is the culprit for this. I would like a query which yield same result but with most efficient time.

You don't select those ids that are minimum in their systemno group so you select only those that have a lower id in their group:

SELECT id FROM dbo.SomeTable st1
WHERE EXISTS
  (
    SELECT *
    FROM dbo.SomeTable st2
    WHERE st1.SystemNo = st2.SystemNo AND st2.id < st1.i
  )


Getting total number of counts for distinct entrys for the same id I have a table which is like this.

CREATE TABLE `ad_analytics` (
  `id` int(90) NOT NULL,
  `ad_id` int(90) NOT NULL,
  `advertiser_id` int(90) NOT NULL,
  `publisher_id` int(90) NOT NULL,
  `visitor_ip` varchar(250) NOT NULL,
  `type_ad` varchar(90) NOT NULL,
  `impression` int(90) NOT NULL,
  `view` int(90) NOT NULL,
  `clicks` int(90) NOT NULL,
  `date_event` date NOT NULL,
  `placed_date` date NOT NULL,
  `ending_date` date NOT NULL,
  `cpc` int(60) NOT NULL,
  `cpv` int(60) NOT NULL,
  `cpi` int(60) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I want to count the number of impressions, views, and clicks for a particular id for distinct IP Aka if there are four impressions from the same IP then it should be counted as one impression for a particular IP. How can I do that?

Sample data.

INSERT INTO `ad_analytics` (`id`, `ad_id`, `advertiser_id`, `publisher_id`,
    `visitor_ip`, `type_ad`, `impression`, `view`, `clicks`,
    `date_event`, `placed_date`, `ending_date`, `cpc`, `cpv`, `cpi`)
VALUES
(34, 49, 113, 109, '::1', 'Video', 1, 0, 0, '2018-04-16', '0000-00-00', '0000-00-00', 0, 0, 0),
(35, 49, 113, 109, '::1', 'Video', 1, 0, 0, '2018-04-16', '0000-00-00', '0000-00-00', 0, 0, 0),
(36, 49, 113, 109, '::1', 'Video', 0, 0, 1, '2018-04-16', '0000-00-00', '0000-00-00', 0, 0, 0),
(37, 49, 113, 109, '::1', 'Video', 1, 0, 0, '2018-04-16', '0000-00-00', '0000-00-00', 0, 0, 0),
(38, 50, 113, 109, '::1', 'Video', 0, 0, 1, '2018-04-16', '0000-00-00', '0000-00-00', 0, 0, 0),
(38, 49, 113, 109, '::1', 'Video', 0, 0, 1, '2018-04-16', '0000-00-00', '0000-00-00', 0, 0, 0);

I am expecting the result like this.

ad_id  Impression views click
49       1         0      1
50       0         0      1

First you need to group by ad_id and then group by visitor_ip
then select max for impression, view and click

so your query would look like

SELECT visitor_ip, ad_id,MAX(impression), MAX(view), MAX(clicks)
FROM ad_analytics
GROUP BY ad_id, visitor_ip;

NOTE : You should avoid using reserved keywords as column name.


MySQL still seems to be establishing a connection even when access is denied, causing aborted connects and instability Lately the server's been going a bit haywire and we've spent several weeks chasing down leads to no avail.  All we can tell is that when the websites go down (and come back up, and go down, and come back up...) the aborted_connects MySQL variable starts increasing somewhat quickly.  The only way we've been able to restore order has been to restart the server.  After that, it can take as little as an hour or as long as a few days, but the problem always comes cropping up again.  This began immediately after upgrading from 5.1 to 5.6.

I've looked through the log files, specifically for threads which have a Connect logged, but no corresponding Quit, and I've found something.  The culprit appears to be the following two lines:

43375 Connect    Access denied for user 'test'@'ch.websitepulse.net' (using password: NO)
43375 Connect    test@ch.websitepulse.net as  on

I assume that "websitepulse" is a service we use to monitor the database and make sure it's up, but the only guy who would know for sure isn't here today.  We  likely knowingly use (and maybe pay for) this service.

Throughout the log file, there are a good number of lines pretty much identical to the two above, and the thread has no corresponding quit message.  It would seem that the first line, access being denied, would indicate that there wasn't even a successful connection established, so there would be no reason for a Quit.  The second line, however, has me wondering.  It appears that despite access being denied, it still connects somehow.  No queries are ever executed, and it's always just these same two lines.

The two spaces on the second line between "as" and "on" in the second line are no mistake.  There is nothing there, or after "on", as I suspect there should be.  Our database guy said that the number of aborted connections corresponded to the number of connections by user NULL@NULL.  The as <blank> on <blank> would seem to indicate that this user is our NULL.

Does this sound right to anyone?  If so, how might we go about fixing it?  I'm really in the dark, and Googling it hasn't turned up anything promising.

Thanks so much.

Sometimes, running FLUSH HOSTS; will clear up the host cache when you can no longer connect. Why ?

According to the MySQL Documentation on FLUSH HOSTS

  Empties the host cache. You should flush the host cache if some of your hosts change IP address or if the error message Host 'host_name' is blocked occurs. (See Section B.5.2.6, “Host 'host_name' is blocked”.) When more than max_connect_errors errors occur successively for a given host while connecting to the MySQL server, MySQL assumes that something is wrong and blocks the host from further connection requests. Flushing the host cache enables further connection attempts from the host. The default value of max_connect_errors is 10. To avoid this error message, start the server with max_connect_errors set to a large value.

When you restart mysql, it clears the host cache in a very heavy-handed manner. Therefore, to apply some preventative to this problem, you should be setting max_connect_errors to 1000000000000 (Yes, that's a trillion). Since max_connect_errors is dynamic, a mysql restart is not needed. Just run

mysql> SET GLOBAL max_connect_errors = 1000000000000;

then add the variable to my.cnf like this

[mysqld]
max_connect_errors = 1000000000000

Give it a Try !!!


Using count() and sum() in a calculation in a select I am trying to do the following in a single select. We have a SAAS that has Company records and User records. A User is a member of a Company (Company.CompanyId = User.CompanyId). In the User record we track bandwidth usage for that user (for the month).

I need to do a select that gives me all companies that have used over 1GB of bandwidth per user. I can get this company by company using:

select count(*) as numUsers,
  sum(BandwidthThisMonth) as totalBandwidth
from [User]
where [User].CompanyId = @CompanyId

The problem with that approach is I have to first get the CompanyId of every company, and then one by one pull this data. And I only need to find the cases where the usage is over 1GB/user, which is rare.

Is there a way to do something like this:

select
  CompanyId,
  count(*) as numUsers,
  sum(BandwidthThisMonth) as totalBandwidth
from Company, [User]
where totalBandwidth - (numUsers*1000000000))/1000000000 > 0

Where the count() & sum() are against just the User records where User.CompanyId = Company.CompanyId?

GROUP BY and the HAVING clause are what you are looking for. GROUP BY allows you to use aggregate functions without having to only have 1 row of data as you currently do. Here, we're going to compute the count of all the users and to total bandwidth for all the users at that company. I don't quite get your tipping point so the logic there may be off. I guess I'm reading it as "we're sharing 1GB of data across all these users"

The HAVING is similar to our WHERE but operates on the GROUPed BY data. Here I've specified that I only want rows where the total bandwidth is over 1GB.

Unless you need something from the Company table, there's no real reason to add them to the query since all of your data is on the User record.

SELECT
    U.CompanyId
,   COUNT(*) as numUsers
,   SUM(U.BandwidthThisMonth) as totalBandwidth
FROM
    Company C
    INNER JOIN
        [User] U
        ON U.CompanyId = C.CompanyId
GROUP BY
    U.CompanyId
HAVING
    -- this assumes storage vendor math and not true GB
    -- As a consumer, I'd expect 1GB of bandwidth to be 1 073 741 824
    SUM(U.BandwidthThisMonth) > 1000000000;


Other way to disconnect user/client in mySql 5.6? Hello everyone is there another way of disconnecting user/client aside from:

kill id

I have a close button which when i click the button it will close the connection on the selected user... but the problem in my side is that connection ID is changed every process... i can't determine which connection ID should be killed.

this syntax also didn't work using laravel db query..

DB::select( " SELECT CONCAT('KILL ',  $userCon, ';') FROM information_schema.processlist WHERE user = $id;");

Hope anyone could help me... Thanks in advance.

Note: I used Laravel in my Web.

Enforcing limits: ALTER USER and set MAX_USER_CONNECTIONS.

If you where using MariaDB you'd have KILL USER

Otherwise, for MySQL and non-MariaDB, for killing off connections try the following SQL:

select @kill := GROUP_CONCAT(CONCAT('KILL ',  id, ';')) FROM information_schema.processlist WHERE user = $id;;
PREPARE stmt FROM @kill;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


My master database has multiple personnality issues I have a tricky case for you, and I didn't find any similar issue anywhere (I must be bad at searching), so here it is...

I took a backup of the master database from a production server (SQL Server 2014 Std), let's call it SQLPRD, and I restored it, like any non-system database (so I thought), to a dev server (SQL Server 2014 Developer), let's call it SQLDEV.

In the restore statement, I changed the name of the destination database to be master_SQLPRD, and I changed the mdf and log file names to match the new name of the database, so they wouldn't overwrite the DEV master.

I extracted what I wanted from master_SQLPRD, so I dropped it. Then SSMS started throwing me errors as it couldn't find that master_SQLPRD database: now I can't list the databases on SQLDEV, I can't expand the SQL Agent node, etc. I restarted the database engine service, expecting it not to come back online... but it did.

Before I rebuild master (which I didn't backup - it's almost a throw-away instance), is there anything I could try to make master identify itself as master, not master_SQLPRD?

Thank you.

Edit: The error showing in the error log is-

Error: 18456, Severity: 14, State: 73.
Login failed for user 'mydomain\my.login'. Reason: Failed to open the database 'master_SQLPRD' configured in the session recovery object while recovering the connection. [CLIENT: ]

Alright... I don't know what the underlying issue was, but closing SSMS (2016) and re-opening it seemed to fix it, as I don't have any error messages anymore.

Thank you all for your help and interest!


Adding index in table suggestion? I have a table called Products with ID(PK, Clustered), Name, Price, Brand, ...., RetailerID, SKU, ModelNumber. My query have

SELECT ....
FROM Products
WHERE RetailerID = @RetailerID AND
      ( (@SKU != '' AND SKU = @SKU)  OR (@ModelNumber != '' AND ModelNumber = @ModelNumber))

I am going to add index for this query. Should I add 3 indices on RetailerID , SKU and ModelNumber or 2 compound indices on (RetailerID, SKU) and (RetailerID, ModelNumber) or 1 compund index on (RetailerID, ModelNumber, SKU)

I'd create a seperate index on SKU and ModelNumber. I don't think an index on RetailerID will do much good as there won't be many different retailers .

The best approach to analyze this is by comparing the execution plans & times of the different sets of indexes


MASTER-MASTER repliation MySql in master A ceate user after using replicate-do-db filter on Master A, that user is not showing on Master B by this query-------select User from mysql.user; how to completely access both machines .... (linux redhat6)

replicate-do says to replicate that table but nothing else.  That is, the "grant" tables are won't be replicated".
How did you "create the user"?  CREATE USER?  GRANT?  Manipulate mysql.user?  Something else.
Did you do FLUSH PRIVILEGES;?


Database with FILESTREAM enabled gives Error 3056 during backup I have a SQL 2014 database enabled for FILESTREAM and I am getting errors 3056 during backup.

The message is

DESCRIPTION:    The backup operation has detected an unexpected file in a FILESTREAM container. The backup operation will continue and include file 'F:\Resources\Staff pictures\Outlook\xxx.jpg'.

Any ideas on how to resolve this greatly appreciated.

Regards

James

You have manually create a file in a FS container. Stop doing so.

A FS container is defined here:

  FILESTREAM data must be stored in FILESTREAM filegroups. A FILESTREAM
  filegroup is a special filegroup that contains file system directories
  instead of the files themselves. These file system directories are
  called data containers. Data containers are the interface between
  Database Engine storage and file system storage.

As you see, is nothing but a file system folder. Do not delete or create files in these folder, is simple as that. all interaction must occur via the FILESTREAM API.


need help with this query This will be in MySQL. Table consists of following fields:

Date, ID, blah, blahblah, blahblahblah

I believe the primary key needs to be Date & ID.

The query I want to run is for every Date, count all the IDs but only if this is the last date the ID shows up.

So if the ID shows up in the database on 9/9/16 but NOT on 9/10/16 then we would say that it disappeared and therefore add it to the total count for date 9/9/16.

If that same ID later reappears on 9/11/16, when we run the query again that ID will no longer be added to the count as of 9/9/16.  If on 9/12/16 that ID disappears again then it would be added to the count as of 9/11/16.

Here's some sample data.  I did not put any data in for the "blahs" since it is not relevant for the query (I believe):

Date,   ID, blah,   blahblah,   blahblahblah
9/8/2016,   1
9/8/2016,   2
9/8/2016,   3
9/8/2016,   4
9/8/2016,   5
9/8/2016,   6
9/8/2016,   7
9/8/2016,   8
9/9/2016,   1
9/9/2016,   2
9/9/2016,   3
9/9/2016,   4
9/9/2016,   5
9/9/2016,   9
9/9/2016,   10
9/9/2016,   11
9/10/2016,  2
9/10/2016,  4
9/10/2016,  6
9/10/2016,  11
9/10/2016,  12
9/10/2016,  13

The expected output run on 9/9/16 would be:

Date, Total IDs Last Day
9/8/16,3
9/9/16,8

The expected output run on 9/10/16 would be:

Date, Total IDs Last Day
9/8/16,2
9/9/16,4
9/10/16,6

Note that when the query is run on 9/10/16, the total for 9/8/16 is different from when it was run on 9/9/16 because an ID (number 6) disappeared but then re-appeared.

One option would be to use a not exists to filter out any rows that appear for a later date.

select date, count(*)
  from your_table a
 where not exists( select 1
                     from your_table b
                    where a.id = b.id
                      and b.date > a.date )
 group by date


Is this Index checking logic ok? I am writing a script to quickly check the indexes in a given database. I think I have the script written ok but to remove duplicate rows I have done alot of SUM functions and I am unsure whether this is correct or not:

SELECT      OBJECT_NAME(i.object_id)                                                                                AS 'Table Name'
,           COALESCE(i.name, '')                                                                                AS 'Index Name'
,           i.type_desc                                                                                AS 'Index Type'
,           i.is_primary_key                                                                                AS 'Primary Key'
,           i.is_disabled                                                                                AS 'Index Disabled'
,           ROUND(CONVERT(FLOAT,SUM(user_seeks)) / (CONVERT(FLOAT,ISNULL(NULLIF(SUM(user_scans),0),1))),2)                                          AS 'Seeks Per Scan'
,           ROUND((CONVERT(FLOAT,SUM(user_seeks) +SUM(user_scans) + SUM(user_lookups))) / (CONVERT(FLOAT,ISNULL(NULLIF(SUM(user_updates),0),1))),2) AS 'Reads Per Write'
,           (SUM(user_seeks) + SUM(user_scans) + SUM(user_lookups))                                                                                AS 'Reads'
,           SUM(user_updates)                                                                                AS 'Writes'
,           SUM(user_seeks)                                                                                AS 'Seeks'
,           SUM(user_scans)                                                                                AS 'Scan'
,           ROUND(AVG(ps.avg_fragmentation_in_percent),2)                                                                                AS 'Avg Fragmentation %'
,           NULLIF((SUM(leaf_insert_count) + SUM(leaf_update_count) + SUM(leaf_delete_count)),0) / NULLIF(SUM(leaf_allocation_count),0)             AS 'Leaf Page Splits Per Write'
,           NULLIF((SUM(nonleaf_insert_count) + SUM(nonleaf_update_count) + SUM(nonleaf_delete_count)),0) / NULLIF(SUM(nonleaf_allocation_count),0) AS 'Non-Leaf Page Splits Per Write'
,           CAST (100.0 * SUM(row_lock_wait_count) / (1 + SUM(row_lock_count)) AS NUMERIC(15,2))                                                    AS 'Block %'
,           SUM(row_lock_wait_in_ms)                                                                                AS 'Row Lock Wait (ms)'
,           CAST(1.0 * SUM(row_lock_wait_in_ms) / (1 + SUM(row_lock_wait_count)) AS NUMERIC(15,2))                                                  AS 'Average Row Lock Wait'
FROM sys.indexes i
LEFT JOIN sys.objects o ON o.object_id = i.object_id
LEFT JOIN sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL) s ON o.object_id = s.object_id AND i.index_id = s.index_id
LEFT JOIN sys.dm_db_index_usage_stats u ON s.object_id = u.object_id AND u.index_id = s.index_id
LEFT JOIN sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps ON u.index_id = ps.index_id AND u.object_id = ps.object_id
WHERE OBJECTPROPERTY(o.object_id,'IsUserTable') = 1
AND NOT OBJECT_NAME(i.object_id) = 'sysdiagrams' -- exclude sysdiagrams
--AND i.is_primary_key = 0
GROUP BY OBJECT_NAME(i.object_id), COALESCE(i.name, ''),i.type_desc,i.is_primary_key,i.is_disabled
ORDER BY OBJECT_NAME(i.object_id), COALESCE(i.name, '')

Is this ok? Or is SUMing the rows bad?

Also, is there any information that you think would be helpful for finding indexes that aren't used/need re-indexing etc

Why not just use one of the scripts which are out there and available for free.  The current reigning version of the one posted on sqlfool.com.


Query Require for Opening and Closing I want to Calculate opening and Closing between two table.
Below is Data and expected output
 Create table #tbl_Receivable (Rec_ID int,Inv_ID int ,Customer_ID int,Rec_Date date,Rec_Amount varchar(50),Inv_type varchar(50),item_Weight int);
  Create table #tbl_Shop_InvM (Inv_ID int ,Customer_ID int,E_Date date,Rec_Amount varchar(50),Inv_type varchar(50),item_Weight int);
   Create table #tbl_Customer ( Customer_ID int ,Customer_Name varchar(50));

 insert into #tbl_Customer values (1,'Akhter'), (2,'Hussian');

INSERT INTO #tbl_Shop_InvM VALUES
(211,1,'2020-03-06',5000,'Payable',0),
(222,1,'2020-03-08',8000,'Payable',0),
(223,2,'2020-03-08',2000,'Payable',0),
(224,1,'2020-03-09',5500,'Payable',0),
(225,2,'2020-03-10',15000,'Payable',500)

INSERT INTO #tbl_Receivable VALUES
   (112,211,1,'2020-03-07',2000,'Received',0),
(115,222,1,'2020-03-09',4000,'Received',0),
(116,222,1,'2020-03-09',2000,'Received',0),
(117,211,1,'2020-03-12',1000,'Received',0),
(118,225,2,'2020-03-11',15000,'Received',0);

I want Below ouput in which Date is giving from 06-03-2020 to 12-03-2020 ,then opending will be 0,because there is not invoice before 06-03-2020 ,
if Date is giving from 12-06-2020 to 12-06-2020 ,then opening will be 09-03-2020 balance 10500

Desire Output

First of all, your Rec_Amount columns should be changed from varchar to something numerical like float.
Secondly, i think you wrote the wrong range in this sentence so i don't understand what you mean here.

if Date is giving from 12-06-2020 to 12-06-2020 ,then opening will be 09-03-2020 balance 10500

I believe this query gives the desired output:
SELECT q.Customer_ID, q.E_Date, q.Payable, q.Received, q.Balance
FROM
(SELECT Customer_ID, E_Date, Payable, Received, SUM(CAST(ISNULL(Payable, 0) AS float)-CAST(ISNULL(Received, 0) AS float)) OVER(PARTITION BY Customer_ID ORDER BY E_Date) AS Balance
FROM
(SELECT s.Customer_ID, s.E_Date, s.Rec_Amount, s.Inv_type
FROM #tbl_Shop_InvM s
UNION ALL
SELECT r.Customer_ID, r.Rec_Date, r.Rec_Amount, r.Inv_type
FROM #tbl_Receivable r) AS a
PIVOT
(MAX(Rec_Amount)
FOR Inv_type IN ([Payable],[Received])) AS p) AS q

/*Set date range */
--WHERE q.E_Date BETWEEN '2020-03-07' AND '2020-03-11'

Output:


Split String Function I have a question regarding this Split String function Aaron Bertrand recommends here.

With it you can extract information between two slashes. Now if I want to extract not only one piece of info like the servername, but also a second piece like the day of the week. How would I have to rewrite the code in order to get that info in one result set?

Here's a sample of my data

E:\BaseData\RUK\HPP_Conversion_Detail\hpp_conversion_detail_report_2015_11_02.csv
E:\BaseData\RUK\Manual_Review\manual_review_report_2015_11_01.csv
E:\BaseData\RUK\Disputes\dispute_report_2015_11_01.csv
E:\BaseData\RSE\HPP_Conversion_Detail\hpp_conversion_detail_report_2015_11_02.csv
E:\BaseData\RSE\Manual_Review\manual_review_report_2015_11_01.csv
E:\BaseData\RSE\Disputes\dispute_report_2015_11_01.csv

I'm interested in everything between the second and third slash and the third and fourth slash. What Geoff suggested is a good idea. I'm then getting the results in rows. Is it possible to get the results back in additional columns?

Thanks!

Try doing the regular split, getting strings that look like 20151002SERVER1. Then you can just refer to CONVERT(date,LEFT(Item,8),112) and STUFF(Item,1,8,'') as your date and server name.


Invalid File Name on login to SQLPLUS The moment I log into SQLPLUS I get an SP2-0556 error. I recently moved OS but am connecting to the same database so I am not sure if it's because I'm using my old TNSNames or what. I can connect and work normally but a few issues have arisen with running SQL scripts due to this where they then say Invalid File Name.

Any help would be greatly appreciated, thanks.

the database is 11g,
the command I use for running scripts is a custom ant script that basically loops through and runs normal .sql scripts,
and I changed from a Ubuntu system to Mac OS X.

The issues are that any new scripts I create (that I know work) give a Invalid File Name error then stop.

This might be Bug 7150873 SQL SCRIPTS FILE NAME CONTAINING SPACES RESULTS IN SP2-0556.

I assume that your script is trying to call a script containing spaces.


Connection Reset by Peer: Socket Write Error We have a vendor working on Java (JDBC) application saying they are getting the error

  Connection Reset by Peer: Socket Write Error

They are insisting that the SQL Server (SQL Server 2012) is forcibly disconnecting their application server for some reason. I have run Profiler traces to collect Attention and User Error Message events and have found nothing helpful. I have checked the ring buffer and have only failed logins - no disconnects.

SELECT CAST(record AS XML) FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_CONNECTIVITY'

Windows logs do not show anything helpful. Is this an error I should be able to track down in SQL Server, or is this purely a network error that will need a network trace or something else? Any ideas?

Doesn't look like an issue with SQL Server. Here's a link which talks about connection being forcibly closed by sql server :

https://technet.microsoft.com/en-us/library/ms187005(v=sql.105).aspx

Also, the following post might help you analyze the error further :

https://stackoverflow.com/questions/25611741/java-net-socketexception-connection-reset-by-peer-socket-write-error-when-serv


How to treat numbers within strings as numbers when sorting ("A3" sorts before "A10", not after) For all of these queries:
SELECT label FROM personal.storage_disks ORDER BY label ASC;
SELECT label FROM personal.storage_disks ORDER BY label COLLATE "C" ASC;
SELECT label FROM personal.storage_disks ORDER BY label COLLATE "POSIX" ASC;
SELECT label FROM personal.storage_disks ORDER BY label COLLATE "default" ASC;

The output is always: DISK 1, DISK 10, DISK 2, DISK 3, [...]
But, I want and expect: DISK 1, DISK 2, DISK 3, [...] DISK 10
I'm out of collations to try now according to SELECT * FROM pg_collation;... unless I'm supposed to use one of the many really weird ones with cryptic names. (I even tried a bunch of those with the same result.)
Please note that I've read the existing seemingly related SE questions as well as many articles on SORT BY, but they didn't help and didn't clear up anything for me.
I'm using PostgreSQL 12.4

Sorting strings naturally puts "15" before "2" because the first digit in the "15" is a "1", which sorts before "2". Sorting a "2" stored in a string type before the "15" can be done in a few ways. The most efficient way is to have the collation itself handle this internally. This option is not well known and not even available in most places, but any system that implements ICU (International Components for Unicode)** has the potential for allowing this type of sorting (as long as it allows for customizing the sort options), which is often referred to as "natural" sorting.
Handling natural sorting is often done programmatically by chopping the string into its pure alpha and numerical pieces and then sorting on those individually. This is a necessary evil in many cases, but fortunately, PostgreSQL (at least as of version 10), does allow for this internally. You would need to create a custom collation (this one even comes straight from their documentation):
CREATE COLLATION numeric (provider = icu, locale = 'en-u-kn-true');

and use that in the ORDER BY. Here is a working example of it:
https://dbfiddle.uk/?rdbms=postgres_11&fiddle=58763b51a8ccb2360cf387d8c2b91d51
NOTE
Due to collations not being generally well-understood, and the ability to customize them being more recent (for databases, at least) and even more esoteric, I would recommend doing the following when implementing this solution:

Prefix the name of the new collation with "custom_" to increase awareness that this is indeed a custom collation that might have non-obvious behavior and might not exist on other systems (hence might need to be added to a system or application setup process)
Place a comment just after each query that uses this custom collation, pointing out that it is a custom collation, and include a link to the official documentation:
https://www.postgresql.org/docs/12/collation.html#id-1.6.10.4.5.7.5
You could even mention that the -kn-true part enables "numeric" sorting.

BONUS
To more fully demonstrate how the "numeric" collation option works, I added some data to the previous example to show:

multiple / separate groups of numbers within a string are handled separately
different non-digit characters are handled as expected
leading 0's do not affect the result

The additional data is:
DISK 2A
DISK 2B
DISK 2B 33
DISK 2B 4
FILE 62
FILE 7
DIRECTORY 1000000
DIRECTORY 57
DIRECTORY 9999
DIRECTORY 57000
DIRECTORY 057
DIRECTORY 0057
DIRECTORY 52

Here is the updated example:
https://dbfiddle.uk/?rdbms=postgres_11&fiddle=20416b0dd731b2cc28b6fdee8ef70ec7

** To be fair, ICU / Unicode isn't "required" for doing this type of sorting, given that any collation or system can implement the same algorithm. However, it is built into ICU, and more and more systems are integrating ICU.


Is it possible to pass a built in function as an argument to a trigger? I ask this question in light of the following trigger producing this error:

ERROR: syntax error at or near "("
LINE 5: ...cessBlogPostApproval"('Blog Post Approval', concat('Your blo...
^

The trigger in question:

CREATE TRIGGER "processBlogPostApproval_AFTER_INSERT"
    AFTER INSERT
    ON public."ApprovedBlogPosts"
    FOR EACH ROW
    EXECUTE PROCEDURE public."processBlogPostApproval"('Blog Post Approval', concat('Your blog post, "', SELECT "Title" FROM public."BlogPosts" WHERE "PostID" == NEW."PostID", '"has been approved.'));

The problem seems to be arising due to the fact that I passed a concatenation function as my second argument, or rather, that I did not pass it correctly. Would appreciate your assistance in identifying which of the two is the cause of the problem.

The CREATE TRIGGER documentation says:

  arguments:
  An optional comma-separated list of arguments to be provided to the function when the trigger is executed. The arguments are literal string constants. Simple names and numeric constants can be written here, too, but they will all be converted to strings.

You cannot do any processing before the function is called; you have to put all the logic into the trigger function.


How to just keep latest records in SQL server? Here is my case,

Table name: EMP_Detail
Records:
ID          Name     Statue     ChangeDate
1111        Apple    Active     2017-01-01
1111        Apple    Active     2017-03-07
1111        Apple    Disabled   2017-04-06
1111        Apple    Active     2017-05-01
2222        Bell     Active     2017-02-05
2222        Bell     Active     2018-01-05
3333        Cisco    Active     2017-03-08

Trying to remove the active and out of date records and keep table like this:

ID          Name     Statue     ChangeDate
1111        Apple    Disabled   2017-04-06
1111        Apple    Active     2017-05-01
2222        Bell     Active     2018-01-05
3333        Cisco    Active     2017-03-08

Thanks a lot!

You can use row_number() and a CTE to delete from the source table.

;with cte as(
   select
        *,
       RN = row_number() over (partition by [Name], [Status] order by [ChangeDate] desc, ID desc)
   from EMP_Detail)

delete from cte
where RN != 1;


How to write a query for exact pairs of groups I am given the schema:
EMPLOYEE(Fname,Ssn,Dno)
DEPARTMENT(Dname,Dnumber,Mgr_ssn,Mgr_start_date)
PROJECT(Pname,Pnumber,Plocation,Dno)
WORKS_ON(Essn,Pno)
fiddle: https://www.db-fiddle.com/f/9LnJN32WLVu1HsdmG8NUAc/5
Primary keys are given in bold.
The question: Is it possible to define a view that will report the following detail for each employee: Ssn and the number of employees who work on exactly the same set of projects? Explain why not or given an SQL query.
My idea was to find unique pairs of employees and check if projects worked by first employee equal those of the second one (this was done by using set operation except as if s1 except s2 union s2 except s1 is empty than it seems like s1 and s2 are equal)
My attempt for this was:
SELECT e1.ssn,count(e2.ssn)
FROM EMPLOYEE e1, EMPLOYEE e2
WHERE e1.ssn < e2.ssn
AND NOT EXISTS
(
 (
 (SELECT Pno
 FROM WORKS_ON w1
 WHERE w1.Essn = e1.Ssn)
 EXCEPT ALL
 (SELECT Pno
 FROM WORKS_ON w2
 WHERE w2.Essn = e2.Ssn)
 )

 UNION ALL

 (
 (SELECT Pno
 FROM WORKS_ON w2
 WHERE w2.Essn = e2.Ssn)
 EXCEPT ALL
 (SELECT Pno
 FROM WORKS_ON w1
 WHERE w1.Essn = e1.Ssn)
 )
)
GROUP BY(e1.ssn)

Does this query look correct? Also is there a neater way of doing this?
As a final side note what exactly determines if it is impossible for us to construct an SQL query for a problem?

This is a question of Relational Division Without Remainder. Joe Celko has a very good article on this.
There are a number of ways to slice this cake. Here are a couple:
1. Get a windowed count of each employee's projects (you can also do this as a scalar subquery).
Left-join employees with each other, matching on Pno.
Group up by each pair of employees, exclude any which either have too many or too few Projects than the other.
Then do a final grouping of each employee, counting up the matching employees.
WITH WORKS_ONwithCount AS (
    SELECT
        *,
        COUNT(*) OVER (PARTITION BY Essn) AS CountPno
    FROM WORKS_ON
),
EmployeesCrossJoin AS (
    SELECT this.Essn,
        other.Essn
    FROM cte this
    LEFT JOIN cte other ON other.Essn <> this.Essn AND other.Pno = this.Pno
    GROUP BY this.Essn, other.Essn, other.CountPno
    HAVING COUNT(*) = COUNT(other.Pno) AND COUNT(*) = other.CountPno
)
SELECT emp.Essn, COUNT(*) AS CountMatching
FROM EmployeesCrossJoin emp
GROUP BY emp.Essn;

2. Group up each employee, with their project numbers in an array.
Then take the count of other employees who: after un-nesting and full-joining both arrays, there are no NULL values on either side.
WITH cte AS (
    SELECT
        Essn,
        array_agg(Pno ORDER BY Pno) AS AerPno
    FROM WORKS_ON
    GROUP BY Essn
)
SELECT Essn,
    (SELECT COUNT(*)
    FROM cte other
    WHERE this.Essn <> other.Essn
        AND NOT EXISTS (SELECT 1
        FROM unnest(this.AerPno) AS nThis(value)
        FULL JOIN unnest(other.AerPno) AS nOther(value) ON nOther.value = nThis.value
        WHERE nOther.value IS NULL OR nThis.value IS NULL
    ) AS
FROM cte this;


SQL Agent Permissions - Job Level We currently have an SQL Job that runs an SSIS package.  The output of the package is required for a report.  As a result, the application that can create the report on demand first tries to run the job that executes the package.  Because this is run by various people at various times, the previous way to handle this was to give SQLAgentOperatorRole to whichever person was in charge of kicking off the report.  I really would rather not be handing out this role (and I was not the one responsible for setting the precedent), for obvious reasons.

From what I know, you cannot give job level permissions to any group of people.  I have seen things about proxies, but not sure that would help here.  So, how do I grant X users access to:

check job status (via sp_help_job, at the moment)
Execute this job, and only this job

We are running SQL2008R2.

First, make sure you have a user in msdb associated with their login.

USE msdb;
GO
CREATE USER username FROM LOGIN loginname;

Next, deny execute on sp_help_job and sp_start_job for that user.

USE msdb;
GO
DENY EXECUTE ON dbo.sp_help_job TO [username];
DENY EXECUTE ON dbo.sp_start_job TO [username];

Next, create two wrapper stored procedures that execute as owner and call the above stored procedures with that hard-coded job_id. Here is the start version, the help version will look amazingly similar.

USE msdb;
GO
CREATE PROCEDURE dbo.start_that_job
WITH EXECUTE AS OWNER
AS
BEGIN
  SET NOCOUNT ON;

  EXEC dbo.sp_start_job @job_id = 'ACE1...';
END
GO
GRANT EXECUTE ON dbo.start_that_job TO [username];
GO

Now, they'll have to call that stored procedure directly, instead of using the UI.

Test it out:

EXECUTE AS USER = N'username';
GO
EXEC dbo.sp_start_job @job_id = 'ACE1...'; -- fails
GO
EXEC dbo.start_that_job; -- succeeds
GO
REVERT; -- this is important


Simple database structure advice Newbie to dba. I'm creating a php/mysql inventory list and would like some advice before I start, on structuring my table(s) and database.

The inventory database will eventually contain hundreds of items, ever increasing (potentially to thousands). The current data is contained in a .xls sheet which I have access to, it contains the following;

items (dummy data for display purposes)

+--------+-------+------+-----------+---------+------+-----------+----------+----------------+----------+--------+----------+
| itemId | invNo | make | model     | ip      | room | dataAdded | dateMod  | description    | location | type   | status   |
+--------+-------+------+-----------+---------+------+-----------+----------+----------------+----------+--------+----------+
| 1      | 234   | HP   | 765       | 123.123 | 54   | 01-02-16  | 02-01-16 | some desc here | floor 1  | server | active   |
+--------+-------+------+-----------+---------+------+-----------+----------+----------------+----------+--------+----------+
| 2      | 765   | Dell | Precision | 123.123 | 34   | 02-01-16  | 03-01-16 | some desc here | floor 3  | pc     | active   |
+--------+-------+------+-----------+---------+------+-----------+----------+----------------+----------+--------+----------+
| 3      | 987   | Dell | Precision | 123.123 | 65   | 05-09-15  | 07-09-15 | some desc here | floor 1  | server | inactive |
+--------+-------+------+-----------+---------+------+-----------+----------+----------------+----------+--------+----------+

Is it ok for me to add all these columns into the one table in my dtabase, or should I seperate the columns across different tables?

For example, should I have a table for location, type and status - if so, why?

Is there any disadvantage to having all the columns in the one table?

The final web application will allow users to create, read, update and delete items. As well as search for items and filter by type/location etc...

I normally use MySql Workbench or PhpMyAdmin to create and design the database.

Any advice is appreciated.

The theory is you should isolate elements of diferent purpose, function or character, in this case fields like invoiceNum, make, model, location and type are normal candidates to live in their own tables, and have only a reference to their id numbers in this table, but it depends a lot on the application itself.

What you are asking for it's called 'Normalization', and you can get a lot of examples from the web:

https://blog.udemy.com/normalization-in-database-with-example/
https://en.wikipedia.org/wiki/Database_normalization

The simple advice is: yes, at least get 1st and 2nd normalization forms when possible, it will make your life easier in the future.

EDIT: I forgot to answer 'why' you should separate different purpose data in different tables: There are a lot of reasons, but even when better explained in the links provided, we can resume it with these points:

Your data will have less redundancy, you can point to a number instead of repeat the same string through thousands or millions of records.
Your data will be easier to modify: if you have to change 'Motorbike' by 'Water Motorbike' you will change it in one row only ( easier, and a lot faster in comparison ).
Your data will be more consistent, as you can have the same concept in different tables, and update all the 'Motorbikes' of one table, but those of other table. If you have normalized your tables, you can change it in one table and would affect all involved tables, not only that you can remember the data was in.
As it's more organized, insertions, updates and searches gets incredibly improved.

This is normally called ACID ( Atomicity, Consistency, Isolation, Durability ), the properties of a well designed database. I'm sure I forget some basic concepts, but there are a lot of reasons to use normalization.


Cast varchar into int, converting any value under 0 to 0 I am inserting values form a staging table in SQL Azure into a production table. Values in the staging table are varchar, while the target value in the production table is tinyint (it is an age field for humans, so I reckon that up to 255 will suffice ;) )

Problem is that due to a bug in the source system, we tend to get some values as -1 or -2 when they should be 0. This causes issues, since TINYINT only supports values form 0 to 255 (and also because this is factually wrong). I was wondering if there is some sort of case statement that I could use to convert values to 0 if they fall under 0, or perhaps if they fall outside the 0-255 range?

As TINYINT only allows 0-255 you could also use

ISNULL(TRY_CAST(YourCol AS TINYINT),0)

The TRY_CAST above returns NULL if supplied an invalid input for the datatype (including -ve numbers) and I would be minded to leave it that way.

But if you must change these to 0 you can wrap it in an ISNULL


Measure CPU and Memory usage while running pgbench what is the best way to capture the usage of CPU and memory while running pgbench? The only way I can think of is to read the info from top header.

what I do now

I run top command by print it out in a log file.
Then grep the cpu and memory in another txt file
Later I download the txt file and import it in excel file to get the average value of Cpu usage and Memory usage
The result of pgbench will be put together in the excel file

Is there any tips or standard of procedure to implement the TPC-B as well as monitor the resources.

I show some tools I know.

pg_activity  is a simple monitoring tool like top.

pg_statsinfo and
pg_stats_reporter are  high quality utilities that monitor statistics and the activity of PostgreSQL as well as Operating system.

There are some general purpose monitoring tools such as  Cacti and Munin. Those support postgres by providing modules and they can easily be used.

BTW, I know a great book: The Art of Computer Systems Performance Analysis. This gives you some hints for benchmarking.


Getting the "flat" query corresponding to a view using nested views? I'm programming an application that queries SAP Business One databases, which are incredibly complex, riddled with cryptic table and column names left over from ancient times (including many interesting typos), and in some parts have quite bad database design.
To ease working with these, I use a large "view library" as I call it, which defines views upon views that bring a lot of clarity, eliminate repetition, and allow me to ultimately aggregate all data which my application needs in a specific screen into one or two views that the application needs to query.
This works very well and I'm happy with it.
(Yes really. The performance is usually great, and when it isn't it can usually be fixed without much hassle and is almost always revealed to be related to a bad join or sub-query somewhere that would have caused the issue regardless of whether views are being used or not. I've only ever had one issue related to using views per se, which was MS SQL Server failing to optimize queries against one of the most complex views --perhaps the most complex in fact-- which I was able to solve simply by turning the view into a procedure that materializes the results of a couple intermediate views first.)
Now in some new deployment variants of SAP Business One, I will lose the ability of creating arbitrary SQL views in the database. Bad news. I will however be allowed to run some read-only queries, via a limited subset of the SQL language...
This brought me to the question: is there a way to "flatten" an SQL view via MS SQL Server (any version), so that I automatically get a query that already has all nested views "resolved" to their definitions, recursively?
For instance, consider the following sequence of (highly simplified) view definitions:
create view myItems as
  select itemCode, itemName from Items

create view myCustomers as
  select customerCode, customerName from Customers

create view myOrders as
  select o.orderId, o.orderNumber, c.*
  from Orders o
  join myCustomers c on c.customerCode = o.customerCode

create view myOrderLines as
  select l.lineId, l.lineNumber, o.*, i.*
  from OrderLines l
  join myOrders o on o.orderId = l.orderId
  join myItems i on i.itemCode = l.itemCode

I want the ability to get the "flat version" of let's say myOrderLines, which would be this:
select l.lineId, l.lineNumber,
       o.orderId, o.orderNumber,
       c.customerCode, c.customerName,
       i.itemCode, i.itemName
from OrderLines l
join Orders o on o.orderId = l.orderId
join Customers c on c.customerCode = o.customerCode
join Items i on i.itemCode = l.itemCode

Is such a thing possible?

So I know this doesn't directly answer your question but I think are things to consider that may be of help:
Why do you lose the ability to create Views and would you be able to create a second database to house your Views instead? (Views can reference other databases besides the one they exist in.)
If the previous solution isn't possible, could you use a data synchronization feature like Replication or Availability Groups to copy the SAP database to another database where you would be able to create your Views (either on the same server or another server)?
After getting a better picture from your comments, you can still look into Replication to centralize the data into one database outside of SAP, that your product can work off of. Another feature in SQL Server useful for consolidating multiple sources of data into one destination is SSIS.


Is there a way to do a textual search through all stored procedures for a table name? I've just inherited a database with a number of tables and stored procedures.  I use Microsoft SQL Server Management Studio.

One, or possibly more, stored procedures update a specific table.  I'd like to do a textual search through all the stored procedures to look for the procedure that updates the table.

If the raw SQL was in our code, I'd just grep the code base, but with stored procedures I'm not sure how to search through all the stored procedures.

I'd also like to rename a few things and do a find/replace throughout the stored procedures.

Is it possible to search and replace in stored procedures using Microsoft SQL Server Management Studio?

Not 100% reliable (could yield false positives, or miss things due to dynamic SQL etc):

SELECT s.name, p.name, m.definition
  FROM sys.schemas AS s
  INNER JOIN sys.procedures AS p
  ON s.[schema_id] = p.[schema_id]
  INNER JOIN sys.sql_modules AS m
  ON p.[object_id] = m.[object_id]
  WHERE LOWER(m.definition) LIKE N'%update%tablename%';

You can't replace directly (and I think you'd have a real hard time building a catch-all replace expression anyway, since T-SQL is not exactly rigidly structured), but you can script out the stored procedures you found, replace CREATE with ALTER and run that to apply changes.

I have written a much more expansive stored procedure that I use for searching across all objects in all databases, including even jobs, and I explain several caveats and show an even better way than LOWER() to handle case sensitivity (you may want the search to be case sensitive; the above assumes you don't care and want all matches regardless of case):

https://sqlblog.org/2011/10/06/a-handy-search-procedure


Consolidating data changes in SQL Server by team What is a good way to manage developers' data changes?

I am using RedGate SQL Source Control to monitor changes to tables and stored procecures, so that when we do a production build, I can include all the changes in a SQL update script.

My question is: what is a good way to consolidate and monitor changes made to the data? (As this is something RedGate Source Control cannot track)

For example, new records included in lookup tables? And new records included in tables used for routing procedure calls. Thank you!

We also use source control for managing schema changes. When it comes to deployment of new versions, I find it irreplaceable. It saves a lot of time and saves oneself from headaches

Red Gate is a good choice, though we went for ApexSQL Diff  and ApexSQL Version

But none of these can track actual data changes. And data changes are not stored in source control

The tool that we use for monitoring data changes is ApexSQL Log, so we can see what data changed (records added, deleted or modified), who made a change and when. It also shows the schema changes - a table modified, a store procedure dropped, etc. Its main request is a database in Full recovery model, which requires regular backups and hard disk space - but on the other hand, you'll be ready if it comes to a disaster recovery ;)


SQL Server virtual log size I'm somewhat of an accidental DBA, being a developer who inherited a couple of database servers (2005 and 2008) from someone who knew little about database administration, and seemingly had even less interest in learning more about the subject.

I'm learning as I go, and am currently trying to figure out transaction log files.

All our databases have been set up with the simple recovery model and autoshrink. I've understood that using autoshrink is usually a horrible idea, but it's my understanding this was done in order to stop the transaction logs from growing out of control. (Does autoshrink actually shrink the log file(s) or just the DB?)

I found this about SQL Server 2012, and was wondering if it's true about 2005 and/or 2008, and exactly what it means: "When a database uses the simple recovery model, the Database Engine truncates the transaction log after a checkpoint. [...] The Database Engine triggers an automatic checkpoint under the simple recovery model when the virtual log becomes 70 percent full." Where is the virtual log size specified?

I want to disable auto shrink on all databases, but before I do that, I need to know that the log files won't grow out of control quickly.

Any help would be greatly appreciated.

A single transaction log file has both a physical size (that you see on disk), and it's also broken down within the physical file into logical sections called virtual log files (VLFs).

Both auto-growth and auto-shrink operate on the physical transaction log file.

Transaction log truncation (also called "log clearing") operates on the logical sections of the transaction log (VLFs), and does not affect the physical file size. This part is frequently the subject of confusion.

A log file must always grow to accomodate a large transaction; turning off auto-shrink will leave the log file with its maximum needed size, instead of physically decreasing its size.

If you don't have large transactions, it will be safe to disable auto-shrink; the log files will not grow without bound like would happen if the database was in FULL or BULK_LOGGED and you weren't taking transaction log backups.

This behaviour is the same for SQL Server 2005+.


ERROR 1146 (42S02): Table 'db.tablename' doesn't exist I have a two databases, when I prompt SHOW DATABASES I can see a list of the databases, including mysql, performance_schema and informations_schema.
I can prompt 'SELECT DATABASE_NAME' on both of them, I can prompt 'SHOW TABLES' on both of them, but when I try to query I get the error mentioned in the title of this question.
I use MySQL 5.7.7, and the InnoDB is set to FILE_PER_TABLE. All files are in the correct folder, including the ibdata1 and both of the log files.

Does anyone has a solution?

I think you might be able to find the answer in the following post:
https://stackoverflow.com/questions/7759170/mysql-table-doesnt-exist-but-it-does-or-it-should

Seems to have the same issue as you.


Saving sets of itemstacks in SQL I'm currently programming a minecraft survivalgames minigame network and am currently setting up a database for it. I have decided to store the possible maps for my spigot instances that run on my physical servers in a table. They will contain a reference to a chest set. Every chest set will contain an itemset, which is a selection of itemstacks, that also are stored in a table. This will make it possible to make the items in the chests be of a great variety and have them pretty much unpredictable.
So in total I now have 4 tables. The first one looks like this:

The CHESTSET row has the ID of the CHESTSET-Table as foreign key. I do this to make every map have an associated chestset with it. The chestset-table also has a row with the ID of it, and 5 Item-Sets, so in total you can define up to 5 different types of chests. These chests will contain random items of the itemset's that are referenced in them (the random selection will be done in my plugin, this is only about storing the selection possibilities).

Every itemset contains up to 10 items, that the contens of an individual chest on the map will be composed of.

The properties of the items themselves will be stored in yet another table, this table gives them an id, and then all the relevant properties, like the item-type, the display name, the subid, etc etc. This table gets pretty big, so I'll just post a dummy, which has all relevant properties.

Now here starts my problem. I've been able to add the ID-field of my CHESTSET-Table as a foreign key to my MAPS-Table. This makes sense to me because every map will have a set of predefined chest candidates.  I now planned to add the ID-column of my ITEMSETS-Table as foreign key to all 5 of my ITEMSET - X-rows, but I'm somehow not able to do that.
Am I thinking in the right way? For me, the multiple tables are a possibility to store multiple values in the place of one. So I store the items in a set, which  can be combined to form chest groups, which then are selected for each map. I think of the foreign key's as a way to basically "insert" the objects of another table in there. However, a column seems to be limited to a one time use as a foreign key.
What I want is that all of my 5 row's in  my CHESTSET-table are associated with an entry of the table ITEMSET, and likewise the entry's of the row's ITEM - x in my table ITEMSETS with an entry of the table CHESTITEMS. How can I achieve that, if  I cant make all of them a foreign key linked to the respective ID? Also I'm fairly new to Database design, so if this is a really dumb question, I apologize in advance.

The maps table looks pretty ok. The name should probably be unique so you have the possability to skip mapid. Also, I would use the identifier mapname instead of just name which is to vauge, and also highly likely to be a reserved word (I have not checked though). Alternative design:
CREATE TABLE maps
( mapname varchar(64) not null primary key
, max_no_players int not null
, min_no_players int not null
);

You may or may not introduce a surrogate key, but then the name should still be unique (my guess):
CREATE TABLE maps
( mapid int not null primary key
, mapname varchar(64) not null unique
, max_no_players int not null
, min_no_players int not null
);

Next, I would look at CHESTS. A chest seem to have no attributes, so I will just add a surrogate key:
CREATE TABLE chests
( chestid int not null primary key
);

A map can contain one or more chests:
CREATE TABLE chests_in_maps
( chestid int not null
, mapid int not null
,     constraint pk_chests_in_maps
          primary key (chestid, mapid)
,     constraint ...
          foreign key (chestid)
          references chests (chestid)
, <f.k. for maps>
);

I would then continue with ITEMS and ITEMS_IN_CHESTS in a similar way. Properties for ITEMS looks like it is attributes of an ITEM.
This is by no means a complete answer, but it hopefully gives you some ideas.


requested recovery stop point is before consistent recovery point I am getting below error while restoring Postgres instance to a point in time.

FATAL:  requested recovery stop point is before consistent recovery point

below is my recovery.conf content

restore_command = 'cp /var/lib/pgsql/pg_log_archive/%f %p'
recovery_target_time = '2018-04-17 10:56:27'
recovery_target_inclusive = false

I want to restore postgreSQL instance to a specified time.What am I doing wrong here?

You need to start with an earlier base backup than the one you are currently starting from.  PostgreSQL can only recover forward, not backward.


After new row INSERT statement, use newly created PK as FK in a second INSERT statement within the same Stored Procedure Using SQLExpress --

I have a table Orders that contains a PK of order_id. I have a second table Items that contains model and quantity along with a FK of order_id.

I am using a gridview to collect model/quantity data for an order and then I want to take that gridview data row by row and insert a new row into Orders, then insert it into the Items table, using the same order_id for each row of the Gridview. Problem is that the order_id doesn't exist until the first Insert is done.

After researching I had hoped I'd found a solution but it is not working as expected. I don't think the title of my question makes sense but I have no idea how to word it. Thanks in advance!

UPDATE: I have tried the edits suggested in the comments and referenced the link provided but still have not gotten my answer.

Below is my code:

Gridview Code:

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" HeaderStyle-HorizontalAlign="Center" HeaderText="Assigned" HorizontalAlign="Center" Width="346px">
<Columns>
    <asp:BoundField DataField="Manuf" HeaderText="Manuf" />
    <asp:BoundField DataField="Model" HeaderText="Model" />
    <asp:BoundField DataField="Qty" HeaderText="Qty" />
    <asp:BoundField DataField="Notes" HeaderText="Notes" />
</Columns>
</asp:GridView>

Code behind to call Stored Procedure:

 protected void CreateOrder_Click(object sender, EventArgs e)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["HMTDBCS"].ConnectionString;
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                foreach (GridViewRow g1 in GridView1.Rows)
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = con;
                    cmd.CommandText = "sp_ProdDataInsert";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@customer_id", TextBox1.Text);
                    cmd.Parameters.AddWithValue("@manuf_name", g1.Cells[0].Text);
                    cmd.Parameters.AddWithValue("@model_name", g1.Cells[1].Text);
                    cmd.Parameters.AddWithValue("@qty", g1.Cells[2].Text);
                    cmd.Parameters.AddWithValue("@notes", g1.Cells[3].Text);
                    cmd.Parameters.AddWithValue("@track_num", TextBox2.Text);
                    cmd.Parameters.AddWithValue("@pckg_num", TextBox3.Text);

                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();

                    MessageBox.Show("New Order Created!");
                    Response.Redirect("NewOrder.aspx");
                }
            }
        }

Stored Procedure Code:

ALTER PROCEDURE [dbo].[sp_ProdDataInsert]

@customer_id INT,
@manuf_name NVARCHAR (50),
@model_name NVARCHAR (50),
@qty NUMERIC,
@notes NVARCHAR (255),
@track_num NVARCHAR (50),
@pckg_num NUMERIC,
@order_id int OUTPUT AS

BEGIN
INSERT INTO Orders
    (customer_id, track_num, pckg_num)
VALUES
    (@customer_id, @track_num, @pckg_num)

SELECT @order_id = coalesce(MAX(order_id),0)+1 FROM Orders (UPDLOCK)
INSERT INTO Items
    (manuf, model, qty, notes, order_id)
VALUES
    (@manuf_name, @model_name, @qty, @notes, @order_id)

END

I think that if you just change this line it should work for you.

SET @order_id = SCOPE_IDENTITY();

Your method of grabbing the max id and adding 1 is not safe for concurrent access, scope_identity() is the correct way of getting the ID of the just inserted row.


count for missing values in group by We have a user table :

  CREATE TABLE `User` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `name` varchar(60) NOT NULL,
      `created_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `status` tinyint(4) NOT NULL DEFAULT '0',
    ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

I need to run a group by query where I need  a count of users with different status values (0, 1, 2, 3)

I tried following query :

select status, count(*)
from User
where
status in (0, 1, 2, 3)
group by status

this gives output for the status values present in the table e.g.

|    status            | count(*) |
|        0             |  10      |
|        1             |  11      |

but if the database does not contain let's say users with status values  = 2 or 3 these records don't appear with a count 0.

How do I go about this one ?

SELECT status_list.status, COUNT(User.status)
FROM ( SELECT 0 status
       UNION ALL
       SELECT 1
       UNION ALL
       SELECT 2
       UNION ALL
       SELECT 3 ) status_list
LEFT JOIN User ON User.status = status_list.status
GROUP BY status_list.status


Error ORA-12505 when trying to access a newly installed instance of oracle-11g express I encounter problems installing a local instance of oracle express on my Windows 7 - SP1 computer.
I did a basic installation, as administrator (run as 'administrator' on setup.exe).

Then I tried to connect with Oracle Sql Developer with the flollowing parameters :

Username : system
password : the one I provided during installation
hostname : localhost
role :sysdba
port : 1521 (didn't change the default one)
SID : XE.

And I always get :

  Statut : échec -Echec du test : Listener refused the connection with the following error:
  ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

My window user is in ORA_DB group, as well as the administrator user.

When I try to connect as sysdba in SQL command prompt, I got :

ORA-12638: Credential retrieval failed

I also tried to use servicename instead ('orcl'), but it failed with ORA-12514 instead.

My listener.ora looks like this:

  SID_LIST_LISTENER =
    (SID_LIST =
      (SID_DESC =
        (SID_NAME = PLSExtProc)
        (ORACLE_HOME = D:\Programmes\BDD\OracleExpress\app\oracle\product\11.2.0\server)
        (PROGRAM = extproc)
      )

  (SID_DESC =
        (SID_NAME = CLRExtProc)
        (ORACLE_HOME = D:\Programmes\BDD\OracleExpress\app\oracle\product\11.2.0\server)
        (PROGRAM = extproc)
      )

  (SID_DESC =
        (SID_NAME = XE)
        (ORACLE_HOME = D:\Programmes\BDD\OracleExpress\app\oracle\product\11.2.0\server)
      )
    )

  LISTENER =
    (DESCRIPTION_LIST =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
        (ADDRESS = (PROTOCOL = TCP)(HOST = MACHINE0138.com.company.intra)(PORT = 1521))
      )
    )

  DEFAULT_SERVICE_LISTENER = (XE)

The result of "lsnrctl status"

  LSNRCTL for 64-bit Windows: Version 11.2.0.2.0 - Production on 08-F╔VR.-2016 10:40:01

  Copyright (c) 1991, 2014, Oracle.  All rights reserved.

  Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
    STATUS of the LISTENER

  Alias                     LISTENER

  Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.2.0 - Production

  Start Date                08-F╔VR.-2016 10:37:59

  Uptime                    0 days 0 hr. 2 min. 6 sec

  Trace Level               off

  Security                  ON: Local OS Authentication

  SNMP                      OFF

  Default Service           XE

  Listener Parameter File   D:\Programmes\BDD\OracleExpress\app\oracle\product\11.2.0\server\network\admin\listener.ora

  Listener Log File         D:\Programmes\BDD\OracleExpress\app\oracle\diag\tnslsnr\MACHINE0138\listener\alert\log.xml

  Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=MACHINE0138.com.company.intra)(PORT=1521)))

  Services Summary...

  Service "CLRExtProc" has 1 instance(s).

Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...

  Service "PLSExtProc" has 1 instance(s).

Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

  The command completed successfully

And eventually, here's the log.xml file after my attempt :

<msg time='2016-02-08T11:17:55.181+01:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='MACHINE0138'
 host_addr='fe80::e958:8b9c:be6f:2610%11'>
 <txt>08-FÉVR.-2016 11:17:55 * (CONNECT_DATA=(SID=XE)(CID=(PROGRAM=SQL Developer)(HOST=__jdbc__)(USER=GLE10445))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=58750)) * establish * XE * 12505
 </txt>
</msg>
<msg time='2016-02-08T11:17:55.185+01:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='MACHINE0138'
 host_addr='fe80::e958:8b9c:be6f:2610%11'>
 <txt>TNS-12505: TNS:listener does not currently know of SID given in connect descriptor
 </txt>
</msg>

Any tip welcolmed, indeed.
And thanks in advance for reading until this here!

you should create at least one database user that you will use to create database objects. A database user is a type of database object: a user is associated with a database schema, you connect to the database as a database user, and the database user is the owner of any database objects (tables and so on) that you create in the schema associated with the user.

For example, to create a database user named chris(For Example). Follow these steps, using the command line:

Display the SQL command prompt window. For example, on Windows, click Start, then Programs (or All Programs), then Oracle Database 11g Express Edition, and then Run SQL Command Line.

Connect as the SYSTEM user:

Type: connect
Enter user-name: system
Enter password:
Create the user. For example, enter a statement in the following form:

SQL> create user chris identified by <password-for-chris>;

Grant the user the necessary privileges. For example:

SQL> grant CREATE SESSION, ALTER SESSION, CREATE DATABASE LINK, -
  CREATE MATERIALIZED VIEW, CREATE PROCEDURE, CREATE PUBLIC SYNONYM, -
  CREATE ROLE, CREATE SEQUENCE, CREATE SYNONYM, CREATE TABLE, -
  CREATE TRIGGER, CREATE TYPE, CREATE VIEW, UNLIMITED TABLESPACE -
  to chris;

Optionally, exit SQL*Plus (which also closes the command window):

SQL> exit

SQL Developer as an Alternative for Creating Database Users:

If you have experience with SQL Developer, you can use it instead of the command line to create a database user, as follows:

Create a database connection for the SYSTEM user.

Open that database connection for the SYSTEM user.

Right-click the Other Users node in the Connections navigator under that connection.

Select Create User, and specify the necessary information. (Under System Privileges, grant ALTER SESSION, CREATE SESSION, CREATE DATABASE LINK, CREATE MATERIALIZED VIEW, CREATE PROCEDURE, CREATE PUBLIC SYNONYM, CREATE ROLE, CREATE SEQUENCE, CREATE SYNONYM, CREATE TABLE, CREATE TRIGGER, CREATE TYPE, CREATE VIEW, and UNLIMITED TABLESPACE.)

I am also attaching the screen shot with example , which is given by Oracle BOL for SQL Developer connection.

For your ref Here And Here.


Recursive Query in MySQL using stored proceedure and CURSOR I'm expanding our user/group system to allow for dynamic groups which are made up of other groups members.  I have three tables users, groups, and relationships.

For simplicity sake, lets say users only contains one field user_id, and groups only contains group_id.  relationships uses three fields: user_id, group_id, related_group_id to represent both user to group and group to group relationships.

A dynamically populated group hierarchy would be something like:

Users A, B, C belong to Group 1 and Users D, E, F belong to Group 2. All of these are represented in the relationships table as (user_id,group_id):

A,1
B,1
C,1
D,2
E,2
F,2

The dynamic Group 3 would have two group to group relationship records (group_id,related_group_id):

3,1
3,2

To illustrate my problem better lets add a Dynamic Group 4 and have it include Group 3:

4,3

Piecing together what I've learned of MySQL CURSOR's, I've created the following stored procedure called fetch_inheritance_groups:

DROP PROCEDURE IF EXISTS `fetch_inheritance_groups`;

CREATE PROCEDURE `fetch_inheritance_groups`(IN parent_id INT)
    READS SQL DATA
    BEGIN

    DECLARE inherit_id char(32);
    DECLARE eol BOOLEAN;
    DECLARE inherit_cur CURSOR FOR SELECT r.Related_Group_ID FROM usr_relationships r WHERE r.Group_ID = parent_id AND r.Related_Group_ID IS NOT NULL;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET eol = TRUE;

    OPEN inherit_cur;
    inherited: LOOP
        FETCH inherit_cur INTO inherit_id;

        IF eol
            THEN
            CLOSE inherit_cur;
            LEAVE inherited;
        ELSE
            CALL fetch_inheritance_groups(inherit_id);
        END IF;

        SELECT inherit_id;

    END LOOP inherited;

END;

...which is called with...

CALL fetch_inheritance_groups(4);

...and returns the expected results of

3
2
1

Herein begins my problem, they're returned as three separate result sets rather than three rows in a single result set, and secondly, the results are meant to be used within a query like:

SELECT r.uer_id FROM relationships r WHERE r.group_id = 4 OR r.group_id IN (CALL fetch_inheritance_groups(4));

So the three questions I'm hoping someone can answer are:

1. Should I be using a CURSOR here or is there an alternative that will get the same recursive result?

2. How can I get the results back in a single result set so that it can be used in the same fashion as a subselect?

3. What is the proper way of using the results from the CALL because I can't at least as far as I've tried get the sample SELECT statement above to work?  I believe this is because I can't use CALL inline but I'm not sure.

UPDATE: In a related question here: How long will a temporary MEMORY table persist if I don't drop it (MySQL) I posted a set of updates to the stored proceedure used above which is still recursive, but provides the list of ID's back as a variable, and in a format compatible with FIND_IN_SET() giving me EXACTLY what I needed.

So the three questions I'm hoping someone can answer are:

1.Should I be using a CURSOR here or is there an alternative that will get the same recursive result?

No, you should not. The CURSOR keep being opened over and over. Thinking of the overhead make me cringe. Personally, I stay away from CURSORs.

2.How can I get the results back in a single result set so that it can be used in the same fashion as a subselect?
3.What is the proper way of using the results from the CALL because I can't at least as far as I've tried get the sample SELECT statement above to work? I believe this is because I can't use CALL inline but I'm not sure.

I would like to bail you out of this by suggesting something I learned from my college days when learning about data structures. You need to perform tree traversal using a queue. This allows you to start at a root and express all descendants of a tree.
The algorithm goes like this

STEP 01 : Start with an empty queue
STEP 02 : Dequeue Node From the Front of the Queue
STEP 03 : Enqueue All Children of the Latest Node
STEP 04 : Process Info From the Latest Node
STEP 05 : If the Queue is Not Empty, Go Back to STEP 02
STEP 06 : All Done

This allows you to traverse a recursive structure without using Programmatic Recursion. At this point, you are probably asking: How can I traverse a tree structure without recursion?
I wrote a post about how to script three Stored Procedures that can using a loop in a single CALL that will traverse a table with nodes and its parent in a table:

GetParentIDByID
GetAncestry
GetFamilyTree

The post is Find highest level of a hierarchical field: with vs without CTEs (Oct 24, 2011). It contains the Stored Procedures already written that will traverse the following table structure:
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| parent_id  | int(11)      | YES  |     | NULL    |                |
| name       | varchar(255) | YES  |     | NULL    |                |
| notes      | text         | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

Please read the code carefully and apply the principles.
Give it a Try !!!


AWS VS Azure , which one is preferable for SQL Server 2012 standard version? I want few recommendations while considering azure vs aws for our current database which is SQL Server 2012 standard edition.
Currently we are using AWS RDS for managing our database, recently we had been considering active read replica for our heavy prod database, but it seems like aws doesn't have read replica option for MS SQL Server , but only CDC(change data Capture ) option , that doesn't solve our needs because CDC involves manual loading of every other thing except for tables and their primary indexes, whereas for Aurora or mySQL , they have proper read replica options.
Hence we want to know if MS Azure will be better for us in this case.

Thanks in advance!

With Azure SQL Database you have the option of Geo-Replication. Active geo-replication enables you to configure up to four readable secondary databases in the same or different data center locations (regions). Secondary databases are available for querying and for failover if there is a data center outage or the inability to connect to the primary database. The failover must be initiated manually by the application of the user. After failover, the new primary has a different connection end point. You can learn more about this Azure SQL Database feature here.