Where is Read Committed insufficient? Wikipedia says that Read Committed is prone to non-repeatable reads. However, I can simply cache the first read result in my transaction (make a snapshot) and release the DB lock to let other transactions update the read rows. Effectively, I achieve repeated reads at the cost of RC isolation and, thus, higher performance than Repeatable Read offers. Nothing bad can happen and I can feel as safe as when using the Repeatable Read isolation, right? No, I guess that holding a read lock for the data I have read helps to prevent some undesirable scenarios in banking or booking reservations, probably. Which ones?

What I am looking for is an example where my emulation of RR fails. I have demonstrated that I can make the reads repeatable simply by caching the data item on its first read access. I want an example where my emulation is not sufficient. What is bad about releasing the lock immediately after reading under this strategy?

The Wikipedia article and the name Repeatable Read seem to imply to me that that is all what we need. But I suspect that Repeatable Read, by taking the shared lock, sacrifices performance for the sake of providing something more important than simple consistency of reads, since the latter can simply be achieved with a simple Read Committed + cache (snapshot) combination. I guess the reader locks persist for the whole transaction in 2PL and we have shared/read locks as in multiple readers/single-writer for a greater purpose than just to provide repeatable reads within a single transaction and, consequently, Repeatable Read is a misnomer that hides the fact of locking, which actually sacrifices the performance for something larger than consistent reads.

The choice of isolation level is driven by the guarantees your application requires from the database. Full ACID isolation is only provided by serializable isolation; all other isolation levels provide a lesser degree of protection for transactions.

  Read Committed admits inconsistent results in repeated reads

The only guarantee provided by read committed isolation is that only committed data will be read (dirty reads are disallowed). Anything beyond that is implementation specific.

For example, in SQL Server, read committed using row versioning (RCSI) also guarantees statement-level repeatable reads and a statement-level point in time view of the database. To be clear, neither of those are guaranteed across multiple statements within the same transaction under RCSI (this is provided by Snapshot Isolation).

SQL Server's implementation of read committed using locking allows repeated reads within the same statement to encounter different values, and allows the database to return data from different points in time.

To emphasise: a single statement that touches the same row more than once may encounter different values. The multiple reads may not be apparent from inspecting the SQL alone, since the query optimizer has considerable freedom in constructing a physical execution strategy.

  I can simply cache the first read result in my transaction and let other transactions to update...

This is a valid approach, if it meets the isolation requirements of whatever it is you are doing with (or based on) the data.

  Nothing bad can happen and I can feel as safe as Repeated Read isolation, right?

Not generally, no. Repeatable read (RR) isolation provides more guarantees than read committed (RC) isolation. Specifically, RR guarantees that data will not change for the life of the transaction once the data item has been read for the first time. RR does not guarantee that reads will in fact be entirely repeatable because new data may appear in subsequent reads (phantoms).

Note that implementations are free to exceed the base requirements of each isolation level, so some products may in fact provide equal guarantees under RC as RR (this is not the case for SQL Server). Indeed, an implementation could make all isolation levels equal to serializable, and still be in compliance with the standard.

  No, I guess that holding a read lock for the data I read helps to prevent some undesirable scenarios...

Locking is an implementation detail. It is better to think about the degree of isolation your use of the data requires to behave correctly in all circumstances (when multiple concurrent data changes are possible).

  ...to prevent some undesirable scenarios in booking reservations, probably. Which ones?

The ways in which multiple concurrent database transactions can interact unhelpfully are limited only by one's imagination.

For example, using RC and caching the value read could result in a lost update, where another transaction updates based on the stored value, before your transaction does the same thing. In terms of histories for two transactions running x:= x + 1:

transaction 1 reads x (=1)
transaction 2 reads x (=1)
transaction 2 writes [x:=x + 1] (=2)
transaction 2 commits
transaction 1 writes [x:=x + 1] (=2)
transaction 1 commits

Only serializable isolation guarantees that if a transaction that can be shown to produce correct results with no concurrent activity, it will continue to do so when competing with any combination of concurrent transactions.

Note that this does not mean that concurrent serializable transactions actually execute sequentially, just that they will have the same effects as if they had (in some unspecified order).

Specifying any other isolation level involves a trade-off: potentially higher performance and/or concurrency with fewer isolation guarantees. Exactly what the trade-off is varies significantly between implementations.

Choosing the appropriate isolation level requires comparing the needs of your application with the guarantees available. In addition, you may need to consider other factors, for example whether the transaction requires a point-in-time view of the data or not.

For more information and some examples from a SQL Server point of view, please refer to my articles in SQL Server Isolation Levels : A Series


Best Book to Learn MySQL I would like to read a book about MySQL database with emphasis on performance and optimization on Linux server. I appreciate your suggestions.

I actually answered a question like this on July 9, 2011

Suggest a MySQL book for me

Let me expand on this answer:

When it comes to MySQL, the must have book is the MySQL 5.0 Certification Study Guide. I have studied this book with a fine-toothed comb. At the time I bought the book, there was an errata page (list of typos in the book) provided my MySQL (the original MySQL, pre-Oracle, pre-Sun).

It forms a very good base for learning MySQL. However, to keep things in persepctive, let me tell you my experience using the book.

I actually purchased the book in September 2006 along with MySQL Stored Procedure Programming and starting using it for everything tasks as a first year MySQL 5.0 DBA (I already had 2 years with MySQL 4.0 as Developer/DBA). After 10 months, I took the exams July 28, 2007 and August 11, 2007. I passed both exams on the first try because of two things: (1) Thorough study of the book and (2) practical experience.

Nothing can substitute for actual usage of MySQL. After proper due diligence and logging enough miles in MySQL, then this book will definitely help you get certified.

Other books will enlighten you further

MySQL Administrator's Guide and Language Reference
MySQL Database Design and Tuning
High Performance MySQL
Understanding MySQL Internals
MySQL High Availability

BTW Yes, I do own all these books. I have read them and still use them !!!


Impact of shrinking ldf file after restoring full backup What is the use of data in .ldf(log) file after restoring a full backup. I am planning to shrink the log files after restoring the full backup in my dev server.

Is there any side effect for this ?

Is there any side effect for this ?

No, not really. The worst thing that could happen is the log file grows again, given this is a dev server that's not a problem. Worst case is the log takes up all available space and your db goes into read-only... which is easily rectifiable and downtime is fine as it's a dev server.

Shrink away.


mssql on ubuntu will not start, cannot update 404 I did a routine Ubuntu system update for security patches today (2018-02-21)... mssql will no longer start or update, seeing failures in error log, systemctl and update output with 404 like:

Err:13 https://packages.microsoft.com/ubuntu/16.04/mssql-server xenial/main amd64 Packages
  404  Not Found

Problem: routine update breaks my vanilla Ubuntu 16 LTS with mssql-server instance. No other substantive info obvious to me looking in logs, etc...

Fixed by guessing this was due to a server update and the source url changed. I added the current source per their instructions https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-ubuntu

This added the NEW source with a sudo add-apt-repository ... per the docs. I then manually comment-out (or you can remove) the old 404 source in /etc/apt/sources.list.

Finally sudo apt-get update -y ... and start the server and I can again see it running (netstat or whatever to see the service up).

To be clear the URL changed:

from
https://packages.microsoft.com/ubuntu/16.04/mssql-server

to https://packages.microsoft.com/ubuntu/16.04/mssql-server-2017

Comment: adding '-2017' to sources, or changing sources for existing installations is highly likely to both break them and makes these sources look dated, possibly all goals driving the change. That sucks.


Do SQL Servers in a cluster need to have the same memory? I have two servers in always on failover configuration with windows clustering underneath.

Do both servers need to have the same memory?
One of our DBs that uses a lot of memory is not in the AG, so ideally I don't want to give the secondary server the same memory as the primary because - it will never need to run the big DB.

There's no functional requirement for servers in a cluster to have the exact same specs (memory, CPU, etc).  So you can definitely set it up this way.

To some extent, you've answered your own question.  To put it differently - if one of the servers in the cluster will never be running the same workload as the other, you should tune that server appropriately for its workload.  It would be a waste of resources to do otherwise.


What would I use a MATCH SIMPLE foreign key for? What I understand:

In addition to valid references to the referenced table, a column with a foreign key is also allowed to hold a NULL value. The SQL standard defines several match modes for foreign keys, like MATCH SIMPLE and MATCH FULL. For multi-column foreign keys, MATCH SIMPLE allows a wrong (= not referencing) value in any of the columns as long as at least one column of the foreign key holds a NULL value.

Multi-column foreign keys are rare (because multi-column primary keys are), but I can imagine valid use cases. For example a translation of a blog post might have a primary key (post_id, language_id). Now something that references such a translation (for example which translation a user is currently editing) would have a multi-column foreign key.

What I don't understand:

Why would I ever use MATCH SIMPLE for my foreign key?

In above example it doesn't make sense to have a "currently editing" entry that only references by post_id and not by language_id.

What would be an example where it does make sense?

My preference is to avoid nulls in foreign key columns. Make all foreign keys non-nullable. In fact I have foreign key = not null as a standard check in some static analysis scripts I use for validating database designs.

The FULL / SIMPLE behaviour is obscure enough that in my experience even experts don't always get it (I'm an expert and I have to think twice about it!) As ypercube points out, SIMPLE is effectively the default in Microsoft SQL Server but I suspect most people working with that product never even considered it. If one of your goals in database design is to make data comprehensible by and useful to the average user then foreign keys ought to be non-nullable.


How to sum (aggregate) all the values in a key/value JSONB? in Postgres 10 I have the following table:

CREATE TABLE testtable (
    id  int   PRIMARY KEY,
    qty jsonb
);
INSERT INTO testtable (id,qty)
VALUES
  ( 1, '{"2018-08-01": 10, "2018-08-11": 20, "2018-10-23": 30}' ),
  ( 2, '{"2018-08-17": 100, "2018-11-01": 200}' ),
  ( 3, '{"2018-09-03": 1, "2018-09-01": 2, "2018-10-01": 3}' );

Is there a quick way, using SQL to return the sum of each JSONB field so that the results would be:

ID Total
1   60
2   300
3   6

