How to change primary key's data type from int to bigint in a huge transactional database under tremendous load? This is a legacy system, with more than 1 billion records in a table that undergoes 10 million transactions per day.

Now the application complains that:

  Arithmetic overflow error converting IDENTITY to data type int

We want to change that id column's data type to bigint, but it can't do it and times out.

What should we do? I have no clue at all. We can't stop the system, because it's a monolithic application and database and does many things. Thus we prefer not to stop the whole system.

We're using SQL Server 10.50.6000.34, that is SQL Server 2008 R2 SP3 (September 2014)

Assuming your current table is something like

CREATE TABLE YourTable
  (
     Id           INT IDENTITY PRIMARY KEY,
     OtherColumns VARCHAR(10)
  )

And that your existing code that references the table just performs basic DQL and DML commands against the table (i.e. SELECT/UPDATE,MERGE,INSERT,DELETE)

And that YourTable(Id) isn't referenced by a foreign key anywhere.

Then possibly the best way of getting up and running quickly (if you can't afford the downtime of rebuilding the whole table in one go) would be to rename that table (e.g. as YourTableInt) and create a new table

CREATE TABLE YourTableBigInt
  (
     Id           BIGINT IDENTITY(2147483648, 1) PRIMARY KEY,
     OtherColumns VARCHAR(10)
  )

You could then create a view with the same name as your original table name.

CREATE VIEW YourTable
AS
  SELECT Id,
         OtherColumns
  FROM   YourTableInt
  UNION ALL
  SELECT Id,
         OtherColumns
  FROM   YourTableBigInt

You would need to write INSTEAD OF triggers that route the Inserts/Updates/Deletes to the appropriate table. This could initially be based on whether Id was <= 2147483647 or not but that isn't going to work if you try and migrate rows in the background from the legacy table to the new one so probably best to do the following.

Delete trigger
    Apply deletes against both tables by joining on id

Update trigger
    Apply updates to both tables by joining on id

Insert trigger
    Route all inserts into the new "YourTableBigInt" table. It shouldn't be possible for an insert through the view to enter an explicit identity that might clash with anything in the original table as any attempt to set identity_insert YourTable will fail now that is actually a view.

You could then have a background process that deletes batches of rows from YourTableInt and outputs them into YourTableBigInt. Once the original table is empty you can drop it and the view and rename YourTableBigInt to YourTable.


Transforming a relation to 3NF I'm trying to transform the following relation to 3NF but can't figure it out.
The relation is (a student can only have 1 major):

STUDENT (StuID, StuName, Major), Primary Key = {StuID}

I tried the following transformation, splitting it into 2:

STUDENT (StuID, StuName)
MAJOR (StuID, Major)

But I don't think it is correct because there are still anomalies. For example, if the school decides to introduce a new Major, they can't add it unless a student has selected that major.

Can someone guide me how I should proceed with this problem.

Second try:

STUDENT (StuID, StuName, MajorID)
MAJOR (MajorID, Major)

I think this is better choice when student can choose multiple Major at a time.

STUDENT (StuID, StuName)
MAJOR (StuID, Major)

You Second Try will be a better choice when student can choose only one Major at a time

STUDENT (StuID, StuName, MajorID)
MAJOR (MajorID, Major)

Now one more step, I can think of here is, as Major  value is some fixed set of variable(limited in number), This will gives the option to add new Major and student can choose more than one Major at a time.

Student (StuID, StuName)
Major (StuID, MajorID)
MajorLookup (MajorID, Major)


Use database to track locking/unlocking of objects I have a requirement to track lock/unlock actions of objects. Before any action made on an object (contract, partner, etc), an lock event is issued. After the action is finished, it issues the unlock event.

I want to obtain those objects which are locked but not yet unlocked. The goal is to make the query quick and to avoid deadlocks.

Below is the table

create table locks (
    id int identity,
    name varchar(255),
    lock int
)

insert into locks values('a', 1)
insert into locks values('b', 1)

insert into locks values('c', 1)
insert into locks values('d', 1)

insert into locks values('a', 0)
insert into locks values('c', 0)

insert into locks values('a', 1)
insert into locks values('b', 1)

I use the query below to object not-yet-unlocked objects:

select distinct m.name from locks m
    where (select COUNT(id) from locks locked
           where locked.lock = 1 and locked.name = m.name)
        >  (select COUNT(id) from locks unlocked
            where unlocked.lock = 0 and unlocked.name = m.name)

It works correct and result a, b and d.

My questions are:
 - Is my solution sufficient enough to avoid deadlocks? Is there any problem may occurs if there are a lots of INSERT during the query execution?
 - Do you have any other (better) way to solve this?

UPDATE

I apologize for not putting the context into the question. The database design above is not for replacing database locking.

We have an external system which we call it from our system. It requires to call lock and unlock method on their systems before each action performing on an object (could be a contract or a partner).

Recently, we have situations such that the server crashes and we have to restart it. Unfortunately, the running processes which already called lock did not have a chance to call unlock to release the objects, thus led to several other problems when our system connects again to the external one.

So we want to provide a capability to track each lock call. Upon restarting the server, we will call unlock on the objects which previously locked.

Thank Remus Rusanu for pointing out that my question is using a prototype DDL. This is the first time I posted a question on DBA and I apologize for not reading the FAQ.

Thanks

The goal is to make the query quick and to avoid deadlocks.

This is an unrealistic goal. Deadlocks are determined by the application acquiring the locks and is out of control of how you implement the locking. The best you can hope for is to detect deadlocks.

Implementing locks as record is problematic. Rows persist and you'll leak locks on application crash, even when the implementation is perfect. Do locks with applocks. They have clear transactional semantics and deadlocks are detected by the engine.

However, looking at what you're trying to achieve, is unlikely you need locks at all. You are describing a queue (pick the next item available to process and avoid conflicts => queue, not locking). Read Using tables as Queues.

As for your specific implementation (using a table that keeps the history of the locking) I must be honest: it is a disaster. To start with the table design is completely inadequate for the intended usage: you query by name but the table is a heap with no indexes. It has an identity column for no apparent reason. You may reply that it is just a 'pseudo-code' table, but this is DBA.SE, you do not post here incomplete DDL!

But more importantly, the implementation does not implement locking! There is nothing to prevent two users from 'locking' the same object twice. Your 'locking' relies entirely on the callers magically behaving correctly. Even a best written application could not use this locking, because there is no way of checking and acquiring the lock atomically. Two users can check, conclude that 'a' is unlocked, and simultaneously insert the ('a', 1) record. At the very least you would need an unique constraint. Which of course would break the semantics of "count the locks vs. unlocks to determine status".

Sorry to say, but this is an F grade implementation.

UPDATE

  So we want to provide a capability to track each lock call. Upon restarting the server, we will call unlock on the objects which previously locked.

W/o engaging into two-phase commit distributed transaction with the remote system all you can do is a 'best effort', because there are many race conditions between you writing the 'unlock' and actually calling 'unlock' on the third party system. As a best effort, here is my recommendation:

create a simple table to track the locks:

CREATE TABLE locks (name VARCHAR(255) NOT NULL PRIMARY KEY);
before calling lock insert the lock into your table and commit.
after calling unlock delete the lock from your table and commit
on system startup look at the table. any row there is a left over lock from a previous run and must be 'unlocked'. Call unlock for each row, then delete the row. Only after all pending locks have been 'unlocked' you can resume the app normal functionality.

I'm proposing this because the table will stay small. At any time it only contains current, active locks. It will not grow ad-nauseam and cause problems later due to sheer size. Is trivial to see what is locked.

Of course this implementation does not  offer an audit history of what was locked by whom and when. You can add that if needed, as a different table, into which you only insert the events (lock or unlock) and never query that to figure out 'orphaned' locks.

You still have to be prepared with calls to 'unlock' to fail during startup because you cannot guarantee that your system did not crash after calling unlock but before deleting the row (in other words your table and the third party system have drifted apart and have different versions of the truth). Again you cannot prevent this w/o distributed transactions and I would never advise for DTC.


Can't understand difference in fulltext results - contains, contains with wildcard, freetext I have a table with an fulltext index on the column named Filecontent.
The table has a row where content contains "W 917". For context, the content column on this rows contains much more than just what I'm searching for.
I don't understand why I'm getting different results depending on whether I'm using contains, contains with wildcard or freetext.
Why is CONTAINS without wildcard getting results, but CONTAINS with wildcard doesn't?
-- Searching for "W 917"
-- No match - CONTAINS with wildcard
SELECT * FROM InvoicePDFContent t1 WHERE CONTAINS(t1.Filecontent, '"W 917*"')

-- Match - CONTAINS
SELECT * FROM InvoicePDFContent t1 WHERE CONTAINS(t1.Filecontent, '"W 917"')

-- Match - FREETEXT
SELECT * FROM InvoicePDFContent t1 WHERE FREETEXT(t1.Filecontent, '"W 917"')

-- Searching for "W"
-- Match - CONTAINS with wildcard
SELECT * FROM InvoicePDFContent t1 WHERE CONTAINS(t1.Filecontent, '"W*"')

-- No match - CONTAINS
SELECT * FROM InvoicePDFContent t1 WHERE CONTAINS(t1.Filecontent, '"W"')

-- No match - FREETEXT
SELECT * FROM InvoicePDFContent t1 WHERE FREETEXT(t1.Filecontent, '"W"')
´´´

The difference is caused by noise word "W". By default SQL Server uses stoplist to exclude some short or frequently used words like "and", "does", "could". You can see the full list using such query:
select * from sys.fulltext_system_stopwords where language_id = 1033

And you can disable the stoplist for your full text index if you want.
Now let's take a look at your queries:
SELECT * FROM InvoicePDFContent t1 WHERE CONTAINS(t1.Filecontent, '"W*"')

this one looks for all rows which contain words starting with "W" and these words shouldn't be in your stoplist.

SELECT * FROM InvoicePDFContent t1 WHERE CONTAINS(t1.Filecontent, '"W"')

this query returns nothing because you are looking for a stop word.

SELECT * FROM InvoicePDFContent t1 WHERE CONTAINS(t1.Filecontent, '"W 917"')

this query just ignores the W and looks for "917".

SELECT * FROM InvoicePDFContent t1 WHERE CONTAINS(t1.Filecontent, '"W 917*"')

This is the most interesting case. According to SQL Server documentation:

When <prefix_term> is a phrase, each word contained in the phrase is
considered to be a separate prefix. Therefore, a query specifying a
prefix term of "local wine*" matches any rows with the text of "local
winery", "locally wined and dined", and so on.

It means actually you are looking for phrases which have two words in a row. The first one is like "W*" (but it shouldn't be in stoplist) and the second one is "917*".


Galera backup failing with error "mysqldump: Error 1213: Deadlock found when trying to get lock; try restarting transaction when dumping table" We face the same issue like this posting on dba.stackexchange.com Deadlock from mysqldump --single-transaction, is it possible?. We offer DBaaS and can't control the DDL statements of our customers.

mysqldump: Error 1213: Deadlock found when trying to get lock; try restarting transaction when dumping table `x` at row: x

After that error the backup fails.

This is the backup command

mysqldump --max_allowed_packet=1G -u root -pvubaluaGebetee1aichaingubaePho --opt --flush-logs --single-transaction  --all-databases | bzcat -zc > /var/lib/mysql/backup/$FILE`date +%Y%m%d-%H%M%S`.sql.bz2

The accepted solution recommends Setup Master/Slave Replication. We use already Galera multi master replication.

> select version();
+----------------------+
| version()            |
+----------------------+
| 5.5.41-MariaDB-wsrep |
+----------------------+
1 row in set (0.00 sec)

other ideas howto make sure the backup completes? I don't care if a few rows not in backup.

Option 1

Use a replication slave off the galera cluster exclusively for creating backups. You can stop slave to ensure that all DDL operations have finished before doing a dump. Parallel dumping operations like mydumper are also possible.

Don't forget to enable log-slave-updates on the galera member you use as a master.

Option 2

Desync a galera node before doing a backup from it.


looping through a table type variable I have a function that has one parameter of a table type, i want to loop through all the fields and return the column that has a null or empty value.

CREATE OR REPLACE FUNCTION toned.function_validate(varmember tbl_member)
  RETURNS text AS
$BODY$
...

The definition of tbl_member below

CREATE TABLE toned.tbl_member
(
  code smallint NOT NULL DEFAULT nextval('toned.seq_code'::regclass),
  name text NOT NULL,
  email text,
  address text,
  city text,
  country text
);

can anyone help me to loop through the variable? I dont know how to
 refer to the field names.
can i do something like varmember[0] ??

Typically I do as much as I can with declarative stuff in the table definition.  Then I may use an exception block if I can't just trust my application to detect and handle standard exceptions normally.

Doing what you are trying to do in plpgsql is likely to be extremely difficult because of the limits of plpgsql expressiveness.  While we can find the attributes, generating a query to check them is not possible without resorting to temporary tables or the like.  So let's talk about temporary tables as an option.  You could:

CREATE TEMPORARY TABLE temp_tblmember OF TYPE toned.tbl_member;

Then you could insert this into that temporary table.

You could then look up the attributes in pg_attribute (joins pg_class on pg_attribute.relid = pg_class.oid).  Then you could use EXECUTE to check the attributes.

Unfortunately this isn't likely to perform very well and it is likely to be very cumbersome to code.

Fortunately you could look at using something pl/perl to do the looping, where you can get access, and then loop through the keys of the hashref.

CREATE OR REPLACE FUNCTION validate(tbl_member) returns int immutable language plperl as
$$
  my ($ref) = @_;
  for my $k (keys %$ref){
      if (!defined $ref->{$k}){
          elog(ERROR, 'tbl_member.' . $k . ' not defined!');
       }
  }
   return 1;
$$;


Why is CTE open to lost updates? I don't understand what Craig Ringer meant when he commented:

  This solution is subject to lost updates if the inserting transaction rolls back; there's no check to enforce that the UPDATE affected any rows.

on https://stackoverflow.com/a/8702291/14731. Please provide a sample sequence of events (e.g. Thread 1 does X, Thread 2 does Y) that demonstrates how lost updates could occur.

I think I probably meant to add that comment on the prior answer, about two separate statements. It was over a year ago, so I'm not totally sure anymore.

The wCTE based query doesn't really solve the problem it's supposed to, but upon reviewing it again over a year later I don't see the possibility of lost updates in the wCTE version.

(Note that all of these solutions will only work well if you try to change exactly one row with each transaction. As soon as you try to do multiple changes in one transaction things get messy because of the need for retry loops on rollbacks. At minimum you'd need to use a savepoint between each change.)

Two-statement version subject to lost updates.

The version that uses two separate statements is subject to lost updates unless the application checks the affected-row count from the UPDATE statement and the INSERT statement and retries if both are zero.

Imagine what happens if you have two transactions in READ COMMITTED isolation.

TX1 runs the UPDATE (no effect)
TX1 runs the INSERT (inserts a row)
TX2 runs the UPDATE (no effect, row inserted by TX1 isn't visible yet)
TX1 COMMITs.
TX2 runs the INSERT, *which gets a new snapshot that can see the row committed by TX1. The EXISTS clause returns true, because TX2 can now see the row inserted by TX1.

So TX2 has no effect. Unless the app checks the rowcount from the update and the insert and retries if both report zero rows, it won't know that the transaction had no effect and will merrily carry on.

The only way it can check the affected rowcounts is to run it as two separate statements rather than a multi-statement, or use a procedure.

You can use SERIALIZABLE isolation, but you'll still need a retry loop to deal with serialization failures.

The wCTE version protects against the lost updates issue because the INSERT is conditional on whether the UPDATE affects any rows, rather than on a separate query.

The wCTE doesn't eliminate unique violations

The writeable CTE version still isn't a reliable upsert.

Consider two transactions that run this concurrently.

Both execute the VALUES clause.
Now both of them execute the UPDATE portion. Since there are no rows matching the UPDATEs where clause, both return an empty resultset from the update and make no changes.
Now both run the INSERT portion. Since the UPDATE returned zero rows for both queries, both attempt to INSERT the row.

One succeeds. One throws a unique violation and aborts.

This isn't cause for concern about data loss so long as the app checks for error results from its queries (i.e. any decently written app) and re-tries, but it makes the solution no better than the existing two-statement versions. It doesn't eliminate the need for a retry loop.

The advantage the wCTE offers over the existing two-statement version is that it uses the output of the UPDATE to decide whether to INSERT, instead of using a separate query against the table. That's partly an optimisation, but it partly protects against a problem with the two-statement version that causes lost updates; see below.

You can run the wCTE in SERIALIZABLE isolation, but then you'll just get serialization failures instead of unique violations. It won't change the need for a retry loop.

The wCTE does not appear to be vulnerable to lost updates

My comment suggested that this solution could result in lost updates, but upon reviewing that I think I may have been mistaken.

It's over a year ago, and I can't recall the exact circumstances, but I think I probably missed the fact that unique indexes have a partial exception from transaction visibility rules in order to allow one inserting transaction to wait for another to insert or roll back before proceeding.

Or perhaps I missed the fact that the INSERT in the wCTE is conditional on whether the UPDATE affected any rows, not on whether the candidate row exists in the table.

Conflicting INSERTs on a unique index wait for commit/rollback

Say that one copy of the query runs, inserting a row. The change is not yet committed. The new tuple exists in the heap and the unique index, but it isn't visible to other transactions yet, irrespective of isolation levels.

Now another copy of the query runs. The inserted row is not yet visible as the first copy hasn't committed, so the update doesn't match anything. The query will go on to attempt an insert, which will see that another in-progress transaction is inserting that same key and will block waiting for that transaction to commit or roll back.

If the first transaction commits, the second one will fail with a unique violation, per the above. If the first transaction rolls back then the second will proceed with its insert instead.

The INSERT being dependent on the UPDATE rowcount protects against lost updates

Unlike in the two-statement case, I don't think the wCTE is vulnerable to lost updates.

If the UPDATE has no effect, the INSERT will always run, because it's strictly conditional on whether the UPDATE did anything, not on the external table state. So it can still fail with a unique violation, but it can't silently fail to have any effect and lose the update entirely.


Monitor table permission changes I need a solution to monitor table permission changes:

how can I implement monitoring of every changes to granted permissions for a specific or any user on a specific table in SQL Server 2008 R2?

With SQL Server 2008 R2 (through current version), you can use SQL Server Audit which uses Extended Events.

Look at Understanding SQL Server Audit:

  Auditing an instance of SQL Server or a SQL Server database involves tracking and logging events that occur on the system. You can use several methods of auditing for SQL Server, as described in Auditing (Database Engine). Beginning in SQL Server 2008 Enterprise, you can also set up automatic auditing by using SQL Server Audit.

SQL Server Audit is a combination of several parts:

SQL Server Audit = on an instance, define Instance or Database level actions or groups of actions to monitor and destincation of the output
Server Audit Specification = linked to one SQL Server Audit and is used to collect server level predifined groups of actions using Extended Events
Database Audit Specification = linked to one SQL Server Audit and is used to collect database level groups of actions or audit events using Extended Events
Target = the destination of the output to a file, Windows Security event log or Windows Application event log

The general process for creating and using an audit is as follow:

  Create an audit and define the target.
  Create either a server audit specification or database audit specification that maps to the audit.
  Enable the audit specification.
  Enable the audit.
  Do something related to the audit...
  Read the audit events by using the Windows Event Viewer, Log File Viewer, or the fn_get_audit_file function.

1. Create Audit:

USE [master]
GO
CREATE SERVER AUDIT [AuditTest]
TO FILE
(   FILEPATH = N'...\...'
    ,MAXSIZE = 0 MB
    ,MAX_ROLLOVER_FILES = 2147483647
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(   QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
);

2. Create Audit Specification:

USE [YourDB]
GO
CREATE DATABASE AUDIT SPECIFICATION [SpecTest]
FOR SERVER AUDIT [AuditTest]
ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP)
WITH (STATE = ON)

3. Enable Or Alter Audit Specification:

ALTER DATABASE AUDIT SPECIFICATION [SpecTest] WITH (STATE=OFF);
ALTER DATABASE AUDIT SPECIFICATION [SpecTest] ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP)
ALTER DATABASE AUDIT SPECIFICATION [SpecTest] WITH (STATE=ON);

4. Enable/Disable Audit

USE [master]
GO
-- disable
ALTER SERVER AUDIT [AuditTest2] WITH (STATE=OFF)
-- enable
ALTER SERVER AUDIT [AuditTest2] WITH (STATE=ON)

5. Change permission

DENY SELECT on dbo.testA to testLogin
REVOKE VIEW DEFINITION ON dbo.testA to testLogin
GRANT ALTER ON dbo.testA to testLogin

6. Read Audit Logs:

SELECT * FROM sys.fn_get_audit_file (N'C:\_data\AuditTest_*.sqlaudit', DEFAULT, DEFAULT);

Output:

event_time                  sequence_number action_id   succeeded   permission_bitmask                  is_column_permission    session_id  server_principal_id database_principal_id   target_server_principal_id  ...
2016-01-25 13:50:36.3210127 1               D           1           0x00000000000000000000000000000001  0                       57          260                 1                       271                         ...
2016-01-25 13:50:36.3210127 1               R           1           0x00000000000000000000000000000100  0                       57          260                 1                       271                         ...
2016-01-25 13:50:36.3210127 1               G           1           0x00000000000000000000000000000200  0                       57          260                 1                       271                         ...

Note that I am using SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP in place of DATABASE_OBJECT_PERMISSION_CHANGE_GROUP. You can read more from SQL Server Audit Action Groups and Actions toward the middle of the page:

  This event is raised whenever a grant, deny, revoke is performed against a schema object. Equivalent to the Audit Schema Object GDR Event Class.


Option to tell Toad to never split a file which contains multiple objects? Is there an option to tell it to stop prompting me to split a file which contains multiple objects when opening .sql files from an edit window?  I never want to split the file!

In Toad 10.6, it's in View => Toad Options => Editor => Open/Save => Opening files: Never split files


What are some ways to implement a many-to-many relationship in a data warehouse? The dominant topologies of Data Warehouse modelling (Star, Snowflake) are designed with one-to-many relationships in mind. Query readability, performance, and structure degrades severely when faced with a many-to-many relationship in these modelling schemes.

What are some ways to implement a many-to-many relationship between dimensions or between the fact table and a dimension in a data warehouse and what compromises do they inflict with regards to necessary granularity and query performance?

In my experience, a recursive hierarchy is the most practical way of tackling this. It offers the following advantages:

Unlimited depth.
Compactness.
Flexibility.
Speed.

By contrast, it takes an extra table for each level of "-to-many" joins. This is hard coded and difficult to maintain against schema updates.

By using filtered indexes, a large table of hierarchical joins can perform at superior speed to dedicated tables. The reason is each join is only "parent-child" compared to "to join table to data table". The latter has more indexes to process and store.

I've been trying to solve this problem for many years. Recently, this is what I came up with.


How do I select MAX/MIN from multiple tables, along with other columns from MIN/MAX row I have a query where I JOIN 4 weekly "summary" tables, and SELECT a MIN and a MAX of units FROM each table. This would give me the highest/lowest total units sold for, and I'm pulling an average from another table.

    SELECT
            L4.TotalUnits / 4   AS AvgMvmt,
            MIN(Ago.TotalUnits) AS LowMvmt,
            MAX(Ago.TotalUnits) AS HighMvmt
    FROM

            RTG_LOOKUP_LAST4 AS L4 INNER JOIN
            (
                    SELECT  UCC12UPCCode, TotalUnits, Date
                    FROM    [CACTUS].[dbo].[RTG_Lookup2_Last1]

                    UNION

                    SELECT  UCC12UPCCode, TotalUnits, Date
                    FROM    [CACTUS].[dbo].[RTG_Lookup2_2_Ago]

                    UNION

                    SELECT  UCC12UPCCode, TotalUnits, Date
                    FROM    [CACTUS].[dbo].[RTG_Lookup2_3_Ago]

                    UNION

                    SELECT  UCC12UPCCode, TotalUnits, Date
                    FROM    [CACTUS].[dbo].[RTG_Lookup2_4_Ago]
            ) AS Ago
                    ON Ago.UCC12UPCCode = L4.UCC12UPCCode
    WHERE
            L4.UCC12UPCCode = '01254601144'
    GROUP BY
            L4.TotalUnits

Now I need to add two more columns, HighDate and LowDate. These columns represent the date for the week that sold the highest/lowest units.

The Ago table looks like this:

UCC12UPCCode  TotalUnits  Date
------------- ----------- ----------
01254601144   90          2018-04-14
01254601144   98          2018-05-05
01254601144   107         2018-04-21
01254601144   132         2018-04-28

How would pull the Date column from the correct table so my end results looks like below:

LowMvmt     HighMvmt    LowDate    HighDate
----------- ----------- ---------- ----------
90          132         2018-04-14 2018-04-28

E: Fiddle
https://dbfiddle.uk/?rdbms=sqlserver_2012&fiddle=749c59e1f11bc2146a605072f1ef7d98

As a another option

WITH Ago AS (
    SELECT  UCC12UPCCode, TotalUnits, Date
    FROM    [CACTUS].[dbo].[RTG_Lookup2_Last1]

    UNION

    SELECT  UCC12UPCCode, TotalUnits, Date
    FROM    [CACTUS].[dbo].[RTG_Lookup2_2_Ago]

    UNION

    SELECT  UCC12UPCCode, TotalUnits, Date
    FROM    [CACTUS].[dbo].[RTG_Lookup2_3_Ago]

    UNION

    SELECT  UCC12UPCCode, TotalUnits, Date
    FROM    [CACTUS].[dbo].[RTG_Lookup2_4_Ago]
    ),
    Ranked AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY UPC ORDER BY TotalUnits, Date) LowUnitRank,
        ROW_NUMBER() OVER (PARTITION BY UPC ORDER BY TotalUnits DESC, Date) HighUnitRank
    FROM Ago
    )
SELECT L4.UCC12UPCCode,
    MAX(CASE WHEN LowUnitRank = 1 THEN TotalUnits END) LowTotalUnits,
    MAX(CASE WHEN HighUnitRank = 1 THEN TotalUnits END) HighTotalUnits,
    MAX(CASE WHEN LowUnitRank = 1 THEN Date END) LowUnitDate,
    MAX(CASE WHEN HighUnitRank = 1 THEN Date END) HighUnitDate
FROM RTG_LOOKUP_LAST4 L4
    INNER JOIN Ranked r ON L4.UCC12UPCCode = r.UCC12UPCCode
GROUP BY L4.UCC12UPCCode;

I've done this using a CTE query as I find it easier to follow and explain.

The first CTE is just your Ago data.

The second CTE ranks each row of the Ago data using ROW_NUMBER based on the ordering of the TotalUnits.  I have include the Date in the order so that if there are duplicate TotalUnits the first occurrence will be picked.

In the final query we are using CASE statements to pivot the data and aggregating the result for each UCC12UPCCode with MAX to create a single row for each UCC12UPCCode.

This query can be run over multiple UCC12UPCCode's


MS SQL Server Incremental(differential) backup interrupted due to Full backup I have a manual full backup happening every night, post which I am taking differential backup after every hour but after  three to four differential backups a full backup happened through third party automated backup happen, which is not in my control.

Can any please suggest the best approach to be followed if I don't want to stop the automated backup taken by third party and differential backup not to be failed because of this?.

While you can't control if the backup happens, could you ask the third party to change how the backup is taken? Using the COPY_ONLY option stops backups from interrupting other backup sequences, so if they add that to their process it will stop interfering with your's. See https://msdn.microsoft.com/en-gb/library/ms191495(v=sql.120).aspx for more info.

That is the only option that doesn't require an extra copy of the database. Other than that you could maintain a replica and have the 3rd party take their backups from there (or take your backups from there).


Could Not Be Accessed Value Of The For Loop Counter In PL/SQL I try to learn PL/SQL for loops in tutorials. I learn flow of control in a for loops. (And i saw that it is same with most of the other programming languages)

Here is the flow of control in a for loop:

 1. The initial step is executed first, and only once. This step allows
   you to declare and initialize any loop control variables.

 2. Next, the condition, i.e., initial_value .. final_value is evaluated.
   If it is TRUE, the body of the loop is executed. If it is FALSE, the
   body of the loop does not execute and flow of control jumps to the
   next statement just after the for loop.

 3. After the body of the for loop executes, the value of the counter
   variable is increased or decreased.

 4. The condition is now evaluated again. If it is TRUE, the loop
   executes and the process repeats itself (body of loop, then increment
   step, and then again condition). After the condition becomes FALSE,
   the FOR-LOOP terminates.

And then i  want to test this steps for my worry in my TOAD IDE. I use simple sample code to test as below.

DECLARE
   a number(2) ;
BEGIN
   FOR a IN REVERSE 10 .. 20 LOOP
      dbms_output.put_line('value of a: ' || a);
   END LOOP;
END;
/

Now this loop start initial counter, initial_value and final_value. counter is 'a', initial_value is 10 and the final_value is 20. And the counter a  will be the first in 20 because of the REVERSE keyword. The loop body is executed for 20. After the body is executed a is decreased. And it will be 19. And then the condition is eveluated. The condition is true because of 19 is in [10, 20]. and then for loop body is executed. This operations is contiuned at the same way. When counter a's value is 9 and then condition evaluated to FALSE.And then the loop is terminated. Flow of control jumps to the next statement just after the for loop.The counter's last value is 9. And i want to see it 9 value. so that i run below code.

DECLARE
   a number(2) ;
BEGIN
   FOR a IN REVERSE 10 .. 20 LOOP
      dbms_output.put_line('value of a: ' || a);
   END LOOP;
   dbms_output.put_line('THE FINAL VALUE OF a: ' || a);
END;
/

When the above code is executed, it produces the following result:

value of a: 20
value of a: 19
value of a: 18
value of a: 17
value of a: 16
value of a: 15
value of a: 14
value of a: 13
value of a: 12
value of a: 11
value of a: 10
THE FINAL VALUE OF a:

But i hope see the result like as below:

value of a: 20
value of a: 19
value of a: 18
value of a: 17
value of a: 16
value of a: 15
value of a: 14
value of a: 13
value of a: 12
value of a: 11
value of a: 10
THE FINAL VALUE OF a: 9

Why the counter 'a' is not connected after the loop. Why I did not see it's value to 9. Does'nt the program compiler made a real way assignment to counter 'a' ??

The problem is that the variable a that you declare in your declaration section is not the same as the variable a that is declared in the context of your loop.  You've declared two different variables named a with different scope.

If you run your block without a declaration section, you'll notice that it works even though you aren't declaring a variable a in the declaration section.  When you write a for loop, your counter is implicitly declared and is in scope only within the loop.  After the end loop, you can no longer reference the variable.

BEGIN
   FOR a IN REVERSE 10 .. 20 LOOP
      dbms_output.put_line('value of a: ' || a);
   END LOOP;
END;

If you want to explicitly declare the variable you are using in your loop and you want to be able to reference the variable outside the loop, you could write a while loop

declare
  i integer;
begin
  i := 20;
  while i >= 10
  loop
    dbms_output.put_line( 'In loop: ' || i );
    i := i - 1;
  end loop;
  dbms_output.put_line( 'Final value: ' || i );
end;


Define Database Size I want to create a database with Size limitation. I am using sql server 2008r2 edition.
Maximum size should be only 20GB. That means it should allow only 20GB space for particular database.
How can I do this?

You can set Maxsize on both the data and the log files of your database.
If the Max size of your database is 20GB you have to make sure the combined max size of both is 20 GB.

Remember to allocate enough room for the log file, so you don't run into issues early on.

You can do this by going into SSMS, right click your database, properties and go to Files.
If you want to script it, it can also be done.
Take a look at the BOL to find the syntax :   ALTER DATABASE FILE

Perhaps this is also useful for you:
Transaction Log Administration Best Practices


List minutes instead of grouping by them Looking how to list minutes 1 to 120 from a 2 hour interval timestamp field instead of getting each minute from both hours together.

What I want is something like this:

min      COUNT(*)
1        300
2        320
3        294
...      ...
...      ...
61       341
62       210
63       258
...      ...
...      ...
119      196
120      347

I was doing the GROUP BY but I'm only getting 60 rows collecting the result of each hour.

min      COUNT(*)
1        641
2        530
3        552
...      ...
...      ...

sorry if it's silly but I can't get the keyword to find it out ... 'group by minutes without collecting, list by minutes query mysql, order more than 60 minutes' were some searches I made.

I'm using this for now, of course I know it doesn't work but I want to initialize from something,

SELECT MIN(timestamp) AS min, COUNT(field) FROM table WHERE timestamp BETWEEN '2014-06-12 10:00:00' AND '2014-06-12 12:00:00' GROUP BY min

You can use the timestampdiff function. You need to use the start date as a second argument, like this:

SELECT TIMESTAMPDIFF(MINUTE,'2014-06-12 10:00:00', timestamp) AS mins,
COUNT(field)
FROM table
WHERE timestamp BETWEEN '2014-06-12 10:00:00' AND '2014-06-12 12:00:00' GROUP BY mins;


Error occur during Oracle and Sql Server Integration I have developed a SQL Server Integration Services package pulling data from Oracle 11g and storing in a SQL Server 2008 database. Everything works as expected, but when I schedule a sqlserver job to run the SSIS package it returns the following error:

  Error: System.InvalidOperationException: Attempt to load Oracle client libraries threw BadImageFormatException. This problem will occur when running in 64 bit mode with the 32 bit Oracle client components installed. ---> System.BadImageFormatException: An attempt was made to load a program with an incorrect format. (Exception from HRESULT: 0x8007000B) at System.Data.Common.UnsafeNativeMethods.OCILobCopy2(IntPtr svchp, IntPtr errhp, IntPtr dst_locp, IntPtr src_locp, UInt64 amount, UInt64 dst_offset, UInt64 src_offset) at System.Data.OracleClient.OCI.DetermineClientVersion() --- End of inner exception stack trace --- at System.Data.OracleClient.OCI.DetermineClientVersion() at System.Data.OracleClient.OracleInternalConnection.OpenOnLocalTransaction(String userName, String password, String serverName, Boolean integratedSecurity, Boolean unicode, Boolean omitOracleConnectionName) at System.Data.OracleClient.OracleInternalConnection..ctor(OracleConnectionString connectionOptions) at System.Data.OracleClient.OracleConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.OracleClient.OracleConnection.Open() at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName, String connStr, Object transaction)

