Minimal 3NF normalization issues I'm trying to learn how to normalize to 3NF but the example that my teacher gave us doesn't really help.

The algorithm I always used is this one:

Find a canonical cover F_c for F
For each FD X->Y in F_c create a relation with schema XY
Eliminate a relation if its schema is a subset of another
Merge those remaining relations into a relation which has all the keys for the db

I also tried the algorithm found in this other question but I'm still failing for this particular example.

So, here's the example (starting from the canonical cover):

R<A,B,C,D,E,F,H>
C->DF; A->H; HF->C; B->E; AD->B; E->D; AB->F
Keys = {AC, AF, AD, AB, AE}

Since we already have the canonical cover, let's follow the second step:

<R1(C,D,F), {C->DF}>
<R2(A,H), {A->H}>
<R3(C,F,H), {HF->C; C->F}>
<R4(B,E), {B->E}>
<R5(A,B,D), {AD->B; B->D; A->D}>
<R6(D,E), {E->D}>
<R7(A,B,F), {AB->F; F->B}>

Before going on, here's the first question: he takes some of the projections from the schema, but there's one that really doesn't make any sense to me. I'm talking about A->D from R5.

The paths leading to D are the ones with E or C, so let's see if we can use them:

C is reached by HF, where H is reached by A and F by AB, meaning that A can't get to C without B
E is reached by B, where B is reached by AD, meaning again that A can't get to E without D

So the first question is: why does he take that A->D projection if isn't there a way for A alone to reach D? Am I missing something?

Please note that the same question applies to F->B in R7, because I don't think that F can reach B without A, that's why I think it should be AF->B

Coming back to the example, let's go on with the algorithm. As we can see there are no relations sharing attributes, so there's nothing to merge/eliminate at step 3.

It's now time for step 4, but his result is the following (he didn't write the FDs here):

R1'(A,B,D,E) - merged R4, R5, R6
R2'(A,B,C,F,H) - merged R2, R3, R7
R3'(C,D,F) - R1 was not merged and didn't change

The problem here is that we're presented with this final result without any explanation on how we got here. He just merged those relations and I really don't understand why.

Can you please try to explain how this works?

A. Canonical Cover

The example is almost a canonical cover, since in a canonical cover each dependency has only an attribute on the right part, so the dependency

C → DF

must be replaced by the two dependencies:

C → D
C → F

B. Second step of the 3NF Synthesis Algorithm

The step says to divide the functional dependencies in groups with the same left part, and create a relation scheme with all the attributes of each group, not to create a relation for each functional dependency. In this case the effect is the same as your solution.

So the second step produces the following schemas:

R1 <(A H), { A → H }>
R2 <(A B D), { A D → B }>
R3 <(A B F), { A B → F }>
R4 <(B E), { B → E }>
R5 <(C D F), { C → D, C → F }>
R6 <(D E), { E → D }>
R7 <(C F H), { F H → C }>

Note that in this the decomposition the dependencies shown are only the original dependencies used to produce the decomposition, since in the algorithm the projection of the dependencies is not calculated, and this is due to the fact that this computation requires an exponential algorithm.

If, however, we look at the projection of the dependencies, here you are correct, since both A → D and F → B are not present in the projection. Here is the above decomposition together with a projection of the original dependencies over the subschema:

R1 <(A H), { A → H }>
R2 <(A B D), { A D → B, B → D }>
R3 <(A B F), { A B → F, A F → B }>
R4 <(B E), { B → E }>
R5 <(C D F), { C → D, C → F }>
R6 <(D E), { E → D }>
R7 <(C F H), { F H → C, C → F }>

C. Third step of the 3NF Synthesis Algorithm

Correct

D. Fourth step of the 3NF Synthesis Algorithm

Here again the step is incorrect. The fourth step simply says: “if no schema exists with a key of the original relation, than add a new schema with this key”. See for instance Algorithm 16.6, R.Elmasri, S.B. Navathe, Foundamentals of Database Systems, 6th Edition, pag. 561 (or any other good book on databases):

  If none of the relation schemas in D contains a key of R, then create one more relation schema in D that contains attributes that form a key of R.

And since in this case there exist relations with a key, no other relation must be added, and the final decomposition is not modified:

R1 <(A H)>
R2 <(A B D)>
R3 <(A B F)>
R4 <(B E)>
R5 <(C D F)>
R6 <(D E)>
R7 <(C F H)>

So the solution given, with R1', R2' and R3' is not correct and it is not in Third Normal Form. It is easy to show this. Taking for instance the second relation,

R2' (A B C F H)

and projecting the original dependencies over it, we found the following dependencies:

{ A F → B
  A B → C
  A → H
  C → F
  F H → C }

with keys:

{ (A B) (A C) (A F) }

So we can see that the dependency A → H violates the Third Normal Form (A is not a (super)key and H is not a prime attribute).

As shown in the answer, if the relations must still be in 3NF, the final solution with three schemas is not the minimal 3NF normalization, since the relation R2' is not in 3NF. In other words, the solution is incorrect.

The classical synthesis algorithm produces the decomposition in 7 relations. I am not aware of any published algorithm that will minimize the number of relations obtained by that algorithm. You could try to combine relations and check if the combination is still in 3NF, but note that checking if a relation is in 3NF is an exponential task, since one should find all the keys, to exclude the possibility that a determinate be a prime attribute.


Execute Job With Stored Proc Signed with Certificate I'm trying to grant permissions to a user to run one job.  I created a certificate, assigned it to the stored proc, and granted the cert role membership to SQLAgentOperatorRole, but when executing the proc the user still gets the error that the job is not found.  I can execute the proc with no errors and the job is started.  What did I miss?

CREATE PROCEDURE spExecuteRefreshJob
AS
BEGIN

EXEC msdb.dbo.sp_start_job @job_name = 'Refresh Job'

END

CREATE CERTIFICATE ExecuteRestoreJobsCert
   ENCRYPTION BY PASSWORD = 'password'
      WITH SUBJECT = 'Cert for Jobs';
GO

ADD SIGNATURE TO dbo.spExecuteRefreshJob
   BY CERTIFICATE ExecuteRestoreJobsCert
    WITH PASSWORD = 'password';
GO

CREATE USER ExecuteRestoreJobsUser
   FROM CERTIFICATE ExecuteRestoreJobsCert;
GO

use msdb
EXECUTE sp_addrolemember
@rolename = 'SQLAgentOperatorRole',
@membername = 'ExecuteRestoreJobsUser'

I should have kept researching just a little bit longer.  I found this article and discovered I needed to counter-sign a few more stored procedures within msdb.  Once I did that my user was able to execute the proc, which kicked off the job.

ADD COUNTER SIGNATURE TO sp_start_job BY CERTIFICATE ExecuteRestoreJobsCert
    WITH PASSWORD = 'password'
ADD COUNTER SIGNATURE TO sp_verify_job_identifiers BY CERTIFICATE ExecuteRestoreJobsCert
    WITH PASSWORD = 'password'
ADD COUNTER SIGNATURE TO sp_sqlagent_notify BY CERTIFICATE ExecuteRestoreJobsCert
    WITH PASSWORD = 'password'
go


Need help in converting this cursor to a set based operation I have a stored procedure which runs as part of a report database refresh nightly which takes about 2 hours complete. The object B_RPT_RC_AUDIT_ASSIGNED_TO_TEMP_T2 has 8 million rows. Can you suggest how can I convert this cursor to a set based approach for better performance? Can a CTE be used?

ALTER PROCEDURE [dbo].[B_RPT_RC_AUDIT_ASSIGNED_TO_TEMP2_SP] AS
SET NOCOUNT ON
DECLARE @SEQ SMALLINT,
@CASEID DECIMAL,
@ASSIGNED  NCHAR(30),
@AUDITSTAMP DATETIME,
@AUDITOPRID NCHAR(60),
@STATUS NCHAR(10),
@LASTCASEID DECIMAL,
@PROVGRPID NCHAR(20)
SET @SEQ = 1
SET @LASTCASEID = @CASEID

DROP TABLE B_RPT_RC_AUDIT_ASSIGNED_TO_TEMP

CREATE TABLE B_RPT_RC_AUDIT_ASSIGNED_TO_TEMP (
  SEQ_NUM SMALLINT,
  CASE_ID DECIMAL,
  ASSIGNED_TO NCHAR(30),
  AUDIT_STAMP DATETIME,
  AUDIT_OPRID NCHAR(60),
  RC_STATUS NCHAR(10),
  PROVIDER_GRP_ID NCHAR(20) )
DECLARE AUDIT CURSOR FOR
   SELECT CASE_ID, ASSIGNED_TO, AUDIT_STAMP, AUDIT_OPRID, RC_STATUS, PROVIDER_GRP_ID
   FROM B_RPT_RC_AUDIT_ASSIGNED_TO_TEMP_T2

-- Open the cursor
OPEN AUDIT
FETCH NEXT
   FROM AUDIT
    INTO @CASEID, @ASSIGNED, @AUDITSTAMP, @AUDITOPRID, @STATUS, @PROVGRPID
WHILE @@FETCH_STATUS = 0

begin
 INSERT INTO B_RPT_RC_AUDIT_ASSIGNED_TO_TEMP
 SELECT @SEQ, @CASEID, @ASSIGNED, @AUDITSTAMP, @AUDITOPRID, @STATUS, @PROVGRPID

SET @LASTCASEID = @CASEID
FETCH NEXT
   FROM AUDIT
    INTO @CASEID, @ASSIGNED, @AUDITSTAMP, @AUDITOPRID, @STATUS, @PROVGRPID

SET @SEQ = CASE WHEN @CASEID <> @LASTCASEID THEN 1 WHEN @CASEID = @LASTCASEID THEN @SEQ + 1 END
end
-- Close and deallocate the cursor
CLOSE AUDIT
DEALLOCATE AUDIT

Why do you need to DROP and RECREATE the table each time?  Since you're doing a DROP-CREATE, it must already exist. So, outside of the SP, recreate it as follows:

CREATE TABLE B_RPT_RC_AUDIT_ASSIGNED_TO_TEMP (
  SEQ_NUM SMALLINT,
  CASE_ID DECIMAL,
  ASSIGNED_TO NCHAR(30),
  AUDIT_STAMP DATETIME,
  AUDIT_OPRID NCHAR(60),
  RC_STATUS NCHAR(10),
  PROVIDER_GRP_ID NCHAR(20),
  PRIMARY KEY(CASE_ID, SEQ_NUM)
); -- don't leave an 8 million record table in a heap!

Since all you need is merely to number the records within a CASE from 1 to N, you can use ROW_NUMBER().

ALTER PROCEDURE [dbo].[B_RPT_RC_AUDIT_ASSIGNED_TO_TEMP2_SP]
AS
  SET NOCOUNT ON;
  TRUNCATE TABLE B_RPT_RC_AUDIT_ASSIGNED_TO_TEMP;

  INSERT INTO B_RPT_RC_AUDIT_ASSIGNED_TO_TEMP
  SELECT ROW_NUMBER() OVER (PARTITION BY CASE_ID ORDER BY AUDIT_STAMP),
         CASE_ID, ASSIGNED_TO, AUDIT_STAMP, AUDIT_OPRID, RC_STATUS, PROVIDER_GRP_ID
    FROM B_RPT_RC_AUDIT_ASSIGNED_TO_TEMP_T2;
GO


