How to query inside jsonb column I have a column type of jsonb named changes

THis is the structure of my column.

[["change","is_enabled",[false,true]]]

I want to query within the column.

I want to find all the rows that "is_enabled" = true

I have tried everything. Can you please help me find a solution

I have a column type of jsonb named changes
[["change","is_enabled",[false,true]]]

This is a terrible schema. You're essentially storing an array of arrays. And what you're asking is how to query a jsonb-array, for any elements that are themselves an array that happen to have an array after a string named "is_enabled" which inside the array, has an element of true.
test=# SELECT jsonb_pretty('[["change","is_enabled",[false,true]]]'::jsonb);
     jsonb_pretty
-----------------------
 [                    +
     [                +
         "change",    +
         "is_enabled",+
         [            +
             false,   +
             true     +
         ]            +
     ]                +
 ]
(1 row)

That's going to be extremely ugly.  Nothing about this makes sense. Your JSON schema should just have one object that has {"is_enabled":true}, or something like this {"name":"change","is_enable":true}. As is, you need something very ugly,
You can unnest both JSON array's getting to the inside with relative ease. Then you'll have to mark the row after the is_enabled element in the array,
SELECT *,
  '"is_enabled"'::jsonb = lag(j2) OVER (PARTITION BY j1 ORDER BY o) AS is_changed
FROM ( VALUES
  ($$[["change","is_enabled",[false,true]]]$$::jsonb)
) AS t(j)
CROSS JOIN LATERAL jsonb_array_elements(j)
  AS jae1(j1)
CROSS JOIN LATERAL jsonb_array_elements(j1) WITH ORDINALITY
  AS jae2(j2,o);
                     j                     |                   j1                    |      j2       | o | is_changed
-------------------------------------------+-----------------------------------------+---------------+---+------------

 [["change", "is_enabled", [false, true]]] | ["change", "is_enabled", [false, true]] | "change"      | 1 |
 [["change", "is_enabled", [false, true]]] | ["change", "is_enabled", [false, true]] | "is_enabled"  | 2 | f
 [["change", "is_enabled", [false, true]]] | ["change", "is_enabled", [false, true]] | [false, true] | 3 | t
(3 rows)

From this, it's simple using bool_or, and another select
SELECT j, bool_or(j2 @> 'true'::jsonb)
FROM (
  SELECT j, j2,
   '"is_enabled"'::jsonb = lag(j2) OVER (PARTITION BY j1 ORDER BY o) AS is_changed
  FROM ( VALUES
    ($$[["change","is_enabled",[false,true]]]$$::jsonb)
  ) AS t(j)
  CROSS JOIN LATERAL jsonb_array_elements(j)
    AS jae1(j1)
  CROSS JOIN LATERAL jsonb_array_elements(j1) WITH ORDINALITY
    AS jae2(j2,o)
) AS t
WHERE is_changed = true
GROUP BY j;

We can add more rows to the VALUES expression to test it
SELECT j, bool_or(j2 @> 'true'::jsonb)
FROM (

  SELECT j, j2,
   '"is_enabled"'::jsonb = lag(j2) OVER (PARTITION BY j1 ORDER BY o) AS is_changed
  FROM ( VALUES
    ($$[["change","is_enabled",[false,false]]]$$::jsonb),
    ($$[["change","is_enabled",[true,false]]]$$::jsonb),
    ($$[["change","is_enabled",[true,true]]]$$::jsonb)
  ) AS t(j)
  CROSS JOIN LATERAL jsonb_array_elements(j)
    AS jae1(j1)
  CROSS JOIN LATERAL jsonb_array_elements(j1) WITH ORDINALITY
    AS jae2(j2,o)

) AS t
WHERE is_changed = true
GROUP BY j;

                     j                      | bool_or
--------------------------------------------+---------
 [["change", "is_enabled", [true, false]]]  | t
 [["change", "is_enabled", [true, true]]]   | t
 [["change", "is_enabled", [false, false]]] | f
(3 rows)

If you just want the ones where bool_or IS TRUE use HAVING..
WHERE is_changed = true
GROUP BY j
HAVING bool_or(j2 @> 'true'::jsonb) IS TRUE;


How to package a PostgreSQL extension as a binary .deb file in Ubuntu? What is the minimal-effort way to package a PostgreSQL extension into a .deb file for Ubuntu (as of 18.04)?

I did some search but find many different suggestions (some outdated).

Suppose I want to package a simple postgres extension such as the first and last aggregate functions.

This StackOverflow answer suggests that one should fist make a directory structure manually as follows:

ProgramName-Version/
ProgramName-Version/DEBIAN
ProgramName-Version/DEBIAN/control
ProgramName-Version/usr/
ProgramName-Version/usr/bin/
ProgramName-Version/usr/bin/your_script

and then dpkg -b /path/to/the/ProgramName-Version after setting the right file permissions.

Others suggest using dh-make, and the Ubuntu Packaging Guid suggests a work flow that uses some toolchain based on bzr.

But I think PostgreSQL has its own build infrastructure for extensions called PGXS (as of PostgreSQL 12.x). I found PGXS quite easy to use, because I don't have to worry the folder structure myself and make install will copy files to the right system directories regardless of the PostgreSQL version.

Is there a way to combine the manual approach above and the PGXS approach to automatically create a PostgreSQL extension as a .deb file?

Is there a way to combine the manual approach above and the PGXS
  approach to automatically create a PostgreSQL extension as a .deb
  file?

Sure. First you need to create a debian/ directory with a control and rules files copied from other extensions. There are many examples in the PGDG Apt repository. Add the following line in your APT sources (bionic is the codename for Ubuntu 18.04) if you don't have it already:

deb-src http://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main

To see the list of packages: apt search 'postgresql-*'

To download the sources: apt source name-of-package

Once your debian directory is good and the dependencies are installed, basically you build the package for the extension with dpkg-buildpackage -rfakeroot -b like any other Debian package.


Get rows that exist exactly once per day for a given period I have a case where I want to extract the device ids (DID) that exist once and only once for each day in a certain period. I have tried different methods and partitions but I seem to only be able to get that data individually per day (where date = X), but I need a query with where date between X and Y.

Example data:

DID date
A   2019-01-01
A   2019-01-01
A   2019-01-02
A   2019-01-03
B   2019-01-01
B   2019-01-02
B   2019-01-03
C   2019-01-01
C   2019-01-02
C   2019-01-02
C   2019-01-03
D   2019-01-01
D   2019-01-02
D   2019-01-03

The query should return only B & D because those exists once for each day from 01 to 03.
I also wish to get the count, which would be 2 in this case

select did from foobar2 where
    date between '2019-01-01' and '2019-01-03'
    group by did
    having count(distinct date)=3 and count(date)=3;

If it has at least one of each date, but more than one of some, then count(date) will be > 3.  If it has less than one for some date, then count(distinct date) will be < 3.

  I also wish to get the count, which would be 2 in this case

Figuring out that B and D have a cardinality of 2 seems pretty easy.  Does that really need to be done inside the query?  If so, use a window function

select did, count(*) over () from foobar2 where
    date between '2019-01-01' and '2019-01-03'
    group by did
    having count(distinct date)=3 and count(date)=3;


Mongoose: Change 2dsphere index version I'm trying to upload my database to the heroku mongodbLab addon. My MongoDB version is 3.2.4 and my servers is 3.0.9. Mongoose is 4.4.6. However, when I run the command $ mongorestore -h myhostname -d mydatabase -u username -p password ~/tmp/mongodump/Loc8r to push the db to heroku I get the error:

building a list of collections to restore from /Users/user/tmp/mongodump/Loc8r dir
reading metadata for database.locations from /Users/user/tmp/mongodump/Loc8r/locations.metadata.json
restoring database.locations from /Users/user/tmp/mongodump/Loc8r/locations.bson
restoring indexes for collection database.locations from metadata
Failed: database.locations: error creating indexes for database.locations: createIndex error: exception: unsupported geo index version { 2dsphereIndexVersion : 2dsphereIndexVersion: 3 }, only support versions: [1,2]

How can I change the 2dsphereIndexVersion in my schema?

Here's my schema file for my db:

var mongoose = require('mongoose');

var openingTimesSchema = new mongoose.Schema({
  days: {type: String, required: true},
  opening: String,
  closing: String,
  closed: {type: Boolean, required: true}
});

var reviewSchema = new mongoose.Schema({
  author: String,
  rating: {type: Number, required: true, min: 0, max: 5},
  reviewText: String,
  createdOn: {type: Date, "default": Date.now}
});

var locationSchema = new mongoose.Schema({
  name: {type:String, required: true},
  address: String,
  rating: {type: Number, "default": 0, min: 0, max: 5},
  facilities: [String],
  coords: {type: [Number], index: '2dsphere', "2dsphereIndexVersion": 2},
  openingTimes: [openingTimesSchema],
  reviews: [reviewSchema]
});

mongoose.model('Location', locationSchema);

As you can see I tried to change it by adding that attribute "2dsphereIndexVersion": 2. However, that didn't work. Anyone know how I can change this?

-EDIT-
I'm using mongoose to create the database with the schema above. I am trying to set the "2dsphereIndexVersion" to 2 because it's required by my servers version of MongoDB.

I ended up changing the .metadata.json file to look like this:

{"options":{},"indexes":[{"v":1,"key":{"_id":1},"name":"_id_","ns":"Loc8r.locations"},{"v":1,"key":{"coords":"2dsphere"},"name":"coords_2dsphere","ns":"Loc8r.locations","background":true,"2dsphereIndexVersion":2}]}


Local JSON file vs MySql database I've built a website using node js and it has two parts. one for users and one for website administrators for editing content on website that is locked by a login page. The user side, loads data from local json file and administrators side, loads data from mysql database.
I want to know is it better to use mysql on both sides for increased performance?
I am new in programming and I'm a bit confused between mysql Connections and Pools. Which one is better for performance if I have a lot of users receiving data?
this is the function for receiving data:
function getAllProductsLocal() {
    try {

        const products_json = JSON.parse(fsExtra.readFileSync("./public/assets/JSON/products.json"));
        return products_json;

    }catch(e) {
        throw e;
    }
}

and this is an example of the data I'm storing in JSON file:
{
    "ID": 19,
    "Cost": 212,
    "Sizes": "[4,7]",
    "Category": "man",
    "Color": "blue"
}

there will be a hundred of them

The question really depends on what kind of querying of the JSON data you're doing, but I suspect if your data grows large enough (or when your use cases become complex enough) you'll find the file processing operations you're doing with fs-extra will be outperformed by the equivalent operations in a database, such as MySQL, especially because you mentioned the JSON file is accessed remotely since it's stored on the server, rather than the client's end.
I think local JSON files are good on the client's end for simple things like user app settings and user preferences, and fine to store on the server for basic things like the navigation menu's structure of the website, for example. But I wouldn't plan to store the main data of the application in a file, rather you'll likely be better off storing it in a database.