Please note this error occurs only while running the package as a sqlserver job. If I run the package via Integrated Services in SQL Server Management Studio then I don't get any error.

I have even installed both 64-bit and 34-bit Oracle driver but no luck :(

Please provide inputs.

Solved the question.Select the check box 'use 32 bit runtime' in Execution options tab as shown below


MySql How to rollback my database to a certain point in time the fastest way? I'm currently working in a testing database and we are not cleaning up after test because of our current configuration and we probably aren't changing that configuration soon, we are currently using a server to apply some UI testing on a staging server.

So I tried mysqldump but the restore time was slow, then I tried mysqldbcopy and the copy time is better, but I read something about incremental backups with innobackupex of percona xtrabackup and I think it could be faster.

I would like to know what is the best option to rollback a database to a certain point in time in the faster way possible.

If you are trying to rollback to previous state after making logical changes i.e. DDL/DMLs - then xtrabackup can help. However, so is making a raw copy of the data while MySQL is shutdown before making the changes. Once you are done with the changes, stop MySQL then restore the previous copy, start it back up.

If you are looking for a more complex point in time recovery - yes, xtrabackup with incremental can help but not as fast the previous suggestion. You can do it like this for example:

Take full backup
Make changes to data
Take incremental backup
Make changes to data
Restore backup from #1
Make more changes to data
Restore backup from #3 - at this point you have restored to a snapshot before you did #4.

This combination of incremental allows you to restore at different points in time instead of single full raw copy.


Do I need to perform any tasks after Postgres goes into recovery mode? For starters, I do not have any sort of automated failover in place.

After two scenarios, I'm unsure of the state of the database and any required actions, if any, to take:

My master server disappears off the face of the planet and I touch my_trigger_file, converting my standby into a master server.
My master server crashes, then restarts in recovery mode

My assumption is that the server in question in each of those scenarios will first be in recovery mode, will then finish recovering, and will finally be ready as a master server.

In scenario 2 my assumption means things are back to normal, while in scenario 1 my assumption means the standby has no idea it was ever a standby and is now simply a master.

Are these assumptions correct, or are there other actions that need to take place in either or both scenarios after recovery finishes (aside from creating a new slave in the case of scenario 1)?

Also, is there a notable technical difference between the state of a slave server after becoming a master vs a master server undergoing recovery after a crash, or as far as Postgres is concerned, are they both essentially master servers that just recovered from something?

I'm using Postgres 9.2 with asynchronous streaming replication.

May I suggest upgrading to PostgreSQL 9.3 (freshly out!)? Some write that the new Streaming-Only Remastering capability turns a replica in a set of replicas into the new master for all of the other replicas. Apparently, it's even more useful in combination with the cascading replication you are using in 9.2

In PostgreSQL 9.2, cascading replication really requires file-based WAL archiving to work in case of disaster recovery. PostgreSQL 9.3 will not require that: you can set up large replication clusters of whatever length you want without any WAL archiving.

More information:

What's new in PostgreSQL 9.3
Cascading Replication and Cycles


MySQL bulk deletions I am currently looking at how to most efficiently delete rows from a table in bulk. The database I'm using is MySQL with innodb.

The table I want to delete from can be any size, but will usually be ~10gb, and has a primary key (int) and indexed hash. This hash is non-unique, and I do drop it before proceeding with the deletes. The table has no other indexes.

I have a second table containing only the id's I wish to delete. The number of id's to delete can be anything from none, to everything.

The problem is I can't decide on a strategy on how to delete these rows quickly. I am aware of 2, which serve opposite sides of the extreme.

Option 1: The standard DELETE FROM table_data WHERE id IN table_todelete. This is good if there are very few deletions.

Option 2: Create a new table. Insert rows from table_data where id NOT in table_todelete. Drop table_data and rename the newly created table. This is obviously better when there are a large number of deletes.

I do not know enough about the performance implications of select, insert, delete, to know how fast they are compared to each other.
My question is, since my data isn't skewed towards always having many or few deletes, which approach is better in terms of performance? Is there some sort of theoretical threshold at which I can swap between the two approaches? If there is a third option I might've not considered, please let me know.

Thanks.

You pretty much answered your own question. Creating a new table will be more efficient when you want to delete a large portion of the table. Just counting the number of records to modify will give you a general idea of the impact. However, you also want to account for time spent updating indexes. If you are deleting a lot of records, you may be concerned about slack space in the table, meaning that each block is only partially filled. Considering all of this together, if you need to delete more than about 30% of a table, then creating a new table would be faster.

On the down-side, creating a second copy of the table will use more disk space, even if only temporarily. It will disrupt database replication, so it is generally not a good option when replication is involved.

If the goal is to minimize impact on the database, then I recommend pt-archiver, from the Percona toolkit. It deletes records in batches and keeps commits to a reasonable size. It's a good option if you want to automate your purge to run on a daily basis.


Setup Multiple SQL Server Tree Structure similar as RDC based on Enviornments I want to setup multiple SQL Servers like a tree structure similar to Remote Desktop connection Manager, where we can setup multiple servers based on environments we support like Test and Prod.

You can do this with a feature of SQL Server Management Studio (SSMS) called Registered Servers.  Access the feature by going to "View" -> "Registered Servers."

You can then right-click on "Local Server Groups" and choose "New Server Group" to create the top-level containers ("TEST_ENV1", "TEST_ENV2").

You can right-click on those containers and choose "New Server Registrations" to add instances to that group ("SQL_SERVER_1", "SQL_SERVER_2").

There's lots of information about this feature on the Microsoft Docs site here: Register Servers

  Benefits of Registered Servers

  With Registered Servers you can:

  Register servers to preserve the connection information.
  ...
  Create groups of servers.
  Provide user-friendly names for registered servers by providing a value in the Registered server name box that is different from the Server name list.

You can also share these registrations, either by exporting them, or by creating a Central Management Server.


Install Oracle Weblogic: Unsupported major.minor version 52.0 I tried to install Oracle Weblogic on Redhat Linux 8

using command

java -jar fmw_12.2.1.4.0_wls_quick.jar

but got an error:

Unsupported major.minor version 52.0

Java version

java version "1.7.0_80"

Then I switched to a higher java version, which is:

openjdk version "1.8.0_232"

Then I got an error:

The OpenJDK JVM is not supported on this platform

What is wrong with that?

"openJDK" is not listed within the Supported Configuration documents.
https://www.oracle.com/middleware/technologies/fusion-certification.html

Use Oracle JDK.


Postgres reported table / db size varies wildly from dump size I have a very strange misreporting of the size of one specific database.

According to \l+ in psql the DB size is 292 MB.

Doing a sum over the sizes reported by the following statement also reports very close to 292 MB.

SELECT
    table_name,
    pg_size_pretty(total_size) AS total_size
FROM (
    SELECT
        table_name,
        pg_total_relation_size(table_name) AS total_size
    FROM (
        SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
        FROM information_schema.tables
    ) AS all_tables
    ORDER BY total_size DESC
) AS pretty_sizes

Yet a pg_dump of this database produces a sql file of 2.02 GB

I suspect that both counting methods skip LOBs since there are two tables in this database that contain TEXT fields with largish content (up to ~4MB per row).

Is there a way to include LOBs in the size calculation?

EDIT:
It gets even stranger:

This query:

SELECT SUM(LENGTH(text_column))
FROM some_table

Gives a result of 2,091,245,318 (i.e. 2.02 GiB) which is about the size of the dump.

Note that unless your application is going out of its way to use the large objects interface, then it is not storing those columns as LOBs. Normal columns can store up to 1gb using TOAST (The Oversized-Attribute Storage Technique).

In addition to storing the data out of line, it may be stored compressed. Although you don't say so I assume the dump you are composing with is uncompressed, which could account for most of the size difference.


How to restore by-default saved 'sys' database in MySQL 8.0? By mistake I deleted by-default saved 'sys' database in Mysql version 8.0.21 So, how to restore it?

It depends on how it was saved, but there's an example at the bottom of the manual page
mysql < sys_dump.sql


What is a non-clustered index scan I know what table scan, clustered index scan and index seek is but my google skills let me down to find a precise explanation into non clustered index scans. Why and when a query uses a non clustered index scan?

Thank you.

A non-clustered index scan may be chosen in this scenario:

the optimizer determines that it is cheaper to scan all rows rather than perform seeks/range scans
the non-clustered index is "skinnier" than the clustered index
the non-clustered still covers the columns needed by the query (or it covers enough of them and a lookup for the remainder is still cheaper than a clustered index scan)

It can also happen, obviously, if you have a heap (no clustered index) and 3. is still true.

This is not meant to be an exhaustive list - there are other cases where a non-clustered index scan may be chosen, but this is probably the most common.


Can we enable tls 1.0 on MSSQL Server 2019 on database level? if yes then how? i am running sql server 2019 on Linux box and trying to connect using a Windows server 2016 client, how can we enable TLS 1.0 for SQL Server 2019 on the Database/instance level.
TIA.

TLDR
You don't.
Long answer
You should be using TLS 1.2. If you follow the documentation, you'll be up and running using TLS, but you'll be using TLS 1.2.
SQL Server 2019 does not support SSL/TLS1.0


MongoDB restoring to existing replica set (avoid creating a new one) I have a MongoDB cluster runnning on 3 AWS EC2 instances, 1 for primary and 2 secondary nodes. I'm trying to perform a backup and restore without having to recreate the replica set. mongodump seems a bad option for the long term if the DB grows too much. So I tried to follow MongoDB documentation to create volume backups for /data and /journal from a secondary but how the backup is restored when using a replica set remains unclear. So:

Will the primary and other secondaries be updated if I restore the backup to only one secondary?
If not, can I restore the secondary backups to the primary? (Including /data and /journal volumes). What should I do afterwards? Will the primary replicate the changes to the secondary?
Can I avoid recreating the replica set?

No. Copying to Secondary will not help
Yes. Primary will replicate the data to Secondary. Follow the steps below
Re-creating the replica set is not an option

Steps:

You can restore it on the Secondary node first
Next, Switch your Secondary with restored data to be the new Primary. Now the Primary has latest data
Bring down the Secondary, erase its data and then bring it up (Do a Resync) so it can copy the data from the Primary with latest data
Repeat the same on other Secondary
Once done, you can switch your Primary if required


Would a cron job to automatically optimize tables on a monthly basis be ideal? I'm wondering if running the OPTIMIZE command via cronjob would be an intelligent move? Sometimes I forget to run optimize for months at a time.

My database is frequently using select, insert, update. It has about 45,000,000 rows.

Select uses the dateChecked column and update uses the URL column.

The compete and correct answer depends on which Mysql version you have and on which engine is the table.

Fast answer: no.

MyISAM

Short answer
Only if you can have some downtime and want to take some risks (see below).

Long answer
During the optimize, the table becomes I inaccessible, and if you need to use that table (selects, inserts, etc), then it's not a good idea to perform an scheduled downtime.

InnoDB

Short answer
If you have mysql 5.6.17 or above, than you may do it without downtime, but I wouldn't recommend it.

Long answer
Since mysql 5.6.17 that InnoDB can perform an optimize table with Online DDL, which means that selects, and even inserts, deletes and updates can be performed on the table while the optimize is running, if that is your case.
With that you can schedule an optimize on the table knowing that your system continues to run.

But it may not be safe to do it like that, as things may go wrong, and you're not with your eyes on the database to fix it ASAP. For example, you may not have free disk space for the optimize to run.

For 5.6.16 or lower, applies what I've said about myISAM.

Conclusion

Optimize table may lock your database users from accessing the database, if your mysql version doesn't support online ddl. And even if it supports, performing an operation like that automatically needs to be done with extreme care.

Maybe create a cron that sends you an email to remember to do the optimize?


Key Lookup isn't applied by default? I'm trying to learn about covering indexes. In the Northwind database, I select from the table Categories:

As you can see the table has a non-clustered index on the column CategoryName.
This SQL query:
select CategoryName  from Categories where Categories.CategoryName like 'Beverages'

returns an execution plan with an index seek:

However, this:
select CategoryName ,Description from Categories where Categories.CategoryName like 'Beverages'

returns this execution plan with an index scan using the primary key index, which isn't expected:

I can find the expected behaviour only when I force the query with the non-clustered index:
 select CategoryName ,Description from Categories
    with(index(CategoryName))
 where Categories.CategoryName like 'Beverages'

What is the problem?

There is no Description column in your index but there is in your query. So, SQL Server has to get this column. There are two options for doing that:

non-clustered index seek + key lookup
clustered index scan

If your statistics make SQL Server think it will read a lot of data, SQL Server makes a choice between the non-clustered index seek + key lookup or clustered index scan, this called tipping point.
Because, if SQL Server has to read huge data, the clustered index scan can be more efficient rather than non-clustered index seek + key lookup.
There is a good blog post here.


How can I speed up this query? Here is the definition of the relevant table:

CREATE TABLE IF NOT EXISTS pricehistory (
        id INTEGER(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        price INTEGER NOT NULL,
        region VARCHAR(20) NOT NULL,
        timestamp TIMESTAMP NOT NULL,
        is_from_api INTEGER DEFAULT 0)

Here is the query at issue:

SELECT MAX(price) AS price
FROM pricehistory
WHERE UNIX_TIMESTAMP(CONVERT_TZ(timestamp, '+00:00', 'SYSTEM'))
      >
      UNIX_TIMESTAMP(CONVERT_TZ(DATE_SUB(NOW(), INTERVAL 1000 DAY), '+00:00', 'SYSTEM'))
  AND region='us';

Here is the Explain

id: 1
select_type: SIMPLE
table: pricehistory
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 368040
Extra: Using where

Ok so this query runs really slow. I think it takes about 500ms to run for only 300k-400k rows.

Roughly 1/5 of all the rows have a region='us'. If I remove the UNIX_TIMESTAMP() function it drops the run time by about 100ms so I'll probably do that.

I've tried indexing, but I'm new to indexing and have never done it before. All the indexing I've done has either had no effect or slowed the query down even more.

I need the max price in the last x days, of only 1/5 of the rows (region='us') in the whole table. I can't think of a way to build an index out of that.

Note: I also have a query that gets the minimum price.

Server version: 10.1.35-MariaDB - mariadb.org binary distribution.

Because your WHERE clasue referencing timestamp has the column wrapped in a function, it is not going to be able to use an index efficiently to may currently be scanning the whole table.

An index on region may help, but not a lot unless that column is quite selective (or at all if the majority of the rows are from the region you are looking at).

As you are applying the same functions to the column and the value you are searching through it with, you can just drop that: the inequality

UNIX_TIMESTAMP(CONVERT_TZ(timestamp, '+00:00', 'SYSTEM')) > UNIX_TIMESTAMP(CONVERT_TZ(DATE_SUB(NOW(), INTERVAL 1000 DAY), '+00:00', 'SYSTEM'))

is exactly equivelant to just

timestamp > DATE_SUB(NOW(), INTERVAL 1000 DAY)

and that will allow in index on timestamp to be useful.

With that change in place, a combined index on both timestamp and region is likely to be most helpful here. Perhaps also adding price to the end then the query running will not need separate lookups to find that column after filtering by the others (though this will increase the size of the index so you are trading space for perhaps only a small amount of speed).


What's the difference between Conditionals and Predicates I am having difficulties finding what the actual difference between "conditionals" and "predicates" are in the context of SQL and Relational Database Management Systems. Do they mean the same thing?
For example, is a WHERE clause a conditional statement or a predicate? Or both?

that is a good question.
Depending in the definitions, you can exclude them or not.
In my opion :
A WHERE Clause is conditional, because it decides at the end which rows are selected depending on the conditions.
For a predicate the result only can be True or False, and a WHERE clause can't return this result.
I thought about that you need predicates to decide if a row is in the result set or not, but the WHERE clause as total doesn't return True or False


Sequence number shows a lower value after dump file import I'm using Oracle 11g database in which I have more than 12 schema's consisting of many tables. I've created sequences for primary keys to these tables. I've also created synonyms to refer my application from the main schema.

My issue is,
while i try to export(using expdp) the schema's from DB in 1 server and import(impdp) the dump file to db installed in another server, sequence values  only for some tables are getting changed. Even if i try to remap the table space or schemas, the value of sequence shows a lesser value than rows in their respective tables. Due to which when i access the application it throws an error while saving the data.

I am confused and don't getting an idea to solve this. Please advise.
Is their any method to solve this issue?

Thanks in advance.

If you run export command against live database without parameters like flashback_scn or flashback_time which ensures consistency during the export, then this can be an expected behavior because these objects are exported in different time and one can become inconsistent with another.

For example, if the sequence object is exported before the table and the application has inserted new records using the sequence and exported later then table and sequence can be out of sync.

Stopping application before starting the export process or using expdpparameters like flashback_time(this may not export all the transactions made after the time specified in this parameter unless you stop the application) can prevent this situation. Or you can adjust the sequence value after the import by recreating it.


Disabling triggers in stored procedures I am building a database application that uses functions and triggers. One trigger I have limits the possible data that can be put in on INSERT to make sure a record goes through a defined set of states. However, there is a function that inserts a record in that table that fires that trigger (it starts in a different state).

My question now, is it OK to disable that trigger using ALTER TABLE in that function to get this working, or should I rethink my trigger design? Another work around would be to insert it in the "start" state and immediately UPDATE the record to make a state-step.

You can disable the trigger in the database function, but any actions on the table with the trigger that occur outside of that transaction (functions create implicit transactions) will be blocked until the function completes.

Simple test case:

-- In session 1
drop table if exists test;
create table test ( x int, y text );

create or replace function test_trg_f() returns trigger as $func$
begin
    perform null;
    return NEW;
end;
$func$ language plpgsql;

CREATE TRIGGER test_trg BEFORE INSERT ON public.test FOR EACH ROW EXECUTE PROCEDURE public.test_trg_f();

-- in session 2
DO $$
begin
    alter table test disable trigger test_trg;
    perform pg_sleep(10);
    alter table test enable trigger test_trg;
end;
$$;

-- In session 1, will be blocked until session 2 completes
INSERT INTO test (x,y) VALUES (1,1);


Are deadlocks always a symptom of design flaws or are they an unavoidable occurrence even in well designed systems with high data writes/reads? A complete redesign proposal is experiencing deadlocks in its prototype. I am of the opinion that deadlocks are always the symptom of design flaws. I do not want to go into the specifics of our system; I just want to know others'opinion in general situations.
If a system is experiencing deadlocks on volume testing at such an early stage in its development, should the design be reevaluated?

I am of the opinion that deadlocks are always the symptom of design flaws

With READ COMMITTED SNAPSHOT set, and foreign keys supported by indexes, deadlocks should be treated as application bugs.

That's not to say you'll never see a deadlock, or that you won't have to introduce locking hints to serialize access to objects.

But with RCSI, deadlocks should be rare enough that they can be individually diagnosed and remediated, as opposed to using a retry loop or resorting to dirty reads.


Rows Holding the Group-wise mysql i have simple table and need to group by duplicate items , also i need to get min with holding row i read this tutorial work , but not sum grouped item level
http://dev.mysql.com/doc/refman/5.7/en/example-maximum-column-group-row.html

+------------+------+-------+----------+----+
| descendant | deep | level | ancestor | id |
+------------+------+-------+----------+----+
|          2 |    0 |     1 |        2 |  1 |
|          4 |    1 |     1 |        2 |  2 |
|          4 |    2 |     1 |       45 |  3 |
|          6 |    1 |     1 |     1368 |  4 |
|          6 |    2 |     1 |        6 |  5 |
|          6 |    3 |     1 |   123456 |  6 |
+------------+------+-------+----------+----+

i need  group  descendant and just show min deep row , bellow code work and not add level

SELECT  descendant,deep,ancestor, count(level)  FROM test as s1

         where deep = (SELECT min(s2.deep)
              FROM test s2
              WHERE s1.descendant = s2.descendant  )

 group by descendant

my Considered  result

descendant | deep | ancestor |count(level)

2          |  0   |    2     |   1
4          |  1   |    2     |   2
6          |  1   |    1368  |   3

thanks

At first glance...

All you need is a GROUP BY clause with the MAX aggregate function:

SELECT id, MAX(rev)
FROM YourTable
GROUP BY id

but with inner join we can holding row wit max or sum value

SELECT a.id, a.rev, a.contents
FROM YourTable a
INNER JOIN (
    SELECT id, MAX(rev) rev
    FROM YourTable
    GROUP BY id
) b ON a.id = b.id AND a.rev = b.rev


PostgreSQL no relations found I am running ruby and rails app with postgresql database, and I want to do batch import from text file to the database.

I've configured the database and if I run psql and \l to list all databases, I can see it.

However, after I run rake db:migrate no relations are created in the database and running psql\d in order to check tables, says "No relations found". But the schema is created and I can see the tables in Induction PostgreSQL client (http://inductionapp.com)

The problem may be a namespace issue.  You can \dn to list namespaces.

Keep in mind that by default \d only lists relations in the search path, and you can run show search_path to see what this is.

If you want to list relations in a namespace outside the search path, \d mynamespace.* will list them and their attributes.  You can also:

set search_path = 'mynamespace';
\d
set search_path = 'public';

To temporarily change the search path for purposes of using \d


Configure listen_addresses automatically in custom installation I have included Postgres in a custom installer for a Windows application. I do not use the installer binary, but extract the Postgres zip archive for installation and then run initdb from a batch script.
After installation, the db cluster should accept connections from everywhere. Currently my installation routine

modifies pg_hba.conf
creates a firewall rule

Problem: I don't know how make the installation routine configure postgresql.conf so that listen_addresses is set to the right value:
listen_addresses = '*'

Currently this has to be done manually. I didn't find a parameter in initdb that does this.
Question: Is there any way to automate the installation process so that listen_addresses is configured as needed? Can this be done using initdb?

The easiest solution is to append:
listen_addresses = '*'

to postgresql.conf after running initdb. As the last occurrence overrides earlier ones, this would even apply if the default entry were commented out.
Db cluster has to be restarted/reloaded after  that.


Problematic slashes and questions marks in unique index values What is it about the Phone values inserted below that SQL Server is treating them as identical in a unique index?

CREATE TABLE Phone
(
  Id int identity(1, 1) primary key,
  Phone nvarchar(448) not null
)
go

create unique index IX_Phone on Phone(Phone)
with (data_compression = page);
go

insert into Phone Values ('?281/?263-?8400');
insert into Phone Values ('281/263-8400');

select * from Phone;

drop table Phone;

I receive an error message:

  Msg 2601, Level 14, State 1, Line 13
  Cannot insert duplicate key row in object 'dbo.Phone' with unique index 'IX_Phone'. The duplicate key value is (?281/?263-?8400).

Your problem is that you passes unicode strings as non-unicode.

Your '281/263-8400' is a string of 15 characters, not 12, there are 3 non printable 8206 symbols, Left-to-right mark

select len('281/263-8400'); -- 15 !!!

Try this code where I pass the unicode values as unicode and see that there is no problem at all:

CREATE TABLE dbo.Phone
(
  Id int identity(1, 1) primary key,
  Phone nvarchar(448) not null
)
go

create unique index IX_Phone on Phone(Phone)
with (data_compression = page);
go

insert into Phone(phone) Values (N'?281/?263-?8400');
insert into Phone(phone) Values (N'281/263-8400');

select * from Phone;

And this is what your string '281/263-8400' really contains (dbo.nums is my table that contains natural numbers):

declare @t table(col1 nvarchar(100), col2 nvarchar(100));
insert into @t values (N'?281/?263-?8400',  N'281/263-8400');

select n, unicode(substring(col1, n, 1)), unicode(substring(col2, n, 1))
from @t cross join dbo.nums
where n <= 15;

Now what happens when you pass your unicode string as non unicode.

Your non-printable symbol 8206 is transformed into ?, that is how non-unicode strings works: every character that cannot be find in the corresponding codepage and represented as ascii code is substituted with question mark.

So for example if you use Latin Collation and try to compare Hebrew and Cyrillic characters (the same number of characters) as varchar they always be equal just because they are transformed to question marks while comparing as nvarchar they are different:

So if now you'll try to insert these 2 values (Hebrew + Cyrillic) into your Phone table passing them as non-unicode (without using N), only one of them will be inserted, and the other will be rejected by unique constraint.
And if you'll try to select from Phone, '?????' will be returned


Column alias in Postgres FROM clause? I'm reading through the Postgres documentation, the page on SELECT statements, and I ran across an aspect of aliases that I have never encountered.

In the section on FROM clauses, subheading alias, there is a sentence stating:

  If an alias is written, a column alias list can also be written to provide substitute names for one or more columns of the table.

There are no examples given in the documentation that I could find.

I know how to set up output names as aliases, but that doesn't appear to be the same thing.

The synopsis for SELECT includes the lines:

  ...
  SELECT [ ALL | DISTINCT [ ON (expression[, ...] ) ] ]
      * |expression[ [ AS ]output_name] [, ...]
      [ FROMfrom_item[, ...] ]
  ...

And defines from_item as:

  where from_item can be one of:

  [ ONLY ]table_name[ * ] [ [ AS ]alias[ (column_alias[, ...] ) ] ]
  (select) [ AS ]alias[ (column_alias[, ...] ) ]
  ...(other forms omitted)...

Note that from_item actually includes column_alias.

It makes sense to me that the ( select ) form can be given column aliases in the FROM clause (rather than only output_names, in the "expressions" of the SELECT statement), since the "columns" of the subquery will have been explicitly chosen in most cases and thus the sequence will be known.  So I would imagine in that case the column_alias values could simply be a list of names, and they would be matched up in sequence against the columns returned by the subquery.  (Though an example would be nice.)

However, how can column aliases be used for a table_name?  Do you have to know the exact sequence of columns defined in the table, or can you set an alias just for one or two of these in the FROM clause?

What if you only want to set a column_alias for one column with a very long name (and leave the other columns not aliased); is this possible?  (If so, is this Postgres specific?)

The column aliases there override the column names/aliases of the internal select subquery (derived table). The same way, they can override the column names of the table (whether it's base table, a view, a derived table or a cte does not matter at all).

So, the simple example will give an error:

select
    a, count_a              -- invalid here (have been overridden)
from
    ( select t.a, count(*) as count_a
      from t
      group by t.a
      order by count_a desc           -- count_a is valid here
      limit 8
    )
      as d (b, count_b) ;

but this will work:

select
    b, count_b              -- valid column aliases
from
    -- identical as above

the names a and count_a are valid inside the subquery (derived table) but not outside because they have been overridden by b and count_b.

  Do you have to know the exact sequence of columns defined in the table, or can you set an alias just for one or two of these in the FROM clause?

Yes, you do have to know the sequence of columns.

But you don't have to change all columns. Say the table has 5 columns. If you use:

select t.*
from table_name as t (a,b,c) ;

only the first 3 columns will appear with the new names (a,b,c). The 4th and 5th will show with their real names. You'll get an error if you provide more aliases than needed (eg. 6 aliases for a 5-column table).

  What if you only want to set a column_alias for one column with a very long name (and leave the other columns not aliased); is this possible? (If so, is this Postgres specific?)

Only if it's the first. Or by providing all the previous column names up to the column you want to alias with a different name.

  I suppose you can't just give an alias for the third column only, or something like that?

I don't know of any syntax to allow you to alias only the 3rd column, without providing the names of the 1st and 2nd column.

Overall, the usefulness of the feature is at least debatable when used for base tables. And the above query that overrides the names of just 3 of the possibly many columns reeks obfuscation and could be very well considered bad practice.

But the feature is provided because it's standard SQL and for completeness. It wouldn't make sense to have this only for subqueries and CTEs and not for other kinds of tables.

One case where it can be useful is (not with base table but with) the VALUES construct, where the columns get default names of column1, column2, etc. and this aliasing can be used to select more meaningful names:

select
    a, b
from
    ( values
         (1, 2),
         (2, 3),
         (3, 5)
    )
      as d (a, b) ;


Customized date range in sql We have got a customized quarter list from client which we need to take reference to get data in SQL. Could you please help.

30-Oct  29-Jan AS Quarter1
30-Jan  30-Apr AS Quarter2
01-May  30-Jul AS Quarter3
31-Jul  29-Oct AS Quarter4

If today falls between any of the quarter we need start date in date format
for example if today is Jan/02/2018 then it shall return start date as 30-10-2017 00:00:00 and end date as 29-01-2018 00:00:00
if today is Dec/30/2017 then it shall return start date as 30-10-2017 00:00:00 and end date as 29-01-2018 00:00:00

Assumption:

some batch work (either as part of a SQL batch, or perhaps a user-defined function) is acceptable (otherwise the batch code could be rolled into a couple additional CTEs)

A batch/CTE solution:

declare @mydate  datetime,

        @mymon   int,
        @myday   int,
        @myyr    int,

        @startyr char(4),
        @endyr   char(4)

select  @mydate = '01/02/2018'

-- break @mydate into numeric components to make it a little easier in the
-- next step to determine our start/end years

select  @mymon = month(@mydate),
        @myday = day(@mydate),
        @myyr  = year(@mydate)

-- determine our start/end years based on current year of @mydate; since we'll
-- be feeding these variables into a convert(datetime,<string>) construct, go
-- ahead and convert to char(4) here in order to minimize coding later

select  @startyr = convert(char(4), case when @mymon = 10 and @myday >= 30 then @myyr
                                         when @mymon in (11,12)            then @myyr
                                                                           else @myyr - 1
                                    end),
        @endyr   = convert(char(4), case when @mymon = 10 and @myday >= 30 then @myyr + 1
                                         when @mymon in (11,12)            then @myyr + 1
                                                                           else @myyr
                                    end);

-- use a CTE to build our list of quarters, using the @starty/@endyr variables
-- to create the datetime ranges that make up the quarters in which
-- @mydate date can fall within

with
quarters as

(select *
 from   (values ('Quarter1', convert(datetime,'10/30/'+@startyr), convert(datetime,'01/29/'+@endyr) ),
                ('Quarter2', convert(datetime,'01/30/'+@endyr),   convert(datetime,'04/30/'+@endyr) ),
                ('Quarter3', convert(datetime,'05/01/'+@endyr),   convert(datetime,'07/30/'+@endyr) ),
                ('Quarter4', convert(datetime,'07/31/'+@endyr),   convert(datetime,'10/29/'+@endyr) ) ) as q(qtr, qstart, qend)
)

-- now it's just a matter of finding which datetime range (aka quarter)
-- @mydate falls between

select q.qtr, q.qstart, q.qend

from   quarters q
where  @mydate between q.qstart and q.qend;

Some sample runs (changing the value of @mydate):

-- @mydate = '10/29/2017'

 qtr      | qstart              | qend
 -------- | ------------------- | -------------------
 Quarter4 | 07/31/2017 00:00:00 | 29/10/2017 00:00:00

-- @mydate = '10/30/2017'

 qtr      | qstart              | qend
 -------- | ------------------- | -------------------
 Quarter1 | 30/10/2017 00:00:00 | 29/01/2018 00:00:00

-- @mydate = '08/17/2016'

 qtr      | qstart              | qend
 -------- | ------------------- | -------------------
 Quarter4 | 31/07/2016 00:00:00 | 29/10/2016 00:00:00

-- @mydate = '01/23/2016'

 qtr      | qstart              | qend
 -------- | ------------------- | -------------------
 Quarter1 | 30/10/2015 00:00:00 | 29/01/2016 00:00:00

Here's a dbfiddle


SSIS Run Excel Macro Error I have an SSIS package that populates an Excel file. The package is run from a SQL Server Agent job.

It all runs fine, but now I have added a Macro to the Excel file. This macro causes the Package to fail when it is run.
Are there any permissions I need to add to allow the SQL Agent account to run the macro?

This is the error:

  Description: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. --->
  System.IO.FileNotFoundException: Could not load file or assembly
  'Microsoft.Office.Interop.Excel, Version=14.0.0.0, Culture=neutral,
  PublicKeyToken=71e9bce111e9429c' or one of its dependencies. The
  system cannot find the file specified.

After installing Excel, the error is as follows:

  The file name or path does not exist. • The file is being used by
  another program. • The workbook you are trying to save has the same
  name as a currently open workbook. at
  Microsoft.Office.Interop.Excel.Workbooks.Open(String Filename, Object
  UpdateLinks, Object ReadOnly, Object Format, Object Password, Object
  WriteResPassword, Object IgnoreReadOnlyRecommended, Object Origin,
  Object Delimiter, Object Editable, Object Notify, Object Converter,
  Object AddToMru, Object Local, Object CorruptLoad)

Solution:

https://stackoverflow.com/questions/881132/retrieving-the-com-class-factory-for-component-failed

In DCOMCNFG, right click on the My Computer and select properties.
Choose the COM Securities tab
In Access Permissions, click "Edit Defaults" and add Network Service to it and give it "Allow local access" permission. Do the same for \Users.
In launch and Activation Permissions, click "Edit Defaults" and add Network Service to it and give it "Local launch" and "Local Activation" permission. Do the same for \Users


sysadmin user can't connect to explicitly specified database I noticed some Failure Audit errors, in the Windows Application event log of the server that my SQL Server runs on, stating:

Event ID: 18456
Login failed for user 'DOMAIN\username'.
Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]

The user mentioned in the errors was in the sysadmin server role, and I checked that the user had a default database associated with it. The errors recurred every five minutes. Does anyone have any insight into this issue?

The SQL Server service itself and SQL Server Agent run as that account.

I checked whether I had any SQL Agent jobs on the server that ran every five minutes, and found one that was failing.  When I checked the job steps, one of them had a blank Database drop down.  I selected a database from the drop down, and that corrected the issue.  Now, after finding the root cause, the error makes perfect sense: that user definitely couldn't open an undefined database.


Collation conflicts between views and functions when using utf8mb4_unicode_ci collation Currently running MariaDB 10.3.24 and I really want to fully support the utf8mb4 unicode character set in my databases.
The problem I am running into is that when I try and set everything up to use the utf8mb4 character set and set all collation references to utf8mb4_unicode_ci I then have problems with views that have functions in them .... getting "collation mismatch" error on some but not all comparisons, e.g. this != that or this = that, etc. If I then set all the collation references back to utf8mb4_general_ci then all is fine ..... Is there something that I am doing wrong or is there some sort of work around that I have to do when creating views and functions when using the utf8mb4_unicode_ci collation ....
Thanks

Before defining Stored Routines, etc, establish the desired CHARACTER SET and COLLATION.  One way to do that is via SET NAMES.
Also check
SHOW CREATE DATABASE

utf8mb4_unicode_520_ci is newer than utf8mb4_unicode_ci.  (I don't know what collation changes were made by Unicode between their 4.0.0 and 5.2.0 versions.  You may never notice a difference.)


Select/Join multiple row values from Column A if dates in Column B are within 'x' seconds of each other I have the following Database structure and need a query to work in SQL Server 2008 and also SQL Server 2014.

Database: ASSETS

Table: COMPUTERS

   PK (GUID)      (nvarchar)   (nvarchar)
|    PC_ID    |  PC_Hostname  |  Win_Ver |
+-------------+---------------+----------+
| ABCDE-12345 |   ACMEINC_1   |   W732   |
| FGHIJ-67890 |   ACMEINC_2   |   W732   |
| QWERT-34567 |   ACMEINC_3   |   W764   |
| JHGFD-87654 |   ACMEINC_1   |   W764   |
| CVBNM-08643 |   ACMEINC_1   |   W1064  |

Table: SOFTWARE

    PK (GUID)      (GUID)      (int)   (nvarchar)         (datetime)
|    REG_ID   |    PC_ID    | SWType |  NAME_Reg |         DATE_Reg        |
+-------------+-------------+--------+-----------+-------------------------+
|  2WS3-8574  | ABCDE-12345 |    8   |   Bob     | 2018-02-19 15:30:10.980 |
|  D46G-2574  | ABCDE-12345 |    7   |   Admin   | 2018-02-19 15:30:12.001 |
|  7F4H-6523  | FGHIJ-67890 |    7   |   NULL    | 2018-03-01 11:02:02.954 |
|  MNDE-4567  | QWERT-34567 |    8   |   Joan    | 2018-11-23 09:28:59.685 |
|  8S22-1254  | QWERT-34567 |    7   | Warehouse | 2018-11-23 09:29:00.103 |
|  C9CC-1854  | JHGFD-87654 |    8   |   Barry   | 2018-11-23 09:46:21.321 |
|  JH21-6325  | JHGFD-87654 |    7   | Accounts  | 2018-11-23 09:46:21.332 |
|  109A-4785  | CVBNM-08643 |    8   |   Sally   | 2019-01-02 12:59:59.753 |
|  34NB-6855  | CVBNM-08643 |    7   |    HR     | 2019-01-02 13:00:00.211 |

I'm trying to query the SOFTWARE table for values in the 'NAME_Reg' column where not 'NULL'.

If two 'DATE_Reg' values are =< 5 sec apart they should be displayed in results on one row with the value matching 'SWType' of 8 in the 'Name' column, and the value matching 'SWType' of 7 in the 'Dept' column.

There will only ever by 2 entries in 'DATE_Reg' within a 5 sec time range of each other.

The results should display the corresponding 'PC_Hostname' linked on 'PC_ID', and the 'DATE_Reg' value corresponding to the 'SWType' value of 8 (Really I'd be ok with either of the two datetime values).

Desired Output

| PC_Hostname |   Name   |    Dept   |         DATE_Reg        |
+-------------+----------+-----------+-------------------------+
|  ACMEINC_1  |    Bob   |   Admin   | 2018-02-19 15:30:10.980 |
|  ACMEINC_2  |   Joan   | Warehouse | 2018-11-23 09:28:59.685 |
|  ACMEINC_1  |   Barry  |  Accounts | 2018-11-23 09:46:21.321 |
|  ACMEINC_1  |   Sally  |     HR    | 2019-01-02 12:59:59.753 |

I'm still very very new to SQL and the closest I've gotten is with the following code after lots of googling.

USE ASSETS
SELECT c.PC_Hostname,
    CASE
        s.SWType
            when '8' then s.NAME_Reg
    END AS Name,
    CASE
        s.SWType
            when '7' then s.NAME_Reg
    END AS Dept,
    s.DATE_Reg
FROM COMPUTERS c
    join SOFTWARE s ON (c.PC_ID = s.PC_ID)
WHERE s.NAME_Reg not like 'NULL'
ORDER BY c.PC_Hostname,s.DATE_Reg

However this results in the following output, which still has the Name & Dept values on alternating rows and has no time range matching. So after getting stuck at this point and not sure of a way forward I'm hoping someone on here could help.

| PC_Hostname |   Name   |      Dept     |         DATE_Reg        |
+-------------+----------+---------------+-------------------------+
|  ACMEINC_1  |   Bob    |     NULL      | 2018-02-19 15:30:10.980 |
|  ACMEINC_1  |   NULL   |     Admin     | 2018-02-19 15:30:12.001 |
|  ACMEINC_1  |   Barry  |     NULL      | 2018-11-23 09:46:21.321 |
|  ACMEINC_1  |   NULL   |   Accounts    | 2018-11-23 09:46:21.332 |
|  ACMEINC_1  |   Sally  |     NULL      | 2019-01-02 12:59:59.753 |
|  ACMEINC_1  |   NULL   |      HR       | 2019-01-02 13:00:00.211 |
|  ACMEINC_3  |   Joan   |     NULL      | 2018-11-23 09:28:59.685 |
|  ACMEINC_3  |   NULL   |   Warehouse   | 2018-11-23 09:29:00.103 |

You can join the software table to itself and use DATEADD() for the 5 second condition:

SELECT
    c.PC_Hostname,
    a.NAME_Reg     AS Name,
    b.NAME_Reg     AS Dept,
    a.DATE_Reg,
    b.DATE_Reg     AS DATE_Reg_b
FROM
    assets.software AS a
  JOIN
    assets.software AS b
  ON    a.PC_ID  = b.PC_ID
    AND a.SWType = 8
    AND b.SWType = 7
    AND a.DATE_Reg >= DATEADD(second, -5, b.DATE_Reg)
    AND a.DATE_Reg <= DATEADD(second, +5, b.DATE_Reg)
  LEFT JOIN
    assets.computers AS c
  ON   a.PC_ID = c.PC_ID
WHERE
      a.NAME_Reg IS NOT NULL
  AND b.NAME_Reg IS NOT NULL
  ;


An inconsistency was detected Am running asp.net application.Am searching records.if i click the view button i got this error,

  an inconsistency was detected during an internal operation in
  database(database name) on page(some page).please contact technical
  support.reference number 6.

I Googled and i got the result as to run DBCC CHECKDB.
I run the following command in sql server 2005,

DBCC CHECKDB (database name) WITH NO_INFOMSGS, ALL_ERRORMSGS

my result was something like this

  Table error: Object ID 1424372489, index ID 1, partition ID 374822652149760, alloc unit ID 93347675439104 (type In-row data). Page (1:72872) is missing a reference from previous page (1:72871). Possible chain linkage problem.
  Msg 8928, Level 16, State 1, Line 1

  Object ID 1424372489, index ID 1, partition ID 374822652149760, alloc unit ID 93347675439104 (type In-row data): Page (1:109577) could not be processed.  See other errors for details.
  Msg 8978, Level 16, State 1, Line 1

  Table error: Object ID 1424372489, index ID 1, partition ID 374822652149760, alloc unit ID 93347675439104 (type In-row data). Page (1:109578) is missing a reference from previous page (1:109577). Possible chain linkage problem.
  Msg 8976, Level 16, State 1, Line 1

  CHECKDB found 0 allocation errors and 10 consistency errors in table 'some table' (object ID 1424372489).

  CHECKDB found 0 allocation errors and 5 consistency errors in table 'some table' (object ID 1432300808).

  CHECKDB found 0 allocation errors and 19 consistency errors in database 'database name'.
  repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (database name).

This inconsistency error can be resolved by running the following command:

dbcc checkdb(database name,repair_allow_data_loss)


Why the deadlock in this scenario? I was going through the examples of deadlock exceptions online and found this link

As I understand, here we have two processes (session 1 and 2). The first one acquires lock on pd1, and second acquires on pd2. Then after that, first one want to read from pd2 and second one from pd1.

What I dont get is that why do we have a deadlock here. Wouldnt it work in a way that session updates and once it is done updating, it will release the lock on pd1, and with that session 2 will be able to read from pd1. Similarly the other way round? Why does the Update + Select has to be a single operation here?

Is it because they are part of the same transaction, hence within a transaction they wont let go of their locks acquired on any object?

Sybase ASE uses blocking locks for transaction isolation, meaning that readers block writers and writers block readers (unlike other databases which use versioning so that writers don't block readers and vice versa).

So each SELECT is blocked by the exclusive lock taken out by the earlier UPDATE on the table it is selecting from, since that update was done by a different session (it wouldn't block if it was the same session). Since neither SELECT can proceed now, there is a deadlock.


How can I allow part of a key to be null and enforce uniqueness of records with nulls? I have an Access table called Engagement with four fields:

Emp_id, Year, Week, Act_id

It records when an employee is/was engaged in an activity.
Each field is Number and each makes up part of the composite primary key.
The semantics of the application was that each entry (each Emp_id-Year-Week-Act_id combination) must be unique.
That is, while an employee will generally have different year-weeks and different activities, an employee can sometimes engage in the same activity on different year-weeks or even engage in different activities in the same year-week. Nulls were not allowed. Everything worked.

Now I need to expand/modify the semantics to allow unknown – or more aptly, not disclosed – Year-Week values associated with any given employee-activity.
Of course, attempting to enter a row with empty Year-Week results in
"Index or primary key cannot contain a Null value".
So I need a change to the table design.

One thing I tried was to convert the primary key index into a non-primary index by turning Primary off (and leaving Unique on) in the Indexes window.
This correctly prevents duplicate records where Year-Week values are non-empty -- but it allows duplicate records where Year-Week are empty.

For example, using the above non-primary, unique index, the following data are allowed:

Emp_id  Year  Week  Act_id
     7  2014    12      31  } Same activity,
     7  2015    22      31  } different dates.
     7  2015    33      32
     7  2015    40      33  } Same dates,
     7  2015    40      34  } different activities.
     7  2016     2      36
     7                  38  } Different activities,
     7                  39  } undisclosed dates.

And none of the following additions are subsequently allowed:

Emp_id  Year  Week  Act_id
     7  2014    12      31  } Both records are
     7  2015    33      32  } duplicates of above.

