Feasible to have thousands of users in Postgres? We are creating SAAS where we will at most have 50.000 customers. We are considering creating a user in the Postgres database for each customer. We will map each user that logs into our service to a user in the database in order to be very sure that they only have access to their own data. We also want to implement an audit trail directly in the database by this solutions, which utilizes triggers. If each customer has its own database user, then it would be very easy to see who did what, even if two customers would share the same data.

Will we be running into some unexpected problems because we have 50.000 users in our database? Performance-wise or administration-wise. Maybe connection pooling would be more difficult, but I do not really know whether we would need it.

Yes, it should be fine. You should use connection pooling though, as pg uses a fair amount of memory per connection (about 10MB AFAIK).

More than 500 simultaneous connections per box will be a problem though (like actively querying the database at the exact same time). More cpus/cores is better. Use SSDs with RAID 10.

Your SaaS application should connect as one user, then set role to the real user. This allows you to use connection pooling, as the connection string will be the same, but use different users. You should reset role when returning connection to the pool.

This is not really database authentication. It's proxy authentication (aka Impersonation).

You could also consider separate pools per company or per role.

To make admin easier, you can put users into groups and set permissions via groups. This is called RBAC.

Update: I was able to create 50,000 users in 2.4 seconds. PGAdmin is noticeably slower, due to the number of users. However connecting via JDBC is as fast as before. I was unable to drop 50,000 users at once, but could do about 10,000 at a time.


'Select @VAR =' and 'Set @VAR =' behaving unexpectedly Working on a database i didn't setup myself i was digging through some stored procedures to find out what they were doing.

In here i found numerous variables being set through a SELECT @var =, I never really did such a thing and often only used SET @var =.

Reading up on the documentation Microsoft TSQL as well as Stackoverflow it seemed to me they are similar in their functionality yet not entirely.

So I decided to test it out with

DECLARE @var1 varchar(200)
SELECT @var1 = 'Generic Name'
SELECT @var1 = (SELECT Name FROM Users)
SELECT @var1 AS 'Company Name'

DECLARE @var2 varchar(30)
SELECT @var2 = 'Generic Name'
SET @var2 = (SELECT Name FROM Users)
SELECT @var2 AS 'Company Name'

DECLARE @var3 varchar(200)
SELECT @var3 = 'Generic Name'
SELECT @var3 = Name FROM Users
SELECT @var3 AS 'Company Name'

DECLARE @var4 varchar(200)
SELECT @var4 = 'Generic Name'
SELECT @var4 = Name FROM Users WHERE Name = '2000'
SELECT @var4 AS 'Company Name'

DECLARE @var5 varchar(200)
SELECT @var5 = 'Generic Name'
SELECT @var5 = (SELECT Name FROM Users WHERE Name = '2000')
SELECT @var5 AS 'Company Name'

Based on the documentation i expected var1 to have the last value from the query and that it wouldn't throw an error. But var 1 and var 2 both throw the error

  Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

The above result is expected for set based on the documentation, but not for assigning variable through select based on my understanding. Var3 does produce the expected result of taking the last name returned.

Similarly var4 and var5 behave weirdly. Neither query returns results, so i'd expect both to stay their old value of Generic Name. But instead only var4 keeps its old value. Var5 is set to NULL instead. And I don't understand why. To me it seems under some circumstances SELECT @var = behaves exactly like SET @var =, and in some it acts according to documentation.

The main difference between SELECT and SET is that the former can perform multiple assignments simultaneously, e.g.

DECLARE @var1 int, @var2 int;

-- with SELECT, you can assign them together:
SELECT @var1 = 5, @var2 = 11;

-- with SET, must perform these separately:
SET @var1 = 5;
SET @var2 = 11;

Personally I would do this even more concisely, dropping both SET and SELECT:

DECLARE @var1 int = 5, @var2 int = 11;

Both SET and SELECT as assignment operators have the same restriction that, if you assign the result of a subquery, that subquery must return exactly one row, so you're not forcing SQL Server to try to determine which row you meant. This restriction is loosened up when no subquery is involved (and it works differently according to the standard, not some choice Microsoft made). So your first two cases work as designed and the error message is expected. @var3 and @var4 also both work as I would expect (though personally I would write this differently - e.g. if you want some concept of "last" row, tell SQL Server which "last" row you mean by using ORDER BY, MIN, MAX, etc.). As written, my results are:

@var3
----------------------------------------------------------------------
"Last" company name -- happens to be "last" in clustered index order,
                    -- but this might be different with other indexes
@var4
----------------------------------------------------------------------
Generic Name -- definitely not NULL

@var5 also works as expected. Yes, you assign a literal value first, but the assignment of the result of a subquery - in the absence of any error - overrides the previous value. In the case of an empty set, then it sets the value to NULL.


Is it always bad to use SELECT * FROM? It is widely recommended NOT to use SELECT *. I understand this, as it is not a good idea to bring the entire row into memory, when only a few cells are needed.

Is it still bad to use SELECT * when we want to read the entire row, or even 90% of it?
Isn't it benefitial when we read the whole row?
In normal condition, the server needs to look for each cells in SELECT col1, col2 FROM in order of the SELECT. But in SELECT *, it simply reads in order of columns? The later should be easier and faster!

In general SELECT * is bad, but like most rules that are exceptions.

Not really, there'll be a minor performance hit but the simplified logic and compatibility more than make up for this.
I believe the SELECT * gets replaced with a list of all fields in the table which the DB engine has to look up before it can run the SQL, this is where the performance hit comes from. However, I've never seen an noticeable performance decrease and in my opinion the simplified maintenance and compatibility make up for this.

There are a couple of things to consider though:

Always use a WHERE clause (unless you REALLY want the entire table), this is a different issue to the SELECT * issue.
If you don't control the schema, listing columns will keep network traffic as low and predictable as possible if someone adds a varbinary(max) field to the table for storing images/PDF file or whatever.

This seems to be one of those "rules" that have come about to stop people reading an entire 400+ column tables into memory.
In my experience, as long as the table is fairly small (in terms of columns) and you really do need all the data, let the highly-optimized DB take the hit while you concentrate on maintainability and compatibility and other benefits to yourself and your users.


InnoDB MVCC vs Locking From what I understand you InnoDB supports

Row Level Locking
MVCC (Multiversion Concurrency Control)

Row Level Locking

  Locking is for when multiple writers are trying to update the same
  rows. Only one writer can update a row at a time, and the first one to
  update the row locks it until they commit the change. Other writers
  have to wait until the first writer commits. But at least with
  row-level locking, they only have contention if they're updating the
  same row.

  A read lock can be used to prevent other users from reading a record (or page) which is being updated, so that others will not act upon soon-to-be-outdated information.

  https://stackoverflow.com/questions/6321647/innodbs-row-locking-the-same-as-mvcc-non-blocking-reads
  https://en.wikipedia.org/wiki/Lock_(database)