Conditionally track CDC on a table in SQL Server 2016 I have CDC working on many tables in SQL Server 2016 without any issues. I now have a requirement to capture data changes (CDC) on a table when a specific criteria is met. Is it possible to capture data using a filter criteria? In other words, track data changes into (_CT) table using a specific "where clause" condition (for example, Where Status = 'Active' and Flag = 1 etc.). How do I go about it? Any help would be appreciated.

Unfortuantely, what you want to do is currently not possible. Documentation here.

However what you can do is on the CDC table put a view that will apply your filter post-capture.
You will loose the extra space, but you will get a view of only the data you want filtered.
Recommendation would be to run your queries on the CDC table with filters in the WHERE clause though rather than bothering with an extra view.

Hope this helps bring ideas.


postgresql 12.3 convert existing DB to use case-insensitive collation? postgresql 12.3 in a container with Alpine Linux 3.10
I would like to convert an existing DB to use case-insensitive collation. Is it possible? I mean, without dropping it and recreating from scratch.
Also, instead of setting this for the whole db, can I add this collation to a field in an existing table, or to a table?

First, a warning: there used to be problems with collations on Alpine Linux, so consider using something else.
The only way to change a a database to use a case insensitive collation throughout is to create such a collation and then use
ALTER TABLE ... ALTER ... TYPE text COLLATE your_coll;

for all string columns in the database.
ICU collations cannot (yet) be used as the default collation for a database.


How to create a view from a join with multiple duplicate column names? I have a few tables with this sort of format

myTable: id | Table1_id | Table2_id | Table3_id | value

Table1: id | text | ...

Table2: id | text | ...

Table3: id | text | ...

I want to create a view that replaces the ID's with the associated text. I can get that using 3 join statements like shown:

SELECT table1.id, table2.id, table3.id, value
FROM myTable

INNER JOIN Table1
  ON Table1_id = Table1.id
INNER JOIN Table2
  ON Table2_id = Table2.id
INNER JOIN Table3
  ON Table3_id = Table3.id;

When I try to create a view using

CREATE VIEW myView AS (

SELECT table1.id, table2.id, table3.id, value
FROM myTable

   INNER JOIN Table1
     ON Table1_id = Table1.id  -- I tried as t1 but that doesn't work
   INNER JOIN Table2
     ON Table2_id = Table2.id
   INNER JOIN Table3
     ON Table3_id = Table3.id
);

I get an ORA-00957: duplicate column name error, which makes sense, they all have the same id column. How do I use an alias or something similar so I can create the view which has a format like this:

myTable: id | t1 | t2 | t3 | value

where tn is the column name that contains tn.text.

I figured it out, the alias goes up with the SELECT statement so the query would be:

CREATE VIEW myView as (

  SELECT table1.id as t1, table2.id as t2, table3.id as t3, value
  FROM myTable

  INNER JOIN Table1
    ON Table1_id = Table1.id
  INNER JOIN Table2
   ON Table2_id = Table2.id
  INNER JOIN Table3
   ON Table3_id = Table3.id

);


Designing a table with multiple column types and different foreign keys I have a table item in which different columns are filled depending on the template type of item that it is e.g.

|item_id|template_id|brand|model|heading|usp_1|usp_2|icon_1_asset_id|icon_2_asset_id|
|-------|-----------|-----|-----|-------|-----|-----|---------------|---------------|
| 1     | 1         | ABC | xyz |       | ddd |     | 1             | 2             |
| 2     | 1         | ABC | xyy |       | eee |     | 3             | 4             |
| 3     | 2         | BCD | ghj | opqrs |     |     | 2             |               |
| 4     | 3         | FGH | stu |       | jjj | kkk |               |               |

In reality, it has about 40 columns.  template_id has a foreign key to the template table, icon_1_asset_id and icon_2_asset_id both have foreign keys to the asset table.  The columns filled depend on the template_id, in this example:

All items have a template_id, brand, model.
template_id = 1 items have usp_1, icon_1_asset_id, icon_2_asset_id
template_id = 2 items have heading, icon_1_asset_id
template_id = 1 items have usp_1, usp_2

As you can see, there is a large amount of wasted space of cells that will never hold data.

I have considered altering the structure to:

Table item

|item_id|template_id|
|-------|-----------|
| 1     | 1         |
| 2     | 1         |
| 3     | 2         |
| 4     | 3         |

Table property_type

|property_type_id| description     |
|----------------|-----------------|
| 1              | brand           |
| 2              | model           |
| 3              | heading         |
| 4              | usp_1           |
| 5              | usp_2           |
| 6              | icon_1_asset_id |
| 7              | icon_2_asset_id |

Table template_property_type

|template_id|property_type_id|
|-----------|----------------|
| 1         | 1              |
| 1         | 2              |
| 1         | 4              |
| 1         | 6              |
| 1         | 7              |
| 2         | 1              |
| 2         | 2              |
| 2         | 3              |
| 2         | 6              |
| 3         | 1              |
| 3         | 2              |
| 3         | 4              |
| 3         | 5              |

Table item_property

|item_property_id|item_id|property_type_id| value |
|----------------|-------|----------------|-------|
| 1              | 1     | 1              | ABC   |
| 2              | 1     | 2              | xyz   |
| 3              | 1     | 4              | ddd   |
| 4              | 1     | 6              | 1     |
| 5              | 1     | 7              | 2     |
| 6              | 2     | 1              | ABC   |
| 7              | 2     | 2              | xyy   |
| 8              | 2     | 4              | eee   |
| 9              | 2     | 6              | 3     |
| 10             | 2     | 7              | 4     |
| 11             | 3     | 1              | BCD   |
| 12             | 3     | 2              | ghj   |
| 13             | 3     | 3              | opqrs |
| 14             | 3     | 6              | 2     |
| 15             | 4     | 1              | FGH   |
| 16             | 4     | 2              | stu   |
| 17             | 4     | 4              | jjj   |
| 18             | 4     | 5              | kkk   |

Although this gives more efficient storage and an amount of database-level control over which properties can be filled, you loose the foreign key constraint on the property value due to the fact that some properties are linking to another table and some are plain text.

Is there a way to put some database-level constraint on the value column - possibly by breaking it out onto another table, or re-working the structure?

Or is there a completely different way to approach this type of data?

I'm using SQL Server, although the fundamental problem is not tied to any specific relational database management system.

You can enforce the column nullability based on the template_id by using CHECK CONSTRAINTS, for example:

ALTER TABLE [item] ADD CONSTRAINT CK_Template_Columns
CHECK (
    (template_id = 1 AND usp_1 IS NOT NULL AND icon_1_asset_id IS NOT NULL AND icon_2_asset_id IS NOT NULL AND heading IS NULL AND usp_2 IS NULL)
    OR (template_id = 2 AND heading IS NOT NULL AND icon_1_asset_id IS NOT NULL AND usp_1 IS NULL AND usp_2 IS NULL AND icon_2_asset_id IS NULL)
    OR (template_id = 3 AND usp_1 IS NOT NULL AND usp_2 IS NOT NULL AND heading IS NULL AND icon_asset_1_id IS NULL AND icon_2_asset_id IS NULL)
)

This CHECK CONSTRAINT enforces the columns that cannot be NULL and those that must be NULL based on the chosen template_id. An attempt to fill an invalid column, or leave a valid column blank, for a given template_id would cause the INSERT/UPDATE to fail.

And if you're concerned regarding storage, within SQL Server you have the option of defining SPARSE columns to help reduce storage space for mostly NULL columns. You can also leverage Data Compression and Filtered Indexes to reduce overall space consumption.

For example, a filtered index for each template_id value would allow you to only index the columns that are not null for that template, thereby reducing the number of rows and columns stored in an index.


When is an application using too many sessions? I'm working on a client-server application that uses OCI to communicate with Oracle. In the past our application often treated sessions as an infinite resource, quite possibly to the detriment of other applications that are sharing that Oracle server.

I'm trying to convince my fellow developers that it is important for our application to play nice with others and not be such a resource hog.

At what point would you Oracle DBAs consider an application to be using (or abusing) sessions excessively?

EDIT: Currently one of the biggest consumers of sessions is this one component that uses direct path loading to bulk load several (20-30) tables in parallel.

As with all things, it depends.

If you're using OCI, I assume you're developing a client/ server application.  I would generally want the database in that situation to support shared server connections and I would want to request a shared server connection when the application connected to the database.  That improves the performance of the application by limiting the amount of work the database has to do in order to create a new session.  That has the side effect of leaving more resources available on the server for others.  Using shared server connections does involve a longer code path when the database executing subsequent queries since the query has to be sent to a shared server process but that generally isn't a big deal if the number of shared server processes is reasonable.

Opening a number of sessions could also be a problem if the DBA is not using automatic PGA management.  If you're using manual PGA management, PGA is configured on a per-session basis so each session can allocate a separate SORT_AREA_SIZE for sorts among other PGA components.  If you created a large number of sessions in a database using manual PGA management and each session tried to maximize its PGA usage, you could easily starve the server of RAM and cause performance problems for everyone.  Assuming you are using Oracle 10.1 or later, however, automatic PGA management is available.  In that case, the DBA configures a PGA_AGGREGATE_TARGET (or includes the PGA in the MEMORY_TARGET in 11g) and the database takes care of ensuring that the aggregate PGA across all sessions is limited so that the database runs out of resources.

If the database supports shared server connections, your application gets a shared server connection, and the database uses automatic PGA management, most DBAs won't care too much how many sessions you create.

Now, if you are creating many sessions so that you can do more work in parallel, that would create performance problems over and above the number of sessions.  It's easy enough to configure the database to support 1000 sessions, for example, it's much harder to configure the database to not die in a pile if all 1000 session simultaneously issue a meaty query against a data warehouse.  If your application is using up all the resources available to the database for your queries, the DBA would probably want to consider using Oracle Resource Manager to prioritize different applications and/or different users.  For example, the DBA could configure Resource Manager so that if the CPU utilization goes to 100%, your application in the aggregate gets 50% of the CPU, some other application in the aggregate gets 25%, and all others get the remaining 25%.  If no other requests were pending, your application would be free to use all 100% of the CPU.

If you are running things in parallel, it might also be useful to investigate Oracle's ability to run statements in parallel because that might involve less overhead than writing your own parallelization code.  I would expect, for example, that it would be far easier and quite possibly faster to write a client application that serially submitted statements that used Oracle parallel query to execute rather than opening multiple sessions and executing each statement from a separate thread of your application while Oracle was not using parallel query to execute any of the statements.  If you use parallel query, the database can also adjust the number of parallel slaves that are spawned so that when the database is particularly busy fewer parallel slaves are started and when the database is relatively idle more parallel slaves are started.  That would potentially satisfy both the desire to make the application as efficient as possible as well as to share the resources of the server more equitably with other applications.


How to force a merge join without a hint Some time ago I used query hints but then realized that most of the time SQL Server is smarter than I. And it's better to build an additional index/reorganize data or the query and get a much better result than forcing the server to use a plan that is inefficient in general but speedy enough for some subset of data. But now I am in a situation where I don't know how to organize data better.

