Listing incident categories I have a need to keep a list of incidents and then assign root cause reasons from a few different static lists.

I'm using MySQL at the moment and the layout I have is as follows:

+----+---------+----------------+----------------+-------+----------------------+----------------+-------------+---------------------+---------------------+
| id | case_id | categories1    | categories2    | notes | reps                 | created_by     | modified_by | created             | modified            |
+----+---------+----------------+----------------+-------+----------------------+----------------+-------------+---------------------+---------------------+
| 45 |    3640 | 10,28          | 3,6,8          | NULL  | person1,person2      | admin          | admin       | 2017-02-15 16:49:42 | 2017-02-20 12:22:18 |
| 46 |    3641 | 2,22,10,29     | 2,3            | NULL  | NULL                 | admin          | admin       | 2017-02-16 10:46:40 | 2017-02-20 16:53:28 |
| 47 |    3642 | 9,10,28        | 1,2            | NULL  | NULL                 | admin          | admin       | 2017-02-16 12:04:24 | 2017-02-22 14:29:14 |
| 48 |    3643 | 39,40,10,37,31 | 8              | NULL  | NULL                 | admin          | admin       | 2017-02-16 12:41:53 | 2017-03-06 14:55:10 |

From what I've read on database design, having the records as comma separated values isn't considered best practice. Taking into consideration that the reports run on these records will be only dealing with a couple of thousand records at any time, is it worth changing the table? If so, can someone give me a suggestion as to a better layout or point me in the right direction?

The usual design is to relocate each column with repeating values into its own table.

You don't say, but I'll call your current table Incidents, and I'll assume the Id is the primary key.

Next create a table for each repeating-value column and link them back to Incidents. For example

IncidentCategories1
  IncidentId
  CategoryID

Values will be (45, 10), (45, 28), (46, 2), (46, 22) etc. Similarly with IncidentCategories2. Reps column becomes

IncidentReps
  IncidentId
  RepName

with values (45, person1), (45, person2) etc. And and so on for the other columns with repeating values.

As for your question about whether it is worth it, well, that's quite subjective. You are using a relational database. The assumption is data is normalised. Your current design isn't. It makes sense to give the tool what it expects to receive, unless there are overwhelming reasons to do otherwise. The only one I can think of is that you only ever treat those repeating groups as a whole and never parse them (in SQL). In that case they can be considered strings that just happen to have commas at certain positions. For this your current design is fine.


Basic sql query problem Here is the scenario: lets say I have a database named sqlprac that contains a table named dept.

There are two ways to write a query:

use sqlprac
/*1*/
SELECT dept.deptno,
       dept.deptname,
       dept.deptsal
FROM   dept;

/*2*/
SELECT deptno,
       deptname,
       deptsal
FROM   dept;

Both queries should return the same result set. However I have seen that this isn't always the case, especially in sub query/inner query. What's the difference then?

Why should I choose one over another?

Also, a third query might be

/*3*/
SELECT *
FROM dept

I know what * is doing, but in which case we should or not use it.

Are there any performance/optimization issues?

Both your queries are shorthand for this:

SELECT dept.deptno AS deptno,
       dept.deptname AS deptname,
       dept.deptsal AS deptname
  FROM dept AS dept;

SQL is sometimes forgiving and will imply the AS clauses and correlation names, other times it requires them for no logical reason e.g.

SELECT deptno
  FROM dept JOIN emp USING (deptno);

will complain that deptno is ambiguous

SELECT deptno
  FROM (SELECT deptno FROM dept);

will complain that the derived table has no correlation name.

To answer the question: would would include, omit or revise the default correlation name to make the code to aid the human coder -- easier to read and understand means it will be  easier to maintain -- but tastes vary (when the same table is involved in a query more than once then you need to used an alternative correlation name to disambiguate for the purposes of the parser, obviously).


Is it critical for a database to have a relationship table/diagram or not? Got a question and wanted to get the best practices or thoughts on this.

I have an application that was developed with a SQL Server back end (site is Entity framework, vb.net, asp.net). The original design does not have a database diagram/schema/relationship table in the db itself.

The site works great but have gotten some pushback from another group that says it has to have a relationship table in order to work properly.

So is this personal preference or what? I'm not a DB guru and trying to find out if it's two ways of development and someone is trying to push personal preference over standards or best practices.

You don't need a diagram for it to work, you need one to train the future developers!

However, if what you really have is a database with no actual formal PK/FK relationships set up (Which you can do when creating diagrams as well as through better ways such as SQL scripts), then that is a different story. That is something that will eventually cause your data to be inconsistent and is a real problem indeed to other users of the database.


Can I Upgrade SQL Server 2008 R2 Express to SQL Server 2014 Developer edition I currently have SQL server 2008 R2 Express installed for my QA/Dev environment on Amazon EC2 instance.

Now my database is growing beyond the 10GB limitation of Express edition, so am planning to buy SQL server 2014 Developer edition.

So can i upgrade my SQL Express 2008 R2 to SQL server 2014 Developer Edition, if yes what is the procedure?

No you cannot do inplace upgrade from SQL server 2008 R2 express to SQL server 2014 developer edition. Its not supported as per Version and edition upgrade supported matrix

You can only upgrade to below using inplace upgrade method

  SQL Server 2014 Enterprise

  SQL Server 2014 Business Intelligence

  SQL Server 2014 Standard

  SQL Server 2014 Web

  SQL Server 2014 Express

But there is other option to do side by side migration. You install SQL server 2014 DEV edition. You backup your user database from SQl server express and restore it on SQl Server 2014.

You can migrate logins using This support article


How can I import/export a MySQL database without redirection? I've been told that redirection via shell (e.g. < and > ) can (not always) affect the collation of a database (.e.g utf8). How can I import an export without redirecting so that I don't have to worry about this.

There's always the mysqlimport utility, however, your source would have to be delimited values... such as those obtained from a "SELECT * FROM ... INTO OUTFILE ..."

If you're moving large amounts of data, it might be faster to use this bulk loading method anyway.

Beware of the consequences it may have with replication however.


ALTER DATABASE SET ALLOW_SNAPSHOT_ISOLATION ON takes forever One of vendor software needed to install on a test server for POC. It takes forever when the setup.exe is trying to alter one of the databases with SET ALLOW_SNAPSHOT_ISOLATION ON.

The test server was on Microsoft SQL Server 2016 (SP1-CU8), developer edition.

After rolling back to SP1-CU5, SET ALLOW_SNAPSHOT_ISOLATION ON worked.

Has anyone experienced the same behavior? Any work around besides rolling back the CU?

There was no blocking and no other users at all. POC is in isolated lab environment. Tried on 2 other dev SQL Servers in live environment with 2016 SP1 CU7, experienced same behavior.

I ran into this exact situation and can confirm there was no blocking in my case as the setup basically altered the snapshot isolation immediately after creating the database.

In my case the wait type was ENABLE_VERSIONING.

When investigating I found this feedback item Enabling snapshot isolation in 2016 SP1 CU6 and CU7 hangs which is still under investigation.

One of the comments seem to suggest a SERVER AUDIT could be the case.

If you don't have a server audit running I'm afraid your option for now will be to roll back to an older CU and watch that feedback item for updates.


MySQL replication constantly checking tables I recently moved one of our slave databases to a new server, the database is purely used for offsite replication/backup and doesn't handle any of the live traffic.

Since moving, the new server seems to constantly want to run CHECK TABLE across the whole database. We've got a a few large tables, one being ~10GB, so it locks everything up for a few minutes while it's checking the table and in result queues up the queries.

I've looked at possible causes as to why it needs to check so often, but can't seem to find a reason.

I was wondering if anyone here has experienced this, or would know of a reason?

If your database tables are MyISAM, you might have given the server option myisam-recover in your my.cnf, which will turn on automatic MyISAM checking.

  If recovery is enabled, each time mysqld opens a MyISAM table, it checks whether the table is marked as crashed or was not closed properly.