So far so good (behavior matches requirements). However, both the following additions are subsequently allowed, but should not be:

Emp_id  Year  Week  Act_id
     7                  38  } Both records are
     7                  39  } duplicates of above.

Why is that?

What is a good way around this problem of, on the one hand, allowing something equivalent to null for Year and Week and, on the other hand, constraining each Emp_id-Year-Week-Act_id combination to be unique?

I can think of two other (untried) solutions:

Choose an equivalent-to-null numeric value, such as zero or -1 and somehow explain this to users.
Convert the Year-Week field types from Number to Text and simply use "" (empty string) for null.

In your experience, what is a good solution in this kind of situation?

I'm aware of What's wrong with nullable columns in composite primary keys? and NULL value in multi-column primary key, which explain certain things but don't provide a solution.

The strongest advice I can give is to keep the primary key immutable unless it is absolutely unfeasible for your use-case. You appear to be describing 2 different datasets.

User-Actions taken during a given Period ( defined by the composite key of Year-Week )
User-Actions taken during an unknown Period

My initial impression is that the unknown period must needs be a different dataset. Surely if you don't know when an action has taken place you also do not yet know enough to move it from its staging environment to the table where User-Action-Period is the unique identifier. I don't know MS Access well enough to comment on the specifics of null keying in that environment, but I strongly recommend defining your data model for yourself in such a way that you know if you need to include the "equivalent-to-null" keyed records on the same table.
From your description, it seems that the problem occurs when attempting to insert multiple instances of a User-Action where the Period occurred is not known. Once again, if you do not know enough to uniquely identify the committed time of the User-Action, it is appropriate to stage the metrics you are recording about the same User-Action elsewhere until you can appropriately identify the relationship this User-Action has to the other similar User-Actions that occurred at another key-able time Period.
I see two paths forward:
If only one User-Action with "unknown" time-key is permissible
Example: The action is "pending" or "to-be-completed". The user will always be submitting data for a future / not-completed period.
Solution: Pick your favorite method of keying the time period as "equivalent-to-null" and make the key columns not-nullable. You may continue to store these datasets in the same table
Gotcha: If the user submits an action with a committed period while an unknown period exists for the same User-Action, you may run into a situation where data is committed where the period is known but other data already committed for an unknown period ( which happens to be the same one ) is left in the "unknown period" state and lost for that User-Action-Time record and the user doesn't immediately know why.
Multiple User-Actions with "unknown" time-keys are permissible
Example: Users are able to uniquely identify the same action type with a different identifier for an unknown Period.
Solution: You must define another way to key the data ( where the time the action occurs is a metric of the other multi-keyed record ). Allow the users to submit data to this separately stored dataset and merge it into your User-Action-Time period dataset using appropriate logic. It's more work, but there's no way around it if the datasets are bona-fide separate.