Do I have to format all my drives into xfs format to better use mongodb? From the mongo official site, xfs is highly recommended,

  Linux/Unix: format your drives into XFS or EXT4. If possible, use XFS as it generally performs better with MongoDB.
  With the WiredTiger storage engine, use of XFS is strongly recommended to avoid performance issues found when using EXT4 with WiredTiger.
  If using RAID, you may need to configure XFS with your RAID geometry.

I have a ext4 format drive mounted on the root path(/) and plan to add another xfs drive to hold all the mongo data. Do I also need to format my root drive into xfs format or not?

To answer the question directly, I would say not to change your root partition (which, I'm assuming, contains all of your OS files - i.e. your kernel) file system to XFS. By all means, use XFS on your new disk system for your Mongodb data.

I'm not sure of your exact disk setup, but if you have multiple disks, I would recommend OS on one (set), swap/tmp on another, database software on another, and actual data on yet others - that way you don't have OS calls blocking db stuff and vice versa. This also helps with restoring in the (unlikely) event of a disk (set) failure.

From here, you are correct in saying that

  When running MongoDB in production on Linux, you should use Linux
  kernel version 2.6.36 or later, with either the XFS or EXT4
  filesystem. If possible, use XFS as it generally performs better with
  MongoDB.

  With the WiredTiger storage engine, use of XFS is strongly recommended
  to avoid performance issues that may occur when using EXT4 with
  WiredTiger.

From here (generally a good site), you can see that xfs is good for large files:

  XFS was developed by Silicon Graphics in 1994 for the SGI IRX
  operating system, and was ported to Linux in 2001. It’s similar to
  Ext4 in some ways, as it also uses delayed allocation to help with
  file fragmentation and does not allow for mounted snapshots. It can be
  enlarged, but not shrunk, on the fly. XFS has good performance when
  dealing with large files, but has worse performance than other file
  systems when dealing with many small files. It may be useful for
  certain types of servers that primarily need to deal with large files.

Now, I'm not sure about Mongodb, but as a database system, it probably has large files :-) - however, the kernel itself is primarily developed on ext4 and it is the default for a reason.

Remember that a recommendation is just that: a recommendation for an average set of databases with an average set of requirements! You should, if possible, benchmark with your own data on your own machines (prod and test!).

Another thing I've noticed about database file system benchmarks is that there's normally not a huge difference between them for a given task - obviously some are better than others in a given set of circumstances, but it seems to me that if one team makes a "breakthrough", others aren't far behind!

So, I would keep my kernel on ext4 and use xfs for my mongodb storage (again, with the caveat about your own benchmarking, to the extent that this is possible).

To answer @NeilLunn 's point: well, yes (what I wrote is essentially an opinion), but I like to think it is a reasonably educated one! Basically, there are lies, damned lies, and statistics! And then, there are database benchmarks :-)

Anything anyone writes on this topic will contain an element of opinion - the thing which separates the wheat from the chaff is those which take information from reputable sites, and those which urge the user to benchmark for their own apps on their own machines with their own realistic data sets and usage patterns.

I believe that I have done that - I'm not going to gainsay Mongodb about whether their systems work work better with file system x, y or z, but I also know that gainsaying Linus Torvalds on which is the best file system for the kernel is also fraught with peril!


Authentication failed when deploy an Analysis Services project I am getting the following error on Analysis Services project on Visual Studio 2012 and SQL Server 2014:

  The project could not be deployed to the 'MyServerName\InstanceName'
  server because of the following connectivity problems :
  Authentication failed.  To verify or update the name of the target
  server, right-click on the project in Solution Explorer, select
  Project Properties, click on the Deployment tab, and then enter the
  name of the server.

On Project-> properties -> Deployment -> Server it has the ServerName\InstanceName properly, using the same ServerName\InstanceName on SSMS I can connect without problem.
On Data Source I tested the connection and it passed successfully
Changed the Impersonator user to my user which is Sysadmin

I google it, but all references I found isn't Autentication failure.

Can anyone help me?

EDIT
If I execute

telnet ipServer 2382

successfully connects.

I tried with VS2019 and had the same error.

Tried using service account for impersonator, and the user services is administrator on server and sysadmin but also get the same error.

Also I checked this guide and no one of those fixes helps me.

I was trying to deploy from my computer to a server on another domain

The connection to SSAS to deploy always uses Windows Authentication, and always uses the account for Visual Studio.  To deploy to an SSAS server in another domain (or no domain), launch SSAS with 'runas /netonly' providing the target domain credentials.

This will launch Visual Studio without impersonation, but using the specified credentials for all network access.


From a set of values, how do I find the values not stored in a table's column? I have a table which will potentially store hundreds of thousands of integers

desc id_key_table;

+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| id_key         | int(16)      | NO   | PRI | NULL    |       |
+----------------+--------------+------+-----+---------+-------+

From a program, I have a large set of integers.  I'd like to see which of these integers are NOT in the above id_key column.

So far I've come up with the following approaches:

1) Iterate through each integer and perform a:

select count(*) count from id_key_table where id_key = :id_key

When count is 0 the id_key is missing from the table.

This seems like a horrible, horrible way to do it.

2) Create a temporary table, insert each of the values into the temporary table, and perform a JOIN on the two tables.

create temporary table id_key_table_temp (id_key int(16) primary key );

insert into id_key_table_temp values (1),(2),(3),...,(500),(501);

select temp.id_key
from id_key_table_temp temp left join id_key_table as main
         on temp.id_key = main.id_key
where main.killID is null;

drop table id_key_table_temp;

This seems like the best approach, however, I'm sure there is a far better approach I haven't thought of yet. I'd prefer to not have to create a temporary table and use one query to determine which integers are missing.

Is there a proper query for this type of search?

(MySQL)

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


Identify which parameters were passed to a procedure (Oracle) Procedures in PL/SQL can have default values for parameters, but the caller can pass values that are identical to the default values.  Is there a way from within the PL/SQL to determine whether a parameter is passed?  As implied this cannot be done by comparing the value with the default value.

So the way I do this is I don't use default values. Or rather, I set them to NULL as default. Then within my procedure body I check to see if it's equal to NULL and if so set it to a sane default. At that point you could mangle a flag or two in the same space to see what's being set or not set.

However, there's the edge case of if they intended to pass in NULL for some reason. However, most devs don't deal well with tripletcase, so I don't usually worry about that (additionally, if I'm passing in a value, I need to pass a value, not null, so null for me means never-was-defined)


best way to run sql server management studio on linux I'm using ubuntu 18.04.2 on my desktop and running sql server 2017 from docker.

I want to use sql server management studio to manage the server.

SO far now, I have a windows 7 enterprise with with ssms 17.9.1 and it works ok

It's really annoying having to boot a whole windows enterprise just for a single app, so I wanted to know if there's some better way to run it. A lighter or better integrated alternative.

that version of ssms doesn't seem to work with wine.

You can't run Sql Server Management Studio on Linux. But there is an alternative that is provided by Microsoft called Azure Data Studio. Not all features are available in Azure Data Studio that are available in Sql Server Management Studio - the main difference is that Azure Data Studio is missing a lot of the wizards that are available in SSMS.

Use Azure Data Studio if you:

Need to run on macOS or Linux
Are connecting to a SQL Server 2019 big data cluster
Spend most of your time editing or executing queries
Need the ability to quickly chart and visualize result sets
Can execute most administrative tasks via the integrated terminal using sqlcmd or Powershell
Have minimal need for wizard experiences
Do not need to do deep administrative configuration

Use SQL Server Management Studio if you:

Spend most of your time on database administration tasks
Are doing deep administrative configuration
Are doing security management, including user management, vulnerability assessment, and configuration of security features
Make use of the Reports for SQL Server Query Store
Need to make use of performance tuning advisors and dashboards
Are doing import/export of DACPACs
Need access to Registered Servers and want to control SQL Server services on Windows

The snippet above is taken from Sql Server Blog and provides a comparison of features.
If you are happy to pay for a tool, then take a look at Aqua Data Studio and SQL Maestro which have quite a lot of features and a wide range of support for other database platforms.


Significantly Slower DB Dumps on AWS RDS After Turning on Encryption? Turning on encryption on an AWS RDS MySQL instance results in significantly slower database dumps (using mysqldump).

mysqldump -u test -p -h xxx.aws.com > dump.sql

Is there a reason for significantly slower DBdumps on AWS RDS after turning on encryption?

mysqldump reads all of your data.  If the encryption is at the disk, then it takes time to decrypt the data before mysqldump can access it.  Hence, the total action is slower.

Do you have any metrics on how much slower (percentage)?

Also, since RDS does key management, there may be a slight, one-time, delay to get the key.


garbd with proxysql and mysql replication Can be configured garbd with proxysql + MySQL Replication?, I installed proxysql with 1 master and two slaves using MySQL replication but I was disputed about to connect garb with proxysql how I was done with galera cluster

garbd is used to a fake Galera cluster member.

It doesn't fake a MySQL server.


Is is possible to handle a UserAbort or ClientTimeOut within a Stored Procedure? An example:

create proc [dbo].[usp_SlowProc]
as
begin try
  set xact_abort on;  -- Ensures rollback on some errors that bypass the try/catch mechanism
  raiserror('Waiting...', 0, 1) with nowait
  waitfor delay '00:00:10'
  raiserror('Completed successfully.', 0, 1) with nowait
end try
begin catch
    raiserror('Caught It.', 0, 1) with nowait
    -- Rollback, log the error.
end catch

Then run it within SSMS/Toad:

exec dbo.usp_SlowProc;

and then cancel it (using SSMS/Toad) before it completes.  The catch block does not run.

Assuming from this link that a user abort is the same as as what .NET does on a client timeout (the result is the same).

SQL Server Transaction Timeout

AFAIK there is no way in TSQL to deal with this other than the way you currently are with XACT_ABORT. There are no structures that TSQL knows (or even cares about) as everything this deals with lives outside of TSQL.

What is actually happening is called an "Attention" event which could range from a connection closing to an actual cancel (for example the sqlcommand.cancel() method). Since this lives outside of the actual query, it just tells SQL Server that something happened and that it needs to clean up some stuff. The actual query has no idea what is going on.

Try/Catch is for query execution errors such as diving by 0, but not for any other type of errors. For example, compile/parse errors are not caught as the try/catch had never run. Since attention signals are not part of query execution (again, it's outside of that scope) it doesn't know anything about it and thus is not called as no execution exception has happened (according to the query itself).


Is there a more efficient way to set up this table I'm looking to add a new feature in our production site that will show users 25 items they most recently viewed.  I already record every view into various tables based on the item type, but querying on the union of these tables can't be efficient, because those tables have (in some cases) millions of rows, and for the new table, I'm only concerned with the user's 25 most recent views.

My initial thought was to make a new table and write additional code to add records to (and delete from) this table with the following conditions:

1) each combination of user_id and item_id should be unique; ie for a given user_id, any item should appear no more than once
2) to keep the table small / fast, no more than 25 items should be stored for a given user.  Once a user_id has 25 items the oldest view would be removed whenever a new view is added for that user_id.

so in order to do this, I need to query if the given combination exists, and then if the given user_id has 25 rows, the user's oldest row would need to be deleted.

I thought there may be a better way to do this and I investigated MySQL 'views' but from what I read it sounds like creating a view based on the existing tables would not be efficient since it would use all the rows but no indexing.

