How to write a query, with multiple foreign key in mysql I am working on a simple PHP and MySQL college level clinic management system. There 4 main table which relation with foreign key. I'm little bit confusing multiple foreign key in table .. what would be MySQL query according tables specially Fee Transaction table ... help will appreciated...thanks

I did try this:

CREATE TABLE patient_appointment
(
appointment_ID int NOT NULL,
Date int NOT NULL,
time int,
PRIMARY KEY (appointment_ID),
CONSTRAINT fk_P_id FOREIGN KEY (patient_id)
REFERENCES patient(patient_id)
)

Double level . please check its right or wrong

CREATE TABLE patient_treatment
(
patient_treatment_no int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (patient_treatment_no),
CONSTRAINT fk_P_id FOREIGN KEY (Patient_id)
REFERENCES Patient(Patient_ID),
CONSTRAINT fk_t_id FOREIGN KEY (treatment_id)
REFERENCES treatment(treatment_id)
)

CREATE TABLE IF NOT EXISTS `patient` (
  `p_id` int(11) NOT NULL AUTO_INCREMENT,
  `fn` varchar(100) NOT NULL,
  `ln` varchar(100) NOT NULL,
  `father_name` varchar(100) NOT NULL,
  `contact` varchar(100) NOT NULL,
  `sex` varchar(20) NOT NULL,
  `DOB` varchar(100) NOT NULL,
  PRIMARY KEY (`p_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

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

CREATE TABLE IF NOT EXISTS `patient_treatment` (
  `p_t_no` int(11) NOT NULL AUTO_INCREMENT,
  `treatment_Fee` varchar(200) NOT NULL,
  PRIMARY KEY (`p_t_no`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `treatments` (
  `t-id` int(111) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `fee` varchar(20) NOT NULL,
  PRIMARY KEY (`t-id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Fee Transaction
Contains record of all the fee transactions carried out for each appointment by each patient.
-   Transaction_ID
-   Appointment_ID (Foreign Key)
-   Patient_ID (Foreign Key)
-   Treatment_ID (Foreign Key)
-   Treatment_Fee**

I don't know how to make table... help me please

Run the script below (in order). Take a look at the image.

You don't need a transaction_fee table - this information is
already in the treatment and medical_procedure tables.

A few words of advice.

You should take a look at Open Source medical practice and/or
hospital management software. You may get some ideas there (maybe even
better than mine!) - you can pick and mix which ideas you choose to
copy/adopt/modify.

The recommendations below about naming are my preferences - the ABSOLUTELY CRITICAL thing is to have a naming convention and STICK to it!

Keep all table names lower case, that way they are easy to distinguish from SQL using upper case.

Separate words using underscore_case - i.e. order_no.

Keep table names singular - tables are collections, so a singular title is appropriate. This one can provoke (almost religious) debate - as above, just pick one (singular or plural) and stick to it. The only reason to breach this rule is for SQL keywords!

If some appointments are with a nurse, you could have a table called medical_professional with a link to a staff_table_id - didn't have time
for that.

CREATE TABLE IF NOT EXISTS `users` -- didn't pick "user" - it is a singular SQL keyword!
(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(100) NOT NULL,
  `password` varchar(100) CHARACTER SET utf8 NOT NULL,
  `status` int(11) NOT NULL,
  `accessLevel` varchar(100) NOT NULL,
  `fk_id` int(11) DEFAULT NULL,
  `p_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `p_id` (`p_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

CREATE TABLE IF NOT EXISTS `patient`
(
  `patient_id` int(11) NOT NULL AUTO_INCREMENT,
  `fn` varchar(100) NOT NULL,
  `ln` varchar(100) NOT NULL,
  `father_name` varchar(100) NOT NULL,
  `contact` varchar(100) NOT NULL,
  `sex` varchar(20) NOT NULL,
  `DOB` varchar(100) NOT NULL,
  PRIMARY KEY (`patient_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

CREATE TABLE IF NOT EXISTS doctor
-- every appointment must have a doctor (or nurse - see above)
(
  doctor_id int not null auto_increment,
  doc_name VARCHAR(50) NOT NULL,
  speciality VARCHAR(100),
  PRIMARY KEY (doctor_id)
);

CREATE TABLE treatment
(
  treatment_id INT NOT NULL AUTO_INCREMENT,
  treatment_fee INT NOT NULL,
  treatment_name VARCHAR(200),
  PRIMARY KEY (treatment_id)
);

CREATE TABLE appointment
-- an appointment must be with a patient
-- appointment_patient_ is redundant.
(
  appointment_id int NOT NULL,
  doctor_id int NOT NULL,
  patient_id int NOT NULL,
  Date int NOT NULL,
  time int,
  PRIMARY KEY (appointment_id),
  CONSTRAINT fk_ap_pa_id FOREIGN KEY (patient_id)
  REFERENCES patient(patient_id),
  CONSTRAINT fk_ap_do_id FOREIGN KEY (doctor_id)
  REFERENCES doctor (doctor_id)
);

CREATE TABLE medical_procedure  -- and not patient_treatment, English is easier.
(
  medical_procedure_id int NOT NULL,
  order_no int NOT NULL,
  patient_id int NOT NULL,
  doctor_id INT NOT NULL,  -- each treatment must have a doctor (at least supervising)
  treatment_id int NOT NULL,
  PRIMARY KEY (medical_procedure_id),
  CONSTRAINT fk_mp_pa_id FOREIGN KEY (patient_id)
  REFERENCES patient(patient_id),
  CONSTRAINT fk_mp_tr_id FOREIGN KEY (treatment_id)
  REFERENCES treatment(treatment_id),
  CONSTRAINT fk_mp_do FOREIGN KEY (doctor_id)
  REFERENCES doctor(doctor_id)
);


Best way to insert error information from catch block in the table for stored procedure? I have stored procedure that process single row transaction. I would like to catch any errors and insert in Error table. Then use nightly process to forward the information from error table to developers. I'm wondering what is the best practice for this situation? Is there any good example on how to handle this? Here is my code and idea on what I have as of now:

CREATE PROCEDURE [dbo].[InsertBuilding]
    @Status BIT = 0,
    @Number CHAR(4) = NULL,
    @Name VARCHAR(50) = NULL,
    @Contact VARCHAR(50) = NULL,
    @Address1 VARCHAR(50) = NULL,
    @Address2 VARCHAR(50) = NULL,
    @City VARCHAR(50) = NULL,
    @State CHAR(2) = NULL,
    @Zip CHAR(5) = NULL,
    @Phone CHAR(10) = NULL,
    @ActionID UNIQUEIDENTIFIER = NULL
AS
    SET NOCOUNT ON
    SET XACT_ABORT ON
    BEGIN
        BEGIN TRY
            INSERT INTO Buildings (
                Status, Number, Name,
                Contact, Address1, Address2,
                City, State, Zip, Phone,
                ActionDt, ActionID
            )
            OUTPUT INSERTED.RecID
            VALUES (
                @Status,@Number,@Name,
                @Contact,@Address1,@Address2,
                @City,@State,@Zip,@PhonePri,
                CURRENT_TIMESTAMP,@ActionID
            )
        END TRY
        BEGIN CATCH
            INSERT INTO dbo.ERROR(
                ErrorNumber,
                ErrorSeverity,
                ErrorState,
                ErrorProcedure,
                ErrorLine,
                ErrorMessage,
                ErrorDateTime
            )VALUES(
                ERROR_NUMBER(),
                ERROR_SEVERITY(),
                ERROR_STATE(),
                ERROR_PROCEDURE(),
                ERROR_LINE(),
                ERROR_MESSAGE(),
                CURRENT_TIMESTAMP()
            )
        END CATCH
    END;

If anyone have any suggestions or example how this can be improved or good way to deliver these errors to developers please let know. Thank you.

The below is what i use to catch errors and send it of to the developers. It generates a mail every time an error occurs

BEGIN CATCH

(SELECT @Servername AS ServerName,

 ERROR_NUMBER() AS ErrorNumber

,ERROR_SEVERITY() AS ErrorSeverity

,ERROR_STATE() AS ErrorState

,ERROR_PROCEDURE() AS ErrorProcedure

,ERROR_MESSAGE() AS ErrorMessage

INTO #ERROR )

/*************************************************************/

/****************** HTML Preparation *************************/

/*************************************************************/

DECLARE @HTML  VARCHAR(MAX),

    @table VARCHAR(MAX)

 SET @HTML = --HTML layout--

            '' +

            '
Error Executing GetAllJobStatuses
' +

            '' +

            '' +

            '
' +

            '
'+

            'Server Name	' +

            'Error Number	' +

            'Error Severity	' +

            'Error State	' +

            'Error Procedure	' +

            'Error Message
';

 SELECT @table = CONVERT(nvarchar(max) ,

(SELECT

 td = ServerName, '',

 td = CAST(ErrorNumber as varchar(30)), '',

 td = CAST(ErrorSeverity as varchar(30)), '',

 td = CAST(ErrorState as varchar(30)), '',

 td = ISNULL(CONVERT(VARCHAR(50),[ErrorProcedure] ),''), '',

 td = ErrorMessage, ''

 FROM

#ERROR

FOR XML PATH(N'tr'), TYPE));

SET @HTML = @HTML + @table + CHAR(10) +

         N'
';

SET @HTML = @HTML + '

End of Report
';

--PRINT @HTML

Declare @MailSubject varchar(100)

SET @MailSubject = 'Error Executing GetAllJobStatuses!! - ' +
DATENAME(weekday, getdate())

IF LEN(@HTML) > 10

EXEC msdb.dbo.sp_send_dbmail

@recipients = 'tester@test.com',

@body_format =  'HTML',

@body= @HTML,

@subject = @MailSubject,

@profile_name = 'SQLDBA'

    --SELECT * FROM #ERROR

 DROP TABLE #ERROR

 END CATCH;


Force standby de-synchronisation for script testing Two databases are configured in a production-standby configuration with Oracle Dataguard.

I want to test a script that supposedly detects whether the dataguard observer is in the status SYNCHRONIZED or not. To do so, I am trying to manually break something to force the synchronization to momentarily fail while I test my script and then fix it.

I already tried stopping the observer but doing so will only cancel the third party status reporting ability of the observer itself which won't affect the databases at all.

A theoretical solution would be to break the network link between the two by disabling the network card. The issue is maybe in this case the observer wouldn't be able to connect to the standby and behave unexpectedly.

Please give me a heads up on where to start here !

Just disable sending redo to the standby on the primary (replace with actual dest number):

SQL> alter system set log_archive_dest_state_2=defer scope=memory;

To re-enable (replace with actual database name):

DGMGRL> enable database orcl_sb


MariaDB GTID current_pos vs slave_pos In a standard master-slave setup using MariaDB (10.3 at this time) and GTID (Global Transaction ID), it is unclear wether to use current_pos or slave_pos.

Reminder from the documentation:

  Using the value current_pos causes the slave to set its position    based on the gtid_current_pos system variable. The slave takes the position given to it by the master server.
  Using the value slave_pos causes the slave to instead use the gtid_slave_pos system variable. With this method, the slave takes into account transactions present    in its own Binary Log.

I think I understand the difference but what is the recommended option?

To the extent that both exist, one might be better for one use case and the other for another use case but the documentation does not clearly state which one should be used in which case...

Which of these options would you use for a very common case of master-slave replication?

EDIT 2019-01-18: Context

The context that led me to ask myself whether I should use current_pos or slave_pos:

I recently upgraded some MariaDB clusters (master-slave setups) from MariaDB 10.1 or 10.2 to MariaDB 10.3 on Debian Stretch, using APT.

In practice, I always proceed like this:

Upgrade slave(s) to 10.3 (using an apt dist-upgrade)
Run mysql_upgrade
Switchover master (using Signal18 Replication-manager)
Upgrade the old master (that became a slave at the previous step)
Switchover master again to return to the original configuration

However, during recent updates, I encountered some failures at Slave Start  after running a mysql_upgrade command. It did not found binlogs using GTID current position.

This happens, I believe, when there has be No transaction on the master since the last master switchover. In this case, switching GTID mode from current_pos to slave_pos fix the problem...

Then, should I definitely use slave_pos? However, even in this case, Replication-Manager forces GTID mode to current_pos after a switchover...

Use current_pos to switchover master. Use slave_pos for a regular replication.

current_pos - last change in GTID domain

slave_pos - last replicated change applied by SQL thread or parallel workers on the replica.

So, imagine your ordinary replica (node2) switched to a master. slave_pos not incrementing, because changes are applied directly without any replication. Then you need to reconfigure cluster and make the new master become replica again.

What you will see on node2?

SHOW global variables like '%pos%'?

you will see, that slave_pos stopped incrementing on the moment of switchover,
current_pos incrementing correctly.

In order node2 to become replica you must use current_pos, after the workload switched to a new master.

Summarizing:

slave_pos

use this option when you don't pretend to write to the replica
you don't want the transaction id part of the GTID to increment on the slave
you are adding a new slave to the cluster and would like to start replicating from the GTID.slave_pos

current_pos

use this when you do write to the slave and want to increment GTID transaction id
when a master need to become a new slave


Speed of Searching Text vs INT/BIGINT - SQL Server 2012 I am building an application that will need to handle extremely high amounts of traffic. The traffic will consist of incoming requests for information from a SQL Server 2012 database. When a request is made, a unique KEY is passed that will be used to identify the account that the information belongs to. This KEY is an alphanumeric string and the table that contains the KEYs can contain millions of rows of data. The length and data type of the KEY can change based on situation.

Example Request

KEY=a123f-5n12d1km9-3cmc32mc32-32cm0429c-243g4453-g43f43
TYPE=123

--The system will look up data for type '123' in the database for account 'a123f-5n12d1km9-3cmc32mc32-32cm0429c-243g4453-g43f43'

The main focus will be on the speed of the query, so I am not concerned too much with database size or memory utilization. I know that searching for integers is a lot more efficient than searching text, so I came up with a way to easily convert the strings into integers. The goal is to make an efficient way to take a string, convert it to an integer, and then perform a quick search to return the results. The problem is that the integers generated by the strings will either take up two BIGINT columns or 4 INT columns.

My question is would it be more efficient to search and compare 4 INT columns or 2 BIGINT columns? Or would searching for the text be faster?

BIGINT Sample Query

SELECT (FIELDS)
FROM KEYS (JOIN .......)
WHERE C1 = xxxxxxxxxxxxxxxxxxxx and  C2 = xxxxxxxxxxxxxxxxxxxx

INT Sample Query

SELECT (FIELDS)
FROM KEYS (JOIN .......)
WHERE C1 = xxxxxxxxxx and  C2 = xxxxxxxxxx and  C3 = xxxxxxxxxx and  C4 = xxxxxxxxxx

Text Sample Query

SELECT (FIELDS)
FROM KEYS (JOIN ........)
WHERE KEY = 'a123f-5n12d1km9-3cmc32mc32-32cm0429c-243g4453-g43f43'

(NOTE: All columns are either type INT or BIGINT respectively)

As other suggested, sometimes the best choice is to test it out yourself. I hate answering my own question, so if someone can provide a better answer then I might chose it if it better explains my results.

It turns out there is not UBIGINT64 in SQL, so I created the table as I described as follows and used decimal instead of BigInt:

CREATE TABLE [dbo].[KEYS](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [KEY] [nvarchar](max) NOT NULL,
    [BI1] [decimal](28, 0) NOT NULL,
    [BI2] [decimal](28, 0) NOT NULL,
    [i1] [int] NOT NULL,
    [i2] [int] NOT NULL,
    [i3] [int] NOT NULL,
    [i4] [int] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

I then wrote a script to populate the table with approximately 2.25 million rows of data and setup the following loop to see how long each one would take.

declare @x integer
set @x = 0
while @x < 100
begin
    --QUERIES WERE INSERTED HERE
    set @x = @x + 1
end

I then plugged the following three queries into the loop and recorded the results for 50 loops and 100 loops.

select * from IntTypes where i1 = 64003101 and i2 = 64003107 and i3 = 64003144 and i4 = 640031031

50 Loops: 9 seconds
100 Loops: 18 seconds

select * from IntTypes where BI1 = 15284527576400310788 and BI2 = 16825458760271544958

50 Loops: 11 seconds
100 Loops: 23 seconds

select * from IntTypes where [KEY] = 'a123f-5n12d1km9-3cmc32mc32-32cm0429c-243g4453-g43f43'

50 Loops: 54 seconds
100 Loops: 1 minute 48 seconds

Perhaps the results would be different if I did use a BIGINT instead of a DECIMAL, but comparing 4 integer columns seems to outperform comparing the two decimal columns. Both of those completely destroyed the text search results timing, but overall it looks like I will be using integer columns.

So to sum it up and answer my own question (sort of), it is faster to search 4 integer columns than searching two decimal columns.


Right Distribution of Databases amongst Multiple Instances So it's obvious that many times you'll have multiple instances (even if on the same server) to have a logical separation of databases for security purposes.

But when does it make sense on the performance side to separate databases amongst multiple instances?  For instance, say you have 200 databases: 100 of them are heavy OLTP and 100 of them are mostly just OLAP.

What is wise in this scenario?  What is the general rule of thumb?  Is there such a thing as instance performance tuning?

I don't think you're going to come up with a perfect formula. It will be almost impossible to predict who is going to consume most of your resources and at what times. What you will likely need to do is come up with some predictable division (based on information we don't know, like client A is 24/7 and demanding but client B is 9-5 only). You might start with 50 OLTP and 50 OLAP on one server, and the other 50/50 on the other server. The nice thing about database-level separation is that if one of those clients turns out to be really busy, you can move databases around relatively easy to provide better balance - either trading one busy database for 10 quieter databases on the other instance, or perhaps introducing a 3rd instance.

In my previous job we had a system where 500 clients had databases on a single instance. In order to lighten the load and mitigate unpredictable performance spikes, we pushed the reporting stuff that didn't need to be real-time to a secondary server. We would perform a full backup and restore each night, restore with recovery, and mark the database as read only, during a window where they were blocked out of the system. Then they could run their reports all day long with absolutely no locking and they were quite happy.


How to upgrade MYSQL from 5.6.12 to 5.6.21? I have been a MSSQL DBA and don't have much knowledge about MYSQL.
Does MYSQL also have patches just like MSSQL?
I need to do MySQL version  upgrade from 5.6.12 to  5.6.21 but I do not have much information at hand.
A step by step guide will be the best but any information will be helpful.

First take a backup of everything using your normal backup software.

Take a dump (mysqldump using the --all-databases) option.

Export:

mysqldump -uroot -p --all-databases > alldb.sql
mysqldump -uroot -p --opt --all-databases > alldb.sql
mysqldump -uroot -p --all-databases --skip-lock-tables > alldb.sql

I cordially invite you to look at the docco for the options that suit
you best - or just run mysqldump with the --help option only.

Uninstall your MySQL 5.6.12. (I'm assuming yum or apt - redhat or Ubuntu - again
check your docco)

Install your MySQL 5.6.21.

Check the release notes to ensure that there are no parameters
that have changed - given that your migrating between minor
versions, this should not be an issue.

Perform a restore using mysql and the dump.

Import:

mysql -u root -p < alldb.sql

Et voilà!


Syntax error using preparedstatements with postgresql I have a Java application connecting to a PostgreSQL database.

Using straightforward code like:

        ps = con.prepareStatement(sql);
        if (dataTypes != null && dataTypes.size() > 0) {
            for (int i = 0; i < dataTypes.size(); ++i) {
                if (dataTypes.get(i) == DataTypes.NUMERIC)
                    ps.setLong((i + 1), Long.parseLong(values.get(i)));
                else
                    ps.setString((i + 1), values.get(i));
            }
        }
        rs = ps.executeQuery();

and with a query like

select ? from dummy

it just works fine. In the postgres logs what shows is:

2015-06-13 12:48:30 EEST [28294-3] xx LOG:  execute <unnamed>: select $1 from dummy
2015-06-13 12:48:30 EEST [28294-4] xx DETAIL:  parameters: $1 = '0'

But with a query like:

select to_char(now()+interval ? day, 'YYYYMMDD') from dummy

I get a syntax error:

Cause [org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"

and in the postgres logs:

2015-06-13 13:17:39 EEST [29311-3] xxx ERROR:  syntax error at or near "$1" at character 31
2015-06-13 13:17:39 EEST [29311-4] xxx STATEMENT:  select to_char(now()+interval $1 day, 'YYYYMMDD') from dummy

but in pgadmin something like

select to_char(now()+interval '0' day, 'YYYYMMDD') from dummy

works just fine without any error.

I just can't figure out what the problem is. Why does one of the queries work but the other one doesn't?

My PostgreSQL version is 9.4

The common solution is to express this as:

interval '1 day' * ?

with ? as a placeholder for a numeric value (possibly with a fractional part).

The syntax tried in the question is rejected because for the SQL grammar, the entire expression interval '1 day' is a constant. It cannot be changed by injecting a placeholder into it, just like we couldn't write 3.? and pass .1415926 as an external parameter to form the PI number.


can i use mysql views to update and delete the records in mysql I am able to create a view for retrieving the records based on some criteria, but does the view only support for retrieving the records or will it support for delete and update the records in the tables.

create view onlinetestview as
    select * from onlinetest where experience = '1-2 Years'

I used the above view its working fine but when i am trying to create a view for updating the table record its giving me an error:

create view onlinetestview2 as
    update  onlinetest set experience='1-2Years' where experience='1-2 Years';

The error is : Lookup Error - MySQL Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'update  onlinetest set experience='1-2Years' where experience='1-2 Years'' at line 1

Yes, but with caveats. Some aspect of your view probably breaks one or more of these conditions:

http://dev.mysql.com/doc/refman/5.7/en/view-updatability.html

  For a view to be updatable, there must be a one-to-one relationship
  between the rows in the view and the rows in the underlying table.
  There are also certain other constructs that make a view nonupdatable.
  To be more specific, a view is not updatable if it contains any of the
  following:

  Aggregate functions (SUM(), MIN(), MAX(), COUNT(), and so forth)
  DISTINCT
  GROUP BY
  HAVING
  UNION or UNION ALL
  Subquery in the select list (fails for INSERT, okay for UPDATE,    DELETE)
  Certain joins (see additional join discussion later in this section)
  Reference to nonupdatable view in the FROM clause
  Subquery in the WHERE clause that refers to a table in the FROM    clause
  Refers only to literal values (in this case, there is no underlying    table to update)
  ALGORITHM = TEMPTABLE (use of a temporary table always makes a view    nonupdatable)
  Multiple references to any column of a base table (fails for INSERT,    okay for UPDATE, DELETE)

If you post the view definition we might be able to tell you which one


Can we use VMDK for FCI on SQL Servers? Can I share a VMDK among 2 nodes for an active passive setup for a SQL Server Failover Cluster setup?

Most of the articles I see mention to use RDM's, however we use Veeam backup for the environment and it doesn't support RDM's.

No, you cannot reliably use VMDK files for shared storage in VMware-virtualized Windows Server Failover Clustering.  RDMs (Raw device mappings) are the only supported method of provisioning disks in this scenario. Use RDMs in physical compatibility mode (pass-through RDM). You cannot use virtual disks or RDMs in virtual compatibility mode (non-pass-through RDM) for shared storage.  You can use VMDKs for the non-shared drives.  See Setup for Failover Clustering and Microsoft Cluster Service from VMware for more details.

Since you have Veeam, you should use the SQL Server backup tools included with that package; see Veeam's help center for vSphere backup and restore for more details.


How do I intentionally fragment a SQL Server Index? I would like to create bad indexes conditions intentionally on a SQL Server 2017 test database I have, just to better understand these maintenance scripts? SQL Server Index and Statistics Maintenance

Is there a fast/automatic way to compromise index integrity or increase index fragmentation? Do you know any useful resource I can look at to achieve this?

One quick way I can imagine is creating a table with UNIQUEIDENTIFIER as a primary key and inserting lots of random values. This could be achieved using this script:

CREATE TABLE dbo.Tests (Id UNIQUEIDENTIFIER PRIMARY KEY);
GO
INSERT INTO dbo.Tests (Id)
WITH x AS (SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) v(n))
SELECT NEWID()
FROM x AS x1, x AS x2, x AS x3, x AS x4, x AS x5, x AS x6;

This will generate million rows.

Knowing that NEWID() does not guarantee any ordering, SQL Server will have to insert into random spots in table - that's going to fragment the primary key.


MySQL 8 - user variables within expressions is deprecated [UDF calls with lot of parameters] I have this:

SELECT
   @foo1 := UDF1(0, a, b, c, d) AS Foo1,
   @foo2 := UDF1(1, a, b, c, d) AS Foo2,
   @foo3 := UDF1(2, a, b, c, d) AS Foo3,
   @foo4 := UDF1(3, a, b, c, d) AS Foo4,
   @foo5 := UDF2( @foo1, @foo2, @foo3, @foo4) AS Foo5,
   @foo6 := UDF3( @foo1, @foo2, @foo3, @foo4) AS Foo6,
   @foo8 := UDF4( @foo5, @foo7, x, y, z) AS Foo8
FROM MyTable;

As you can see it's quite complicated and a, b, c, d, x, y and z are field names which are quite long [The names express their functionality].

I receive now this error message on MySQL 8.0.20:

  X Setting user variables within expressions is deprecated and will be
  removed in a future release. Consider alternatives: 'SET
  variable=expression, ...', or 'SELECT expression(s) INTO
  variables(s)'.

OK, this is the wrong place to discuss if it makes sense that @var := value is deprecated, so I have to move on and I want to assure that the program doesn't stop working if the next MySQL update is installed.

I could solve it like this:

SELECT
   UDF1(0, a, b, c, d) AS Foo1,
   UDF1(1, a, b, c, d) AS Foo2,
   UDF1(2, a, b, c, d) AS Foo3,
   UDF1(3, a, b, c, d) AS Foo4,
   UDF2(UDF1(0, a, b, c, d), UDF1(1, a, b, c, d), UDF1(2, a, b, c, d), UDF1(3, a, b, c, d)) AS Foo5,
   UDF3(UDF1(0, a, b, c, d), UDF1(1, a, b, c, d), UDF1(2, a, b, c, d), UDF1(3, a, b, c, d) ) AS Foo5,
   UDF3(UDF2(UDF1(0, a, b, c, d) , UDF1(1, a, b, c, d) , UDF1(2, a, b, c, d) , UDF1(3, a, b, c, d) ) ,
        UDF3(UDF1(0, a, b, c, d), UDF1(1, a, b, c, d), UDF1(2, a, b, c, d), UDF1(3, a, b, c, d) ), x, y, z) AS Foo6
FROM MyTable;

Honestly, doesn't this hurt and, what I find worst, it becomes so unreadable and changing any call I have to maintain many times --> buggy.

Also, in the current version the length of the SELECT grows from 2'334 bytes to 3'504 bytes.

I am trying to work with a temporary table but to fill the table is a quite long and [useless] complicated SELECT using LEFT JOIN as @foo5 depends on @foo1-4 and @foo6 depends on @foo5.

This works, but I am wondering if there may be another solution I am not capable to see. The suggested SELECT expression(s) INTO variables(s) I don't understand how this should help in my case.

As I wrote in the beginning, I don't understand why this feature is deprecated as it apparently can solve lot of troubles and makes complicated SELECT statements simpler...

Any suggestions?

WITH
cte1 AS ( SELECT UDF1(0, a, b, c, d) AS Foo1,
                 UDF1(1, a, b, c, d) AS Foo2,
                 UDF1(2, a, b, c, d) AS Foo3,
                 UDF1(3, a, b, c, d) AS Foo4,
                 x, y, z
          FROM MyTable ),
cte2 AS ( SELECT Foo1, Foo2, Foo3, Foo4, x, y, z,
                 UDF2(Foo1, Foo2, Foo3, Foo4) AS Foo5,
                 UDF3(Foo1, Foo2, Foo3, Foo4) AS Foo6
          FROM cte1 ),
SELECT Foo1, Foo2, Foo3, Foo4, Foo5, Foo6,
       UDF4(Foo5, Foo6, x, y, z ) AS Foo8
FROM cte2;


How do I add a time dimension to this forecasting database without having to duplicate every data point @ each reforecast? I have a database designed to facilitate revenue forecasting. Revenue forecasting consists of tracking Orders which are recognized into Revenue at multiple points in time (each, a revenueDate). There are two tables (keys in italic):

Order: id, orderDate
Revenue: ForeignKey(Order.id), revenueDate, amount

At any point in time, querying the Revenue table will give me the total revenue for each revenueDate.

I'd like to add a time component so that I can track what the revenue forecast looked like at any given point of time. Something like the following:

Order: id, forecastDate, orderDate
Revenue: ForeignKey(Order.id), ForeignKey(Order.forecastDate), revenueDate, amount

Doing the table this way would allow me to query (forecastDate, revenueDate) to get my answer. Unfortunately, it would also require me to duplicate all data points for every reforecast, even those that don't change.

Is there a more efficient way for me to design my tables so that each new forecast only requires appending changes or the changed data points, while still allowing me to view a forecast at any historical point in time?

===Update===

I actually don't expect there to be a definitive answer on this, as it seems like luminaries in the field like Snodgrass and Darwen have fundamental disagreements about how best to model time within a database (see ISO's rejection of temporal elements back in 1996).

Snapshotting vs. Running Sums

Any approach to point-in-time reporting is effectively equivalent to snapshotting or calculating a running sum.  Your options are variations on:

Calculate a running sum from the beginning of time, or some periodicaly checkpointed position.  This requires you to record changes rather than totals.
Calculate periodic snapshots and do your historical statiscs based on the snapshots.
Hybrid running-sum with periodic snapshots to baseline positions and reduce the data set over which the running sum needs to be calculated.

This is essentially a speed for space tradeoff, and temporal databases are really just abstractions on a data structure that is semantically equivalent to one of the schemes described above.

If you want speed, calculate and persist periodic snapshots of your position.  This will use more space, but if your DBMS platform supports partitioning then the snapshots can be managed and disposed quite efficiently.  The snapshot date forms the partition key, so any queries that specify dates will give the optimiser enough information to ignore partitions that are not relevant to the query.

Frequent snapshots will use lots of disk space, so you trade off granularity against disk space usage.

If you want to save space then store changes and calculate running sums for your as-at positions.  You can also use periodic (though less frequent) snapshots in combination with the deltas for a period to optimise the query by starting with balance figures from the appropriate snapshot and adding the deltas to your as-at date.  This technique is commonly used with accounting systems such as Oracle Financials.

Hybrid approaches

Essentially you have a continuum from a pure running sum to a pure snapshot.  Snapshotting is faster to query, and can be used to speed up running sum calculations by allowing you to start the running sum calculation at the snapshot date.  A hybrid approach is more complex as you have to implement both mechanisms, but it does allow you to tune your speed/disk usage tradeoff to whatever is most appropriate to your application.

Some thoughts on what might be applicable when

It's far easier to retrofit snapshots to a base transactional model than to go the other way around.  However, some models - things where you have time-dependent state - only make sense when viewed as a snapshot that shows the state at a given point in time.  An example of this is aged debt reporting where you want to see the invoices with money that's been owing for more than (for example) 180 days.  If you want to retain a reconcilable record of aged debt positions then you may wish to snapshot this periodically.

In the first instance a transactional fact table with the base movements (deltas) gives you the raw data from which you can calculate your snapshotted positions.  It's best to have that available if at all possible, unless recording the data in that format is made infeasible by some limitation of the upstream data source.

I've done an aged debt reporting system and a claims reporting system based on snapshots before.  The aged debt report was an operational report run at the beginning of each week, and the requirement for the claims snapshot table was for a monthly report where the business wished to see counts and outstanding values of claims that were open at the time.

Some things (e.g. claim reserving) are done on a periodic basis, so (for example) ultimates and IBNR1 are only calculated on a monthly or quarterly basis and it only makes sense to report those on a periodic basis.  In this case, snapshotting the figures makes sense due to the nature of the underlying process that generates the data.

1Ultimates are insurance-speak for forecasts of the total claims expected on policies incepting in a given year.  IBNR (Incurred but not Reported) is an estimate of the figures on claims that have not occurred or been reported yet.


localhost:port_number/em (what is my port number)? I am new to SQL databases. I have started learning SQL databases from SQLZOO, where they told me to use Oracle. I have installed Oracle Database 11g on Windows 10 with the help of this video.
I have created a table using SQL*Plus, so I am assuming that I have installed Oracle correctly.

Now when I am trying to go to http://localhost:1158/em from my Google Chrome, it is saying:

  The site cannot be reached

I have searched and found that this 1158 is supposed to be my port number. I have tried many ways to find this port number for the last 3 days to no avail.

Now what should I do?
Have I missed installing something?

the default port for 11g OEM is 1158. If it's not set to the default in your installation, then the value will be in portlist.ini. You can also find the full URL in the emd.properties file:

http://docs.oracle.com/cd/E25054_01/server.1111/e10897/em_manage.htm#BABHJAGE

  In your Web browser, enter the following URL:

  https://hostname:portnumber/em
  For example, if you installed the database on a host computer named comp42.example.com, and the installer indicated that your Enterprise Manager Console HTTP port number is 1158, then enter the following URL:

  https://comp42.example.com:1158/em
  If you have recently installed Oracle Database, you can determine the port number for Database Control on Linux and UNIX systems by viewing the $ORACLE_HOME/install/portlist.ini file.

  Otherwise, you can determine the port number for Database Control by searching for REPOSITORY_URL in the $ORACLE_HOME/Oracle_sid/sysman/config/emd.properties file, where Oracle_sid is the system identifier (SID) for your database instance.

$ORACLE_HOME is the home directory you chose when installing the database.


Error: "temporary file size exceeds temp_file_limit" My database is suddenly hanging on some queries that join many tables and I see:

temporary file size exceeds temp_file_limit (1025563kB)

A sample query and query plan can be seen here:

https://pastebin.com/wug86Wrr

http://www.sharecsv.com/s/a6333a7f610e049dd81ebcfc19a4c02f/temp_file_limit_exceeded.csv

This query normally takes less than 100ms but hangs when hit temporary file size limit.

When I run:

SELECT temp_files AS "Temporary files", temp_bytes AS "Size of temporary files"
FROM pg_stat_database db;

I see:

Temporary files  Size of temporary files
---
22550            10651900248

How do I resolve this?

I was able to enable more of the problem. Say a user lists they can speak 2 languages - the query runs ok. Then they edit their profile to say they know 20 languages. The query ends up exceeding the temp_file_limit and hanging.

You are joining around 40 tables to retrieve around 200 columns. I did not dig in but, typically, such a query is a misunderstanding. Either way, no wonder you amass a lot of temporary files.

Also note that all temp files of the same session count against the limit. Bound cursors can become a problem that way. Or multiple large queries in the same transaction.

By default the parameter temp_file_limit is set to -1, which means "no limit". So either sanitize your query or remove or extend the limit. (Only superusers can do that.)

If you cannot change either, setting more work_mem might help to keep more operations in RAM, so less temp space is needed.

Don't confuse this setting with temp_buffers, which is mostly unrelated and sets the amount of RAM that can be used for temporary objects (like in CREATE TEMP TABLE ...)

And you did not bother to mention your version of Postgres. Maybe outdated?


SQL Trace for compatibility check? My idea is to trace a few days all statements (scripted the replay template) on a SQL Server 2008 r2 and replay those against a deployed version of the db on a newer version (SQL Server 2014).

Is a re-playable trace file suitable to figure out server version compatibility issues? Does this scenario make sense?

How can one realize problems? How are errors displayed when replaying queries that have issues?

Before a replay, I would:

Run the Upgrade Advisor and Best Practices Analyzer
Run a trace looking for deprecation events
Look for some of these issues; the above methods are unlikely to find all of them.

Note that none of the above, nor replaying the trace, are guaranteed to find all compatibility issues (consider the case where a stored procedure will only have a problem with very specific parameters are used, and those don't happen to be used during your trace).

And when you replay the trace, you'll have to parse the output file(s) or table(s) yourself, and perhaps even perform diffs to see any cases where there are errors on the new version that didn't happen on the old version. The replay doesn't produce a nice "here's what worked differently from the original activity" report. You can do this by simply performing a join against .

Here is a quick example I whipped up on my own system - you need to add the Error column to your trace, and note that I am absolutely not advocating Profiler for this, it was just the easiest way to demonstrate. First, on 2014, create a database using 100 compatibility level:

CREATE DATABASE Compat100;
GO
ALTER DATABASE Compat100 SET COMPATIBILITY_LEVEL = 100;
GO

Now, run these two batches:

USE Compat100;
GO
DECLARE @x TABLE(n DATETIME2, x AS CONVERT(CHAR(10), n)); -- not style 121
INSERT @x SELECT GETDATE();
-- error:
SELECT CONVERT(INT, LEFT(x,4)) FROM @x;
GO

USE tempdb;
GO
DECLARE @x TABLE(n DATETIME2, x AS CONVERT(CHAR(10), n)); -- style 121
INSERT @x SELECT GETDATE();
-- not an error:
SELECT CONVERT(INT, LEFT(x,4)) FROM @x;

A trace will yield these:

(Again, do not use Profiler for the actual work, this was just to quickly illustrate that a trace can capture error conditions, and that if you had these two batches run identically on two different servers, you should easily be able to join on TextData equality and Error inequality. And yes, I do realize that this example works the opposite way - the error happens on 2008 but works on 2014, but hopefully it still demonstrates the approach.)

Also see this tip.


What is the purpose of BACKUP WITH NAME? I looked at the Microsoft documentation (http://technet.microsoft.com/en-us/library/ms186865.aspx) and see that there is a WITH NAME option for backups.  But it's not the name of the file (I specify that with TO DISK).  And if I don't specify it, the name is left blank.

So what is the purpose of specifying WITH NAME?  What benefits do I get?

This merely allows you to look up a backup by name, using:

SELECT * FROM msdb.dbo.backupset WHERE [name] = N'the_name_you_used';

This can be useful as a kind of tagging mechanism, e.g. if you specify backups for different things (such as restoring to dev vs. initiating log shipping vs. shipping off-site). Much easier than relying solely on the file name (which can be changed in Windows after the backup is complete), or the file number when you've appended multiple backups to the same file.


Migrating MySQL to new server We have a MySQL 5.1 instance running on Windows Server 2008 R2 x64 (Server A) replicated to another MySQL 5.1 instance on Windows Server 2003 R2 (Server B).

We have a new MySQL 5.5 instance on another Windows Server 2008 R2 x64 machine (Server C).

I want to replace Server A with Server C.

My plan is to set up Server C as another slave to Server A by 'cloning' Server B like this:

http://www.redips.net/mysql/add-new-slave/.

After that I plan on doing this:

http://blesseddlo.wordpress.com/2011/06/03/convert-mysql-slave-to-master/.

After that I want to change Server B's master to Server C.

Is this a viable approach? Are there caveats? May there be any compatibility issues when setting a MySQL 5.5 instance as slave to a MySQL 5.1 instance?

I can't just stop everything and use mysqldump since there can be no downtime and we have ~25 GB of data that must be transferred over a Gigabit LAN.

8 months later, but here's the answer:

No problem in having a MySQL 5.5 slave to a 5.1 master. There is a problem with having it the other way around -- I've seen it fail.

Your approach is very viable and is being done frequently. You have both B and C replication from A. Then, at some stage you:

freeze A (via FLUSH TABLES WITH READ LOCK)
Verify both slaves are caught up with A's last statement.
Take A down
Change C to replicate from B, using B's SHOW MASTER STATUS
Tell your application B is the new master
Celebrate with beer

This does require a minimal downtime of a few seconds (and up to a minute) -- it mostly depends on your skills and confidence.


Job management: How to get the next running date by querying MSDB Table sysjobschedules contains 2 columns next_run_date and next_run_time. With the help of sysschedules and column enable it should be easy to determine the next running date/time of a specific job.
However, when you change a schedule, sysjobschedules is only refreshed 20 minutes after alteration by the Sql Server Agent.
Table sysjobactivity contains a datetime next_schedule_run_date which is updated in real time.
But I've failed to see how to link sysjobactivity to sysschedules since it doesn't contain a column to identify the id of the schedule.
Can someone help?

You can use the (documented) stored procedure sp_help_job to get the next execution time, however progratically capturing the results can be difficult due to the limitation around nested INSERT...EXEC calls. As an alternative, many folks use the (undocumented) stored procedure xp_sqlagent_enum_jobs which captures much of the same info.
Like you, I prefer to capture the data from the base tables directly using an alternate approach that I wrote about in a blog post, and have published a T-SQL function on GitHub as part of my DBA Database.
The table-valued function (TVF) definition itself is just a single SELECT statement that takes a job name, and returns a bunch of stuff, including last & next run time, and whether the job is currently running:
SELECT TOP 1
    IsRunning = CASE WHEN ja.job_id IS NOT NULL AND ja.stop_execution_date IS NULL THEN 1 ELSE 0 END,
    LastRunTime = ja.start_execution_date,
    NextRunTime = ja.next_scheduled_run_date,
    LastJobStep = js.step_name,
    JobOutcome = CASE
                    WHEN ja.job_id IS NOT NULL AND ja.stop_execution_date IS NULL THEN 'Running'
                    WHEN run_status = 0 THEN 'Failed'
                    WHEN run_status = 1 THEN 'Succeeded'
                    WHEN run_status = 2 THEN 'Retry'
                    WHEN run_status = 3 THEN 'Cancelled'
                END
FROM msdb.dbo.sysjobs j
LEFT JOIN msdb.dbo.sysjobactivity ja
    ON ja.job_id = j.job_id
    AND ja.run_requested_date IS NOT NULL
    AND ja.start_execution_date IS NOT NULL
LEFT JOIN msdb.dbo.sysjobsteps js
    ON js.job_id = ja.job_id
    AND js.step_id = ja.last_executed_step_id
LEFT JOIN msdb.dbo.sysjobhistory jh
    ON jh.job_id = j.job_id
    AND jh.instance_id = ja.job_history_id
WHERE j.name = @JobName
ORDER BY ja.start_execution_date DESC;

Because it’s a TVF, you can pass a value directly to it and get a result set back:
SELECT *
FROM dbo.SqlAgentJob_GetStatus('Test Job');

Or APPLY it across a table for a set of results (in this case I’m getting all job in job_category 10):
SELECT sts.*
FROM msdb.dbo.sysjobs j
CROSS APPLY dbo.SqlAgentJob_GetStatus(j.name) sts
WHERE j.category_id = 10;

Or get a list of all SQL Agent jobs that are currently running:
SELECT sts.*
FROM msdb.dbo.sysjobs j
CROSS APPLY dbo.SqlAgentJob_GetStatus(j.name) sts
WHERE sts.IsRunning = 1;

What about sysschedules?
You'll notice that my code doesn't use sysschedules, because it just isn't necessary.
If you wanted to make use of sysschedules in code, you would do something like this, joining from sysjobs to sysjobschedules to sysschedules. Keep in mind that jobs can have zero to many schedules.
SELECT JobName      = j.name,
       ScheduleName = s.name,
       ...other columns...
FROM msdb.dbo.sysjobs AS j
LEFT JOIN msdb.dbo.sysjobschedules AS js
     ON j.job_id = js.job_id
LEFT JOIN msdb.dbo.sysschedules AS s
     ON js.schedule_id = s.schedule_id;

To determine the "next run date/time" you would need to essentially identify active schedules for the job, and take the next run date out of multiple schedules. sysjobactivity does that heavy lifting for you by computing the next run date (you can think of it as the "actual next execution")--but it simply doesn't keep track of which schedule is responsible for that execution. If that matters to you, you'll need to hunt that down yourself by looking at what schedules are active for that job.


Looking for a tool for managing complex powershell automation processes with multiple scripts Sysadmin here,
I'm wondering what tools you all use to manage large scale automation tasks with powershell. I currently have several powershell scripts that are regularly used, but do not interact or call each other. I'm looking for a GUI interface to help make sense of it, and grab variables as outputs from one script into another.
I can do all this through powershell alone, but wondering if there's a more visual way to set up the complex interactions between scripts.
An example of a process that would need to be completed:

SQL backup taken on hosted client server
Backup uploaded to FTP Site
Backup loaded to data scrubbing server
Backup restored to local SQL Instance
Client sensitive info scrubbed
Backup taken and uploaded to NAS
NAS backup restored to local support environment
Database modified to add users for WinAuth
Various server config on support environment for our software

I propose you two things:

With a GUI: https://www.minionware.net/
Without a GUI: https://dbatools.io/


all rows from left table with sum of amount from right table I'm trying to return a list of customers and the sum of their transactions in the last week,   but have the query return customers who didn't wager at all. I am trying to do this with a left join, but the query only returns a row if a customer did wager, which is not how I expect the left join to work.   What can I do to make this work the way I want it?

select i.accountnumber, i.firstname, i.lastname, sum(a.amount)
from accountinformaton i
left join accountactivity a
    on i.accountnumber = a.accountnumber
where a.transactiontype = 'Bet'
  and a.transactiondate >='2015-07-31'
group by i.accountnumber, i.firstname, i.lastname

the 'accountinformation' table has thousands of rows,  the query above returns a few hundred.  I was expecting to be able to return all rows from 'accountinformation' and have the sum be null on accounts that didn't wager.

Figured it out myself.  My where clause is telling sql to only show rows that are 'Bet' and have that date criteria, so without me realising it I'm telling sql to show me rows that had bets.

Here is my alternative query...

select i.accountnumber, i.firstname, i.lastname, sum(isnull(amount,0))
from accountinformation i
left join accountactivity a
    on i.accountnumber = a.accountnumber
   and a.transactiondate >='2015-07-31'
   and a.transactiontype = 'Bet'
group by i.accountnumber, i.firstname, i.lastname

Putting the conditions in the join clause instead of the where clause makes the left join behave as expected.


Function in same Session become increasingly slow when run multiple times concurrently PostgreSQL 12.2 (Ubuntu 12.2-2.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit
Client is Data Grip and same behavior from my report server which uses the driver that comes with Jaspersoft
I am running the same function for reporting with different parameters multiple times under the same session. Also does the same thing using the same parameters.
These are being one run one after the other and not at the same time.
The result only has a few rows but does read from quite a few tables, no writes.
It is just table joins and selects no inputs or updates to the tables themselves (would like to be able to post query but can't for security reasons).
After I run the function a few times it starts to slow down and gets to an unacceptable level. For example one of the functions goes from 1 second to over 90+ seconds (that is where I stopped testing).
Troubleshooting:
I have gone to the server and terminated the session and after that it starts to run normally for a few runs.
The standard report does use a temp table but I have removed that for testing. I have run the following after it starts having the issue to try and fix the issue.
VACUUM all touched tables; -- I know this should not be required as there are no major changes to these tables but was trying pretty much anything.
Language plpgsql.
SET SESSION AUTHORIZATION DEFAULT;
RESET ALL;
DEALLOCATE ALL;
CLOSE ALL;
UNLISTEN *;
SELECT pg_advisory_unlock_all();
DISCARD PLANS;
DISCARD SEQUENCES;
DISCARD TEMP;

There does not seem to be a good way to terminate idle connections without a separate application or script run through a job.
Further data point: when looking at the execution plans with auto_explain, I see plans with $1 in them from the sixth execution on.

Since the problem are bad generic plans (symptom: execution becomes slower from the sixth execution on) and you are using PostgreSQL v12, the solution is simple:
ALTER FUNCTION xy SET plan_cache_mode = force_custom_plan;


How should an object that can reference an object of multiple types be modeled in a relational schema? Apologies for the obscure question, it may make more sense with a concrete example:

In my application, I may create a portfolio that contains a set of projects. However, I may also add a 'sub-portfolio' nested within a portfolio (which then may contain projects). Currently, I have my schema drawn out the following way:

+------------+    +-----------------------+    +------------+
| Portfolios |    | Portfolio_project_map |    | Projects   |
+------------+    +-----------------------+    +------------+
|   port_id  |    |   parent_id           |    |   proj_id  |
+------------+    +-----------------------+    +------------+
|            |    |   child_id            |    |            |
                  +-----------------------+
                  |   child_is_portfolio  |
                  +-----------------------+

This seems extremely ugly. What is the proper way of modeling such a relationship in a relational database? Or is there really not one, and such details should be closely enforced in the business logic? Is it even possible to specify foreign key constraints on the Portfolio_project_map such that child_id references either port_id or proj_id?

Also, what exactly is such a problem called? I found 'one-to-either' referenced in a couple places, but I have a feeling that's not completely correct?

If my memory serves  me well, 'One to either' is called Polymorphic Association. Usually, it can be resolved by using a common parent table (index definitions are skipped for simplicity):

PortfolioParent (id int not null PRIMARY KEY,
portfolio_type char(1) not null,
CONSTRAINT UQ_PORTFOLIO_PARENT UNIQUE (id,portfolio_type),
-- unique constraint may seem redundant, but it's required so other tables can refer to
-- (id,portfolio_type)
CONSTRAINT CHK_PORTFOLIO_PARENT_TYPE CHECK portfolio_type IN ('P','S')) --'P' - portfolio,'S'-subportfolio

);

Portfolio(id int not null PRIMARY KEY,
parent_id int not null,
portfolio_type char(1) not null,
--portfolio attributes
CONSTRAINT CHK_PORTFOLIO_TYPE CHECK (portfolio_type ='P'),
CONSRTRAINT FK_PORTFOLIO_PORT_PARENT FOREIGN KEY (parent_id,portfolio_type)
  REFERENCES PortfolioParent(id,portfolio_type));

SubPortfolio (id int not null PRIMARY KEY,
parent_id int not null,
portfolio_type char(1) not null,
-- sub-portfolio attributes, portfolio_id
CONSTRAINT CHK_SUBPORTFOLIO_TYPE CHECK (portfolio_type ='S'),
CONSRTRAINT FK_SUBPORTFOLIO_PORT_PARENT FOREIGN KEY (parent_id,portfolio_type)
   REFERENCES  PortfolioParent(id,portfolio_type));

Project(id int not null PRIMARY KEY,
parent_id int not null,
--project attributes
CONSTRAINT FK_Project_PARENT FOREIGN KEY(parent_id) REFERENCES PortfolioParent(id));


If A -> B is augmented with C, is AC -> B legal? Given the augmentation axiom and the decomposition axioms of functional dependencies and a relation of R(A,B,C) with an FD of:
A -> B

I can augment A -> B with C to get AC -> BC.

Can I then decompose AC -> BC to get AC -> B?

Community wiki answer:

Yes. You can decompose to get AC -> B and AC -> C.


In RDS digest function is undefined after creating pgcrypto extension On an AWS RDS instance, we have run

CREATE EXTENSION pgcrypto;

The extension gets created.  Both queries:

SELECT digest('a', 'sha256') FROM table_name;
SELECT public.digest('a', 'sha256') FROM table_name;

Gives the error function public.digest(unknown, unknown) does not exist

My coworker discovered the issue, in case anyone is having a similar problem, the issue was that pgcrypto had been installed into a schema that was not public.  So the query for reported installed extensions said it was installed, installing if not exists did not change its schema or install it to all schemas.  The final solution was to remove it from all schemas and install it directly in the public schema.


How does the disk and file share witness work? Can somebody provide an in depth explanation to how the disk witness and file share witness assign a vote to a node?

There is a good article on how cluster quorum works in Windows Failover Clustering here.

to summarise, for File Share or Cloud Witnesses, a witness log file is maintained with state information about the cluster, witnesses and quorum voting. Each node has a cluster service running that periodically checks in with the other nodes and the witnesses (depending on dynamic voting, node weighting etc) to validate the state and health of the cluster. If a node cannot communicate with other nodes, it will then verify connectivity with the witness, assess the witness log (if possible) and proceed from there with either taking control of the cluster or not.

For example, assume we have two nodes, Node A and Node B, and a File Share Witness (Witness A), with Node A the primary node in the cluster. If Node B, during a periodic health check, cannot communicate with Node A because a switch went down then it will try to verify cluster health by contacting the cluster witness (Witness A) and reviewing the witness log to determine health state.

If Witness A can still communicate with both nodes because it has a different path to Node A than Node B does, this information will be in the witness log and Node B will not try to assume the role of the primary node. Node A will also see this information in the witness log and continue normal operations.

On the other hand, if Witness A has also lost communication with Node A, this information will be in the witness log and Node B will try and assume the role of the primary node. Since Node A will not be able to communicate with Node B or Witness A in this scenario, its cluster service will have shut down to prevent split-brain from occurring.

A Shared Disk witness works almost identically, except the witness is highly-available storage connected to the cluster, so instead of a witness log the shared disk maintains a copy of the cluster configuration database on the disk instead of just a witness log. Behaviorally, the witness and node interactions are the same.


What is the benefit of two FK, when one of them can be deduced from another table? The third party Microsoft SQL Server database I'm working on is using the structure which can be illustrated with this example:

Let's take three tables:

Shop which is the topmost level and corresponds, for example, to an e-commerce website, given that several websites are using the same database,
Category which is a logical category of products within a Shop,
Product which belongs to a Category (given that the category is mandatory, so there should be no products which don't belong to any category).

Category has one foreign key to Shop. The Product has two foreign keys: one to Category, another to Shop.

If I were designing a similar database, I would have put only one foreign key to Product, linking it only to a Category. IMO, it:

Simplifies the schema,
Avoids the risk of inconsistent state, where product 1 belongs to category 1 and site 2, but category 1 itself belongs to site 1,
(Avoids redundant information to waste the place in the database),
Doesn't make it particularly more difficult to query data. Even if there are cases (for example a search) where the website would need products without carrying too much about the categories, but still considering to what shop they belong, making two joins instead of one is not a big deal.

Why was this third party database done in the way it's done? Are there benefits from this approach?

Category has one foreign key to Shop. The Product has two foreign keys: one to Category, another to Shop.

There should be a single foreign key referencing two columns in categories.

create table products (
  ...
  primary key (shop_id, category_id, product_id),
  foreign key (shop_id, category_id)
    references categories (shop_id, category_id)
);

Think about it this way. Conceptually, normalization starts with a single relation that contains all the attributes.

shop_name  category_name    product_name
--
Wibble     Chain saws       Stihl 350
Wibble     Chain saws       Poulan 3X
Wibble     Pole saws        Black & Decker 14 foot electric
Wibble     Pole saws        Corona 15 foot

Thursby    Pole saws        Hitachi electric
Thursby    Pole saws        Remington electric
Thursby    Chain saws       Husqvarna 460
Thursby    Chain saws       Poulan 3x

It should be clear that the only candidate key is {shop_name, category_name, product_name}. It's in 5NF. There's no redundant data.

Replacing text with ID numbers won't improve that.

A design that requires you to chase ID numbers through a hierarchy of tables is an anti-pattern. It's like building an IMS database in SQL. (IMS was a problem the relational model intended to solve.)


Make database use more than one file I have to following problem: since my database runs out of memory in a few days, I am looking for a way how to make the database use more than one file. I would like to store upcoming data in another file, which is located on a different hard drive.

I am using SQL Server 2008. Under "database properties" I already found out I can create more logical files. I created one in the filegroup PRIMARY. Anyway, SQL Server doesn't use the new file. What can be the reason for this?

Do I have to limit the file size of my "primary" file to make it use the new file? I don't want to wait until my disc runs out of memory, hopefully somebody has some way to solve this!

Thank you very much in advance!

tommy

First and foremost lets make sure you did due diligence and have determined that is the data file that had grown, not the log. IF the log is growing, read How to shrink the SQL Server log and Factors That Can Delay Log Truncation.

Do you have more that one filegroup, ie aside from PRIMARY, is there another one? SQL Server tables are stored in individual file groups (strictly speaking is partitions of indexes (including index 0 and index 1, better known as the 'table') that are store on individual file groups. If you have more than one filegroup and the table that grows is not in PRIMARY then SQL cannot use the new file for the 'big' table. You need to add the file to the filegroup that has the size problem.

But, before adding a file, I would recommend you investigate how is the space used. Which table has grown and why? Are you wasting space? sp_spaceused will be your friend to investigate this. PErhaps you'll discover that you can reclaim the used space and save you from the troubles of an ever increasing file.


Overwhelmed with Sensor Data - Excel to SQL Server fails I'm managing a lot of sensor data and need to do charting, smoothing, stats over time on the data. Currently I import the data from flat files using VBA to do ETL into Excel.

The format of the data is:

[DataTime][SystemID][SystemParams 1][SystemParams ..n][Data 1][Data ...n]

There are 30 System Parameters and 8000 datapoints in each file.
All this is fine in Excel where I can manipulate, filter and pivot off the system parameters and then chart the Data over time.

But I want to get all rows out of Excel into a database as there will be 10K files showing up every week  in this format.

When I export the rows from Excel to SQL Server (using the SQL Server import/export tools I only get 200 columns of the 8000+ from my Excel row due to the column restrictions in SQL Server

What would be the correct way of structuring this data so that I can run queries on the System Parameters an retrieve the 8000 data points back into Excel for charting?

I'd like to stay in SQL Server but have read I might be better off with NoSQL. All advice gratefully accepted..

Overwhelmed

To help those who follow... I ended up packing the data into a comma separated string for insert into a varchar(max) column. Not what I wanted to do but it works and I'm on my way. Another gotcha with this method is that although varchar(max) can store 2GB the table read from an external connection in excel only returns the first 32765 bytes of the string. To get round that I needs to run a vba script to get the full string using GetChunk() method.

Best.


DTC in SQL server 2008 R2 Clustering I believe DTC is optional but cannot confirm it (can't find it on MSDN)

Why we need DTC (distributed transaction cordinator) when clusting a single instance (active passive scenario)?

It is optional, though you will get a warning during setup.

Here are different config scenarios for MSDTC.


Mysql Master Offline In a unidirectional Master-Slave setup, should the slave be manually stopped in cases where the Master is offline due to Maintenance, Crash etc. ? Or should one just leave it so that it keeps trying to reconnect until the Master is up and running again ?

If the master crashes, then it doesn't really matter what you do as far as stopping the slave, as long as you observe the slave once the master is back online, to verify that it has successfully started reading and executing events from the master again.

If you don't stop it, the slave should still be fine, and will sit and continually try to reconnect to the master based on master-retry-count and slave_net_timeout.  These retries are not going to have any significant performance impact on other queries running on the slave.  After enough retries, the slave will eventually give up, after which STOP SLAVE; followed by START SLAVE; should bring it back online again (or make it try again for the allocated number of retries).

If you take the master down for maintenance, the safest thing to do would arguably be to STOP SLAVE IO_THREAD; on the slave before shutting down the master, but if you don't do this, the master should shut down the slave's connection gracefully.  The slave will still continue to retry, but the retries as the slave attempts to reconnect aren't going to do any harm.

http://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html


How to removing or reduce the size of redo logs in MySQL cluster Can anybody tell me that how can we reduce the redo log file size or can we remove the redo log file size in MySQL Cluster and also what will be the impact of it on performance of cluster.Also can we disable Redo logging in MySQL cluster.

You can decrease the size of redo log files by decreasing parameter NoOfFragmentLogFiles which defines no. of fragment files or by reducing size of each fragment file by parameter FragmentLogFileSize. By default  NoOfFragmentLogFiles=16 and FragmentLogFileSize=16M. Hence total redo log files size will be 256MB and there are 4 of these if you will look into your file directory system; D8 D9 D10 D11; If your NoOfReplicas parameter is 1 then only one redo log file will take upto size 256MB and rest will have an apparent size of 256MB not actual.You can see the redo usage by executing command all dump 2398; on management node and look into node's ndb_"NODEID"_out.log. You will see redo files usage.


What is the behavior of null/empty value in compound shard key? I have a shard key based on {Field1:1,Field2:1} where Field2 can
  be null. So if two documents have same value for Field1 and
  null/empty value of Field2 then are they considered to belong to
  the same shard key/non split-able??

In MongoDB 'null' is just another value so two documents that have the same values for field1 (some non-null) and same value for field2 (null) have the same value for the shard key and will always be in the same "chunk".


MySQL consumes memory for each portion of read I'm using MySQL instance for read only operations (data migration to another DB).

I'm reading several dozens of millions rows from one table using limited portions of data (100 000 rows) using select SQL_NO_CACHE * from TABLE.

After each read portion MySQL consumes about 50-100 MB of RAM, and doesn't free memory.

I have no idea, why it does consumes memory as I read data with SQL_NO_CACHE parameter.
At least after several iterations of reading all memory becomes eaten and server starts dropping incoming connections.

So my question is: how to avoid this constant memory consuming for read-only usage?

UPD If it matters, it is AWS RDS instance of MySQL

MySQL has several "caches".  Some of them grow as needed up to a pre-determined limit.  This probably explains (at a high level) what you are seeing.

A well-tuned MySQL-only server will consume nearly all of RAM, then sit there quite happy.

Do you have other applications running on the same server?  If so, you need to tune MySQL not to reach for filling up the server.

If you want to discuss tuning, please prove the amount of available RAM (after accounting for other processes), plus the my.cnf settings.


mssql-service error exit with status 255 ubuntu I cannot run mssql immediately following an apt-get update. Is this due to a bad script (below: The file "C:\Install\SqlTraceCollect.dtsx" does not exist)? Any suggestions on a fix? I'm at a loss.

sudo systemctl start mssql-server
sudo systemctl status mssql-server
● mssql-server.service - Microsoft SQL Server Database Engine
    Loaded: loaded (/lib/systemd/system/mssql-server.service; enabled; vendor preset: enabled)
       Active: active (running)
        ...
sudo systemctl status mssql-server
● mssql-server.service - Microsoft SQL Server Database Engine
    Loaded: loaded (/lib/systemd/system/mssql-server.service; enabled; vendor preset: enabled)
       Active: inactive (dead) (Result: exit-code) since Wed 2018-03-21 22:46:15 PDT; 1min 27s ago
         Docs: https://docs.microsoft.com/en-us/sql/linux
      Process: 2977 ExecStart=/opt/mssql/bin/sqlservr (code=exited, status=255)
     Main PID: 2977 (code=exited, status=255)
    ...
        sudo cat /var/opt/mssql/log/errorlog
        2018-03-21 22:36:24.19 Server      Microsoft SQL Server 2017 (RTM-CU5) (KB4092643) - 14.0.3023.8 (X64)
        ...
        2018-03-21 22:36:35.90 spid6s      Creating SSIS folders...
        ...
        2018-03-21 22:36:35.91 spid6s      Creating procedure [dbo].[sp_syscollector_upload_instmdw]...
        2018-03-21 22:36:35.92 spid6s      Uploading data collector package from disk: C:\Install\SqlTraceCollect.dtsx
        2018-03-21 22:36:35.92 spid6s      Uploading data collector package from disk: C:\Install\SqlTraceCollect.dtsx
        2018-03-21 22:36:35.93 spid6s      Error: 4860, Severity: 16, State: 1.
        2018-03-21 22:36:35.93 spid6s      Cannot bulk load. The file "C:\Install\SqlTraceCollect.dtsx" does not exist or you don't have file access rights.
        2018-03-21 22:36:35.93 spid6s      Error: 912, Severity: 21, State: 2.
        2018-03-21 22:36:35.93 spid6s      Script level upgrade for database 'master' failed because upgrade step 'msdb110_upgrade.sql' encountered error 200, state 7, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
        2018-03-21 22:36:35.95 spid6s      Error: 3417, Severity: 21, State: 3.
        2018-03-21 22:36:35.95 spid6s      Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.
        2018-03-21 22:36:35.96 spid6s      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

Seems like the only solution is downgrading:
On a Ubuntu server do the following to perform a downgrade to the previous (14.0.3022.28-2) version:
systemctl stop mssql-server
sudo apt-get install mssql-server=14.0.3022.28-2
systemctl start mssql-server

For other linux OS use your package manager or download the package manually from here: https://packages.microsoft.com

Update 2018-03-25:
The support team replied and included a solution for this problem in the release notes:

To resolve this error, enable SQL Server Agent and restart SQL Server with the following commands:
sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true
sudo systemctl start mssql-server

https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-release-notes#known-upgrade-issue


Java application unable to access Blob stream in Oracle after upgrade to 11g This issue below might be an application problem, but I cannot seem to rule out a poor database setup, I was hoping to get some insights into potential database issues.

I am maintaining a legacy Java application, our hosting provider provides DBA support for our Oracle database. We were on 10g, several months ago we upgraded to 11g. I in turn upgraded the Java drivers from ojdbc5.jar to ojdbc6.jar. Everything mostly seems fine....

The application uses Hibernate ORM tool, one of the fields of this one entity happens to be a Blob type. When the application saves changes to this entity, the generated SQL is an update statement that includes this Blob column regardless if it has changed or not.

On this one operation, the user updates their annual subscription, so they do it once a year, but when it calls the update here Java throws "Unable to access blob stream!" and then claims the underlying connection is closed.  This ONLY occurs for this one operation.

If I completely shut down and restart the server, and then perform the same operation again it works perfectly fine and continues to work fine for that one record forever and ever amen.

But it seems to happen here for every single record in this one table at least once.

Needless to say this is a nightmare to debug because I cannot reproduce it on a single record, just observe it once per record on my test system, then never see it happen again, and yes the same behavior occurs on my test environment that is a mirror of my production database.

My question is if it is possible that when the Oracle 11g upgrade occurred that it corrupted the LOB space in some way to where this seems to be happening?

EDIT:

Now I can't reproduce it on any record....
its like the database felt that I was trying to debug it and temporarily reverted into a state of good behavior

Maybe the LOB space was sleeping, then woke up?

EDIT 2:

The problem happened again this morning, after I left the database to sit unused for several hours.

As requested here is the exact error:

  Unable to access blob stream!
  Cause: Closed Connection
  SQL State: 08003
  Vendor Code: 17008

Preliminary research seems to suggest that this might be an issue with the Oracle JDBC drivers?  Oracle JDBC 11.2.0.3 ojdbc6.jar

I have faced similar situation for a procedure call with input Blob parameter. Same piece of code was working just fine on 11.2.0.2, but began to throw
"java.io.IOException: I/O Exception: Connection reset by peer: socket write error"
on 11.2.0.3. I have downgraded from ojdbc6.jar to ojdbc14.jar+ocrs12.jar now it is working. I don't know why but it seems ok for now.


differences between SPID and session_id I'm configuring a job to get the SPID of an offending process and kill it. I'm using a mix between the great sp_Whoiscative from @AdamMachanic and joining with sysprocesses on login name and SPID and session_id values as those are supposed to be the same (or I'm wrong and then that's my error?).

I'm getting something I don't understand: for a same SPID from sysprocesses I'm getting different session_id values from whoisactive results. What is incorrect here?

Here is the code I'm using. The filter to get values 10s before current datetime is because the whoisactive is running with the parameter @deltainterval that takes about 10s to execute on each run.

USE master;

--  Log information about current running processes to table Log_WhoIsActive
    EXEC [master].[dbo].[sp_WhoIsActive]
        @get_full_inner_text = 1,
        @get_plans = 2,
        @get_outer_command = 1,
        @get_transaction_info = 1,
        @get_task_info = 2,
        @get_locks = 1,
        @get_avg_time = 1,
        @get_additional_info = 1,
        @delta_interval = 2,
        @sort_order = '[start_time] DESC',
        @destination_table = 'Log_WhoIsActive_tests';

--compare date from sysprocesses with data gathered on sp_whosiactive
SELECT  dt.collection_time, sp.spid, dt.[dd hh:mm:ss.mss], sp.loginame,
        dt.login_name, dt.session_id
FROM    sys.sysprocesses sp JOIN Log_WhoIsActive_tests dt
            ON sp.loginame = dt.login_name
WHERE   sp.loginame = 'my_login_name'
        AND sp.status = 'runnable'
        AND sp.spid > 50
        AND dt.collection_time > DATEADD(ss, -10, GETDATE());

SPID === session_id.

What you are seeing is a Cartesian product since you are joining on a field that is not unique within the data set. You currently have:

FROM    sys.sysprocesses sp JOIN Log_WhoIsActive_tests dt
            ON sp.loginame = dt.login_name

You should probably, at the very least, add the following condition to that JOIN:

          AND sp.spid = dt.session_id

Also, please do not use sysprocesses. It was deprecated when SQL Server 2005 was released, and the sys.sysprocesses "table" you are using is just a compatibility view. The MSDN page for sys.sysprocesses even has a note at the top stating:

  This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping System Tables to System Views (Transact-SQL). This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

You should instead use the new DMV (Dynamic Management Views):

sys.dm_exec_sessions
sys.dm_exec_requests
sys.dm_exec_connections

Please note that a Login might need to be granted the server-level permission of VIEW SERVER STATE in order to see more than their own data for sys.dm_exec_sessions and sys.dm_exec_requests, or see anything at all in sys.dm_exec_connections.

If this permission is needed, then the current database when you run the GRANT statement needs to be master as this is a server-level permission.


How to prevent read replica restarts during high replication lag We're running an Aurora PostgreSQL cluster, with a single read replica along with the master node.

Periodically, there is very heavy write load which causes high replication lag.  This can cause the read replica to restart which is undesirable for us in a high availability environment. When this happens, clients that are connected to the cluster via the read-only endpoint get this JDBC error: org.postgresql.util.PSQLException: FATAL: the database system is starting up. Additionally, the AWS console shows these peppered throughout the logs:

  Read replica has fallen behind the master too much. Restarting postgres.

followed by

  DB instance restarted

We can tolerate the read replica being behind by several minutes, but we can't tolerate the read replica restarting to catch up.

Is there a way to prevent the read replica from restarting during these periods?

Alternatively, are there any recommended tweaks for reducing replication lag during periods of heavy write load?

I think this is what they would call "working as designed". It is stated in the documentation for Aurora MySQL (emphasis mine):

  The tradeoff with having multiple Aurora Replicas is that replicas become unavailable for brief periods when the underlying database instances are restarted. These restarts can happen during maintenance operations, or when a replica begins to lag too far behind the master. Restarting a replica interrupts existing connections to the corresponding database instance.

The same I expect holds true for Aurora PostgreSQL, because the replication implementation is likely very similar, if not identical.

The reason for this behaviour I believe is explained by the way the changes are propagated from the write instance to the read replicas: the redo records are sent, and the replicas are expected to apply them to the appropriate locally cached pages in the right sequence -- see slide 27 of this presentation.

While the cache update processing should be fast, it can still overwhelm the replica, particularly if it's running on a less capable AWS instance. Once the replica loses track of what cache pages are stale and what aren't, it has no other choice than to start from scratch. I presume the original solution designers elected to use the existing instance startup process for that, instead of developing a new cache invalidation and reload mechanism, especially given that the restart can happen relatively quickly.

In other words, the answer to is there a way to prevent the read replica from restarting seems to be "no".

As to are there any recommended tweaks for reducing replication lag, since the lag depends entirely on the replica's CPU and memory capacity (as no I/O is involved), try scaling up your replica, permanently or at least during these heavy updates.

If you have control over the client application code, you could modify it to handle broken connections more gracefully and retry when needed. If you don't, you could try setting up a proxy (e.g. pgpool) between your clients and the read replica, it might relieve the pain to some extent by proactively testing and reëstablishing connections.

If you find neither of these suggestions workable, you can also contact the AWS support and see if they have any better ideas.


How to execute a query based on the condition if a column exists? I want to run the following query:

CREATE TABLE dummy AS
SELECT x, y, z
FROM table1
WHERE y!=0

then I want to do the following:

if z column has a non-null entry do:
1. copy the content of z into A
2. drop z
SELECT x,A
FROM Dummy

What the second query essentially tries to do is that copy those rows of z into A which have no missing values.
My question is how can we write query 2 and if there is a way to merge these two queries into one for optimisation?

Any help/advice much appreciated.

Since you aren't using column y, you can ignore it in your first query. Then, you can simplify this with a single statement:

SELECT
    x,
    A = z
FROM table1
WHERE y!=0 and z is not null


What is the difference between lock_timeout and deadlock_timeout? What is the play between lock_timeout and deadlock_timeout ?

What is the reason for a lock_timeout to occur not leading to a deadlock timeout. I am not able to think of scenarios from database perspective ?

The difference between a live lock (handled by lock_timeout) and a deadlock is that the latter is guaranteed never to end, because two (or more) sessions are waiting for each other.

A live lock can always end – it will end as soon as the session that locks the resource ends the database transaction. With a deadlock, that is not possible, because all involved transactions are blocked and cannot continue.

By default, the database resolves deadlocks by killing one of the involved transactions, but it does not interrupt live locks.


How would I track all price changes in a db in order to get the price of 'x' product on 'y' date I would need to track a products price changes so that I can query the db for a product price at a given date. The information is used in a system that calculates historical audits so it must return the correct price for the correct product based on the date of purchase.

I would prefer to use postgres in building the db.

I need with the design of the database but any and all best practice suggestions are also welcome.

If I understand the scenario appropriately, you should define a table that retains a Price time series; therefore, I agree, this has a lot to do with the temporal aspect of the database you are working with.
Business rules
Let us start analyzing the situation from the conceptual level. So, if, in your business domain,

a Product is purchased at one-to-many Prices,
each Price of purchase becomes Current at an exact StartDate, and
the Price EndDate (which indicates the Date when a Price ceases to be Current) is equal to the StartDate of the immediately subsequent Price,

then that means that

there are no Gaps between the distinct Periods during which the Prices are Current (the time series is continuous or conjunct), and
the EndDate of a Price is a derivable datum.

The IDEF1X diagram shown in Figure 1, although highly simplified, depicts such a scenario:

Expository logical layout
And the following SQL-DDL logical-level design, based on said IDEF1X diagram, illustrates a feasible approach that you can adapt to your own exact needs:
-- At the physical level, you should define a convenient
-- indexing strategy based on the data manipulation tendencies
-- so that you can supply an optimal execution speed of the
-- queries declared at the logical level; thus, some testing
-- sessions with considerable data load should be carried out.

CREATE TABLE Product (
    ProductNumber INT      NOT NULL,
    Etcetera      CHAR(30) NOT NULL,
    --
    CONSTRAINT Product_PK PRIMARY KEY (ProductNumber)
);

CREATE TABLE Price (
    ProductNumber INT  NOT NULL,
    StartDate     DATE NOT NULL,
    Amount        INT  NOT NULL, -- Retains the amount in cents, but there are other options regarding the type of use.
    --
    CONSTRAINT Price_PK            PRIMARY KEY (ProductNumber, StartDate),
    CONSTRAINT Price_to_Product_FK FOREIGN KEY (ProductNumber)
        REFERENCES Product (ProductNumber),
    CONSTRAINT AmountIsValid_CK    CHECK       (Amount >= 0)
);

The Price table has a composite PRIMARY KEY made up of two columns, i.e., ProductNumber (constrained, in turn, as a FOREIGN KEY that makes a reference to Product.ProductNumber) and StartDate (pointing out the particular Date in which a certain Product was purchased at a specific Price).
In case that Products are purchased at different Prices during the same Day, instead of the StartDate column, you may include one labeled as StartDateTime that keeps the Instant when a given Product was purchased at an exact Price. The PRIMARY KEY would then have to be declared as (ProductNumber, StartDateTime).
As demonstrated, the aforementioned table is an ordinary one, because you can declare SELECT, INSERT, UPDATE and DELETE operations to manipulate its data directly, hence it (a) permits avoiding the installation of additional components and (b) can be used in all the major SQL platforms with some few adjustments, if necessitated.
Data manipulation samples
To exemplify some manipulation operations that appear useful, let us say that you have INSERTed the following data in the Product and Price tables, respectively:
INSERT INTO Product
    (ProductNumber, Etcetera)
VALUES
    (1750, 'Price time series sample');

INSERT INTO Price
    (ProductNumber, StartDate, Amount)
VALUES
    (1750, '20170601', 1000),
    (1750, '20170603', 3000),
    (1750, '20170605', 4000),
    (1750, '20170607', 3000);

Since the Price.EndDate is a derivable data point, then you must to obtain it via, precisely, a derived table that can be created as a view in order to produce the “full” time series, as exemplified below:
CREATE VIEW PriceWithEndDate AS

    SELECT  P.ProductNumber,
            P.Etcetera AS ProductEtcetera,
           PR.Amount   AS PriceAmount,
           PR.StartDate,
           (
                SELECT MIN(StartDate)
                      FROM Price InnerPR
                     WHERE P.ProductNumber   = InnerPR.ProductNumber
                       AND InnerPR.StartDate > PR.StartDate
           ) AS EndDate
        FROM Product P
        JOIN Price   PR
          ON P.ProductNumber = PR.ProductNumber;

Then the following operation that SELECTs directly from that view
  SELECT ProductNumber,
         ProductEtcetera,
         PriceAmount,
         StartDate,
         EndDate
    FROM PriceWithEndDate
ORDER BY StartDate DESC;

supplies the next result set:
ProductNumber  ProductEtcetera     PriceAmount  StartDate   EndDate
-------------  ------------------  -----------  ----------  ----------
         1750  Price time series…         4000  2017-06-07  NULL      -- (*)
         1750  Price time series…         3000  2017-06-05  2017-06-07
         1750  Price time series…         2000  2017-06-03  2017-06-05
         1750  Price time series…         1000  2017-06-01  2017-06-03

-- (*) A ‘sentinel’ value would be useful to avoid the NULL marks.

Now, let us assume that you are interested in getting the whole Price data for the Product primarily identified by ProductNumber 1750 on Date 2 June 2017. Seeing that a Price assertion (or row) is current or effective during the entire Interval that runs from (i) its StartDate to (ii) its EndDate, then this DML operation
 SELECT ProductNumber,
        ProductEtcetera,
        PriceAmount,
        StartDate,
        EndDate
   FROM PriceWithEndDate
  WHERE ProductNumber = 1750        -- (1)
    AND StartDate    <= '20170602'  -- (2)
    AND EndDate      >= '20170602'; -- (3)

-- (1), (2) and (3): You can supply parameters in place of fixed values to make the query more versatile.

yields the result set that follows
ProductNumber  ProductEtcetera     PriceAmount  StartDate   EndDate
-------------  ------------------  -----------  ----------  ----------
         1750  Price time series…         1000  2017-06-01  2017-06-03

which addresses said requirement.
As shown, the PriceWithEndDate view plays a paramount role in obtaining most of the derivable data, and can be SELECTed FROM in a fairly ordinary way.
Taking into account that your platform of preference is PostgreSQL, this content from the official documentation site contains information about “materialized” views, which can help to optimize the execution speed by means of physical level mechanisms, if said aspect becomes problematic. Other SQL database management systems (DBMSs) offer physical instruments that are very alike, although different terminology may be applied, e.g., “indexed” views in Microsoft SQL Server.
You can see the discussed DDL and DML code samples in action in this db<>fiddle and in this SQL Fiddle.
Related resources

In this Q & A we discuss a business context that includes the changes of Product Prices but has a more extense scope, so you may find it of interest.

These Stack Overflow posts cover very relevant points regarding the type of a column that holds a currency datum in PostgreSQL.

Responses to comments

This looks similar to work that I did, but I found it much more convenient / efficient to work with a table where a price (in this case) has a startdate column and an enddate column - so you're just looking for rows with targetdate >= startdate and targetdate <= enddate. Of course, if the data isn't stored with those fields (including enddate 31st December 9999, not Null, where no actual end date exists), then you'd have to do work to produce it. I actually made it run every day, with end date = today's date by default. Also, my description requires enddate 1 = startdate 2 minus 1 day. – @Robert Carnegie, on 2017-06-22 20:56:01Z

The method I propose above addresses a business domain of the characteristics previously described, consequently applying your suggestion about declaring the EndDate as a column —which is different from a “field”— of the base table named Price would imply that the logical structure of the database would not be reflecting the conceptual schema correctly, and a conceptual schema must be defined and reflected with precision, including the differentiation of (1) base information from (2) derivable information.
Apart from that, such a course of action would introduce duplication, since the EndDate could then be obtained by virtue of (a) a derivable table and also by virtue of (b) the base table named Price, with the therefore duplicated EndDate column. While that is a possibility, if a practitioner decides to follow said approach, he or she should decidedly warn the database users about the inconveniences and inefficiencies it involves. One of those inconveniences and inefficiencies is, e.g., the urgent need to develop a mechanism that ensures, at all times, that each Price.EndDate value is equal to that of the Price.StartDate column of the immediately successive row for the Price.ProductNumber value at hand.
In contrast, the work to produce the derived data in question as I put forward is, honestly, not special at all, and is required to (i) guarantee the correct correspondence between the logical and conceptual levels of abstraction of the database and (ii) ensure data integrity, both aspects that as noted before are decidedly of great importance.
If the efficiency aspect you are talking about is related to the execution speed of some data manipulation operations, then it must be managed at the appropriate place, i.e., at the physical level, via, e.g., an advantageous indexing strategy, based on (1) the particular query tendencies and (2) the particular physical mechanisms provided by the DBMS of use. Otherwise, sacrificing the appropriate conceptual-logical mapping and compromising the integrity of the data involved easily turns a robust system (i.e., a valuable organizational asset) into a non-reliable resource.
Discontinuous or disjunct time series
On the other hand, there are circumstances where retaining the EndDate of each row in a time series table is not only more conenient and efficient but demanded, although that depends entirely on business-environment-specific requirements of course. One example of that kind of circumstances comes about when

both the StartDate and the EndDate pieces of information are held before (and retained via) every INSERTion, and
there can be Gaps in the middle of the distinct Periods during which the Prices are Current (i.e., the time series is discontinuous or disjunct).

I have represented said scenario in the IDEF1X diagram displayed in Figure 2.

In that case, yes, the hypothetical Price table must be declared in a manner that is similar to this:
CREATE TABLE Price (
    ProductNumber INT  NOT NULL,
    StartDate     DATE NOT NULL,
    EndDate       DATE NOT NULL,
    Amount        INT  NOT NULL,
    --
    CONSTRAINT Price_PK            PRIMARY KEY (ProductNumber, StartDate, EndDate),
    CONSTRAINT Price_to_Product_FK FOREIGN KEY (ProductNumber)
        REFERENCES Product (ProductNumber),
    CONSTRAINT DatesOrder_CK       CHECK       (EndDate >= StartDate)
);

And, yes, that logical DDL design simplifies administration at the physical level, because you can put up an indexing strategy that encompasses the EndDate column (which, as shown, is declared in a base table) in relatively easier configurations.
Then, a SELECT operation like the one below
 SELECT  P.ProductNumber,
         P.Etcetera,
        PR.Amount,
        PR.StartDate,
        PR.EndDate
   FROM Price   PR
   JOIN Product P
  WHERE P.ProductNumber = 1750
    AND StartDate      <= '20170602'
    AND EndDate        >= '20170602';

may be used to derive the whole Price data for the Product primarily identified by ProductNumber 1750 on Date 2 June 2017.


Is it possible to do an in-place upgrade of SQL Server 2005 Standard to SQL 2012 Enterprise? At the moment, I have a SQL 2005 Standard box. I'd like to upgrade it to SQL 2012 Enterprise in-place.  When I say in place, I mean an install similar to Office 2010, e.g. it removes the older (Office 2007) version, binds itself to extensions once held by its predecessor and keeps all the settings as well.

Is this possible?

If not, what should be the procedure?  So far, I got this.

Back up the databases
Write down all the settings.
Uninstall SQL 2005.
Install SQL 2012 Enterprise.
Restore settings
Restore the databases.

Yes, according to Supported Version and Edition Upgrades you can upgrade in place from 2005 Standard to 2012 Enterprise, provided SQL Server 2005 is currently on an operating system supported by SQL Server 2012 Enterprise (meaning forget about XP or 2003 RTM) and that the architecture is the same (e.g. 64-bit -> 64-bit).

Though you should do step 1 and 2 anyway so you have something to compare to and fall back on, since you never know what might go wrong during the upgrade. (And don't forget to knock on wood and all that.)

Personally I prefer a side-by-side installation onto a new machine, especially if the 2005 hardware and/or operating system is old. The performance benefits you get "for free" when you upgrade are amplified immensely when you also upgrade the underlying stuff as part of the process.


How do I track down wait events for a specific transaction in Oracle Enterprise Manager? I have an application that makes tens of thousands of calls per day to a stored procedure.  Normally this executes in about 50 ms.  However, periodically, it takes 2-3 seconds to execute.  I have an application log file that shows what time and how long each call to the stored procedure took.  I'm wondering...how can I track down this exact transaction within Oracle Enterprise Manager and display the associated wait events so that I can understand why this specific call is taking so long to execute.

The ASH report shows top wait events, top SQL and top sessions. Using Enterprise Manager, you can generate the ASH report by navigating to the performance page and then clicking 'Run ASH Report'. On thhe performance page, you can also click the 'Top Activity' link. On the Top Activity page, you can then choose a time interval by dragging the shaded area(may have different drag controller version to version and browser to browser), and then click Run ASH Report.
For details:
Identifying High-Load SQL Statements


Manually Grow DBs in an Availability Group When you manually grow a data file on the primary replica in an Availability Group, which of the following happens?

The operation gets synced to the other secondary replicas.
I have to manually grow the files on all replicas of cluster.

First one.

  The operation gets synced to the other secondary replicas.

Because it is a logged operation.