I have seen more complicated possibilities using UNNEST and/or replace (https://stackoverflow.com/questions/26699601/sum-of-values-of-json-array-in-postgresql).

However, I was hoping for something more elegant.

Despite having searched for hours I think I found my answer 10 minutes after posting!

Including for reference for others:

SELECT id, sum(value::float)
FROM testtable
CROSS JOIN LATERAL jsonb_each_text(qty)
GROUP BY id;


How to restore function using PG_RESTORE I did the backup of my database:

-bash-4.2$ pg_dump -d mydb --format=custom -f /tmp/dump
-bash-4.2$

Below checked and I saw my functions there:

-bash-4.2$ pg_restore -l /tmp/dump | grep -i addconf
233; 1255 39949 FUNCTION public addconfiguration(text) mydb
247; 1255 39950 FUNCTION public addconfiguration2(text) mydb
273; 1255 39951 FUNCTION public addconfiguration3(text) mydb

Then I tried to restore one function using the Postgres pg_restore instructions:

-bash-4.2$ pg_restore -d postgres --function=addconfiguration2(text) -v /tmp/dump
-bash: syntax error near unexpected token `('

I also tried without the parentheses and the arg, but it didn't work:

-bash-4.2$ pg_restore -d postgres --function=addconfiguration2 -v /tmp/dump
pg_restore: connecting to database for restore
pg_restore: implied data-only restore

After the attempts I verified if the function was created using \df inside psql, but without success.

How could I restore functions using pg_restore?

The parenthesis is special to the shell.  You don't have to remove it, you have to quote/escape it to get it to pass through the shell.

pg_restore -d postgres --function='addconfiguration2(text)' -v /tmp/dump


how to find min max of sum? hello everyone i try to find minimum and maximum value of SUM(units_sold) i test some ways but i can't find best solution please help me !

    SELECT TOP 1    dp.product_name, SUM(fs.units_sold) AS SumOfunits_sold
    FROM           dim_product dp
    INNER JOIN  fact_sales fs
        ON     dp.product_code = fs.product_code
    GROUP BY
            dp.product_name
    ORDER BY
        SUM(fe.units_sold) DESC
UNION ALL
     SELECT TOP 1     dp.product_name, SUM(fs.units_sold) AS SumOfunits_sold
      FROM   dim_product dp
      INNER JOIN  fact_sales fs
      ON     dp.product_code = fs.product_code
      GROUP BY
             dp.product_name
      ORDER BY
                SUM(fe.units_sold) ACS ;

You could try this

WITH cte AS (
    SELECT dp.product_name, SUM(fs.units_sold) AS SumOfunits_sold
    FROM   dim_product dp
    INNER JOIN  fact_sales fs ON dp.product_code = fs.product_code
    GROUP BY dp.product_name
)
SELECT *
FROM (
    SELECT TOP 1 product_name, SumOfunits_sold
    FROM cte
    ORDER BY SumOfunits_sold DESC
) a
UNION
SELECT *
FROM (
    SELECT TOP 1 product_name, SumOfunits_sold
    FROM cte
    ORDER BY SumOfunits_sold
) b


SQL IN doesn't work to filter table So I have 'students' with the following attributes:
student_id, name, mentor, district_id. Where district_id is a foreign key. Mentors are also students, so mentor refers to a student_id.
And I have 'districts' with the following attributes:
district_id, country.
I want to get students who are from a specific country, whos mentors are from a specific country.
Here's how I get the students from specific country:
SELECT student_id,
FROM districts, students
Where students.district_id = districts.district_id
AND district.country = "Japan";

I do the exact same thing but with the intention of using it to filter out by mentors of students.
SELECT student_id,
FROM districts, students
Where students.district_id = districts.district_id
AND district.country = "USA";

So now I have two tables one for students from Japan, and one for students from the USA. To get Japanese students with American mentors I would 'combine' these two tables like this:
SELECT student_id,
FROM districts, students
Where students.district_id = districts.district_id
AND district.country = "Japan"
AND students.mentor in (
    SELECT student_id,
    FROM districts, students
    Where students.district_id = districts.district_id
    AND district.country = "USA"
);

The idea is that I check if the studentID given by students.mentor is in the table of studentID of people from the US.
However this doesn't work. Only some are filtered off. I checked by adding students.mentor to the SELECT in the first line. And comparing it to the IDs in the second table.
Here is an example of the what the tables would look like with the added check.
Table 1) Students in Japan:
100
101
102

Table 2) Students in USA:
201
202
203

Table 3) Students in Japan with USA mentors (wrong output)
101 201
102 301
103 789

Table 3) Desired output
101 201

You need to join the mentors to the students, which translates here to a self join of students and then join the districts twice, once for the students and once for the mentors. Then you can filter by the countries.
SELECT ss.student_id
       FROM students ss
            INNER JOIN students sm
                       ON sm.student_id = ss.mentor
            INNER JOIN districs ds
                       ON ds.district_id = ss.district_id
            INNER JOIN districts dm
                       ON dm.district_id = sm.district_id
       WHERE ds.name = '<the country the students should be from>'
             AND dm.name = '<the country the mentors should be from>';

Some notes:

It is recommended to use modern, explicit JOIN syntax over old style joins with a comma separated list in the FROM clause. The former are easier to write without errors, to read and understand and maintain.
Do not use double quotes for string literals. In SQL string literals are quoted using single quotes. MySQL does accept double quotes but your code will be more portable when you use single quotes. And you'll get used to it and aren't in for surprises should you write a query for another DBMS.
In this case the question was easy enough to understand without it. But next time make sure to include sample input, i.e. sample data for the tables you work with, preferably as INSERT statements. Your current result as well as the desired result is absolutely meaningless without it. Of course you should also include your desired result. But never omit sample data, if you want to help those who try to help you.


Relationship normalization in SQL Server I want to know should I remove PersonTypeId from Persons table or is it a good idea to have it in there?

You need it there so that you know what PersonType is Person is. Without it, you don’t know.

Normalisation means that you don’t have PersonTypeName in the Persons table. But PersonTypeID is fine and what you need.


Compare the same column on different dates I have two tables

project_key: project_id, key_id
project_report: project_id, key_id, level, created_at

I would like to compare the column level when created_at is now() and created_at is now()::date - 1.

Sample data:

project_id: 1, keyword_id: 1, level: 2, created_at: today
project_id: 1, keyword_id: 2, level: 3, created_at: today
project_id: 1, keyword_id: 3, level: 5, created_at: today
project_id: 1, keyword_id: 4, level: 6, created_at: today

project_id: 1, keyword_id: 1, level: 1, created_at: yesterday
project_id: 1, keyword_id: 2, level: 6, created_at: yesterday
project_id: 1, keyword_id: 3, level: 2, created_at: yesterday
project_id: 1, keyword_id: 4, level: 6, created_at: yesterday

In the end a report should be produced, the expected output:

Legend:

increased: today < yesterday
decreased: today > yesterday
not_changed: today =  yesterday

Output:

project_id: 1, increased: 1, decreased: 2, not_changed: 1

Also when one of the level(today or yesterday) is 0 the increased and decreased will change:

increased: today > yesterday
decreased: today < yesterday

First cut query, using a self full outer join:

SELECT
  coalesce(product_today.product_id, product_yesterday.product_id) AS product_id,
  sum(CASE WHEN product_yesterday.keyword_id IS NULL THEN 1 END) AS new_today,
  sum(CASE WHEN product_today.keyword_id IS NULL THEN 1 END) AS deleted_today,
  sum(CASE WHEN product_yesterday.level > product_today.level THEN 1 END) AS decreased,
  sum(CASE WHEN product_yesterday.level < product_today.level THEN 1 END) AS increased,
  sum(CASE WHEN product_yesterday.level = product_today.level THEN 1 END) AS not_changed
FROM (
  SELECT *
  FROM product_report
  WHERE created_at = current_date)
AS product_today
FULL OUTER JOIN (
  SELECT *
  FROM product_report
  WHERE created_at = current_date - INTERVAL '1' DAY
) AS product_yesterday
ON (product_today.project_id = product_yesterday.project_id
    AND product_today.keyword_id = product_yesterday.keyword_id)
GROUP BY project_id;

This query will handle products that're deleted or newly added. If you don't care about that you can simplify it to an inner join, so it only shows products that were neither deleted nor newly added:

SELECT
  product_today.product_id AS product_id,
  sum(CASE WHEN product_yesterday.level > product_today.level THEN 1 END) AS decreased,
  sum(CASE WHEN product_yesterday.level < product_today.level THEN 1 END) AS increased,
  sum(CASE WHEN product_yesterday.level = product_today.level THEN 1 END) AS not_changed
FROM product_report product_today
INNER JOIN product_report product_yesterday
ON (product_today.project_id = product_yesterday.project_id
    AND product_today.keyword_id = product_yesterday.keyword_id)
WHERE product_today.created_at = current_date
  AND product_yesterday.created_at = current_date - INTERVAL '1' DAY
GROUP BY project_id;

If you don't care about "yesterday", but rather "the previous entry", you can probably use a window function in a subquery, but it'll be fiddly to write without sample data and schema.


SQL Management Studio - Add-on / Extension to view Column Data Type Is there any way (built in) or free/paid add-on/Extension for SQL Management Studio that allows us to know exactly a data type of a column when we are writing a query?

In this specific project I have had situations where by looking a the query result I am not exactly sure of the datatype, if it is a number written in a nvarchar(max) column.... or if it is a tinyint, or int, or bigint, etc....

And I am spending so much time just to drill on the "tree list" to reach a speficic table and expand the Columns node to find out what am I dealing with....

A situation when you have bunch of JOINS (and allias) and sp_help <tablename> would require me always to go to some other query window and type more sql (table names)....  then it would be easier and faster just to have like a tooltip message of the column with extra info.

This is standard functionality of SSMS (and has been for a while, I tested in both SSMS 2014 and SSMS 17.2).

For example when you hover over a column the tooltip shows you the data type:

The intellisense autocomplete also shows the data type when you select a column with your up/down arrows:


PostgreSQL crash recovery We are planning to use PostgreSQL in our application but are concerned about crash-safety and recovery.

I can't find any database crash recovery method or process in PostgreSQL. I know there must be something in PostgreSQL.

Advice? So if anyone can help me out to find  this then that would be great.

Crash Safety

PostgreSQL is crash-safe within certain limits. It guarantees to always preserve committed data if the database system crashes or the host it's on reboots/loses power/crashes unexpectedly. That's what the D in ACID means - atomicity, consistency, isolation, durability.

The critical feature in PostgreSQL's crash safety is the write-ahead log (WAL). The documentation explains it much better than I ever could.

This crash safety is not a substitute for good backups.

PostgreSQL cannot protect you against file system corruption or hard drive failure. The storage system it uses needs to be reliable.
Hard drives and SSDs will fail, so RAID is necessary but not sufficient for protection. You need backups and/or replication to protect against multi-drive failures, file system corruption, etc.
There are certain settings documented in PostgreSQL that weaken the crash-safety guarantees it makes. For example, if you set fsync=off you give PostgreSQL permission to destroy your data if it crashes in exchange for going a bit faster.
PostgreSQL guarantees that it will not save uncommitted data, it will be lost if the system crashes before a transaction COMMITs. Statements not surrounded by BEGIN and COMMIT for this purpose commit when the statement returns.

There's more, but the short version is that Pg is crash-safe and durable, but you still need to look after the other layers in your infrastructure, which is why you need:

Backups

Don't just do a weekly pg_dump where you overwrite the same dump file over and over, that'll end in tears. Use more than one backup strategy, and keep historical backups not just a most recent backup.

For example, I keep both dumps and a continuous archiving/PITR replica. I keep daily pg_dumps for a week, then weekly dumps for a month and monthly dumps for a year to give me some timeline protection. For the PITR setup I refresh from a base backup every week; it's mostly there to give me the ability to recover to the nearest 5-minutes from any failure or catastrophic mistake. Nightly dumps won't help me recover the last 23 hours of data when I accidentally drop a table; PITR will.

There's lots more here:

Pg manual - backup and restore
Pg manual - streaming replication
Pg manual - high availability
Pg wiki - Backup category
Pg wiki - high availability

Don't be one of the people who turns up on the Pg list saying "my hard drive crashed, how can I recover my database?". Be like this guy, who asked the question for fun because he had backups but was curious if his DB could be recovered. (Answer: probably not).

Repair or recovery of corrupt databases

If you're reading this because you found it in a search and you suspect you have a corrupt Pg database, read this before attempting any repair.

If by crash recovery you mean a repair tool to fix a damaged database, no, there isn't anything much. pg_resetxlog may save you from corrupt/lost transaction logs, but will probably corrupt your database in the process, so you usually need to dump and reload afterwards. There isn't any kind of fsck tool, nor is there anything like myisamchk because unlike MyISAM, Pg's tables are designed not to get corrupted as a routine part of normal operation. It'd actually be quite nice to have some sort of pg_dbcheck, but it would probably not be able to tell you much more than "Yup, there's some corruption here, go find a backup".

The database just doesn't get damaged in the first place unless:

The underlying hardware fails
The OS or file system has a bug
PostgreSQL has a bug
PostgreSQL is explicitly configured not to be durable
The user did something silly like went around deleting things from the database directory

In all of these cases automated repair would be impractical anyway.

Keep good backups of any database, not just Pg. Don't rely on being able to repair corruption, because you probably won't be able to.


Performance in a single SQL Server 2014 database is slower than the rest This is an interesting one... I've inherited a SQL Server for our ERP system. It currently runs SQL Server 2014 CU 10. I've done some best practices and performance tuning on it and server performance has gone from the toilet to acceptable levels.

However, I have a query/stored procedure that when run on one specific database takes up to 7 minutes to run while on similar databases (there are 6 others, 1 has MUCH more data) it takes maybe 30 seconds tops. I've checked file permissions, I've diff'ed the SP's, I've checked database settings and I can't find anything that points to the slowness.

Does anyone have any recommendations on what I should be looking at?

I was able to get an execution plan excerpt from the databases (2MB limit on Brent's site).

This is the execution plan on the "bad" database.
This is the execution plan for the "good" database.

The only difference between the two is the "good" DB doesn't have the current data (i.e. older copy).

I've double checked my server settings in both environments & they appear to be identical. The only thing I can confirm so far is if I drop the "bad" database's compatibility level down to 2008 it performs perfectly. Yet the same database inside the dev environment at 2014 runs flawlessly.

There is a possible coding error in the stored procedure I'm checking. I can't get anything out using CTRL+L but I do if I attempt to run it.

The databases are not replicated. The server is a VM and the drives are all shared.
I've done a table comparison & they are identical. Compatibility level is the same as well as are indexes and stats.

I'll update again when I know more.

Also try to look the estimated execution plan. Besides, while executing your SP open a new session and look for wait_stats. That will give you more hints on what is your query waiting on.


Export table without BLOB column I have a big table with BLOB objects and some references from other tables to this one.
I prepare dev and test schema for my app by making expdp (data pump) from production, then anonymizing data on dev/test host. The problem is that this table with the BLOBs is so large, that copying dump file from production to test/dev and then importing it takes ages...

Is there any solution to export SCHEMA without some columns in tables (maybe some filter which replaces all BLOBs with empty BLOBs or NULLs)?

I consider also making a "just BLOB schema" and link it to the production schema, and not export it at all, but this could cause performance issues.

You could do that in Oracle 11gR2 with expdp and the REMAP_DATA option.

Create a function in a package that takes a blob as argument, and returns null (or an empty blob perhaps).
Call expdp as usual, adding:

REMAP_DATA=SCHEMA.TAB.BLOB_COLUM:SCHEMA.PACKAGE.YOUR_FUNCTION

Short example (schema: mat):

create or replace
package remap as
  function null_lob(col blob) return blob;
end remap;
/

create or replace
package body remap as
  function null_lob(col blob)
    return blob is
    eblob blob;
  begin
    return null;
  end null_lob;
end remap;
/

create table foo(a number, b blob);
-- insert a line with a 10M blob, commit

select a, length(b) from foo;

     A  LENGTH(B)
---------- ----------
     1   10485760

Plain export:

$ expdp mat/*** DIRECTORY=EXP_DIR TABLES=MAT.FOO
...
Total estimation using BLOCKS method: 11.06 MB
...
. . exported "MAT"."FOO"                                 10.00 MB       1 rows
...

Dump file is ~11M.

With the data remap:

$ expdp mat/*** DIRECTORY=EXP_DIR TABLES=MAT.FOO \
                REMAP_DATA=MAT.FOO.B:MAT.remap.null_lob
...
Total estimation using BLOCKS method: 11.06 MB
...
. . exported "MAT"."FOO"                                 5.398 KB       1 rows
...

Dump file is ~100k, importing that dump does indeed import a null in the column.

Another option would be to use an external table to do the load/unload, with the ORACLE_DATAPUMP driver (I think this was introduced in 10g, see external table concepts).

For the unload, use something like:

create table foo_unload
  organization external (
    type oracle_datapump
    default directory exp_dir
    location ('foo.dmp')
  )
as select a, empty_blob() b from foo;

(You can drop the external table right after creating it, that doesn't - by default anyway - delete the dump file.)

Then move the foo.dmp file to your target server & directory, and create the external table there:

create table foo_load (a number, b blob)
  organization external
    type oracle_datapump
    default directory exp_dir
    location ('foo.dmp')
  );

And finally load your target table (CTAS or truncate ...; insert /*+ append */ ... for instance):

create table foo as select * from foo_load;

Drop the external loader table once that's done.


Improving select query I was at an interview for a new job today and got asked a question regarding a SELECT query:

SELECT lastname FROM users WHERE name='John';

This SELECT query is to run over a million rows.

I was asked: How would you make that faster/more efficient?

I didn't have the answer, and some googling also didn't reveal anything to me.

I know the possibility of this question being closed is quite high, but I'd really like to know how you would make that query run faster. Any ideas?

The table may have a million or a billion or a trillion rows. The answer is the same:

Add an appropriate index on the columns used in the WHERE (and other clauses).

In this case, a simple index on (name) would be enough. Assuming that the number of rows that match the condition name = 'John' is small, say 0.1%, then the optimizer will use the index to find the matching rows and then read only those rows from the table (this is done with some additional lookups), to find the lastname values.

With a very large table, this may not be fast enough. If the percent of matching rows is 0.1% with a billion rows, that means a million rows with name = 'John' and a million lookups on the table. We can then have instead a "covering" index - on (name, lastname) - so we don't have to do the additional lookups on the table. Only the index will be used and the million values will be found with a simple index seek (and partial scan).

Response to your comment

  This is, as far as I can see, as simple as altering the table structure and just setting the name column to be an index. Correct?

Basically yes, with either ALTER TABLE ... ADD INDEX ... or CREATE INDEX ... The table structure doesn't really change, the index is an additional (physical) structure.


Mysql join query improvement and GROUP_CONCAT on GROUP BY I have two tables which I need to perform a left outer join on to get the result set I want. The query I have now works but its taking too long to process. Any suggestions?

Query:

SELECT Date_format(a.call_date, '%Y-%m-%d') Call_Date,
   Date_format(a.call_date, '%H:%i:%s') Call_Time,
   a.lead_id,
   customer_number,
   status,
   a.call_type,
   agent,
   skill,
   campaign,
   disposition,
   hangup,
   a.uniqueid,
   time_to_answer,
   talk_time,
   hold_sec,
   wrapup_sec,
   Date_format(start_time, '%H:%i:%s')  Start_Time,
   Date_format(end_time, '%H:%i:%s')    End_Time,
   Ifnull(a.transfered, b.transfered)   AS transfer,
   comments,
   location,
   duration,
   handling_time,
   number_dialed                        AS DID
FROM   cdr_temp a
   LEFT OUTER JOIN (SELECT USER,
                           Substring(number_dialed, 18, 11) AS transfered,
                           uniqueid
                    FROM   transfertable)
                   b
                ON a.uniqueid = b.uniqueid
WHERE  a.call_date BETWEEN '2019-01-01 00:00:00' AND '2019-03-23 00:00:00'
GROUP  BY a.lead_id,
      b.uniqueid

Tables:cdr_temp

transfertable

Index:

uniqueid and call_date for transfertable
uniqueid and lead_id for cdr_temp

Explain on query

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  a   ALL     NULL    NULL    NULL    NULL    1333    Using where; Using temporary; Using filesort
1   SIMPLE  transfertable   ref     uniqueid    uniqueid    22  test.a.uniqueid     1

PS: I need to concatenate skills and number_dialed on GROUP BY. I've tried using GROUP_CONCAT but it didn't work and I have no idea why.

UPDATE: I've tried this query using a temp table. Is it better?

CREATE TEMPORARY TABLE transfertable_temp (
    INDEX idx (uniqueid, transfered)
)
SELECT uniqueid, SUBSTRING(number_dialed, 18, 11) AS transfered
FROM transfertable WHERE call_date BETWEEN '2019-03-25 00:00:00' AND '2019-03-25 23:59:59';
SELECT
    DATE_FORMAT(a.call_date, '%Y-%m-%d') Call_Date,
    DATE_FORMAT(a.call_date, '%H:%i:%s') Call_Time,
    a.lead_id,
    customer_number,
    status,
    a.call_type,
    agent,
    GROUP_CONCAT(skill SEPARATOR ';') AS Skill,
    campaign,
    disposition,
    hangup,
    a.uniqueid,
    time_to_answer,
    talk_time,
    hold_sec,
    wrapup_sec,
    Date_format(start_time, '%H:%i:%s') Start_Time,
    Date_format(end_time, '%H:%i:%s') End_Time,
    IFNULL(a.transfered, b.transfered) transfer,
    comments,
    location,
    duration,
    handling_time,
    number_dialed AS DID
 FROM cdr_temp a
 LEFT JOIN transfertable_temp b
    ON a.uniqueid = b.uniqueid
 WHERE a.call_date BETWEEN '2019-03-25 00:00:00' AND '2019-03-25 23:59:59'
 GROUP BY a.lead_id,
            b.uniqueid

Generally, you should avoid JOIN ( SELECT ... ).  In the past it was notoriously inefficient.  In later versions it is sometimes somewhat slower than alternatives.

Instead, let's get rid of the JOIN some of the time:

...
    Ifnull(a.transfered, b.transfered)   AS transfer,
    ...
FROM   cdr_temp a
   LEFT OUTER JOIN (SELECT USER,
                           Substring(number_dialed, 18, 11) AS transfered,
                           uniqueid
                    FROM   transfertable)
                   b
                ON a.uniqueid = b.uniqueid
WHERE  ...
GROUP  BY a.lead_id,
          b.uniqueid

-->

...
    Ifnull(a.transfered,
             ( SELECT substring... FROM transfertable
                          WHERE uniqueid = a.uniqueid
             )
          )   AS transfer,
    ...
FROM   cdr_temp a
   -- gone:  LEFT OUTER JOIN
WHERE  ...
GROUP  BY a.lead_id,
          a.uniqueid  -- Note "a."

It is "improper" to GROUP BY only some of the non-aggregate columns; you will get random values for the others.  Is a "skill" associated with a "lead"?

It would be more informative to have SHOW CREATE TABLE for each table.  What are the PRIMARY KEYs of the tables?  Are there any other UNIQUE columns?


Matching substring to table column I have a mapping table which defines access.

   Us | Them
    ----------
    1    MA
    2    LA
    3    NA
    4    MA

Customers send in their role and we return our role which represents access inside our system.
A simple example is

MA = Master access
LA = Limited access
NA = No access
SA = Some access

We now will be receiving a list of roles from one of our customers,
this will be in the form of "MA;LA;NA" or "LA;NA" or even "LA;XX"

What I need to do is return the highest role back to them.
SO if it exists in our table a users role becomes the highest possible access.   Input is "LA;MA" I return 1.
Input is "NA;LA" I return 2.
Input is "does not exist" I return 3.

Again this is a simple example they have of 60 roles defined.
My question is what is a clean way of doing this?

I have a working solution which has a series of IF's and ELSE IF's where if the inputted string is like "MA" it represents a 1 and move on. However the code is rather verbose and pretty ugly, order matters and if the customer requests a new role say "Orange" and we insert it into the mapping table, I then would have to edit the stored procedure.

Any thoughts to point me in the right direction?

I will suggest to have a table valued function. This function will accept your string as an input parameter and then return a table by splitting the inputs separated by ";" in different rows with an index ID. You can use this function internally with you logic and determine the MAX(Us) from mapping table. Below is the sample example for the function.

CREATE FUNCTION [dbo].[funcSplit] ( @string VARCHAR(MAX) )
RETURNS
 @List TABLE ([Name] [nvarchar] (500))
AS
BEGIN

 DECLARE @name NVARCHAR(255)
 DECLARE @pos INT

 WHILE CHARINDEX(';', @string ) > 0
 BEGIN
  SELECT @pos  = CHARINDEX(';', @string )
  SELECT @name = SUBSTRING(@string , 1, @pos-1)

  INSERT INTO @List
  SELECT @name

  SELECT @string = SUBSTRING(@string , @pos+1, LEN(@string )-@pos)
 END

 INSERT INTO @List
 SELECT @string

 RETURN
END

Once implemented, use this function as below in your own logic to determine the roles.

SELECT MAX(us)
FROM YourMappingTable AS A
     INNER JOIN funcSplit('MA;LA') AS B
     ON A.Them = B.Name

Only thing is remaining is to include an exception for the NA or "Input Does not exists" case which can be easily handled.


SQL Server 2012 slower than 2008 I migrated a large website and database from an older server (Windows 2008 / SQL Server 2008 / 16 GB RAM / 2 x 2.5 GHz Quad Core / SAS disks) to a newer, much better server (Windows 2008 R2 / SQL Server 2012 SP1 / 64 GB RAM / 2 x 2.1 GHz 16 Core processors / SSD disks).

I detached the database files on the old server, copied and attached them on the new server. Everything went very well.

After that, I changed to compatibility level to 110, updated statistics, rebuild indexes.

To my huge disappointment, I noticed that most sql queries are much slower (2-3-4 times slower) on the new SQL 2012 server than on the old SQL 2008 server.

For example, on a table with around 700k records, on the old server a query on index took around 100ms. On the new server, the same query takes around 350 ms.

Same happens for all queries.

I would appreciate some help here. Let me know what to check/verify. Because I find it very hard to believe that on a better server with a newer SQL Server, the performance is worse.

More details:

Memory is set to max.

I have this table and index:

CREATE TABLE [dbo].[Answer_Details_23](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [UserID] [int] NOT NULL,
    [SurveyID] [int] NOT NULL,
    [CustomerID] [int] NOT NULL default 0,
    [SummaryID] [int] NOT NULL,
    [QuestionID] [int] NOT NULL,
    [RowID] [int] NOT NULL default 0,
    [OptionID] [int] NOT NULL default 0,
    [EnteredText] [ntext] NULL,
 CONSTRAINT [Answer_Details_23_PK] PRIMARY KEY NONCLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDX_Answer_Details_23_SummaryID_QuestionID] ON [dbo].[Answer_Details_23]
(
    [SummaryID] ASC,
    [QuestionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

I executed this query:

set statistics time on;
select summaryid, count(summaryid) from Answer_Details_23 group by summaryid order by count(summaryid) desc;
set statistics time off;

OLD SERVER - SQL Server Execution Times:
   CPU time = 419 ms,  elapsed time = 695 ms.

NEW SERVER - SQL Server Execution Times:
   CPU time = 1340 ms,  elapsed time = 1636 ms.

EXECUTION PLANS uploaded here:
http://we.tl/ARbPuvf9t8

Later update:

AMD 2.1GHz Opteron 16 core processors look much worse than Intel 2.5GHz quad core processors
Great improvement changing windows power options from ballanced to high power
Further improvement changing max degree of parallelism to 8 and cost threshold to 4

Now, SQL Server Execution Times: CPU time = 550 ms, elapsed time = 828 ms.

It's still worse than the old server, but not that bad. If you have any other suggestions (other than local query optimizations) please feel free to comment.

Let me know what to check/verify

You have a performance problem. Follow a performance troubleshooting methodology like Waits and Queues to identify the bottleneck. The linked methodology shows you what to measure and how. Post here the findings and we can help with specific advice based on your actual measurements. As it is is too open and is anybody's guess. Narrowing it down to a specific issue will eliminate guesswork.

After update

The plans are quite different. The old plan had a stream aggregate low on the stack which actually has a bad  cardinality estimate (141k vs. 108k) and the hash math further mispredicts, the other way (35k vs. 108k). The new plan does not have the stream aggregate and has accurate estimates all the way to the top. Of course, this does not explain why the old plan was executing faster.

The bottom scans have a slightly different row number (not significant) but quite different costs: old is 2.49884 (IO 2.28979 CPU 0.20905) vs new 1.59109 (IO 1.53868 CPU 0.0524084). Again would point toward a better 2012 execution (the index rebuild has perhaps reduced fragmentation?).

What is very different is the number of threads: 32 in new (each getting ~23k rows) vs. 8 in old (each getting ~95k rows). The table is fairly narrow. It could be that the large number of threads is actually hurting performance because of much much more frequent cache invalidations. I would try:

eliminate HyperThreading in the new server config (if any) and/or
try the query with a DOP 8.

Noticed your comment:

  Added the execution plan with maxdop 8 Query is actually faster this way

It probably is just CPUs stepping on each other toes. With SSDs in place the IO is probably next to nothing so and the table is definitely too small to warrant 32 scanners. That exchange swap is probably invalidating L1/L2 constantly.


Enumerate Oracle tables in PLSQL How do you run a query to list all tables, or all tables where tableName like '%abc%'.

Just poking around, I tried the following with no success:

select * from SYS.TAB$
select * from SYS.OBJ$ where Name like '%abc%'

I'm trying to re-learn in Oracle some basic things I know in MSSQL.

Depending on your privilege level, you'd want to use DBA_TABLES, ALL_TABLES, or USER_TABLES.  There is a StackOverflow thread that walks through this in some detail.

By default, Oracle identifiers are going to be stored in upper case in the data dictionary and queries are case-sensitive.  Most likely, if you have a table whose name contains the string "abc", you'd need to search in upper case, i.e.

SELECT *
  FROM dba_tables
 WHERE table_name LIKE '%ABC%'


How do I replace strings of a table from another table column How do I update/replace the value of the first table from the list of my second table in SQL. Sorry im not so good in using replace() of SQL especially replacing from values base from different table

First table.

ID | Value
======================
 1 | Fruits[Apple]
 2 | Fruits[Apple,Mango]
 3 | Apple[Red,Green]

Second table

Search | Replace
=========================
Apple  | Orange
Green  | Yellow

In Oracle SQL it goes like this :

UPDATE FIRST_TABLE FT
   SET FT.VALUE =
       (SELECT ST.REPLACE FROM SECOND_TABLE ST WHERE FT.VALUE = ST.SEARCH)

But for this this to work properly, keep in mind that the attribute Search in the SECOND_TABLE must be unique, otherswise you'll need to rearange your query.


select records by a particular month wise in sql I want to get the records for a month of January'2017 and here is the one row sample of my huge table

Table:

CUSTTRIPID  TPTRIPID    CUSTOMERID  ENTRY_TOLLTXNID EXIT_TOLLTXNID  ENTRYTRIPDATETIME   EXITTRIPDATETIME    TRIPIDENTMETHOD TRIPCHARGETYPE  ENTRYLANEID ENTRYPLAZAID    EXITLANEID  EXITPLAZAID VEHICLENUMBER   VEHICLESTATE    VEHICLECLASS    VEHICLEID   TAGREFID    TOLLAMOUNT  FEEAMOUNTS  DISCOUNTSAMOUNT OUTSTANDINGAMOUNT   TRIPSTAGEID TRIPSTATUSID    TRIPSTATUSDATE  POSTEDDATE  PAYMENTSTATUSID CREATEDDATE CREATEDUSER UPDATEDDATE UPDATEDUSER REFLINKTRIPID   PLAZA
39038181    34040337    20272679    35126165    35126165    2017-05-04 08:15:54.000 2017-05-04 08:15:54.000 980 P   NULL    NULL    NULL    NULL    KA03D8435   KA  VC10    272614  34161FA8203289720249F4C0    30.00   0.00    0.00    30.00   3   1   2017-05-04 08:31:54.657 2017-05-04 08:31:54.657 456 2017-05-04 08:32:11.130 TRIPPOST    2017-05-04 08:32:11.130 TRIPPOST    0   BangaloreNelamangalaTollPlaza

I have applied this query to get the Date part because the requirement was to retrieve CUSTOMERID more than 2 records with time (of a month).

Query:

SELECT   TOP (10) [CUSTOMERID],
         CONVERT(VARCHAR(20), [ENTRYTRIPDATETIME], 101) AS DATEPART,
         CONVERT(VARCHAR(20), [ENTRYTRIPDATETIME], 108) AS TIMEPART
FROM     [ISSUER].[TOLLPLUS].[TP_CUSTOMERTRIPS]
GROUP BY [CUSTOMERID],[ENTRYTRIPDATETIME]
HAVING   COUNT(*) > 2;

Result:

CUSTOMERID  DATEPART    TIMEPART
20010438    12/16/2016  22:16:55
20015278    1/28/2017   5:18:20
20108092    2/10/2017   0:37:09
20018833    1/27/2017   4:53:32
20199820    1/27/2017   5:34:24
20124122    2/1/2017    2:47:01
20240710    3/21/2017   13:02:18
20143691    3/21/2017   8:08:03
20074212    12/15/2016  7:28:56
20114518    12/18/2016  6:11:31
20009754    12/17/2016  20:38:40
20072106    3/17/2017   19:37:20
20092287    1/28/2017   10:54:06
20055028    1/27/2017   4:36:51
20003676    4/23/2017   1:34:11

Now how to get the result for a month, assume for January 2017 ?

You could use a WHERE clause to filter the desired rows:

SELECT   TOP (10) [CUSTOMERID],
         CONVERT(VARCHAR(20), [ENTRYTRIPDATETIME], 101) AS DATEPART,
         CONVERT(VARCHAR(20), [ENTRYTRIPDATETIME], 108) AS TIMEPART
FROM     [TP_CUSTOMERTRIPS]
WHERE    YEAR([ENTRYTRIPDATETIME]) = 2017
AND      MONTH([ENTRYTRIPDATETIME]) = 2
GROUP BY [CUSTOMERID],[ENTRYTRIPDATETIME]
HAVING   COUNT(*) > 2;

CUSTOMERID | DATEPART   | TIMEPART
---------: | :--------- | :-------
  20124122 | 02/01/2017 | 02:47:01
  20108092 | 02/10/2017 | 00:37:09

db<>fiddle here

Or use a range of dates:

SELECT   TOP (10) [CUSTOMERID],
         CONVERT(VARCHAR(20), [ENTRYTRIPDATETIME], 101) AS DATEPART,
         CONVERT(VARCHAR(20), [ENTRYTRIPDATETIME], 108) AS TIMEPART
FROM     [TP_CUSTOMERTRIPS]
WHERE    [ENTRYTRIPDATETIME] >= CAST('20170201' AS DATETIME)
AND      [ENTRYTRIPDATETIME] <  CAST('20170301' AS DATETIME)
GROUP BY [CUSTOMERID],[ENTRYTRIPDATETIME]
HAVING   COUNT(*) > 2;

db<>fiddle here


Postgres transactions with persistent connection So I have a program written in NodeJS, that has a pool of persistent connections, all works as expected except when I execute queries similar to the following:

BEGIN;
  INSERT INTO user(id, name) VALUES ('ce1b346cf35493d30', 'John Doe');
  INSERT INTO login(auth_id, user_id) VALUES ('123456', 'ce1b346cf35493d30') RETURNING id;
COMMIT;

If the transaction succeeds then all is good, but if it fails e.g. due to unique constraint on (auth_id, user_id) then the first time with throw the error I would expect due to the unique constraint, however if I attempt to run any other query (whether I expect it to succeed or fail) I get the following error:

ERROR: current transaction is aborted, commands ignored until end of transaction block
SQL state: 25P02

And the only way to fix it is to close and reopen the database connection. It would seem that the transaction is still somewhat left hanging in some way. The connection state gets stuck as idle in transaction (aborted). Is it normal that only one begin/commit transaction can be used per connection or is there some way to clear the transaction without having to re-establish a connection?

Conceptually, one connection can deal with only one transaction at a time; but you can have more than one in a single session, as far as you commit (or rollback) your transaction before you start a new one.

This would be an example that works:

CREATE TABLE
    "user" (id text, "name" text, primary key(id)) ;
CREATE TABLE
    "login" (id serial, auth_id text, user_id text references "user"(id), primary key(id));

BEGIN;
  INSERT INTO "user"(id, "name") VALUES ('ce1b346cf35493d30', 'John Doe');
  INSERT INTO login(auth_id, user_id) VALUES ('123456', 'ce1b346cf35493d30') RETURNING id;
COMMIT;

BEGIN;
  INSERT INTO "user"(id, "name") VALUES ('strangeid', 'Nick');
  INSERT INTO login(auth_id, user_id) VALUES ('56789', 'ce1b346cf35493d30') RETURNING id;
COMMIT;

Some connection tools (for instance, pgAdmin in default configuration) will have an autocommit feature (in pgAdmin, you control it by means of menu checks at "Query > AutoCommit" and "Query > AutoRollback"). If these are checked, a transaction that fails would be AutoRollbacked. If you disable these checks, the next example would behave like you indicate:

DROP TABLE IF EXISTS "user" ;
DROP TABLE IF EXISTS login ;
CREATE TABLE
    "user" (id text, "name" text, primary key(id)) ;
CREATE TABLE
    "login" (id serial, auth_id text, user_id text references "user"(id), primary key(id));

-- This transaction block will work
BEGIN;
  INSERT INTO "user"(id, "name") VALUES ('ce1b346cf35493d30', 'John Doe');
  INSERT INTO login(auth_id, user_id) VALUES ('123456', 'ce1b346cf35493d30') RETURNING id;
COMMIT;

-- This one will not. As AutoRollback is off, the transaction won't be
-- commited (because of the error) nor Rolled Back
BEGIN;
  INSERT INTO "user"(id, "name") VALUES ('strangeid', 'Nick');
  INSERT INTO login(auth_id, user_id) VALUES ('56789', 'ce1b346cf35493d30') RETURNING id;
COMMIT;

-- It will generate the following error
ERROR: current transaction is aborted, commands ignored until end of transaction block
SQL state: 25P02

The way to make it work again, is by "finishing" your transaction. As you cannot commit it, you have to roll it back:

ROLLBACK;

this now works...

BEGIN;
  INSERT INTO "user"(id, "name") VALUES ('anotherid', 'Mike');
  INSERT INTO login(auth_id, user_id) VALUES ('56789', 'ce1b346cf35493d30') RETURNING id;
COMMIT;

Your Node.js client connection framework might have an autocommit/autorollback feature that might automatically finish the job for you [don't know enough about Node.js DB clients to advise on them]. If not, you get out of the "aborted" state by just rolling back and finishing your transaction.


Don't delete exist data with SSMA I'm trying to use Microsoft's SQL Server Migration Assistant for Sybase 5.3 ('SSMA') to migrate multiple Sybase databases (that have the same schema) to a single MSSQL database.

The first database migrated fine. But when I try to migrate data from the next one, I see lots of warnings like:

The table 'MyDatabase.dbo.MyTable' contains data.
If you continue with data migration, all data in the table will be deleted.

I don't want the existing data to be deleted, but can't find a way to do this in SSMA.

I figured it out. I had to go to Tools > Options > General > Migration and set Extended data migration options to Show.

Then I had a new Data Mig... tab for each table, and had to uncheck Truncate SQL Server table and hit Apply individually for each table. Rather laborious when you have a lot of tables... but it worked.


Metrics to prove or disprove the need for additional DBA's? We currently have 2 DBA’s, me and one other guy and we both feel the need for an additional resource (or two). I have had the conversation with my direct manager on this topic a couple different times, but am having difficulty in getting the notion sold. Most recently our manager shared with us that it is really good to come up with our “soft values” list of things that we want or feel we need to be doing, but what will really help is to come up with “hard values”, showing dollar savings.

Could you help me in understanding first of all if our perceived need is legitimate, and if so, how do we go about coming up with this “hard values” list. I feel like we could come up with those things that we need to do in order to save the company money, but that could just get put at the front of our priorities list, and that much more stress on us in the process.

To give you a high level look at our environment, here are the things that I am looking at in terms of what we are providing support for.

  Production servers – 30+
  Production Databases – 200+
  SQL Versions – 2008/2008R2/2012, looking into 2014 now, and 2016 later in the year
  Applications(3rd party and home grown) – 20+
  Application Teams Supported – 6
  Virtualization – 75% virtualized 25% physical
  Clusters – 3+ more planned
  Replication – 1 distribution server, 2 subscription servers, 24 subscriptions, 6 publication servers, 12 publications
  Log shipping – 8 primaries +more planned, 4 secondaries + more planned, 49 log shipped databases + more planned
  Availability Groups – None at the moment, but exploring the possibility
  Average application upgrade/installs per year that drive version change or DBA resources – 2-3
  Patching (SP, CU’s) – Non-existent at the moment unless an issue comes up
  Cumulative database size – 14TB+
  Reporting Servers – One scale-out deployment consisting of 2 servers, neither of us are well versed in SSRS
  Analysis Server – Two servers, neither of us are well versed in SSAS

What metrics should we use to prove or disprove the need for additional DBA's?

My initial intention was demonstrating our current capacity vs. our planned projects and repeating tasks, but we need to demonstrate the ROI on hiring additional resources.

Demonstrating ROI on hiring additional DBAs is hard since DBAs don't generate revenue by themselves. They do however allow the organization to function and generate revenue, just like any other supporting staff. A lack of staff in any department makes the organization function less optimal and lose money or lose opportunity. So there is a cost from lack of investment rather than a return on investment.

If you feel you need additional DBAs you must know what tasks you are unable to perform because of lack of time/skill, if it's lack of skill  (such as the SSAS stuff you mentioned) that can be trained, if it's lack of time it could be temporary or structural. I've been in situations where you just know that your life would be a lot easier if you could just automate this and that or set up some alerting but just never get around to because of too much reactive or repetitive work and general fire fighting.

Once you have determined why your team isn't functioning as well as it should it's time to document it.

First of all, document what you do now, why that takes up all your time and make clear up front to your manager the existing problems that need solving are absolutely none of your team's fault. Make sure you don't give the impression that the problem would be solved by hiring 'better' DBAs.
Make an excel sheet showing your workload for the coming weeks if you can, charts impress, and it can show your workload in a single slide.

Once you are clear on that, document all additional tasks and everything that needs improving, but especially document what the business risks and possible costs are, and the savings could be. Also document how much man-hours you think would be needed to make those improvements and perform those tasks.

Things that go on the investment side of the equation are

Salary of the newly hired DBA
Price of training

Things that go on the savings side of the equation are

Projects not finished on time
Preventing downtime that makes the business lose money
Preventing legal risk
Preventing data loss
Consulting fees could be reduced
Cost of burning out employees because of too much work

Once you have compiled the list as complete as you can it should be clear what the best price/reward option is

Getting additional training
Getting additional DBAs
Hiring a temporary consultant to assist in automating stuff, monitoring stuff, performing a migration so that the existing team has more time
Changing the priorities put on you by the business so you get time to get things in place that will allow you to work more efficiently later on


MongoDB Document size too large I'm having trouble with my MongoDB Replica Set. I'm connecting to the server and using the database fine from two servers but on another server I'm getting the following error:

bson.errors.InvalidDocument:
    BSON document too large (2119 bytes) -
    the connected server supports BSON document sizes up to 0 bytes.

I have no idea where to begin on this, my MongoDB Server version is 2.4.10, the code on each server is the same with some configuration file differences. The DB Server is on AWS as are the two working servers. The server that is not working is on a Digital Ocean server.

I have allowed the Digital Ocean server access to the DB server with AWS Security Groups and it seems to be connecting fine.

Also, I am able to access and update the database from my local machine using the command line and RoboMongo.

The replica set was setup with the internal AWS addresses. So when python connected with MongoReplicaSetClient and the server sent back the addresses it couldn't find the other members of the replica set.

You can update the replica set using rs.conf() to get the config and then rs.reconfig() to set your new configuration.

Or, as I did, add the internal AWS address to the server hosts file, which I was able to do because it was not the internal AWS IP that was used.


How to add a column from another table? I'm starting to using SQL statements in my Oracle database XE (tables and relations made by me) and I'm having a hard time to understand how can I put a column from another table.

The example in this case is:

I have two tables that are interconnected, ORDERS and PRODUCT.

In ORDERS there are the following attributes:

ORDERID (defined as Primary Key), PRODUCT_NAME (a Foreign Key from the PRODUCT table) and QUANTITY.

In PRODUCT there are:

NAME (defined as Primary Key) and PRICE.

I want to add a column that gets the PRICE from the PRODUCT table and insert it to the ORDERS table according to the PRODUCT_NAME, obviously.

How can I do it?
Are there any workarounds?

Please, keep in mind that I'm a newbie and my knowledge is very limited.

The original question was:

  I want to add a column that gets the PRICE from the PRODUCT table and
  insert it to the ORDERS table according to the PRODUCT_NAME,
  obviously. How can I do it? Are there any workarounds? Please, keep in
  mind that I'm newbie and my knowledge is very limited.

and the ultimate reply (after much communication through comments - see above), my response was

  And you can't use triggers to make updating automatic? I'd say they
  are the way to go!

Which worked.


how to find out the log backups that I need to restore in order to bring log shipping from standby to online? I have log shipping set up from server1 to server2.

on server1 transaction logs backups are taken every 15 min to a network share folder.

these transaction log backups are then copied (afte 1 hour) to server2 where they are restored.

question:
in the event that I need to bring any of these databases from standby to online, how do I find out what transaction logs need to be restored for each database?

I found these 2 good links:

Your poor man’s SQL Server log shipping-PowerShell version

Automate Restoration of Log Shipping Databases for Failover in SQL Server

this second one did not work for me:

on msdb:

 SELECT destination_dir, destination_database, last_file_loaded
 FROM log_shipping_plans a INNER JOIN log_shipping_plan_databases b
 ON a.plan_id=b.plan_id

this script is to be run on server1 and gives me the latest log backup that is available

--------------------------------------------------------
--the latest backup for each database
--filtered by backup type
--------------------------------------------------------
declare @backup_type CHAR(1) = 'L' --'D' full, 'L' log

                ;with Radhe as (
                    SELECT  @@Servername as [Server_Name],
                    B.name as Database_Name,
                    ISNULL(STR(ABS(DATEDIFF(day, GetDate(), MAX(Backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,
                    ISNULL(Convert(char(11), MAX(backup_finish_date), 113)+ ' ' + CONVERT(VARCHAR(8),MAX(backup_finish_date),108), 'NEVER') as LastBackupDate
                    ,BackupSize_GB=CAST(COALESCE(MAX(A.BACKUP_SIZE),0)/1024.00/1024.00/1024.00 AS NUMERIC(18,2))
                    ,BackupSize_MB=CAST(COALESCE(MAX(A.BACKUP_SIZE),0)/1024.00/1024.00 AS NUMERIC(18,2))
                    ,media_set_id = MAX(A.media_set_id)
                    ,[AVG Backup Duration]= AVG(CAST(DATEDIFF(s, A.backup_start_date, A.backup_finish_date) AS int))
                    ,[Longest Backup Duration]= MAX(CAST(DATEDIFF(s, A.backup_start_date, A.backup_finish_date) AS int))
                    ,A.type
                    FROM sys.databases B

                    LEFT OUTER JOIN msdb.dbo.backupset A
                                 ON A.database_name = B.name
                                AND A.is_copy_only = 0
                                AND (@backup_type IS NULL OR A.type = @backup_type  )

                    GROUP BY B.Name, A.type

                )

                 SELECT r.[Server_Name]
                       ,r.Database_Name
                       ,[Backup Type] = r.type
                       ,r.DaysSinceLastBackup
                       ,r.LastBackupDate
                       ,r.BackupSize_GB
                       ,r.BackupSize_MB
                       ,F.physical_device_name
                       ,r.[AVG Backup Duration]
                       ,r.[Longest Backup Duration]

                   FROM Radhe r

                    LEFT OUTER JOIN msdb.dbo.backupmediafamily F
                                 ON R.media_set_id = F.media_set_id

                    ORDER BY r.Server_Name, r.Database_Name

and this script is to run on server2 and gives me the latest transaction log backup that has been restored:

-----------------------------------------------------------------
-- get the latest transaction log backup restored
-- modified from
--http://www.sqlservercentral.com/scripts/Backup/127480/
-----------------------------------------------------------------
SET NOCOUNT ON
SELECT
     rh.*,
    destination_database_name
    ,bmf.physical_device_name
    ,restore_date
FROM        msdb.dbo.restorehistory rh
INNER JOIN  msdb.dbo.backupset          as bs   ON bs.backup_set_id = rh.backup_set_id
INNER JOIN  msdb.dbo.backupmediafamily  as bmf  ON bs.media_set_id  = bmf.media_set_id
WHERE restore_history_id IN
    (
    SELECT MAX(restore_history_id)
    FROM msdb.dbo.restorehistory
    WHERE restore_type = 'L'
    AND destination_database_name IN
        (
        SELECT DISTINCT destination_database_name
        FROM msdb.dbo.restorehistory
        )
    GROUP BY destination_database_name
    )
ORDER BY rh.restore_date DESC


Oracle connection from Squirrel SQL I am trying to set up Squirrel SQL for the first time on my Ubuntu Desktop. Here is the SQLPlus command I use to connect to my DB:

 sqlplus64 "user/pw@pashratgdb01.lex.bb.com:1876/atgpen1.lex.bb.com"

So I try to set this up in Squirrel SQL as follows with the Oracle Thin Driver:

jdbc:oracle:thin:@pashratgdb01.lex.bb.com:1876:atgpen1.lex.bb.com"

When I do this, I get the following error:

java.util.concurrent.ExecutionException: java.lang.RuntimeException: java.sql.SQLException: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

at java.util.concurrent.FutureTask.report(FutureTask.java:122)
at java.util.concurrent.FutureTask.get(FutureTask.java:202)
at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.awaitConnection(OpenConnectionCommand.java:132)
at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.access$100(OpenConnectionCommand.java:45)
at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand$2.run(OpenConnectionCommand.java:115)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
at java.util.concurrent.FutureTask.run(FutureTask.java:262)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.lang.RuntimeException: java.sql.SQLException: Listener refused the connection with the following error:

ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.executeConnect(OpenConnectionCommand.java:171)
at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.access$000(OpenConnectionCommand.java:45)
at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand$1.run(OpenConnectionCommand.java:104)
... 5 more
Caused by: java.sql.SQLException: Listener refused the connection with the following error:

ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:743)
at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:666)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:566)
at net.sourceforge.squirrel_sql.fw.sql.SQLDriverManager.getConnection(SQLDriverManager.java:133)
at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.executeConnect(OpenConnectionCommand.java:167)
... 7 more
Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

at oracle.net.ns.NSProtocolStream.negotiateConnection(NSProtocolStream.java:275)
at oracle.net.ns.NSProtocol.connect(NSProtocol.java:264)
at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1452)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:496)
... 12 more

Your original connection string:

sqlplus64 "user/pw@pashratgdb01.lex.bb.com:1876/atgpen1.lex.bb.com"

This specifices atgpen1.lex.bb.com as a service name. The JDBC syntax you used specifies a SID. Service name identifies a service within a database. SID identifies an instance within a database. Those two are not the same.

Instead of this:

jdbc:oracle:thin:@pashratgdb01.lex.bb.com:1876:atgpen1.lex.bb.com"

Use this:

jdbc:oracle:thin:@//pashratgdb01.lex.bb.com:1876/atgpen1.lex.bb.com"


mysql update based on select result, How combine? Here are two SQL statements. One is an select statement and the other is a update statement. I want to combine them.

Select

SELECT * FROM test WHERE `id`='1'

Update

UPDATE test SET `select_count`=`select_count`+'1' WHERE `id`='1'

Based on your question I think you need to include SELECT statement in UPDATE statement:

Sample SQL in below:

create table test1
(ID int,
select_count int,
select_name varchar(50))

Insert into test1 values (1,1,'A')
Insert into test1 values (2,2,'B')

UPDATE test1
SET select_count = select_count + 1
where select_count = (SELECT select_count FROM test1 WHERE select_name = 'A')

Another way:  If you have 2 tables (TableA and TableB)

UPDATE TableB
SET TableB.value = (
    SELECT TableA.value
    FROM TableA
    WHERE TableA.name = TableB.name
)
where TableB.value < X;


how to install mysql on mac? My Mac runs Mac OS X, version 10.7.5., so it's a 64 bit system.

First, I downloaded mysql-5.5.28-osx10.6-x86_64.dmg.

Then I double clicked mysql-5.5.28-osx10.6-x86_64.dmg, it's mounted, and shows

mysql-5.5.28-osx10.6-x86_64.pkg -> I double-clicked it to run, after clicked buttons a few times it's said to be successfully installed. However, I could not see anything in Applications.
MySqlStartupItem.pkg -> I double-clicked to run, also successfully done. However, I still could not see anything in Applications.
MySQL.prefPane -> I double-clicked it to run. Now I see a window jumped out, with a button Start/Stop MySQL Server, I clicked it, now it says "The MySQL Server Instance is running".

I'm a bit lost here:

Q1. Why until now there's nothing in the Applications? I can't see any item or folder like mysql there. Is it that every time I want to run mysql, I have to mount the installation package?

Q2. What did MySQLStartupItem.pkg install?

Q3. Are there any client side tools inside the installation package? I mean, for example, Microsoft SQL Server for Windows, there are Microsoft SQL Server; and there are Microsoft SQL Server Management Studio, which serves as a client tool to browse databases and doing queries; also, if I download "Microsoft SQL Server Express with Tools" package, it will install both. Does mysql-5.5.28-osx10.6-x86_64.dmg contain only the server, or also a client tool inside?

You can install it via Homebrew

brew install mysql

This will install the mysql deamon and the cli tool.


How to monitor a single db of an instance in SQL Server I am trying to program against a vendor supplied database, and would like to understand what SQL statements or procedures they are calling.

Is there a way I can monitor a specific database?  The system was setup with both the production and test database in the same instance (so monitoring an instance is too broad).

You can accomplish this with the SQL Server Profiler.

http://msdn.microsoft.com/en-us/library/ms181091.aspx

First you will want to create a trace.

http://msdn.microsoft.com/en-us/library/ms175047.aspx

Then you will want to specify the events you want to track.

http://msdn.microsoft.com/en-us/library/ms188674.aspx

Then setup a filter for the database name.

http://msdn.microsoft.com/en-us/library/ms175520.aspx


How to definitively clear away database before restoring from new version of backup file We are a team of mathematicians (i.e., no DBA experience).

We are migrating from an old server box (SQL Server 2012) to a new server box (SQL Server 2017).  For each database on the old server, we made a backup copy, ported it over to the new server, and restored it to its full database glory from the backup file.

So far, so good.

However, there was a delay in our move-in date and we are still using the old server.  As we continue to work on the old server, changes continue to accrue in those databases.  So it means that the copied databases sitting on the new server are now obsolete and out-of-date.  (Ironic, isn't it?)

For those databases that have changed, we know we will eventually have to make new backup copies and port those over to the new server.

When it comes time, what is the smartest way to get rid of those now-obsolete databases on the new server before restoring the latest backup copies?

We worry that with the same names, some occluding or eclipsing might occur if we are not careful.  We want to make sure that the out-of-date copies on the new server are well and truly gone before restoring the latest versions.

Just use WITH REPLACE in your restore command and it will overwrite the existing databases on the destination.   Simple, and done.   You can DROP first, but its not necessary.

WITH REPLACE is the T-SQL keyword that corresponds to "overwrite the existing database" in the restore database GUI (added for future readers of this thread)

Oh...and are you running an RC version of 2017?  RTM isn't until next Monday 10/2...just an afterthought.


Is the Service Broker good for publishing data between servers? I'm trying to find a better solution when publishing data to a remote table.

So we have Server A and Server B.  Server A holds a master table which should be replicated to Server B.

Currently, we have this pretty chaotic stored procedure that runs every minute or so.  It locates any changes and inserts them into a temporary table on Server B.  Once loaded, it inserts new records or updates existing records from this temp table (The table is hit constantly and we want to limit any for of lock).

We don't have a DBA currently so I'm trying my best (I'm just a web developer) to figure out better solutions that could scale in the future.

Note: We have a couple dozen stored procedures that are like this.

Your best bet is to use Transactional Replication.

T-Rep is meant to be suited in your scenario as you want to replicate a table from server A to server B.

No need to run a store procedure every min (unless you are doing something specific with these SP's which you have not highlighted in your question).

Note: Only when you generate an initial snapshot in transactional replication, there will be locks on the table. So better to set up during maintenance window if your table is very large.

Also, in T-Rep changes are tracked asynchronously and applied asynchronously to the subscriber (destination) server. It has Wizards to allow you to configure T-Rep correctly with no coding efforts.

Service Broker on other hand will track data changes synchronously and apply to the destination server asynchronously. It will require more coding and depending on the amount of data to be transferred, it will be slow.

I have dealt with Service Broker and trust me ... its very hard to debug .. just like a black box vs Replication will show you errors on GUI and is easy to troubleshoot as well.


Best way to compare multiple non null/non zero fields in a row in T-SQL? I need to compare figures across a row and show where there is any mismatch, excluding zero and null values. So in the example, ids 1, 4 and 5 would be returned. What is the most painless way of doing this without a ton of where and or statements?

CREATE TABLE #XX
(
   ID  INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
   VAL1  INT,
   VAL2  INT,
   VAL3  INT,
   VAL4  INT,
   VAL5  INT
)

INSERT INTO #XX (VAL1,VAL2,VAL3,VAL4,VAL5) VALUES
(5,10,NULL,17,12),
(12,12,12,NULL,0),
(15,15,15,15,15),
(3,NULL,6,0,NULL),
(10,NULL,15,0,20)

Cheers.

CREATE TABLE #XX
(ID  INT IDENTITY(1,1) NOT NULL PRIMARY KEY, VAL1  INT, VAL2  INT, VAL3  INT, VAL4  INT, VAL5  INT)

INSERT INTO #XX (VAL1,VAL2,VAL3,VAL4,VAL5) VALUES
(5,10,NULL,17,12),
(12,12,12,NULL,0),
(15,15,15,15,15),
(3,NULL,6,0,NULL),
(10,NULL,15,0,20)

--First Unpivot the values so we can join the resulting values back to itself for comparisons.
--This will also eliminate NULL values from the comparison
;WITH cte AS (
SELECT id, ValueNumber, Value
FROM
(SELECT id, val1,val2,val3,val4,val5 from #XX) v
UNPIVOT
(Value FOR ValueNumber IN(val1,val2,val3,val4,val5))
AS unpvt)

--SELECT only distinct ID's
SELECT DISTINCT v1.id FROM cte v1

--Join the table back to itself based on the ID column
--so we can compare values within each row
INNER JOIN cte v2 ON v1.id = v2.id

--Check the values are different
WHERE v1.value <> v2.value

--Ensure we are only checking between different columns
AND v1.ValueNumber <> v2.ValueNumber

--Check neither value is 0
AND v1.Value <> 0
AND v2.Value <> 0

dbfiddle

The above seems to do what you need - if you don't know the particular columns you'll need to UNPIVOT for then this will need to be reformed into a dynamic UNPIVOT script.


Assign a unique count to each row in the resulting table of a select statement Let's assume we have a simple database that has the following schema: Article(Id, Body, AuthorId) and Author(Id, Name). Suppose that we want to develop a page where each author will be able to view his own posts only. There is only one requirement:

When the author wants to see his posts, the SQL query should allow him to see a counter for each item, starting at 1.

Example
Here's a simple set up:
(1,"Hi",1), (2,"there",1), (3,"test",2), (4,"foo",1).

By executing some sort of SQL statement: `select count, body where authorid=1;
we want to get:
(1,"Hi",1), (2,"there",1), (3,"foo",1).

Question
As you can see, the suggested statement wont work because count does not make sense yet. My question is, is there any way to assign a local count for each item  to produce the result above?

You can use ROW_NUMBER, like this:

SELECT
    ROW_NUMBER()OVER(ORDER BY ID ASC) AS [Count],
    body
FROM @Article AS A
WHERE authorid=1

Count                body
1                    Hi
2                    there
3                    foo


MySQL 5.7.15 turn off strict mode mysql  Ver 14.14 Distrib 5.7.15, for Linux (x86_64) using  EditLine wrapper

I'm trying to configure my MYSQL instance to turn off some of the stricter settings currently I've done this:

   mysql> select @@GLOBAL.sql_mode;
    +-------------------------------------------------------------------------------------------------------------------------------------------+
    | @@GLOBAL.sql_mode                                                                                |
    +-------------------------------------------------------------------------------------------------------------------------------------------+
    | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |

sudo vi /etc/mysql/mysql.cnf

[mysqld]
sql-mode=""

mysqld restart. Then try to see what the global SQL mode is:

mysql> select @@GLOBAL.sql_mode;
        +-------------------------------------------------------------------------------------------------------------------------------------------+
        | @@GLOBAL.sql_mode                                                                                |
        +-------------------------------------------------------------------------------------------------------------------------------------------+
        | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |

Anyone any ideas on what to try next. Can't seem to get this setting to stick permanently and can't see any other files to try.

Edit:
locate returns:

/etc/alternatives/my.cnf
/etc/mysql/my.cnf
/etc/mysql/my.cnf.fallback
/var/lib/dpkg/alternatives/my.cnf

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

On Aug 05, 2015, I answered the post Set sql_mode “blank” after upgrading to MySQL 5.6

In my answer, I explained how Oracle created an additional my.cnf called /usr/my/cnf.

It has this (keep in mind this is MySQL 5.6 I am answering back then)

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

I guess some developer just gave up putting it in the code and slapped up a configure file as some demented shortcut.

If you have such a file, please comment out the last line and restart mysqld.

MySQL 5.7's default value for sql_mode is as mentioned

ONLY_FULL_GROUP_BY STRICT_TRANS_TABLES NO_ZERO_IN_DATE NO_ZERO_DATE ERROR_FOR_DIVISION_BY_ZERO NO_AUTO_CREATE_USER NO_ENGINE_SUBSTITUTION

UPDATE 2017-04-27 17:23 EDT

I just ran mysqld --help --verbose | head -13 and got this

$ mysqld --help --verbose | head -13
2017-04-27T21:17:13.237941Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2017-04-27T21:17:13.238060Z 0 [Warning] Changed limits: max_connections: 214 (requested 500)
2017-04-27T21:17:13.238065Z 0 [Warning] Changed limits: table_open_cache: 400 (requested 2048)
mysqld  Ver 5.7.17-log for Linux on x86_64 (MySQL Community Server (GPL))
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Starts the MySQL database server.

Usage: mysqld [OPTIONS]

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

Please run mysqld --help --verbose 2>/dev/null | head -13 | tail -1

Check every file mentioned

UPDATE 2017-04-29 19:03 EDT

The /usr/my.cnf trick I mentioned in Set sql_mode “blank” after upgrading to MySQL 5.6 comes from a post written by Morgan Tocker under the heading "Changes in MySQL 5.6". This bait-and-switch method for sql_mode may not apply in this case since this is MySQL 5.7.

As I mentioned in my earlier comment, I have successfully sql_mode by adding this to /etc/my.cnf

[mysqld]
sql_mode=''

You should not have to restart mysqld. You login to mysql and run

SET GLOBAL sql_mode='';
SELECT @@GLOBAL.sql_mode;

This will set incoming connections to a blank sql_mode

This will not change the sql_mode of currently established connections.

Restarting mysqld will guarantee the all incoming connections will have it blank.

If you cannot restart mysqld, you must restart your app/web servers and make them disconnect. Then,m restart your app/web servers to establish connections.

Please try this and let us the results.


Group_concat on IF Select MYSQL I have a query as below:

SELECT o.order_id,
       c.customer_name,
       Group_concat(p.product_name, ' x', p.product_quantity ORDER BY
       receipt_order ASC
       SEPARATOR
       '
') AS items,
       order_status,
       o.delivery_collection,
       o.order_total,
       o.payment_method,
       o.discount_value,
       o.order_time,
       o.order_note,
       IF(o.delivery_collection = 'delivery', (SELECT
       Group_concat(address_1, address_2, postcode SEPARATOR '
') AS delAdd
                                               FROM   address a
                                               WHERE
       c.customer_id = a.customer_id
       AND c.customer_type =
           a.customer_type), NULL)
FROM   orders o,
       order_products p,
       customer c
WHERE  c.customer_id = o.customer_id
       AND c.customer_type = o.customer_type
       AND o.order_id = p.order_id
       AND order_time > Now() - INTERVAL 72 hour
GROUP  BY p.order_id
ORDER  BY order_status DESC,
          o.order_id DESC

The problem I have is that the Group_Concat() inside the IF concatenates but it doesn't include the separator and is not called delAdd.

I have also tried CONCAT_WS and CONCAT but all are giving the same results.

I am using MySQL 5.6.43 on 10.3.18-MariaDB server.

To alias the expression computed inside a Correlated Subquery, you will need to specify it outside the complete subquery. In this case, it will be outside the If(..) block:

SELECT o.order_id,
       c.customer_name,
       Group_concat(p.product_name, ' x', p.product_quantity ORDER BY
       receipt_order ASC
       SEPARATOR
       '
') AS items,
       order_status,
       o.delivery_collection,
       o.order_total,
       o.payment_method,
       o.discount_value,
       o.order_time,
       o.order_note,
       IF(o.delivery_collection = 'delivery',
          (SELECT Group_concat(address_1,
                               address_2,
                               postcode SEPARATOR '
') -- no need of aliasing here
           FROM   address a
           WHERE c.customer_id = a.customer_id
                 AND c.customer_type = a.customer_type),
          NULL) AS delAdd  -- Aliasing needed here
FROM   orders o,
       order_products p,
       customer c
WHERE  c.customer_id = o.customer_id
       AND c.customer_type = o.customer_type
       AND o.order_id = p.order_id
       AND order_time > Now() - INTERVAL 72 hour
GROUP  BY p.order_id
ORDER  BY order_status DESC,
          o.order_id DESC


How to find the number of queries executed since server startup in PostgreSQL Is there any way to get the number of queries executed since server startup in PostgreSQL?

In MySQL we get this information from status variable Questions, Queries.

Thanks.

I don't believe there is a way to do that.  The view pg_stat_database will give you info on the number of transactions and number of rows, but not the number of queries.  Also, the counters are reset only upon manual intervention, or upon crashes.  Clean restarts do not reset them.


Preserving full refresh data source I have a SQL Server database that gets updated every week with a completely new set of data.
I am currently using two staging databases. A series of view databases are connecting to these and one week the view tables have views that point at one set of tables contained in one of the staging databases and the then the views are altered the other week when the ingest of the refreshed data is complete. Staging tables are truncated before each load. There are 12 tables which range from 50Gb to 100Gb.
This is to avoid people waiting for the lengthy ingest to complete and keeps people working.
Are there any more efficient strategies? In Oracle I would turn a materialised view into a table and then refresh the view once the ingestion was complete.
I don't think there is a similar strategy for SQL Server. I am using SQL Server 2016 enterprise.

ALTER TABLE ... SWITCH would allow you to switch in each table when you want to change the "live" table.  The only requirement is to ensure both tables involved in the switch are in the same filegeoup, and have the same structure.


SQL Server Assembly on Failover cluster after Framework update We have database that relies on a CLR assembly. The assembly references other assemblies. After a Windows Framework update we need to drop and reinstall the assembly to have it work.

Now the issue is that the database is part of a failover cluster. Yesterday the secondary server was updated and with it came a framework update. The assembly no longer works on the secondary, but we cannot reinstall it because it is a read only.

The primary server hasn't been updated, so no point in reinstalling the assembly.

Does anybody has experience in dealing with these sort of issues? If so is there a recommended way of updating assemblies?

We could do a failover and update the assembly. However in this case the assembly doesn't match the Framework on the other server. I guess we need to do a failover, reinstall the assembly and update the other server to the same Framework?

Thanks!

Are you saying that the one assembly you have references unsupported .NET framework libraries that get imported implicitly when creating your assembly? In which case it would make sense that a Framework update could have this effect. SQLCLR requires that assemblies used in SQL Server that are also in the main OS GAC be the exact same version, and the version in the main GAC is now newer on the secondary due to the Framework update.

As far as I can tell, you will need to apply the same Framework update to the primary, reload the custom assembly, and failover, and then reload the assembly again. Even if the secondary was not read-only and you were able to update the assembly, if that instance failed over to the main node (assuming it still has not received the Framework update), then the SQLCLR object(s) would again fail due to the library version mismatch. And, even right now, if the primary instance fails over to the other node, the SQLCLR object(s) will error due to the library version mismatch.


How to promote hidden mongo node to primary? We are trying to implement DR setup for our mongodb replica set and currently three nodes in DR are configured to run as hidden nodes. We are trying to test failover to DR which will require promoting hidden node to primary and secondary respectively. What is the best way to achieve this?

We are trying to implement DR setup for our mongodb replica set and
  currently three nodes in DR are configured to run as hidden nodes.

As per MongoDB documentation Configure a Hidden Replica Set Member Hidden members are part of a replica set but cannot become primary and are invisible to client applications. Hidden members may vote in elections.

The most common use of hidden nodes is to support delayed members. If you only need to prevent a member from becoming primary, configure a priority 0 member.

Member Configuration Document

To configure a secondary member as hidden, set its members[n].priority value to 0 and set its members[n].hidden value to true in its member configuration:

{
  "_id" : <num>
  "host" : <hostname:port>,
  "priority" : 0,
  "hidden" : true
}

Configuration Procedure

The following example hides the secondary member currently at the index 0 in the members array. To configure a hidden member, use the following sequence of operations in a mongo shell connected to the primary, specifying the member to configure by its array index in the members array:

cfg = rs.conf()
cfg.members[0].priority = 0
cfg.members[0].hidden = true
rs.reconfig(cfg)

After re-configuring the set, this secondary member has a priority of 0 so that it cannot become primary and is hidden. The other members in the set will not advertise the hidden member in the isMaster or db.isMaster() output.

  We are trying to test failover to DR which will require promoting
  hidden node to primary and secondary respectively. What is the best
  way to achieve this?

Note with WARNING:

The rs.reconfig()
shell method can force the current primary to step down, which causes
an election.
When the primary steps down, the mongod
closes all client connections. While this typically takes 10-20
seconds, try to make these changes during scheduled maintenance
periods.
Avoid reconfiguring replica sets that contain members of different
MongoDB versions as validation rules may differ across MongoDB
versions.


Merge replication - Could not remove directory We are facing an issue with our replication server. We are currently setting up a new environment to replace the old replication server and we keep getting the following error.

Server XXX\YYY, Level 16, State 1, Procedure sp_MSreplremoveuncdir, Line 83
Could not remove directory '\\\XXXXXX\VTWSnapshot\unc\XXXX$YYYY\77_11\'.
Check the security context of xp_cmdshell and close other processes that may
be accessing the directory. (Source: MSSQLServer, Error number: 20015)

Sometimes when we run the job to generate a new snapshot, it fails and sometimes it succeeds. Since it sometimes succeeds, I'm sure it's not an issue with access or permissions, else it would always fail.

My second thought was that something is locking some of the files, not allowing SQL to delete anything from the folders.
Sadly I only have permission on SQL and not on the server or file share, making this hard to check. Communication also goes a bit slow, me being in Belgium and the head office of their company being in America, that's why I'd like to get as much information as possible so I could ask everything at the same time.

[This may be coincidence, but I'll mention it anyway]

When we run the snapshot job X+1 times, it succeeds, with X being the number of subscriptions we have.

Meaning:
If we have 3 subscriptions A, B and C

Job fails the first time on folder A
Job fails on folder B
Job fails on folder C
Job succeeds

This kind of made me believe that the folders are somehow locked and the lock gets released after the job has run, but failed. The next time the job runs, it can continue with the next folder until they are all unlocked?

[/end of conspiracy]

Any help, thoughts, ideas are welcome.

The account under which xp_cmdshell is executing must have the proper access to the replication data share.

As a first step I would recommend check if xp_cmdshell is configured or not. If not, you have to enable for your purposes.

  -- To allow advanced options to be changed.
  EXEC sp_configure 'show advanced options', 1;
  GO
  -- To update the currently configured value for advanced options.
  RECONFIGURE;
  GO
  -- To enable the feature.
  EXEC sp_configure 'xp_cmdshell', 1;
  GO
  -- To update the currently configured value for this feature.
  RECONFIGURE;
  GO


send jobs via psql asynchronously I want to backup a huge number of records and delete them, but I don't have complete access to database server so I can't run my script on the server. because it takes so long I need to submit jobs(say REINDEX for example) and then turn off my computer. Is it possible to do something like this?

Not really. There is pgAgent (part of pgAdmin package), that could be abused to do it, but it's not its main purpose.

Generally, to run such long queries/jobs, you need somewhere to have a psql session to live until it will finish. Usually, psql on screen on some kind of server - database server, or app server, doesn't matter much.


How to view the original script that built a materialized view and that was created by another user? In the Oracle database server use, another user (called MIMIC2DEVEL) created a materialized view and made it public. How to view the original script that was used to built it?

Here are the grants on the materialized view:

I am aware of How to view the original script of a materialized view? but the two solutions proposed there do not seem to work for materialized views that was created by another user.

I use Oracle SQL Developer and the database server runs:

Specify the owner in GET_DDL, or use ALL_MVIEWS instead of USER_MVIEWS:

select dbms_metadata.get_ddl('MATERIALIZED_VIEW', 'MVIEW_NAME', 'OWNER') from dual;

select query from all_mviews
  where mview_name = 'your materialized view';