I have two tables. First table, T1, is (Id, CustomerId) and second, T2, has the same columns. I want to join T1 to T2 on CustomerId. And get top N rows. In this case the optimizer sees that I want only N top and says, "Hey, I will use loops and find N matches very quick, especially when I use an index seek." But it's not working, because there is no data satisfying the conditions. Thus it uses a loop join to join a 25m table and a 100k table which is pretty slow.

When I force SQL Server to use a merge join, I get the following plan, which is executed in one second:

I don't want to force it for two reasons:

First, SQL Server is smart enough, as I said earlier.
Second, I'm using an ORM and it's quite hard to inject hints into
the generated query, so I'd like to avoid it.

What should I do in this situation?

To switch from a nested loop join to a merge join without a hint you need the plan with the merge join to have a lower estimated cost than the plan with the nested loop. Technically, you also need the query optimizer to find the lower cost plan during plan exploration but there isn't a lot that you can do about that.

First, a note about merge join costing. In my experience, SQL Server is pretty pessimistic about merge join costing. This seems understandable when you consider how much the IO requirements for a merge join can change when small amounts of data are changed. Consider a simple example in which one table has the integers from 1 - 10000 and the other has the integers from 100001 to 1100000:

DROP TABLE IF EXISTS X_SMALL_TABLE;

CREATE TABLE X_SMALL_TABLE (ID INT NOT NULL PRIMARY KEY (ID));

INSERT INTO X_SMALL_TABLE WITH (TABLOCK) (ID)
SELECT N
FROM dbo.GetNums(10000);

UPDATE STATISTICS X_SMALL_TABLE WITH FULLSCAN;

DROP TABLE IF EXISTS X_LARGE_TABLE;

CREATE TABLE X_LARGE_TABLE (ID INT NOT NULL PRIMARY KEY (ID));

INSERT INTO X_LARGE_TABLE WITH (TABLOCK) (ID)
SELECT N + 100000
FROM dbo.GetNums(1000000);

UPDATE STATISTICS X_LARGE_TABLE WITH FULLSCAN;

If I join the tables together I will get 0 rows returned. If I force a MERGE JOIN then SQL Server will scan all of the rows from the small table and only one of the rows from the large table. By default, SQL Server will traverse through the tables in ascending order according to the join key. However, the query optimizer estimates that all 1000000 rows will be scanned from the large table and subsequently assigns a relatively large cost to that operator:

As we can see from the actual plan, only one row was scanned. What happens if we insert just one new row into the small table?

INSERT INTO X_SMALL_TABLE
SELECT 1200000;

UPDATE STATISTICS X_SMALL_TABLE WITH FULLSCAN;

Since the merge join algorithm goes in ID order once we hit ID 10000 in the small table we should loop through all of the rows in the large table until we hit ID 1200000.

The actual plan confirms that all rows were read from both tables. So small changes in data can lead to large differences in merge join performance. Based on that it seems reasonable for SQL Server to be pessimistic about merge join costing, which will work against you if you are trying to get a merge join to appear in your query. In your second query you scan all of the rows from CustomerDiscountCards. That operation alone could have a higher estimated cost than the entire nested loop join plan.

When using hints to force a MERGE JOIN note that the MERGE JOIN join hint also adds a FORCE ORDER hint to the query. Based on the information in the question, your second query doesn't run quickly strictly because of the MERGE JOIN. It runs quickly because of the new join order. The optimizer joins together CustomerDiscountCards and DiscountCards using full scans on both tables and finds 0 matching rows. Any inner join against a 0 row result set is going to be fast because query execution can stop at that point (disregarding blocking operators which occur before that). So instead of focusing on forcing a merge join, which will probably be difficult without hints, I would focus on getting the join order correct.

A simple way to force join order is to materialize the first result set that you want into a temporary table. You could join CustomerDiscountCards and DiscountCards in an initial query and put the matching relevant rows into a temp table. This should run in under a second for your test case because you won't actually be inserting any rows into the temp table. If your second query joins to the empty table it should finish almost instantly. You will need to test the performance of the query for other parameter values in which you actually get rows back because you could be putting a fair number of rows and columns into tempdb.

You may be able to rewrite your SQL to effectively force the join order without using a temp table. Of course using the FORCE ORDER hint is the easiest way to do that but you said that you don't want to use hints and that hint can be a difficult one to use. It applies to the entire query so if your data ever changes you could end up with poor performance. For an idea on how to force join order without the hint, it looks like pairing together the tables that you want to be joined first in a derived table and adding a superfluous TOP operator seems to push SQL Server in the right direction:

INNER JOIN
(
    SELECT TOP 9223372036854775807 ...
    FROM
    CustomerDiscountCards
    INNER JOIN DiscountCards ON ...
) t ON ...

However, I cannot conclude if SQL Server is actually forced to evaluate that join separately from the rest of the query. As before you will need to test.

To give you a final option to consider, since the initial problem appears to relate to row goals you could hide the row goal from the optimizer. One way to accomplish that is to use the OPTIMIZE FOR query hint and to use a variable for your TOP statement. Consider the query snippet below:

DECLARE @TOP BIGINT = 10;

SELECT TOP (@top)
...
OPTION (OPTIMIZE FOR (@TOP = 9223372036854775807))

That query will only return the first 10 rows that it finds in the result set. However, the plan will be created as if it returns the first 9223372036854775807 rows. This may disfavor the nested loop join plan that you experienced the issues with, but it's hard to say how well this will perform in general.

Ultimately, it's hard to say more with the information that we have in the question. Presumably your query can return rows in some cases (there isn't much reason to continually run a query that always returnns no rows) so you'll need to test your options for all of the different cases.


Zombie Sql Server DB on Secondary Replica is both in and out of an Availability Group I have a database in SQL Server 2012 that is on the secondary replica of an availability group.  I removed the database from the AG db list on the primary and the db is currently in the Restoring state on the secondary.

The problem is that I'm unable to drop the database off the secondary.

DROP DATABASE [ZOMBIEDB]

Gives me the following error:

  "The database 'ZOMBIEDB' is currently joined to an availability group.
  Before you can drop the database, you need to remove it from the
  availatility group."

However:

ALTER DATABASE [ZOMBIEDB] SET HADR OFF;

Yields the following error:

  "Cannot complete this ALTER DATABASE  SET HADR
  operation on database 'ZOMBIEDB'. The database is not joined to an
  availability group. After the database has joined the availability
  group, retry the command."

So, how do I convince SQL Server that the db isn't in an AG so I can drop it?

I pulled the oldest full backup I could find and was able to restore that over the Restoring db using:

RESTORE DATABASE [ZOMBIEDB] FROM  DISK = N'D:\ZOMBIEDB.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5

I was then able to delete the restored database.


Why do I need this attribute? This is my relational schema;

Book(title, shelfmark, ISBN, author, year)
Genre(name)
GenreBook(name, ISBN)
User(firstName, lastName, cardID)
UserBooks(cardID, ISBN, position)

Why do I need the position attribute in the last relation? It is a N-M relation but (ISBN, cardID) should be enough to identify a tuple. Maybe my user can have two books with the same ISBN?

I would guess that position is not really part of a relation, but rather an ordering key used to denote what book is the user's first, second, third and so on.

Or, like you say, it could be that a user can have multiple copies of a book, but that still wouldn't make position part of the relation, only of the primary key of UserBooks.

Or, a combination of the above two (ordering key and user can have multiple books).


Can I use MySQL-SERVER tunning variables in MYSQL CLUSTER? When tunning MySQL-SERVER for best performance, the edition is done down my.cnf file.

Such as:

max_connections = 500
wait_timeout = 600
sort_buffer_size = 2M

Can I improve MySQL CLUSTER performance with those editable variables down [mysqld] tag of config.ini of mgmt node or my.cnf of ndbd node?

The main reason of this question is that I'm getting ERROR 1205: Lock wait timeout exceeded; try restarting transaction when importing a 1.2 G .sql file. I can't find any solution to this refering to MySQL CLUSTER so I want to try with the wait_timeoutvariable, but I don know if it's compatible with.

Will it accept those variables?

Yes, all this tuning variables are described in MySQL Documentation


mysql greatest n per group: latest message content and time for each conversation I have two tables, one for topics, and one for messages.
In terms of relationship, messages are posted within a topic, so each topic has many messages.

I am trying to make a query that would:
- list all the topics
- get the count of messages for each topic
- get the latest message posted for each topic (time and content)

I basically tried to do a query similar to:

SELECT
  topic.*,
  COUNT(msg.id),
  MAX(msg.time),
  msg.content

FROM topics

LEFT JOIN (
  SELECT
    msg.id,
    msg.time,
    msg.content,
    msg.topic_id
  FROM messages AS msg
  ORDER BY time DESC
) AS msg
ON msg.topic_id=topic.id

GROUP BY topic.id

However, this results in an error

  Expression #... (closed) of SELECT list is not in GROUP BY clause and contains nonaggregated column 'msg.time' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I understand that I could just toggle the only_full_group_by setting, but my understanding is that I then have no guarantee that the msg.content will return me the latest message as expected.

I found many greatest n per group questions online, but most seem to only deal with a single table with the same table being queried in the query and subquery.

Is there any reliable way to achieve this with MySQL?

SELECT t.*,
       m4.count,
       m4.time,
       m4.content
FROM topics t
LEFT JOIN ( SELECT m1.topic_id,
                   m3.count,
                   m3.time,
                   m1.content
            FROM messages m1
            INNER JOIN ( SELECT m2.topic_id,
                                COUNT(m2.topic_id) count,
                                MAX(m2.time) time
                         FROM messages m2
                         GROUP BY m2.topic_id ) m3 ON m1.topic_id = m3.topic_id
                                                  AND m1.time = m3.time ) m4 ON t.id = m4.topic_id

I assume that messages (topic_id, time) is unique.


Adding parameters to ORACLE sql case statement I have the following sql query

SELECT DISTINCT SAMPLE.SAMPLE_NUMBER, LOT.PRODUCT, SAMPLE.LOGIN_DATE, SAMPLE.STATUS as SAMP_STATUS,
CASE
    WHEN SAMPLE.TEXT_ID LIKE '%TOP%'
    THEN 'TOP'
    WHEN SAMPLE.TEXT_ID LIKE '%MID%'
    THEN 'MIDDLE'
    WHEN SAMPLE.TEXT_ID LIKE '%BOTT%'
    THEN 'BOTTOM'
    WHEN SAMPLE.TEXT_ID LIKE '%RETAIN%'
    THEN 'RETAIN' ELSE 'ROUTINE'
END as LOCATION,
CASE
    WHEN WORK_CENTER = 'LABWARE'
    THEN C_PRODN_RESOURCE ELSE WORK_CENTER
END as PROD_RESOURCE, RESULT.ANALYSIS, RESULT.NAME, RESULT.FORMATTED_ENTRY, RESULT.IN_SPEC
CASE
    WHEN SAMPLE.STATUS <> 'X'
    THEN SAMPLE.STATUS
    WHEN SAMPLE.STATUS <> 'A'
    THEN SAMPLE_STATUS
