Need an Audit Log. Should I create an Audit Log Table or Audit Fields in each table? By no means am I a DB expert, but I do have experience with creating DBs. Not sure about the following though.

I want everything that happens in the system to be trackable. My client is dealing with lots of money and if mistakes are made, they need to be tracked. From both a DB and a developer perspective, what's easier: 1)

created_by
created_date
updated_by
updated_date

in 7-10 tables or

2) a dedicated table with

action_done
action_done_by
action_date

and then a secondary table with what the actions are.
id
action_type

Pros to 1: Easier to make sure you don't miss anything.
Cons to 1: Annoying to maintain, report on, etc. and so not clean.

Pros to 2: Oh so clean, easy to report on.
Cons to 2: Easier to miss something you want to log (though that's possibly true for 1, it's less likely because the fields are in each table and they need to be used for something.)

Is that it and I just need to make a decision one way or the other? Or is there a DB performance concern I should have or something else I'm missing?

Your two scenarios serve different purposes. The former is useful when you application needs to know the history of an entity, e.g. to display the balance sheet as of last December. See also "slowly changing dimensions" and "temporal tables".

The latter is closer to the true audit trail, which should be separate from the data it reports on, and probably even from the database it reports on -- otherwise that same [compromised] application that enters erroneous data can also modify the audit trail to disguise the change. Storing audit data separately from its source entities also benefits performance -- the application doesn't need to sift through days/months/years of historical data to get at the current records.

If you decide to store the audit trail in the database, at least ensure that the authorization ID that modifies the data cannot access the audit trail table, and the audit authorization ID cannot change the source data (also known as separation of duties).


Reverse auto_increment id existing column in phpMyAdmin I have imported csv file through phpMyAdmin into one table. I have set auto-increment field, as shown below, but of course it ordered it ascending:

+----+------------+------+
| id | date       | name |
+----+------------+------+
| 1  | 2015-02-28 | Mike |
| 2  | 2015-02-26 | Sara |
| 3  | 2015-02-22 | Luke |
+----+------------+------+

So now, when I add new user, it gives him id=4. But what I want is to reverse the order of the above table, to reverse id, so when I add new user, because of the date it will be ordered according to the date.

I cannot change the csv file, I need to do something in PhpMyAdmin to change the ids (auto-increment) - to reverse them.
It should look like this:

+----+------------+------+
| id | date       | name |
+----+------------+------+
| 1  | 2015-02-22 | Luke |
| 2  | 2015-02-26 | Sara |
| 3  | 2015-02-28 | Mike |
+----+------------+------+

How to achieve this? As I want when adding new user to give him the id=4, but the date will be todays date and in the whole table it will be sorted by date normally.

You can create a new table like the old one and insert the values in a different order, for example:

CREATE TABLE new_table like old_table;
INSERT INTO new_table (date, name)
SELECT date, name FROM old_table
ORDER BY date ASC, id DESC

Change the ORDER BY to whatever suits to you- I have ordered by date so it doesn't trust that the original data was right, you can order just by id DESC.

You can later delete the old table and rename the new table as the old table:

DROP TABLE old_table;
RENAME TABLE new_table TO old_table;

It can be done just using a table, but that is easier and lets you check that the contents are ok before changing the original table.

If you did something like this the ids will collide and the query would fail -it would require an additional column or the drop of the PK and two table rewrites instead of one:

SELECT max(id) INTO @maxid FROM my_table;
UPDATE my_table SET id = @maxid - id + 1;


Duplicate entries - condition on second table with join I have 2 tables (1. PLATE, 2. MOVEMENT).
First table contains some duplicate plate numbers.
Every plate number (Fields: PLATE_ID, PLATE_COUNTRY, PLATE_NUMBER) has an unique id, which exists in movement table (Fields: MOV_ID, MOV_ENTRYDATE, MOV_EXITDATE) too.
How can I find out all duplicate entries with the movement date, where MOV_EXITDATE in first entry = MOV_ENTRYDATE in second movement data.

Example:

PLATE_ID   PLATE_NUMBER   PLATE_COUNTRY
1          AS25           UK
2          AS25           UK
3          GG10           D
4          GG10           D
5          BB40           I

MOV_ID    MOV_COUNTRY   MOV_ENTRYDATE     MOV_EXITDATE
1         GB            2019-03-01        2019-03-05
2         GB            2019-03-05        0001-01-01
3         D             2019-02-01        2019-02-20
4         D             2019-02-21        0001-01-01
5         I             2019-01-01        0001-01-01

Result:

PLATE_ID   PLATE_NUMBER   PLATE_COUNTRY    MOV_ENTRYDATE     MOV_EXITDATE
1          AS25           UK               2019-03-01        2019-03-05
2          AS25           UK               2019-03-05        0001-01-01

Note: Plate AS25 is duplicate in table PLATE and MOV_EXITDATE = MOV_ENTRYDATE.

I'm not successful with my query below.

Select PLATE_ID, PLATE_NUMBER, PLATE_COUNTRY, MOV_ENTRYDATE, MOV_EXITDATE
from PLATE t

inner join (Select PLATE_ID, PLATE_COUNTRY, count(PLATE_NUMBER) from PLATE
group by PLATE_ID, PLATE_COUNTRY
having (count(PLATE_NUMBER) > '1')) k
on t.PLATE_ID = k.PLATE_ID and t.PLATE_COUNTRY = k.PLATE_COUNTRY

inner join (Select MOV_ID, MOV_COUNTRY, MOV_ENTRYDATE, min(MOV_EXITDATE) as minexitdate
from MOVEMENT
group by MOV_ID, MOV_COUNTRY, MOV_ENTRYDATE) p
on t.PLATE_ID = p.MOV_ID and t.PLATE_COUNTRY = p.MOV_COUNTRY

inner join (Select MOV_ID, MOV_COUNTRY, MOV_EXITDATE, max(MOV_ENTRYDATE) as maxentrydate
from MOVEMENT
group by MOV_ID, MOV_COUNTRY, MOV_EXITDATE) p
on t.PLATE_ID = p.MOV_ID and t.PLATE_COUNTRY = p.MOV_COUNTRY

where minexitdate = maxentrydate

Thanks, for every helpful tip.

Good day.

Using the WITH syntax your subqueries may be more readable.

WITH PLATE_MOVEMENT AS (
    SELECT P.PLATE_ID, P.PLATE_NUMBER, P.PLATE_COUNTRY,
           M.MOV_ENTRYDATE, M.MOV_EXITDATE
    FROM PRUEBA.PLATE P INNER JOIN PRUEBA.MOVEMENT M
         ON (P.PLATE_ID = M.PLATE_ID)
)
SELECT A.PLATE_ID, A.PLATE_NUMBER, A.PLATE_COUNTRY,
       A.MOV_ENTRYDATE, A.MOV_EXITDATE
FROM PLATE_MOVEMENT A INNER JOIN PLATE_MOVEMENT B
     ON  A.PLATE_NUMBER = B.PLATE_NUMBER
     AND A.PLATE_COUNTRY = B.PLATE_COUNTRY
     AND (A.MOV_EXITDATE = B.MOV_ENTRYDATE OR B.MOV_EXITDATE = A.MOV_ENTRYDATE)
-- optionally an order by
ORDER BY A.PLATE_COUNTRY, A.PLATE_NUMBER, A.MOV_ENTRY_DATE

With result

PLATE_ID    PLATE_NUMBER PLATE_COUNTRY MOV_ENTRYDATE MOV_EXITDATE
----------- ------------ ------------- ------------- ------------
          1 AS25         UK            03/01/2019    03/05/2019
          2 AS25         UK            03/05/2019    01/01/0001

  2 record(s) selected.

Quite obscure will be the condition

A.MOV_EXITDATE = B.MOV_ENTRYDATE OR B.MOV_EXITDATE = A.MOV_ENTRYDATE

If omitted the second part, the last row in the chained collection of historic rows will be omitted.  If omitted the first part, the first row in the chained collection of historic rows will be missing.


Using SQL Profiler on SSIS package Is it possible to use SQL Server Profiler to trace a running SSIS package? I tried to do that, but all I can see in the trace is executing dp_sqlagent_... and the other internal commands! None of the tables/script that the package is working on displayed in the trace.

I tested different trace templates but had not much lock. Have you got any experience in this area?

Thank you.

Cheers,
Nazila

If you aren't seeing the activity in your trace, it may not be getting that far. You'll want to turn up some logging for your SSIS package.


How to loop through tables dynamically and INSERT INTO a temporary table in MSSQL I have an MSSQL (2016) DB with multiple tables for accounts of customer that have similar names like bandwidth_timeseries_ACC_1111111, bandwidth_timeseries_ACC_222222 etc. They are identical to each other in design and data types. In addition I have a control table with the following structure:

AccountID      Report_Name   Appear_In_View
111111       QUARTERLY REPORT   1
222222       QUARTERLY REPORT   0
[...]

And in the loop should be included those tables belonging to those accounts where the bit "Appear_In_View" =1 only. Also in each physical table (like bandwidth_timeseries_ACC_1111111) there is a column named "Most_Recent_Data" which is also a bit (0 or 1) and only lines where it's set to 1 should be included.
All the lines from each table should be inserted into a temp table that unions the physical table.

Up until today I had an SP which performed a similar thing only by creating Views that UNION all tables that match the above criteria, however I was advised of a possible performance problems down the road because the union query is growing rapidly in length and that using small INSERT INTO queries in a loop are a better solution.

I know the implementation should be done using T-SQL but I'm not very proficient in writing in this language and would appreciate any assistance that can be offered. Thanks.

If you want to avoid a union, you can loop using a cursor.

So, first you define your cursor that will list all the tables you want to query (let's assume you have all your data tables listed somewhere (system tables or config)).

And then loop doing :

create a dynamic SQL statement with the name of the table you retrieved
execute this statement

So something like:

declare @sql varchar(max)
declare @data_table varchar(50)
declare data  cursor for
    select data_table   -- name of the table you have to query
    from somewhere      -- where these table names are stored

open data
fetch next from data into @data_table

while (@@fetch_status = 0)
begin
    -- result_table = name of the table you want the results to be stored
    -- control_table = name of the control table you mentioned
    set @sql = 'insert into result_table
                select *
                from '+@data_table+'
                where AccountId in (select AccountId
                                    from control_table
                                    where Appear_In_View = 1)
                and Most_Recent_Data = 1'
    exec(@sql)
    fetch next from data into @data_table
end

close data
deallocate data

EDIT : with new inputs from Ariel Hayoun
Try this kind of where clause if (and only if) there is one line te retrieve:

set @sql = 'insert into result_table
            select *
            from '+@data_table+'
            where AccountId in (select AccountId
                                from control_table
                                where Appear_In_View = 1)
            and Retrieved_At = (select max(Retrieved At) from '+@data_table +')'

If you have multiple lines per tables (one line per account id for instance), you may be able to achieve what you want using row_number() function


Can Select but not Insert from SQL Server to MySQL I'm having a problem with inserting data from SQL Server to MySQL. I saw this syntax that helped others:
EXEC master.dbo.sp_addlinkedserver @server='MYSQL', @srvproduct='MySQL',
@provider='MSDASQL', @provstr='DRIVER={MySQL ODBC 5.1
Driver};SERVER=HOST;Port=3306;USER=uid;PASSWORD=pw;OPTION=3;DATABASE=mydb;