Multiversion Concurrency Control

  Writers don't block readers
  Readers don't block anyone, and don't get blocked by anyone.

  (https://stackoverflow.com/questions/22923127/mvcc-row-locking-vs-textbook-transaction-behavior)

Those two are the opposite of each other.

My question is: when does locking or mvcc occur? Where do I need to specify which one the database should use?

MVCC applies to isolation levels read-committed and repeatable read (default).

You don't need to specify anything for both of these features to work together.  Maybe one way to think about it, is that row level locking is important so that you can update multiple rows at a time, and MVCC is so that the updates don't affect read operations at all.


Efficient many to two relationship I'm building out a database with a users table, and I want to store the mutual (Facebook) friends between every set of two users in the database. Doing this in a normalized, efficient way seems tricky. I've considered two options so far:

Option 1

Create a table mutual_friend_set with columns id, user1_id, user2_id. Create another table mutual_friends with columns id, mutual_friend_set_id, name, picture, unique_mutual_friend_id.

The problem with this solution is that there's nothing to distinguish user1_id from user2_id, so I'd need to either create duplicate entries such as:

id   user1_id  user2_id
1    123       456
1    456       123

which would take up twice as many rows as necessary and throw normalization out the window, or ensure the lower of the 2 user_ids was in the user1_id column. If, however, I wanted to get all mutual friend sets for a single user, I'd need to query across both columns.

Option 2

Create a table mutual_friend_set with fields id, user_pair, such that user pair is a string of the combined user relationship, delimited by a comma. Eg: id: 1, user_pair: '123,456'. The user with the lower ID would be placed before the comma.

This would get around the normalization issues, but of course if I wanted to grab all the mutual friends pairs for a single user I'd have to run a LIKE query which isn't exactly efficient.

I assume this is a common problem, which raises the question: is there a standard way of doing this? Given my inexperience, am I missing something obvious?

Any ideas appreciated

Adding as friend is not a commutative operation. When A add B as friend that doesn't means that B do the same for A. That is why the mutuality emerge.

You definitely need both rows:

id   user1_id  user2_idz
1    123       456
2    456       123

If you want to get mutual relationships from that table, you have to JOIN it with itself:

SELECT w.*
  FROM friends AS w
  JOIN friends AS z ON z.user2_id = w.user1_id
                   AND z.user1_id = w.user2_id
                   AND w.user1_id < w.user2_id


How to exceute view without DBLink in oracle server I have 2 views, example V1 from Database  DB1 , V2 from database DB2, these 2 database server are remotely configured, I can able to execute these views separately without database link in the respective database server. Now i want to join these views and want to execute it without database link. How do I achieve it?

You must have a server (say X) with V1 (pointing at server Y) and V2 (pointing at server Z). Can't you use create GTTs (Global Temporary Tables) with V1 and V2 (this will happen on X) and then perform your join operation on the GTT tables on X using the data from GTT_V1 and GTT_V2? There's a good link here about using GTTs. Check out the guidelines section.


Suitable schema to record email sent and response received and invoicing in DB When a form submission is made on a website:

An email with form's details is sent to a set of recipients email addresses stored in a DB.
The email contains a link for the recipient to show interest. When the link is clicked a response is sent to server with verify_code. The server will UPDATE the table below setting has_confirmed to true or 1 to record their interest on DB.
An email is sent back to the recipient as acknowledgement

This is the current table to record the above:

RequestSentConfirmation

Sent_ID (int) PK
Request_Id (int) FK
Recipient_Id (int) FK
verfied_code (varchar(100))
has_confirmed (bool)
when_confirmed (datetime)

Request_Id is ID to form submission details stored in another table.
Recipient_Id is ID to recipient email addresses stored in another table.

Is it better to store the has_confirmed in the same table as above.

The confirmations are required to collect and generate an invoice for each recipient.
To generate an invoice from above table I will need to run SQL script to check which sent emails are confirmed.

Or best to store the confirmations in separate table? That way I could run reporting script on the separate confirmation table to generate invoice for each recipient?

Is there a better way to design this with DB since I'm using emailing here.

Assuming the system handles only a few actions per second and only millions of ids, either flavor of schema design should work well enough.

I can think of no strong argument for, or against, splitting the confirmations off into a separate table -- performance, sparseness (or lack of) in the Confirmations table, 1:1 relationship, coding convenience, purging, etc.

If you anticipate hundreds of actions per second or billions of ids, then you should sketch out more details, including INSERTs, UPDATEs, and SELECTs.  Plus consider a purging policy (which might include PARTITIONing by date).

You've had the foresight to think about the 'right' design -- Good.  Suggest you plan on significant redesign after maybe a year of use.  Build a layer between you application an MySQL.  Then focus on the application without fretting over the schema design.  With luck, the impact of the redesign will be mostly limited to the 'layer'.

For example, the layer would be asked to SendConfirmation(id); it would then either UPDATE (if one table) or INSERT (if 2 tables).


Can SQL Server 2012 FCI have multiple names? Is it possible to have multiple names for the same SQL Server Failover Cluster Instance (virtual server)? IT has a naming convention they like, but it hard codes the Windows cluster name into the SQL Server cluster name, which makes extra client configuration work when we have to migrate servers.

The last time they migrated (from SQL Server 2000 to SQL Server 2005 in 2000 compatibility mode), I think they just added another (DNS) hostname alias, but they only had one FCI. We are now migrating to SQL Server 2012 and we will have two FCIs ("active/active", although I know that's not really the right term), so we can't just use a default instance.

Note that I'm not the one who will be implementing the cluster, but I am migrating the databases and the client software, so I'm making the requests to the guys who are implementing the cluster. I don't want to ask them to do something that can't be done or isn't a good idea.

Consider using Sql Client Aliases to abstract the data source name. The aliases are set on client computers by either adding keys to registry or by running c:\windows\system32\cliconfg.exe (for x86, x64 systems) and c:\windows\syswow64\cliconfg.exe (for 32-bit applications in x64 system). A Sql Client Alias is quite a bit like the additional DNS record you described. What's more, aliases support instance names too, a feature that DNS record won't do.

After an alias has been created, configure the client to use it instead of real Sql Server instance. Should you ever need to change the server, just modify the alias.

As a caveat, there seem to be some applications that won't work well with aliases. These often are legacy ones, like those using ODBC or proprietary database drivers. For modern applications, aliases will work just fine.


Error during Oracle database 12c Release 1 installation I am trying to install Oracle database 12c on Oracle Linux 7 version. But it is keep on failing with the below error.

[INS-30131] Initial setup required for the execution of installer validations failed.

CAUSE: Failed to access the temporary location.

ACTION: Ensure that the current user has required permissions to access the temporary location.

ADDITIONAL INFORMATION:

Framework setup check failed on all the nodes
Cause: Cause Of Problem Not Available
Action: User Action Not Available

Summary of the failed nodes
ravilinux

Version of exectask could not be retrieved from node "XXXXXX"
Cause: Cause Of Problem Not Available
Action: User Action Not Available****

I see solutions are available for this error in Windows. But I don't see a solution for linux.

Can someone help on this please? I am trying to resolve this error for a week now.

I have /tmp with 3.9gb allocated. It is Free with 3.6GB still.

I tried creating /tmp which was not existing as a separate file system and kept 4gb of space. But the issue did not get resolved.

I try to keep my temporary file in a different place where the space available around 20gb. Still issue did not get resolved.

Finally I had to run the below command which resolved the issue. I couldn't find the solution so easily in google in first few pages as none of the people had raised this issue for Linux OS. Everyone was asking about Windows OS issue.

I had run the below command to provide execute permission for the folder CVU_12.1.0.2.0_**** under my temp directory.

chmod -R 777 /tmp/CVU_12.1.0.1.0_oracle/*

This resolved my issue. Thanks for the website which provided this solution.

I am posting here just to let others know, so that if anyone facing the same issue in future like me, can see this and get benefit.

Thanks for everyone who tried to answer my question. I really appreciate it.


Difference between Hints and Indexes Oracle Hints

  Hints can extend the capability of cost-based optimization. This technique is especially helpful if your distributed query contains an aggregate, subquery, or complex SQL.

Indexes

  Indexes can provide faster data access. Indexes are optional structures associated with tables and clusters that allow SQL queries to execute more quickly against a table.

Those are the definitions of Hints and Indexes provided by Oracle, having said that, we can see that both have the objective of improving the performance of the query.
I'd like to know if there is any other difference between them.

Hints are more for performance issues, and Indexes are for response speed?

Both are related to performance and response speed.

A hint gives the Cost-Based Optimiser (CBO) a "nudge" in a particular direction; an explicit instruction to try and get to the data in the way you tell it to.  The use of hints requires a [very] good understanding of the CBO and how Oracle chooses to do things internally.  Given that this might change with any given Release, it's probably the less effective of the two, generally speaking.

I can't recall having applied hints to any Oracle SQL I've ever written.

Indexes are generally there to speed up access.
If Oracle can use an index to get to data faster, then it [usually] will and the increase in speed can be huge!  Put millions of rows into an un-indexed table and try to query them; Oracle has no choice but to scan through them all and it will be really, really slow.  Put an index on the field you're filtering by and things can get [several] orders of magnitude faster.  A simple binary search on a single value can get to any of, say, one million records in about twenty comparisons (instead of the million).  Oracle's indexes are generally even better than that, holding more values per "bucket" and getting down to the detail even faster.

Of course, the flip side is that inserts and updates can get slower, because Oracle has to update the base data and then the index[es] as well.  It's trade-off, like so much else in the Database World.


Check existence with EXISTS outperform COUNT! ... Not? I've often read when one had to check existence of a row should always be done with EXISTS instead of with a COUNT.

Yet in several recent scenarios I've measured a performance improvement when using count.
The pattern goes like this:

LEFT JOIN (
    SELECT
        someID
        , COUNT(*)
    FROM someTable
    GROUP BY someID
) AS Alias ON (
    Alias.someID = mainTable.ID
)

I'm not familiar with methods to tell what's happening "inside" SQL Server so I was wondering if there was a unheralded flaw with EXISTS that gave perfectly sense to the measurements I've done (could EXISTS be RBAR?!).

Do you have some explanation to that phenomena?

EDIT:

Here's a full script you can run:

SET NOCOUNT ON
SET STATISTICS IO OFF

DECLARE @tmp1 TABLE (
    ID INT UNIQUE
)

DECLARE @tmp2 TABLE (
    ID INT
    , X INT IDENTITY
    , UNIQUE (ID, X)
)

; WITH T(n) AS (
    SELECT
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM master.dbo.spt_values AS S
)
, tally(n) AS (
    SELECT
        T2.n * 100 + T1.n
    FROM T AS T1
    CROSS JOIN T AS T2
    WHERE T1.n <= 100
    AND T2.n <= 100
)
INSERT @tmp1
SELECT n
FROM tally AS T1
WHERE n < 10000

; WITH T(n) AS (
    SELECT
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM master.dbo.spt_values AS S
)
, tally(n) AS (
    SELECT
        T2.n * 100 + T1.n
    FROM T AS T1
    CROSS JOIN T AS T2
    WHERE T1.n <= 100
    AND T2.n <= 100
)
INSERT @tmp2
SELECT T1.n
FROM tally AS T1
CROSS JOIN T AS T2
WHERE T1.n < 10000
AND T1.n % 3 <> 0
AND T2.n < 1 + T1.n % 15

PRINT '
COUNT Version:
'

WAITFOR DELAY '00:00:01'

SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT
    T1.ID
    , CASE WHEN n > 0 THEN 1 ELSE 0 END AS DoesExist
FROM @tmp1 AS T1
LEFT JOIN (
    SELECT
        T2.ID
        , COUNT(*) AS n
    FROM @tmp2 AS T2
    GROUP BY T2.ID
) AS T2 ON (
    T2.ID = T1.ID
)
WHERE T1.ID BETWEEN 5000 AND 7000
OPTION (RECOMPILE) -- Required since table are filled within the same scope

SET STATISTICS TIME OFF

PRINT '

EXISTS Version:'

WAITFOR DELAY '00:00:01'

SET STATISTICS TIME ON

SELECT
    T1.ID
    , CASE WHEN EXISTS (
        SELECT 1
        FROM @tmp2 AS T2
        WHERE T2.ID = T1.ID
    ) THEN 1 ELSE 0 END AS DoesExist
FROM @tmp1 AS T1
WHERE T1.ID BETWEEN 5000 AND 7000
OPTION (RECOMPILE) -- Required since table are filled within the same scope

SET STATISTICS TIME OFF

On SQL Server 2008R2 (Seven 64bits) I get this result

COUNT Version:

  Table '#455F344D'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table '#492FC531'. Scan count 1, logical reads 30, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  SQL Server Execution Times:
     CPU time = 0 ms,  elapsed time = 81 ms.

EXISTS Version:

  Table '#492FC531'. Scan count 1, logical reads 96, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table '#455F344D'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  SQL Server Execution Times:
     CPU time = 0 ms,  elapsed time = 76 ms.

I've often read when one had to check existence of a row should always be done with EXISTS instead of with a COUNT.

It's very rare for anything to always be true, especially when it comes to databases. There are any number of ways to express the same semantic in SQL. If there is a useful rule of thumb, it might be to write queries using the most natural syntax available (and, yes, that is subjective) and only consider rewrites if the query plan or performance you get is unacceptable.

For what it's worth, my own take on the issue is that existence queries are most naturally expressed using EXISTS. It has also been my experience that EXISTS tends to optimize better than the OUTER JOIN reject NULL alternative. Using COUNT(*) and filtering on =0 is another alternative, that happens to have some support in the SQL Server query optimizer, but I have personally found this to be unreliable in more complex queries. In any case, EXISTS just seems much more natural (to me) than either of those alternatives.

  I was wondering if there was a unheralded flaw with EXISTS that gave perfectly sense to the measurements I've done

Your particular example is interesting, because it highlights the way the optimizer deals with subqueries in CASE expressions (and EXISTS tests in particular).

Subqueries in CASE expressions

Consider the following (perfectly legal) query:

DECLARE @Base AS TABLE (a integer NULL);
DECLARE @When AS TABLE (b integer NULL);
DECLARE @Then AS TABLE (c integer NULL);
DECLARE @Else AS TABLE (d integer NULL);

SELECT
    CASE
        WHEN (SELECT W.b FROM @When AS W) = 1
            THEN (SELECT T.c FROM @Then AS T)
        ELSE (SELECT E.d FROM @Else AS E)
    END
FROM @Base AS B;

The semantics of CASE are that WHEN/ELSE clauses are generally evaluated in textual order. In the query above, it would be incorrect for SQL Server to return an error if the ELSE subquery returned more than one row, if the WHEN clause was satisfied. To respect these semantics, the optimizer produces a plan that uses pass-through predicates:

The inner side of the nested loop joins are only evaluated when the pass-through predicate returns false. The overall effect is that CASE expressions are tested in order, and subqueries are only evaluated if no previous expression was satisfied.

CASE expressions with an EXISTS subquery

Where a CASE subquery uses EXISTS, the logical existence test is implemented as a semi-join, but rows that would normally be rejected by the semi-join have to be retained in case a later clause needs them. Rows flowing through this special kind of semi-join acquire a flag to indicate if the semi-join found a match or not. This flag is known as the probe column.

The details of the implementation is that the logical subquery is replaced by a correlated join ('apply') with a probe column. The work is performed by a simplification rule in the query optimizer called RemoveSubqInPrj (remove subquery in projection). We can see the details using trace flag 8606:

SELECT
    T1.ID,
    CASE
        WHEN EXISTS
        (
            SELECT 1
            FROM #T2 AS T2
            WHERE T2.ID = T1.ID
        ) THEN 1
    ELSE 0
    END AS DoesExist
FROM #T1 AS T1
WHERE T1.ID BETWEEN 5000 AND 7000
OPTION (QUERYTRACEON 3604, QUERYTRACEON 8606);

Part of the input tree showing the EXISTS test is shown below:

ScaOp_Exists
    LogOp_Project
        LogOp_Select
            LogOp_Get TBL: #T2
            ScaOp_Comp x_cmpEq
                ScaOp_Identifier [T2].ID
                ScaOp_Identifier [T1].ID

This is transformed by RemoveSubqInPrj to a structure headed by:

LogOp_Apply (x_jtLeftSemi probe PROBE:COL: Expr1008)

This is the left semi-join apply with probe described previously. This initial transformation is the only one available in SQL Server query optimizers to date, and compilation will simply fail if this transformation is disabled.

One of the possible execution plan shapes for this query is a direct implementation of that logical structure:

The final Compute Scalar evaluates the result of the CASE expression using the probe column value:

The basic shape of the plan tree is preserved when the optimize considers other physical join types for the semi join. Only merge join supports a probe column, so a hash semi join, though logically possible, is not considered:

Notice the merge outputs an expression labelled Expr1008 (that the name is the same as before is a coincidence) though no definition for it appears on any operator in the plan. This is just the probe column again. As before, the final Compute Scalar uses this probe value to evaluate the CASE.

The problem is that the optimizer doesn't fully explore alternatives that only become worthwhile with merge (or hash) semi join. In the nested loops plan, there is no advantage to checking if rows in T2 match the range on every iteration. With a merge or hash plan, this could be a useful optimization.

If we add a matching BETWEEN predicate to T2 in the query, all that happens is that this check is performed for each row as a residual on the merge semi join (tough to spot in the execution plan, but it is there):

SELECT
    T1.ID,
    CASE
        WHEN EXISTS
        (
            SELECT 1
            FROM #T2 AS T2
            WHERE T2.ID = T1.ID
            AND T2.ID BETWEEN 5000 AND 7000 -- New
        ) THEN 1
    ELSE 0
    END AS DoesExist
FROM #T1 AS T1
WHERE T1.ID BETWEEN 5000 AND 7000;

We would hope that the BETWEEN predicate would instead be pushed down to T2 resulting in a seek. Normally, the optimizer would consider doing this (even without the extra predicate in the query). It recognizes implied predicates (BETWEEN on T1 and the join predicate between T1 and T2 together imply the BETWEEN on T2) without them being present in the original query text. Unfortunately, the apply-probe pattern means this is not explored.

There are ways to write the query to produce seeks on both inputs to a merge semi join. One way involves writing the query in quite an unnatural way (defeating the reason I generally prefer EXISTS):

WITH T2 AS
(
    SELECT TOP (9223372036854775807) *
    FROM #T2 AS T2
    WHERE ID BETWEEN 5000 AND 7000
)
SELECT
    T1.ID,
    DoesExist =
        CASE
            WHEN EXISTS
            (
                SELECT * FROM T2
                WHERE T2.ID = T1.ID
            ) THEN 1 ELSE 0 END
FROM #T1 AS T1
WHERE T1.ID BETWEEN 5000 AND 7000;

I wouldn't be happy writing that query in a production environment, it's just to demonstrate that the desired plan shape is possible. If the real query you need to write uses CASE in this particular way, and performance suffers by there not being a seek on the probe side of a merge semi-join, you might consider writing the query using different syntax that produces the right results and a more efficient execution plan.


Sudden increase in Page Writes/s after patching / rebooting I was given a SQL Server 2008R2 to run, but my DBA skills are rusty after some years away from the day-to-day ops, so please bear with my (somewhat) silly questions.

This SQL Server is running in Azure with 14GB RAM, 8 cores and 3 sets of striped disks (two stripes per set).

Whenever I have to apply an Update and reboot the machine, Users experience a MAJOR system slowdown during the first 20/30 minutes of the next business day.

In ALL occasions I was online inspecting the behaviour, the only significant change I could detect was a sudden increase in Page Writes/s.  Further investigation pointed to a sudden increase in TEMPDB i/o ops.  I have then tried to increase TEMPDB's base size, but this still happens.  Event Viewer logs report nothing.  It's like SQL Server needs to "re-tune" itself under load after reboot.

As I said, the slowdown lasts for 30 minutes tops, but it is obviously annoying and I can't figure out a way to prevent it.

Any ideas?

Thanks in advance,

When you restart the service or reboot, the buffer pool and plan cache get wiped out as a result, and so when the server starts back up again, they don't get loaded until users start executing queries.

What you can do to mitigate this is have a stored procedure aimed at simply running some queries that will load up your most frequently-used data into memory. Call that procedure after a restart, and you will feel that initial delay instead of your users.


Postgres - using aggregate functions to return a different column? I currently have a table containing raw temperature data that looks like this:

I have written a query to find the min, max and avg of this data grouped by the date of the timestamp:

drop table solardb.weatherinfodaily;

select date_trunc('day',ts) as ts,
min("Air Temperature (Actual) (Degrees C)")*9/5 + 32 as "Daily Min Temp (F)",
avg("Air Temperature (Actual) (Degrees C)")*9/5 + 32 as "Daily Avg Temp (F)",
max("Air Temperature (Actual) (Degrees C)")*9/5 + 32 as "Daily Max Temp (F)"
into solardb.weatherinfodaily
from solardb.weatherdata
group by date_trunc('day',ts)
order by date_trunc('day',ts);

select * from solardb.weatherinfodaily;

I would like to return the timestamp that is associated with the aggregate function and I have not figured out a way to do this yet.

i.e. the query would return:

ts, Daily Min Temp, timestamp from raw data table when the min temp happened, daily avg temp, daily max temp, timestamp from raw data when max temp happened.

How can I write such a query?

You need to self-join the results of your current query with the base table (twice) on the values of temperature and date. Your query might look something like this:

with agg (day, min_c, avg_c, max_c ) as (
    select date_trunc('day',ts) as ts,
    min("Air Temperature (Actual) (Degrees C)") as "Daily Min Temp (F)",
    avg("Air Temperature (Actual) (Degrees C)"),
    max("Air Temperature (Actual) (Degrees C)")
    from solardb.weatherdata
    group by date_trunc('day',ts)
    order by date_trunc('day',ts)
)
select
  agg.day as ts,
  agg.min_c*9/5 + 32  as "Daily Min Temp (F)",
  data_min.ts as "Time of minimum",
  agg.avg_c*9/5 + 32 as "Daily Avg Temp (F)",
  agg.max_c*9/5 + 32 as "Daily Max Temp (F)",
  data_max.ts as "Time of maximum"
from agg
join solardb.weatherdata data_min
  on agg.min_c = data_min."Air Temperature (Actual) (Degrees C)"
  and agg.day = date_trunc('day',data_min.ts)
join solardb.weatherdata data_max
  on agg.max_c = data_max."Air Temperature (Actual) (Degrees C)"
  and agg.day = date_trunc('day',data_max.ts)

The above is not tested. I used the CTE to make logic more obvious, but one could use a simple subselect too.


LDAP authentication with Percona Server Reading over this post it seemed to indicate this plugin allowed user authentication via LDAP

  Percona Server is bundled with the PAM plugin which opens a plethora
  of ways to authenticate to MySQL such as restricting time when users
  can connect to MySQL, authenticate via a USB key, authenticate to an
  external authentication system such as LDAP and many, many more PAM
  compatible mechanisms.

However that intro paragraph is the only mention of LDAP in the entire post.  I've read through that as well as the general Percona PAM installation post. Every setup instruction seems to be talking about authenticating against a local unix account.  No mention of specifying an LDAP server or anything.

I found one other SourceForge project and some other mentions of perhaps slapping mysql_proxy infront.  Frankly that SF project doesn't feel very production ready.

Am I missing something about this percona plugin being able to authenticate via LDAP?

If I'm completely off base with that plugin perhaps my larger questions is "How can I get users authenticated via LDAP?"

I have used the Percona PAM Authentication Plugin for MySQL to successfully authenticate individual users via LDAP but so far I haven't managed to the group/proxy part working.

You do need to make sure that there is a PAM profile for MySQL, which is probably the bit that is missing (first question) from the article you linked. I had also read through that along the way.

I found this guide particularly helpful for getting it working (second question):

http://www.arubin.org/blog/2012/07/16/mysql-pam-ldap-authentication-module-configuration/

I'm still in the process of finalising this all and once I'm finished I'll blog about my experience and update this post with more information. Hope this helped!


MongoDB "select x from y where id in" another table I'm trying to understand how can I achieve this in MongoDB:
select *
from Table1
where id in
(selct id from table2)

I'm trying to aggregate but I just can't understand how JSON works:
db.Collection1.find
    (
            {"Field_from_Collection1":
                {$nin:db.Collection2.distinct("The_Field_I_want_to_compare")

                }

            }
    ).limit(1)

I need to find all information from collection1, but only if the ID is in collection2.
The reason I'm doing this is that I need to find the documents in Collection1, that has specifics IDS, and it's 600k IDS to look for.
as a SQL Server DBA, I'm really struggling to do this.

You should use $lookup for subquery.
Check out the document.
There is an example from the document.
db.collection1.aggregate([
{
   $lookup:
     {
       from: <collection to join>,
       localField: <field from the input documents>,
       foreignField: <field from the documents of the "from" collection>,
       as: <output array field>
     }
}


Troubleshooting high network traffic between primary and secondary replicas We have two SQL server hosts. There are about 12 availability groups, where the primary replica is on the "primary" host and the secondary replicas are on the "secondary" host. Now, for one reason or another, every Saturday, the network out of the primary and the network in of the secondary spike up about 600% compared to other days. It stays this way all day. It abruptly starts in the morning and abruptly stops at night.

Given the fact that the network in of the primary does not spike, we are assuming that the network traffic is somehow involved with an "internal" process between the two hosts.

This is obviously to general of a situation to just toss a solution at me. My question is, what data or troubleshooting techniques would be helpful with finding the reason behind this traffic?

I'm familiar with the SQL profiler and activity monitor, but I don't feel like these will be helpful if the network traffic is not the result of clients' queries. I can go as low-level as wireshark, but I don't think I'll be able to discern what the traffic is related to from the packets.

Some things we've already ruled out:

Heavy client query load on Saturdays
Scheduled jobs on Saturdays

If you have high outbound network activity on the primary server, and high inbound network activity on the secondary server, it is very likely that there is an increase in logged (write) actions in the availability group databases during this period on Saturdays.  these are getting synchronized to the secondary, resulting in a lot of network activity.

One option for catching this would be to log sp_WhoIsActive to a table during this time period.  You can come back and review these logs to see what queries were running to produce all of this extra AG sync activity.

You mentioned you ruled out scheduled jobs, but this sounds suspiciously like a maintenance activity - rebuilding indexes is something often done on weekends, and it produces tons of traffic in AGs.


Grant usage partially on schema to user on Postgres I granted INSERT in a specific table for one user. The problem is that I need to also grant USAGE in the schema for this same user, but granting USAGE in schema also turn all relations in that schema visible for that user.

I need that this specific user can only INSERT in this specific table and is unable of viewing other relations (tables, sequences, etc) in the same schema. By 'unable of viewing' I mean not being able of seeing that these relations exists.

Being even more clear, in the vision of this user, the schema has only that table inside and nothing more.

I suggest you create a dedicated schema for this user (if it does not exist already), typically with the same name as the name of the role.

CREATE SCHEMA this_user;
REVOKE ALL ON SCHEMA this_user FROM public;
GRANT USAGE ON SCHEMA this_user TO this_user;

The default search_path in typical Postgres installations is "$user",public. So the new schema is automatically the "current" schema for this_user. If your installation is different, consider setting it explicitly:

ALTER ROLE this_user SET search_path=`"$user",public`;

More about the search_path in this related answer on SO:

How to create table inside specific schema by default in Postgres?

Create a VIEW in this schema:

CREATE VIEW this_user.the_table AS
TABLE other_schema.the_table;  -- shorthand for "SELECT * FROM"

GRANT SELECT, INSERT ON this_user.the_table TO this_user;

You may need privileges on the SEQUENCE additionally, if a serial column is involved. See:

Explicitly granting permissions to update the sequence for a serial column necessary?

The same is not necessary for IDENTITY columns in Postgres 10 or later.

Since Postgres 9.3 simple views like the above are automatically updatable. Per documentation:

  Simple views are automatically updatable: the system will allow
  INSERT, UPDATE and DELETE statements to be used on the view in the
  same way as on a regular table. A view is automatically updatable if
  it satisfies all of the following conditions:

Then REVOKE all privileges from public, that should stay hidden from the public (including this_user).

More about default privileges and the basics:

Why can a new user select from any table?


Remote access from domain and other pages on that domain I know that you can access a database from a domain name with a user like this user@example.com but will this user also be able to access the database if they are trying to connect via another page on that domain i.e. example.com/page?

I am asking this as I am trying to connect from a domain name that will be establishing connections from different pages and sub folders i.e. example.com/admin/page, will setting it up with just example.com as the ending will all variables of that domain be accepted from the database connection?

From your example:

GRANT ALL PRIVILEGES ON *.* TO user@example.com IDENTIFIED BY 'password' WITH GRANT OPTION;

That reference to example.com has little to do with the web site example.com and particularly not with users of that site or pages on that site.

Machines with IP addresses are often reachable by hostnames.  Given a hostname, DNS provides you with a way to look up the IP address(es) associated with that hostname.  Reverse DNS provides the opposite -- given an IP address, look up a hostname that can be used to reach that IP address.

MySQL allows user accounts to be associated with an IP address or range (e.g. 'user'@'10.1.1.1' or 'user'@'10.1.%', meaning 10.1..) but it also allows the IP address matching the rule to be derived from reverse DNS.  For 'user'@'example.com' to work, the IP address of the machine connecting to the MySQL Server must reverse-lookup to example.com, and a forward lookup of example.com must then return the connecting machine's IP address.

As you hopefully see, this operating at a layer where the page a user on the example.com web server is completely meaningless, and if other web sites were hosted on that same server, the permission for other sites hosted on that server to access the MySQL Server would not be any different.  The hostname on in a mysql user account serves only to identify the machine making the incoming connection.  MySQL has no idea why that machine might be connecting.


Concurrency in NoSQL I am planning on writing a webapp where a user can buy an object from a seller.
Also I would like to outsource the payment so that I don't have to deal with the payment process myself. I was wondering how the following scenario would work out in a NoSQL Database like MongoDB?

What happens exactly if a user buys an object, but at the same time
the seller deletes that object, because he does'nt want to sell it
anymore?
How do I deal with it in a NoSQL Database like MongoDB?
Or do I have to do it in the application level?

I saw an example that a big travel company uses MongoDB, so my guess is that scenarios like that should be possible to fix, I just don't know how. Should I stick with an SQL-Database like PostgreSQL?

I suggest you read following documentation about MongoDB first. This is a very broad question for a forum like this. Unless somebody analyze your application requirements in details it is not possible to give a precise answer.

Broadly in MongoDB ACID property is respected for a single document and NOT for multi document.

Does MongoDB support ACID transactions?

Yes, but in a limited sense. MongoDB supports ACID transactions at the document level; today MongoDB does not support multi-document transactions. For many but not all applications, this is sufficient because data for a record tends to be managed as a single document. Like most databases, MongoDB uses write-ahead logging to an on-disk journal to guarantee write operation durability and to provide crash resiliency.

The distributed nature of MongoDB necessitates additional consideration beyond ACID regarding consistency and availability. MongoDB automatically maintains replica sets, multiple copies of data that are distributed across servers, racks and data centers for high availability ( see How does MongoDB ensure high availability? ) MongoDB is strongly consistent: all reads and writes are applied to the primary member by default. ( See How does MongoDB ensure consistency? ) Write operations are automatically applied to all replica set members. You can configure each write to return after success on the primary, on multiple set members, a majority of set members, or all members. Reads can be applied to the primary member, to secondary members if the primary is unavailable, to specific members exclusively (for workload isolation), or to the nearest secondary based on ping distance. You can configure your reads and writes to achieve the consistency and availability required for your application.

Read more about concurrency here.


Is it a good idea to use a personal number as a primary key for a customer table? We are designing a new system to handle customers at our firm and me and another developer are split on wether we should use the customers personal numbers as a primary key. My preference would be to auto increment a unique id (int) for each new customer.

Currently all customers we add do have a personal number but are there any good examples of why this may be a good or bad implementation?

One argument is that using an incremental id would simply add an extra field which is unnecessary and take up more space.

The personal number is stored as a varchar and the customer table holds close to a million customers. The personal number or id would be added as a foreign key for orders and customer support requests.

How do I know whether these are good or bad implementations? How would I argue either side?

Some good criteria for choosing or designing keys are: Simplicity, Stability and Familiarity but first you need to understand the fundamental business requirement your key is supposed to address. Ask yourself how you intend to identify customers in your business processes. Assuming you have a requirement to identify customers and Customer Number is the means your business will use to do that then it makes perfect sense as a key in your customer table - not necessarily the only key, but certainly a key of that table.


Can low memory cause Table Spool operators in plan I was trying to optimize a query and found that the plan had table spool(eager) in it.  But when I ran it on another server which had much more memory(256GB compared to 64GB) than the other one the operator was gone and the query was much faster.

So I am wondering if that memory is even the cause of the plan change which I see ie is it even a plausible theory?

volume of Data is the same.

SQL Server needs to have a reasonably correct estimate of how many rows your query is going to handle. If it doesn’t, things can go terribly, terribly wrong.

MOre info

https://www.brentozar.com/archive/2013/08/query-plans-what-happens-when-row-estimates-get-high/


Only one administrator can connect at this time (MS SQL Server error 18461) I was experimenting the effect of giving SQL Server a small amount of memory I thought it was going to recover.

I configured SQL Server to use 200MB of memory now it does not want to start, I did some searches on the internet and I was advised to start SQL Server in single-user mode. however, I get the error

  Login Failed for user A. Reason: Server is in single user mode. Only
  one administrator can connect at this time (Microsoft SQL Server,
  Error: 18461)

I have stopped the SQL Server Agent.

If you start SQL Server in single user mode only one administrator can connect at the same time.

What's probably happening here is that some service is using a sysadmin login to connect such as Reporting services or SQL Server agent.

When starting SQL Server in single user mode you have the option to specify a client application so only that application can connect.

Have a look at the command line options where it lists:

  -m "Client Application Name"

  When you use the -m option with SQLCMD or SQL Server Management
  Studio, you can limit the connections to a specified client
  application. For example, -m"SQLCMD" limits connections to a single
  connection and that connection must identify itself as the SQLCMD
  client program. Use this option when you are starting SQL Server in
  single-user mode and an unknown client application is taking the only
  available connection. To connect through the Query Editor in
  Management Studio, use -m"Microsoft SQL Server Management Studio -
  Query".

  Client Application Name is case sensitive.

If you are able to connect this way, change the maximum server memory to something reasonable. I suppose you will be able to connect because otherwise you would probably receive something as "there is no process on the other end of the pipe" so I would assume your server is running.

If however you can't log in to SQL Server because your memory configuration doesn't allow you, you can start SQL Server in minimal configuration using the -f option.

  -f

  Starts an instance of SQL Server with minimal configuration. This is
  useful if the setting of a configuration value (for example,
  over-committing memory) has prevented the server from starting.
  Starting SQL Server in minimal configuration mode places SQL Server in
  single-user mode. For more information, see the description for -m
  that follows.

For reference: SQL Server can’t start after accidently set the "max server memory" to 0


How can I create notification on user's connecting to database? I have users that have access to several databases.
I used to have an alert and notification sent to me when a specific user connects to a specific database, but I accidentally clobbered it. Now I have to recreate it.

If I understand correctly, logon triggers won't help, since I don't need to know when they connect to other databases.

Please help!
Thank you!

Sergey

There are three ways to find who is connected to a database in sqlserver.

First one:

Use the SQL SERVER Management Studio -- Management -- Activity Monitor
This gives a list of users, database names, status, command, transactions, application with which they are using the database, CPU, IO and Memory usage, Login time etc.

Second One:

Use the built in stored procedure called sp_who2
Run the command exec sp_who2
This gives a list of users, database names, status, command, program with which they are using the database, CPU time, Login time etc.
Third One:

Third one Use the script

DECLARE @temp TABLE(spid int , ecid int, status varchar(50),
                     loginname varchar(50),
                     hostname varchar(50),
blk varchar(50), dbname varchar(50), cmd varchar(50), request_id int)
INSERT INTO @temp

EXEC sp_who

SELECT COUNT(*) FROM @temp WHERE dbname = 'DB NAME'

From the above methods, you can use them to implement in a sql job to get required notification.

The best method i used is the latest sp_whoisactive by MVP Adam Machanic


associate different credit cards with every purchase I have a schema in which I store data regarding customer's credit cards and I want to find a way to associate each order (can have multiple payment entries -partial payments-) with its the selected credit card in the checkout funnel
So far I have
Order
-----
id
status (started, completed)

customer_cards
----
id
customer_id
card_id (entity card_token_id)
other card_data

Payment_entries
-----
id
payment_token (charge_id from entity, like stripe)
order_id (FK)
payment_type_id (FK to payment_type) (in this case id for stripe entry in payment_type)
status

I would like to know which customer_cards has been used to place an order (first order-payment_entry entry)
I've been thinking on adding a new column in Payment_entries that stores the card_id or customer_cards id of each payment entry but I'm worried It would not scale if we add a different payment_type
Other idea is to add a table with the relation order_id - customer_cards but it sounds a bit redundant to me.
What do you folks advise?
Thanks

I think it's fine to do your first idea with adding the id of customer_cards as a field to the Payment_entries table. This then makes it a linking table between Order and customer_cards so you can answer the question "which customer cards have been used to place an order?".
For scalability, I would advise calling the new column something like payment_method_id in Payment_entries that way it's generically re-usable for other payment_types you may add as you scale.


PostgreSQL list of integers separated by comma or integer array for performance? As per subject, I have a field which is varchar in which i store some numbers separated by commas. The list grows and shrinks based on clients choices. I then use it in queries to check if a particular value is in that list against

value || ','

It is working well but was thinking of converting it to an integer array.

Would this be a better solution both in the technical/performance way and to properly program the database?

It's highly likely that the best approach will be a side-table of sometable(main_id, value) where you have a composite index on (main_id, value). This allows very fast lookups to see "for this mainid, does this value exist". This will let you enforce foreign key relationships. Unless you have a good reason, use this conventional relational approach.

Failing that, you can and should use an array field instead of a comma separated list. Using a comma-separated list is just downright horrid from a design point of view. It makes queries harder to write, more error prone, and forces you to do lots of slow and inefficient string manipulation and number parsing just for simple operations, and prevents any kind of integrity checking without very inefficient CHECK constaints or triggers. I think Bill nailed it with:

  1,2,3,banana,5

With an array you can use the intarray extension to provide a GiST index that lets you quickly test if the array contains a given value using an indexable @> or <@ operation. You may want to add the btree_gist extension too, so you can create a composite GiST index of main_id, the_values_array, in case your queries are usually of the form:

WHERE main_id = blah AND the_values_array @> ARRAY[42]

(or have two separate indexes and see if it'll do a bitmap index scan).

You can't enforce a foreign key relationship into an array in PostgreSQL yet, though the feature seems to be on the way. You'd need to do it with somewhat complicated triggers in the mean time. Still, it's a lot better than a comma-separated list.


How to link each column in sql? I have been working around on a query given by one of my developers who works on crystal reports as a junior DBA, I always like to help them even though I am busy with other things as it will increase  my knowledge in T-sql. The current scenario was simple as to do a self join. the schema for this scenario is given below

create table resource(
res_id int,res_name varchar(20), man_id int)

populating the content and running the query below give the resource name and its manager

SELECT a.res_name,b.res_name AS manager FROM dbo.RESOURCE a INNER JOIN dbo.RESOURCE b
ON b.res_id=a.man_id

res_name   manager
------------------
sam       will
sunny     helen
will      micah

but next time my friend entered into my cubicle he need a different answer from the above he needs a result like this

sam will micah

so how will I chain the results I no idea where to start.

Are you looking to create a view like this:

SELECT  t1.employeeid ,
    t1.managerid ,
    t2.managerid
FROM    emp t1
    INNER JOIN emp t2 ON t1.managerid = t2.employeeid

If you want to get the employee hirarchy you should use recursive CTE. Check this article at SqlAuthority: SQL SERVER – Introduction to Hierarchical Query using a Recursive CTE.


Using Mixed mode authentication to authenticate Active Directory users to an application I have a vendor supplied application that I have very limited access to change.  The application controls a machine, and cannot easily be logged off between users (expect up to 5 user changes per hour).  I also have to deal with Part 11, and Corporate rules.

The plan is to have a shared login to the PC (AD user), then the application which has to connect to a MS SQL database anyways, will prompt for the user name and password, which it will use for the database connection.  (previous user's DB connection will be closed between users).  If the user/password does not authenticate, they will not be able to use the application.

I have AD authentication working, and I can connect with Server Management Studio using either a SQL user, or an AD user.  The application can also connect using a SQL user.  But the application always fails to connect when I attempt to use an AD user (I am only substituting the user/password, I have also tried to prepend the "Domain\" to the user).  The application is using the default MS SQL drivers that are loaded when Server Management Studio is installed.

Is this even workable?

Having an application connect would be very different than an end user on SSMS.  Applications tend to use connection strings.  Your applications connection string or config might need to be modified to connect to the SQL Server as an AD user as opposed to putting in a username/password for a SQL user.

You will want that user to have the same rights as the SQL User and probably want it to own the database.  Contacting the vendor might be a good case.  Going with AD users gives you more auditing visibility anyways which should please auditors.

Some connection string examples:
https://www.connectionstrings.com/sql-server-2012/


Difference between "Field Specification" and "Field Domain" I'm currently in the process of learning (Relational) Database Design based on two popular books, online tutorials and articles.

Some places, when they talk about attributes constraints, they call it an Attribute Domain.
Some other places, when they talk about attribute specification, they call it a Field specification.

However, some other places, they say that they are both the same, but attribute domain seems to me to be part of the field specifications since by definition, attribute domain sounds like the Physical Element part of field specification:

  Physical elements: Data type, Length, Decimal Places, Character support, input mask, display format

What is it now?

Attribute domain defines the range of acceptable values for an attribute at the logical model level, e.g. "an arbitrary string representing a person's name", "a non-negative integer", "a boolean (true or false) value" etc.

Column specification defines SQL properties of a database column that is used to store the corresponding attribute values in an SQL database (i.e. at the physical model level). Sometimes they are more restrictive than the attribute specification, e.g. you must specify the maximum length of a character column, although the attribute domain may not prescribe it). Sometimes they are less restrictive, for example you cannot represent a "non-negative integer" by an SQL INTEGER data type without additional constraints. Some column specifications, such as input mask or display format, have nothing at all to do with the domain specification and exist solely as technical implementation details of the application in question.

I prefer not to use the term "field" when discussing relational databases.  Relational tables have rows and columns. Using "fields" and "columns" interchangeably often brings confusion. Input elements of a user interface or components of a sequential file records can be referred to as "fields".


Cross-schema Foreign Keys Can a foreign key in one MySQL database/schema refer to a table in a different database/schema?

If so, what limitations are there on doing this?

Yes, you just need to specify the schema before the table name. For example, if the referencing table is in schema qa and the referenced table in xx:

ALTER TABLE qa.test_results
    ADD COLUMN file INTEGER,
    ADD FOREIGN KEY (file) REFERENCES xx.files (file) ;

Then all of your queries will also need to be formatted using schema.table


DateTime conversion fails when the datetime has seconds This seems to be most discussed, but I could not arrive at a solution yet. I have a date format and have to add a month to it (DATEADD should not be used as my date is string initially) and finally should convert it as date. 103 format converts but it does not give seconds.
Below is the code I tried
    Declare @time_info nvarchar(max)
Declare @format_time_info nvarchar(max)
Declare @time_info_final nvarchar(max)
Declare @time_detail datetime
SET @time_info='30/1/2020 20:34:16 6 300'

Select @format_time_info =  (Select REPLACE(@time_info,('/'+SUBSTRING( @time_info,
CHARINDEX('/', @time_info) + 1,
LEN(@time_info) - CHARINDEX('/', @time_info) - CHARINDEX('/', REVERSE(@time_info)))+'/'),
(CONCAT('/',(convert(varchar,(convert(int,(SUBSTRING( @time_info,
CHARINDEX('/', @time_info) + 1,
LEN(@time_info) - CHARINDEX('/', @time_info) - CHARINDEX('/', REVERSE(@time_info)))+1))))),'/'))))

Select @time_info_final =(Select left(@format_time_info, charindex(' ', @format_time_info, charindex(' ', @format_time_info)+1)-1))
 Select @time_detail =  CONVERT(datetime, @time_info_final,103)-- Cast(@time_info_final as datetime)-- Convert(datetime,Cast(@time_info_final as datetime2),210) --
Print @time_info_final
 Print @time_detail

This throws the common exception
In this query I used 103
Select time_info_final =(Select convert(datetime,left(@format_time_info, charindex(' ', @format_time_info, charindex(' ', @format_time_info)+1)-1),103))

This is the dateformat I get from source '30/3/2020 20:34:16 6 300' or sometimes '30/03/2020 20:34:16 6 300'. So I used substring to find '/' and added date. The thing that troubles me is the seconds is always 00 ... looks 103 format changes it. Using other formats throws error while converting

Well, February doesn't have 30 days, so you can't naively just add 1 to the month number and expect to end up with a valid date. (Not to mention leap years, Dec-to-Jan wraparounds that should advance the year, etc.)
I think you'll find it much easier to convert to a date first, then use DATEADD. Then, if you like, convert back to a string format:
DECLARE @origstring VARCHAR(40) = '30/1/2020 20:34:16'
DECLARE @origdate DATETIME, @newdate DATETIME

SET @origdate = CONVERT(DATETIME, @origstring, 103)
SET @newdate = DATEADD(MONTH, 1, @origdate)

SELECT FORMAT(@newdate, N'MM/d/yyyy HH:mm:ss')

Your original string '30/1/2020 20:34:16 6 300' doesn't appear to be a valid date, the problem is the  6 300 at the end. If that's in your source data, just trim that off when you convert:
SET @origdate = CONVERT(DATETIME, LEFT(@origstring, 18), 103)


Is it Okay for mysql to hit 59% without no query running in my site? Please I need some little help from you guys.

I have a VPS I bought since 2018 and since then one of the most difficult thing i have been facing is high load on my SQL, but i guess that was due to my big database of 1GB size.
my vps spec is 16GB RAM & 6 cores.

Recently I have sold the site and deleted the 1GB database which was causing me issues on mysql server. Ever since then I have little wp sites on the server with No much traffic, but I still have mysql hitting up to 59% sometimes. I have check to see if its long query that is taking it but nothing at all.

One day I even switch off my apeche just to make sure I see the sql server uses low cpu process but to my greatest surprise I am getting at least 5-15%

I thought since no apache is connected the sql server has to fall to 0.2 or i don't even want to see it among my list of processes when i run top command.

Please Guru in the house is it okay to get this limit while no website is running on the server. and what are the optimization needed to stop it from reaching such percentage even with no long query running on the websites. or is there an evil spirit from the old 1gb deleted database that is still disturbing the mysql server. help me out please.

With nothing running, mysqld will use 0% of CPU, therefore you must have something running. Enable general log, that will show you exactly what it's doing.

And 1GB isn't a particularly big database.


Postgres - Multiple commands on table at the same time How robust is Postgres when it comes to running multiple commands on a table at the same time?

Can I for instance, set a primary key and create a couple of indexes using multiple separate commands at the same time?

How about a query that will only affect/change data in one column? Is it ok to run other commands as long as they don't interfere with the column affected by the first command?

You can't run multiple DDL statements at the same time because each statement will request an exclusive lock. So if you add a primary or foreign key, the table is locked.

You can however create multiple indexes at the same time using the concurrently option.

Without the concurrently this will also block write access to the table while the index is created (read access is still possible in any case).


SQL Express 2008 R2 on Amazon EC2 instance: tons of free memory, poor performance The old SQL Express 2005 was running on a low-end single Xeon CPU Dell server, RAID 5 7200 disks, 2 GB RAM (SBS 2003).

I have not done any baseline measurements on the old physical server, but the Web app is used by half a dozen people (maybe 2 concurrently), so I figured "how bad can an Amazon EC2 instance be?".

It's pretty horrible: a difference of 8 seconds of load time on one screen.

First of all, I'm not a SQL guru, but here's what I've tried:

Had a Small Instance, now running a c1.medium (High Cpu Medium) Windows 2008 32-bit R2 EBS-backed instance running IIS 7.5 and SQL Express 2008 R2.  No noticeable improvement.
Changed Page File from fixed 256 to Automatic.
Setup a Striped Mirror from within Disk Management with two attached 1 GB EBS volumes. Moved database and transaction log, left everything else on the boot EBS volume.  No noticeable change.
Looked at memory, ~1000 MB of physical memory free (1.7 GB total).  Changed SQL instance to use a minimum of 1024 RAM; restarted server, no change in memory usage.  SQL still only using ~28MB of RAM.

So I'm thinking: this database is tiny (28MB), is the whole thing cached in RAM?  Can I confirm? Surely that would speed up performance.  The transaction log is 241 MB.  Seems kind of large in comparison -- has this not been committed?  Is it a cause of performance degradation? I recall something about Recovery Models and log sizes somewhere in my travels, but not positive.

Another thing: the old server was running SQL Express 2005.  Not sure if that has any impact, but I tried changing the compatibility level from SQL 2000 to 2008, but that had no effect.

Anyways, what else can I try here?  Seems ridiculous to throw more virtual hardware at this thing.  I know I/O is going to be rough on EBS volumes, but surely others are successfully running small .NET/SQL apps on reasonably priced instances?

Decided to uninstall SQL Express 2008 and install SQL Express 2005.  Restored the database (same one I had originally backed up for the migration), load time for the one page was down to ~6 seconds from ~13 seconds.

Reindexed the database for fun and profit and checked consistency.  Seemed fine (return code 0).


Cassandra row ordering best practice I have a column family that store articles.
I'll need to get those articles from the most recent to the oldest, getting them from Country, and of course the ability to limit the number of fetched articles.

I though about another ColumnFamily "ArticlesByDateAndCountry" with dynamic columns

The Key would a mix from the 2 Char country Code (ISO 3166-1), and the articles day's date
so something like : US-20150118 or FR-20141230 -- (XX-YYYYMMDD)

In those Row, the column name would be the timeuuid of the article, and the value is the article's ID.

It would probably get a thousand of articles per day for each country.

Let's say I want to show only 100 of the newer articles, I'll get the today's articles, and if it does not fill the request (too few articles), I'll check the day before that, etc...

Is that the best practice, or does someone has a better idea for this purpose ?

I thought about another ColumnFamily "ArticlesByDateAndCountry" dynamic columns.

You're on the right track with this thought, but I'd stay away from dynamic columns.  Currently, there isn't a way to really manage column families with dynamic columns in CQL3.  So, the only real solution is to go the route of creating it in the cassandra-cli, which is being deprecated.  Sticking with CQL tables gives you a much easier path to data access, and that in itself (IMO) makes it worth it.  Besides, all the latest drivers work with CQL, so you're really backing yourself into a corner by choosing a path that can't be managed by it.

While it may not be readily apparent, there is a way to solve your current problem to adequately serve your queries.  I would build a (CQL) table, like this:

CREATE TABLE ArticlesByDateAndCountry (
 countrycode text,
 articledate timestamp,
 field1 text,
 field2 text,
 PRIMARY KEY ((countrycode),articledate))
WITH CLUSTERING ORDER BY (articledate DESC);

Note: I have created two sample payload fields, field1 and field2.  I'm sure your payload fields will vary.  Also, I have opted to use a timestamp instead of a timeuuid, as it makes the example easier.

Essentially, this will group your data by countrycode.  And within each countrycode, your data will be sorted by articledate.

SELECT articledate, field1, field2
FROM ArticlesByDateAndCountry
WHERE countrycode='US'
AND articledate >= '2015-01-23 00:00:00' AND articledate < '2015-01-24 00:00:00';

You should read Patrick McFadin's article Getting started with Cassandra time series data modeling.  It has several examples that are quite similar to what you are doing here.  While I have demonstrated this with the timestamp type, you could very easily make this work with time UUIDs instead.  Here is a link to DataStax's documentation on Cassandra's timeuuid functions that I am sure you will find useful.


Autoclose property resets to true after attaching db even if model is set to false I am using SQL Server Express 2008 R2.
The auto close property of a newly created database will be set to value same as model db. In my case I have set autoclose to false for model so that when I create a new database it will have autoclose set to false as well.

If I detach a database with autoclose set to false and then attach it, autoclose is set to true. I was expecting autoclose to be false since it will inherit its properties from the model database. In my case detaching and attaching a database results in the autoclose property being set to true.

Why is it so? Is there any solution to this problem?

Attaching a database does not involve model at all, so there is no automatic way to enforce that your model database settings are applied to any database that you later attach. This is only used for databases that you CREATE, and even more restrictive, databases that you create without FOR ATTACH or FOR ATTACH_REBUILD_LOG.

In Express, attached databases are automatically attached as AutoClose = true, so there is nothing you can do with model, or the attach process itself, to set it to false. You need to either manually set it to false after attach, or I suppose you could set up a periodic job that checks if this setting is enabled on any database that has been created since the last time the job has run (or without a filter at all), and set it to false for you.

Why does Express work that way? Because Express is largely designed for local development, and to conserve resources on a developer's workstation (which typically doesn't have a whole lot of resources to spare, compared to production servers), it only holds open databases that are actively being used. This topic in Books Online helps explain, though it doesn't explicitly state that any database attached to Express sets this property to true.

If you don't want to deal with this (and other Express limitations), you could always use Developer Edition.


pivot on date and count values by hour I hope this makes sense.  I have a database which logs the day and time a row was inserted. I have a query which will select and count the number of insertions per hour.
I would like to pivot this so that it shows the date as rows and the hours as columns.

eg:

  DateTime      Hour1     Hour2    Hour3    |
+-----------------------------------------+
| 2016-01-01     11        10      35      |
| 2016-01-02     12        15      25      |

My current query shows as :

  DateTime      Hour       total    |
+-----------------------------------+
| 2016-01-01     1          11      |
| 2016-01-01     2          10

Query :

SELECT CAST([datetime_received] as date) AS ForDate,
       DATEPART(hour,[datetime_received]) AS OnHour,
       COUNT(*) AS Totals
FROM [claims]
where [datetime_received] between '2018-08-03 00:00:00.000' and '2018-08-03 23:59:00.000'
GROUP BY CAST([datetime_received] as date),
       DATEPART(hour,[datetime_received])

I do not have much experience with pivot queries so any help would be appreciated.

thanks

Ok, so after few trial and error queries I managed to solve this.

It may not be the best way but it seems to work fine.

select *
from
(
  select CAST([datetime_received] as date) AS ForDate,   DATEPART(hour,[datetime_received]) AS OnHour,     COUNT(*) AS Totals
  from [claims]
  where [datetime_received] between '2019-07-01 00:00:00.000' and '2019-07-31 23:59:00.000'
  GROUP BY CAST([datetime_received] as date),
       DATEPART(hour,[datetime_received])

) src
pivot
(
  sum(Totals)
  for OnHour in    ([0],[1], [2], [3],[4], [5], [6],[7],[8], [9], [10],[11], [12], [13],[14], [15], [16],[17],[18], [19],[20],[21], [22], [23])
) piv
order by ForDate

If any one can help me refine this i will appreciate it.

Thanks


Do we need to shutdown DB/Stop DB service before Windows server patching Before patching Windows server, do we need to shut down any DB or stop DB service.

If we need to shutdown a MongoDB on a node(Windows server) which is part of a replica set, what we need to before patching windows server..

Thanks

To upgrade a replicaset, please follow MongoDB upgrade process.
Here's a quick summary:

upgrade all secondary nodes - if necessary check if everything's working properly
stepDown and upgrade the primary node
set the setFeatureCompatibilityVersion to the version matching your upgrade


Shopping cart database, should order object have a relationship with the product, or should I duplicate the product information I am creating a shopping cart and an currently planning / building the checkout process. Everywhere I look I see the recommendation to have an order table, and in that order table have a productId for the product that is being ordered... That seems nice. But I can not seem to get over the issue of what happens of that product is updated or worse deleted? Then the record of what is ordered is updated or deleted. Would it be better to duplicate the product details into an OrderedProduct table so you have a snapshot of what the product looked like when it was ordered? Then even if the product is changed or deleted, you have all the information from when it was ordered? That is what I am doing, but want to make sure I am not making a mistake or there is a better way to accomplish it.

If the item changes or gets updated, it should get a new ItemID. You need the historic record. So say you have a shirt and it comes in blue ItemID Shirt. If you offer it in Green change the new ItemId to be ShirtGreen. iF it is an upgrade, say offering blue shirt with a  pointed collar: ItemID: ShirtPointedCollar.


Joining three tables with null values in SQL I have a BOOKS table, a USERS table, and a RATINGS table (with a book id, a user id, and a rating value).
For each book, I would like to be able to get an array with all the users and their rating for the chosen book BUT more importantly, if the user has not rated the book, I still want it in the array but with a rating value of null, as follows :

user
book
value

1
1
3

1
2
null

2
1
null

2
2
1

And so on..
The closest way I could get to it was by doing :
SELECT u.id as user, b.id as book, r.value
        FROM users u, books b
        LEFT OUTER JOIN ratings as r ON r.book_id = b.id
        ORDER BY b.id ASC

But if I try to add LEFT OUTER JOIN ratings as r ON r.book_id = b.id AND ON r.user_id = u.id as my final clause, it obviously doesn’t work. Wrong result: it gives me the first rating found for each book, not the one related to the user.
Any idea how I could achieve this?

Try to use explicit join. It seems that MySQL has some issue referencing the implicit cross join.
select b.id, u.id, rating
  from books as b
   join
       users as u on 1=1
  left join ratings on u.id = userid and b.id = bookid


Seconds_Behind_Master alternates between 0 and a number x I have a slave server with mariadb 10.3.10 on debian 9. For several days the server has been working correctly, Seconds_Behind_Master was always kept at 0 indicating that it was properly synchronized. Since yesterday Seconds_Behind_Master has started to alternate between 0 and numbers greater than 1000. I run show slave status and see:
  Seconds_Behind_Master: 3893, then I run it again and Seconds_Behind_Master: 0 then Seconds_Behind_Master: 3895 and it has kept growing and decreasing. Can someone help me solve this problem?

Seconds_behind_master doesn't exactly measure the replication lag. It measures the difference between the timestamp of the last binlog event fetched by the IO thread, versus the timestamp of the last binlog event executed by the SQL thread.
This can become misleading for example if the master has more binlogs that the replica hasn't fetched yet. If the network between the master and replica is too slow for the rate of binlogs, the replica might check the timestamp difference and conclude incorrectly that it is caught up. Then the IO thread fetches another batch of binlogs, and suddenly the timestamp difference is great again, and it looks like the replica is not caught up.
In my environment, when we see this happen, it often affects the cross-country replication link when the network is degraded.
It can also happen if the master is writing a lot of bulky content to the binlogs in ROW-based binlog format. A good example is a database that has records with large BLOB/TEXT content, but it makes rapid updates to another column of the same row. By default, ROW-based binlogs write the entire row to the binlog, even columns you did not change. So a simple update to a status column may result in many megabytes being written to the binlog, and causes a delay in transfer over the cross-country link.
The replica downloads binlog events as fast as it can, but it naturally flip-flops between "I'm all caught up" versus "I'm way behind" if bulky binlog events keep arriving.
Solutions: set binlog_row_image=MINIMAL on the master. This causes ROW-based binlogs to store only the columns that have changed.
If you want to get a more accurate measure of replication lag, use a heartbeat tool like pt-heartbeat.


Is there any way to prevent the memo structure from being pruned? We know that the memo structure is pruned and some expensive alternative plans are discarded during optimization. I was wondering if there is any way to prevent this and let the optimizer just consider every possible plan and select the best from all alternatives?

We know that the memo structure is pruned and some expensive
  alternative plans are discarded during optimization. I was wondering
  if there is any way to prevent this and let the optimizer just
  consider every possible plan and select the best from all
  alternatives?

There is, but I don't publicise it because it would be misunderstood and misapplied. In any case, it would not result in an exhaustive search of the plan space because only a limited set of transformations (ones that commonly produce good results) are implemented.

Preventing pruning and discarding would generally simply result in (much) longer compilation times without much of an improvement in final plan quality, if any.

Ultimately, the question is a natural and reasonable one, but it is predicated on a misunderstanding of the goals of the SQL Server query optimizer: it is designed to find good plans for common queries quickly. It is not built on a framework designed for exhaustive search.

If you have a real-world situation that would benefit from a different approach to optimization, you could make the case for it at the Connect website (though I do think it unlikely Microsoft would invest the necessary engineering resources).


Better to use a RANGE RIGHT or RANGE LEFT Partition Scheme I'd like to know if it is better to use a RANGE RIGHT partition scheme or a RANGE LEFT partition scheme?

Is one of these options by default a better choice, or is it heavily dependent on the actual data?

Once you've already used a RANGE LEFT, how difficult is it to switch to a RANGE RIGHT? Or is it easier to just add in splits in the current scheme?

It's not a question of better or worse, but business requirements will drive that choice. For instance, we're talking about whether or not you want your defined boundary values to live in one partition or the next.

An example of a boundary value of '9/12/2013', would you want that boundary valued data to live in partition x (range left) or partition x + 1 (range right)?  This will especially come into play when you're thinking about things like individual partition management, as well as partition switching for purposes such as archiving.


ORA-00904: "GET_JAVA_PROPERTY": invalid identifier - 11gR2 In Oracle DB 11.2.0.4, linux 6.7 single instance

select get_java_property('java.version') from dual;

SQL> show user
USER is "SYS"
SQL> select get_java_property('java.version') from dual;
select get_java_property('java.version') from dual
       *
ERROR at line 1:
ORA-00904: "GET_JAVA_PROPERTY": invalid identifier

I can't find anything on this ORA-00904: "GET_JAVA_PROPERTY": invalid identifier, anyone know why this happens?

This works fine

SQL> SELECT  dbms_java.get_ojvm_property(PROPSTRING=>'java.version') FROM dual;

DBMS_JAVA.GET_OJVM_PROPERTY(PROPSTRING=>'JAVA.VERSION')
--------------------------------------------------------------------------------
1.6.0_191

SQL>

thanks

Well, you found it somehow, didn't you?

It was never part of the database officially, it is just some custom function you can find on the Internet. For example: https://stackoverflow.com/questions/28164252/how-to-check-jdk-version-in-oracle

create function get_java_property(prop in varchar2)
return varchar2 is
language java name 'java.lang.System.getProperty(java.lang.String) return java.lang.String';


MySQL Varchar Primary Key With Not Null Constraint Accepting Nulls I have the following table definition:

CREATE TABLE `invite` (
  `id` varchar(255) CHARACTER SET utf8 NOT NULL,
  `email` varchar(255) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uc_i_e` (`email`)
)

My goal with the not null constraint on id is to make sure there are no null values as the primary key, and to ultimately fail to insert.

However, not specifying the id on insert works just fine:

INSERT INTO invite (`email`) VALUES ('e@mail.com');

There is an 1364 warning specifying Field 'id' doesn't have a default value, but the insert succeeds just fine.

It looks like MySQL is using the not null constraint to turn this into a non null (blank) value at insert.

If I actually specify null as a value of id, the insert fails as expected.

Without having a trigger to stop empty strings being inserted, how can I stop this scenario from happening, i.e force a non empty string value to be specified?

To prevent this insert from occurring, turn on strict mode.

mysql> set session sql_mode = 'STRICT_TRANS_TABLES';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> INSERT INTO invite (`email`) VALUES ('e@mail.com');
ERROR 1364 (HY000): Field 'id' doesn't have a default value
mysql> select * from invite;
Empty set (0.00 sec)

Note: Be sure to follow the description in the link I provided, NOT copy my command regarding setting sql_mode. Otherwise you may disable other settings.


Setting up Master - Slave replication: error connecting to master (Error_code: 2003) I'm configuring this across two different servers on two different hosting companies.

Here is the tutorial I've followed for setting this up: https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-in-mysql

The master my.cnf settings are as follows:

bind-address = 00.000.000.00
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = mydatabase

On the slave:

server-id = 2
relay-log = /var/log/mysql/mysql-relay-bin.log
expire_logs_days = 10
max_binlog_size = 100M
binlog_do_db = mydatabase

Everything looks good until I try and check mysql> SHOW SLAVE STATUS\G

*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 000.000.000.00
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: mysql-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 0
Relay_Log_Space: 154
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 2003
Last_IO_Error: error connecting to master 'slave_user@000.000.000.00' - retry-time: 60  retries: 3
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 180104 23:22:36
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:

I've checked the error log as well and can see this:

2018-01-04T23:05:10.948738Z 2 [ERROR] Slave I/O for channel '': error connecting to master 'slave_user@000.000.000.00:3306' - retry-time: 60  retries: 6, Error_code: 2003

I've checked the firewall and the port, which looks correct. I'm not sure what else I can check?

So, I actually had several issues that may come in handy to walk through for others:
Error: 2003
I removed the bind-address from the master
Errno: 1045
Access denied for user
I explicitly set the IP address for the slave user:
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'000.000.000.00' IDENTIFIED BY 'password';
instead of
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';
Error 1236
Got fatal error 1236 from master when reading data from binary log: 'log event entry exceeded max_allowed_packet
It turns out the default max_allowed_packet is 1MB for <= 5.6.5 and 4MB for >= 5.6.6.
I set both of these manually to 16MB as per this article
Error 1236
Got fatal error 1236 from master when reading data from binary log: 'bogus data in log event
I followed the instructions in this answer
That solved the last of my errors and the replication appears to be working as expected now!


BULK INSERT - best usage I need to export 150 million rows of only int/bigint columns from one SQL instance to another. I am using BCP queryout to export the rows and BULK INSERT to import the rows to another table.

I split 150 mln row into 3 BCP Export files each of 50 million rows and 3 BULK INSERTS. It worked (15 min BCP out/ 40 min Bulk Insert), but I would rather export just one file and import as well one file.

Is it technically possible or I can have issues for the big amount of rows? I never Imported more than 50 mln rows from a BCP file and I am not sure how this can work out. Is there a limit for BCP export or Import?

Thanks,

It is technically possible, yes. But you may be able to leverage performance gains by having multiple files on multiple disks as opposed to just one file. Why not try using one file and see if it works, and what the performance is like?

Here is a link for info on BULK INSERT: http://msdn.microsoft.com/en-us/library/ms188365.aspx