Regarding My career path I'm MySQL DBA having 4 years of experience.
My Question is:
1. Can I consider MySQL as long term technology.
2. Apart from MySQL which database I should learn, so that I can get job easily outside.

Regards,
Sudeep

The answer to could be really broad, but I'll try and keep it brief.

1.

MySQL as a long term technology is a really open-ended question - what do you mean by 'long' term? It's unlikely to go anywhere in the next few years, or even decade, but the market is constantly changing. Feeling too secure in anything probably isn't a good idea.

MySQL is still in the top rankings for RDMSs www.serverwatch.com , http://db-engines.com/en/ranking

So doesn't seem to be going anywhere. The fact it's free, and there's no looking like it's going to stop being supported, means that it's probably fairly stable in its position.

The questions you need to ask yourself are:
Do you have a job, and do you like it?
Do you want to do something else?
Can you learn new skills where you are, and what are the requirements there, what can you get training for?

The tricky thing with MySQL is how it's used

Lots of companies seem to expect MySQL DBA stuff as just something a really good PHP dev does. In my shop (where I'm a SQL Server DBA) the web team tends to look after the MySQL DBs, it's technically not in my remit (and I only muddle through with them if they need a hand).

Also, if a company is choosing to go open source, that's usually partly because they don't have a lot of money - making it less likely they'll have funding for a full time MySQL DBA (or at least won't be paying much).