but the error is still:

OLE DB provider "MSDASQL" for linked server "MYSQL1" returned message "[MySQL][ODBC 5.1 Driver][mysqld-5.5.44-MariaDB-log]Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.".
Msg 7343, Level 16, State 2, Line 1
The OLE DB provider "MSDASQL" for linked server "MYSQL1" could not INSERT INTO table "[MSDASQL]".

It says here that the BINLOG_FORMAT is equal to STATEMENT but when I checked it, it is set to MIXED.
Found out a solution for these, they said that the BINLOG_FORMAT must be ROW but then I'm having a doubt or difficulty to edit in my.cnf because I don't know what will happen if I changed the binlog format from mixed to row.
How can I resolve this problem?

Found out a solution for these, they said that the BINLOG_FORMAT must be "ROW" but then I'm having a doubt or difficulty to edit in my.cnf because I don't know what will happen if I changed the binlog format from mixed to row.

Try it. If MySQL is going to eat your data, it's not going to care about that option -- it'll just plow forward regardless of your configuration. BINLOG_FORMAT says explicitly

  If you are using InnoDB tables and the transaction isolation level is READ COMMITTED or READ UNCOMMITTED, only row-based logging can be used. It is possible to change the logging format to STATEMENT, but doing so at runtime leads very rapidly to errors because InnoDB can no longer perform inserts.

That means you have two options, either use the default isolation level of REPEATABLE READ or set the option.

On a side note, MySQL Isolation Levels are pretty borked and probably don't do what you want anyway


Update a unique column with an unknown number of values I have a table with three columns, ID, a foreign key field and a text field (unique). The user should be able to update all text fields where the FK has a certain value.

The problem is that the user can enter as many values and in whatever order he likes. If he enters less than there are in the database all of them should be removed and replaced with the new ones, for example:

The names are already in the database: John, Amy and Joe.

The user wants to update these names with the following: Amy and Jessica. Which leads to an error (because he is trying to update the value John with Amy when Amy already exists) and a problem (if it would work): the value Joe would not be deleted.

Because of this I see myself forced to have two statements: DELETE and then INSERT. The problem with this is that if one of the insert statements fails, I will lose the data that was in the database initially (John, Amy and Joe).

I've tried setting up a transaction to solve the problem so that I could rollback if something failed. However, this leads me back to the original problem: Integrity constraint violation: 1062 Duplicate entry 'John' for key 'Name'.

The names correspond to items whose characteristics are stored in a separate table, since each item can have various names.

What does my database design need to look like for this problem not to occur?

I've asked this question on StackOverflow already, and it was suggested I should revisit my database design. Here is the original question (with code): https://stackoverflow.com/questions/31727353/update-a-unique-column-with-an-unknown-number-of-terms

CREATE TABLE Items
(
ID int NOT NULL PRIMARY KEY AUTO_INCREMENT,
Characteristics varchar(255)
)

CREATE TABLE Names
(
ID int NOT NULL PRIMARY KEY AUTO_INCREMENT,
ID_ITEM int,
Name varchar(255) UNIQUE
)

Your requirements:

  The problem is that the user can enter as many values and in whatever order he likes. If he enters less than there are in the database all of them should be removed and replaced with the new ones

suggest that a reasonable option would be your idea:

DELETE all the rows with same FK,
then INSERT all the news ones,

both (DELETE and INSERT statements) in one transaction. I tried in SQLdiddle and it worked just fine:

CREATE TABLE Names
(
ID int NOT NULL PRIMARY KEY AUTO_INCREMENT,
ID_ITEM int,
Name varchar(255) UNIQUE
) ;

INSERT INTO Names
  (ID_ITEM, Name)
VALUES
  (1, 'John'),
  (1, 'Amy'),
  (1, 'Joe') ;

START TRANSACTION ;
    DELETE FROM Names
    WHERE ID_ITEM = 1 ;

    INSERT INTO Names
      (ID_ITEM, Name)
    VALUES
      (1, 'John'),
      (1, 'Joe') ;
COMMIT ;

Then:

SELECT * FROM Names ;

returns:

ID   ID_ITEM   Name
--------------------
4     1        John
5     1        Joe


Joins in MySQL database I am a newbie to the MySQL database.I want to know different types of joins in MySQL and any link or any other kind of documentation with examples which will clear the doubts will highly appreciable.

Joins are a concept of Relational Algebra, you can find some information on wikipedia.
Join is considered an additional operator.


Why are execution plans wildly different when selecting 1000 rows vs 2000? Consider the queries below.  The only difference is 1000 vs 2000 rows returned.

SELECT TOP 1000 lwa.Message INTO #foo
FROM dbo.LogWidgetsAPI lwa (NOLOCK)
ORDER BY lwa.TimeStamp

vs

SELECT TOP 2000 lwa.Message INTO #foo
FROM dbo.LogWidgetsAPI lwa (NOLOCK)
ORDER BY lwa.TimeStamp

However, 1000 rows are returned in under a second, while the query with 2000 rows takes significantly longer.

The query plan for the first query is reasonably simple:

but the 2nd query is using parallelization:

What would using just 1k rows more force parallelization?

P.S.  The table contains over 6 million records and TimeStamp column is indexed.

As per other comments, it sounds like the cost threshold for parallelism is too low, and is making queries go parallel even when the process to do so/ gather the streams makes your process take longer.

Try setting that to 50, or experimenting with different numbers.

Also, worth a read: https://www.brentozar.com/archive/2017/03/why-cost-threshold-for-parallelism-shouldnt-be-set-to-5/


How to query Last inserted row for each group in oracle 10 I have two tables about users where I want to join two tables and show the latest remarks of each user

bellow is table

I want to query the latest remarks for each user (1 remark per user)
bellow query I have written but it returns multiple remarks per user if date and time is the same for remarks

SELECT a.cust_id,b.remarks,b.contact_date,b.contact_time FROM customers a,(select * from customer_review
where (cust_id,contact_time,contact_date) in (select cust_id, max(contact_time),max(contact_date) from customer_review group by cust_id)
) b WHERE  a.cust_id=b.cust_id;

Please give any solution to query 1 remark per user
(Note: sorry I cannot alter column details if any)
Live link Oracle Live

What you want to do is something like this with ROW_NUMBER() and PARTITIONing by cust_id and ORDERing BY contact_time DESC so that the first one will be the latest. A fiddle is available here - Oracle has microsecond precision, so it's very unlikely that you'll get simultaneous remarks by the same customer at the same time.

Create and populate customers table:

CREATE TABLE customers
(
  cust_id INTEGER NOT NULL,
  cust_name VARCHAR (25) NOT NULL
);

INSERT INTO customers VALUES (1234, 'John Doe');
INSERT INTO customers VALUES (1235, 'Ram');
INSERT INTO customers VALUES (1236, 'Rahim');

Do the same for customer_review:

CREATE TABLE customer_review
(
  review_id VARCHAR (25) NOT NULL,
  cust_id   INTEGER NOT NULL,
  remarks   VARCHAR (250) NOT NULL,
  contact_date DATE NOT NULL,
  contact_time TIMESTAMP NOT NULL
);

INSERT INTO customer_review VALUES
('NEW123', 1235, 'I am new user',
TO_DATE('2019-10-27', 'yyyy-mm-dd'), TO_TIMESTAMP('2019-10-27 06:10:13.278',  'yyyy-mm-dd hh24:mi:ss:ms'));
INSERT INTO customer_review VALUES
('CUST123', 1234, 'This is awesome product',
TO_DATE('2019-10-26', 'yyyy-mm-dd'), TO_TIMESTAMP('2019-10-26 11:15:15.123', 'yyyy-mm-dd hh24:mi:ss:ms'));
INSERT INTO customer_review VALUES
('CUST124', 1234, 'This is not good product. I have changed my mind now',
TO_DATE('2019-10-27', 'yyyy-mm-dd'), TO_TIMESTAMP('2019-10-27 16:25:15.567', 'yyyy-mm-dd hh24:mi:ss:ms'));
INSERT INTO customer_review VALUES
('CUST125', 1234, 'After upgrade, it is now working fine!',
TO_DATE('2019-10-27', 'yyyy-mm-dd'), TO_TIMESTAMP('2019-10-27 19:52:56.345', 'yyyy-mm-dd hh24:mi:ss:ms'));

Then run the following SQL:

SELECT c.cust_name, t.cust_id, t.remarks FROM
(
  SELECT
    ROW_NUMBER() OVER (PARTITION BY cust_id ORDER BY cust_id ASC, contact_time DESC) AS rn,
    *
  FROM customer_review
) t
JOIN customers c
  ON t.cust_id = c.cust_id
WHERE t.rn = 1;

Result:

CUST_ID     REMARKS                                  CUST_NAME
   1234     After upgrade, it is now working fine!   John Doe
   1235     I am new user                            Ram

(after edit - these are now the correct answers - the remark by John Doe is his latest one as should be the case! Ram has only made one remark so by definition, it is the latest).

A few words of advice. You have your customers table with a plural and customer_review in the singular - pick one or the other and stick to it (you can search for "SQL style guide" and choose one you like. Personally, I recommend singular - a table is a set of things and therefore singular.

Next time you post, could you please include your table definitions as DDL text (CREATE TABLE xxxx (field_1 type_1...);) and your data as DML text (INSERT INTO xxxx VALUES (val_1, val2...);). Here are a few reasons why posting screenshots is not the best approach for questions on DBA.SE. There are a few articles on how to ask questions here on my profile - you might want to take a look?

Finally, I recommend that you use an integer for review_id - it makes sorting easier - otherwise you might be faced with having to use messy code like

SELECT CAST(SUBSTR('CUST1235',  5, LENGTH('CUST1235')) AS SMALLINT) AS no;

to extract the INTEGER from the review_id field.

It is possible to solve this problem using the Oracle ROWNUM pseudo-column, but this is non-standard and it's better to develop good habits - and it will make your application more portable in the event that you/your company decide to do this. I hope this has helped - if it doesn't correspond to your requirements, please let me know and we can try and modify it. p.s. welcome to the forum! :-)

Edit:

I finally got the Oracle fiddle working here - to microsecond precision.

Another point that struck me on rereading the question and my answer. You don't need to declared a contact_date field - it can be easily derived from the contact_time field. The data (date in this case) is therefore only stored in one field and one field only - which is conformant with RDBMS best practice.

There are two ways of doing this