Is there a better way to do this (maybe triggers?  or some way within the mysql schema?) or is my best bet "brute force"; to create additional code to manage the new table, on top of the code I already have to record every view?

This is the basic schema for the new table:

| Field                 | Type
+-----------------------+-----------------
| customer_id           | int(11)
| item_id               | int(11)
| view_date             | datetime

thank you in advance for any advice or suggestions.

I think your approach is reasanoble.
Because it would show online data (no need for background processing) and would only use resources from active transactions (customer not seeing anything will not need to have their data reprocessed).

I would do it using triggers. You would not have to change your existing code and you could disable/enable this new functionality more easily.

An idea:
Try to use a LRU algorithm.
Try to see the "importance" of an item. For example, adding an extra column for counting the amount of times an item as viewed.
If a user visited an item many times you could not stop showing it on this Top25 List simply because some other 24 random items were searched.


Stuck with Oracle 9i This is my first time installing Oracle 9i.

I want to create a simple database.

When I go to "Oracle Enterprise Manager Console" and add a database to the tree, I cannot access the database as I don't have a valid username/password.

After a lot of searching, I found that I need to create a new database repository. While I was creating the new repository, it asked for USERNAME, Password, and Service.

username : SYS/sys
Password : change_on_install
service  : <host>:<port>:<SID>
         : SYSDBA

This is not working.  I need proper guidance on how to get out of this.

First, are you really sure that you want to be starting out with a desupported version of the database that is at least 4 major releases out of date?  It would seem much more useful to download a more recent version of Oracle from the Oracle Technology Network (OTN) and to start with that.  You can get the express edition of Oracle 11.2 completely free and the enterprise edition is free if you just want to learn the product.

Second, when you installed Oracle, it should have prompted you to create a database as part of the installation process.  If you chose not to create a database during the installation process, you should run the Database Creation Assistant (DBCA) to create a database now.  Note that what lots of products refer to as a "database" is more like a schema in Oracle.  In general, you would only have one Oracle database on your machine though that database may have multiple schemas.

What operating system are you using?


Database design for tracking referral dollars and referred dollars Dropbox has a great referral incentive program that gives a storage bonus based on whether a person is referred to Dropbox or refers others to Dropbox.  What data base design is good for managing information of this nature but with a slight twist.

Say that getting referred to a service gives you a fixed storage bonus of 100 MB.
Further, say that a referrer gets a storage bonus equivalent to the purchased amount by the referral.

So if Tom refers Jerry, Jerry gets 100 MB. If Jerry buys another 300 MB, Tom gets 300 MB of storage free of charge for as long as Jerry is paying for the incremental 300 MB.  Tom might also refer other people and get more free storage according to the same terms and Jerry can do the same.  Given that the people that are paying at any given time is changing, you need to track what each person is entitled to based on their referrals and the amount their referrals are purchasing at any given point in time.

Any suggestions?

It's a Multi-Level Marketing system!  Jeff Moden has written a a pair of articles here and here on efficient implementation of Hierarchical Reporting against a SQL Server database. There are a number of ways to store the hierarchical information but the two main ones are Adjacency List (each child has a parent foreign key) and Nested Sets (each parent stores details of its child hierarchy). Adjacency List is more intuitive and faster to update, while Nested Sets provides faster reporting.

Jeff has explained this topic far better than I can, and developed efficient SQL Server algorithms for converting an large Adjacency List tree into a Nested Set representation,

CREATE PROCEDURE dbo.RebuildNestedSets AS
/****************************************************************************
 Purpose:
 Rebuilds a "Hierarchy" table that contains the original Adjacency List,
 the Nested Sets version of the same hierarchy, and several other useful
 columns of data some of which need not be included in the final table.

 Usage:
 EXEC dbo.RebuildNestedSets

 Progammer's Notes:
 1. As currently written, the code reads from a table called dbo.Employee.
 2. The Employee table must contain well indexed EmployeeID (child) and
    ManagerID (parent) columns.
 3. The Employee table must be a "well formed" Adjacency List. That is, the
    EmployeeID column must be unique and there must be a foreign key on the
    ManagerID column that points to the EmployeeID column. The table must not
    contain any "cycles" (an EmployeeID in its own upline). The Root Node
    must have a NULL for ManagerID.
 4. The final table, named dbo.Hierarchy, will be created in the same
    database as where this stored procedure is present.  IT DOES DROP THE
    TABLE CALLED DBO.HIERARCHY SO BE CAREFUL THAT IT DOESN'T DROP A TABLE
    NEAR AND DEAR TO YOUR HEART.
 5. This code currently has no ROLLBACK capabilities so make sure that you
    have met all of the requirements (and, perhaps, more) cited in #3 above.

 Dependencies:
 1. This stored procedure requires that the following special purpose HTally
    table be present in the same database from which it runs.

--===== Create the HTally table to be used for splitting SortPath
 SELECT TOP 1000 --(4 * 1000 = VARBINARY(4000) in length)
        N = ISNULL(CAST(
                (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1)*4+1
            AS INT),0)
   INTO dbo.HTally
   FROM master.sys.all_columns ac1
  CROSS JOIN master.sys.all_columns ac2
;
--===== Add the quintessential PK for performance.
  ALTER TABLE dbo.HTally
    ADD CONSTRAINT PK_HTally
        PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
;

 Revision History:
 Rev 00 - Circa 2009  - Jeff Moden
        - Initial concept and creation.
 Rev 01 - PASS 2010   - Jeff Moden
        - Rewritten for presentation at PASS 2010.
 Rev 02 - 06 Oct 2012 - Jeff Moden
        - Code redacted to include a more efficient, higher performmance
          method of splitting the SortPath using a custom HTally Table.
****************************************************************************/
--===========================================================================
--      Presets
--===========================================================================
--===== Suppress the auto-display of rowcounts to prevent from returning
     -- false errors if called from a GUI or other application.

SET NOCOUNT ON;

--===== Start a duration timer
DECLARE @StartTime DATETIME,
        @Duration  CHAR(12);
 SELECT @StartTime = GETDATE();

--===========================================================================
--      1.  Read ALL the nodes in a given level as indicated by the parent/
--          child relationship in the Adjacency List.
--      2.  As we read the nodes in a given level, mark each node with the
--          current level number.
--      3.  As we read the nodes in a given level, convert the EmployeeID to
--          a Binary(4) and concatenate it with the parents in the previous
--          level's binary string of EmployeeID's.  This will build the
--          SortPath.
--      4.  Number the rows according to the Sort Path.  This will number the
--          rows in the same order that the push-stack method would number
--          them.
--===========================================================================
--===== Conditionally drop the final table to make reruns easier in SSMS.
     IF OBJECT_ID('FK_Hierarchy_Hierarchy') IS NOT NULL
        ALTER TABLE dbo.Hierarchy
         DROP CONSTRAINT FK_Hierarchy_Hierarchy;

     IF OBJECT_ID('dbo.Hierarchy','U') IS NOT NULL
         DROP TABLE dbo.Hierarchy;

RAISERROR('Building the initial table and SortPath...',0,1) WITH NOWAIT;
--===== Build the new table on-the-fly including some place holders
   WITH cteBuildPath AS
( --=== This is the "anchor" part of the recursive CTE.
     -- The only thing it does is load the Root Node.
 SELECT anchor.EmployeeID,
        anchor.ManagerID,
        HLevel   = 1,
        SortPath =  CAST(
                        CAST(anchor.EmployeeID AS BINARY(4))
                    AS VARBINARY(4000)) --Up to 1000 levels deep.
   FROM dbo.Employee AS anchor
  WHERE ManagerID IS NULL --Only the Root Node has a NULL ManagerID
  UNION ALL
 --==== This is the "recursive" part of the CTE that adds 1 for each level
     -- and concatenates each level of EmployeeID's to the SortPath column.
 SELECT recur.EmployeeID,
        recur.ManagerID,
        HLevel   =  cte.HLevel + 1,
        SortPath =  CAST( --This does the concatenation to build SortPath
                        cte.SortPath + CAST(Recur.EmployeeID AS BINARY(4))
                    AS VARBINARY(4000))
   FROM dbo.Employee      AS recur WITH (TABLOCK)
  INNER JOIN cteBuildPath AS cte
          ON cte.EmployeeID = recur.ManagerID
) --=== This final INSERT/SELECT creates the Node # in the same order as a
     -- push-stack would. It also creates the final table with some
     -- "reserved" columns on the fly. We'll leave the SortPath column in
     -- place because we're still going to need it later.
     -- The ISNULLs make NOT NULL columns
 SELECT EmployeeID = ISNULL(sorted.EmployeeID,0),
        sorted.ManagerID,
        HLevel     = ISNULL(sorted.HLevel,0),
        LeftBower  = ISNULL(CAST(0 AS INT),0), --Place holder
        RightBower = ISNULL(CAST(0 AS INT),0), --Place holder
        NodeNumber = ROW_NUMBER() OVER (ORDER BY sorted.SortPath),
        NodeCount  = ISNULL(CAST(0 AS INT),0), --Place holder
        SortPath   = ISNULL(sorted.SortPath,sorted.SortPath)
   INTO dbo.Hierarchy
   FROM cteBuildPath AS sorted
 OPTION (MAXRECURSION 100) --Change this IF necessary
;
RAISERROR('There are %u rows in dbo.Hierarchy',0,1,@@ROWCOUNT) WITH NOWAIT;

--===== Display the cumulative duration
 SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);
RAISERROR('Cumulative Duration = %s',0,1,@Duration) WITH NOWAIT;

--===========================================================================
--      Using the information created in the table above, create the
--      NodeCount column and the LeftBower and RightBower columns to create
--      the Nested Sets hierarchical structure.
--===========================================================================
RAISERROR('Building the Nested Sets...',0,1) WITH NOWAIT;

--===== Declare a working variable to hold the result of the calculation
     -- of the LeftBower so that it may be easily used to create the
     -- RightBower in a single scan of the final table.
DECLARE @LeftBower INT
;
--===== Create the Nested Sets from the information available in the table
     -- and in the following CTE. This uses the proprietary form of UPDATE
     -- available in SQL Serrver for extra performance.
   WITH cteCountDownlines AS
( --=== Count each occurance of EmployeeID in the sort path
 SELECT EmployeeID = CAST(SUBSTRING(h.SortPath,t.N,4) AS INT),
        NodeCount  = COUNT(*) --Includes current node
   FROM dbo.Hierarchy h,
        dbo.HTally t
  WHERE t.N BETWEEN 1 AND DATALENGTH(SortPath)
  GROUP BY SUBSTRING(h.SortPath,t.N,4)
) --=== Update the NodeCount and calculate both Bowers
 UPDATE h
    SET @LeftBower   = LeftBower = 2 * NodeNumber - HLevel,
        h.NodeCount  = downline.NodeCount,
        h.RightBower = (downline.NodeCount - 1) * 2 + @LeftBower + 1
   FROM dbo.Hierarchy h
   JOIN cteCountDownlines downline
     ON h.EmployeeID = downline.EmployeeID
;
RAISERROR('%u rows have been updated to Nested Sets',0,1,@@ROWCOUNT)
WITH NOWAIT;

RAISERROR('If the rowcounts don''t match, there may be orphans.'
,0,1,@@ROWCOUNT)WITH NOWAIT;

--===== Display the cumulative duration
 SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);
RAISERROR('Cumulative Duration = %s',0,1,@Duration) WITH NOWAIT;