END
FROM LOT JOIN SAMPLE ON (LOT.LOT_NUMBER = SAMPLE.LOT)
JOIN RESULT ON (SAMPLE.SAMPLE_NUMBER = RESULT.SAMPLE_NUMBER)
LEFT JOIN PRODUCT_SPEC ON ((LOT.PRODUCT = PRODUCT_SPEC.PRODUCT)
    AND (RESULT.NAME = PRODUCT_SPEC.COMPONENT) AND (SAMPLE.PRODUCT_VERSION = PRODUCT_SPEC.VERSION)
    AND (LOT.SAMPLING_POINT = PRODUCT_SPEC.SAMPLING_POINT) AND (LOT.PRODUCT_GRADE = PRODUCT_SPEC.GRADE)
    AND (SAMPLE.STAGE = PRODUCT_SPEC.STAGE) AND (SAMPLE.SPEC_TYPE = PRODUCT_SPEC.SPEC_TYPE)
    AND (RESULT.ANALYSIS = PRODUCT_SPEC.ANALYSIS)) WHERE LOT.LOT_NUMBER = '40000165304' AND RESULT.REPORTABLE = 'T'
    AND SAMPLE.SPEC_TYPE <> 'RETAIN' AND PRODUCT_SPEC.T_SHOW_ON_CERTIFICATE = 'T' Order by ANALYSIS, LOCATION

I NEED TO ADD CONDITIONS FOR THE sample.status i.e.,
Sample.status <> ‘x’ or Sample.status <> ‘R’

I’m not sure where to add these conditions to?

This still isn't working

    SELECT DISTINCT SAMPLE.SAMPLE_NUMBER, LOT.PRODUCT, SAMPLE.LOGIN_DATE,   SAMPLE.STATUS as SAMP_STATUS,
CASE
    WHEN SAMPLE.TEXT_ID LIKE '%TOP%'
    THEN 'TOP'
    WHEN SAMPLE.TEXT_ID LIKE '%MID%'
    THEN 'MIDDLE'
    WHEN SAMPLE.TEXT_ID LIKE '%BOTT%'
    THEN 'BOTTOM'
    WHEN SAMPLE.TEXT_ID LIKE '%RETAIN%'
    THEN 'RETAIN' ELSE 'ROUTINE'
END as LOCATION,
CASE
    WHEN WORK_CENTER = 'LABWARE'
    THEN C_PRODN_RESOURCE ELSE WORK_CENTER
END as PROD_RESOURCE,
RESULT.ANALYSIS, RESULT.NAME, RESULT.FORMATTED_ENTRY,   RESULT.IN_SPEC
CASE
    WHEN SAMPLE.STATUS NOT IN ('X', 'A')  THEN SAMPLE_STATUS
END
FROM LOT JOIN SAMPLE ON (LOT.LOT_NUMBER = SAMPLE.LOT)
JOIN RESULT ON (SAMPLE.SAMPLE_NUMBER = RESULT.SAMPLE_NUMBER)
LEFT JOIN PRODUCT_SPEC ON ((LOT.PRODUCT = PRODUCT_SPEC.PRODUCT)
    AND (RESULT.NAME = PRODUCT_SPEC.COMPONENT) AND (SAMPLE.PRODUCT_VERSION =   PRODUCT_SPEC.VERSION)
    AND (LOT.SAMPLING_POINT = PRODUCT_SPEC.SAMPLING_POINT) AND   (LOT.PRODUCT_GRADE = PRODUCT_SPEC.GRADE)
    AND (SAMPLE.STAGE = PRODUCT_SPEC.STAGE) AND (SAMPLE.SPEC_TYPE =   PRODUCT_SPEC.SPEC_TYPE)
    AND (RESULT.ANALYSIS = PRODUCT_SPEC.ANALYSIS))
WHERE LOT.LOT_NUMBER = '40000165304'
  AND RESULT.REPORTABLE = 'T'
  AND SAMPLE.SPEC_TYPE <> 'RETAIN'
  AND PRODUCT_SPEC.T_SHOW_ON_CERTIFICATE =   'T'
Order by ANALYSIS, LOCATION

When I run the initial query without the Sample.Status NOT IN ('X','A') I get:

What I trying to get is the same view but without a Sample.Status being 'X' or 'A'.

I'm not sure where in the initial code to put the condition to exclude those sample.status lines.


Do I really Need to Upgrade to SQL Server 2014 SP2 ? - We have a Application upgrade to next version.

SQL Server 2014 is currently on SP1.

The upgrade pre-requisite is not necessarily SQL Server SP2.

But I feel and read the below link which says SQL Server 2014 has many fixes related to AOAG and some fix to SQL File stream.

https://support.microsoft.com/en-in/help/3171021/sql-server-2014-service-pack-2-release-information.

I am stuck up in below situation:

My Manager asked do we really need to upgrade to SP2 as it is not necessary for upgrade .

He is little worried as this might be involved in little risk as they are PRODUCTION servers.

But I am sure Microsoft released the SP2 for some intention as this help the SQL Server 2014 performance.

I want to convey the same and make them accept the feasibility to apply the SP2 for our PROD servers ?

So do I really need to apply SP2 and how can I make them understand it helps ?

Your manager will be concerned about possible regressions and new bugs in the service pack (rare, but it has happened in the past) or one of your applications relying on an undefined behaviour that is changed by SP2. These are valid concerns and it is not recommended that you upgrade in production without first at least reading all the official change documentation to make sure none of your applications are likely to be affected. Fully testing your application(s) against the upgrade in another environment is strongly recommended too.

If you have third party applications you might not have the product knowledge to perform the above checks/test, in which case you will need to discus the matter with the application vendor. Hopefully they can give you definitive word on whether they support their application(s) when used with SQL Server 2014sp2.