Unused Indexes Best Practices Based on this query, if I see a low amount of total reads (very close to 0 or 0, like 1 or 2) and a high or moderate amount of user updates (I couldn't find inserts or deletes with this query) with a large row count, I should in theory remove the index.

SELECT DISTINCT
    OBJECT_NAME(s.[object_id]) AS ObjectName
       , p.rows TableRows
       , i.name AS [INDEX NAME]
       , (user_seeks + user_scans + user_lookups) AS TotalReads
       , user_updates UserUpdates
FROM sys.dm_db_index_usage_stats s
    INNER JOIN sys.indexes i ON i.[object_id] = s.[object_id]
        AND i.index_id = s.index_id
    INNER JOIN sys.partitions p ON p.object_id = i.object_id
WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1
       AND s.database_id = DB_ID()
       AND i.name IS NOT NULL
ORDER BY (user_seeks + user_scans + user_lookups) ASC

I want to cross-check the accuracy of this assumption here.  For instance, an index that has existed for over a year but has never been read, but highly updated, seems like it would be a bad idea to have.  Is there a scenario where this assumption is invalid?

This DMV only maintains statistics since the last SQL Server restart; the view gets wiped out completely and everything starts from scratch.

More importantly, the rows in this view for any specific index are removed when that index is rebuilt (but not when it is reorganized). If you are performing regular index maintenance, it might be useful to look at the maintenance logs and see if any of the indexes you're considering dropping might have been rebuilt recently.

So, making decisions based on low reads since the last restart, when the last restart may have been for last week's patch Tuesday updates or yesterday's service pack, might not be wise. Or when you are performing index maintenance, since the last rebuild. There may be a report that is only run once a month, or once a quarter, or once a year, and it is run by an important and impatient person.

Further, an index might be there for something that will happen in the future that you don't know about - a series of reports being prepared for tax season, let's say.

So, my advice is:

Use the DMV to identify candidate indexes to remove, but don't make that decision in a bubble - you need to do the legwork to determine why an index might exist before dropping it, even if it looks like it isn't currently being used.


Why query optimizer doesn't use negative filter index I have a table that has following structure, as you can see, there are one clustered index and two non-clustered indexes, one non-clustered index is IX_ParentId_Include, and another one is FLIX_ParentId_Include

they are same apart from the second index is filtered.

CREATE TABLE [dbo].[PhotoRepo] (
    [PhotoRepoId]              INT            IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL,
    [TypeId]                   TINYINT        NULL,
    [ParentId]                 INT            NULL,
    [RemoteLocation]           VARCHAR (4000) NOT NULL,
    [UploadedAt]               DATETIME       NOT NULL,
    [UploadedBy]               VARCHAR (255)  NULL,
    [FileSize]                 INT            NOT NULL,
    [DefaultChild]             BIT            NOT NULL,
    [RemoteLocationUploadedAt] DATETIME       NULL,
    CONSTRAINT [PK_FileList] PRIMARY KEY NONCLUSTERED ([PhotoRepoId] ASC)
);

GO
CREATE CLUSTERED INDEX [IX_PhotoRepoId]
    ON [dbo].[PhotoRepo]([PhotoRepoId] ASC);

GO

CREATE NONCLUSTERED INDEX [IX_ParentId_Include]
    ON [dbo].[PhotoRepo]([ParentId] ASC)
    INCLUDE([RemoteLocation], [DefaultChild], [TypeId]);
GO

CREATE NONCLUSTERED INDEX [FLIX_ParentId_Include]
    ON [dbo].[PhotoRepo]([ParentId] ASC)
    INCLUDE([RemoteLocation], [DefaultChild], [TypeId])
    WHERE TypeId <> 7;

GO

Now when I run this query with execution plan enabled, the query optimizer doesn't use filter index, which I expect it should be more efficient one, it uses IX_ParentId_Include. It uses filter index only when i either force it to, or delete IX_ParentId_Include

Anyone knows why?

SELECT
    *
FROM   PhotoRepo
WHERE
    typeid <> 7 and
    parentid = 123

PS: there is 30% rows that have typeid = 7

Why do you think it should use the filtered index? You've used SELECT * in your query and your predicate involves more than just the filter condition. SQL Server is going to have to look up those columns in another index that covers it anyway (your SELECT * plan should include a key lookup to retrieve those other columns).

SQL Server will decide based on stats/cardinality (e.g. how many rows currently match the filter, how many rows total) and decide at that point which index it should use. In this case, it is probably a coin flip, because it can seek to the 19 rows that match the ParentId predicate, and not cost any more in I/O to do so. It might choose the filtered index if it represented a much smaller portion of the table than 30%. You should see what it does, though, if you add OPTION (RECOMPILE) to the query. Depending on your parameterization settings, it may be using a plan that was cached with a different parameter value (e.g. one that doesn't match the index filter).

In this case, I believe you are seeing this behavior because, well, RTM. Please install Service Pack 3 and consider Cumulative Update 16. I browsed quickly and didn't see anything relating to filtered indexes, but this could be a by-product of some other optimizer wonkiness that has been fixed in the meantime. In any case, I can't really think of any good reason why you should continue running RTM at this time.

Could also be related to these are other "bugs known gaps in functionality":

Connect #329459 : SQL does not use filtered index on a simple query
Connect #454744 : Filtered index not used and key lookup with no output
Connect #643850 : Filtered Index execution plan is not optimized

I've also blogged about a whole slew of limitations with filtered indexes, and pointed to a ton of related Connect items. Most of these remain unfixed and, for the most part, filtered indexes have received absolutely no attention since they were first introduced. If you're going to use these objects, and expect them to be used to speed up your queries, you'd be wise to familiarize yourself with all of these limitations, and consider using an index hint to force them to be used in some scenarios. Caveats with using an index hint, of course:

It may not work better (you don't always know better than the optimizer); it may work better for some queries but not all. Usually SQL Server will use the filtered index if (a) it can, and (b) it deems it to be the most efficient access method.
Queries will fail if the index is dropped, or re-created with a new name.


Why does a WHERE clause with an indexed, computed datetime column on an indexed view seek the clustered index on the original table First, sorry for the long title.

The setup

I reduced the example to the minimum, to make it clearer, so there is no meaningful semantic anymore.

DBMS: Azure SQL Database V12.

Assume there is following table:

CREATE TABLE [dbo].[MuchDataTable](
    [SmallDateTimeColumn] [smalldatetime] NOT NULL
)

GO
CREATE CLUSTERED INDEX [IX_MuchDataTable_SmallDateTimeColumn] ON [MuchDataTable] ([SmallDateTimeColumn])

Now I want to get aggregated data specific for a range of an arbitrary amount of days. As the table contains a lot of rows (over 150 Mio.) I created an indexed view, which looks basically like following:

CREATE VIEW [dbo].[AggregatedDateView]
    WITH SCHEMABINDING
AS
    SELECT CONVERT(DATE, [SmallDateTimeColumn]) AS [DateColumn], COUNT_BIG(*) AS [Count]
    FROM [dbo].[MuchDataTable]
    GROUP BY CONVERT(DATE, [SmallDateTimeColumn])

GO
CREATE UNIQUE CLUSTERED INDEX [IX_AggregatedDateView_Date] ON [dbo].[AggregatedDateView] ([DateColumn])

For my day grouping I convert each DateTime to a Date. Then I create an clustered index over the view.

The problem

Executing a simple SELECT * over the view scans the index of the view. That's fine:

However, if I query with a WHERE constraint on the computed date column, a clustered index seek on the original table is performed:

The question

My understanding is, that there should be a B-Tree consisting of all converted dates for the indexed view. Why does SQL Server choose to scan the clustered index of the underlying table? Is this not possible with an indexed view and I have to build a solution atop of triggers and a normal table?

Edit: for more clarification, it becomes a real problem, once a range of dates is scanned, so something like

SELECT * FROM [AggregatedDateView] WHERE [DateColumn] > '2016-05-20' AND [DateColumn] < '2016-08-20'

Here the index over 3 months is scanned, which is in my case already a lot.

SQL Server expands the view and considers accessing the base tables instead. If the view or the base tables are used is a cost based decision by the optimizer.

To force SQL Server to use your indexed view you should use the noexpand hint.

SELECT *
from [AggregatedDateView] WITH(NOEXPAND)
where [DateColumn] > '2016-05-20' AND
      [DateColumn] < '2016-08-20'


Difference between "and" and "&" I'm trying to understand the order of precedence for logical operations and have the following code:

declare @T bit ='TRUE'
declare @F bit ='False'

print @T and @F

and it returns an error as

  Incorrect syntax near the keyword 'and'.

I replaced 'and' with '&', and the code works again. Why didn't the former code work?
I'm using SQL server.

print @T & @F

Returns 0

& is the Bitwise AND operator.

  The & bitwise operator performs a bitwise logical AND between the two
  expressions, taking each corresponding bit for both expressions. The
  bits in the result are set to 1 if and only if both bits (for the
  current bit being resolved) in the input expressions have a value of
  1; otherwise, the bit in the result is set to 0.

In your case @T & @F resolves to 1 & 0 and so returns a result of datatype BIT with value 0

When passed to the PRINT operator this bit result is implicitly cast to string and the result output to the client.

print @T and @F

Has quite a lot wrong with it.

AND

  Combines two Boolean expressions and returns TRUE when both expressions are TRUE

bit is not the same as boolean. They are not interchangeable and SQL Server won't implicitly cast bit to a boolean datatype when needed (SQL Server does not implement the SQL Boolean datatype.).

So you would need to use an expression like

@T = 'TRUE' AND @F = 'TRUE'

instead of

@T and @F

Even then your problems aren't over - PRINT doesn't accept a boolean expression anyway. You could use the expression in CASE as below.

PRINT CASE
          WHEN     (@T = 'TRUE' AND @F = 'TRUE') THEN 'True'
          WHEN NOT (@T = 'TRUE' AND @F = 'TRUE') THEN 'False'
          ELSE 'Unknown' -- SQL uses three valued logic
      END


Converting varbinary data to varchar in SQLServer leads to unexpected results I need to fix some data conversion issue in SQL server 2008. I got some requirement change on storing the data. Without much care I converted existing varbinary data to varchar using CONVERT(NVARCHAR(max), @bytearraydata, 1).

The same conversion in C# is done using Encoding.Default.GetString and Encoding.Default.GetBytes methods. Encoding.Default.GetBytes(string) gets back the bytearray as it was earlier. But when I try to get back the byte array of string which I converted using CONVERT()  gives me a wrong result.

My work is to fetch byte array stored as string the database and convert it to byte array and finally render the content as PDF. Data going through encoding mechanism (while saving and while fetching) works fine for me. But when I try to fetch the data which was converted using CONVERT it is failing to generate PDF.

How can I resolve this problem?

Summary:

Byte array column has been changed to string.

Existing data conversion done using this function:

Convert(NVARCHAR(MAX), @bytearraydata, 1)

In the application byte array conversion is done using Encoding.Default.GetString(bytearraydata)

Are Encoding and CONVERT not compatible?

There are a few of confusions going on in the question, leading to the unexpected results:

The terms VARCHAR and NVARCHAR are being used interchangeably (or so it seems), yet they are quite different. NVARCHAR is a 16-bit encoding – UTF-16 LE (Little Endian) to be exact – and this does not change. VARCHAR is an 8-bit encoding, and the specific 8-bit encoding being used is determined by the Code Page associated with the Collation of the column (we will ignore VARCHAR data in string literals and variables for now since this question is about data stored in a table). If you want to know what Code Page is associated with a particular Collation, you can use the COLLATIONPROPERTY built-in functions:

SELECT COLLATIONPROPERTY(N'Latin1_General_100_CI_AS_SC', 'CodePage') AS [CodePage];
-- 1252

When converting between VARBINARY and either VARCHAR or NVARCHAR you need to be careful to be consistent with that string datatype. You can't convert from VARCHAR to VARBINARY and then take that same VARBINARY and convert it to NVARCHAR.
The Encoding class in .NET represents a particular encoding of text, whether it be 7-bit, 8-bit, 16-bit, 32-bit, or variable (such as UTF-8). In order to get "expected" results, you need to create an encoding that matches what you need to convert to or from, with respect to the byte[] representation. Strings in .NET are always UTF-16 LE (same as NVARCHAR ), and this is what the Unicode encoding in .NET refers to. The byte[] representation of an encoding will be of whatever encoding was created, but the string representation will always be UTF-16 LE. So, which encoding to create depends on what type of data you are dealing with:

NVARCHAR: Use Encoding.Unicode
VARCHAR: Determine the Code Page of the Collation via COLLATIONPROPERTY(N'collation_name', 'CodePage') and then use that int value in Encoding.GetEncoding(CodePageIntValue).

When using the CONVERT built-in function, be careful which "style" number you are using. For example:

SELECT CONVERT(VARBINARY(50), N'bob');
-- 0x62006F006200

Now take that returned VARBINARY value and convert it back into NVARCHAR, using "style" values of 0 (default) and 1 (which is what your CONVERT function is using in the question):

SELECT CONVERT(NVARCHAR(MAX), 0x62006F006200, 0) AS [Style_0],
       CONVERT(NVARCHAR(MAX), 0x62006F006200, 1) AS [Style_1];

Returns:

Style_0        Style_1
bob            0x62006F006200

So, if the following statement from the question is true:

  In the application byte array conversion is done using Encoding.Default.GetString(bytearraydata)

then that would equate to using VARCHAR instead of NVARCHAR, and a "style" value of 0 (or nothing) instead of 1:

CONVERT(VARCHAR(MAX), 0x62006F006200)


Is there any way to have Unique Constraint and NOT have Unique Index? Looking into the Microsoft documentation, it says when a unique constraint is created, a unique index is created automatically.

Is there a trick/workaround to have a unique constraint and NOT have a unique index, so it does not take space?

Reasonable Answer: No.

You really should just let it create the unique index and let it do it's job.  Space is cheap.

If you make the column you want unique the clustered index key, then it won't cost you any additional disk space, but it may slow down access to the table if your queries don't go through the unique column.

Less Reasonable Answer: Yes.

But it burns CPU, increases disk I/O and slows down just about everything.

You put an AFTER trigger on the table and have it count the number of rows in the base table for the column you want to make unique.  If the count is greater than 0 then rollback the transaction.

Example (of how not to do it):

CREATE TABLE dbo.Test
    (
    TestID INT NOT NULL PRIMARY KEY IDENTITY(1,1)
    , TestValue VARCHAR(100) NOT NULL
    );

GO

CREATE TRIGGER trg_TestInsert ON dbo.Test AFTER INSERT, UPDATE
AS
BEGIN

    DECLARE @DuplicateCount INT;
    SET @DuplicateCount = 0;

    WITH CTE_Count AS
        (
        SELECT TestValue, COUNT(*) AS TestValueCount
        FROM dbo.Test
        GROUP BY TestValue
        )
    SELECT @DuplicateCount = COUNT(TestValue)
    FROM CTE_Count
    WHERE TestValueCount >=2;

    IF @DuplicateCount >= 1
    BEGIN
        ROLLBACK;
    END

END;

GO

/** Test Insert Statement */
INSERT INTO dbo.Test (TestValue)
VALUES ('TestMe');

INSERT INTO dbo.Test (TestValue)
VALUES ('TestYou');

--HINT: This one will fail.
INSERT INTO dbo.Test (TestValue)
VALUES ('TestYou');

SELECT TestID, TestValue FROM dbo.Test;

--Cleanup.
DROP TABLE dbo.Test;


Status show SSL is in use even though I did not set up certificate on the client side? I want to set up SSL connections to a distant MariaDB server. I followed the instructions here. I generated the certificate for the server, restarted it, and got this:

+---------------------+-------------------------------------------------+
| Variable_name       | Value                                           |
+---------------------+-------------------------------------------------+
| have_openssl        | YES                                             |
| have_ssl            | YES                                             |
| ssl_ca              | /var/packages/MariaDB10/etc/ssl/ca-cert.pem     |
| ssl_capath          |                                                 |
| ssl_cert            | /var/packages/MariaDB10/etc/ssl/server-cert.pem |
| ssl_cipher          |                                                 |
| ssl_crl             |                                                 |
| ssl_crlpath         |                                                 |
| ssl_key             | /var/packages/MariaDB10/etc/ssl/server-key.pem  |
| version_ssl_library | OpenSSL 1.0.2r-fips  26 Feb 2019                |
+---------------------+-------------------------------------------------+

I also generated the client certificate. However, to make sure I understand the process, I proceeded step by step, and did not install them. I first changed the user settings to REQUIRE SSL. Then

mysql --user=user --password=password

fails with a

ERROR 1045 (28000): Access denied for user 'user'@'host' (using password: YES)

So far so good. However,

mysql --user=user --password=password --ssl

succeeds, even though I did not provide any client certificate. status shows

SSL:                    Cipher in use is DHE-RSA-AES256-GCM-SHA384

which, if I understand correctly, means that the connection is indeed using SSL. How is that possible, is it the expected behavior?

I am using MariaDB 10.3.

REQURE SSL only requires a TLS connection. Use REQUIRE X509 to enforce a TLS client certificate, or better, use REQUIRE SUBJECT or REQUIRE ISSUER to ensure that is the identical certificate that matches the one issued.


What's the best way to use a gist index on tsrange to check if a time occurs after the range? table (simplified)

                                        Table "public.events"
      Column      |            Type             |                       Modifiers
------------------+-----------------------------+--------------------------------------------------------
 id               | integer                     | not null default nextval('events_id_seq'::regclass)
 duration         | integer                     | not null
 start_at         | timestamp without time zone |
Indexes:
    "events_pkey" PRIMARY KEY, btree (id)
    "my_idx" gist (tsrange(start_at, end_at(events.*), '[)'::text))

function

CREATE FUNCTION end_at(rec events)
  RETURNS timestamp without time zone
  IMMUTABLE
  LANGUAGE SQL
AS $$
  SELECT $1.start_at + ($1.duration * ('00:00:01'::interval));
$$;

what I am already doing successfully

The index is used for queries like this:

-- check if current time is within the start and end times
-- of event
where localtimestamp <@ tsrange(start_at, events.end_at, '[)')

And it works well.

what I want to do

I want to query for events where the current time is after they have ended. Ways I know of how to do this:

where tsrange(localtimestamp, localtimestamp, '[]') >> tsrange(start_at, events.end_at, '[)'). I'm pretty sure this is the semantics I want, and explain analyze says it's using the index, but it's a bit ugly and I'm wondering if there's a better way to express this (and also am vaguely uncertain it's the semantics I want, as I am new to ranges).
where localtimestamp > upper(tsrange(start_at, events.end_at, '[)')) + a btree index on upper(tsrange(start_at, events.end_at, '[)')). This will work well, but requires keeping another index around.
where localtimestamp > events.end_at. + a btree index on events.end_at. Same situation as above.

Is there a more elegant (or correct) way to achieve the first bullet point above?

Any other ideas for how to go about this?

Postgres doesn't support any operators for this. As pointed out by @evan-carroll, it probably could and should.

So, the best solution is

where tsrange(localtimestamp, localtimestamp, '[]') >> tsrange(start_at, events.end_at, '[)')


Select only last row from table for list of values As example we have a table:

CREATE TABLE t1 (
   a BYTEA NOT NULL,
   r_pointer BIGINT,
   s_pointer BIGINT,
   data BYTEA,
   PRIMARY KEY(a, r_pointer)
) PARTITION BY HASH (a);

In case we I want get last element:

SELECT * FROM t1 WHERE a ='\xaaa' order by r_pointer desc limit 1;

Of course, I can use executemany for this statement, but this is not very good for performance.

How can I get only the first row found for each element in a given array like {\xaaa,\xbbbb}. Similar to the following, but this returns all rows:

SELECT * FROM t1
WHERE  a = ANY ('{\xaaa,\xbbbb}'::bytea[])
ORDER  BY r_pointer DESC;

For big tables, modern PostgreSQL (your example suggests Postgres 11, since PKs are not supported for partitioned tables before that), and many input values, this should perform excellently:

SELECT t1.*
FROM   unnest('{\xaaa,\xbbbb}'::bytea[]) a
CROSS  JOIN LATERAL (
   SELECT *
   FROM   t1
   WHERE  t1.a = a.a
   ORDER  BY t1.r_pointer DESC
   LIMIT  1
   ) t1;

Your PK on (a, r_pointer) provides just the index needed for this.

Related:

How to properly implement compound greatest-n filtering


How to store user defined scoring formulas? First post, so apologize if I have not chosen the best spot for it:

If I had table of values, let's say corresponding to a question number and its corresponding answer, what is the correct way to plan a database to allow users to store formulas to calculate a score based on said answers?

Example:

Let's say I have a table of answer given to a series of questions:

Question_and_answer_table

ID   Answer
 1       3
 2       5
 3       7
 4       9

Then I have 3 different users, User1, User2, User 3.

If I want each user to be able to define how they want to weigh the answers to determine a final score

  ex: User1: A1*3-A2*2+A3*7*A4=SCORE

what's the best way to handle storing a formula for each user?

Thank you for your time and help!

Cheers

You can have a table of weights -- that is, an association of user with each question and how much that answer is weighted. Then you can give each user their own weight for each answer.

create table Weights(
    UserID     int not null references Users( ID ),
    AnswerID   int not null references Answers( ID ),
    Weight     int default 1, -- For Ux: Ax * Weight,
    constraint PK_Weights primary key( UserID, AnswerID )
);

I've shown the weight as an integer but you can change that if you want fractional values (A1 * 3.5 + A2 * -2.75 + A3 * 7 + A4 * 3.1415 = SCORE ).


Monitoring slow queries on large database (Percona 5.6) I am looking for a solution to monitor very large production database (many replicas, over 200 tables, some of tables have 10-50 GB of data) with a lot of traffic (a wide set of query complexity).
The main goal is to keep an eye on heavy queries after application code change. We have experienced that new code introduced non-optimal sql queries.
The database dump is very huge so we use a sample on staging environments only. It is almost impossible to detect performance issue on staging in this case.
I thought about using something like New Relic but for such  big infrastructure costs are too high and I am not sure it affects performance in a negative way.
On the other I can use MySQL slow log. I enabled it on some replicas with a log rate limit. After one month I do not see I/O speed impact but I am not sure about enabling it for all replicas and the master database.
I addition I found this article http://blog.symedia.pl/2016/10/performance-impact-general-slow-query-log.html and it says something different that I read in the past.
What would you do to monitor large database?

You can use pt-query-digest --processlist
While there are four different ways to use it, IMHO the best way to use it is to poll the processlist and collect metrics every hour. This method does not need a slow query log.
I have suggested this before (See mysql 5.7 general_log table has user_host but the file does not)
I have promoted this idea many times over the years.


Count total unique rows for each date and group by date I have the following table:

| email   | date_added |
-------------------------
| a@a.com | 2019-01-01 |
| b@b.com | 2019-01-01 |
| a@a.com | 2019-01-02 |
| c@c.com | 2019-01-02 |
| x@x.com | 2019-01-02 |
| a@a.com | 2019-01-03 |

I am looking for the following result

| count_unique_emails | date       |
|         2           | 2019-01-01 |
|         4           | 2019-01-02 |
|         4           | 2019-01-03 |

I have no idea where to start from.
Any thoughts?

Thanks

SELECT COUNT(DISTINCT srctable.email), dates.date_added
FROM srctable
JOIN ( SELECT DISTINCT date_added
       FROM srctable ) dates ON srctable.date_added <= dates.date_added
GROUP BY dates.date_added


Updating mongo sharded cluster without down time for mongod I am trying to update my mongo sharded version from 2.4 to 2.6.3.

I have some queries on steps to update this.

When I will run one mongos with the --upgrade option, will it update all the config and mongos or do I have to manually replace binaries on each one of them and restart the mongos and config?
How can I update my config servers without downtime?
Upgrading shard needs to down its members one by one and update the binary. Is there any other way to do it?

All details are here: http://docs.mongodb.org/manual/release-notes/2.6-upgrade/

Answer 1: All metadata will get updated. I suggest to execute the mongos --upgrade using an extra mongos

Answer 2: You don't have any downtime from the config servers since mongos have the require cached information and balancer is disable

Answer 3: There is no other way. You upgrade the secondaries and then promote one of them to PRIMARY and upgrade the old-PRIMARY. Downtime is minimal (1-2 second my case) if replica set are well-sync


How to add an automatically-populated custom sequence number? I have table something like this:

CREATE TABLE [dbo].[ado_test_table](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [tip] [varchar](10) NOT NULL,
    [datum] [datetime] NOT NULL,
    [doc_number] [int] NULL
) ON [PRIMARY]

I want column doc_number have sequential numbers for each tip. To be clear, I want to automatically or default or use a computed column with a function like this:

create FUNCTION [dbo].[fn_test_number]
(
     @tip varchar(10)
    ,@datum datetime
)
RETURNS int
AS
BEGIN
    DECLARE @number int

    select
        @number=    max(doc_number)+1
    from ado_test_table
    where
        year(datum) = year(@datum)
        and tip = @tip

    set @number=isnull(@number,1);

    return @number
END

In SQL Server 2005, can I have a column which will automatically have values? Should this be done by computed column, default (constraint) or trigger?

Create a trigger that on INSERT will populate that filed.

You cannot have it as a calculated column, as latter cannot refer to any tables.


How can change the default authentication method in MariaDB / MySQL I'm trying to set the default authentication method to unix_socket.
Following the instructions here:
https://lindevs.com/change-default-authentication-plugin-in-mysql/
I added to my.cnf :
[mysqld]
default_authentication_plugin=unix_socket

And restarted: sudo service mysql restart
Unfortunately, I don't see any change:
MariaDB [(none)]> SHOW VARIABLES LIKE 'default_authentication_plugin';
Empty set (0.001 sec)

This is on WSL1 and 10.3.27-MariaDB-0+deb10u1-log Debian 10.
Thanks

default_authentication_plugin is a MySQL system server variable and not in MariaDB.
For the moment you'll need to specify the plugin explicitly when creating users.
There's no feature requests to add this functionality yet.


Non clustered index not used for nullable column I'm having a strange (to me) issue with a non clustered index on SQL Server 11.0. I have a quite large table with about 4 million rows. I've created a non clustered index which looks like this

Neither is colum is primary key
I then run the follow queries with different results. One uses the non clustered index but the other does not and I can't figure out why. The only difference I can see is that AggregateEntityId is nullable.

The second query takes about 15 seconds the first takes about 50 milliseconds. Why doesn't the second query use the non clustered index?

This is because your nonclustered index covers predicates for "EntityId" and "AggregateEntityId" or only "EntityId" based on the order that the columns are listed on the index.
This is because the B-Tree that stores the nonclustered index data first sorts on EntityId then by AggregateEntityId.
Since your second query doesn't use EntityId in the WHERE clause, the nonclustered index is invalid in this case because it's unable to efficiently locate AggregateEntityId without being able to first filter on EntityId.
You would need another nonclustered index that starts with AggregateEntityId (or only contains AggregateEntityId) under the Indexed Columns.


Difference in number format (comma and dot) on SSMS and SSIS I have a numeric(5,2) field on my SQL Server database. When I run the query, the number on that field appears with . (dot) separator. However, when I preview the query on SSIS, it appears with comma(,) separator. I am trying to show the number format with dot(.) on SSIS as well. How can I do that?
Thank you.

The issue here is that the Locale settings of the file connection in SSIS are different to the language settings in SQL Server.

In SSIS, go to the Properties window for your File Connection (Right-click->Properties). There should be a setting for LocaleID that will need to be set to match SQL Server (or to English).

Further information can be found in the following Stack Overflow post


How to protect MySQL database from sql-injection? I am new to databases and security.

I am planning to put up a website.

I've read that sql-injection vulnerabilities can be very dangerous since the server might leak user passwords and credit-card information.

Are there any steps that I need to follow to prevent SQL injection vulnerabilities?

I was going through a wiki on sql-injection, but I didn't understood the following query:

SELECT * FROM users WHERE name = '" + userName + "';

you better use prepared statement from your programming source code, e.g. for PHP use PDO's prepare statement!

Check this - https://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection-in-php


index used when query process on oracle database I a new one using and learn about oracle database.
currently i have sql query like this
select attend_code from attadanstatusdetail
where attend_id = 'attendidcode001'
and company_id = 168

currently i have 2 schema on one instance or database with same index. From explain results
schema1 using index range scan which is this is good for me.
but at schema2 using index fast full scan.
my question

whats going on at schema2, why schema2 not using index range scan ?
after I running exec dbms_stats.gather_schema_stats('schema12','indexname') on schema2 i got same results from explain this is index range scan ?

can explain more to me about thats
thanks

Any Relational Database, not just Oracle, will work out for itself what it considers to be the "best" way of getting the data you ask for.
It bases its "working out" on many, many factors, but most of these are taken from the statistics that it automatically gathers about every table, index, etc.
The fact that your query plan changed when you gathered statistics on the table suggests that the statistics were "stale", that is to say "out of date" compared to what was actually in the table.
An extreme example of this might be if you created an empty table last thing one evening.  The statistics job generally runs overnight and would note that the table had no rows in it.  If, the following morning you loaded it with millions of records, it is quite likely that any query against that table would simply try to Table Scan it, "believing" it (based on the statistics) to be empty.  Updating the statistics, showing the DBMS the "truth" about the table's contents allows it to make a better assessment of how to "get at" rows in that table and would, most likely, improve things dramatically.


Cause of delay after use `$database` For a certain project, it seems like the command use <database>; takes an extraordinarily long time (often a minute or more). (This seems to be the case across a couple of different instances on different versions of MySQL (5.3 and 5.5, I think)). I understand that this is caused by the Tab complete functionality, and I am aware that I could just use a -A flag, but I am interested in the cause of this delay.

My first guess was that this has to do with the speed of INFORMATION_SCHEMA, and that the sheer number of columns caused a delay, but at 4.7k columns (580 tables at ~8 columns a table), that seems a bit of a stretch. There are 3.6k indexes, but that does not strike me as a likely cause of the issue.

You've pretty much hit the nail on the head.

If the use command results in a change of default database, then the MySQL client branches into build_completion_hash().

Provided that the client was not started with the -A option, that function:

hashes all SQL commands;
executes show databases and hashes the results;
executes show tables and hashes the results; and
for each table, calls mysql_list_fields() and hashes the results.

So, in your case, it is making 582 separate database requests (one for each of steps 2 and 3; and 580 for step 4), looping over the results of each one.  Ouch.

Of course, it'd have been imminently more sensible to do (or at least first attempt to do) a single SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() but ours is not to ponder why…


;with [Users](Id,Name,Other_Columns)
AS(
    SELECT 1,'John','Blah' UNION ALL
    SELECT 2,'Ricky','Blah' UNION ALL
    SELECT 3,'Stella','Blah' UNION ALL
    SELECT 4, 'Bob','Blah')
,
Saldo (Id,[User_id],Saldo)
AS
    (SELECT 1,    3 ,       0.00 UNION ALL
    SELECT 2,    1,        9.00 UNION ALL
    SELECT 3,    2,         0.15 UNION ALL
    SELECT 4,    4,         3.50 )

,Payments(Id,[User_id],Amount,Paid_date)
AS
    (SELECT 1,2,10.00,'2014-09-01 08:10' UNION ALL
    SELECT 2,2,25.00,'2014-09-01 09:00' UNION ALL
    SELECT 3,4,100.00,'2014-05-10 12:47' UNION ALL
    SELECT 4,1,20.50,'2014-02-23 15:30')

SELECT
    ID
    ,Name
    ,Saldo
    ,[Last Payment]
FROM
(
    SELECT
        U.ID,U.Name,S.Saldo
        ,LEFT(COALESCE(P.Paid_date,'0000-00-00'),10)  + ' ' + CAST( COALESCE(Amount,0) AS VARCHAR(50))  [Last Payment]
        ,ROW_NUMBER() OVER(PARTITION BY U.ID ORDER BY COALESCE(P.Paid_date,'0000-00-00') DESC) AS RN
        FROM
            [Users] AS U
            INNER JOIN Saldo AS S
            ON U.ID= S.[User_id]
            left JOIN Payments AS P
            ON P.[User_id] = U.ID
)X
WHERE X.RN =1

output:

ID  Name    Saldo   Last Payment
1   John    9.00    2014-02-23 20.50
2   Ricky   0.15    2014-09-01 25.00
3   Stella  0.00    0000-00-00 0.00
4   Bob 3.50    2014-05-10 100.00


Synchronize creation of rows between tables Lets say I have two tables, posts(id_pk,title) and posts_content(post_id_pk,blob). Whenever I create a row in posts, I want a complimentary row to be created in posts_content, with the same id. If I just use auto_increment on both, when two posts are created simultaneously, they might get switched. What is the correct way to do this?

Let's say you have this:

CREATE TABLE posts
(
    id int not null auto_increment,
    title varchar(100),
    primary key (id)
) ENGINE=InnoDB;
CREATE TABLE posts_content
(
    post_id int not null,
    blobdata blob,
    primary key (post_id),
    CONSTRAINT `FK_posts` FOREIGN KEY (`post_id`) REFERENCES posts (`id`)
) ENGINE=InnoDB;

You could perhaps run the two INSERTs like this:

START TRANSACTION;
INSERT INTO posts (title) VALUES ('some-title');
SET @GivenPostID = LAST_INSERT_ID();
INSERT INTO posts_content (post_id,blobdata)
VALUES (@GivenPostID,'oxbdsixds');
COMMIT;

Give it a Try !!!


Decompose this relation into BCNF Today, I'm reading about BCNF decomposition algorithm. It says that:

  BCNF Decomposition Algorithm

  Input: a relation R0 with a set of functional dependencies S0

  Output: a decomposition of R0 into a collection of relations, all of
  which are in BCNF

  Method: R=R0, S=S0

  Check whether R is in BCNF. If so, nothing to do, return {R}
  If there are BCNF violation, let one be X→Y
  Compute X+
  Choose R1=X+, and let R2 have attributes X and those attributes of R
  that are not in X+
  Compute the sets of FD’s for R1 and R2, let these S1, S2
  Recursively decompose R1, R2 using this algorithm. Return the union
  of the result of these compositions

I'm trying to apply that algorithm to decompose this relation:

R(A, B, C, D)
={AB→C,C→D,D→A}

As you can see, the key is {AB}, and 2 violations are C→D,D→A.Then:

Compute {}+ = {,,}
Decompose R into R1(C, D, A) and R2(B, C).
In R1, C is the key, so D→Ais a violation.
Compute {D}+ = {D,A}
Decompose R1(C, D, A) into R3(C, D) and R4(D, A)
The final result is: R2(B, C), R3(C, D), R4(D, A)

My first question is: is it correct?

I feel that we can decompose R into 2 relations which are (A, B, C) and (C, D). They are also in BCNF. How do we decompose R into that 2 relations? Which algorithm? Which way is better?

Thanks for your help.

First, let's note that in this case there are three keys, not only one:

AB
BD
BC

This means that all the attributes are primes, so the relation is by definition already in Third Normal Form.

For the BCNF, you have described the “analysis algorithm”, which is the algorithm presented in every good book on databases. But, with this example, you have also discovered that in normalizing, you can have different results, that can depend for instance on the order in which the dependencies that violates the normal form are chosen (not in this case, but possible in other cases with this same algorithm), or by using a different algorithm (there are others, not interesting from a practical point of view).

In your example, the second decomposition, which is in BCNF as you correctly stated, cannot be produced by the analysis algorithm: there is nothing wrong about it, in general there is no guarantee that this algorithm will produce the “best” decomposition (for instance a decomposition with a minimum number of relations), and, if this is an exercise, you have done it correctly!

What instead is interesting to note, also from a practical point of view, is if the decomposition satisfies all the important properties, i.e. if it preserves the dependencies of the original schema (since the decomposition produced by the algorithm is instead guarantee to be always a lossless decomposition).

And you can see that both the decompositions fail under this aspect. The decomposition in R2(B, C), R3(C, D), R4(D, A) does not preserve the dependency A B → C, while the decomposition R1(A, B, C), R2(C, D) does not preserve the dependency D → A. This means that in practice both of them reduce the redundancies and other anomalies of the data, at the expense of losing an important contraint over them.

So, knowing that the relation is already in 3NF, one could be satisfied by this result and leave the relation in this form. This means the preservation of the dependencies, together with a limited amount of redundancy, which in practice can be tolerated.


Passing in the table into a stored procedure Is it possible to pass in the name of a table into a stored procedure?

For example, suppose you have several views of the same table. They all have the exact same structure.

You want a stored procedure that can be run for any of the views.

Something like:

create procedure myprocedure
@tableName varchar(50) = ''
select blah from @tableName where blah = blah2

When I try to do this, I get

Must declare the table variable @tablename

Any ideas how I can do this?

Instead of creating views, why not make an inline table-valued function - effectively a parameterized view.  You basically get all the benefits of views AND parameters.

Another thing you can do in your TVF is something like this:

SELECT yourtable.*
FROM yourtable
INNER JOIN ranges
    ON yourtable.date BETWEEN ranges.start AND ranges.end
WHERE ranges.name = @range_name

Now your ranges have names (in a table where they can be easily managed without changing the schema) - this also fairly seamlessly handles multiple ranges with the same name, although if ranges overlap you can have duplicates due to the join matching both.

Also, please consider avoiding BETWEEN:

https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common

I strongly suggest that you reconsider your requirements and when asking questions here be sure to include more about your motivations, because when you bring in a question with a preconceived notion of what the solution is, you are likely to get a suboptimal solution.  (like the dynamic SQL solutions given aren't going to easily work for your OR case, but that information about the problem space is buried in a comment from you)

From: http://sqlfiddle.com/#!6/a628a/1

CREATE TABLE LogData (
  Id INT IDENTITY NOT NULL
  ,Dt DATETIME NOT NULL
  ,Data VARCHAR(max) NOT NULL
);

CREATE TABLE Ranges (
  Id INT IDENTITY NOT NULL
  ,Name VARCHAR(50) NOT NULL
  ,DtStart DATETIME NOT NULL
  ,DtEnd DATETIME NOT NULL
);

CREATE TABLE Groups (
  Id INT IDENTITY NOT NULL
  ,Name VARCHAR(50) NOT NULL
);

CREATE TABLE RangeGroups (
  RangeId INT NOT NULL
  ,GroupId INT NOT NULL
);

INSERT INTO LogData (Dt, Data)
VALUES ('2011-11-23 11:00', 'before thanksgiving day')
       ,('2011-11-24 11:00', 'on thanksgiving day')
       ,('2011-12-24 11:00', 'on christmas eve')
       ,('2011-12-25 11:00', 'on christmas day')
       ,('2012-11-21 11:00', 'before thanksgiving day')
       ,('2012-11-22 11:00', 'on thanksgiving day')
       ,('2012-12-24 11:00', 'on christmas eve')
       ,('2012-12-25 11:00', 'on christmas day');

INSERT INTO Ranges (Name, DtStart, DtEnd)
VALUES ('THANKSGIVING2011', '2011-11-24', '2011-11-25')
       ,('CHRISTMASEVE2011', '2011-12-24', '2011-12-25')
       ,('CHRISTMASDAY2011', '2011-12-25', '2011-12-26')
       ,('BOXINGDAY2011', '2011-12-26', '2011-12-27')
       ,('NEWYEARSEVE2011', '2011-12-31', '2012-01-01')
       ,('NEWYEARSDAY2012', '2012-01-01', '2012-01-02')
       ,('THANKSGIVING2012', '2012-11-22', '2012-11-23')
       ,('CHRISTMASEVE2012', '2012-12-24', '2012-12-25')
       ,('CHRISTMASDAY2012', '2012-12-25', '2012-12-26')
       ,('BOXINGDAY2012', '2012-12-26', '2012-12-27')
       ,('NEWYEARSEVE2012', '2012-12-31', '2013-01-01')
       ,('NEWYEARSDAY2013', '2013-01-01', '2013-01-02');

INSERT INTO Groups (Name)
VALUES ('HOLIDAYS2011')
       ,('HOLIDAYS2012')
       ,('ANYCHRISTMAS');

INSERT INTO RangeGroups (RangeId, GroupId)
SELECT Id, (SELECT Id FROM Groups WHERE Name = 'HOLIDAYS2011')
FROM Ranges WHERE Name LIKE '%2011';

INSERT INTO RangeGroups (RangeId, GroupId)
SELECT Id, (SELECT Id FROM Groups WHERE Name = 'HOLIDAYS2012')
FROM Ranges WHERE Name LIKE '%2012';

INSERT INTO RangeGroups (RangeId, GroupId)
SELECT Id, (SELECT Id FROM Groups WHERE Name = 'ANYCHRISTMAS')
FROM Ranges WHERE Name LIKE 'CHRISTMAS%';

CREATE FUNCTION dbo.JustDoIt(@GroupName VARCHAR(50))
RETURNS TABLE
RETURN (
SELECT LogData.*
FROM LogData
INNER JOIN Ranges ON LogData.Dt >= Ranges.DtStart
AND LogData.Dt < Ranges.DtEnd
INNER JOIN RangeGroups
ON RangeGroups.RangeId = Ranges.Id
INNER JOIN Groups
ON Groups.Id = RangeGroups.GroupId
AND Groups.Name = @GroupName
);

SELECT *
FROM dbo.JustDoIt('HOLIDAYS2011');

SELECT *
FROM dbo.JustDoIt('HOLIDAYS2012');

SELECT *
FROM dbo.JustDoIt('ANYCHRISTMAS');

This part:

SELECT LogData.*
FROM LogData
INNER JOIN Ranges ON LogData.Dt >= Ranges.DtStart
AND LogData.Dt < Ranges.DtEnd
INNER JOIN RangeGroups
ON RangeGroups.RangeId = Ranges.Id
INNER JOIN Groups
ON Groups.Id = RangeGroups.GroupId
AND Groups.Name = @GroupName

Means that each log item will be joined to the ranges it fits in (by date), each range will join to the groups they are in, but only that group selected will be used.


How to (a) rewrite a query that JOINs three tables and (b) make its execution more efficient I have three tables (A, B and C) with various data regarding mining facilities in them. But only one table (C) has two columns with coordinates. My final goal is to produce a table which pulls similar data about mine name, owner, product etc. from A and B and places them next to similar columns in C. I am using mine name.

The DDL design is as follows:

-- Table "A"
CREATE TABLE "globalminfac_db".minfac (
    ROW_ID INT,
    MINERAL_COMMODITY_GENERAL TEXT,
    FACILITY_PRODUCES TEXT,
    COUNTRY TEXT,
    CITY TEXT,
    LOC_DESC TEXT,
    LOCATION_NAME TEXT,
    OPERATOR_NAME TEXT,
    OWNER_NAME TEXT,
    PRIMARY_OWNER TEXT,
    SECONDARY_OWNER TEXT,
    FINAL_DDLAT REAL,
    FINAL_DDLONG REAL,
);

-- Table "B"
CREATE TABLE "drc_db".OUTLOOK_TABLE (
    COUNTRY TEXT,
    COMMODITY TEXT,
    MINE_NAME TEXT,
    OPERATOR_NAME TEXT,
);

-- Table "C"
CREATE TABLE "drc_db".table2_drc (
    COMMODITY TEXT,
    MAJOR_OPERATOR_OWNER TEXT,
    LOCATION_MAIN_FACILITIES TEXT,
);

However, I'm only able to perform INNER JOIN on these tables and this produces redundant rows where it takes all rows from each table with name "x" and gives each of them a row in my new table. See my SELECT operation below and a screenshot of the sample data. I've already done the initial work of joining the tables using matching columns, which is location/mine name.

SELECT minfac.mineral_commodity_general a_commodity,
       outlook.commodity b_commodity,
       table2.commodity c_commodity,
       minfac.location_name a_locationName,
       outlook.mine_name b_locationName,
       table2.location_main_facilities c_locationName,
       minfac.operator_name a_operator,
       outlook.operator_name b_operator,
       table2.major_operator_owner c_operator,
       minfac.final_ddlat Latitude,
       minfac.final_ddlong Longitude
    FROM "drc_db".minfac_drc minfac
    INNER JOIN "drc_db".outlook_drc outlook
        ON minfac.location_name LIKE concat(outlook.mine_name, '%')
    INNER JOIN "drc_db".table2_drc table2
        ON SPLIT_PART(table2.location_main_facilities, ' ', 1) = SPLIT_PART(minfac.location_name, ' ', 1)

This produces this table below:

As you can see, I have 6 rows listing "Dikulushi mine" with the same coordinates (3 from table A for Silver, Copper and Cobalt; 1 from table B and 2 from table C).

How can I rewrite my query to return the above table, but include only unique location names?

The final goal, to reiterate my earlier point is to have a final table which contains rows and coordinates from all three tables.

I am using pgAdmin4 with Postgres 9.5.14.

Any help with this question or towards making my query more efficient/better would be appreciated.

My first impression is that you should redesign your tables a bit. I'm not sure what SPLIT_PART does, but it sounds as if it grabs part of a string, and having such functions in an ON clause is really bad for performance. Assume table A and B has 1000 rows each:

SELECT ...
FROM A
JOIN B
    ON f(A.x) = f(B.x)

In a nested loop join there will be 2*1000*1000=2 miljon calls to f. I would investigate the possibilities to add a generated column for the join conditions, and add an index.

To remove redundancy from the answer you can use distinct, but you will still get redundant info in the following situation:

GOLD, SILVER, ...
SILVER, GOLD, ...

Does it matter if a_commodity and b_commodity switches place for a certain row? If not you can arrange the in lexigraphical order like:

SELECT distinct
       LEAST(minfac.mineral_commodity_general, outlook.commodity, table2.commodity)
     , ...
       GREATEST(minfac.mineral_commodity_general, outlook.commodity, table2.commodity)
     , ...

For the middlest one you will have to exclude the least and the greatest one.


Database transaction log growth who is the cuplrit? There are abundant information on how to shrink a growing Transaction log, from frequent log backups, to changing the RECOVERY from FULL to SIMPLE and truncate. A daily background job takes care of this process and it's working fine. By now, most people know how and why Transaction Log works. There is a complete explanation here.

I have a misbehaving application that, somehow, create a growing transaction log by not releasing, not sure if it's not committing or not closing or what, at the end of the (whatever) action.

It doesn't look like there is data loss or inconsistencies and not any unusual situation on the DB.

Upon restarting SQLSERVER show the database in 'recovery mode' for a while and then back to normal.

Doing DBCC LOGINFO, shows over 100 entries, all of them with 'Status=2' so I understand, they are in use. Interesting but on some entries, the 'filesize' is even larger that the Database itself!

The subject database on SELECT name,log_reuse_wait_desc,recovery_model_desc FROM sys.databases show log_reuse_wait_desc as 'NOTHING' and recovery_model_desc as 'FULL'

My question is: Is there any method, documented or not, to 'read' the log file and find what table, application, data or whatever info is on the VLF to help finding the culprit application?

Are you running transaction log backups?

If so, you may need to increase their frequency.
If not, you need to either start taking them, or switch to SIMPLE recovery mode.

If you are taking frequent tran log backups, and the logs are still growing, you might have a problem with bad application code, if they never properly commit/close transactions. In that case have your developers (or the vendor) fix the app.

You can use a query like this to find all open transactions:

Select st.session_id, st.is_user_transaction, st.is_local,
    at.transaction_type, at.transaction_state,
    sp.dbid, db_name(sp.dbid) as dbname, sp.cpu, sp.physical_io,
    sp.login_time, sp.last_batch, sp.open_tran, sp.status,
    rtrim(sp.hostname) as hostname, rtrim(sp.program_name) as program,
    rtrim(sp.cmd) as command, rtrim(sp.loginame) as loginname
from sys.dm_tran_active_transactions at
INNER JOIN sys.dm_tran_session_transactions st
    ON at.transaction_id = st.transaction_id
INNER JOIN sys.sysprocesses sp ON st.session_id = sp.spid
ORDER BY at.transaction_begin_time ASC

EDIT: Some further thoughts, based on your comments:

Make sure you are using a fixed growth size, for a large database I usually use 2000MB, 4000MB, or 8000MB, depending on what I expect the total size to be.
Follow Kim's instructions here to shrink and then re-grow the log, so you have reasonably-sized VLFs.
Is this database involved in replication? Was it ever?
Is rebooting the server the only time the log clears? Do tran log backups clear it?
When was the last full backup? You need a proper full to "restart" the log backup chain.

Run the following query to distinguish between used and free space within the log file, and monitor it throughout the day:

 SELECT DB_NAME() as dbname, type_desc, name as logical_name,
    CONVERT(decimal(12,1),size/128.0) as TotalMB,
    CONVERT(decimal(12,1),FILEPROPERTY(name,'SpaceUsed')/128.0) as UsedMB,
    CONVERT(decimal(12,1),(size - FILEPROPERTY(name,'SpaceUsed'))/128.0) as FreeMB,
    physical_name
 FROM sys.database_files WITH (NOLOCK)
 ORDER BY type, file_id;


Unique identifier field with a conditional I have a database that's not in production, so the main table being CustodyDetails, this table has a ID int IDENTITY(1,1) PRIMARY KEY column and I'm looking for a way of adding another unique identifier that its not referenced in any other table, I would think by taking this in account the content of the column wouldn't be exactly an identity key.

This new identity column has a few specific details though, and here's where my problem starts.
The format is as follows: XX/YY where XX is an auto incrementable value that resets/restarts every new year and YY is the last 2 digits of the current year SELECT RIGHT(YEAR(GETDATE()), 2).

So for example lets pretend one record is added a day starting from the 28/12/2015 ending 03/01/2016, the column would look like:

ID    ID2     DATE_ADDED
1     1/15    2015-12-28
2     2/15    2015-12-29
3     3/15    2015-12-30
4     4/15    2015-12-31
5     1/16    2016-01-01
6     2/16    2016-01-02
7     3/16    2016-01-03

I thought of using the frontend to parse the composite ID (ID2 in the example) get the last 2 digits and compare with the last 2 digits of the current year and then decide whether or not to start a new correlative. Of course it would be grand to be able to do it all on the database side.

EDIT 1: btw, I have also seen people using separate tables just to store parallel identity keys, so one table Identity key becomes a second table secondary Key, this sounds a bit dodgy but maybe this is the case such implementation comes in place?

EDIT 2: This extra ID is a legacy document reference that labels every file/record. I guess one could think of it as an special alias for the main ID.

The number of records this database handles yearly hasn't been out of the 100 in the last 20 years and is highly (really, extremely highly) improbable that it would, of course if it does go over 99 it the field will be able to carry on with the extra digit and the frontend/procedure will be able to go over 99, so its not like it changes things.

Of course some of these details I did't mention at the beginning because they would only narrow down the possibilities of solution to suite my specific need, tried to keep the problem range broader.

You could use a key table to store the incrementing part of your second ID column.  This solution does not rely on any client-side code, and is automatically multi-year aware; when the @DateAdded parameter passes in a previously unused year, it will automatically begin using a new set of values for the ID2 column, based on that year.  If the proc is consequently used to insert rows from prior years, those rows will be inserted with "correct" values for the increment.  The GetNextID() proc is geared to handle possible deadlocks gracefully, only passing an error to the caller if 5 sequential deadlocks occur when trying to update the tblIDs table.

Create a table to store one row per year containing the currently-used ID value, along with a stored procedure to return the new value to use:

CREATE TABLE [dbo].[tblIDs]
(
    IDName nvarchar(255) NOT NULL,
    LastID int NULL,
    CONSTRAINT [PK_tblIDs] PRIMARY KEY CLUSTERED
    (
        [IDName] ASC
    ) WITH
    (
        PAD_INDEX = OFF
        , STATISTICS_NORECOMPUTE = OFF
        , IGNORE_DUP_KEY = OFF
        , ALLOW_ROW_LOCKS = ON
        , ALLOW_PAGE_LOCKS = ON
        , FILLFACTOR = 100
    )
);
GO

CREATE PROCEDURE [dbo].[GetNextID](
    @IDName nvarchar(255)
)
AS
BEGIN
    /*
        Description:    Increments and returns the LastID value from
                        tblIDs for a given IDName
        Author:         Max Vernon / Mike Defehr
        Date:           2012-07-19

    */
    SET NOCOUNT ON;

    DECLARE @Retry int;
    DECLARE @EN int, @ES int, @ET int;
    SET @Retry = 5;
    DECLARE @NewID int;
    WHILE @Retry > 0
    BEGIN
        SET @NewID = NULL;
        BEGIN TRY
            UPDATE dbo.tblIDs
            SET @NewID = LastID = LastID + 1
            WHERE IDName = @IDName;

            IF @NewID IS NULL
            BEGIN
                SET @NewID = 1;
                INSERT INTO tblIDs (IDName, LastID)
                VALUES (@IDName, @NewID);
            END
            SET @Retry = -2; /* no need to retry since the
                                  operation completed */
        END TRY
        BEGIN CATCH
            IF (ERROR_NUMBER() = 1205) /* DEADLOCK */
                SET @Retry = @Retry - 1;
            ELSE
                BEGIN
                SET @Retry = -1;
                SET @EN = ERROR_NUMBER();
                SET @ES = ERROR_SEVERITY();
                SET @ET = ERROR_STATE()
                RAISERROR (@EN,@ES,@ET);
                END
        END CATCH
    END
    IF @Retry = 0 /* must have deadlock'd 5 times. */
    BEGIN
        SET @EN = 1205;
        SET @ES = 13;
        SET @ET = 1
        RAISERROR (@EN,@ES,@ET);
    END
    ELSE
        SELECT @NewID AS NewID;
END
GO

Your table, along with a proc to insert rows into it:

CREATE TABLE dbo.Cond
(
    CondID INT NOT NULL
        CONSTRAINT PK_Cond
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , CondID2 VARCHAR(30) NOT NULL
    , Date_Added DATE NOT NULL
);

GO
CREATE PROCEDURE dbo.InsertCond
(
    @DateAdded DATE
)
AS
BEGIN
    DECLARE @NextID INT;
    DECLARE @Year INT;
    DECLARE @IDName NVARCHAR(255);
    SET @Year = DATEPART(YEAR, @DateAdded);
    DECLARE @Res TABLE
    (
        NextID INT NOT NULL
    );
    SET @IDName = 'Cond_' + CONVERT(VARCHAR(30), @Year, 0);
    INSERT INTO @Res (NextID)
    EXEC dbo.GetNextID @IDName;

    INSERT INTO dbo.Cond (CondID2, Date_Added)
    SELECT CONVERT(VARCHAR(30), NextID) + '/' +
        SUBSTRING(CONVERT(VARCHAR(30), @Year), 3, 2), @DateAdded
    FROM @Res;
END
GO

Insert some sample data:

EXEC dbo.InsertCond @DateAdded = '2015-12-30';
EXEC dbo.InsertCond @DateAdded = '2015-12-31';
EXEC dbo.InsertCond @DateAdded = '2016-01-01';
EXEC dbo.InsertCond @DateAdded = '2016-01-02';

Show both tables:

SELECT *
FROM dbo.Cond;

SELECT *
FROM dbo.tblIDs;

Results:

The key table and stored proc come from this question.


Select Information from All Tables EER Diagram

Question

List the customer number, customer name (first and last), order number, order date, employee number, employee name (first and last), product number, product name, and order cost (product quantity * product unit price) for telephone orders (A telephone order has an associated employee) placed on January 23, 2007 and the order cost exceeds $150. Order the result by the order
cost in descending order.

My Code

SELECT
    Customer.CustNo,
    Customer.FirstName,
    Customer.LastName,
    `Order`.OrderNo,
    `Order`.OrderDate,
    Employee.EmpNo,
    Employee.FirstName,
    Employee.LastName,
    Product.ProdNo,
    Product.ProdName,
    CONCAT('$', ProductInOrder.Qty * SUBSTRING(Product.Price, 2)) AS `Order Cost`
From Customer, `Order`, Employee, Product, ProductInOrder
WHERE
    `Order`.Employee_EmpNo IS NOT NULL                          AND
    STR_TO_DATE(`Order`.OrderDate, '%m/%d/%Y') = '2007-1-23'    AND
    ProductInOrder.Qty * SUBSTRING(Product.Price, 2) > 150
ORDER BY `Order Cost` DESC;

Result

14:57:29    SELECT  Customer.CustNo,     Customer.FirstName,     Customer.LastName,     Order.OrderNo,     Order.OrderDate,     Employee.EmpNo,     Employee.FirstName,     Employee.LastName,     Product.ProdNo,     Product.ProdName,     CONCAT('$', ProductInOrder.Qty * SUBSTRING(Product.Price, 2)) AS Order Cost From Customer, Order, Employee, Product, ProductInOrder WHERE  Order.Employee_EmpNo IS NOT NULL       AND     STR_TO_DATE(Order.OrderDate, '%m/%d/%Y') = '2007-1-23' AND     ProductInOrder.Qty * SUBSTRING(Product.Price, 2) > 150 ORDER BY Order Cost DESC LIMIT 0, 1000   1000 row(s) returned    0.290 sec / 0.011 sec

My Question

I think there is something wrong with the code From Customer, Order, Employee, Product, ProductInOrder. How to join all the tables together? If I use INNER JOIN, it will be more complicated.

Thank you for your help.

It says like syntax error, which you missed the ,

SELECT
    Customer.CustNo,
    Customer.FirstName,
    Customer.LastName,
    `Order`.OrderNo,
    `Order`.OrderDate,
    Employee.EmpNo,
    Employee.FirstName,
    Employee.LastName,
    Product.ProdNo,
    Product.ProdName,
    CONCAT('$', ProductInOrder.Qty * SUBSTRING(Product.Price, 2)) AS `Order Cost`
From Customer, `Order`, Employee, Product, ProductInOrder
WHERE
    `Order`.Employee_EmpNo IS NOT NULL                          AND
    STR_TO_DATE(`Order`.OrderDate, '%m/%d/%Y') = '2007-1-23'    AND
    `Order`.Cost > 150
ORDER BY `Order`.Cost DESC;


Performance first low, then high after adding index, then keep high after drop index I am testing performance of a complicated select query. It is very slow.

Using explain, I have found a ALL in type COLUMN. Then I ADD INDEX. The result comes out fast. However, when I DROP the INDEX I added, the result comes out fast too.

I have set query_cache_size=0. There is no cache for each query result.

I am a new learner. Could some experts tell me why and what other items I need to check? How can I go back to the first slow scenario and verify that it is the index I need to add which has big impact of the performance?

Sample:

select col1,col2 from tbl1 groupby col1,col2;

show create table shows "index(col1), index(col2)"

explain shows the select query have type=ALL.

Then I add index(col1, col2) which made explain type=RANGE.

Most likely the statistics on the field were out of date. Adding the index created/updated statistics with a full table scan. Or creating the index pulled all the data you need into memory, like any scan would.

Adding an index will only perform better if it is being used. Add the index and then check the query plan. If the stats are up to date and the query optimizer elects to use it there is a big chance it will improve performance. Removing statistics has no use as it will result most likely in a suboptimal query plan.

More info on statistics https://mariadb.com/kb/en/mariadb/documentation/optimization-and-tuning/engine-independent-table-statistics/


Unable to connect to Mysql at peak time I have two servers one is application server and another is database server. Continuously I am firing query to mysql. At peak time I get error -

Can\'t connect to MySQL server on \'IP Address\' (99)

I couldn't track the issue.

Below is my my.cnf file-

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
default-time-zone = '+05:30'
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,

innodb_file_per_table=1
skip-name-resolve
innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
innodb_buffer_pool_size = 4096M

log-error=/var/log/mysql/mysql_error.log
long_query_time=2
slow_query_log=1
log-warnings=2
slow-query-log-file=/var/log/mysql/log-slow-queries.log
max_connections = 500
wait_timeout=10
interactive_timeout=10
general_log_file=/var/log/mysql/general.log
max_allowed_packet=256M
#log_queries_not_using_indexes = 1

#starting
#old_passwords=1

binlog-format=MIXED

server-id=1
#ending
key_buffer_size = 2048M

thread_cache_size = 128M

table_cache = 75000
table_definition_cache = 40000
open_files_limit= 75000

sort_buffer_size = 3M
read_buffer_size = 5M
read_rnd_buffer_size = 3M
myisam_sort_buffer_size = 20M
join_buffer_size=5M

query_cache_limit = 100M
query_cache_size = 2048M
query_cache_type=1

tmp_table_size = 256M
max_heap_table_size = 256M

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
#log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#log-warnings=2

At a particular time around 7000 tcp connections in TIME_WAIT and 1 or 2 in ESTABLISHED from application server to database server.

No server load, No Memory Issue, No firewall on both the server.

Please help me to solve this issue.

In your configuration you got max-connecions = 500 this means you acutally have 500 connections available to your server, and mysql adds an extraslot for and adminuser who's able to access over a terminal.

So you actually should always be able to connect to your Database even when all connections are used up.

Try to evaluate who/what is using up your administration connection, and get rid of it. Reasons i encountered are poorly written cronjobs which connect via shellscript to your DB (for example a self written backupscript) this takes a long time to run and locks you out of your Database.

Also check your open files limit on your OS in most distribution these values need to be raised when you have some heavy traffic on your server.


What synchronizes two delayed replicas, which is part of the replica set, included in sharded cluster? Please take a look at my mongodb map at http://postimg.org/image/krzvmep4d/

What synchronizes two delayed replicas, which is part of the replica set, included in sharded cluster? How can I use my delayed backup, if my replicaset "halfs" are not synchronized, because of I have shutdown one of my production servers first (using db.shutdownServer()), and shutdown another my server a few minutes later?

Or my mongo map just not perfect? In this case, please explain what I do wrong.

P.S. Re-added from here https://stackoverflow.com/questions/22715589/what-synchronizes-two-delayed-replicas-which-is-part-of-the-replica-set-includ

There is an obvious problem with your "delayed backup" model, in that your delayed secondaries will reflect the state of each replica set but not the full state of the sharded cluster at a given point in time.

A simple example:

there is a chunk migration from shard1 => shard2 in progress
documents will exist on both shard1 and shard2 while they are being copied over
your "delayed backup" does not have the matching config metadata to interpret the state of the cluster (and it will be changing over time, unless you disable the balancer)

Depending on the length of the replication delay (your diagram mentions "hour" and "week"), multiple changes to the cluster metadata may have happened and the config data will be very out of sync.

While you will have a delayed copy of the "data", if it isn't in sync with the sharded cluster metadata your path to a full restore will be reloading (and resharding) the data. You will also have to worry about resolving duplicate documents which can exist due to in-progress migrations.

For more information see the Backup and Restore Sharded Cluster tutorials in the MongoDB documentation. If you have a large amount of data, you will typically want to use the Filesystem snapshot approach to create an approximate point-in-time backup.


Hostname configuration for oracle12c on OL7 VM I am trying to install Oracle 12c (for learning/studying purposes)on a VirtualBox VM.  i have set up a VM with Oracle Linux 7.4 and when I setup the wireless/networking aspects during installation I decided to change the hostname from:

localhost

to:

OracleLinux7VM

I'm trying to follow along with the Oracle Installation Docuemenation and I'm on the HostsFile configuration section, it states:

"""

For example.

127.0.0.1       localhost localhost.localdomain localhost4 localhost4.localdomain4
192.168.0.215   ol7.localdomain  ol7

Set the correct hostname in the "/etc/hostname" file.

ol7.localdomain

"""

In the terminal on my VM, if I execute:

hostname

it returns

OracleLinux7VM

but if I cat the /etc/hosts file:

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4

::1 localhost localhost.localdomain localhost6 localhost6

I tried to understanding this through googling and reading the documentation but I'm not sure how exactly I should configure my /etc/hosts file based on my hostname: OracleLinux7VM based in order to successfully install Oracle 12c.  Any and all guidance would be greatly appreciated.

Simply add the following to /etc/hosts with your actual IP address:

192.168.0.215   OracleLinux7VM


Logging all changes made to MySQL database I have a web application setup where there are separate Apache-Wordpress containers for development and production. Database is hosted externally on a virtual host.

When setting up development environment, I simply take a copy of the Apache container and create a MySQL container where I import dumps from the external database.

Problem arises when I want to update the production environment by replacing the production container with the development container. Updating the development container has made changes to the testing database, for example new tables or schema changes, which are not present in the external database.

How could I update the external database with the changes made to the testing database, without losing data that have come to the production database after I took the dumps?

There are several tools available to serve the purpose. Following are my favorites:

SQLYog
DB Ninja

Here SQLYog is paid tool, howover it has only windows version available. If you choose this one then its following features will do the job for you:

Schema and Data Synchronization
Visual Data Compare

If you prefer web based interface then DBNinja is great tool. You can compare two database schema easily and fix the differences. following is the example screenshot from their official page:


Hash Join vs Hash Semi Join PostgreSQL 9.2

I'm trying to understand the difference between Hash Semi Join and just Hash Join.

Here're two queries:

I

EXPLAIN ANALYZE SELECT * FROM orders WHERE customerid IN (SELECT
customerid FROM customers WHERE state='MD');

Hash Semi Join  (cost=740.34..994.61 rows=249 width=30) (actual time=2.684..4.520 rows=120 loops=1)
  Hash Cond: (orders.customerid = customers.customerid)
  ->  Seq Scan on orders  (cost=0.00..220.00 rows=12000 width=30) (actual time=0.004..0.743 rows=12000 loops=1)
  ->  Hash  (cost=738.00..738.00 rows=187 width=4) (actual time=2.664..2.664 rows=187 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 7kB
        ->  Seq Scan on customers  (cost=0.00..738.00 rows=187 width=4) (actual time=0.018..2.638 rows=187 loops=1)
              Filter: ((state)::text = 'MD'::text)
              Rows Removed by Filter: 19813

II

EXPLAIN ANALYZE SELECT * FROM orders o JOIN customers c ON o.customerid = c.customerid WHERE c.state = 'MD'

Hash Join  (cost=740.34..1006.46 rows=112 width=298) (actual time=2.831..4.762 rows=120 loops=1)
  Hash Cond: (o.customerid = c.customerid)
  ->  Seq Scan on orders o  (cost=0.00..220.00 rows=12000 width=30) (actual time=0.004..0.768 rows=12000 loops=1)
  ->  Hash  (cost=738.00..738.00 rows=187 width=268) (actual time=2.807..2.807 rows=187 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 37kB
        ->  Seq Scan on customers c  (cost=0.00..738.00 rows=187 width=268) (actual time=0.018..2.777 rows=187 loops=1)
              Filter: ((state)::text = 'MD'::text)
              Rows Removed by Filter: 19813

As can be seen, the only difference in the plans is that in the first case, the hastable consumes 7kB, but in the second 37kB and that the node is Hash Semi Join.

But I don't understand the difference in the hashtable size. The Hash node uses perfectly the same Seq Scan node having the same Filter. Why is there the difference?

In the first query, only the customer_id needs to be saved from the customers into the hash table, because that is the only data needed to implement the semi-join.

In the second query, all of the columns need to be stored into the hash table, because you are selecting all of the columns from the table (using *) rather than just testing for existence of the customer_id.


SQL Server: Failed to start debugger - Data is Null I'm a developer working on a web application using an instance of SQL Server 2014. I use Visual Studio 2010 sp1 for development. For a task I'm currently working on, I'd like to use the SQL Server Remote Debugger to debug a very large stored procedure that we call through our ORM (Entity Framework). To do this, I open SQL Server Management Studio 2014 and type the following into a new query window:

EXEC [dbo].[MoveAllInvoiceStage]

and when I press the Debug button, I receive the following error:

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

  Failed to start debugger

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

  Data is Null. This method or property cannot be called on Null values.
  (System.Data)

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

  at System.Data.SqlTypes.SqlBinary.get_Value()    at
  Microsoft.SqlServer.Management.UI.VSIntegration.DebugSession.DebugCallbacks.OnSqlInitializeDebuggingEvent(ISqlInitializeDebuggingEvent
  sqlInitializeDebuggingEvent)    at
  Microsoft.SqlServer.Management.UI.VSIntegration.DebugSession.DebugCallbacks.Microsoft.VisualStudio.Debugger.Interop.IDebugEventCallback2.Event(IDebugEngine2
  debugEngine, IDebugProcess2 debugProcess, IDebugProgram2 debugProgram,
  IDebugThread2 debugThread, IDebugEvent2 debugEvent, Guid& riidEvent,
  UInt32 attribute)

Which is followed by a dialog that says:

  Unable to start program 'MSSQL:://<servername>/<databasename>/sys/=0'.

I am using a Windows login with a sysadmin role to connect to the database server, and all necessary firewall rules are in place on the server and my PC. I ensured that the requirements listed here are met.

The remote debugger was working when the database server hosted a SQL Server 2008 R2 instance, but it seems that it stopped working after upgrading the instance to SQL Server 2014. I can still debug on my local SQL Server 2014 instance (which hosts a copy of the remote database). Only remote debugging produces this issue.

I have confirmed that another developer on a different PC is experiencing the same issue, so the problem seems to be server-side.

Are there any additional steps I can take to troubleshoot this issue?

I had this exact problem, and solved it, on my local dev machine, by reinstalling a Visual Studio update.

I use 2012 SQL Server and VS 2015.2. This is all Localhost - no remote debugging.

Problem occurred after installing SSMS 2016 RC3. Debugging in SSMS 2016 caused the above error.

SSMS 2014 worked just fine.

The problem went away when I reinstalled (Repaired) the VS2015.2 update.

Each SSMS version has a VS Isolated Shell version it uses. (SSMS 2016 uses VS2015, SSMS 2014 uses VS2012 etc)

It would seem there was some sort of version conflict between the SSMS installation's VS Isolated Shell, and the full VS installation. Maybe the VS update has something to do with it.

Anyway, hope this helps somebody.


How to execute maintenance job from script and get back the result I am trying to find a way to execute a maintenance job from script and have it return if the the job was successful or not. I know that I can use msdb.dbo.sp_start_job to execute the maintenance job, but it will only kick it off with out returning if it was successful or not.

My ultimate goal for this is we use JAMS for all our job scheduling and we want to only need to check there for any job failures.

If your end goal is to use JAMS, then it handles many of the SQL agent job conversions for you.

JAMS includes a native conversion utility for SQL Agent jobs to streamline the transition your organization’s transition to enterprise scheduling. Comprehensive mapping preserves the SQL Agent logic you’ve created and extends each job with an array of properties for advanced scheduling.

Taken from http://www.jamsscheduler.com/platforms/sql-databases/


Can't cast a stored procedure parameter? I have several stored procedures that take a date/time input, one of which takes a date only in the form of a nvarchar because it utilizes dynamic SQL. When I try to cast the variable going into the stored procedure, I get an error. This doesn't make much sense to me.

I know I can work around it by creating a second set of nvarchar variables or casting within the stored procedure, but I'd like some insight on why I can't just cast the variable in-line.

Example.  *TOP is a Select statement telling me my NVARCHAR DATE kicks out just fine.

MID is where SSMS is throwing the error, BOTTOM is where SSMS thinks it's just fine.*

Stored procedure parameters can only accept literals, constants or variables as parameters. It can't take expressions of any kind.

You can say:

EXEC dbo.myprocedure @foo = 'bar';

But you can't say:

EXEC dbo.myprocedure @foo = 'b' + 'ar';

You should not be converting a date to a string and then back to a date again. Your dynamic SQL can easily take a date parameter, parameterized safely (you may want to read up on "SQL injection"), and not have to worry about ugly string concatenation. Here is an example:

CREATE PROCEDURE dbo.foo
  @dt DATE
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @sql NVARCHAR(MAX);
  SET @sql = N'SELECT DATEPART(DAY, @dt);';
  EXEC sys.sp_executesql @sql, N'@dt DATE', @dt;
END
GO

DECLARE @fromDay DATETIME = '20010914 03:45';
DECLARE @dt DATE = @fromDay; -- or just declare @fromDay as DATE in the first place;
EXEC dbo.foo;

Results:

----
14


SQL Developer export/import SSH hosts Is it possible to export/import ssh hosts from SQL Developer? The hosts are listed under C:\Users\name\AppData\Roaming\SQL Developer\system17.4.0.355.2349\o.sqldeveloper.ssh but I don't find a way to export/import them into a SQL Developer.

No way that I have seen or found.  I also pinged Jeff Smith and Kris Rice (Product Owner and Developer for the platform) on Twitter and they confirmed as well.  (see https://twitter.com/thatjeffsmith/status/1125396802423992322)

On the plus side:  You have located the configuration file.  And, while not ideal, at least you can make your configurations somewhat portable.  :-)


How to make SQL Server order fields by 'Id' (which in this case is an Identity column) I have an SQL Server table that i post into using C#. I would like the fields to be ordered using Id. Instead i get something like this:

I need the id column to be arranged in order of increasing number because i use a data table in C# to go through these values one at a time, therefore jumping from 7 to 9 to 11 back to 8 is very inconvenient.

N.B: I'm not talking about select * from table_name order by id. I need the values to be 'permanently stored' in the order requested.

Now, when you say:

  I need the id column to be arranged in order of increasing number because I use a data table in C# to go through these values one at a time, therefore jumping from 7 to 9 to 11 back to 8 is very inconvenient.

it shows some confusion. There are two issues here that are orthogonal to each other:

the internal order that rows are stored
the order that the rows are returned in a result set

These two are not to be confused. The internal order is a matter of the database's physical design while the order of a result set can vary from query to query, depending on the ORDER BY clause.

Tables are inherently unordered. Despite that, in many DBMS - and in this case SQL Server - we have the option to add a "clustering index" on a table, so there is a B-tree index (with the chosen order) that leads to the rows.

This is however irrelevant to the order that a result set is presented. The only way to have a guaranteed order in the result set is to specify an ORDER BY clause.

If you don't specify an ORDER BY clause, the order can be arbiratry:

you may have a heap (table without clustered index) and still sometimes see the result ordered by id and sometimes in a random order.
you may have a clustered table and still sometimes see the result ordered by id and sometimes in a random order.

Notice that there is no difference above. The underlying table structure doesn't guarantee an order.

So, if you do want the rows stored in a specific order on disk, make (id) the clustered index. But note that without an ORDER BY id, you will still get the results in arbitrary order.


What does 'quiesce' stand for? I want to know what does "quiesce" in "db2 luw" means, how can I use it in linux and how it can affect the access to the tablespaces

I want to know how to tell whether or not a tablespace in a ddl was quiesced or not.

To Quiesce means to pause/alter an application/device to allow the application/device to achieve a consistent state.

In terms of DB2 the Quiesce command should force all the users off the specified database and should flush all the buffers to disk (been a while since I've used DB2).

While the database is a Quiesced mode there are users who can still access the database such as SYSADMIN and SYSMAINT.

There are several DB2 functions that can be used to determine the status of a table such db2tbst and MON_GET_TABLESPACE_QUIESCER


On SQL Server Backup: Msg 3271, Level 16, State 1, Server Line 1 A nonrecoverable I/O error occurred on file I am trying to back up a pretty big database and I keep getting this error:

Msg 3271, Level 16, State 1, Server {Server Name}, Line 1 A
nonrecoverable I/O error occurred on file "{Backup File Location}:"
112(failed to retrieve text for this error. Reason: 1815). Msg 3013,
Level 16, State 1, Server {Server Name}, Line 1 BACKUP DATABASE is
terminating abnormally.

Does anyone happen to know what this is?

So after many google searches and realizing that there was very little space left on the drive that I was trying to back up to, I found the issue.

Resolution: There is not enough space on the drive to fit the backup.

That seems like something that would be pretty easy to display Microsoft!!

Hope this helps someone else


How to change the replicate_*_table variable without modifying my.cnf? what is the syntax of replicate_*_table without having to modify my.cnf file?
and do i need to restart mysqld for changes to be applied

Unfortunately the replicate-do|ignore-db|table variables cannot be set dynamically. In fact, they're a bit weird, since they're not even presented in SHOW GLOBAL VARIABLES output.

So you have to specify these params in the my.cnf file.

Just as final note, regardless of these particular params, whenever you set a global variables dynamically (e.g. SET GLOBAL sql_mode := 'TRADITIONAL') and then restart the mysqld server -- your changes are lost. Whenever the server starts up, it's what's written in the config file that takes place. No persistence for online changes.


Remotely inserting 3.5m records mysql I need to remotely insert 3.5 million rows to my amazon rds. Currently my data is in an access database, for which I will use bullzip, access to mysql to create dump files. I have created my schema on the RDS and defined the data types pre-insert (because when you auto upload through the app it defines most cols as varchar(255)).

So far I have used the following methods, when uploading to a test server, whilst building my application.

Direct transfer using bullzip's app. Left it to run overnight once, and 1.7 million rows had inserted after about 10 hours.
Split the file into numerous sql dumps (300k rows each at the most, split by date). Which is also incredibly slow ( over an hour so far to 200k rows)
Export to CSV, and upload through mysql workbench. ( also taking a hell of a long time)

I believe that my issue is that bullzip's application, produces an sql file which performs an insert statement for every row, rather than multi-row insert queries.

Is there a way to produce an sql file, in a multi-row insert format?

Or, do I have to just put up with it, because I'm exporting from access?

And I have read elsewhere that you can only add 50k rows to a multi-value insert. So is what I'm asking even suitable for me?

This is the SHOW CREATE TABLE:

'CREATE TABLE `1_txns` (
  `txn_id` varchar(32) DEFAULT NULL,
  `nationality_id` int(3) DEFAULT NULL,
  `transaction date` int(8) DEFAULT NULL,
  `Yr` int(4) DEFAULT NULL,
  `mth` int(2) DEFAULT NULL,
  `dy` int(2) DEFAULT NULL,
  `hr` int(2) DEFAULT NULL,
  `Min` int(2) DEFAULT NULL,
  `Flight No` varchar(12) DEFAULT NULL,
  `sku` varchar(20) DEFAULT NULL,
  `sales` decimal(8,2) DEFAULT NULL,
  `units` int(8) DEFAULT NULL,
  KEY `txnid` (`txn_id`),
  KEY `skutxnid` (`sku`,`txn_id`),
  KEY `skunatid` (`sku`,`nationality_id`,`txn_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8'

It would be convenient to diagnose where is the performance bottleneck in this case. Can you monitor the network throughput / CPU usage / disk usage to see which one is higher while uploading?

If it's a network bandwidth problem, make sure you're using compression in server/client protocol, any client should have this feature, Mysql Workbench for sure does
Most probably it's not related to the fact that the data comes from Access
Having your own test Mysql server inside your network would be of use for two things: importing the data there to see if the network is the problem; and importing it there and exporting it from there, forcing a multi-row insert export format


Database events but not triggers This is a question regarding general DB inner workings, not particular to an implementation or paradigm, though answers for certain technologies are welcome.
I am asking if there is a way to listen to what commands the database has received, or something like read an inner log of the database, at least the last changes.
I need such a functionality to be able to find if there were changes to a table and if so, read the particular row that has changed. It is assumed changes to the columns are not to happen.
I am only a listener of the database, hence I am not able to program triggers.

Most major DBMS products today have facilities for this: it may be called

Change Data Capture (Oracle, SQL Server)
logical replication (Postgres, MySQL)

Most of these facilities operate on the publish/subscribe model and build upon the DBMS's built-in transaction logs to provide a stream of change events. Often the amount of logging will increase slightly because the primary key has to be included in the stream rather than just physical information about which blocks have changed.
Organisationally, you may encounter resistance to implementing things like this with conservative DBAs and organisations (I have encountered this several times when trying to implement Oracle CDC), while it's not likely to be an issue where they have switched to, or are using open-source DBMSes (more  open to change).


How to get the information of the optimizer of sql server? The info like total optimization time, the time spent on each stage of optimization, the number of alternative plans and the cost of each alternative plan. Is there any way to get these info using sql server 2008?

You should check out the SQL Management Studio

It will let you see many things about your query. I know it will show you the cost of executing the query. It will also show you the cost of each step of the execution.

http://msdn.microsoft.com/en-us/library/ms178071(v=sql.105).aspx

http://msdn.microsoft.com/en-us/library/ms191194(v=sql.105).aspx


Creating a PostgreSQL SERIAL column using pgAdmin3 When I use pgAdmin3 to create a column of type serial, why does it instead create nextval('prices_id_seq'::regclass)?

Should we keep this type defined by pgAdmin? Manually create the table with SQL defining id column as type serial causes the pgAdmin SQL pane to again display it as nextval('prices_id_seq'::regclass).

pgAdmin SQL Pane

id integer NOT NULL DEFAULT nextval('prices_id_seq'::regclass)

This is not a pgAdmin feature but that of PostgreSQL.  If you check your table from an other client (eg. psql) you will see the exact same column definition.  Why is it so?

  The data types serial and bigserial are not true types, but merely a
  notational convenience for creating unique identifier columns (similar
  to the AUTO_INCREMENT property supported by some other databases). In
  the current implementation, specifying:

CREATE TABLE tablename (
    colname SERIAL
);

  is equivalent to specifying:

CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

It is always useful to consult the documentation.