--===========================================================================
--      Prepare the table for high performance reads by adding indexes.
--===========================================================================
RAISERROR('Building the indexes...',0,1) WITH NOWAIT;

--===== Direct support for the Nested Sets
  ALTER TABLE dbo.Hierarchy
    ADD CONSTRAINT PK_Hierarchy
        PRIMARY KEY CLUSTERED (LeftBower, RightBower) WITH FILLFACTOR = 100
;
 CREATE UNIQUE INDEX AK_Hierarchy
     ON dbo.Hierarchy (EmployeeID) WITH FILLFACTOR = 100
;
  ALTER TABLE dbo.Hierarchy
    ADD CONSTRAINT FK_Hierarchy_Hierarchy FOREIGN KEY
        (ManagerID) REFERENCES dbo.Hierarchy (EmployeeID)
     ON UPDATE NO ACTION
     ON DELETE NO ACTION
;
--===== Display the cumulative duration
 SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);
RAISERROR('Cumulative Duration = %s',0,1,@Duration) WITH NOWAIT;

--===========================================================================
--      Exit
--===========================================================================
RAISERROR('===============================================',0,1) WITH NOWAIT;
RAISERROR('RUN COMPLETE',0,1) WITH NOWAIT;
RAISERROR('===============================================',0,1) WITH NOWAIT;

and then to report hierarchical subtotals (as you require here) from the Nested Set representation:

--===== Start a "Timer" to see how long this all takes.
DECLARE @StartTime DATETIME;
 SELECT @StartTime = GETDATE();

--===========================================================================
--      1.  Read ALL the nodes in a given level as indicated by the parent/
--          child relationship in the Adjacency List.
--      2.  As we read the nodes in a given level, mark each node with the
--          current level number.
--      3.  As we read the nodes in a given level, convert the EmployeeID to
--          a Binary(4) and concatenate it with the parents in the previous
--          level’s binary string of EmployeeID’s.  This will build the
--          SortPath.
--===========================================================================
--===== Conditionally drop the work table to make reruns easier in SSMS.
     IF OBJECT_ID('dbo.Hierarchy','U') IS NOT NULL
         DROP TABLE dbo.Hierarchy;

--===== Build the new table on-the-fly including some place holders
   WITH cteBuildPath AS
( --=== This is the "anchor" part of the recursive CTE.
     -- The only thing it does is load the Root Node.
 SELECT anchor.EmployeeID,
        anchor.ManagerID,
        HLevel   = 1,
        SortPath =  CAST(
                        CAST(anchor.EmployeeID AS BINARY(4))
                    AS VARBINARY(4000)) --Up to 1000 levels deep.
   FROM dbo.Employee AS anchor
  WHERE ManagerID IS NULL --Only the Root Node has a NULL ManagerID
  UNION ALL
 --==== This is the "recursive" part of the CTE that adds 1 for each level
     -- and concatenates each level of EmployeeID's to the SortPath column.
 SELECT recur.EmployeeID,
        recur.ManagerID,
        HLevel   =  cte.HLevel + 1,
        SortPath =  CAST( --This does the concatenation to build SortPath
                        cte.SortPath + CAST(Recur.EmployeeID AS BINARY(4))
                    AS VARBINARY(4000))
   FROM dbo.Employee      AS recur WITH (TABLOCK)
  INNER JOIN cteBuildPath AS cte
          ON cte.EmployeeID = recur.ManagerID
) --=== This final INSERT/SELECT creates an iterim working table to hold the
     -- original Adjacency List, the hierarchal level of each node, and the
     -- SortPath which is the binary representation of each node's upline.
     -- The ISNULLs make NOT NULL columns
 SELECT EmployeeID = ISNULL(sorted.EmployeeID,0),
        sorted.ManagerID,
        Sales      = ISNULL(CAST(0 AS BIGINT),0), --Place Holder
        HLevel     = ISNULL(sorted.HLevel,0),
        SortPath   = ISNULL(sorted.SortPath,sorted.SortPath)
   INTO dbo.Hierarchy
   FROM cteBuildPath AS sorted
 OPTION (MAXRECURSION 100) --Change this IF necessary
;
--===== You'll be tempted to add the following index because it seems so
     -- logical a thing to do for performance, but DON'T do it! It will
     -- actually slow the rest of the code down by a factor of 2!!!!
 --ALTER TABLE dbo.Hierarchy
 --  ADD CONSTRAINT PK_Hierarchy PRIMARY KEY CLUSTERED (EmployeeID)
--;
--===== Populate the Hierarchy table with current Sales data.
 UPDATE h
    SET h.Sales = s.Sales
   FROM dbo.Hierarchy h
  INNER JOIN dbo.CurrentMonthlySales s
     ON h.EmployeeID = s.EmployeeID