This article talks about this more (it's a bit out of date, but what it's talking about doesn't seem to have changed).

Apart from MySQL, what DB should I learn?

Again, this is a bit of an open question: I could be flippant and ask back - what do you want to learn? It all depends on your reasons "Easily getting a job outside" is a bigger question (maybe take up being a woodsman, if working outside is your bag..!).

According to rankings Oracle and SQL Server are still right at the top with MySQL, so if it's just number of potential clients is your question - then they're the answer. Problem is, that's not so straightforward. Both of those markets already have lots of established DBAs - that's not to put you off, it's perfectly possible to start in the career, but it takes work. You need proven experience of systems in order to get jobs looking after them.

If you want to stick in the open source sphere, then investigate other open source systems people are using. Are you familiar with MariaDB? Since the split from MySQL it has been becoming increasingly more popular, and there are increasingly more reasons people are switching. Being already familiar with MySQL, this should be easier for you.

My personal opinion

Familiarising yourself with other RDMSs is always a good idea, but focus on something. Its rare a shop only has one RDMS and no others, even if they are legacy, or have other DBAs, it's always good to be able to help out and make yourself more invaluable - you can also learn more on the job.

I also think the question is not what RDMSs you should learn, but what other skills do you have, learning associated skills helps even more getting a doing a DBA job.

If you're sticking with MySQL or MariaDB, what's your PHP like?

Having a scripting language is always useful -
If you want to go into SQL Server (or use anything on Windows), consider learning PowerShell.

Choose a programming language to learn - what will depend on what area you want to go into (as a SQL Server DBA, I'm always trying to improve my .net and C#).

Having good project management skills/qualifications is another string to your bow.

Really, there's lots of things you can do, but there's no quick answers. You've got to research and find out what suits you.

Sorry... didn't keep it brief.