You can use the TO_DATE function when calling the table. Your table will now look like this (note contact_date is commented out. You then just call TO_DATE(contact_time) when you just want to get the date (example shown in the fiddle - table customer_review_bis).

CREATE TABLE customer_review_bis
(
  review_id VARCHAR (25) NOT NULL,
  cust_id   INTEGER NOT NULL,
  remarks   VARCHAR (250) NOT NULL,
  -- contact_date DATE NOT NULL,
  contact_time TIMESTAMP NOT NULL
);

You can also use a GENERATED AS (aka COMPUTED BY or CALCULATED) column. These are very convenient and once defined, you have an effective guarantee that the date will always match the value in contact_time. This functionality used to be performed by TRIGGERs - it really is worth learning about these. The syntax column_name [datatype] [GENERATED ALWAYS] AS (expression) [VIRTUAL] is described here (oracle-base is a super site). Your table definition now becomes (also shown in fiddle - customer_review_ter):

CREATE TABLE customer_review_ter
(
  review_id VARCHAR (25) NOT NULL,
  cust_id   INTEGER NOT NULL,
  remarks   VARCHAR (250) NOT NULL,
  contact_time TIMESTAMP NOT NULL,
  contact_date DATE GENERATED ALWAYS AS (TO_DATE(contact_time)) VIRTUAL
);  -- you just treat contact_date as a normal field when you call it, in
    -- exactly the same way as you did in your original example - table
    -- customer_review (first one) in the fiddle.

If you choose the GENERATED AS, you have to slightly modify the INSERTstatement by adding the fields that are updatable:

INSERT INTO customer_review_ter
(review_id, cust_id, remarks, contact_time)
VALUES (.... data for specified fields ....);

Finally, as @EdStevens says, Oracle 10 is EOL (End of Life) - you really should think about upgrading!


purge worker, purge coordinator, shutdown handler stuck I can't make out if the following is a problem or not, and what the solution might be.

show processlist;
+--------+------------------+-----------+-------------------+---------+------+--------------------------+------------------+----------+
| Id     | User             | Host      | db                | Command | Time | State                    | Info             | Progress |
+--------+------------------+-----------+-------------------+---------+------+--------------------------+------------------+----------+
|      2 | system user      |           | NULL              | Daemon  | NULL | InnoDB purge worker      | NULL             |    0.000 |
|      1 | system user      |           | NULL              | Daemon  | NULL | InnoDB purge worker      | NULL             |    0.000 |
|      3 | system user      |           | NULL              | Daemon  | NULL | InnoDB purge worker      | NULL             |    0.000 |
|      4 | system user      |           | NULL              | Daemon  | NULL | InnoDB purge coordinator | NULL             |    0.000 |
|      5 | system user      |           | NULL              | Daemon  | NULL | InnoDB shutdown handler  | NULL             |    0.000 |
| 175658 | jma_staging_user | localhost | autismontario_civ | Query   |    0 | Init                     | show processlist |    0.000 |
+--------+------------------+-----------+-------------------+---------+------+--------------------------+------------------+----------+`

The system user processors seem stuck and haven't changed for the last 20 minutes that I've looked.

Server version: 10.3.11-MariaDB MariaDB Server

These threads are not a problem as they are normal background tasks with nothing to do at the moment.


How to do While Loops? How can we use while loops in MySQL? My test script:

BEGIN
    SELECT 0 INTO @n;
    WHILE @n < 10 DO
    SELECT @n;
    SET @n := @n +1;
    END WHILE;
END;

But it has syntax errors. I'm running the loop using the SQLyog client in a standard query window. The syntax errors are of the following form:

  Error Code: 1064
  You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near...

I've also tried to use the while loop example provided by https://dev.mysql.com/doc/refman/5.6/en/while.html but it still didn't work.

Which part of the script is wrong? (Using MySQL 5.6.)

I'm trying to make a 6 month moving average of some data so I was hoping that a while loop would be able to append the "new" 6 month average onto the "old" 6 month average through each iteration of the loop.

You can't do a for loop in an SQL editor without a stored procedure. I use MySQL Workbench to make this.

A quick stored procedure should do the job:

DROP PROCEDURE IF EXISTS proc_loop_test;
CREATE PROCEDURE proc_loop_test()
BEGIN
  DECLARE int_val INT DEFAULT 0;
  test_loop : LOOP
    IF (int_val = 10) THEN
      LEAVE test_loop;
    END IF;

    SET int_val = int_val +1;
    SELECT int_val;
  END LOOP;
END;


Sharded Mongodb Communication I would like to know what ports I need have open for a sharded mongodb cluster. For example does the mongos need to be able to accapt connections to and from mongoc. I dont want to have to many open ports that I dont need to have.

The TL;DR versions:

You should assume that all mongod processes (including config servers) and all mongos processes need to talk to each other.  Naturally, this only applies to the actual database port (27017 default) and not the HTTP console (+1000) or REST if you have that enabled.

More verbosely:

The mongos will need to talk to every member of each shard (usually a replica set) for reads and writes.  The mongos also need to talk to the config servers - that is where it gets its config from, and how it does locking, keeps admin users and more.

The members of a replica set all need to talk to each other (replication/elections).  All primaries need to talk to the config servers as well as to other primaries in other shards.  Since all secondaries can become primary, then they all should have the same ports open as the primaries.

Also, make sure the permissions are bi-directional, without going into a lot of details, the origin of connections often surprises people (pull versus push in chunk migrations for example).

In fact, the only processes I can think of that you may get away with restricting ports on are the Arbiters - they will only need to be able to talk to the members of the set and can never become primary.  However, the mongos does ping it as part of a health check thread, so in the end I think the simplicity of a policy to cover all would be more benefit than the couple of ports you can omit for the arbiter special case.


Is there a simple, safe and low cost way to implement a (limited) database to be outside (close) IT control? This question is a spin off of the discussion here (What are the risks of building a database outside IT control?).

Background: I'm a risk guy that builds little betas, mostly in Excel-VBA. I have a lot of freedom in this 'beta mode'. People ask me things, I build it, they test, it crashes, I fix, it crashes, we change the specs, I rebuild it, they start using in a daily basis, it crashes, I fix it, specs, rebuild, etc..,etc. Lots of betas don't work and we just let it go. For the surviving ones, one sunny day we all realize the whole thing is stable enough, its minimally documented, and they put in a list that one day one real programmer will integrate into some new ideal system to come.

I realize this is problematic for IT (and really for everybody in the end) and that's one of the reason I want to start building simple SQL databases which make the whole thing way less messy then a db hidden in a branch of excel files.

Now, my IT tells me its policy, as in many places, that mortal user like me should not build dbs.

My question is: What's the set up I can offer that is simple, safe and low cost to implement from IT perspective? One idea is just to put it in a separate PC but I do need people in the network to be able to get data for the beta thing to work. Otherwise it can be confined to the hard drive.

Here's my view of the situation:

The data you are working with already have a source; they are extracted from some data mart or "system of record" and given to you, probably as a CSV file or something, which you load into Excel and build your models. Subsequently, there is already a database that has the data you need. Further, there is probably a bunch of development and QA (that is, non-production) versions of this database, to which it is much easier to get access.

If that is so, your best chance would be to discover where your data are coming from (if you don't already know) and ask your IT to give you access to a development copy of that database. Since they already maintain it, there won't be much extra effort required on their side and they might be less reluctant to help you.


Need a stored procedure to check and update row when it does not already exists. Need a stored procedure that will check and then update the rows (with username & email) except the id (auto incremented with a unique one).

For example, if id = 5 has, the username abc and the email is def@ggg.com and the id is unique.  Before it updates that row, it has to check the table to see if the username and email already exists.  If anybody already has that username and email the table is not updated.

Please provide a stored procedure for this issue.

without any more information it is difficult to give you the "right :)" answear. but what i can tell you, check:

http://www.techrepublic.com/article/defining-sql-server-constraints-with-tsql/6181836

and ...

When you specify a PRIMARY KEY constraint for a table, the SQL Server Database Engine enforces data uniqueness by creating a unique index for the primary key columns. If a PRIMARY KEY constraint is defined on more than one column, values may be duplicated within one column, but each combination of values from all the columns in the PRIMARY KEY constraint definition must be unique.

See more on http://msdn2.microsoft.com/en-us/library/ms175132.aspx here


How can I create a copy of postgis db from one windows machine to another I have postgres 9.3 database with postgis extension.
I want to create a new database on different computer and import all the tables from the source database.

In mssql it would be simple , just backup -> restore.

In postgres when I try to backup and restore using pgAdmin i keep getting different problems.

The PostGIS tables (with the geometries columns) are not being backedup
I get errors when resoring that sequence does not exits

Is there a simple way / tool on windows to backup / restore postgres db with gis extension ?

As @a_horse_with_no_name wrote pg_dump and pg_restore cope with PostGIS things without problems. At least since PostGIS 2.0, previously (before PostGIS became an extension) you might had to change paths of the c-library used for geometry-functions.

In PostgreSQL since 9.x ist is also fairly easy:

backup at the original databse with pg_dump
at the new system:

create a new database
login to that new database and run CREATE EXTENSION postgis;
restore the backuped file with pg_restore to the new database

That works for me as well on windows as on linux, you could even use PgAdmin for it.

Note: the version of pg_dump of the original (backed up) system may not be newer than of the new system - see https://stackoverflow.com/q/12836312/8957103


Create postgres schema in db with multi user in AUTHORIZATION Is it possible to create a schema that is owned or accessible by more than one user in the same database?

I was reading postgres create schema documentation and postgres alter schema documentation and there is no syntax to provide more than a single user.

I was also googling for such a solution, either in simple or hard way and found nothing, not even a single point to any workaround.

Could you, please, point me to a solution or possible ways to workaround that, or make me certain, that such a thing is not possible in any way?

Owned by more than one user: no
Accessible by more than one user: yes

To give a user (or role) access to the tables in a schema you can use something like this:

grant all on schema foobar to role_name;
grant all on all tables in schema foobar to role_name;
grant all on all sequences in schema foobar to role_name;
grant all on all functions in schema foobar to role_name;

The grant all on schema grants the two privileges usage and create on that schema. To quote the manual: "allows access to objects contained in the specified schema (assuming that the objects' own privilege requirements are also met). Essentially this allows the grantee to "look up" objects within the schema". But this does not grant the privilege to select data from a table within the schema. So all of the four statements above are necessary. If you don't want the users to create new objects, then you would use grant usage on schema ....

It is probably easier to grant this to a role and then grant that role to the individual users if you need to do this for more than one user.

If you want to extend the grants to any newly created table (or other objects), you need to change the default privileges on the schema:

alter default privileges in schema foobar grant all on tables to role_name;
alter default privileges in schema foobar grant all on sequences to role_name;
alter default privileges in schema foobar grant all on functions to to role_name;
alter default privileges in schema foobar grant all on types to role_name;

Don't forget to commit those statements if you are not running in autocommit mode.

Of course if you only want those users to access the objects replace the ALL keyword with SELECT or SELECT,INSERT,UPDATE depending on the object type and what you want to allow them.

You probably also want to set the default search_path for those users (if they only need to access that single schema) to avoid having to fully qualify the object names


Why does phpMyAdmin execute all queries with an apostrophe? For example, if I use the GUI to perform an INSERT, the console will log the following execution:

Why does each item have apostrophes (aside from NULL)?

I know that I can do a  literal INSERT via terminal or PHP without the apostrophes, especially when referencing the table or columns (and several tutorials only apply apostrophes around the insert values, not table or column references). I know that this isn't exactly a, "help me question" but I am very curious if there's a reason behind this. Thanks.

Those around values are single quotes - ' and denote string values. Numbers should not be quoted if they are supposed to go to a numeric type column but in PhpMyAdmin every input field is textual by default so it probably just tries to stay safe with quoting the '5' too, MySQL can convert the type when needed.

The other type are backticks ` and it is a different character. MySQL uses it to quote identifiers (names of column, tables, indexes etc.). It is not needed when you use normal identifiers like name or email but it is needed for others:

if you need to use a reserved word as an identifier - `insert` or `order` - not a good idea to create tables with such columns, but sometimes it already exists and you just need to query it.
when you want to use complex names - `column named X` - again better not to name your columns like this, but it is often used for column aliases in selects

.

select col1 as `column 1`, col2 as `column with diacritics áéí` from table;

For PMA it is just simple to quote every identifier than to check if it is safe or not to go unquoted.


Is it possible to assign sql logins into a group? This may be a bit too elementary question for this site but is it possible to control SQL Server authenticated logins using groups? So is it possible to assign sql logins under groups and give rigths for these groups? If so, are these groups only seen for an instance in question?

When working with SQL Logins you "group" them by using roles within SQL Server. Roles are created at the database-level but with SQL Server 2012 you will be able to create them at the server-level as well.

Once the role is created you can assign specific permissions to that group and it will be applied to the logins that are member of that role. The roles are localized to the instance you are under and are not visible or accessible to remote instances. You will only be able to add the logins in that instance of SQL Server to the roles.


Geo Route - Best way/db to store route path We are developing an app in that UI need to show a path where a truck driver drives the truck.
Driver mobile will continuously send (example every 10 seconds) their live location.
Which database is more suitable for storing location path (polylines).
Our team has experience of Mongo (with Geodata), Redis, MySQL
Is there any best database to store this kind of Geodata?

Much depends on what you mean by "storing a route path"
(a) Do you mean storing the geographical shape of the road segments traversed by the truck ? That assumes you do a form of map-matching, i.e. correlate the GPS locations sent by the trucks with your road database.
(b) Do you mean constructing a track from the GPS points you receive from each truck ?
(c) Do you mean just storing the GPS coordinates in a table ?
You can use any database to store geographical information, whether that is individual points or complex shapes ... For points, a couple of X and Y numeric columns is all you need. For lines, you can use any of the available standard textual representations: OGC WKT, GML, GeoJson, even KML. They are typically stored in CLOB structures due to the potential size and complexity (essentially the number of vertices).
The real question is about the processing you do to construct those geographical shapes. Points (case c) are trivial. Constructing a line from points as a GPS track (case b) is more of an effort: building say a GeoJSON representation is easy: it just means adding a new point to the JSON string. What requires work is the calculations to throw out bad locations - too far off track to be anything but errors.
Doing the map matching (case a) is yet more work as it requires locating the proper road segment for each GPS location - considering the approximation of the coordinates and the history of preceding road segments. And of course you need a complete database of all road segments in the geographical area your application covers. That requires a fairly advanced database that has proper spatial types with indexing and spatial predicates. Few databases have this: Oracle has that as native capabilities. PostgreSQL has it via the PostGIS extension.
Then there is the question about what the application is all about: is it just to show a route on a map for each truck ? Do you need to replay a route ? View previous day routes ? Do route-deviation checks (match a route with the planned route and send alerts) ?  Predict arrival times ? Compare the pace of a truck with the planned route (is in early ? is it delayed). All those again need a database with advanced spatial processing capabilities. Unless you do all this processing in some external engine and only use the database for storing the results ...


What query specific counters / metric if any are exposed for monitoring outside of SQL Server itself? I am aware of generic Microsoft SQL Server Performance counters and other ways to monitor general SQL server performance through monitoring system, files, and executable performance, and its internal metrics etc. and I monitor that today through an enterprise wide monitoring solution.

I am wondering what, if any, query specific metrics (specific stored procedures) are exposed outside of Microsoft SQL server that I could query, collect, and monitor outside of SQL Server consoles, AND not requiring extensive permission. The monitoring account for example cannot run sp_who and sp_who2 and I am currently concerned with specific stored procedures.

So for example if I know an application that when it performs a particular task it kicks off sp_Task1, and then sp_task2.. etc.  I need to monitor, alert on long duration, and track long term run times of each of those tasks through an already adopted enterprise monitoring solution that does not currently have the ability to do it itself.

I need this as the application is 'sensitive' to long run times and it can set off a chain of other negative events. Using the image below, I am more concerned about width of the wave front trough-to-read trough than total height or peak-to-peak as if the waves touch things go down hill for a lot of applications really fast.

Though I am trying specifically know how to monitor the run time of a stored procedure from outside of Microsoft SQL server consoles...

My question is: What query specific counters / metric if any are exposed for monitoring outside of SQL Server itself in MS SQL Server 2008 or later?

SQL Server does not expose performance counters for any user defined object.

In fact PerfMon architecture is not a suitable platform for highly dynamical changes. If SQL Server would provide such feature it has to create counters every time a SP created. Think about SP renames, restores etc.

A couple of suggestions for monitoring problem of yours would be :

Creating a wrapper stored procedure for Adam Machanic's sp_WhoIsActive to filter out sessions you are not concerned about and apply practices at How to grant permissions for non-dbas section of sp_AskBrent from Brent Ozar to that stored procedure.
You can log activities to a table using an agent job like every 15 second execute sp_WhoIsActive filter out unwanted records from resultset log them to a table then let GUI generate reports from that table. With a quick search while i'm writing this answer i have come across a blog post from Brent Ozar. I didn't read the whole post but the idea is same.


What commands are available to visualize the data pages on SQL Server? From msdn:

  The page is the fundamental unit of
  data storage in SQL Server. An extent
  is a collection of eight physically
  contiguous pages. Extents help
  efficiently manage pages.

  This section describes the data
  structures that are used to manage
  pages and extents. Understanding the
  architecture of pages and extents is
  important for designing and developing
  databases that perform efficiently.

So what commands/utilities are available to visualize the data pages on SQL Server ?

DBCC PAGE

Paul Randal also has lots of stuff about on-disk structures and articles "Anatomy of a (insert storage thing here)" (some overlap here of course)


Contained User on View - Select permission denied on object 'table1', database 'Database1', schema 'test' As an example, I have a database called 'Database1' on Azure SQL Server 12.0.2000.8
I needed to setup an account that could only access a view in the database (eg. View1).
This was achieved by the following:
CREATE USER [testuser] WITH PASSWORD 'P@ssword1!'
GO
GRANT SELECT ON [dbo].[View1] to [testuser]
GO

This worked perfectly. I was able to connect to the Database and only see the view and not the related tables.
My colleagues then utilized this method to create additional user accounts with read access to other views.
Recently the user accounts have been unable to access the views with the following error msg.

"Select permission denied on object 'table1', database 'Database1', schema 'test'"

I have checked that there is no deny permission against the accounts.
If I grant select on the specified table (table1), the issue is resolved, however this enables the user account to view the table contents which I would like to avoid.
Any assistance in troubleshooting this would be appreciated.

SELECT permission on a view doesn't grant SELECT permission on the referenced tables.  Instead if the view and the tables have the same owner, there is an "ownership chain" between them and permissions on the tables are not checked.
So ensure that the view and tables have the same owner.
Objects are owned by the owner of their schema by default.  It is possible (but unwise) to change the ownership of a single object.  Anyway you can list the objects and their owners like this:
select o.name, type_desc, user_name(coalesce(o.principal_id,s.principal_id)) owner
from sys.objects o
join sys.schemas s
  on o.schema_id = s.schema_id
where o.is_ms_shipped = 0
  and type_desc in ('USER_TABLE','VIEW')
order by type_desc, name


SET ANSI_NULLS - Performance of Stored Procedures Does setting ANSI_NULLS ON/OFF affect the performance of stored procedures?

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[TestProcedure]

This does not affect the performance of stored procedures directly, but in general it can affect performance indirectly, since SET settings used at execution time can influence the plan that is chosen. Two different users, one with the setting on, and one with the setting off, will get their own copies of the plans, and they may not even be the same plan (depending on other variables at runtime, like parameter values). See Erland Sommarskog's post, Slow in the Application, Fast in SSMS? On top of differently performing queries, this can lead to potentially different performance and will create two different plans in the cache, wasting memory.

Eventually, you won't even have the ability to set ANSI_NULLS to OFF; see the documentation:

  In a future version of SQL Server, ANSI_NULLS will always be ON and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

So, you should already be in the habit of always setting it to ON. If it's just always on, then it won't be an issue.


In memory database error: table is full I got hit by error "Tale is full" on my in memory database.

My database is 4.5GB in size and has 300 partitions.

I run MySql 5.6.17 version_compile_machine=x86_64 on 64 bit Windows 10,  16GB ram. Disk are NTFS and have 100GB spare place.

I've setup tmp_table_size and max_heap_table_size to 12GB.
Those values ware 30 MiB untill "table is full" error, so it seems that they ware and are ignored (changing to 12GB did not help.)

If it has to do something with this setting innodb buffer pool size is 12GiB also.

System runs on 50% of RAM memory.

What else can do to increase allowed size of in memory DB?

I was changing tmp_table_size and max_heap_table_size both via PhpMyAdmin and via SET GLOBAL ... in my scripts. And it didn't work.

Changing those varaibles via my.ini, restarting server and then loading DB into memory allowed for bigger DB.


MySQL function: Get string/number separated by some place holder I have the following product table

| product_id    | sku   | created_at    |
|------------   |-----  |------------   |
| 145687        | abc   | 2017-08-08    |
| 22456         | xyz   | 2017-09-01    |
| 6553214       | asd   | 2017-09-05    |

I want to get output as 1/4/5/6/8/7 with SQL

SELECT someMySQLFunction(product_id) FROM product WHERE sku = 'abc';

How to achieve this in MySQL?

For this solution I've opted for the 'easy' way out ... a user-defined function ...

Proposed function:

drop function if exists myinsert;

create function myinsert (instring char(100), inchar char(10))
returns char(100)
deterministic
begin
  declare i int;
  set i = char_length(instring);
  while i > 1 do
    set instring = insert(instring,i,0,inchar);
    set i = i - 1;
  end while;
return instring;
end;

set i = char_length(instring) : get length of input string
while i/set i : loop through values of i down to '2'
insert(instring,i,0,inchar) : at position i in our string, insert inchar
at this point we've stepped backwards through instring, inserting the inschar string before every character in instring (except for the first character)

Calling the function:

select myinsert(cast(product_id as char),'/') as new_string
from   product
where  sku = 'abc';

new_string
-----------
1/4/5/6/8/7

select myinsert(cast(product_id as char),'-/-') as new_string
from   product
where  sku = 'abc';

new_string
---------------------
1-/-4-/-5-/-6-/-8-/-7

Here are rextester and a db-fiddle fiddles


AlwaysOn Listener and DNS We have two DNS servers which replicate entried between them and we recently had a DNS issue where some of the listener addresses were removed from one of the servers only.

At that point some of our connections to AlwaysOn started to fail.  My question is, if he SQL server is configured with both DNS servers then shouldn't it have found the entry in the DNS server that still had the entry, or is this something specific to how SQL works?

This is how DNS works with multiple servers the client will ask the servers in a round-robin fashion. if an entry is removed from one of the DNS servers the client will get Non-existent domain as an answer from that server and will not query the other.

Here you can get an overview but it boils down to this

The DNS Client service queries the DNS servers in the following order:

The DNS Client service sends the name query to the first DNS server on the preferred adapter’s list of DNS servers and waits one second for a response.
If the DNS Client service does not receive a response from the first DNS server within one second, it sends the name query to the first DNS servers on all adapters that are still under consideration and waits two seconds for a response.
If the DNS Client service does not receive a response from any DNS server within two seconds, the DNS Client service sends the query to all DNS servers on all adapters that are still under consideration and waits another two seconds for a response.
If the DNS Client service still does not receive a response from any DNS server, it sends the name query to all DNS servers on all adapters that are still under consideration and waits four seconds for a response.
If it the DNS Client service does not receive a response from any DNS server, the DNS client sends the query to all DNS servers on all adapters that are still under consideration and waits eight seconds for a response.


Trouble with triggers and insert .. on duplicate key update I have two tables with identical schemas.  one is a "main" table that is pruned periodically and kept small.  The other is a "reporting" table that is an archive of everything that ever was.  For simplicity lets say the table schemas look like

create table main_table(
    pk int unsigned not null primary key,
    value int unsigned
);

create table reporting_table(
    pk int unsigned not null primary key,
    value int unsigned
);

I have two triggers, one AFTER INSERT, and one AFTER UPDATE that look like

create trigger reporting_insert after insert on main_table
    for each row
        insert into reporting_table (id,value) VALUES
        (NEW.id, NEW.value);

create trigger trigger reporting_update after update on main_table
    for each row
        update reporting_table  set
            value=NEW.value
        where
            id=NEW.id;

The main table is populated with queries like

insert into main_table values (1,10) on duplicate key update value=value+1;

My problem is the reporting table is not catching this ODKU +1 for the value (and it clearly is updating in the main table).

From the trigger docs:

  A potentially confusing example of this is the INSERT INTO ... ON DUPLICATE KEY UPDATE ... syntax: a BEFORE INSERT trigger will activate for every row, followed by either an AFTER INSERT trigger or both the BEFORE UPDATE and AFTER UPDATE triggers, depending on whether there was a duplicate key for the row.

From reading that I would think it would follow the latter path executed any Before/After update triggers.  Doing a straight UPDATE query on main_table does result on the changes getting propagated to the reporting table.

What am I missing?

This question came back on my radar after a recent comment was posted.

This issue at the time turned out to be this reporting table that was meant to be maintained by triggers was on a slave only.

The insert on duplicate key statements were getting flagged as not safe for statement based replication and getting pushed through in RBR (the stream was in mixed mode).

RBR events do not fire triggers.


moving a 13 gb table to amazon rds I am moving our current database server from a regular EC2 instance running mysql to a RDS instance. I moved all the databases using the amazon-suggested method, simply piping the mysqldump output to a mysql client connected to the remote database (RDS instance). Everything worked fine for most databases, except one. After searching, the cause is a big, 13 Gb table that starts copying OK, but after some time the mysql client throws an error (sadly I didn't take note of the error code when it happened). It was "Connection lost" for sure.

This is the table:

CREATE TABLE `UniqueTextLookup` (
    `UniqueTextLookupID` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `TextID` CHAR(32) NOT NULL COLLATE 'ascii_bin',
    `CrcTextID` INT(10) UNSIGNED NOT NULL,
    `UniqueText` CHAR(255) NOT NULL,
    `CrcUniqueText` INT(10) UNSIGNED NOT NULL,
    PRIMARY KEY (`UniqueTextLookupID`),
    UNIQUE INDEX `TextID` (`TextID`),
    INDEX `UniqueText` (`UniqueText`(10)),
    INDEX `CrcTextID` (`CrcTextID`),
    INDEX `CrcUniqueText` (`CrcUniqueText`)
)

I suspect that the table size has something to do with the indexes more than the data (it's just a lookup table for unique texts, with some hacks to accelerate queries using CRC32 of some columns). It has 15 millon rows.

Any Ideas? I'm running the dump+pipe again to pinpoint the error code.

Some notes:

I cannot login by ssh to the RDS instance, so dumping to a file and copying by scp to the RDS server is not an option.

EDIT:

Checking with mytop It looks like this query (one of the lasts of the dump) :

/*!40000 ALTER TABLE `UniqueTextLookup` ENABLE KEYS */

gets stalled, it has been running for 322 seconds and counting...

I realize you've already worked around the issue, but here is the explanation and the fix for what you encountered.  It's nice to be able to do it with the pipe, so you don't have to write that big file somewhere.

As you know, when you type a query into the mysql command line client, while that query is executing, the client doesn't accept any more input from you.  That same thing essentially happens when you're stringing the these tools together with a pipe -- the target mysql client waits for the target server to return from the query before it can read more data from the pipe from mysqldump.

Meanwhile, as mysqldump is being blocked on writing to the pipe, because we're waiting for the target server to finish a query, it stops reading data from its socket connection.  The OS will only buffer so much data before it stops accepting data from the origin server.

If we do something like ENABLE KEYS which takes a while, we hit a timeout... on the origin MySQL server.  But the timeout value can be changed.

MySQL Server has two timers, net_write_timeout and net_read_timeout, which default to 60 and 30 seconds respectively, and which will cause the serve to tear down a client connection when the timer is exceeded while blocking on a write to or a read from the network.

Most likely, it's net_write_timeout you're hitting, so the origin server is giving up on the connection from mysqldump, which isn't accepting data fast enough (because it's blocked on its output).  Or, more precisely, it isn't accepting data often enough.  If any one statement takes too long to execute, the game is over, so, on the origin server:

SET GLOBAL net_write_timeout = 3600; # one hour

This is setting approximately how much time we can wait for any single query to finish executing on the destination server without the origin server timing out.  Normally, you wouldn't want MySQL to sit and wait for an hour for a blocking client, so set the timer back after you're done.


DBMS_REDEFINITION with WHERE clause I wanted to know if DBMA_REDEFINITON package allows a WHERE clause to filter contents before migration.

I have a partitioned table and wants to copy data and constraint to another table using DBMA_REDEFINITON but while copying contents, I do not want to copy a particular partition from the original table. Is it possible to drop this partition using WHERE clause.

The question came from following information given on Oracle Tips site

Online table redefinition:  You can drop large numbers of rows from a table by adding a WHERE clause predicate to filter-out unwanted rows when you copy the table.

My requirement is from the example below, can a new table have only partitions Part_2 and Part3. (Dropping partition Part_1 using WHERE clause)

-- Existing Table
CREATE TABLE ORIGINAL
(
  ID                INT NOT NULL PRIMARY KEY,
  TEXT              VARCHAR2(255 CHAR) NOT NULL
)PARTITION BY RANGE
  (
    ID
  )
  (
    PARTITION part_1 VALUES LESS THAN(2),
    PARTITION part_2 VALUES LESS THAN(3),
    PARTITION part_3 VALUES LESS THAN(MAXVALUE)
  )
/
-- some data in the table
insert into ORIGINAL values (1, 'test');

-- some index on the table
create index idx_ORIGINAL on ORIGINAL(TEXT);

--INTERIM table for REDEFINITION (has all partitions right now)
CREATE TABLE ORIGINAL_INTERIM
(
  ID                INT NOT NULL PRIMARY KEY,
  TEXT              VARCHAR2(255 CHAR) NOT NULL
)
PARTITION BY RANGE
  (
    ID
  )
  (
    PARTITION part_1 VALUES LESS THAN(2),
    PARTITION part_2 VALUES LESS THAN(3),
    PARTITION part_3 VALUES LESS THAN(MAXVALUE)
  )
/

-- Start Redefinition
BEGIN
  DBMS_REDEFINITION.START_REDEF_TABLE('TSPACE1','ORIGINAL','ORIGINAL_INTERIM');
END;
/

-- Copy objects
DECLARE
  num_errors PLS_INTEGER;
BEGIN
  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS ('TSPACE1','ORIGINAL','ORIGINAL_INTERIM',
    DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
END;
/

-- Sync
BEGIN
  DBMS_REDEFINITION.SYNC_INTERIM_TABLE ('TSPACE1','ORIGINAL','ORIGINAL_INTERIM');
END;
/

-- finish
BEGIN
  DBMS_REDEFINITION.FINISH_REDEF_TABLE ('TSPACE1','ORIGINAL','ORIGINAL_INTERIM');
END;
/

-- Drop interim table
drop table ORIGINAL_INTERIM cascade constraints purge;

You can use part_name parameter of DBMS_REDEFINITION.START_REDEF_TABLE procedure. Here is the complete example of online table redefinition. Online Table Redefinition Examples from Oracle Docs.
Methods to move a partition to another tablespace


How can I get the host_name who creates a DB (proactive mode) in SQL Server 2008? First, I want to clarify that I am not looking for a SELECT HOST_NAME().

I am managing a database environment and have given the developers permission to create the databases they need in the development environment. However, I want to store the machine name (HOST_NAME()) from where the database is created independent of the database user that they use for that purpose. This way, proactively, when a developer creates a database in the future I want to know who creates databases, not only by the owner user but by the name of the machine.

That information is available in the default trace (see this answer for more detail), if it hasn't rolled away (the default trace only keeps so much history).

DECLARE @path NVARCHAR(260);

SELECT
   @path = REVERSE(SUBSTRING(REVERSE([path]),
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT
   LoginName,
   HostName,
   ApplicationName,
   SPID,
   StartTime,
   DatabaseName
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass = 46
AND EventSubClass = 1
AND ObjectID IS NULL
AND ObjectType = 16964
-- AND DatabaseName = N'<database name>';

Other disclaimers:

HostName can't always be collected (e.g. connecting to SQL Server from Visual Studio Code on a Mac leaves HostName NULL).
HostName is easy to spoof.

Going forward, you can do just about anything you want with a DDL trigger, subject to the same disclaimers as above. You can start with something like this:

CREATE DATABASE Watchdog;
GO
USE Watchdog;
GO
CREATE TABLE dbo.CreateDatabaseLog
(
  EventTime datetime2 NOT NULL DEFAULT SYSUTCDATETIME(),
  HostName nvarchar(128),
  EventData XML
);
GO

USE master;
GO
CREATE TRIGGER CaptureDatabaseCreation
ON ALL SERVER
FOR CREATE_DATABASE
AS
  INSERT Watchdog.dbo.CreateDatabaseLog(HostName, EventData)
    SELECT HostName, EVENTDATA();
GO

Now, I'll leave parsing DatabaseName out of the XML as an exercise for the reader, mostly because I am late picking up my kids from daycare. :-)


How to recover deleted records by mistake in Mysql I am using Mysql.And by mistake I deleted very important info.Is there a way to recover or even to see the deleted info over a month ago?

If it's MyISAM - no chance to recover w/o a backup.

If InnoDB - it depends.
InnoDB flags a record as deleted and keeps it in a page for a while. When a tree is rebalanced the deleted records are purged.

So whether you can undelete records depends on how much writes were done to the table after the delete.

Open InnoDB tablespace in a hexeditor and try to find the deleted records:

hexdump -c table.ibd

If table is relatively small I prefer bvi (it allows search by a string or a binary patter)

bvi table.ibd

So if you see the deleted records - it's recoverable.

Other place to look for deleted records is the binlog. If it's in a row format - the delete event will contain old and new values.

InnoDB redo log stores the changes, too. But definitely it won't keep them for a month.

To retrieve deleted records from an InnoDB page you can use a MySQL data recovery tool . Recovery of deleted records is similar to recovery from corrupted tablespace. But in that case c_parser must be called with -D option that will dump deleted records only.

DISLAIMER: I'm an author of the tool


How to get list of users on all instance's databases? excluding the mirrors! For an audit of our MSSQL databases, I have been asked to provide a list of all users for each database running on each instance.

There is an existing stack overflow question

which advised to use...

exec sp_MSforeachdb 'select * from ?.sys.sysusers'

However, it hits a few problems.

1) If any of the databases are a mirror, the script errors as below and doesn't return any results.

"The database "###" cannot be opened. It is acting as a mirror database."

2) If any of the databases have a special character in their name (eg. a hyphen -), again it errors and doesn't return results.

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '-'.

3) The results are all in separate tables.  Ideally I would like it to just output 1 table of database and users.

Can anyone help adjust the code to achieve the above?

Thanks

sp_MSforeachdb is undocumented, unsupported, and has some serious shortcomings.  You'll need to use something else.  Either writing your own cursor over sys.databases, something like this:

declare c cursor local for
    select name
    from sys.databases d
    where name not in ('master','model','tempdb')
    and d.state=0
declare @db sysname

open c
fetch next from c into @db
while @@fetch_status = 0
begin
  begin try
     declare @sql nvarchar(max) = concat(  N'use ', quotename(@db), N';
     select db_name() db, *
     from sys.database_principals u
     where type = ''U'''
     )

     --print @sql
     exec ( @sql )

  end try
  begin catch
    print error_message()
  end catch

  fetch next from c into @db
end

close c
deallocate c

or copying someone else's, eg here.


Get statistics of slow running queries My database application runs many different queries. I have set log_min_duration_statement to 1000. But queries logged there are not always slow, most times they just take a few ms.

Is it possible to get a statistic of all queries, and how often they took longer than 1000ms?

Install the extension pg_stat_statements with the SQL command

CREATE EXTENSION pg_stat_statements

You may want to make sure you create this by using an appropriate user (such as the user your application uses or some dba account). Be aware that whichever user creates the extension will also own it.

This will require a server restart for it to be usable (because you have to change the config in postgresql.conf a tiny bit that affects memory consumption), but it will give you statistics on all queries.

Please see the documentation page for more information about what pg_stat_statements provides. See here for a quick reference on configuration of the postgresql.conf config file for pg_stat_statements.

Once you have it installed and running, you can view statistics by querying the pg_stat_statements view..

select * from pg_stat_statements;
-- or
select * from pg_stat_statements where total_time / calls > 200; -- etc ..


How to identify a Windows Remote Desktop user by SQL Server session id? In SQL Server I've got the session id of a process that is blocking other processes. Now I'd like to identify the specific user or machine that causes this blocking. The users are authenticating using SQL Server Authentication.

Normally I can get the desired information from the activity monitor (or through a query). But in case that the user connected from a Remote Desktop session, I'm lost. The host name is same for all RDP users and there is no additional information that helps distinguishing the sessions. It seems there is no way to actually identify the user who is actually blocking the others.

Is there any way to identify the user nevertheless? If yes, how?

(If it's not possible, then I have to live with it...)

One way to accomplish this would be to use the host_process_id column from sys.dm_exec_sessions

This host_process_id in this table maps to the PID in task manager.

In this case we could run the following query to get our host_process_id, and then take the result and check in task manager which application currently has a PID matching the result.

SELECT session_id, host_process_id FROM sys.dm_exec_sessions WHERE session_id = XXX

You can then check which account is running that process.


Help with preventing double bookings with variable lengths I'm designing a sample database for a venue where members can create bookings for venues. I'm looking for a method to prevent double bookings (possibly by creating a button on a form that checks availability). I'm aware of the method where times are stored in a separate table (found this by searching on Google), but that doesn't work for me because members should be able to choose what time they'd like to start their booking.

The values that I want to be unique are: the date, the time and the venue room number. For example,

21/12/14, 16:45, Room 4

should not be compatible with

21/12/14, 16:45, Room 4

because all data is the same, but it should be compatible with

21/12/14, 16:45, Room 3

because the room number is different.

Also, I need to find a way where a person can't book the same room while it is being used for someone else. I've thought about using a start and end time for this, but I'm not sure how I could implement it.

If it helps, then I am using Microsoft Access to create this database.

I'd appreciate any help I can get to either problem, and thanks!

Lets say that we have two venues A and B with both a start date/time and a end date/time. We need to check whether their time spans overlap. They overlap if:

A.end > B.start AND A.start < B.end


Add Leading Zeros in CASE, CHARINDEX and SUBSTRING Query I am splitting a column based on the colon using this code which works.
    ,CASE WHEN CHARINDEX (':', ref) > 0
THEN SUBSTRING(ref, 1, CHARINDEX(':', ref) -1)
    ELSE NULL
END AS EndVal

The 'EndVal' is numeric and when it is less than 4 characters long I need to add leading Zeros so

123  = 0123

1    = 0001

1234 = 1234
I know I can accomplish that using the RIGHT or REPLICATE function but I cannot get my head round how I either add it to my existing code or edit it so it does both jobs?

Any advice on the original code also greatly appreciated. Having to get this to work on SQL 2008, yes I want to upgrade but...

You can use RIGHT either inside your CASE statement or outside it, depending on whether you want to retain the NULL value when ':' isn't found in the string.

Inside CASE

SELECT
  CASE
    WHEN CHARINDEX (':', Ref) > 0 THEN RIGHT('0000' + (SUBSTRING(Ref, 1, CHARINDEX(':', Ref) -1)), 4)
    ELSE NULL
  END
FROM Data

Outside CASE

SELECT RIGHT('0000' + ISNULL(
  CASE
    WHEN CHARINDEX (':', Ref) > 0 THEN (SUBSTRING(Ref, 1, CHARINDEX(':', Ref) -1))
    ELSE NULL
  END, ''), 4)
FROM Data

The difference here is that NULL values from the CASE statement are replaced with four zeroes in the Outside CASE example, but in the Inside CASE example they remain NULL. You can see the different results in this db<>fiddle.


Simple Linux MySQL GUI Client That Reads UTF-8? I'm an inexperienced MySQL user running Ubuntu and I need to open a database full of Hebrew data. I prefer a GUI and I downloaded MySQL Workbench. Not only is it incredibly confusing for me, but when I finally figured out how to import the database and run a query, the data just comes out like %16%19%22 etc. Apparently that's because it's using Latin encoding.

So my question is, is there a very simple MySQL GUI Front-end that will let me just see the tables and the data in it in Hebrew, and let me simply and easily execute queries containing Hebrew characters?

This was asked (and answered) on StackExchange, but I do think it's a very valid question for our forum.

The correct answer is that you have to issue the following command:

SET NAMES 'charset_name' COLLATE 'collation_name'

which for your case (from here) is

SET NAMES 'hebrew' COLLATE 'hebrew_general_ci'

If for whatever reason this doesn't work for you, you could install a Hebrew version of Linux on some machine you have lying around?


Tyring to get a better understanding of ORACLE admin accounts In reviewing some configuration information for an ORACLE 12C server, I see that the only user assigned the DBA role appears to be SYS account. Also, the only user shown with SYSDBA and SYSOPER privileges appears to be SYS account. My understanding is that these are built in accounts that can perform all administrative functions. Absent other user accounts,  it seems to indicate that the SYS account is used to perform day to days admin functions. Shouldn't there be a "named" user with appropriately assigned privileges,versus using the SYS account?

Yes, there should be. Even the documentation mentions that:

Administrative User Accounts

  Create at least one additional administrative user and grant to that
  user an appropriate administrative role to use when performing daily
  administrative tasks. Do not use SYS and SYSTEM for these purposes.

But the database can not force you to do this. It is a discipline that some companies follow, while other companies ignore.


Using SQL Server's 2016 Temporal Tables to Record Prior History I've been reading with interest about SQL Server 2016's Temporal Table capability.

It almost seems like the answer to a problem I have (and I'm sure many have had), except for one thing:

  StartTime and EndTime durations are immutable.

Which is great for transactions going forward...but what if you want to model stuff that changed in the past?

Is it possible to model things like "Organization A was named X between 1970 and 1980, Y between 1980 and 1990, and Z since 1990" using temporal tables? Or are we stuck with traditional methods for this stuff?

It looks to me like temporal tables are only good with changes that happen after the temporal table is created, but I'd like someone to confirm or deny that. (Bonus points if my assumption is correct and you can point me to something that describes the ideal way to implement tables and queries to handle historical queries.)

Currently temporal supports system time, which means the date/time values placed in those start/end time columns are populated by the system.

In a future version, temporal will also support application time, which will provide ways to have the application dictate the time and, I expect, to update existing data with real start/end times.

The only way to override this really would be to archive data from the history table into your own table (which you may end up doing over time to keep the history table from growing forever anyway), change the times there, and use that table as part of your queries.


How to estimate the size of a PostgreSQL DB to be dumped through pg_dump Similar to this question about MySQL --
I need to find out the size of a dump file prior dumping but for a PostgreSQL Database. I could not find any reference to this in the documentation

A very rough estimate is to compute the size of all tables with:

select pg_size_pretty(sum(pg_table_size(tablename::regclass)))
from pg_tables
where schemaname not in ('pg_catalog','information_schema');


Upgrading MongoDB ReplicaSet to 3.0 causes BSONElement: bad type 108 I followed the official guide to upgrade a MongoDB ReplicaSet from 2.6 to 3.0 on the secondary member.

Shutdown mongod (systemctl stop mongodb.service) on secondary
Installed binaries (via apt-get)
Update mongod.conf
Restart mongod: failed!

[NetworkInterfaceASIO-Replication-0] Uncaught exception in NetworkInterfaceASIO IO worker thread of type: Location10320: BSONElement: bad type 108
[NetworkInterfaceASIO-Replication-0] Fatal Assertion 28820

Looks like my databases are corrupt but mongod w/out replSet works fine with the same dbs.
Did I miss something?

Found the answer:

In 3.2 WiredTiger is the default storage engine. So I had to remove existing databases (/var/lib/mongod). After starting mongod it will replicate from the primary member.

I accidently updeted directly to 3.2.9 and it worked. That was the reason that WiredTiger was default engine.


PostgreSQL Update Query Hangs I have an update query being fired from the Java Hibernate (for a single user), which works fine on a small dataset of rows in a table (100MB), but the same update query goes into a hung state if the number of records in the same table increases (12 GB).
I didn't find any blocking or deadlocks queries been executed.
I have performed autovacuum, reindex, analyze, increased shared buffers, set locktimeout, statement timeout values - but with no effect.
The update transaction doesn't move ahead only and never gets committed.
Output of the pg_locks table:

DDL Statement:

ANALYZE explain output:

I verified the database schemas on both databases and no difference was found. Doing the analyze, same plan is being used for both the databases but time for execution is slightly on the higher side for the database with large size. But the update gets committed when fired directly whereas through application it's waiting continuously.
Please find the output of EXPLAIN (ANALYZE, BUFFERS) update:
"QUERY PLAN"
"Update on ""CaseProductUnderwriting""  (cost=0.00..2421622.16 rows=1 width=652) (actual time=14339.234..14339.235 rows=0 loops=1)"
"  Buffers: shared read=1644192"
"  ->  Seq Scan on ""CaseProductUnderwriting""  (cost=0.00..2421622.16 rows=1 width=652) (actual time=14339.228..14339.229 rows=0 loops=1)"
"        Filter: (""CaseProductUnderwritingPK"" = '8908693'::bigint)"
"        Rows Removed by Filter: 62196975"
"        Buffers: shared read=1644192"
"Planning Time: 1.421 ms"
"Execution Time: 14339.377 ms"

Please note that the same UPDATE is not moving forward.
EXPLAIN (ANALYZE, BUFFERS) Update "QUERY PLAN"
"Update on ""CaseProductUnderwriting"" (cost=0.00..2421622.16 rows=1 width=652) (actual time=14339.234..14339.235 rows=0 loops=1)"
" Buffers: shared read=1644192" " ->
Seq Scan on ""CaseProductUnderwriting"" (cost=0.00..2421622.16 rows=1 width=652) (actual time=14339.228..14339.229 rows=0 loops=1)"
" Filter: (""CaseProductUnderwritingPK"" = '8908693'::bigint)"
" Rows Removed by Filter: 62196975"
" Buffers: shared read=1644192" "Planning Time: 1.421 ms"
"Execution Time: 14339.377 ms"

If the execution plan that you posted is for the query that is too slow for you, the answer would be to create an index on "CaseProductUnderwritingPK".


Example optimisic offline lock in SQL/PL? Can someone show me an example of an optimistic offline lock in
an Oracle Stored Procedure?

Typically I think that systems would select the ora_rowscn value when they read the data, and when they write it back they would specify that value as part of the update statement. If no rows are updated then either the record has been deleted or the record has changed.


TSQL success or failure result of EXEC dynamic SQL Based on the following code:
/* This SP takes a query and a unique temporary table name as input...
The outputs is a string with the command to create the tmp table based on the query that is given in the @SRC parameter
*/
EXEC dbo.createTmpFromQuery @SRC,@uniqueTMP,@createTmpStr=@SQLStr OUTPUT
/* This executed the query that will create the tmp table and insert the data from @SRC */
EXEC(@SQLStr)

The whole stored procedure that calls these 2 lines (among many others) is in a Try Catch.
I've just run into a problem where the query in @SRC had a syntax error which called the Catch portion:
BEGIN CATCH
        ROLLBACK TRANSACTION;
        SET @ERRORMESSAGE=CASE WHEN @ERRORMESSAGE='' THEN ERROR_MESSAGE() ELSE @ERRORMESSAGE END
        RAISERROR (@ERRORMESSAGE,16,1)
END CATCH

the Try has many validations and I set @ErrorMessage to personalized messages in most cases.
In this case, I would like to set a personalized message instead of getting the standard error message
(example): Incorrect syntax near 'FROM'.

I'm trying to figure out how I can get the EXEC(@SqlStr) return a successful or failure code, so I can set the @ErrorMessage before it goes into catch portion of the code.

I found the solution...
I wrapped the statements in another try catch within the big try catch
(Didn't know we can do that) :-(
    BEGIN TRY
        EXEC dbo.createTmpFromQuery @SRC,@uniqueTMP,@createTmpStr=@SQLStr OUTPUT
        EXEC(@SQLStr)
    END TRY
    BEGIN CATCH
        SET @ErrorMessage='Error Creating '+@uniqueTMP+' table - verify query in @SRC'
        RAISERROR(@ErrorMessage,16,1)
    END CATCH

I tested it an it worked.
I hope this will help someone else...


Generating Error when data size is larger then max text repl size I recently ran into an issue with where an application tried to do an insert with data over the max text repl size.  this time the failed insert was captured by the application log, and I was made aware.  I was a little disturbed though to find out the error wasn't captured in the sql server error log, and I don't see it captured any place else.

Any advise on how to capture, and Ideally generate alerts based on the 'Length of LOB data (XXXXXX) to be replicated exceeds configured maximum " would be appreciated.

The SQL Server error log only captures errors having a significant severity (generally 19-25, with exceptions). See the documentation here:

https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/database-engine-error-severities

Imagine if all divide by zero, ambiguous column, or syntax errors flooded the error log? That log would quickly become an unmanageable mess.

What you can do is use an Alert, which can trigger notifications and other actions whenever an exception with a certain severity (or, in your case, error number) is raised:

https://docs.microsoft.com/en-us/sql/ssms/agent/alerts

For example:

EXEC sys.sp_add_alert @name = 'repl size error',
  @message_id = 7139, -- I believe this is the right message
  ...

See this doc for sp_add_alert usage:

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-add-alert-transact-sql


Tail Log backup when doing a restore? Typically when doing a restore of a DB from a production server to a non-production server I will use the WITH REPLACE option as when I forget I get an error about the tail log not being backed up.

According to MSDN I should indeed be backing up my tail log before restoring:

  If the database is online and you plan to perform a restore operation on the database, begin by backing up the tail of the log. To avoid an error for an online database, you must use the … WITH NORECOVERY option of the BACKUP Transact-SQL statement.

What are some dangers or disadvantages of the way I'm doing it? Why is backing up the tail log first of advantage to me?

I'm using SQL Server 2008R2 but I'm presuming this query will be relevant to most newer versions of SQL Server also so have not tagged it as such initially.

If you don't back up the tail of the log you lose any transactions that ocurred since you last backed up the database.


Why use an intent lock at a higher level in the hierarchy, why not just a regular lock? This article suggests intent locks are used at a higher level of the lock hierarchy to indicate that a lock has been taken on a lower level. The idea of this mechanism is that SQL Server doesn't need to check every row for active locks if it wants to place a lock on a page or table.
As an example, if I update a single row in AdventureWorks:
USE AdventureWorks2014;

BEGIN TRAN;
UPDATE  Person.Person
SET     LastName = 'Smith'
WHERE   BusinessEntityID = 1;

and then in another session, run sp_whoisactive with get_locks to see what locks were taken:
EXEC sp_whoisactive @get_locks = 1;
I can see the following:
      <Locks>
        <Lock resource_type="KEY" index_name="IX_Person_LastName_FirstName_MiddleName" request_mode="X" request_status="GRANT" request_count="2" />
        <Lock resource_type="KEY" index_name="PK_Person_BusinessEntityID" request_mode="X" request_status="GRANT" request_count="1" />
        <Lock resource_type="OBJECT" request_mode="IX" request_status="GRANT" request_count="1" />
        <Lock resource_type="PAGE" page_type="*" index_name="IX_Person_LastName_FirstName_MiddleName" request_mode="IX" request_status="GRANT" request_count="4" />
        <Lock resource_type="PAGE" page_type="*" index_name="PK_Person_BusinessEntityID" request_mode="IX" request_status="GRANT" request_count="1" />
      </Locks>

this backs up what the article above says - we have an exclusive row (key) lock on the two relevant indexes and therefore the pages and object have IX locks.
If I try and SELECT that row in another session:
SELECT * FROM Person.Person WHERE BusinessEntityID = 1
that query is indeed blocked.
My question therefore is, why is an intent lock a separate thing to a "regular" lock? Why would SQL Server not just place an X lock on the table?

I think I have worked out the answer (Please correct this if my understanding is wrong)
After a bit more reading, I found a simple matrix here:

It appears that the X lock is less compatible with other locks than the the IX lock.
If we were to put an X lock on a table due to reading one row, we couldn't get an IS lock on that table but if we have an IX lock on the table we can get a IS lock.
If I understand correctly then, If we were to lock the table with an X lock to update one row, we couldn't read a different row because the read couldn't place an IS on the table. However, if we use an IX, it can get the IS lock it needs.
The following test seems to prove this is the case:
Update the single row:
USE AdventureWorks2016;

BEGIN TRAN;
UPDATE  Person.Person
SET     LastName = 'Smith'
WHERE   BusinessEntityID = 1;

in another session, SELECT a different row to the one being updated
SELECT * FROM Person.Person WHERE BusinessEntityID = 2

this row returns without being blocked
However, if I was to gain an X lock:
USE AdventureWorks2016;

BEGIN TRAN;
UPDATE  Person.Person WITH (TABLOCKX)
SET     LastName = 'Smith'
WHERE   BusinessEntityID = 1;

this query is now blocked:
SELECT * FROM Person.Person WHERE BusinessEntityID = 2


Unique index updates and statistics row modification counters Given the following table, unique clustered index, and statistics:
CREATE TABLE dbo.Banana
(
    pk integer NOT NULL,
    c1 char(1) NOT NULL,
    c2 char(1) NOT NULL
);

CREATE UNIQUE CLUSTERED INDEX pk ON dbo.Banana (pk);

CREATE STATISTICS c1 ON dbo.Banana (c1);
CREATE STATISTICS c2 ON dbo.Banana (c2);

INSERT dbo.Banana
    (pk, c1, c2)
VALUES
    (1, 'A', 'W'),
    (2, 'B', 'X'),
    (3, 'C', 'Y'),
    (4, 'D', 'Z');

-- Populate statistics
UPDATE STATISTICS dbo.Banana;

The statistics row modification counters obviously show zero before any updates:
-- Show statistics modification counters
SELECT
    stats_name = S.[name],
    DDSP.stats_id,
    DDSP.[rows],
    DDSP.modification_counter
FROM sys.stats AS S
CROSS APPLY sys.dm_db_stats_properties(S.object_id, S.stats_id) AS DDSP
WHERE
    S.[object_id] = OBJECT_ID(N'dbo.Banana', N'U');

Incrementing each pk column value by one for every row:
-- Increment pk in every row
UPDATE dbo.Banana
SET pk += 1;

Uses the execution plan:

It produces the following statistics modification counters:

Questions

What do the Split, Sort, and Collapse operators do?
Why do the pk stats show 2 modifications, but c1 and c2 show 5?

SQL Server always uses the Split, Sort, and Collapse combination of operators when maintaining a unique index as part of an update that affects (or might affect) more than one row.
Working through the example in the question, we could write the update as a separate single-row update for each of the four rows present:
-- Per row updates
UPDATE dbo.Banana SET pk = 2 WHERE pk = 1;
UPDATE dbo.Banana SET pk = 3 WHERE pk = 2;
UPDATE dbo.Banana SET pk = 4 WHERE pk = 3;
UPDATE dbo.Banana SET pk = 5 WHERE pk = 4;

The problem is that the first statement would fail, since it changes pk from 1 to 2, and there is already a row where pk = 2. The SQL Server storage engine requires that unique indexes remain unique at every stage of processing, even within a single statement. This is the problem solved by Split, Sort, and Collapse.
Split
The first step is to Split each update statement into a delete followed by an insert:
DELETE dbo.Banana WHERE pk = 1;
INSERT dbo.Banana (pk, c1, c2) VALUES (2, 'A', 'W');

DELETE dbo.Banana WHERE pk = 2;
INSERT dbo.Banana (pk, c1, c2) VALUES (3, 'B', 'X');

DELETE dbo.Banana WHERE pk = 3;
INSERT dbo.Banana (pk, c1, c2) VALUES (4, 'C', 'Y');

DELETE dbo.Banana WHERE pk = 4;
INSERT dbo.Banana (pk, c1, c2) VALUES (5, 'D', 'Z');

The Split operator adds an action code column to the stream (here labelled Act1007):

The action code is 1 for an update, 3 for a delete, and 4 for an insert.
Sort
The split statements above would still produce a false transient unique key violation, so the next step is to sort the statements by the keys of the unique index being updated (pk in this case), then by the action code. For this example, this simply means that deletes (3) on the same key are ordered before inserts (4). The resulting order is:
-- Sort (pk, action)
DELETE dbo.Banana WHERE pk = 1;
DELETE dbo.Banana WHERE pk = 2;
INSERT dbo.Banana (pk, c1, c2) VALUES (2, 'A', 'W');
DELETE dbo.Banana WHERE pk = 3;
INSERT dbo.Banana (pk, c1, c2) VALUES (3, 'B', 'X');
DELETE dbo.Banana WHERE pk = 4;
INSERT dbo.Banana (pk, c1, c2) VALUES (4, 'C', 'Y');
INSERT dbo.Banana (pk, c1, c2) VALUES (5, 'D', 'Z');

Collapse
The preceding stage is enough to guarantee the avoidance of false uniqueness violations in all cases. As an optimization, Collapse combines adjacent deletes and inserts on the same key value into an update:
-- Collapse (pk)
DELETE dbo.Banana WHERE pk = 1;
UPDATE dbo.Banana SET c1 = 'A', c2 = 'W' WHERE pk = 2;
UPDATE dbo.Banana SET c1 = 'B', c2 = 'X' WHERE pk = 3;
UPDATE dbo.Banana SET c1 = 'C', c2 = 'Y' WHERE pk = 4;
INSERT dbo.Banana (pk, c1, c2) VALUES (5, 'D', 'Z');

The delete/insert pairs for pk values 2, 3, and 4 have been combined into an update, leaving a single delete on pk = 1, and an insert for pk = 5.
The Collapse operator groups rows by the key columns, and updates the action code to reflect the collapse outcome:

Clustered Index Update
This operator is labelled as an Update, but it is capable of inserts, updates, and deletes. Which action is taken by the Clustered Index Update per row is determined by the value of the action code in that row. The operator has an Action property to reflect this mode of operation:

Row modification counters
Note that the three updates above do not modify the key(s) of the unique index being maintained. In effect, we have transformed updates to the key columns in the index into updates of the non-key columns (c1 and c2), plus a delete and an insert. Neither a delete nor an insert can cause a false unique-key violation.
An insert or a delete affects every single column in the row, so statistics associated with every column will have their modification counters incremented. For update(s), only statistics with any of the updated columns as the leading column have their modification counters incremented (even if the value is unchanged).
The statistics row modification counters therefore show 2 changes to pk, and 5 for c1 and c2:
-- Collapse (pk)
DELETE dbo.Banana WHERE pk = 1;                         -- All columns modified
UPDATE dbo.Banana SET c1 = 'A', c2 = 'W' WHERE pk = 2;  -- c1 and c2 modified
UPDATE dbo.Banana SET c1 = 'B', c2 = 'X' WHERE pk = 3;  -- c1 and c2 modified
UPDATE dbo.Banana SET c1 = 'C', c2 = 'Y' WHERE pk = 4;  -- c1 and c2 modified
INSERT dbo.Banana (pk, c1, c2) VALUES (5, 'D', 'Z');    -- All columns modified

Note: Only changes applied to the base object (heap or clustered index) affect statistics row modification counters. Non-clustered indexes are secondary structures, reflecting changes already made to the base object. They do not affect statistics row modification counters at all.
If an object has multiple unique indexes, a separate Split, Sort, Collapse combination is used to organize the updates to each. SQL Server optimizes this case for nonclustered indexes by saving the result of the Split to an Eager Table Spool, then replaying that set for each unique index (which will have its own Sort by index keys + action code, and Collapse).
Effect on statistics updates
Automatic statistics updates (if enabled) occur when the query optimizer needs statistical information and notices that existing statistics are out of date (or invalid due to a schema change). Statistics are considered out of date when the number of modifications recorded exceed a threshold.
The Split/Sort/Collapse arrangement results in different row modifications being recorded than would be expected. This, in turn, means that a statistics update may be triggered sooner or later than would be the case otherwise.
In the example above, row modifications for the key column increase by 2 (the net change) rather than 4 (one for each table row affected), or 5 (one for each delete/update/insert produced by the Collapse).
In addition, non-key columns that were logically not changed by the original query accumulate row modifications, which may number as many as double the table rows updated (one for each delete, and one for each insert).

The number of changes recorded depend on the degree of overlap between old and new key column values (and so the degree to which the separate deletes and inserts can be collapsed). Resetting the table between each execution, the following queries demonstrate the effect on row modification counters with different overlaps:
UPDATE dbo.Banana SET pk = pk + 0; -- Full overlap

UPDATE dbo.Banana SET pk = pk + 1;

UPDATE dbo.Banana SET pk = pk + 2;

UPDATE dbo.Banana SET pk = pk + 3;

UPDATE dbo.Banana SET pk = pk + 4; -- No overlap


Display posted on date and time like1 hr ago or 30 min ago I want to show the posted date like 1 hr ago, or 30 min ago. This is my code:

declare @date datetime ='2014-07-24 11:50:22.033';
SELECT CASE WHEN DATEPART(DD,@date)=DATEPART(DD,GETDATE()) THEN
  CASE WHEN DATEPART(HH,@date)=DATEPART(HH,GETDATE()) THEN
      CAST(DATEDIFF(MINUTE, @date, GETDATE()) AS NVARCHAR(50)) +' Minutes ago '
     ELSE
      CAST(DATEDIFF(HOUR, @date, GETDATE()) AS NVARCHAR(50)) + ' Hours ago  ' END
 ELSE
  CAST(DATEDIFF(DAY, @date, GETDATE()) AS NVARCHAR(50)) + ' Days ago.. '
END AS Postedon

I am getting a problem when the posted time is 11:50 and the server time is 12:10. In that case, I would like to show "20 min ago", but my code showing 1 hr ago.

As I said in the comments above, this should really be formatted in the application layer, where you are controlling the presentation to the user.

The issue lies with the way SQL handles the DATEDIFF function, in that comparing the HOUR difference between 08:59 and 09:00 will result in 1 hour, because the hour has ticked over and there is 1 hour difference between 8 and 9.

My solution may not be the most elegant, however it uses difference in MINUTE to get the number of hours passed since posting and the difference in HOUR to get the number of days.

 declare @date datetime ='2014-07-23 09:50:22.033';
    SELECT
      CASE
        WHEN DATEDIFF(SECOND, @date, GETDATE()) < 60 THEN ' Just Posted'
        WHEN DATEDIFF(MINUTE, @date, GETDATE()) < 60 THEN CAST(DATEDIFF(MINUTE, @date, GETDATE()) AS VARCHAR(10)) + ' Minutes ago'
        WHEN DATEDIFF(MINUTE, @date, GETDATE()) < 24 * 60 THEN CAST(FLOOR(DATEDIFF(MINUTE, @date, GETDATE())/60) AS VARCHAR(10)) + ' Hours ago'
        ELSE CAST(FLOOR(DATEDIFF(HOUR, @date, GETDATE())/24) AS VARCHAR(10)) + ' Days ago'
    END AS Postedon


Deadlocks in SQL Server We're currently encountering a problem with out ASP.NET Application that results in deadlocks on the SQL Server database. A bit of background information I can provide is that we rely heavy on AJAX callbacks.

The problem occurs on a page with various "fields" that contain information about a resource. The user can click on each field to turn it into "edit mode" which allows the change of data. Basically what this does is open a transaction, save the item, update the html and close the transaction.

The user can change the data and then save the changes again with a transaction. We also have buttons that can be pressed to trigger scripts (C# dynamic code) which run a new transaction.

Below is a small transcript of two threads running in such a scenario and what they're doing.

* Thread 1 *

Step 1. SET TRANSACTION ISOLATION LEVEL read committed
Step 2. begin transaction
Step 3. update Person set Name = 'Person1' where itemId  = 801
Step 7. update Person set Name = 'Person3' where itemId  = 801
Step x. commit transaction

* Thread 2 *

Step 4. SET TRANSACTION ISOLATION LEVEL read committed
Step 5. begin transaction
Step 6. update Person set Name = 'Person2' where itemId  = 801
Step x. commit transaction

After executing step 7 you will receive a deadlock.

> Transaction (Process ID 124) was deadlocked on lock resources with
> another process and has been chosen as the deadlock victim. Rerun the
> transaction.

We're currently investigating how deadlocks are working in SQL Server and how we can prevent them from our side, but any input/advice on this to do/check is welcome.

If additional information is required, I can add it as long as it's not violating our rules here at work.

How to solve deadlocks depends entirely on your data requirements.

Are you content with only applying the last update to the data ?
In that case, don't use a transaction at all - this will happen automatically.

Does every user have to see the real updates being applied for only that user?
This is what you are currently attempting, including (ugh) keeping a transaction open while waiting on a web page, of all things.

I would start with investigating the actual need to do this very bad thing...

Regardless of the ultimate goal, explicit transactions should be used sparsely, wisely, and with good reason.