;
--===== Conditionally drop the final table to make reruns easier in SSMS.
     IF OBJECT_ID('dbo.PreAggregatedHierarchy,'U') IS NOT NULL
        DROP TABLE dbo.PreAggregatedHierarchy
;
--===== Now, build "Everything" into the PreAggregatedHierarchy table.
WITH
cteSplit AS
(--==== Splits the path into elements (including Sales and HLevel)
     -- so that we can aggregate them by EmployeeID and HLevel.
     -- Can't aggregate here without including the SortPath so we don't.
 SELECT EmployeeID = CAST(SUBSTRING(h.SortPath,t.N,4) AS INT),
        h.HLevel, h.Sales
   FROM dbo.HTally         AS t
  CROSS JOIN dbo.Hierarchy AS h
  WHERE t.N BETWEEN 1 AND DATALENGTH(SortPath)
),
cteAggregate AS
(--==== Creates the aggregates and introduces the "Relative Level" column.
     -- NodeCount = Count of nodes in downline for each EmployeeID by Level
     -- Sales = Total Sales in downline for each EmployeeID by Level.
 SELECT EmployeeID,
        HLevel,
        RLevel    = ROW_NUMBER() OVER (PARTITION BY EmployeeID
                                           ORDER BY EmployeeID, HLevel),
        NodeCount = COUNT(*),
        Sales     = SUM(CAST(Sales AS MONEY))
   FROM cteSplit
  GROUP BY EmployeeID, HLevel
)
--===== Adds a "Rollup" to create all the subtotals that we need.
     -- We couldn't do this in the previous step because we didn't know what
     -- the "Relative Level" was for each row, yet.
     -- The HAVING eliminates unnecessary subtotals that are created.
 SELECT EmployeeID = ISNULL(a.EmployeeID,0), --Convert NULL total lines to 0
        HLevel     = MIN(a.HLevel), --Just so we don't have to GROUP BY
        RLevel     = ISNULL(CAST(a.RLevel AS TINYINT),0),
        NodeCount  = SUM(a.NodeCount), --Just so we don't have to GROUP BY
        Sales      = SUM(a.Sales) --Just so we don't have to GROUP BY
   INTO dbo.PreAggregatedHierarchy
   FROM cteAggregate a
  GROUP BY EmployeeID, RLevel WITH ROLLUP
 HAVING EmployeeID > 0 --Eliminates the NULL total lines for cleaner output
;
--===== Add the Clustered Index as a Primary Key
  ALTER TABLE dbo.PreAggregatedHierarchy
    ADD CONSTRAINT PK_PreAggregatedHierarchy
        PRIMARY KEY CLUSTERED (EmployeeID, RLevel) WITH FILLFACTOR = 100
;
--===== Display how long it all took
  PRINT 'Duration: ' + CONVERT(CHAR(12),GETDATE()-@StartTime,114) + ' (hh:mi:ss:mmm)';


SQL Server 2008 Mirroring How would one start the secondary mirror instance if the principal SQL server instance went off line?

I'm assuming you don't have automatic failover and no witness, otherwise the mirror would have been promoted automatically.

In this case you need to switch the roles manually, with forced service:

ALTER DATABASE database_name SET PARTNER FORCE_SERVICE_DATA_LOSS

See Role Switching During a Database Mirroring Session and ALTER DATABASE Database Mirroring for more detail.


2016 Read Only Secondaries With Different Indexes The other day, someone told me that one feature in SQL Server 2016 involving read-only secondaries for reporting was that the read-only secondary could have different indexes than the primary.  I've searched for this everywhere and haven't found this to be the case.  In fact, Microsoft seems to indicate the opposite here (in this manner, indexing is similar to 2012).

Just curious if I'm reading this correctly; read-only secondaries with different indexing would provide an option for reporting, but I don't see them being compatible with AG groups in any version of SQL Server.

I can say with a very high level of confidence that there is no plan to support alternate indexes on AG secondaries of any kind in SQL Server 2016.

Perhaps your friend is confusing indexes and statistics - you can create alternate statistics on read-only secondaries (details here).


migration from mysql 4.1.0 to mysql5.6.2 I have been trying to backup data with structure from my old legacy server to my local server for modifications. PhpMyAdmin timed out at the time of export, MySQL Workbench has performed connections between source and target machines as Ok and upto bulk data transfer everything went well, after that point I am getting errors as "Connection using old(pre-4.1.1)authentication protocol refused(client option secure_auth enabled)although I checked disable_auth in Advanced tab.
Error: Error getting row count from source tables.

Any suggestions relating to this would be useful.

Please read this but report:

http://bugs.mysql.com/bug.php?id=69027

The workaround ("solution", really) to this is to change the password for the affected user to a post-4.1 hash. This is really a recommended best practice, regardless - password hashing and authorization process prior to 4.1 has notable security limitations (discussed in documentation at http://dev.mysql.com/doc/refman/5.0/en/password-hashing.html).
Restoring a 4.1 version of the mysql schema on to a 5.6 server is a bad idea at any rate, because 5.6 has additional columns in some tables and some completely new tables, which may or may not be missing now depending on how you configured mysqldump when you created the dump file. You may have caused other problems that you may not see immediately.

Also, specify skip-secure-auth option in [mysqld] section of my.cnf/my.ini file of your MySQL server and restart it.


What is the risk of doing a rebuild to my indexes? What is the risk of doing a rebuild to my indexes in SQL Server 2012?
I have to do a REBUILD, but I don't know what are the risks.

The server is running and a lot of information is going in and out.

Without knowing details about exactly what type of queries are accessing that data and the indexes you are dealing with a precise answer is not possible.

However, in general, an index rebuild drops and then re-adds that index and locking needs to occur for that to happen. If you have queries that are attempting to access this table while the rebuild is going on, they will likely need to wait for it to finish before they can complete.

I found a very good rundown of the locking in a blog post by Kendra Little; and under the "Online" section of the ALTER INDEX MSDN page you can see Microsoft's description of the timing of these locks.

If you are using Enterprise Edition, you can run the index rebuild (or even create) in online mode (with hint Online = on) and it will not cause blocking.


Localhost vs LAN Database Performance Normally, what some of us do is to just host the database and web-server inside 1 machine. Meanwhile we also may focus a machine with high RAM just for the remote database on LAN and another machine for web-server.

How does it affect performance for I/O speed to high load and busy server and/or anything that might need to be put under consideration for database read/writes rate. Does it efficient and optimizing the resource well if using 2 separate machine? Or any better suggestion for high performance database optimized with the resource? How about the security?

edit: 2 server with 4 thread processor (1 Web-server & 1 Database) vs 1 Server with 4 thread processor. Does it matter (always in term of performance)? Also how does it apply like Cloud Hosting performance? It does share database too among all their cluster to distribute resources and high availability am I wrong?

If I understand correctly you are asking:
"Which would be better and faster, to run everything on 1 machine and have clients directly connect to the server to remove networking as a potential bottleneck, or having clients access the server from another machine by moving the web services and log in to that other machine?"

In I.T. we usually try to squeeze out all the performance we can for every dollar thus I could see why you would ask this, but this is a dangerous path that is being entertained.

Firstly, what you're really talking about is resource utilization and where does the bottleneck lie.  If your bottleneck is in fact networkIO, then this would help performance but you run A LOT of risks which are outlined below.  However, if NetworkIO REALLY is the bottleneck, you have to fix that.  Use NOCOUNT ON in your code, verify the network is stable.  Rarely is network_io the bottleneck compared to diskIO or RAM.

What DB Engine are you using?  If you're using MS SQL Server you could do a simple

SELECT * FROM SYS.DM_OS_WAIT_STATS ORDER BY 3 DESC

How high does network IO show up on the list?  Depending on your version you should get 450-500 rows or so returned.  If NetworkIO isn't even in the top 50 then you probably shouldn't entertain this idea.

Having Everyone Directly Connect On 1 Machine And Run DB/Web Services Locally:

In a world where I.T. best practices trump all else, then you would without a doubt separate client access from the main machine.  There is just way too many things that could go wrong.  For example, if you're using SQL Server and SSMS, SSMS itself will EASILY eat up 500MB per instance.  That means every client who connects and opens up SSMS will eat up RAM, so if you have 20 people connecting, you just ate up 10GB ram.

Also while devs might be great at development, but unless you have a strong DevOps environment they cannot be trusted in prod.  They are under the gun to get things to run fast and will just hack around.  When you remove devs from prod all kinds of mystery issues immediately leave.

Security would be another risk.  In many I.T. environments I've consulted at, security was an afterthought.  I wouldn't be surprised if devs and users would have access to install files, create shares, change settings, etc. on your prod server.  This is horrible.

How about if they start saving large files on your drives and you run out of disk space?

Having everyone connect from a web server:

In this scenario, users are still connecting to the web server directly which means they could stop web services, however those could typically be recovered fast and it won't affect the overall health of the DB server.  You isolate the DB from everyone and this is a much safer alternative.


Mysql master-slave replication makes no sence I have the following values in my.cnf settings in a simple Master -> Slave setup:

# MASTER my.cnf:
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format=ROW
binlog_do_db = db2
max_binlog_cache_size = 2G
binlog_cache_size = 32K
max_binlog_stmt_cache_size = 2G
binlog_stmt_cache_size = 32K
max_binlog_size = 100M
expire_logs_days=3

# SLAVE my.cnf
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
relay-log = /var/log/mysql/mysql-relay-bin.log
binlog_format=ROW
replicate-wild-do-table=db2.%
max_binlog_cache_size = 2G
binlog_cache_size = 32K
max_binlog_stmt_cache_size = 2G
binlog_stmt_cache_size = 32K
max_binlog_size = 100M
expire_logs_days=3

The MySQL documentation states that in row-based replication the USE database is ignored and only the changed database is taken under consideration. However I've noticed the following behavior:

USE db1;
CREATE TABLE db2.Fake (field1 int,field2 int);

This is not replicated to Slave.

When I change the USE to db2 the replication works.
Am I missing something here ?

Am I missing something here ?

sure, just few row above:

  For example, DDL statements such as CREATE TABLE and ALTER TABLE are always logged as statements, without regard to the logging format in effect, so the following statement-based rules for --binlog-do-db always apply in determining whether or not the statement is logged.


How do I convert between the transaction ID formats in fn_dblog() and sys.dm_tran_database_transactions? fn_dblog() shows transaction IDs in this format:

0000:00049d43

And sys.dm_tran_database_transactions shows transaction IDs in this format:

9811233

I believe the above examples show the same transaction ID in different formats.

Is there a way to convert between the two formats? Or are these actually two different entities -- and in that case, is there a way of matching up fn_dblog() with the transaction-related DMVs?

I do not believe that the [Transaction ID] column of sys.fn_dblog() is the transaction ID you are looking for. The transaction id in that result set that matches the transaction_id column of sys.dm_tran_database_transactions is [Xact ID].

Try the following:

Find a Transaction ID

SELECT *
FROM   sys.dm_tran_database_transactions
WHERE  database_id = DB_ID();

Get that Transaction's details from the Log

DECLARE @TransactionID NVARCHAR(50);

SELECT @TransactionID = [Transaction ID]
FROM   sys.fn_dblog(NULL, NULL)
WHERE  [Xact ID] = 10450416; -- some [transaction_id] from sys.dm_tran_database_transactions

--SELECT @TransactionID;

SELECT *
FROM   sys.fn_dblog(NULL, NULL)
WHERE  [Transaction ID] = @TransactionID;


how to sum times in EXPLAIN for Postgres parallel query I'm having trouble understanding this EXPLAIN on Postgres 12.3:
EXPLAIN (ANALYZE, VERBOSE, BUFFERS) SELECT count(1) FROM mytable WHERE page ~ 'foo';

This is a 22GB table with 30 million rows, on a server with 16GB memory. The query counts 7 matching rows.
I interpret the output as saying 164 seconds was spent on I/O, yet the whole query only took 65 seconds. I thought it might be double counting some parallel workers, but when I add VERBOSE, it doesn't seem to add up either.
It looks like it's saying each of 2 workers spent ~55 seconds reading. If that sums to 110 seconds, how do I get to 164 seconds of I/O? (Since this query takes ~10 seconds when pages are cached, I'm guessing the actual time reading isn't too far from 50 seconds here, FWIW)
I'm also confused how the Parallel Seq Scan seems to take 32 seconds, but then there's still another 30+ seconds left to get the final result. I would think that because of the 7 rows it finds, there's really almost no work to do besides the scan. Am I reading this section wrong?
                                                                       QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=3092377.14..3092377.15 rows=1 width=8) (actual time=65028.818..65028.818 rows=1 loops=1)
   Output: count(1)
   Buffers: shared hit=75086 read=2858433 dirtied=1
   I/O Timings: read=164712.060
   ->  Gather  (cost=3092376.92..3092377.13 rows=2 width=8) (actual time=65028.732..65030.093 rows=3 loops=1)
         Output: (PARTIAL count(1))
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=75086 read=2858433 dirtied=1
         I/O Timings: read=164712.060
         ->  Partial Aggregate  (cost=3091376.92..3091376.93 rows=1 width=8) (actual time=65026.990..65026.990 rows=1 loops=3)
               Output: PARTIAL count(1)
               Buffers: shared hit=75086 read=2858433 dirtied=1
               I/O Timings: read=164712.060
               Worker 0: actual time=65026.164..65026.164 rows=1 loops=1
                 Buffers: shared hit=25002 read=952587
                 I/O Timings: read=54906.994
               Worker 1: actual time=65026.264..65026.264 rows=1 loops=1
                 Buffers: shared hit=25062 read=954370 dirtied=1
                 I/O Timings: read=54889.244
               ->  Parallel Seq Scan on public.ui_events_v2  (cost=0.00..3091374.68 rows=896 width=0) (actual time=31764.552..65026.980 rows=2 loops=3)
                     Filter: (ui_events_v2.page ~ 'foo'::text)
                     Rows Removed by Filter: 10112272
                     Buffers: shared hit=75086 read=2858433 dirtied=1
                     I/O Timings: read=164712.060
                     Worker 0: actual time=16869.988..65026.156 rows=2 loops=1
                       Buffers: shared hit=25002 read=952587
                       I/O Timings: read=54906.994
                     Worker 1: actual time=64091.539..65026.258 rows=1 loops=1
                       Buffers: shared hit=25062 read=954370 dirtied=1
                       I/O Timings: read=54889.244
 Planning Time: 0.333 ms
 Execution Time: 65030.133 ms

Since parallel_leader_participation is at its default value off, the leader participates in the sequential scan. The I/O times of the worker processes are listed individually, but the I/O time of the leader can only be found by subtracting the workers' times from the total time.
The parallel sequential scan took 65026.980 milliseconds, almost all of the time. The fist number (31764.552) is the startup time, that is the time it took until the first result row was returned. If anything surprises me here, it is that high startup time.


What is the recommended RAID configuration for an Oracle database? RAID (Redundant Arrays of Inexpensive Disks) comes with different configurations (RAID-0, RAID-1...). What is the recommended RAID configuration that I should set up and use when installing an Oracle database. The database will mainly be used as a data warehouse.

It depends. When looking at a data warehouse, if you don't have a specific design in mind, automatic storage management may be an excellent route.

Consider the discussion at AskTom, OTN Forums, OTN Forums 2, and OTN Forums 3.

There is no one right way to deal with things, and the answers change based on a host of hardware and network factors. In order to discover for yourself, preload a sample data warehouse (only a gig or two, enough to play with) on an ASM based machine, on a SAN with the Raid being virtualized by linux and on a hardware based raid machine.

By timing the results of queries on all three of the environments, you'll be able to discover which methodology works the best for you performance-wise. I've deployed databases using ASN and linux-based virtual raids, and a virtual raid behaved slightly better (a few years ago.) However, I suspect that was in part the way the drives were set up.

There is no singular right answer. If you can provide us more details about the size and performance requirements, it may be possible to explore various test cases.

--Edit--

Every "disk group" may be made up of one or more disks, directories, or files on the appropriate subsystem. Oracle recommends "For best performance and reliability, choose a RAID device or a logical volume on more than one physical device and implement the stripe-and-mirror-everything (SAME) methodology." when placing files on a filesystem. That reads as if oracle is recommending RAID 1+0.

ASM managed disk groups, however, "A normal redundancy disk group requires a minimum of two failure groups (or two disk devices) if you are using two-way mirroring. The effective disk space in a normal redundancy disk group is half the sum of the disk space in all of its devices" apparently automatically provide mirroring.

These devices themselves can be comprised of RAID devices, and so on. In practical tests when I was setting up RAIDed data warehouses, a simple virtual RAID 5 on the filesystem provided acceptable performance, and additional ASM added no performance benefits. In this kind of optimization task, first identify your resources, and then test the every possible configuration, as sometimes the results can be extremely counterintuitive.


Problem creating my first database , annoying error I created two tables:

person (id as pk , person and purchase)
user (id as pk , other columns not relevant to question).

After linking the two with an n:m relationship, and proceeding to forward engineer (I'm using MySql Workbench), I get this error:

  Error:  Executing SQL script in server

  ERROR: Error 1064: You have an
  error in your SQL syntax; check the manual that corresponds to your
  MariaDB server version for the right syntax to use near ' INDEX
  fk_table1_has_user_table1_idx (table1_id ASC) VISIBLE, CONSTRAINT' at
  line 8

Note: I'm following a tutorial. As far as I can tell, my code's the exact same as what's in the tutorial, and the tutorial guy didn't get an error.

SQL Code:

-- Table mydb.table1_has_user --
-----------------------------------------------------
 CREATE TABLE IF NOT EXISTS mydb.table1_has_user
      ( table1_id INT NOT NULL,
        user_user id INT NOT NULL,
        PRIMARY KEY (table1_id, user_user id),
        INDEX fk_table1_has_user_user1_idx (user_user id ASC) VISIBLE,
        INDEX fk_table1_has_user_table1_idx (table1_id ASC) VISIBLE,
        CONSTRAINT fk_table1_has_user_table1 FOREIGN KEY (table1_id)
            REFERENCES mydb.table1 (id) ON DELETE NO ACTION ON UPDATE NO ACTION,
        CONSTRAINT fk_table1_has_user_user1 FOREIGN KEY (user_user id)
            REFERENCES mydb.user (user id) ON DELETE NO ACTION ON UPDATE NO ACTION
     ) ENGINE = InnoDB

SQL script execution finished: statements: 7 succeeded, 1 failed

Fetching back view definitions in final form.
Nothing to fetch

Can someone please help me with this? I don't know what I'm doing wrong , the primary key is correct why is this happening?

This is the diagram of the database I'm trying to create:

The keyword VISIBLE is a MySQL-8.0 syntax. If you are using an earlier version, or MariaDB, omit this word.


How to load data into Vertica from C? I'm investigating column oriented databases and came across Vertica.

My need is to feed the Vertica database from C code. I don't succeed in grabbing this information from Vertica: I'm told to use "vsql" and the "copy" command. All I want is issue INSERT statements to my Vertica database.

Can this be done?

For instance, in PostgreSQL you can do "embedded SQL" by linking the Postgres ecpg library to your C binary. I have no idea if such thing exists for Vertica, and I know of no other way.

Any ideas?

You have two options really:
You can create a delimited file with your data, like the following:

row1col1data,row1col2data,row1col3data,row1col4data
row2col1data,row2col2data,row2col3data,row2col4data
row3col1data,row3col2data,row3col3data,row3col4data

You can then load that into Vertica using the COPY command and vsql. If you have database superuser rights, you can load the file directly, using:

vsql -U <username> -w <password> YOURDATABASENAME -c "COPY yourtablename (col1name, col2name, col3name, col4name) FROM 'yourdelimitedfile' DELIMITER ','"

Omit -w  if you want to enter keep the password out of your bash history and enter it when the program starts. If you don't have database superuser rights, you can still cat the file into vsql and get the data from STDIN - quite why Vertica prevents you from loading data from a file without superuser rights, but will let any user pipe it in via cat, I have no idea, but you can do it as follows:

cat yourdelimitedfile | vsql -U <username> -w <password> YOURDATABASENAME -c "COPY yourtablename (col1name, col2name, col3name, col4name) FROM STDIN DELIMITER ','"

Or, if you have a bunch of individual insert statements in a file, separated by semicolons, you can just run the file through vsql, as follows:

vsql -U <username> -w <password> YOURDATABASENAME -f pathtoyoursqlfile

But if you're doing more than 1000 rows, bulk loading using a delimited file is supposed to be much faster.

Hope that helps!


ROWLOCK in sql server I have a table to update with no indexing in the where clause so I use ROWLOCK in the update script, hoping to get row lock instead of table lock but no luck.. so what is the function of the ROWLOCK then? I was using it in the select statement but still locking the entire table... so annoying!

  DBCC TRACEON (-1, 3604, 1200)

  BEGIN TRAN

  UPDATE [Order]

  with (ROWLOCK)

  SET ProductId = 3

  WHERE CustomerId = 1

TRACE OUTPUT:

  DBCC execution completed. If DBCC printed error messages, contact your
  system administrator.

  Process 54 acquiring IX lock on OBJECT: 16:229575856:0 (class
  bit2000000 ref1) result: OK

  Process 54 acquiring IU lock on PAGE: 16:1:196 (class bit0 ref1)
  result: OK

  Process 54 acquiring U lock on RID: 16:1:196:0 (class bit0 ref1)
  result: OK

  Process 54 acquiring IX lock on PAGE: 16:1:196 (class bit2000000 ref0)
  result: OK

  Process 54 acquiring X lock on RID: 16:1:196:0 (class bit2000000 ref0)
  result: OK

  Process 54 releasing lock reference on RID: 16:1:196:0

  Process 54 acquiring U lock on RID: 16:1:196:1 (class bit0 ref1)
  result: OK

  Process 54 releasing lock on RID: 16:1:196:1

  Process 54 releasing lock reference on PAGE: 16:1:196

  (1 row(s) affected)

You ARE getting row locks!

See Understanding Locking in SQL Server to understand why a ROWLOCK update must acquire hierarchical intent locks, for an explanation of the Process 54 acquiring IU lock on PAGE: 16:1:196 (class bit0 ref1) result: OK.


Entity Relationship Diagram - Final Physical Data model Review I am new to this programming world and I am learning drawing ER diagram for a small airport management suite and use the below physical data model in my project report before that, I want to verify If the the following are correct or not.
(i) Relationships
(ii) Primary Keys and Foreign Keys
(iii) In Actual_flights_ there is a aircraft_id which must be FK but it was not mentioned as FK against it in the diagram, same with pilot_id.
(iv) In table Hangers, Airport_ID is termed as FK but there is no relation between Hangers and Airports_ table.

Hence, I would like to verify before using the same ER. For your reference. I am currently developing database in access. Please help me!!

Entity Diagram

Yes, aircraft_id should be a FK to the Aircraft_ table, just as pilot_id should refer to the Pilot_ table. There are other problems, such as there is no way to limit pilots to only aircraft they are rated to fly. Any pilot may be thrown in with any aircraft. And not all airports can handle all aircraft. Many cannot handle the larger aircraft (know as heavies). One of the attributes I would think important for airports is how many heavy-capable runways they have. Come to think of it, how many runways total.

Look into other relationships. Write them out in plain English, such as: "Each aircraft has one or more owners." This shows a relationship between aircraft and owners that is not represented in your schema design.

The actual_flight_ table relates a pilot with an aircraft, thus the need for FKs, and other attributes into a, well, actual flight. This can also be stated in plain language so you can understand the entities and their relationships.


Help with Query. Finding records that have the same relationships (MySQL) I have a table of Listings that has a many to many relationship with a Taxons table. The table structure looks like this:

listings
----------------
id (int)
name (varchar)

listings_taxons
----------------
listing_id (int)
taxon_id (int)

taxons
----------------
id (int)
name (varchar)

My goal is to select all rows in the listings table that has a matching list of taxon ids. Each returned listing record must have a relationship with the two taxons, such that I get the record set containing the intersection of records between the two taxons.

Example: I have a listing called "Muffler" and it has the following taxons: "Ford", "Mustang", "Exhaust". If I query for all listings with "Ford" and "Exhaust" I should get all listings that have "Ford" and "Exhaust" as taxons.

How would I construct this query efficiently?

If I understand correctly, you want to perform relational-division. Try this question with lots of different ways to accomplish that: How to filter SQL results in a has-many-through relation.

I would go for the (multiple) JOIN solution but you can always test with your data and queries:

SELECT
    li.*

FROM
    listings AS li

  JOIN
    listings_taxons AS lt1
      ON  lt1.listing_id = li.id
  JOIN
    taxons AS t1
      ON  t1.id = lt1.taxon_id
      AND t1.name = 'Ford'

  JOIN
    listings_taxons AS lt2
      ON  lt2.listing_id = li.id
  JOIN
    taxons AS t2
      ON  t2.id = lt2.taxon_id
      AND t2.name = 'Exhaust'


Oracle - physical writes to an index We have a large UPDATE (affects about 200K records) on a huge table (80M records).
Due to a coding mistake, the UPDATE updates the same set of rows (200K) all over again with the exact same value.

There is a temporary index (say, TMP_INDX) on the column that the UPDATE updates. [This column is also used in the WHERE clause]. This index is "temporary" in the sense it is created at the beginning of the script and dropped at the end.
Now, the UPDATE runs as part of daily batch and is fast on all days except on one day of the week where it takes almost 10x more time than the rest of the days.
AWR shows there is a 1000x times increase in the physical writes to TMP_INDX on the day the update takes lot of time. This is the only thing that seems to change in the AWR. Strangely, AWR shows "dbfile sequential read" to be the culprit instead of "physical writes" on the days this happens. The execution plan is still the same as the other days.

Given the above input, I am thinking the slow UPDATE might be the result of the increase in the physical writes to the index caused by the update to a column that is part of a composite index. However, as I mentioned earlier, the column is updated with the exact same value, in which case oracle seems to do redo optimization which will not update the index. I could not understand why this would fail on one particular day of the week. Can somebody throw some light into this? Thanks.

I would guess that there is a weekly maintenance job that is running on that one day a week, that is affecting something that is forcing extra reads of the index.  Is your daily batch job always run long on the same day of the week each week?  If so,  look at the views dba_scheduler_jobs and dba_scheduler_schedules to see if any jobs are scheduled.

Now the bigger question is, is why not fix the UPDATE statement to include criteria to avoid updating rows where the value is already the correct value?  IE:

update student set score = 100 where score != 100 -- and whatever other criteria

Also as a temporary fix to your "temporary" index, create it using the nologging clause in the index attributes part might help a little.


rename datafile when file does not exists I am trying to clone our production database in test virtual machine. Our production database works with ASM. I would like to use plain file system in test database.

I created database, services, pfile etc. I gave production database control files to test database.  Right now I am trying to rename datafiles in test database. I get following error.

 SQL> startup mount;
  .....
 SQL> alter database rename file '+NEWDATA/XX/XX01.dbf' to      'M:\oracle\datafiles\XX\XX01.dbf'; alter database rename file
 '+NEWDATA/XX/XX01.dbf' to 'M:\oracle\datafiles\XX\XX01.dbf';
* ERROR at line 1: ORA-01511: error in renaming log/data files ORA-01141: error renaming data file 162 - new file
'M:\oracle\datafiles\XX\XX01.dbf' not found ORA-01110: data file 162:
'+NEWDATA/XX/XX01.dbf' ORA-27046: file size is not a multiple of
logical block size OSD-04012: file size mismatch O/S-Error: (OS 4) The
system cannot open the file.

My backup is normal rman backup pieces not backup as copy. I am trying to rename nonexistent files. After this rename process , I will try to restore database from rman backup. But this backup is not piece by piece copy.

You wrote:

  After this rename process , I will try to restore database from rman
  backup. But this backup is not piece by piece copy.

Then this is not the method for doing this. In order to use the ALTER DATABASE RENAME FILE, the file have to already exist at the new location. Instead of this, you can (and should) handle this in RMAN:

sqlplus / as sysdba
startup mount
exit

Which you have already done, then:

rman target /

And run the following:

run
{
  set newname for datafile '+NEWDATA/XX/XX01.dbf' to 'M:\oracle\datafiles\XX\XX01.dbf';
  set newname for datafile '+NEWDATA/XX/XX02.dbf' to 'M:\oracle\datafiles\XX\XX02.dbf';
  set newname for datafile '+NEWDATA/XX/XX03.dbf' to 'M:\oracle\datafiles\XX\XX03.dbf';
  # ..., list all datafiles this way
  # alternatively you can use the datafile number: set newname for datafile 1 to '.../...dbf';

  restore database;
  switch datafile all;
  recover database;
}


What are the things need to be considered while creating tablespace? I am creating temporary/permanent tablespace on Oracle. As I am going to create an application that is transactional there will be traffic. I bumped into concept called tablespace in oracle. What kind practial things should I consider while creating tablespace?
Should I create temporary table or should I use default temp tablespace?
Are there any resource that I can study except theory?

I have though to create tablespace as:

 CREATE TEMPORARY TABLESPACE OPTIMADATA_TEMP TEMPFILE
    '/opt/app/oracle/oradata/orcl/test01.dbf' SIZE 1024M reuse autoextend on,
    '/opt/app/oracle/oradata/orcl/test02.dbf' SIZE 1024M
    reuse
    autoextend on
    extent management local;

Do I create multiple files or Single file is sufficient?

CREATE TABLESPACE OPTIMADATA DATAFILE '/opt/app/oracle/oradata/orcl/test.dbf' size 2000M AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL;

Do I always do Autoextend on ??

While creating a tablespace, you need to consider few things like

i) Type of the tablespace either permanent or temporary or undo (default is permanent)

ii) Extent management whether it is managed locally or dictionary based