Do note that without SP2 SQL 2014 becomes officially unsupported in October according to the official lifecycle (https://support.microsoft.com/en-gb/lifecycle/search?alpha=sql%202014) so you are going to want to install SP2 (or upgrade further) before then, meaning you can't avoid this effort completely.

The chance of significant problems between service packs is much lower than between full releases so many people fly by the seat of their pants and just upgrade to the latest service pack without testing. This attitude is somewhat too risky IMO.


postgres+JDBC: fire trigger with non autocommit transaction i have a jdbc client that make an two INSERT query on database whitout autocommit, and a trigger on database that fires when a insertion is done on a certain table

public foo() throws MyException {
    String INSERT1 = "INSERT INTO table1 VALUES(?,?)";
    String INSERT2 = "INSERT INTO table2 VALUES(?)";
    try {
        Connection conn = ConnectionManager.getConnection();
        PreparedStatement pstm1 = conn.prepareStatement(INSERT1);
        PreparedStatement pstm2 = conn.prepareStatement(INSERT2);
        conn.setAutoCommit(false);
        conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

        pstm1.setString(1, "string1");
        pstm1.setString(2, "string2");
        pstm1.executeUpdate();

        pstm2.setString(1, "string3");
        pstm2.executeUpdate();

        conn.commit();
    }catch (ClassNotFoundException ex) {
        throw new MyException("foo error: " + ex.getMessage());
    } catch (SQLException ex) {
        if (conn != null) {
            try {
                conn.rollback();
            } catch (SQLException ex1) {
                throw new MyException("foo error: " + ex.getMessage());
            }
        }
    }catch (Exception e) {
        System.out.println(e.getMessage());
    } finally {
        try {
            if (conn != null) {
                conn.setAutoCommit(true);
                conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
            }
            new CloseConnection(pstm1, pstm2, null, conn).closeAll();
        } catch (SQLException ex) {
            throw new MyException("foo error: " + ex.getMessage());
        }

and the trigger looks like

CREATE FUNCTION my_procedure() RETURNS TRIGGER AS $my_trigger$
DECLARE new_field VARCHAR(30);
BEGIN
    new_field = NEW.field;
    --field is a column of table 2
    --retrieve some data from table1 and table2 then execute some insert on
    --another table
COMMIT;
END;
$my_trigger$ LANGUAGE plpgsql;

CREATE TRIGGER my_trigger
AFTER INSERT ON table2
EXECUTE PROCEDURE my_procedure();

now, everytime I execute the jdbc code, the insertion always fail. With some debug with println() I see that my code fail on

pstm2.executeUpdate();

but no exception was caught. I think it's a trigger problem that try to start execution when my jdbc transaction is not yet committed, in fact, the error on postgresql is

  record new is not assigned yet

There's a way to fix it (start trigger when my trasaction is committed)?

Your trigger is fired for each statement not for each row. In statement level triggers you can not access the new and old records.

You need to change your create trigger to create a row-level trigger, rather than a statement level trigger:

CREATE TRIGGER my_trigger
  AFTER INSERT ON table2
  FOR EACH ROW -- this is the change
EXECUTE PROCEDURE my_procedure();

Triggers are always executed in the same transaction as the triggering statement.


unnest all columns from a given table I have a table, which was loaded dynamically from an external job. This table has a changing number of coulmns from time to time.
I want to unnest all the columns with the coulmn name as 'Key' and the value as 'Value':

SELECT
   unnest(array[(select array_agg( column_name::text )from information_schema.columns where table_name = 'tableName')]) AS "Keys",
   unnest(array[tableName.col1, tablename.col2, ...]) AS "Values"
FROM tableName ;

The problem that I'm facing is that I need something to replace the second unnest Statement, because I don't know the names of the columns.

I thought I just use unnest(array[tableName.*]) as "Values"but that does not work.

Any ideas welcome.

Example:

create table mytest ( col_1 varchar(30), col_2 varchar(30));
insert into mytest values ('a1','b1');
insert into mytest values ('a2','b2');
insert into mytest values ('a3','b3');
SELECT
   unnest(array[(select array_agg( column_name::text )from information_schema.columns where table_name = 'mytest')]) AS "Key",
   unnest(array[mytest.col_1, mytest.col_2]) AS "Value"
FROM mytest ;

Results in:

"Key"   "Value"
col_1   a1
col_2   b1
col_1   a2
col_2   b2
col_1   a3
col_2   b3

which is fine.
But when I get a table of unknown numbers of columns I need something to put into the second unnest statement (unnest(array[tableName.col1, tablename.col2, ...])) so I thought somethng like tablename.* instead of tableName.col1, tablename.col2.

You can use JSON functions to do that for you:

select x.*
from mytest, jsonb_each_text(to_jsonb(mytest)) as x("key",value);

With your sample data, this returns:

key   | value
------+------
col_1 | a1
col_2 | b1
col_1 | a2
col_2 | b2
col_1 | a3
col_2 | b3

to_jsonb(mytest) converts the whole row into a JSON value using the column names as keys and jsonb_each_text() is the unnesting of that JSON value.

Online example: https://rextester.com/DSNOIU44927


How to get month name from week number and year I have a week number and a year (e.g. 34 and 2015). I want to get the name of the month to which the specified week belongs.

How can I do that?

Please try this:

SELECT MONTHNAME(STR_TO_DATE('201534  Monday','%X%V %W')) month_name

Instead of hardcoding the values, you can pass dynamic value also.


ERD modeling organization and roles I am trying to model the following scenario:

  -I have multiple company bureaus.

  -Each Bureau has multiple offices

  -Each office has multiple divisions

  -Each Bureau has employees that only work at the bureau level (think CEO's,CIO's,etc) and employees that work at the many office level

  -Each office has people who only work at the office level (maybe managers, chief,s etc) and people who work at the many divisions levels

  - Each division has people who work only at the division level (division chiefs, for example) and employees who work on projects

  -  Also, each division has projects that are run by division staff (employees, not the high level divisions chiefs)

I want to be able to do the following with the data:

query the structure of the organization (what divisions are in what office, for example)
query the various C level people from each division, office, bureau, etc.
find projects by all levels ( if given a bureau, office, or division get projects associated)
find people who are working on a given project.  These projects will have multiple roles

What I currently have is this:

But I can't get who works at the bureau or office level if they aren't tied to a division.  Any help would be great.

thanks

-Each Bureau has employees that only work at the bureau level (think CEO's,CIO's,etc) and employees that work at the many office level
-Each office has people who only work at the office level (maybe managers, chief,s etc) and people who work at the many divisions levels
-Each division has people who work only at the division level (division chiefs, for example) and employees who work on projects
-Also, each division has projects that are run by division staff (employees, not the high level divisions chiefs)

These rules imply that each employee has a role that requires they work either only for a bureau, an office, or a division, or requires they work for a given bureau and many offices, or an office and many divisions, or a division, and that only employees who work for a division but are not division staff run projects.  The schema shown however asserts only that employees work for a division in any given role and that any of them can run a project.  Changes are needed to bring the schema into line with rules.
The simplest way to address the business rules is to add tables to associate employees to offices and bureaus in addition to divisions.  Each employee would then be associated to each organization level in which they work.  Employees working only at a bureau, or office, or division would only be associated to that level.  Employees working at a bureau who also work at many offices would be associated to the bureau in that associative table and then also associated to the many offices by that associative table.  Note this approach only addresses where each employee actually works and not the rules as to which roles can work at which organizational levels.  To address the rules more discovery and modeling is required and is beyond the scope of this answer.
Secondly, the business rules imply that an employee has a single role - say a CIO or office manager or division chief - and not a role per associated location.  The schema can be brought into agreement with this rule, if correct, by making the role table a parent of the employee table such that each employee is assigned a single role.  Now this implication may or may not be true.  Perhaps an employee can have only a single role at a moment in time, but many over time.  In this case temporal columns are also required.  Perhaps an employee really can have many roles at the same time at different organization levels, in which case the role would not be determined by the employee.  Ultimately, more clarity is needed on the full scope of the business rules by having the subject matter expert review the model and determine if it represents accurately the rules by which they operate.  It is vital to fully understand and document the functional dependencies and natural keys in order to arrive at the correct logical design.  Right now the diagram does not show any natural keys so even if the functional dependencies were presented it would not be possible to determine if the logical design is in agreement with them.  Finally, the diagram shows that each parent entity is optional and I doubt that is really the case.  A business SME can determine under what circumstances the parent occurrence is optional, and for each of those circumstances more work is needed to determine why the parent occurrence is optional and resolve it so it is no longer optional.
I hope these points help in improving the design to meet all the informational objectives.


Slave_IO_Running: Connecting in Master-Slave Replication I was trying to implement Master-Slave Database Replication on two systems.
I followed the steps from the following links
https://www.youtube.com/watch?v=DmQWcU2INqQ
and
Master Slave Replication

But in both cases, I ended up with

  Slave_IO_Running: Connecting
  Slave_SQL_Running: Yes

I've tried to check the network connection between the machines and they're able to ping each other successfully.
Disabled the antiviruses and Firewalls on both systems
Added an alias for the slave to access the master machine, Working.
Both machines using 3306 port for mysql.

  Last_IO_error: error connecting to master 'user01@192.168.2.46:3306' -- retry-time 60 retries: 1

I faced a similar error some days ago. I was able to solve that error based on some Google search results. Here are some of the suggestions that might hopefully help you too:

Check whether you created the replication user with required privileges. If not, create a new user on master with the following command:

Create user ‘repl’@’192.168.2.46’ identified by ‘replpwd’;
Grant replication slave on *.* to ‘repl’@’192.168.2.46’;

If you already created the required replication user, check the privileges of the replication user. You can check the privileges by using the following command:

show grants for repl;

Usually, this issue occurs because of the incorrect password. In that case you can reset the password on the slave by using the following command:

change master to master_password = 'rplpassword';

Also, check the for any mistakes in the given replication user and host IP address.

Since, you are able to ping the master server, it is most probably because of the above mentioned reason.


Backup SQL DataBase to C$ Another simple SQL backup question from me....

I've got a SQL Server database running on SSMS 2008 and am trying to configure some backup jobs. I am now seemingly fine with backing up and restoring the databases but have been asked to backup from one of our servers to another's C$, though am having trouble doing so. I've got the line:

backup database MyDB to disk = '\\NetworkLocation\C$\Backups\backup.bak'

And am being given the error:

Cannot open backup device '\\NetworkLocation\C$\Backups\backup.bak'. Operating system error 5(failed to retrieve text for this error. Reason: 15105).

Which (if I'm correct) is an access denied sort of error.

Anyways.... I was told to 'just set permissions to C$' but haven't been able to find out how to, or even if that's possible. I have got it working such that the server will back up to a shared folder on the second machine if one's set up but the boss has asked, specifically, if it can be done straight onto C$. Am I just going about it the wrong way?
Many thanks

Technically you can do it directly to \\NetworkLocation\C$ like your boss wants, but the c$ share is one of the windows Adminstrative Shares ( http://en.wikipedia.org/wiki/Administrative_share ) and is only available to users in the Admistrators group. So to use it in this case you'd need to add the user account running your SQL Agent to the Administrators group on NetworkLocation

Using these shares like this is a really bad idea. It would be much better for example to share c:\Backups\ as \\NetworkLocation\Backups$  (the $ here indicates a hidden share, so it doesn't show up in the list when a user looks at the machine). As it's not an administrative share you can set the permissions however you want for any account, without giving them Administrator on NetworkLocation

The files are in exactly the same place and you've not compromised your security so all is good.


Will a clustered index improve performance of updates performed sequentially on that clustered index? I know indexes reduce data modification performance, but I have a task where (almost) all updates are done on items in a sequential order. Would a clustered index improve or reduce performance in updating these rows?

The clustered index would be on column id where id is an IDENTITY column. id is thus never changed and will be sequential (in addition rows should never be deleted).
This is the format of my update statements:

UPDATE [table] SET value = 1
WHERE  id IN (1,2,3,4...)

The values are not necessarily continous however. A sequence of IN (1,2,4,5) is also possible if id=3 is not set to be updated, but they will always be in order.

When all updates are in sequential order, will a clustered index improve or reduce performance?

The IN like this is a set of discrete values. SQL Server won't bother working out it is a range or checking ascending values.

That is, x IN (1,2,4,5) is parsed out to x=1 OR x=2 OR x=4 OR x=5. OR is non-SARGable in most cases and result in scans rather than seeks.

If you put the data into a temp table with an index and did x IN (SELECT foo FROM #bar) then it becomes a semi-join and will more likely use an index


How to accomplish SQL Server 2012+2014 high availability Maybe I'm searching for the wrong thing, but I can't see how I can do this.

I have two servers:

Server A (has SQL Server 2012 installed)
Server B (has SQL Server 2014 installed)

Server A provides an API via IIS. The code both inserts and gets data from the database depending on the API call.

Server B does exactly the same for its local database.

I would like to have it so that the two databases are consistent copies of each other. I do believe this is what HA accomplishes.

So a load balancer, for example, might direct API calls to A sometimes, and B sometimes, but the data needs to be consistent either way.

I found some documentation on HA for SQL Server 2012, but not on how to do this for a mixed environment of 2012 and 2014.

Any help or pointers to documentation would be great! Thanks.

how to do this for a mixed environment of 2012 and 2014.

Doing HA in a mixed environment is a recipe for disaster because during disaster once you failover from lower (2012) to higher (2014) version, you cannot failback.

Since you are using Enterprise edition of SQL Server, you can leverage AlwaysON technology wherein you can configure writes occurring on Primary and reads on the secondary configured as readable secondary.

Thinking a bit more on what you mean by mixed, I presume that transactional replication would be much better from sql server 2014 as publisher and 2012 acting as subscriber.

The performance would be dependent on how big is the database, if you are replicating only the tables that you need or the entire database, the latency between the two servers as well as the amount of transactions that are occurring on your publisher.

Note that if you go with T-Rep, doing schema changes will require a new snapshot. Also there will be a distribution database created as a part of setting up T-Rep.


How to create a SQL trigger to execute a stored procedure when a table is updated i need some help with creating a trigger to run a qf.stored_procedure when a qf.table is created. AT the moment, i have a job t run the stored procedure every 15 minutes but changing that is not an option.

The idea is for the trigger to run the stored procedure whenever the table is updated regardless of whether its a single row or all. As well am not sure whether to create it in triggers under the table or Database triggers?. i want the script to check if the trigger exist and if it doesn't then create it.

table name: qf.customer_working_hours
columns: WHours_id,DayOFW

Stored Procedure: qf.ServiveRefreshCustomer_WH

Am new to SQL scripting and SQL triggers, any help will be appreciated

I am assuming you are trying to implement some basic replication using a trigger. As long as your process remains fairly simple, this should work OK.

However, I would advise you to read up on (i.e. google) why executing stored procedures from triggers is not a good idea, and also about SQL's built in transactional replication feature. Here is a practical guide to the 2nd topic: http://www.sql-server-performance.com/2010/transactional-replication-2008-r2/

Having said that, here is what I think should work:

USE [database_name]
IF EXISTS (SELECT 1 FROM sys.triggers WHERE name = N'trigger_name')
BEGIN
  THROW 51000, 'The trigger [trigger_name] already exists.', 1;
END
ELSE
  CREATE TRIGGER [trigger_name] ON qf.customer_working_hours
  AFTER INSERT, UPDATE, DELETE AS
  BEGIN
    SET NOCOUNT ON
    EXEC [procedure_database].qf.ServiveRefreshCustomer_WH
  END
BEGIN
END

EDIT: Updated trigger if exists check from Aaron's suggestion below.


Changing stored procedure parameter results slow stored procedure performance I have a stored procedure to fetch all users in the database. To do this it queries a view which is composed on two tables: Users (holds basic info like username) and UserPortals (maps users to certain sites).

The only parameter that changes between executions is the 'PortalId'.
Here is an execution plan that returns results in about 1 second
However, when changing the PortalId to a different value it takes about 21 seconds. As seen here. The slower query is for a PortalId of a newer portal so the data is presumably further towards the end of the table. Does this matter?

The interesting difference in the execution plans is that for the slower query, all rows in the Users table are scanned, however this is not the case for the faster one.

Are there any ideas on how to make the slower query faster? I assume it is an indexing problem.

Note: I have tried adding the recommended index and it actually resulted in worse performance.

One of the first things to look at in an actual execution plan, especially on the most expensive operations, is the estimated vs the actual row counts, executions, et cetera.

Often, when the estimated numbers are significantly off from the actual numbers, it indicates that the statistics for the table are out of date. This can cause the query engine to choose a bad plan. Based on what it knows about your data, the plan makes sense; however, enough of the data has changed since your statistics were last updated that the plan's not the best option for actually returning your data.

If you don't have a job that regularly updates statistics, trying updating the statistics on just this one table (or, more likely, the tables involved in this view), and see if the query speed becomes more predictable. I'd recommend setting up a statistics update job to run on a regular basis (probably at least weekly).

Note that, as the stored procedure is recompiled each time through, some time is spent calculating that optimal plan. If the speed of the stored procedure becomes more predictable, you may want to consider trying it without the WITH RECOMPILE; depending on the complexity of the query, the cost of the recompilation might be higher than the cost of using the same plan with different parameters.


Merge data with UNION ALL and INNER JOIN in MySQL I try merge a data to one row. I have query:

(select t.`id`,
        t.`coins_id`,
        t.`first_coin`,
        t.`second_coin`,
        t.`price` as `price_old`,
        null as `price_young`,
        t.`time`
from hist_all t
inner join (
  select `first_coin`,
        MIN(`time`) as `MINDATE`
  from hist_all group by `first_coin`
) t1
on t.`first_coin` = t1.`first_coin`
and (t.`time` = t1.`MINDATE`)
WHERE (second_coin = 'USD' OR second_coin = 'USDT')
and `time` between (NOW() - interval 9999 minute)
AND NOW()
group by t.`first_coin`
order by t.`ID`)
UNION ALL
(select t.`id`,
        t.`coins_id`,
        t.`first_coin`,
        t.`second_coin`,
        null as `col_null_1`,
        t.`price` as `new`,
        t.`time`
from hist_all t
inner join (
  select `first_coin`,
         MAX(`time`) as `MAXDATE`
   from hist_all
   group by `first_coin`
) t1
on t.`first_coin` = t1.`first_coin`
and (t.`time` = t1.`MAXDATE`)
WHERE (second_coin = 'USD' OR second_coin = 'USDT')
and `time` between (NOW() - interval 9999 minute)
AND NOW()
group by t.`first_coin`
order by t.`ID`)

This query reurn for example:

I need such a result:

The id, coins_id and time values are irrelevant, I gave them for example.

This can probably be simplified, but in it's simplest form all you need to do is to use an aggregate function for price_old and price_young:

select first_coin, second_coin, max(price_old), max(price_young)
from (
    (select t.`id`,
            t.`coins_id`,
            t.`first_coin`,
            t.`second_coin`,
            t.`price` as `price_old`,
            null as `price_young`,
            t.`time`
    from hist_all t
    inner join (
      select `first_coin`,
            MIN(`time`) as `MINDATE`
      from hist_all group by `first_coin`
    ) t1
    on t.`first_coin` = t1.`first_coin`
    and (t.`time` = t1.`MINDATE`)
    WHERE (second_coin = 'USD' OR second_coin = 'USDT')
    and `time` between (NOW() - interval 9999 minute)
    AND NOW()
    group by t.`first_coin`
    order by t.`ID`)
    UNION ALL
    (select t.`id`,
            t.`coins_id`,
            t.`first_coin`,
            t.`second_coin`,
            null as `col_null_1`,
            t.`price` as `new`,
            t.`time`
    from hist_all t
    inner join (
      select `first_coin`,
             MAX(`time`) as `MAXDATE`
       from hist_all
       group by `first_coin`
    ) t1
    on t.`first_coin` = t1.`first_coin`
    and (t.`time` = t1.`MAXDATE`)
    WHERE (second_coin = 'USD' OR second_coin = 'USDT')
    and `time` between (NOW() - interval 9999 minute)
    AND NOW()
    group by t.`first_coin`
    order by t.`ID`)
) as t
group by first_coin, second_coin


JOIN on variable number of columns I have the two tables courses AND something_about_courses:

courses

course_code
course_session
course_type

something_about_courses

course_code
course_session
some_description

To ensure that I fetch some_description only for courses that actually exist, I use:

SELECT some_description
  FROM something_about_courses A
  JOIN courses B
    ON (A.course_code = B.course_code
   AND A.course_session = B.course_session)

Okay, everything is good and the system is put in to use. Now, two year after the system is in use, the client wants to compare not just with {course_code, course_session} but also with course_type.

So I change the structure of something_about_courses to:

course_code
course_session
course_type
some_description

and I change to query to:

SELECT some_description
  FROM something_about_courses A
  JOIN courses B
    ON (A.course_code = B.course_code
   AND A.course_session = B.course_session
   AND A.course_type = B.course_type)  <<<<<<< AND clause added to JOIN

The problem I now face is that all old data in something_about_courses doesn't JOIN successfully since they have a NULL for course_type as it was a newly added column.

In other words, I have working queries but a disconnect in the data sets.

Solutions I have thought of:

Date based segmentation: use the old query for before the feature change and the new query for after. However, this isn't elegant and has a huge amount of maintenance overhead.
Manual adjustment of the old data: there is way too much of this to adjust it to be compatible with the new query to make this feasible.

Neither of the above solutions are ideal. I was wondering if there is anything I can do at the query level to JOIN on 2 or 3 columns based on whether something_about_courses.course_type IS NULL.

Try and see if this yields the result you want:

SELECT some_description
FROM something_about_courses A
JOIN courses B
ON (A.course_code = B.course_code
AND A.course_session = B.course_session
AND (A.course_type = B.course_type OR B.course_type IS NULL))


Pagination in SQL Server I have a very large database, roughly 100 GB. I'm executing query:

select * from <table_name>;

and I want to show only the 100th to 200th rows.

I want to understand how this happens internally. Does the database fetch all of the records from disk into memory and sends back 100th to 400th rows to the querying client? Or does there exist any mechanism, so that only those records (100th -200th) are fetched from database - by using indexing mechanism like B-trees etc.?

I found that this is related to pagination concept, but I could not exactly find how it happens internally at the database level.

In the query you posted:

select * from <table_name>;

There's no such thing as the 100th-200th rows, because you don't specify an ORDER BY. Order isn't guaranteed unless you include the ORDER BY for a whole lot of interesting reasons, but that's not really the point here.

So to illustrate your point, let's use a table - I'm going to use the Users table from the Stack Overflow data dump, and run this query:

SELECT * FROM dbo.Users ORDER BY DisplayName;

By default, there's no index on the DisplayName field, so SQL Server has to scan the entire table, then sort it by DisplayName. Here's the execution plan:

It's not pretty - that's a lot of work, with an estimated subtree cost of around 30k. (You can see it by hovering your mouse over the select operator over at PasteThePlan.) So what happens if we only want rows 100-200? We can use this syntax in SQL Server 2012+:

SELECT * FROM dbo.Users ORDER BY DisplayName OFFSET 100 ROWS FETCH NEXT 100 ROWS ONLY;

The execution plan on that is pretty ugly too:

SQL Server's still scanning the whole table to build the sorted list just to give you your rows 100-200, and the cost is still around 30k. Even worse, this whole list will be rebuilt every time your query runs (because after all, someone might have changed their DisplayName.)

To make it go faster, we can create an nonclustered index on DisplayName, which is a copy of our table, sorted by that specific field:

CREATE INDEX IX_DisplayName ON dbo.Users(DisplayName);

With that index, our query's execution plan now does an index seek:

The query finishes instantly and has an estimated subtree cost of just 0.66 (as opposed to 30k).

In summary, if you organize the data in a way that supports the queries you frequently run, then yes, SQL Server can take shortcuts to make your queries go faster. If, on the other hand, all you have is heaps or clustered indexes, you're screwed.


How export a sql server 2008 diagram to PDF filetype? I want to have an export from my database diagram to PDF or image types. How can I do this?
I worked with SQL Server 2008 R2.

There is no direct way to transform the standard SQL Server diagram to a PDF or an image file type. Actually there is no simple way to export the diagram from within the tool.

You can, however, save it externally and send it to someone by:

print to a file printer (an .xps file) which is formatted like a pdf and an XPS file viewer exists on any windows machine;
open the diagram and right click somewhere in the middle and you'll have there the option to "Copy diagram to clipboard". From here you paste to MSPaint or another image creator and save to wanted format;

Not very straightforward, but can do the job.


What is a residual predicate? Although I was able to find the definition of predicates online at MSDN:

Is an expression that evaluates to
TRUE, FALSE, or UNKNOWN. Predicates
are used in the search condition of
WHERE clauses and HAVING clauses, the
join conditions of FROM clauses, and
other constructs where a Boolean value
is required.

I have been unable to find the definition of what a residual predicate is. What is a residual predicate?

A residual predicate is one that has an hidden extra cost because the predicate has to be tested again on every combination of rows that is fetched.

See Probe Residual when you have a Hash Match – a hidden cost in execution plans by Rob Farley.


What is the correct name and significance of EAV table pivot? I've been forced to use a very uncomfortable structure for a table. After trying to up similar examples, I've discovered the EAV, which seems almost like a pivot of said structure. The attribute order is not set in stone (except that there are no new attributes after a NULL column). For the max amount of attributes N, there are 2*N columns in pairs of Attribute_Name - Attribute value.

EAV example:

ID| Entity  | Attribute  | Value
__________________________________
1 | Entity1 | Attribute1 | Value1
2 | Entity2 | Attribute1 | Value2
3 | Entity2 | Attribute2 | Value3
4 | Entity2 | Attribute3 | Value4
5 | Entity3 | Attribute1 | Value5
6 | Entity3 | Attribute2 | Value6

Example of my table:

Entity  | A_Name1    | A_Value1 | A_Name2       | A_Value2 | A_Name3       | A_Value3
______________________________________________________________________________________
Entity1 | Attribute1 | Value1   | NULL          | NULL     | NULL          | NULL
Entity2 | Attribute2 | Value3   | Attribute1    | Value2   | Attribute3    | Value4
Entity3 | Attribute2 | Value6   | Attribute1    | Value5   |  NULL         | NULL

Should this be considered separate from EAV structure? Is there any classification for this kind of table structure, or is it one of a kind? Basically, I want to learn as much as possible about it and ways to work around it.

EDIT: Forgot to mention -- the Attribute names might also repeat like this:

Entity  | A_Name1    | A_Value1 | A_Name2       | A_Value2 | A_Name3       | A_Value3
______________________________________________________________________________________
Entity1 | Attribute1 | Value1   | NULL          | NULL     | NULL          | NULL
Entity2 | Attribute2 | Value3   | Attribute1    | Value2   | Attribute2    | Value4
Entity3 | Attribute2 | Value6   | Attribute1    | Value5   |  NULL         | NULL

The process of pivoting data is often called transposition, as it is similar to the matrix operation of that name in mathematics (http://en.wikipedia.org/wiki/Transpose), though that isn't really what you are doing in your example.

In your output you still have the EAV pattern but stored less efficiently. The columns "Entity, A_Name1, A_Value1" is one attribute in the EAV pattern, "Entity, A_Name2, A_Value2" is another, and so forth. Each of your value columns could contain a range of probably unrelated attribute values so you are not in any sort of normal form and reporting is likely to be even more difficult than the original layout.

A proper transposition in this case would be:

Entity  | Attribute1 | Attribute2 | Attribute2
----------------------------------------------
Entity1 | Value1     | NULL       | NULL
Entity2 | Value2     | Value3     | Value4
Entity3 | Value5     | Value6     | NULL

This way each column contains data for only one attribute.


Error while running sp_blitz in 2008 SQL Server and lower versions I have created an SSIS package to run sp_blitz in my production servers. The stored procedure runs fine for servers having a version higher than 2012 but fails for 2008 and 2005.

Please find the subsequent error message that I received while running the package.

  [SP Call [50]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB
  error has occurred. Error code: 0x80040E14. An OLE DB record is
  available.  Source: "Microsoft SQL Server Native Client 11.0"
  Hresult: 0x80040E14  Description: "Statement(s) could not be
  prepared.". An OLE DB record is available.  Source: "Microsoft SQL
  Server Native Client 11.0"  Hresult: 0x80040E14  Description:
  "Incorrect syntax near 'RESULT'.".

I have googled about the error and found that EXECUTE WITH RESULT SETS feature was introduced in 2012. Thus running the stored procedure with RESULT SETS fails in lower versions. I am using the below query to execute the stored procedure.

EXEC sp_Blitz WITH RESULT SETS (
(
Priority TINYINT ,
FindingsGroup VARCHAR(50),
Finding VARCHAR(200),
DatabaseName NVARCHAR(128),
URL VARCHAR(200),
Details NVARCHAR(4000),
QueryPlan XML  NULL,
QueryPlanFiltered [NVARCHAR](MAX) NULL,
CheckID INT,
Server_Name sql_variant,
[Server_Version] varchar(128),
[Server_Edition] sql_variant,
[Server_Product_Level] sql_variant,
[Server_Product_Version] sql_variant
));

Half of my production servers are 2008 and 2005. Is there any alternative command or method that can be used so that this can run in lower versions as well.

One way you could go about this if you want to influence data types or column names is create an intermediate table to store the result of the stored procedure and then selecting from it.

Something along the lines of

CREATE TABLE #temp (< column definitions like the stored procedure returns >);

INSERT INTO #temp EXEC sp_Blitz;

SELECT < CAST columns to data types you want > AS < alias column names to names you want >
FROM #temp;


Why is the ORDER BY clause in a view ignored as soon as it's called with a WHERE claue? I have a view that joins several tables, just to simplify a frequently called query. This view has an ORDER BY clause on a calculated field (the order will never be different). (on SQL Server Express 2008 R2).

The basic idea is like this (simplified):

SELECT [EventType].Name,
    [EventType].TotalOccurrences,
    [Session].ID,
    [Session].TotalOccurrences,
    [Session].TotalOccurrences / [EventType].TotalOccurrences AS saturation
FROM [EventType]
    INNER JOIN [Session] ON [EventType].ID = [Session].Event
ORDER BY saturation

This view will never be called without a where clause though (which in my case would almost return the entire database). It will always be with WHERE [EventType].ID = x. However as soon as I add the where clause, the ORDER BY clause is ignored! Without the where clause it is working as expected.

Actually a different question but I'm throwing it in here while I'm at it, is there a better way to retrieve such info? The [EventType].Name and [EventType].TotalOccurrences will be identical for all returned rows, which is a small waist of network bandwidth. Not that that is a problem, but I'm wondering if anything exists to tackle that without the latency overhead of multiple round-trips to the database?

[Edit] The above sample was over-simplified, the calculated field has other factors so that it would return a different order than simply ORDER BY [Session].TotalOccurrences as it would in this example, but the principle should be clear.

[Edit2] From the answer I come to the conclusion that the Graphical Designer has more features than the underlying database:

The query you posted is not valid for creating a view; running CREATE VIEW xy AS for this query will result in an error. Are you using a TOP clause?

A view, being a table expression (a set), can't have the order defined, since that would be against the principles of a relational model (there is no order for rows in a relational table - a set is an unordered collection of tuples). Same goes for other table expressions - derived tables, CTEs etc.

From BOL article about the ORDER BY clause:

  The ORDER BY clause is not valid in views, inline functions, derived
  tables, and subqueries, unless either the TOP or OFFSET and FETCH
  clauses are also specified. When ORDER BY is used in these objects,
  the clause is used only to determine the rows returned by the TOP
  clause or OFFSET and FETCH clauses. The ORDER BY clause does not
  guarantee ordered results when these constructs are queried, unless
  ORDER BY is also specified in the query itself.

Long story short: Use the ORDER BY clause in the outer query that references the view. Do not use it in a view. Even using it with TOP(100) PERCENT (or on SQL Server 2012, the OFFSET-FETCH equivalent) does not guarantee presentation order, it just means you'll get the top 100% of the rows, in any order.


Does 'LGWR' writes to online redo log files strictly in circular fashion? Does 'LGWR' writes to online redo log files strictly in circular fashion? I saw this sentence from Oracle documentation.

  If archiving is disabled (the database is in NOARCHIVELOG mode), then when the last redo log file is full, LGWR continues by overwriting the first available active file.

Does that means some redo log files could be skipped when LGWR writes to them?

Thanks.

Yes it does - Online Logfiles cannot be skipped because they are still ACTIVE. This causes a wait event called checkpoint not complete, by the way.


How to handle user with no password in postgresql I installed postgresql but it's asking me for passwords that I never had and never entered. There is a postgres ubuntu user and a database user named moodle. Neither of these were created with a password. Now that my system is asking me for those passwords, what should I do? Why is this process not easy?

$ psql -Umoodle -hlocalhost moodle < /var/www/vcmoodle/moodle/kth-install/moodle_fresh_postgres.sql
Password for user moodle:
psql: fe_sendauth: no password supplied
postgres@dac-VPCSA2Z9E:~$ sudo psql -Umoodle -hlocalhost moodle < /var/www/vcmoodle/moodle/kth-install/moodle_fresh_postgres.sql
[sudo] password for postgres:
postgres@dac-VPCSA2Z9E:~$ sudo -u moodle psql moodle
sudo: unknown user: moodle
sudo: unable to initialize policy plugin
postgres@dac-VPCSA2Z9E:~$ sudo -u postgres psql moodle
postgres is not in the sudoers file.  This incident will be reported.
postgres@dac-VPCSA2Z9E:~$ sudo -u dac psql moodle
[sudo] password for postgres:
Sorry, try again.
[sudo] password for postgres:
sudo: 1 incorrect password attempt
postgres@dac-VPCSA2Z9E:~$ sudo -u postgres psql
postgres is not in the sudoers file.  This incident will be reported.
postgres@dac-VPCSA2Z9E:~$

In pg_hba.conf, you should set:
# TYPE  DATABASE        USER            ADDRESS                 METHOD
local     all           all                                   trust

Authentication method should be TRUST, that will allow you to enter postgres without a password and set it for postgres user. Below you see how to change password using SQL:
ALTER USER postgres WITH PASSWORD 'pass';

However, you should use psql's \password instead, so the password change (and the password itself) is not logged under any circumstance.
From the PostgreSQL 9.4.12 Documentation for psql:

\password [ username ]
Changes the password of the specified user (by default, the current user). This command prompts for the new password, encrypts it, and sends it to the server as an ALTER ROLE command. This makes sure that the new password does not appear in cleartext in the command history, the server log, or elsewhere.

Of course you should change option TRUST to for example md5 later for security reasons.


Unable to open SQL Server Config Mgr, get a WMI error I got a WMI error from SQL Server Configuration Manager after I ran sp_dropserver and sp_addserver commands.

Various posts suggested I run the mofcomp command.  I did this, and got the following errors.  I did run it using administrator privileges, and we are on SQL Server 2008 R2, so "100" should be correct.

C:\Program Files (x86)\Microsoft SQL Server\100\Shared>mofcomp sqlmgmproviderxpsp2up.mof
Microsoft (R) MOF Compiler Version 6.2.9200.16398
Copyright (c) Microsoft Corp. 1997-2006. All rights reserved.
Parsing MOF file: sqlmgmproviderxpsp2up.mof
MOF file has been successfully parsed
Storing data in the repository...
An error occurred while opening the namespace for object 1 defined on lines 4 - 7:
Error Number: 0x80070422, Facility: Win32
Description: The service cannot be started, either because it is disabled or because it has no enabled devices associated with it.
Compiler returned error 0x80070422
C:\Program Files (x86)\Microsoft SQL Server\100\Shared>

Any assistance is much appreciated. I am stumped as to what to do next. The instance is up, but I cannot stop or restart it even from SSMS (options greyed out).

History of what happened, more details and error message as suggested.

SA "cloned" the entire box A to new box B (not actual names, of course).
I was on box B and needed to change name of instance from A to B so
that developers could connect to a "new" instance (B).  Remember, this
is a clone of A, so the instance name was A even though I was on
the physical box B.  Using SSMS, I connected to the database using name
A, because that is what I saw as the server name in the connection box.
OOPS!  I was actually connected to instance A on box A, not B, but
I did not know that at this time. I ran dropserver A, addserver B
and then restarted instance (remember, I am on box B).  The change
did not appear (I now know why, the change happened on box A!).

Discovering my mistake, I go to box A and see that the instance name
is now B (output of sp_helpserver).  I run dropserver B, then addserver
A to "fix" it (put it back to A), then I tried to run SQL Config Manager
(as administrator) to "enable" the rename. That is when I get this error:

Cannot connect to WMI provider.  You do not have permission or the
server is unreachable. The service cannot be started because it is
disabled or because it has no enabled devices attached to it.
Error 0x80070422.

I tried to fix this using the mofcomp command I mentioned in original
post.  The error I got from that seems to match the error above when
I run Config Manager.

When I now run sp_helpserver on A, I still see instance name as B.  However,
the SSMS connection box shows instance name as A, and it cannot connect
to instance B (not found).  The output of SELECT @@SERVERNAME AS 'Server Name';
is instance name A.  So, there is a discrepancy between what @@SERVERNAME
shows and what sp_helpserver shows.

I found the issue. The WMI service had been disabled (hence the error message). I used the properties menu (running as admin) from the services list, and put this service on auto start and then started it. Config Mgr came up.


ALTER DATABASE statement not allowed within multi-statement transaction I've downloaded the AdventureWorks based In-memory sample from here, and followed all the steps described in the accompanying doc. However, when I try to run the script in SQL Server Management Studio, I get the error message:

  ALTER DATABASE statement not allowed within multi-statement transaction

The error points to line 9, which is:

IF NOT EXISTS (SELECT * FROM sys.data_spaces WHERE type='FX')
    ALTER DATABASE CURRENT ADD FILEGROUP [AdventureWorks2012_mod]
    CONTAINS MEMORY_OPTIMIZED_DATA
GO

Since this is (more or less) official Microsoft documentation, I'm assuming it's something I'm doing wrong, but I can't figure out what it is.

No, you're not doing anything wrong. I got the same thing. I solved it by breaking the sample up into multiple scripts and running each section of the script sequentially, in its own query window, instead of as one big script. This worked in my case because I'm always running these samples in an isolated VM (not on a production server!) and transaction handling is unnecessary since I'm the only one here.

Looking at the script again today more closely, there is no transaction handling defined explicitly, but perhaps you pasted the script into a query window that already had an active transaction, or created a new query window that automatically added BEGIN TRANSACTION; / COMMIT TRANSACTION; statements.

I also pointed out a couple of other potential gotchas in this blog post.


Resolving MySQL Process ID to page that ran it? I asked this question on stackoverflow and was directed to ask this question here.

I have a couple of scripts in PHP that run MySQL queries.  On occasion they cause the server to seize up or become clogged with MySQL processes that take a while, for example badly formed LEFT JOINs etc.

When I run the query to find information about all current MySQL queries that have been run is it be possible to resolve a query ID to the file that ran it?

There is a query that has been sleeping for 3973 there is no info about this query and so I am trying to find as much information as possible?

There are three(3) ways to find out info on queries and its process ID

Way #1 : Activate the General Log as a Text File

If you add the following lines to my.cnf and restart mysql

[mysqld]
general-log
general-log-file=/var/log/mysql_general.log

You can parse /var/log/mysql_general.log text file. The header of each query should have the process ID (Thread ID) that the query ran under

Way #2 : Activate the General Log as a MyISAM table

There is a table called mysql.general_log. It is a CSV file by default. Run the following commands:

ALTER TABLE mysql.general_log ENGINE=MyISAM;
ALTER TABLE mysql.general_log ADD INDEX (event_time);

then add the following to my.cnf

[mysqld]
general-log
log-output=FILE

Once you have the general log entries stored as a table rows, you can run SELECTS against the MyISAM at will, with queries like:

SELECT * FROM mysql.general_log
WHERE event_time >= (DATE(NOW())+INTERVAL 0 SECOND)
AND event_time <= NOW();
AND argument like '...';

Way #3 : Check the Process List

If you want to catch the queries in the act of running long, do the following (if you have MySQL 5.1) :

SELECT id,info FROM information_schema.processlist
WHERE command = 'Query' AND user <> 'system user';

This shows you every query currently running. You can parse the info column for whatever query you are tracking down


Understanding MAXDOP behaviour for queries going parallel For my current server as below

4 Sockets with 10 cores per socket and 20 logical processors per socket.

Total 80 per SQL licensing: 4 NUMA nodes with HT enabled

I've set CTOP 50 and MAXDOP 8 on this DB servers with just one database of approx 15 TB which is Log Shipping stand by database ie Read only state

I recently changed the MAXDOP from 0 to 8 as i see lots of crapy queries running over 4-5 hours [not all but most of them] assuming were all over the CPU threads when checked their DOP was 64 all the time.

From above i assumed there could be excessive work being done by specific CPU cores when those heavy query starts coming, while some might still be idle. Not sure if this is how it can work.

SO now i've changed to MAXDOP 8 and see DOP for those queries showing as 8 fpr each individual queries ,say for 10 different queries running at this time.

  How can i test/check if those queries are actually distributing the
  work across all the cores evenly or is it still the case where all of
  them might be doing all the work on one NUMA node socket with 20 cores
  while remaining 60 are idle?

Yes there are performance issues with reports slow but its one of such case where i want to ensure first from my end if there is something that can be done at my server level while developers or people running the reports tune up their queries. Also this being ready only i cant change indexes or control better update to stats on LS restore occurring every 12 hours .

How can i test/check if those queries are actually distributing the
  work across all the cores evenly or is it still the case where all of
  them might be doing all the work on one NUMA node socket with 20 cores
  while remaining 60 are idle?

I would start by looking at overall CPU utilization on the server. If CPU is relatively high then you know that you don't have the problem that you described. In that situation, you might do one or more of the following to improve throughput:

Tune your queries to use less CPU
Run fewer queries
Get more or better hardware

If CPU is relatively low then you have a different type of CPU problem. In that situation, you might do one or more of the following to improve throughput:

Run more queries
Check wait stats to see if there's something stopping queries from running or using as much CPU as you'd like

As a rule of thumb, you should not expect that if you run 10 MAXDOP 8 queries then all 80 schedulers will end up with at least one parallel worker. The algorithm for allocating worker threads just isn't that smart. Bob Dorr provides some details here. In my experience with large servers I've found that issuing more queries than I would otherwise like was essential to get all of the schedulers to have a chance of having at least one parallel worker. Parallel worker threads are by default not bound to the same NUMA node. The situation that you're describing (only one busy NUMA node) can happen if your queries on other NUMA nodes finish first.

You may find some of the following links to be helpful if you want to look into the amount of work each CPU is performing:

Overview of Windows Performance Monitor
Resource Monitor
sys.dm_os_tasks


SQL keyword difference between MySQL and Oracle 10g? Good afternoon,

Are there differences between Oracle 10g and MySQL with the CREATE, ALTER, DROP, REF, PRIMARY KEY, FOREGIN KEY and attribute variable type keywords?

The reason I ask, is I'm planning to use MySQL WorkBench to generate the SQL for Oracle 10g. If you'd recommend a different ERD modelling tool, please suggest one that can be generated from text.

Thanks for all suggestions,

Alec Taylor

There are big differences between the two. The datatypes are different (e.g. Oracle doesn't have the ENGINE part, int(5) is invalid for Oracle, LONGTEXT is invalid for Oracle and so on) and part of the syntax as well. Oracle does not have an "autoincrement" column. It has a different concept using sequences.

If you want to find all the differences you will have to read the manuals and compare for yourself

If MySQL Workbench emits only MySQL syntax you will need some manual search & replace to convert that to valid Oracle SQL but it is certainly doable.


Unable to attach database to Amazon RDS using SQL Server Management Studio 2016 I'm attempting to attach an mdf file to an instance of SQL Server running on Amazon's RDS service.  Using Microsoft SQL Server Management Studio 2016, I follow the following steps:

After connecting to the amazon instance, I right click on the Databases directory and select Attach.
Up comes the Attach Databases dialogue
I click the Add button to select the local .mdf
I get the following error message:

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476

ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The EXECUTE permission was denied on the object 'xp_fixeddrives', database 'mssqlsystemresource', schema 'sys'.
The EXECUTE permission was denied on the object 'xp_fixeddrives', database 'mssqlsystemresource', schema 'sys'.
The EXECUTE permission was denied on the object 'xp_fixeddrives', database 'mssqlsystemresource', schema 'sys'.
The EXECUTE permission was denied on the object 'xp_fixeddrives', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)

I've seen this error message on several forums but always when trying to modify the database (at this point I believe I'm only bringing up a file browser to locate the mdf).  I've also tried running it as admin but no luck.  I'm quite new to SQL Server and am much more at home in a Linux environment, so I wouldn't rule out something obvious.  Any help would be appreciated.

With Amazon RDS, you don't get direct access to the underlying filesystem.

If you have an existing database that you want to migrate into RDS SQL Server, rather than using the MDF/LDF files, you'll need to do a restore instead.

Amazon's instructions to restore a database are pretty good, but since you're asking this question, I'm guessing I probably need to fill you in on a background concept. Remember how I said you don't get access to the filesystem? Well, you can get access to OTHER file systems, kinda like how mapped drives work in Windows. S3 is Amazon's simple storage service where you can create buckets and put files in 'em. You're going to need to:

Create an S3 bucket
Put your database backup (not MDF/LDF) in the bucket
Give your RDS instance permission to read from that S3 bucket (that's where Amazon's instructions come in)
Use Amazon's custom stored procedure rds_restore_database to restore the backup


Can't create federated table in MariaDB using CREATE SERVER - 'Foreign data src error' I'm using MaraiaDB 5.5.44 on Centos 7.2.

I've used federated tables before across physical servers ... I found the 'CREATE SERVER' mechanism a bit flaky (I could only manage to link tables of the same name) but it worked.  In this case I want to set up federated tables in database A that reference tables in database B where both databases on on the same host.  I'd like the connection between the tables to work using the Unix socket rather than TCP/IP.  The MariaDB server isn't bound to any network interface.

It all works using the CONNECTION string, creating a link here from table 'tmp_table' on the local database to the table of the same name in the 'tmp' database:

> create table tmp_table (t varchar(32)) engine=FEDERATED CONNECTION='mysql://testuser:testpassword@localhost/tmp/tmp_table';

That works perfectly.  But when I set up a SERVER for the connection, using the CREATE SERVER statement, I get a 'Foreign data src error':

> create server test_server foreign data wrapper mysql options (user 'testuser', password 'testpassword', host 'localhost', database 'tmp');

> select * from mysql.servers;
+------------------+-----------+----------------------+----------------+------------------+------+--------+---------+-------+
| Server_name      | Host      | Db                   | Username       | Password         | Port | Socket | Wrapper | Owner |
+------------------+-----------+----------------------+----------------+------------------+------+--------+---------+-------+
| test_server      | localhost | tmp                  | testuser       | testpassword     |    0 |        | mysql   |       |
+------------------+-----------+----------------------+----------------+------------------+------+--------+---------+-------+

> create table tmp_table (t varchar(32)) engine=FEDERATED CONNECTION='test_server/tmp_table';
ERROR 1434 (HY000): Can't create federated table. Foreign data src error:  database: 'tmp'  username: 'testuser'  hostname: 'localhost'

I've tried various permutations of the CONNECTION string - 'test_server/', 'test_server' - but always get the 'Foreign data src error'.

I have been able to get federated tables working before, using a TCP/IP connection to the source database on another host, so I'm wondering if I've made a mistake or if MariaDB simply can't use a SERVER for a connection between local databases over the Unix socket.  Can anyone help me with this problem?

Nuts.  I'd been looking at the documentation on the MySQL web site for version 5.5 of MySQL.

When I looked up the MariaDB's site and explored its knowledge base pages I discovered that:

a.  They've abandoned the FEDERATED engined and have replaced it with a FederatedX engine, even though:

b.  The syntax still uses the 'FEDERATED' key word, but also

c.  They have added a 'Socket' option to the CREATE SERVER statement.

The 'Socket' column was in the listing of the mysql.server table but I assumed it should just be blank, and it wasn't mentioned in the MySQL documentation.

Anyway, adding a 'socket' option to CREATE SERVER to tell MariaDB to explicitly user the database server's socket has solved the problem.

Weird how the SOCKET wasn't necessary in the long-form CONNECT string though.