iii) Segment space management whether it is automatically managed or manually managed

Always you can use temporary tablespace since it is the best practice. You can create multiple files and mainly it depends on your application.

We have two types of tablespaces like small file and big file. Small file tablespaces can have multiple datafiles. Each file with maximum size of
1024 bytes. Big file tablespaces should contain only one file with maximum size of 8 Hexabyte.


Restore db with encrypted column in SQL Server, without overwriting existing Service Master Key Scenario:

Server1 running SQL Server 2012 with Service Master Key A, db1 with Database Master Key 1, symmetric key and certificate available. Password used to protect the DBMK 1 is unknown, encryption is enabled by SMK A.

Server2 running SQL Server 2012 with SMK B, db2 with DBMK 2, symmetric key and certificate available. Password used to protect DBMK 2 is unknown, SMK B used for encryption.

A restore of db2 to Server1 is required. There's one encrypted column in a table that stores passwords for an internal ASP.NET application.
There are other applications accessing encrypted data in db1 on Server1 .

Question:

Best option to restore db2 to Server1 and access the encrypted data without causing issues to db1 ?

Tested:

A simple restore will bring the db2 data, symmetric key and certificate to Server1, but SMK A can't be used to decrypt the data: "Please create a master key in the database or open the master key in the session before performing this operation."
Restored SMK B on a test server and all was good. Can't use this solution on Server1, as I believe that restoring SMK B to Server1 (overwriting SMK A) will disable my access to the encrypted data in db1. Don't want that to happen.

Any advice ? Thank you.

Server1 running SQL Server 2012 with Service Master Key A, db1 with Database Master Key 1, symmetric key and certificate available.

I assume that the db1 master key is encrypted with the SMK. This makes everything encrypted by the database master key 'available' to applications, w/o having to explicitly open the database master key.

What you need to do is to restore the database, open the database master key using the DBMK password and then add the Server 2 SMK encryption  to the DBMK:

RESTORE DATABESE ... FROM ...;
USE ...;
OPEN MASTER KEY DECRYPTION BY PASSWORD = ...;
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;


Pull multiple fields from table B where a specific value from Table A is not in Table B SQL Newbie here. I have a question about pulling multiple fields from a database when one value is not in another table. I have two tables:

"Contact" table - contains all of our contact information such as their email address, contact creation date, department, job, etc. This is considered our master table.
"Event" table - contains information regarding a contact and their email activity such as their email address, email delivery status, email create date, etc.

The db uses the email address as the joining key. I don't have the privileges (due to work restrictions) to add a new PK.

The task is to find all contacts in the Contact table that are not in the Event table, and pull their email address AND their contact creation date. The Event table does not have the contact creation date. When I select only the email address it works fine, but when I try to add in the second field (create date), it gives me an error.

The query I'm using is below. Any help is appreciated!

SELECT e.recipient, c.contactProperty_createdate
FROM Event as e
WHERE NOT EXISTS
    (SELECT c.emailAddress, c.contactProperty_createdate
    FROM Contact as c
    WHERE c.emailAddress = e.recipient);

Based on your comment

  The task is to find all contacts in the Contact table that are not in
  the Event table, and pull their email address AND their contact
  creation date.

Why doesn't this work?

SELECT c.emailAddress
    ,c.contactProperty_createdate
FROM Contact AS c
WHERE NOT EXISTS (
        SELECT *
        FROM Event AS e
        WHERE e.recipient = c.emailAddress
        );


There are quite a few ways to achieve your desired results.

Undeterministic methods

(in the event that many rows in table 2 match one in table 1)

UPDATE T1
SET    address = T2.address,
       phone2 = T2.phone
FROM   #Table1 T1
       JOIN #Table2 T2
         ON T1.gender = T2.gender
            AND T1.birthdate = T2.birthdate

Or a slightly more concise form

UPDATE #Table1
SET    address = #Table2.address,
       phone2 = #Table2.phone
FROM   #Table2
WHERE  #Table2.gender = #Table1.gender
       AND #Table2.birthdate = #Table1.birthdate

Or with a CTE

WITH CTE
     AS (SELECT T1.address AS tgt_address,
                T1.phone2  AS tgt_phone,
                T2.address AS source_address,
                T2.phone   AS source_phone
         FROM   #Table1 T1
                INNER JOIN #Table2 T2
                  ON T1.gender = T2.gender
                     AND T1.birthdate = T2.birthdate)
UPDATE CTE
SET    tgt_address = source_address,
       tgt_phone = source_phone

Deterministic methods

MERGE would throw an error rather than accept non deterministic results

MERGE #Table1 T1
USING #Table2 T2
ON T1.gender = T2.gender
   AND T1.birthdate = T2.birthdate
WHEN MATCHED THEN
  UPDATE SET address = T2.address,
             phone2 = T2.phone;

Or you could pick a specific record if there is more than one match

With APPLY

UPDATE T1
SET    address = T2.address,
       phone2 = T2.phone
FROM   #Table1 T1
       CROSS APPLY (SELECT TOP 1 *
                    FROM   #Table2 T2
                    WHERE  T1.gender = T2.gender
                           AND T1.birthdate = T2.birthdate
                    ORDER  BY T2.PrimaryKey) T2

.. Or a CTE

WITH T2
     AS (SELECT *,
                ROW_NUMBER() OVER (PARTITION BY gender, birthdate ORDER BY primarykey) AS RN
         FROM   #Table2)
UPDATE T1
SET    address = T2.address,
       phone2 = T2.phone
FROM   #Table1 T1
       JOIN T2
         ON T1.gender = T2.gender
            AND T1.birthdate = T2.birthdate
            AND T2.RN = 1;


MongoDB Restore / Snapshot Schedule - Point In Time vs. Oplog Timestamp in docs Restore a Replica Set from a Backup those restore points are available:

Snapshot
Point In Time
Oplog Timestamp

What's the differences between those restore points? I guess Snapshot is fast restore, because only copy already existing files to destination. But it takes compute and storage resources the generate every 6 hours a snapshot and it will be stored only for 3 days.

I don't understand the difference between Point In Time and Oplog Timestamp. When to choose what (advantages and disadvantages)? Point in Time is only available 24 hours back, but Oplog Timestamp since first backup (Oplog Store)?

this is our snapshotSchedule. Any improvments for generic DBaaS provider, which doesn't know individual customers? Any issues?

configureDefaultBackupOptions = true
snapshotIntervalHours = 6 //Supported values are 6, 8, 12, and 24
snapshotRetentionDays = 3 // Supported values are 1 - 5
dailySnapshotRetentionDays = 10 //Supported values are 1 - 365
weeklySnapshotRetentionWeeks = 6 //Supported values are 1 - 52.
monthlySnapshotRetentionMonths = 6 // Supported values are 1 - 36
pointInTimeWindowHours = 24
retryIntervalInSeconds = 15
maxRetryDurationInMinutes = 30

What's the differences between those restore points? I guess Snapshot is fast restore, because only copy already existing files to destination. But it takes compute and storage resources the generate every 6 hours a snapshot and it will be stored only for 3 days.

A snapshot is a full backup of your data captured at a specific interval. Restoring from a stored snapshot is the fastest option because minimal manipulation needs to be done by Ops Manager in order to provide the restore files. The snapshotIntervalHours (6 hours in your config) and snapshotRetentionDays (3 days in your config) are meant to provide more frequent restore points for recent data as compared to the archived daily/weekly/monthly snapshots. With your current configuration, restores would be possible to: stored snapshots taken on 6 hour intervals in the last 3 days, a point in time within the last 24 hours, or a daily/weekly/monthly snapshot.

  I don't understand the difference between Point In Time and Oplog Timestamp. When to choose what (advantages and disadvantages)?

These are both options for custom point-in-time snapshots, which can be time and resource intensive for Ops Manager to process. The backend processing involves restoring the nearest (older) stored snapshot and then applying the oplog changes up to the specified point in time (as marked by a date/time in seconds or an oplog timestamp). Both are limited by the available oplog history as specified in your pointInTimeWindowHours configuration (i.e. 24 hours).

The Point In Time restore point creates a custom snapshot up to the selected date and time; the Oplog Timestamp is a more precise custom snapshot which presumes you know the exact oplog timestamp you want to restore to (for example, up to and including the entry preceding an accidental collection drop).

  Any improvments for generic DBaaS provider, which doesn't know individual customers? Any issues?

It depends what level of backup/restore granularity you want to offer your customers. Increasing snapshot frequency or retention will consume more server resources, but perhaps provide more comfort for your customers. Conversely, you could reduce the snapshot frequency or retention to save on resources.

As a DBaaS provider I expect you would work standard backup & licensing costs into your service model and perhaps allow customers to vary backup options based on subscription plans or premiums (for example, as MongoDB Cloud Manager does).

Note: MongoDB Ops Manager is not currently intended to be the base for a DBaaS offering and should only be used in production as part of a MongoDB Enterprise Advanced subscription (which includes commercial support for questions such as configuration, capacity planning, tuning, ...).


Sum values from two tables ORACLE I want two values, in two different tables, to be used as a total in a third table.

For instance:

Table accommodation and flight
Add value from acc_price with value from flight_price
Add those values up to be used in table reservation subtotal

Things I've tried;

SELECT  F.FLI_PRICE + AC.ACC_PRICEPN
INTO    R.SUBTOTAL
FROM    HOLIDAY_RESERVATION R, FLIGHT F, ACCOMMODATION AC;

UPDATE

I wish to add the code from Leigh into a trigger, I tried the following but got a mutation error

CREATE OR REPLACE TRIGGER HR_SUBTOTAL
AFTER DELETE OR INSERT OR UPDATE ON HOLIDAY_RESERVATION
FOR EACH ROW
BEGIN
UPDATE HOLIDAY_RESERVATION R SET SUBTOTAL =
   NVL((SELECT F.FLI_PRICE FROM FLIGHT F WHERE F.FLI_ID = R.IN_FLIGHT_ID), 0) +
   NVL((SELECT F.FLI_PRICE FROM FLIGHT F WHERE F.FLI_ID = R.OUT_FLIGHT_ID), 0) +
   NVL((SELECT AC.ACC_PRICEPN FROM ACCOMMODATION AC WHERE AC.ACC_ID = R.ACC_ID), 0);
END;
/

ERROR at line 1:
ORA-04091: table LATIN.HOLIDAY_RESERVATION is mutating, trigger/f
ORA-06512: at "LATIN.HR_SUBTOTAL", line 2
ORA-04088: error during execution of trigger 'LATIN.HR_SUBTOTAL'

To update a column using a sum of values from other tables that may have nulls, do something like this:

UPDATE Holiday_Reservation R SET Subtotal =
   NVL((SELECT F.Fli_Price FROM Flight F WHERE F.Fli_ID = R.In_Flight_ID), 0) +
   NVL((SELECT F.Fli_Price FROM Flight F WHERE F.Fli_ID = R.Out_Flight_ID), 0) +
   NVL((SELECT AC.Acc_PricePn FROM Accommodation AC WHERE AC.Acc_ID = R.Acc_ID), 0);

In a trigger this would look something like this:

CREATE OR REPLACE TRIGGER HR_SUBTOTAL
BEFORE INSERT OR UPDATE ON HOLIDAY_RESERVATION
FOR EACH ROW
BEGIN
   SELECT
      NVL((SELECT F.Fli_Price FROM Flight F WHERE F.Fli_ID = :new.In_Flight_ID), 0) +
      NVL((SELECT F.Fli_Price FROM Flight F WHERE F.Fli_ID = :new.Out_Flight_ID), 0) +
      NVL((SELECT AC.Acc_PricePn FROM Accomodation AC WHERE AC.Acc_ID = :new.Acc_ID), 0)
      INTO :new.Subtotal
   FROM dual;
END;
/


Select ``,* from table? I'm trying to upgrade our database servers to MySQL 5.5. In doing so, I'd like to convert all of our tables to InnoDB to take advantage of several performance benefits.

However, we use VARCHAR(50) fields for all of our primary keys. From what I've read about InnoDB indexes, the shorter the primary key the better, since all secondary keys reference the primary key. So, instead of converting the tables by issuing an ALTER TABLE tablename ENGINE = InnoDB;, I'd like to create a new temporary table with all of the fields from the old table plus a new autoincrement primary key. The old primary keys will become unique keys. Then, I'll insert all rows into the new table, drop the old table, and rename the new table to the old.

However, I need a clever query to do so, because insert into tablename_temp '',* from tablename; generates a syntax error (I assume because I can't select anything with *). I'm trying to come up with an alternative, but I can't figure it out.

Short question: How can I insert into tablename_temp '',* from tablename;

Any input is much appreciated!

You can do the following:

CREATE TABLE tablename_temp (id int AUTO_INCREMENT PRIMARY KEY)
ENGINE=InnoDB
SELECT * FROM tablename;

ALTER TABLE b
ADD UNIQUE INDEX some_index_name (your_original_key);

You can find the documentation here: http://dev.mysql.com/doc/refman/5.1/en/create-table-select.html
And a small example on SQLFiddle.


Unquoting JSON strings; print JSON strings without quotes SELECT json_array_elements('["one", "two"]'::json)

gives result

| json_array_elements |
| :------------------ |
| "one"               |
| "two"               |

I would like to have the same but without the quotes:

one
two

Looks like I can't use ->> here because I don't have field names in the JSON. It's just an array of strings.

Postgres version:
PostgreSQL 10.0 on x86_64-apple-darwin, compiled by i686-apple-darwin11-llvm-gcc-4.2 (GCC) 4.2.1 (Based on Apple Inc. build 5658) (LLVM build 2336.11.00), 64-bit

SELECT value#>>'{}' as col FROM json_array_elements('["one", "two"]'::json);

Result:

col
---
one
two


Is it possible to encrypt a column and NOT have to change app code? Is it possible to encrypt a column (with Symmetric Key and Triple DES encryption) and then set up a login for an application such that the query code in the app does not have to be changed to used the DECRYPTBYKEY function?

Right now the app is essentially using straight ANSI SQL.  Pseudo code would looks like:

String query = "select EncryptedCol from TestTable";

To my knowledge the above has to be changed to:

String query =
"OPEN SYMMETRIC KEY TestTableKey DECRYPTION " +
"BY CERTIFICATE EncryptionTestCert " +
"SELECT CONVERT(VARCHAR(50),DECRYPTBYKEY(EncryptedCol)) AS EncryptedCol " +
"FROM TestTable ";

SQL Server version is 2012 EE SP3-CU1 and the app is written in Java using jTDS.

Hope this question makes sense and appreciate any feedback.

This is what Always Encrypted was created for, but you'll have to wait for SQL Server 2016 (and for the jTDS drivers to catch up).

In the meantime, your Java app could call stored procedures, such that the data is encrypted there. That's really not going to change the fact that the decrypted text is being sent over the wire, but it does mean you can isolate query changes from the app...


How to know if I am running SQL Server 2008 R2? I ran @@version.

This is what it gave me:

Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64)   Sep 16 2010 19:43:16   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.0  (Build 6002: Service Pack 2)

How do I know if this is r2?

If you were running SQL Server 2008 R2 it would have 10.5.xxx instead of 10.0.xxx.  And it would also say Microsoft SQL Server 2008 R2 ....

You can always find out the named version by the major/minor build number:

10.5 : 2008 R2
10 : 2008
9 : 2005
8 : 2000
...so on and so forth


All recent changes in MySQL have been automatically removed. Recently INSERTed or UPDATEd data is not available I have been using MySQL for the last two months. I'm regularly INSERTing new records and UPDATEing old ones. But when I opened phpMyAdmin today, all of the changes which I've made in the 10 days have vanished.
INSERTed data for the last 10 days is unavailable and The UPDATEs I have made to other records have also reverted to previous versions.
The AUTO_INCREMENT field is still incrementing to the next number like nothing is DELETEd.
I need my recently INSERTed data and my UPDATEs restored.
More importantly perhaps, why is this happening?
This problem happen between 5th to 7th January. This is the Error file of these days:
Error Log

set autocommit=OFF
Do lots of INSERTs/UPDATEs/DELETEs
disconnect

Step 3 will do an implicit ROLLBACK since there was no COMMIT.  And this is why I advise against ever using autocommit=OFF.
And, yes, the AUTO_INCREMENT would not be reset.  That's just one of the quirks of auto_inc.  (Actually, it is an efficiency measure.)
So... either
SET autocommit=ON;

and leave it that way.  Check the configuration, too.  Or use transactions:
BEGIN;
inserts, etc
COMMIT;


"ERROR: could not read from config file" MongoDB I'm trying to configure MongoDB in OS X Mountain Lion. I've installed with Homebrew; I have followed lots of tutorials, but I have got an issue which I haven't been able to solved.

The issue is about mongod.conf file. I've got it in /usr/local/Cellar/mongodb/2.4.6/ directory and it contains the following code:

fork = true
bind_ip = 127.0.0.1
port = 27017
quiet = true
dbpath = /usr/local/Cellar/mongodb/2.4.6/data/db
logpath = /usr/local/Cellar/mongodb/2.4.6/data/log/mongod.log
logappend = true
journal = true

Why if I execute mongod in the terminal appears a ERROR: could not read from config file?

First, assuming you are actually specifying where to read the file from, make sure that you have permission read that file with the current user (cat /usr/local/Cellar/mongodb/2.4.6/mongod.conf - or use less/vi/editor of choice). Assuming that works (and if it does not, adjust your permissions), then the next thing you need to do is make sure you are actually pointing at the correct file.

However, if you are not specifying where to read the file from, by default, if you just run mongod using the brew installation it will attempt to read from:

/usr/local/etc/mongod.conf

I verified this by installing 2.4.6 with brew and then checking the logs when it starts up:

Tue Oct 22 17:17:30.695 [initandlisten] options: { bind_ip: "127.0.0.1", config: "/usr/local/etc/mongod.conf", dbpath: "/usr/local/var/mongodb", logappend: "true", logpath: "/usr/local/var/log/mongodb/mongo.log" }

You can either modify that file (/usr/local/etc/mongod.conf) to look the way you want in your example, and make sure you have permissions to get to it, or you can run this instead to specify the original file:

mongod -f /usr/local/Cellar/mongodb/2.4.6/mongod.conf


Select only rows with at least 2 non-missing values I'm pretty new to MySQL (used to programming in R/Stata), and getting stuck on this - I'm probably going about it the wrong way. Any pointers would be appreciated!

I have a large database (a couple million rows) and want to select records where at least two fields from a selection of variables are non-missing (not NULL). I can find lots on counting missing values by column, but not by row.

As an example, I would like to write a select statement for the table below, based only on var1-var5, that would select ID 1 and 3 as they have at least 2 non-NULL values.

ID | var1 | var2 | var3 | var4  | var5 | var6 | var7
1  | NULL | 4    | X    | NULL  | NULL | NULL | R69
2  | NULL | NULL | NULL | NULL  | 1    | X    | J10
3  | 4    | 5    | 23   | 1jan03| 2    | 22   | Z38

I was thinking creating a user-defined variable to count the number of non-missing values and delete rows where this is <2, but it doesn't seem to work.

SET @include = 0;
SELECT var1, var2, var3, var4, var5 FROM db1;
@include := @include + 1 if var1 is not null;
DELETE FROM db1 WHERE @include<2;

I'm guessing this is something I can do with a WHERE statement in the SELECT line, but I can only think of writing it so it only selects rows where everything is non-missing, not at least 2 values.

I have the following way to get the IDs of the rows that have at least two not null values; however, I am afraid this is not efficient when it comes to few million rows per table. I'd like to share it anyway:

mysql> select * from l;
+------+------+------+------+------+------+------+------+
| id   | var1 | var2 | var3 | var4 | var5 | var6 | var7 |
+------+------+------+------+------+------+------+------+
|    1 | NULL |    4 |    9 | NULL | NULL | NULL |    7 |
|    2 | NULL | NULL | NULL | NULL | NULL |    1 |    2 |
|    3 |    4 |    5 |   23 |    1 |    2 |    4 |   22 |
+------+------+------+------+------+------+------+------+
3 rows in set (0.00 sec)

mysql> select id, 5-(isnull(var1)+isnull(var2)+isnull(var3)+isnull(var4)+isnull(var5)) AS non_null_count from l;
+------+----------------+
| id   | non_null_count |
+------+----------------+
|    1 |              2 |
|    2 |              0 |
|    3 |              5 |
+------+----------------+
3 rows in set (0.00 sec)

mysql> SELECT id FROM (select id, 5-(isnull(var1)+isnull(var2)+isnull(var3)+isnull(var4)+isnull(var5)) AS non_null_count from l)x WHERE non_null_count>=2;
+------+
| id   |
+------+
|    1 |
|    3 |
+------+
2 rows in set (0.01 sec)

Basically, I am "counting" how many non-null values exist in each row.


Flush/Clean MySql ib_logfile0 ib_logfile1 Is there a way to flush ib_logfile0 and ib_logfile1 without dumping the tables to a .sql file, deleting, then re-inserting? Somebody entered a couple plain text credit card numbers into the customer notes table. It came up in my PCI scan and I removed them from the table but they still exist in the log files :(

You could have InnoDB clean things up as follows:

signal mysqld to have InnoDB flush everything out of the logs on shutdown
shutdown mysqld
delete the logs
start mysqld

No crash recovery will happen because there will be nothing to recover.
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS} -h127.0.0.1 -P3306 --protocol=tcp"
SQL="SET GLOBAL innodb_fast_shutdown = 0"
mysql ${MYSQL_CONN} -ANe"${SQL}"
mysqladmin ${MYSQL_CONN} shutdown
cd /var/lib/mysql
rm -f ib_logfile*
service mysql start

If you want lingering data pages cleansed out of the Double Write Buffer, please see my old post Fill ibdata1 With Zeros/Nulls/etc Or Change A Table's Tablespace
GIVE IT A TRY !!!