Is it possible to copy mounted database without RMAN? I need to make a copy of production database for development. For some reason I can't use RMAN. My question is: can I copy standby database while its in mounted state and recovery is canceled? Of course I can shut it down to make a "cold backup", but I do not want to lose time for manually copying new archive logs (and I don't have enough space on primary for keeping them for a long time).

The procedure was fine until today. After a few hours I got this:

/usr/sfw/bin/gtar: owfin_d/owfin_d06.dbf: file changed as we read it.

It is strange because timestamps on all db files shows the time when I canceled standby recovery.

UPD After a long time I found the problem. For some reason a scrip on cron schedule changes file permissions and tar responds with "file changed as we read it".

If you change file's permissions during copy operation, tar will throw "file changes as we read it".


Ensuring Only Fresh Data Gets Accessed on SQL Server 2008/2012 Replication For Reads I am designing a scale out solution for a globally dispersed company that has data centers on all continents. Over 10,000 servers report to a SQL Server cluster in Los Angeles, and also query it to perform certain tasks.

The Justification/Reason

In order to move from clusters, the issues with vertical scaling, and to improve performance due to the global network distance between many nodes to the cluster in LA, I thought to use Transactional Replication on specific core tables to subscribers (single pub multi sub) spread out across the world as my first step.

Issue to be Resolved

If for whatever reason the data is over X minutes old (let's say 10 minutes), I want them to go to the publisher (or another defined server) and read it from there.  I would like to automatically grab data from the publisher instance if the preferred one is not available or stale.

I'm not sure what the best method is for doing this, especially if the data is 'stale'.  I know we have replication monitoring tools, but how would I use that to know if the data is stale and force the connections to another server that isn't?

The reason is I'm concerned that certain issues will take over 10 minutes to resolve, during this time the critical apps such as maintenance and automatic provisioning will get stale data and the business could be greatly impacted.

Possible Solutions & Issues With Them:

Put the Subscribers/Publisher in a 'read only' load balancer and have all queries go there.

If the database servers were all in the same data center, I would probably use a load balancer and pull servers out of it, but that will require manual intervention, and I want the system to force all reads to the publisher (or another subscriber) and bypass the subscriber they connect to.

This would not resolve the issue with stale data however, such as if replication falls behind and it takes an hour to fix.

Modify Code To Check Outstanding Replicated Queries And Adjust Accordingly

This would require code modifications on many queries and the devs won't have the cycle to do so, plus it doesn't sound like a very plausible idea.

Query Replication Status And Change Connection String Automatically If X Number Of Queries Are not Replicated

I should be able to do this in Powershell, but the majority of servers are Linux so they would need an 'app' which queries the replication status, and modifies their connection string if it is over X number of unreplicated commands.

Thank you!  I have 1 2012 environment and several 2008 environments I'd like to implement this on, with the idea that we will move them all to 2012.  Enterprise is an option.

Other Ponderings

Having a load balancer in LA that the world sends a very quick small query to, to decide which server to pick might work well actually, as it would be 1 small query and perhaps the requesting end could cache that info temporary.

It seems like I'd have to have the SQL or other DB engine that is the 'read only' pair spit back to the app 'this data is stale, go somewhere else, preferably here'

Transactional replication can be used for something like this, but it would be tricky. However it sounds like what you are doing is keeping multiple front facing caches up to date.

This is tricky as a polling mechanism limits scalability. What works best is a push mechanism where if a change occurs, or some data meets a certain criteria it is send to all caches, in other works a messaging framework.

If this is to be done within SQL Server, service broker might be a better fit if the number of nodes are high and the transactions low. If the transactions are high and the number of nodes relatively small, transactional replication might be a better fit.

If the database is a backend for an app which is providing global failover services an application solution will likely be a better fit. I happen to know a guy who implemented a similar caching mechanism for a large auction site and then


Display multiple rows of one table into one row without using PIVOT I'm currently trying to form a SELECT query to from a table without using PIVOT.

Examples of data and expected result are shown below:

DB Data

| Id |  TYPE      |  Amount |
| 1  |  Deposit   |  50     |
| 2  |  Withdraw  |  10     |
| 3  |  Withdraw  |  30     |

Expected Result

| Row |  TYPE      |  Amount |   TYPE     |  Amount |
|  1  |  Deposit   |   50    |  Withdraw  |  10     |
|  2  |     -      |    0    |  Withdraw  |  30     |

The deposits and withdrawals are not related to each other, just displaying in ascending order of id. The expected end result is two row of all deposits while another two row with withdrawals. The purpose of doing this is to maximize spaces.

Thought of using left join but there's no unique id that can let me use GROUP BY

Thanks in advance for helping.

declare @t table (Id int, [TYPE] varchar(100), Amount int);
insert into @t values
( 1,  'Deposit',  50),
( 2,  'Withdraw',  10),
( 3,  'Withdraw',  30);

with cte as
(
select *, ROW_NUMBER() over(partition by [TYPE] order by id) n
from @t t
),

nums as
(
select distinct n
from cte
)

select n.n as row, c1.TYPE, c1.Amount, c2.TYPE, c2.Amount
from nums n
     left join cte c1
         on n.n = c1.n and c1.TYPE =  'Deposit'
     left join cte c2
         on n.n = c2.n and c2.TYPE = 'Withdraw';


Help Writing an UPDATE Statement With JOIN I am new to SQL Server and am attempting to write an UPDATE statement that includes a JOIN. Essentially this is what I am trying to write, but obviously this is invalid syntax.  In SQL Server 2008 R2 what would be the appropriate way to write this syntax?

Update mi
Set totalvalue = TV
FROM masterinfo mi
Inner Join xyz
(
  Select accID
  ,SUM(totalval) TV
  from OPENQUERY(EA, 'Select accID, totalval from sv')
  GROUP BY accID
) xyz

I think you just missed the ON clause and placed the xyz alias twice. Corrected:

UPDATE mi
SET totalvalue = xyz.TV
FROM masterinfo mi
  INNER JOIN                     -- removed incorrectly placed alias xyz
  (
    SELECT accID, SUM(totalval) TV
    FROM OPENQUERY(EA, 'select accID, totalval from sv')
    GROUP BY accID
  ) xyz                          -- correct here
  ON xyz.accID = mi.accID        -- missing ON clause, adjust
                                 -- according to your structure
 ;

Once - and if - you are sure that the above is correct (from the point of what exactly you want to do), you may try to do the aggregation in the remote server. It might be more efficient:

UPDATE mi
SET totalvalue = xyz.TV
FROM masterinfo mi
  INNER JOIN
  (
    SELECT accID, TV
    FROM OPENQUERY(EA, 'select accID, sum(totalval) TV from sv group by accID')
  ) xyz
  ON xyz.accID = mi.accID
;

and I think this one can be simplified a bit:

UPDATE mi
SET totalvalue = xyz.TV
FROM masterinfo mi
  INNER JOIN
     OPENQUERY(EA, 'select accID, sum(totalval) TV from sv group by accID') xyz
  ON xyz.accID = mi.accID
;


Mysql replication rebuild dump database lock I need to rebuild master-slave replication of whole instance of mariadb on innodb. What I often see is that people do read lock then dump master then unlock, like:

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
mysqldump --all-databases > all.sql
UNLOCK TABLES;

In this scenario master is locked for significant amount of time. My master is production, so I would rather perform this operation without downtime. My plan is to do this instead:

mysqldump --single-transaction --master-data --all-databases > all.sql

Am I right to assume that in this case I do not need to keep database lock and dump is consistent and good to be used to rebuild the slave machine? I understand that at the beginning of my statement there will be short lock. I just do not see any need to perform additional lock in my case. Is that correct?

Additionally is --single-transaction assuring consistency of single database or whole instance? Is it just one transaction no matter how many databases are there on the instance or new transaction for each database?

--single-transaction is assuring the consistency of the whole instance so yes, across multiple database.

It assumes most tables are transactional (i.e. innodb). For myisam tables these are still read locked briefly for the time taken to dump them.

The mysqldump snapshot method is in the manual.


How to track changes to a record that spans multipe tables I have the challenge to design a new database using a RDBMS, Oracle, to be precise. In this database there will be one specific record/document which may span multiple tables.

With as little duplication as possible, I want to keep track of the changes made to this record, and offer a simple way to create altered copies of that record.

I don't want a history table that will sum up the changes in text, because I want to be able to fetch document x exactly the way it looked at time t. But I don't want to have a full copy for every history entry either.

Are there best practices or patterns how to achieve this?

I may have an idea how to solve this using a strategy mimicking git, but I wanted some opinions first before I go that road.

In Oracle 12c specifically there is a new feature called "temporal flashback queries" that serves exactly this purpose. (Similar features are available in other databases: DB2, SQL Server, Vertica to name a few.)

To enable temporal queries you:

Ensure that each of the tables representing your document has a pair of dates indicating the start and end date (time) when a particular record was valid (active), and
Keep historical records by not updating them in place but rather inserting new versions of the records, updating only the end dates of historical records.

You will then be able to issue queries against your tables that look much like SELECT whatever FROM mytable AS OF some_date, which would return the records that were active at the specified date.

You can read the detailed description here.


The SELECT permission was denied on the object 'syscolumns', database 'mssqlsystemresource', schema 'sys' MSSQL 2005 sp3 32 bit running on Windows 2003

I have a sql server user MyUser in a database called MyDatabase.  MyUser is able to access the MyDatabase database via SSMS.  He is also dbo for that database.

When using the EMS Database Comparer 2011 to compare this database, I am getting the following error.

  The SELECT permission was denied on the object 'syscolumns', database
  'mssqlsystemresource', schema 'sys'

MyUser is not in the db_denydatareader or db_denydatawriter database roles

Is this normal, if so, what can be done to resolve it.

I am able to run the comparison as sysadmin.

I ran a trace using profiler and replayed using the MyUser user

Here is the result

The Query that causes the error

SELECT
    p.action
,   p.protecttype
,   p.columns
,   p.grantor
,   MAX(c.colid) AS colnum
,   u.name
FROM
    sysprotects p
    INNER JOIN
        sysusers u
        ON u.uid = p.uid
    LEFT OUTER JOIN
        syscolumns c
        ON c.id = p.id
WHERE
    p.id = 1497772393
GROUP BY
    p.id
,   p.action
,   p.protecttype
,   p.columns
,   p.grantor
,   u.name

The error sent back by profiler

  [Microsoft][SQL Native Client][SQL Server]The SELECT permission was
  denied on the object 'syscolumns', database 'mssqlsystemresource',
  schema 'sys'. (State 42000) (Code 229)

Some additional Clues

Permissions error if the query is run from SSMS on the same machine as EMS
No error when if the query is run from SSMS on the MSSQL server itself.

As the error indicates the login does not have SELECT permissions on the sys schema. This would require you to specifically grant SELECT to the login, or I believe you could also just GRANT VIEW DEFINITION for the database.

Update
Sorry just caught that last sentence. I have not worked with that software but seems it might be using a different login to execute the query if you can execute the same query fine in SSMS.

On a side note, since you are in SQL 2005, it would be a better practice to start using the new views for looking at catalog information.


Absolute beginner needing some help with PostgreSQL and Python I am primarily a developer, with very little experience around databases, and this is my first time using PostgreSQL. I am using Python 3.6 and PostgreSQL with Psycopg2.

After a user enters a membership_number, 3 things should happen:

Save the membership_number with the time and date, and the location of the store, in a table, let's say the search
table.
Retrieve the customer_name associated with that membership_number, if it is already in the users' table.
If customer_name is not present, ask the user for it and save it on the users' table.

Both tables have the customer_id so I can cross check all the times that someone has searched for a particular membership_number, get their name and  other records with a LEFT JOIN.

I plan to have several devices linked to it, so my main concern is race conditions if I write a query that does something like this:

check if membership number is present, if not, ask the user for the customer_name, then save it and then retrieve the fields needed and make some left join to get aditional info.

Even if this is the right way to do it, I don't get how that query would be constructed.

Also, I've read something about temp tables, but should I:

send the membership_number
save to a temp table
update search table
return results if present in users' table
if not, ask store for customer_name
save to temp table
update users' table
return results (from left join)

This doesn't seem right.

Also read something about IF NOT EXISTS, but again, never used it and don't know how to.

Could it possibly be done with just one table? Am I overthinking this? What should be the best way to achieve this?

DATABASE

The primary function of a database is to ensure that you always have valid data.

Defining valid data is done with CONSTRAINTS (including NOT NULL) and Data Types.

Your application design seems to be based on the idea: "a database is a bit bucket to store data".

This isn't how you treat a database.

A lot of your requirements can be handled by the database if and only if you properly define what is valid data.

Your applications should:
 1. Try to modify the data.
 2.  If the modification fails, handle it within your application.

Constraints

Here is how constraints can throw an error for your application to handle your specified situations.

Adding row to Search table (scan entry)

After you type in the membership ID, you attempt to INSERT a row into the Search table.

The Foreign Key Constraint will automagically lookup the membership_id in the User's table.  If the membership_id is not found, the INSERT throws an error.

Adding row to User's Table (membership info)

After you ask for the person's name for the given membership_id, you attempt to INSERT a row into the User's table.

A Primary Key Constraint will ensure that one row and only one row exists in the User's table with that membership_id.

If that membership_id already exists, an error is thrown for your INSERT statement.

Updating User's information

You are going through a "update customer info" set of pages.  You attempt to UPDATE a row in the User's table.

If that membership_id does not exists, an error is thrown for your UPDATE statement.

If row exists, UPDATE; if not, INSERT

Some databases have a MERGE or UPSERT statement that allows you to UPDATE a row.  If it doesn't exist, it will INSERT the row.

Lookup Member's Name from User's Table (membership info)

You SELECT some data from the Users table.

I'm not 100% sure if you'll receive a "no data found" error for this one.

However, you can always check if you have received 0, 1, or more rows within your application.

Race Condition

A lot of your "Race Condition" concerns should be handled by an ACID Compliant database (eg PostgreSQL)


Unable to send WHERE parameters to subquery I have the following problem (in PostgreSQL in combination with PostGIS):

I have the following nice query who clusters multiple records. Although it uses PostGIS function, my problem is not PostGIS related, so bear with me ;)

SELECT count(*), ST_Centroid(ST_Collect("geom")) AS "geom"
FROM (
    SELECT kmeans(ARRAY[ST_X("geom"), ST_Y("geom")], 5) OVER (), "geom"
    FROM "table"
    WHERE "geom" && ST_GeomFromText("Bounding box") --It's this line
) AS ksub
GROUP BY kmeans
ORDER BY kmeans;

Where "Bounding box" is a polygon.

This gives me a result. So far so good.

I use Geoserver which retrieves the data from the database based on a bounding box. It does so by requesting all the records using the following query:

SELECT "count", "geom" FROM "table" WHERE "geom" && ST_GeomFromText("Bounding Box");

Where "Bounding Box" is again a polygon.

I'm not able to change the structure of this query.
The reason I need Geoserver is because it will convert the results to a different format (GeoJSON).

Is it possible to write a view that sends the WHERE parameters (or the value "Bounding box" to the subquery? It's not possible to leave the WHERE statement outside of the subquery because the subquery has to calculate on the bounding box.
If you put the WHERE statement outside of the subquery, the subquery will calculate the clusters for all the data and then the WHERE statement will limit it to the bounding box. This is not what I want.
Or perhaps somebody knows how to rewrite the query so the WHERE statement from Geoserver will succeed?

I'm not able to change the structure of this query. The reason I need Geoserver is because it will convert the results to a different format (GeoJSON).

PostGIS supports converting results to a different format. In your case, you want,

ST_AsGeoJSON

Moreover, it supports a ton of other Geometry Exports (current as of 2.5) too,

ST_AsBinary — Return the Well-Known Binary (WKB) representation of the geometry/geography without SRID meta data.
ST_AsEncodedPolyline — Returns an Encoded Polyline from a LineString geometry.
ST_AsEWKB — Return the Well-Known Binary (WKB) representation of the geometry with SRID meta data.
ST_AsEWKT — Return the Well-Known Text (WKT) representation of the geometry with SRID meta data.
ST_AsGML — Return the geometry as a GML version 2 or 3 element.
ST_AsHEXEWKB — Returns a Geometry in HEXEWKB format (as text) using either little-endian (NDR) or big-endian (XDR) encoding.
ST_AsKML — Return the geometry as a KML element. Several variants. Default version=2, default maxdecimaldigits=15
ST_AsLatLonText — Return the Degrees, Minutes, Seconds representation of the given point.
ST_AsSVG — Returns a Geometry in SVG path data given a geometry or geography object.
ST_AsText — Return the Well-Known Text (WKT) representation of the geometry/geography without SRID metadata.
ST_AsTWKB — Returns the geometry as TWKB, aka "Tiny Well-Known Binary"
ST_AsX3D — Returns a Geometry in X3D xml node element format: ISO-IEC-19776-1.2-X3DEncodings-XML
ST_GeoHash — Return a GeoHash representation of the geometry.
ST_AsGeobuf — Return a Geobuf representation of a set of rows.
ST_AsMVTGeom — Transform a geometry into the coordinate space of a Mapbox Vector Tile.
ST_AsMVT — Return a Mapbox Vector Tile representation of a set of rows.


Howto create useful dacpac versioning along with SSDT deployment? It took me almost one day to go through lots of articles and blogs to realize check-in driven continuous integration with SQL Server Database Projects (SSDT) using TFS and msbuild. Now once this is working properly, I would like to introduce versioning.

The database project's dacpac properties allow us to fill in a version in the format "x.x.x.x" that is getting published with the dacpac to Sql Server and can be queried there using:

select * from msdb.dbo.sysdac_instances_internal

This is nice but I wonder how to create something more useful and practical than a manually editable text for the version number? Of course the version number will never change as long it's up to developers to adjust it in the database project's properties...

Remembering how assembly version for example in c# project works, there is some magic increment possible by defining something like

  "1.2.."

including the build number. How can this be done?

We finally solved this by adding the build numbers in short and long format to

a) The target database as user-defined extended properties

b) A versioning table


In MDX what is the ParallelPeriod of UnknownMember? Lets say I have a time dimension with the levels all, year, quarter, month, day. I have some sales per month and a calculated measere [sales last year]

WITH MEMBER [Measures].[sales last year] AS (ParallelPeriod([Time].[Time].[Year],1,[Time].[Time].CurrentMember), [Measures].[sales])
SELECT {[Measures].[sales], [Measures].[sales last year]} ON 0
, [Time].[Time].[Month].Members ON 1
FROM [Sales]

Now the time dimension has a visible UnknownMember and it shows as [sales last year] the same value I see for january of my last year in the hierarchy.

Why is that so?
How can I avoid it? I mean the sum of all [sales last year] should be the same as the sum of all [sales] over all years except the first.

The way `PARALLELPERIOD' works is so that it moves up the hierarchy up to the specified level and then moves back or forward the number of periods, then moves down again using the same path.

In this 'moving down the path' parallelperiod will descend the same member ordinal again. So if it moved up from the first member (in your example january) it will move down the first member again. If it moved up the second member (in your example february) it will move down the second member again.

This of course breaks with an unknown hierarchy, since each child is the first member of it's parent.

So lets say for example you have this hierarchy

     2015             unknown
jan   feb    mar      unknown

And then you do a parallelperiod on the leaf member of the unknown hierarchy, the parallelperiod will move up to the year level of the unknown hierarchy, move back a year and move down to the leaf level again, remembering it moved up from the first member of the unknown hierarchy and moving down the first member of the 2015 hierarchy, which is jan 2015.


Single query for searching for records without knowing what the search column is for I have a single line search where i should be able to search for address or city or zip or state. I should be able to show data of what ever that matches for the typed in word. Suppose user had typed in new york then I have to write an sql query  which should give  the output like

  SearchResult new York(coming from city)

  123 main street New York (coming from address)

  .

  . .

If i type in 58986

  SearchResult

  123 main street houston 58986  (coming from address data)

  58986  (coming from zip column data)

  .

  .

I have tried writing like this

select sd_name,zip from mls_data where sd_name like '%new%' or zip like 'new%'

but its not working

table mls_data

mls   address                   city            zip
1   56 Main Street, New York    New York        123456
2   10178 Berlin, Germany       Berlin          566996
3   5th Street, USA             Washington      878788
4   3rd Street, Washington      Washington      589345

input parameter for query

washington

result

mls     alisasName
4   3rd Street, Washington
4   Washington
3   Washington

If you want to know which column matched the parameter your best bet is probably to use a union query such as the following.

set @param = '%new%';

select address, city, zip, 'address' as matched_on
from mls_data
where address like @param
union all
select address, city, zip, 'city' as matched_on
from mls_data
where city like @param
union all
select address, city, zip, 'zip' as matched_on
from mls_data
where zip like @param;

This will return multiple rows if more than one column matches (e.g. both the city and the address contain "new") indicating that multiple columns matched.

Example results: http://sqlfiddle.com/#!9/b814a/4

Update

After clarification of the question in the comments, I think this query may be what you're looking for:

declare @param varchar(max) = '%new%'

select mls, cast(address as varchar(max)) as [value]
from mls_data
where address like @param
union all
select mls, cast(city as varchar(max))
from mls_data
where city like @param
union all
select mls, cast(zip as varchar(max))
from mls_data
where zip like @param;

This returns two columns, the row ID and the matching value, regardless of which column the value comes from.

Example: http://sqlfiddle.com/#!3/ea1c9/5


ENUM and ID as a Foreign Key Using PostgreSQL 11.

I've tried digging through both Stack Overflow and here and was unable to find an answer on best practices.

I'm working on a database design and have arrived at a schema that uses a generic "join table". This join table contains five columns:

CREATE TABLE many_joins_table (
  id PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  object_id int NOT NULL,
  object_table joins_object_t NOT NULL,
  parent_id int NOT NULL,
  parent_table joins_parent_t NOT NULL);

I've been using this table to represent adjacent and many-to-many relationships between objects in my database. One such example is tags.

CREATE TABLE tag (
  id PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  name text NOT NULL UNIQUE);

CREATE TABLE comment (
  id PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY);

CREATE TYPE joins_object_t AS ENUM ('tag');
CREATE TYPE joins_parent_t AS ENUM ('comment');

When a tag is added to the comment table, I would insert a new row into this join table with the following fields:

INSERT INTO many_joins_table
VALUES(1, 'tag'::joins_object_t, 1, 'comment'::joins_parent_t);

Besides inflexibility of enums, addressed with PostgreSQL 9.1 https://stackoverflow.com/questions/1771543/adding-a-new-value-to-an-existing-enum-type/7834949#7834949.

Are there any significant disadvantages or advantages of such an approach? I'm worried that I've mistakenly implemented an anti-pattern. Are there any best practices I can apply to improve this implementation (indexing or constraints)?

Thanks!

Note: I'm aware there are better ways to implementing tags, namely using intarrays. I'm just using tags as an example since it's easily understood. https://stackoverflow.com/questions/23508551/integer-array-lookup-using-postgres

Edit: Removed the UUIDs since it may be a distraction to the question.

First, stay off enums for things like this. Enum values can never be removed, so use them only if you are sure that that will never be necessary, which doesn't seem the case here.

Anyway, I would say that your design is too complicated, and still lacks the crucial feature of referential integrity.

Use a junction table for each pair of objects that can be related. This way, you

make clear which objects can be related
can have referential integrity

Having many tables is something that a database is good at. If you contend that you have 1000 tables and each object can be related to each other, that would be too many tables. But in that case you should probably go for a model where you have not a table per object type anyway.


SQL Anywhere 12 - exporting data to Excel Do I need to install MS Office Excel on machine where is SQL Anywhere 12 server for below code to work?

Or SA 12 or Windows have MS Excel Driver builtin?

SELECT * FROM SalesOrders;
OUTPUT USING 'Driver=Microsoft Excel Driver (*.xls);
DBQ=c:\\test\\sales.xls;
READONLY=0' INTO "newSalesData";

The OUTPUT USING format of the statement is using a ODBC driver to create/send the data to.

This means you must have a ODBC driver on the server which can create XLS files.

There is a MS Office 2007 ODBC download available from microsoft which provides such a driver.

I don't know if there exist 64-Bit drivers (I think it must match with your dbserver 32/64bit) nor if Office 2010/2013/2016 ODBC drivers exist.

Docu output-statement


Mirroring Database tables and views on SQL Server Express I need some idea on how to mirror all table and views in my development server to my demo server.

Both servers are running SQL Server Express.

I have tried it manually but it's very time consuming.
I can try to replace the demo server virtual machine but that means deleting all data.

This is not a question of replication, nor mirroring. Is about updating the app.

You should have a deployment strategy for your app, including upgrade. Updating the 'demo' would simply mean running the normal deployment of you app. Read Version Control and your Database. Study Rails Migrations, they're a great example of deployment control. Read about EF Code First. Stop updating the DB directly. think in term of source files.


Cant get rid of filesort in simple query I have two tables. One for texts and one to assign those texts to categories. I want to select texts of one specific category and order them by last change date. So, I am using this query:

SELECT
     t2.text_id
FROM
    texts_to_categories AS t1,
    texts AS t2
WHERE
    t1.category_id = 123
    AND t2.text_id = t1.text_id
ORDER BY
    t2.time_changed DESC
LIMIT 10

I tried a lot of index combinations but I keep getting a temporary and filesort:

id  select_type table   type    possible_keys                   key         key_len ref         rows    extra
1   SIMPLE      t1      ref     text_id,text_id_2,category_id   category_id 5       const       203     Using where; Using temporary; Using filesort
1   SIMPLE      t2      eq_ref  PRIMARY,text_id                 PRIMARY     4       text_id     1       Using where

Edit: Here is a CREATE TABLE for the two tables:

CREATE TABLE `texts` (
`text_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`time_changed` datetime DEFAULT NULL,
PRIMARY KEY (`text_id`),
KEY `time_changed` (`time_changed`)
) ENGINE=InnoDB AUTO_INCREMENT=132207 DEFAULT CHARSET=utf8

CREATE TABLE `texts_to_categories` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`text_id` int(11) DEFAULT NULL,
`category_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `text_id` (`text_id`,`category_id`),
KEY `text_id_2` (`text_id`),
KEY `category_id` (`category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=740789 DEFAULT CHARSET=utf8

You can try to add an index on time_changed:

alter table texts add index (time_changed);

this could get rid of Using temporary and filesort. But you need to define indexes for your query because the query plan show Using where instead of more healthy Using index.

Updated

Maybe the main culprit for the temporary/filesort is the order of the join. It is not good practice to force the optimizer without a good reason, but you could try to force the join order to test if the temporary/filesort goes away. I have swapped the tables and added a straight_join clause:

SELECT straight_join
     t2.text_id
FROM
    texts AS t2,
    texts_to_categories AS t1
WHERE
    t1.category_id = 123
    AND t2.text_id = t1.text_id
ORDER BY
    t2.time_changed DESC
LIMIT 10;

Let me know what is the query plan of the previous query.

I don't think that temporary/filesort is a big deal for few hundred rows. I would let the optimizer do its job. I would be more worried to get a better tables schema. If it is feasible for you, I suggest the following changes to your schema.

For texts_categories your table permits NULL value for text_id and for category_id. So I would delete NULL values:

delete from texts_to_categories  where category_id is NULL or text_id is NULL;

alter table texts_to_categories
change category_id category_id int unsigned not null,
change text_id text_id int unsigned not null;

Next if you don't need texts_categories.id column, I would get rid of it, and I would reorganize indexes:

alter table texts_to_categories
drop column id,
add primary key category_text (category_id, text_id),
drop index text_id,
drop index category_id,
add index text_id;

For table texts I will try time_changed column to do not permit null values:

update texts_bck
set time_changed = '2010-01-01' -- some default
where time_changed is NULL;

alter table texts
change column time_changed time_changed datetime not null
default current_timestamp;

I hope this is of help.


How can I prove that the following T-SQL is bad for performance? I have recently inherited a codebase with a large amount of Stored Procedures.  The system they are supporting is encountering numerous performance problems which I am looking in to.

A number of the Stored Procedures have a pattern like this:

Create Temp table
Build up dynamic SQL query to insert a bunch of records, e.g.

DECLARE @sql VARCHAR(MAX)
SET @sql = 'INSERT INTO @tempTable
SELECT SomeColumn, SomeColumn2, SomeColumn3, etc FROM MyTable'

IF @someParam = [SomeValue]
    SET @sql = @sql + 'WHERE SomeColumn = [SomeValue]';

IF @someOtherParam = [SomeOtherValue]
    SET @sql = @sql + 'WHERE SomeOtherColum = [SomeOtherValue]';

Execute this dynamic sql

EXEC(@sql);

Select from the temp table and bring in a bunch of additional information to return to the client.

SELECT

   ...

FROM

   @tempTable
   INNER JOIN ...

My immediate thoughts are:

There is dynamic SQL, so no cache plans, meaning plans generated every time.
There is an INSERT SELECT pattern, so table locking is more likely to be an issue.

I have re-written some of the Stored Procedures in this way instead:

    SELECT

       ...

    FROM

        MyTable
        INNER JOIN ...

    WHERE

    (
        @someParam != SomeValue
        OR
        SomeColumn = SomeValue
    )
    AND
    (
        @someOtherParam != SomeOtherValue
        OR
        SomeOtherColumn = SomeOtherValue
    )

From comparing execution plans and client statistics in SQL Management Studio, I have not sped the Stored Procedures up so I am apprehensive about suggesting wholesale re-writes of all Stored Procedures.

I am trying to set up some profiling of a live customer scenario, but as yet have been unable to prove my thoughts.

Can anyone offer any confirmation of the theory behind my thoughts, or any better ways of proving my suspicions?

The problem is I have read that dynamic SQL is not always a closed case - i.e. it depends on how it is used.  My understanding of locking also falls down at the fact that nowhere can I get 100% confirmation of how this type of INSERT SELECT will lock tables.

There is dynamic SQL, so no cache plans, meaning plans generated every time

Not necessarily true. Dynamic SQL can (and does) use cached plans just as well as static SQL. For dynamic search conditions resolving to dynamic SQL is oft the right answer. See Dynamic Search Conditions in T-SQL for more details.

  There is an INSERT SELECT pattern, so table locking is more likely to be an issue.

Not necessarily true, specially with a @tempTable

  have re-written some of the Stored Procedures in this way instead

Using multiple OR conditions like that is an anti-pattern. You are forcing the query optimizer to come up with a plan that works for any value of all those parameters. Usually the only solution is a scan, ignoring any index. The original code was better.

  Can anyone offer ... any better ways of proving my suspicions?

Yes. Measure. Use a methodology like Waits and Queues. Don't relly on your intuition. Find the bottlenecks and address them accordingly.


Database Restoration Issue Msg 3156, Level 16, State 6 Recently while restoring a database with memory optimized filegroup I start facing an issue with below error message:-

  Msg 3156, Level 16, State 3, Line 2 File 'XYZ' cannot be restored to
  'D:\FolderPath\Memory_Optimized_FolderName'. Use WITH MOVE to identify
  a valid location for the file.

First I thought it might be syntax error but syntax was correct and I even checked the permission to filepath which was also fine. So what is the reason for this error?

I found the solution of my problem which was folder location. The folder gets created itself. So once I removed the last folder name which was in some format

  "TableName_Memory_Optimized"

And using only below path in my restoration script it starts working fine.

  'D:\FolderPath'


Full to Simple recovery mode during massive data update? I`ve got a 50 Gb database, in which a table takes up 43 GB, and it's full of bad data(over 90%), and i plan to clean it. Database is in Full recovery mode.

My plan is:

0 - take a backup for safety reasons

1 - put database in simple recovery mode

2 - remove 90% of bad data from that table (use batches so that log file doesn't grow)

3 - shrink the data file to an acceptable size, so estimated data for next 1-2 years wont cause any file growth (ofc i will need to rebuild all indexes)

4 - put database back Full recovery mode

5 - take a full backup of the database, so the log backup chain is restored from this point on

Now, my question is a bit strange, but, am I doing anything wrong ? especially by switching database from full -> simple, then simple -> full ? Am I missing anything important from this plan ? Is this the best approach to my problem ?

I hope i gave all needed details, and my questions are not too vague.
Thanks.

The only thing you're really changing by using simple recovery is you're shifting the time when you have to perform additional backup work. Instead of taking log backups throughout the process (which shouldn't be extra work, because you should already be doing that!), now you have to take a full backup at the end of the process. In the meantime, you've opened yourself up to real problems because should anything go wrong between the start of the operation and the completion of the full backup, you are going to be in some hot water because you've lost the ability to recover to a point in time.

(Since the bulk of your activity is delete, you can't even benefit from minimal logging - and even if you could, Thomas' advice to use bulk-logged is a better approach than switching to simple - this still allows you to recover to a point in time that has been backed up by the log and is not inside a minimally-logged transaction.)

As you're planning to delete 90% of your data, you may want to think about this in a different way: move the data you want to keep to a new table, then drop the old table and rename the new one. The stuff that costs a lot of log is when there is a lot of data movement. DROP is logged too, but unlike DELETE, only the deallocation, no movement. You'll need a little extra space to support this (~10%) but you'll also avoid any after-the-fact rebuild.

All of these options involve trade-offs, but I think your plan to change recovery to simple offers the least benefit and introduces you to the most risk. I wouldn't do it without having your resume dusted off.


How do I identify the natural key of a table? In my mind, a natural key is like a primary key and thus should identify a unique row in the table.

The specific situation that I'm working on is that a bunch of duplicate data has been inserted into our tables. This is because the tables have no primary keys. I'm now need to know how to systematically identify duplicate records as well as a natural key to create a unique constraint.

Any info is appreciated!

You are right - a natural key is indeed a combination of one or more columns that uniquely identifies one row in a table.

Natural keys are formed from the business attributes we identify for an entity type. They form part of the vocabulary the users would employ while discussing these entities. They arise "naturally" in discussion: hence the name. Any combination of such attributes that respect the rules of normalisation can form a natural key. Sometimes there can be more than one combination that works. We refer to these as alternate keys. For example, a house could be identified by number, street and town or by latitude and longitude or by postcode and number. There are three different combinations of values that are commonly used by the business, each of which could uniquely identify exactly one house. There are three alternate natural keys.

For your problem you must identify a combination of columns which could uniquely identify a row and distinguish it from the other rows in the table. It will be a combination which will allow you to identify duplicates, as @sp_BlitzErik pointed out. In other words, you can say this row is a duplicate of that row because these columns have the same value in both.

Be careful you choose a combination that must be true because of the definition of the table and the type of data it holds, and not just happens to be true because of the values that happen to be in the table at this particular point in time.

Natural keys stand in contrast to surrogate keys. Surrogate keys are a thing that we invent when implementing a logical data model in physical database management software to account for real-world limitations and peculiarities. Surrogate keys are, as the name suggests, a surrogate for the natural key. They stand in for the natural key and perform its function. Commonly we do this because the natural key is long and contains strings which the DBMS does not process efficiently. The problem is, because we invent them, we can invent values for them and thus enforce uniqueness where the natural key would say there is duplication.

Ideally surrogate keys should only ever be hidden inside our software and never be exposed to the users (though they often are).


Should I declare one or two tables to represent Items and Sub Items? I'm facing a dilemma in my database design for an upholstery store.

This store cleans carpets, pillows, car seats, car trunks, couches.
There are chairs and sofas with fixed and removable seat cushions. Car seats and trunks with different sizes. The same occurs with pillows.

From my point of view all of these things are items. My idea is to create a table like this:

CREATE TABLE Item (
    ItemID …
    Name …
    Description …
    Material … -- (like 'cotton', 'wool', 'rayon', 'silk')
    Length …
    Height …
    Width …
);

The items mentioned earlier have sub items. A car has seats and trunk. A chair can have a fixed or a removable seat, a couch can have pillows and so on.

I've read these posts:

Sub and sub sub items organization
What is Parent/Child relationship
Parent/Child relationship best practice
Items with sub items

After reading them, I have some questions:

Is it a good idea to include another column called subItem which references a parent item from the same table? Doing that it hurts performance?
Is this a case of a parent/child table?

I'm wondering what approach fits simpler for maintenance and performance.

This looks like a straightforward one-to-many relationship, which as you suggested you can model by setting a foreign key in the child items table referring to the parent items table. As for performance, you won't have any kind of performance hit at all for a small site. Modelling your schema properly is your priority.

Consider renaming the tables too. For example, if the shop offers a cleaning service then you could think of the tables in terms of Services (e.g. car) and Costs (seats, trunk, etc)


set SPN on SQL cluster I have a question about how to manually set SPN for using Kerberos authentication on a SQL cluster.

Do I set one SPN on the clustername or one on each node?

I also have a default named instance, do i specify the name of the instance?

To create a SPN for a SQL FCI, use the FQDN of the FCI instance. For example, if the FCI name is "SQLFCI1" on the contoso domain and it listens on port 22000 with domain account SQLSvcAcct then the spn would be: setspn -s MSSQLSvc/SQLFCI1.contoso.com:22000 Contoso\SQLSvcAcct

If you don't want to deal with doing this by hand there is a great tool provided by Microsoft for this.


Exporting and importing a database I have setup an Oracle 11g Database and have a question about exporting and importing a database.

I am using Enterprise Manager and have browsed to the following: Data Movement->Export to Export Files. Down the bottom of the screen, I need to supply Host Credentials. When trying to use the username SYS, I see that the error message says to logout and login using a different role.

What role should I use? Should I create a specific role for this task, and if so, what priviledges should the role have?

The link below is from oracledistilled.com and goes through Enterprise Manager >> Data Movement.

Clone a Database to Another Host using Enterprise Manager 11gR1 Grid Control

It is very well explained with good screen shots and walks through Cloning i.e. another function under 'Data Movement' >> 'Export Export to File'.

Under host credentials the normal user is the O/S Oracle user, Alex Poole mentioned in a comment:

  Host credentials are for an operating system account (e.g. Oracle). SYS is a database account and has to be connected AS SYSDBA (not as 'normal' in the drop-down role when you log in to EM).


Phantom Table in MySQL Programmer here, be gentle.

I'm working in a MySQL database with only a single table, as shown by show tables:

mysql> show tables;
+----------------------+
| Tables_in_widget_web |
+----------------------+
| widget_signups       |
+----------------------+
1 row in set (0.00 sec)

At some point earlier in development there might have been other tables as well, but for what we were doing we decided that we were probably over-architecting and nixed them.

I noticed the other day that auto-complete within mysql was not working properly in this database. When I am using the particular database and I type SELECT * FROM widget<TAB> I wasn't getting my good old auto-complete that I was used to. Double tabbing to see possible auto-completions yields:

mysql> SELECT * FROM widget<TAB><TAB>
widget_date
widget_signups
[... columns on widget_signups ...]

Yet when I try to DROP this mysterious and previously deleted widget_date table, I predictably get errors saying the table doesn't exist.

My question is this: how can I repair my beloved mysql auto-complete by removing this phantom table? I would assume (wrongly?) that auto-complete suggestions within the mysql command line are drawn from a table within mysql. Where can I go to zap this safely - or do I just need to man up and get used to typing the entire table name?

This is happening because the auto-complete isn't context aware & it's offering you a column name of widget_date as well as the table name you're expecting.


Compressing Base64 Encoded Image in Database Field. Table Size too Large Database Info

Database Type - Percona MySQL.
Table engine - InnoDB.

I have a database table that is currently using 17GB at 400,000 rows. This size is mainly because I'm storing a 40kb base64 encoded image in each row. I'm wondering if anyone else has ran into the same type of problem and what they decided to do about it. The options I'm currently thinking about are:

Method 1 Storing each image named by the row's unique id in some sort of cloud hosting server. This would fix my database size problem, but it would be a pain to manage because the images would be on a different server. Also pushing the images up to the cloud server would take longer than the current method.
Method 2 Compressing just that column on a mysql database level? (if that's possible? I'm not sure but I believe it is, still researching. I would assume this would slow down the insert and select statements also though.)
Method 3 Compress the base64 before sending it over to the database with something like LZString Compression. (I'm sure this would entail on my database level such as what field type to put this data in or character set/encoding would be needed. Possibly could combine method 2 and 3 together for maximum savings? not sure if that would work or if it would be possible)

If anyone could give me some information on what would be the best way for me to approach this dilemma or if anyone has run into this problem before I would really appreciate the any help/info.

Thanks in advance.

Base64 is an encoding for transferring data reliably over the wire. It usually increases a files size so I wouldn't look to store a file in this format.

BLOB
The first thing I would do is decode the Base64 back to the original file and store it as a BLOB field. This could shrink the files by a third.

Table design
You said each row has a 40kb image. Why are they all the same size? If some rows share the same image then you could put the images in a table of their own and reference them from your big table.

Limit filesize
Most sites that let you upload an avatar limit the filesize/resolution of the image. Without knowing what these images are for perhaps you could impose a similar limit. You could even rescale the image automatically when they upload it.

Sorry if these are already obvious to you. It's difficult to be more specific without knowing the background about what the images are for.


How is it possible to model in a database many different products? For example, a smartphone might have attributes such as RAM and camera resolution, whereas a fridge has way different attributes. Is there a way to store these data in a relational database?

You probably have some columns that are common to almost all products:  Price, SKU, Manufacturer, etc.  Those may as well be columns.  The rest can be put into a JSON string.  More discussion:  http://mysql.rjweb.org/doc.php/eav


Repeat an action for X number of PostgreSQL tables Note: The solution does not need be optimised for processing speed. It's supposed to be part of a setup phase and won't be run more than once.

General problem

I have X number of tables in a PostgreSQL database with very different names. I can neither be bothered to write the same PostgreSQL commands for each of them, nor can I be sure that the next set of data that I import has the exact same set of tables. Yet I would like to repeat the same action on each of them. How would I go about doing this? Is it even possible?

I will accept an answer that can do this much. However, an answer that does this and also allows me to exclude some tables that I do know the names of would be even better.

Specifics

This question is closely related to another question which details what I want to do with the tables, but both specific and more general answers are appreciated.

Findings

While looking for a solution I found this question, but I don't know if and how I could use those answers. I'm including it here in hopes that someone else might know how to apply them to my question.

Thanks to the hint given in the comments by @a_horse_with_no_name I've found the answer in the documentation for the DO command: https://www.postgresql.org/docs/9.6/static/sql-do.html

CREATE TABLE new_table ();
DO $$DECLARE r record;
BEGIN
    FOR r IN SELECT table_name FROM information_schema.tables
         WHERE table_schema = 'public' AND table_type = 'BASE TABLE' AND table_name <> ALL (string_to_array('merge_table,spatial_ref_sys', ','))        LOOP
        -- Stuff that is specific to what I want to do
        EXECUTE 'CREATE TEMP TABLE temp_table AS SELECT * FROM ' || quote_ident(r.table_name) || ' NATURAL FULL OUTER JOIN new_table';
        EXECUTE 'DROP TABLE new_table CASCADE';
        EXECUTE 'CREATE TABLE new_table AS SELECT * FROM temp_table';
        EXECUTE 'DROP TABLE temp_table CASCADE';
        EXECUTE 'DROP TABLE ' || quote_ident(r.table_name) || ' CASCADE';
        -- End of the specific stuff
    END LOOP;
END$$;

Thanks to @a_horse_with_no_name for the suggestion on simplifying the exclusion of specific tables.

EDIT:

I found an even better way to exclude specific tables which at least works for my purposes. What I do is that I run the following query before I import any of the tables that I wish to perform the repeated action on:

DO $$BEGIN
    DROP TABLE IF EXISTS tables;
    CREATE TABLE tables (table_name text);
    INSERT INTO tables (
        SELECT table_name
         FROM information_schema.tables
         WHERE table_schema = 'public'
          AND table_type = 'BASE TABLE'
        );
END$$;

This creates a table containing the names of all the tables (including itself). More tables can of course be added manually with INSERT INTO tables VALUES ('new_table');.
Then in the WHERE clause of the FOR loop, instead of doing table_name <> ALL (string_to_array('merge_table,spatial_ref_sys', ',')) I do table_name NOT IN (SELECT table_name FROM tables). This is much easier to maintain than changing the string whenever new tables to be excluded are introduced.


Remove while loop from query inserting x values I want to make this query set based and remove the while loop for inserting.

I am passing in a TVP to the stored procedure with the type and quantity required. Because TVP needs to be READONLY, I am copying to a temp table to do the delete in the while loop. I have control over the way the data is passed in so could change the TVP if required.

CREATE TYPE WidgetsUDT AS TABLE(
    [WidgetTypeId] [int] NOT NULL,
    [Required] [int] NOT NULL
)
GO
CREATE TABLE WidgetOrder (Id int, WidgetTypeId int, OrderId int)
GO
CREATE PROCEDURE [BuyWidgets]
@Widgets [WidgetsUDT] READONLY
AS
BEGIN
SET NOCOUNT ON
DECLARE @WidgetTypeId int, @Required int
CREATE TABLE #TempWidgets (WidgetTypeId int, Required int)
INSERT INTO #TempWidgets SELECT * FROM @Widgets

WHILE EXISTS(SELECT * FROM #TempWidgets)
BEGIN
SELECT TOP 1 @Required = Required, @WidgetTypeId = WidgetTypeId FROM         #TempWidgets

;WITH CTE AS
(
SELECT TOP (@Required) *
FROM WidgetOrder
 WHERE OrderId IS NULL AND
 WidgetTypeId = @WidgetTypeId
)
UPDATE CTE SET OrderId = 100

DELETE FROM #TempWidgets WHERE WidgetTypeId = @WidgetTypeId
END

DROP TABLE #TempWidgets

    SELECT * FROM WidgetOrder
END
GO

INSERT WidgetOrder (Id, WidgetTypeId, OrderId) VALUES (1,1,NULL)
INSERT WidgetOrder (Id, WidgetTypeId, OrderId) VALUES (2,1,NULL)
INSERT WidgetOrder (Id, WidgetTypeId, OrderId) VALUES (3,1,1)
INSERT WidgetOrder (Id, WidgetTypeId, OrderId) VALUES (4,2,NULL)
INSERT WidgetOrder (Id, WidgetTypeId, OrderId) VALUES (5,2,NULL)
INSERT WidgetOrder (Id, WidgetTypeId, OrderId) VALUES (6,2,NULL)
INSERT WidgetOrder (Id, WidgetTypeId, OrderId) VALUES (7,2,NULL)
INSERT WidgetOrder (Id, WidgetTypeId, OrderId) VALUES (8,2,NULL)
INSERT WidgetOrder (Id, WidgetTypeId, OrderId) VALUES (9,2,2)
INSERT WidgetOrder (Id, WidgetTypeId, OrderId) VALUES (10,3,NULL)
INSERT WidgetOrder (Id, WidgetTypeId, OrderId) VALUES (11,3,NULL)
INSERT WidgetOrder (Id, WidgetTypeId, OrderId) VALUES (12,3,NULL)
INSERT WidgetOrder (Id, WidgetTypeId, OrderId) VALUES (13,3,3)
INSERT WidgetOrder (Id, WidgetTypeId, OrderId) VALUES (14,3,3)
INSERT WidgetOrder (Id, WidgetTypeId, OrderId) VALUES (15,3,3)

DECLARE @MyWidgets WidgetsUDT
INSERT @MyWidgets (WidgetTypeId, Required) VALUES (1,2)
INSERT @MyWidgets (WidgetTypeId, Required) VALUES (2,3)
INSERT @MyWidgets (WidgetTypeId, Required) VALUES (3,1)
EXEC BuyWidgets @MyWidgets

DROP TABLE WidgetOrder
DROP PROCEDURE [BuyWidgets]
GO
DROP TYPE WidgetsUDT
GO

I believe I have solved it with the following cte and cross apply. Thanks to Aaron for letting me think it was simpler than I had it.

;WITH cte AS
(
SELECT
    D.Id, D.WidgetTypeId
FROM
    @Widgets R
    CROSS APPLY
    (
        SELECT
            TOP(R.Required) *
        FROM
            WidgetOrder A
        WHERE
        A.OrderId IS NULL AND A.WidgetTypeId = r.WidgetTypeId) D
)
UPDATE A SET OrderId = 100 FROM WidgetOrder A JOIN cte ON A.Id = cte.Id


Cannot Deploy SSIS Package in SQL Server Data Tools 2015 (Destination server name doesn't allow password entry) I'm extremely frustrated.  I'm using SQL Server Data Tools 2015.  I have an Integration Services project and I want to deploy a single package of this solution to SQL Server 2012.  I've set the solution to Deployment Target Server Version to SQL 2012.  I right click on the package and select Deploy, however at the Select Destination part of the wizard, it only allows me to select a server name (and not enter any credentials).  However, I need to enter a specific username and password as my domain account is not a valid login.

I understand that that wizard will eventually run isdeploymentwizard.exe and I looked at the parameters for it as well and do not see a way to enter any connection information outside of a server name.

How can I deploy this package to the server using a username and password instead of a trusted account?  I feel like I must be missing something incredibly stupid, because I was unable to find someone with a similar issue even after searching Google for an hour.  Apparently everyone deploying to SQL Server from SSDT2015 is using a trusted account.

Edit: In older versions of BIDS, you could open the project and select File > Save Copy of .dtsx as then save it to the SQL server and use credentials.  However in SSDT, the Save Copy of option only allows you to save the package locally.

You have a lot going on here.

Package vs Project deployment model

Starting with the 2012 release, SSIS projects have the choice between a Package Deployment Model and a Project Deployment Model. Project allows you to create project level artifacts - shared connection managers, project (and package) parameters. The default deployment model for new projects is Project Deployment Model.

The deployable unit of code from a project deployment model is the .ispac file. Your SSIS packages + project parameters + shared connection managers + manifest file get zipped up into a file named MyProject.ispac. The ispac file is deployed to a SQL Server catalog called SSISDB.

The deployable unit of code from package deployment model is the SSIS package itself. You can deploy the .dtsx file to a File System, the SSIS Package Store (also just the file system) or a SQL Server instance. However, the SSIS package will be stored in the MSDB (sysdtspackages90/sysssispackages)

The 2016 release complicates the above matrix in that we can now deploy .dtsx package(s) into the SSISDB. This is to address the incremental deployment story. Behind the scenes, it will generate an ispac and that's what's deployed. But that's only for a 2016 server which won't help your 2012 story.

SSIS Versions

SSIS packages have versions that correspond to the version of SQL Server and tooling you're working with (2005/2008/2008 R2/2012/2014/2016/v.Next). Packages are only* forward compatible so if you make the mistake of opening a 2005 package with the 2014 tooling (or deploy with it) to a 2005 instance guess what, you've upgraded to the current version and deployed to a location that doesn't speak that dialect.

2016 confuses this issue because the tooling now supports targeting (Project -> properties, Configuration Properties -> General: TargetServerVersion)

Deployment

Ispacs are deployed via isdeploymentwizard.exe or you can do it in TSQL.

-- You must be in SQLCMD mode
-- Otherwise, modify the value of $(isPacPath) down below
:setvar isPacPath "C:\sandbox\SSDTDeploy\TSQLDeploy\bin\Development\TSQLDeploy.ispac"

DECLARE
    @folder_name nvarchar(128) = 'TSQLDeploy'
,   @folder_id bigint = NULL
,   @project_name nvarchar(128) = 'TSQLDeploy'
,   @project_stream varbinary(max)
,   @operation_id bigint = NULL;

-- Read the zip (ispac) data in from the source file
SELECT
    @project_stream = T.stream
FROM
(
    SELECT
        *
    FROM
        OPENROWSET(BULK N'$(isPacPath)', SINGLE_BLOB ) AS B
) AS T (stream);

-- Test for catalog existences
IF NOT EXISTS
(
    SELECT
        CF.name
    FROM
        catalog.folders AS CF
    WHERE
        CF.name = @folder_name
)
BEGIN
    -- Create the folder for our project
    EXECUTE [catalog].[create_folder]
        @folder_name
    ,   @folder_id OUTPUT;
END

-- Actually deploy the project
EXECUTE [catalog].[deploy_project]
    @folder_name
,   @project_name
,   @project_stream
,   @operation_id OUTPUT;

That sounds like what you want because you can be a local SQL User but if memory serves, the above functions do this impersonation jiggery that goes awry if you're not a Windows user.

This application requires one of the components..

With 2005/2008/2008 R2 you could only get SSDT, nee BIDS, with the installation media. This meant you needed Developer Edition at a minimum to build out your SSIS Packages. Starting with 2012, you could download the SSDT tooling directly from MS without having a license for SQL Server. However, you are only licensed for development purposes. The command line tools are not classified as development tooling so dtutil or dtexec will both fail a licensing check and report the above (or a similar error message).

The resolution, as you point out, is to install the Integration Services Service on the machine that requires "more" than just Integration Services development. Be aware that this does count as an installation of SQL Server from a licensing perspective so be sure the person in your organization that handles licensing is aware of machines where you have done this so you don't have an unpleasant surprise come audit time.


Is it safe, and how can I delete mysql.sock before mysqld start? I have CentOS running on virtual machine on my desktop. When my desktop unexpectedly restarts (blue screen, power failure, restart after updates), virtual machine faces the same problem - unexpected shutdown.
When VM starts again, /var/lib/mysql/=mysql.sock file (0 bytes) should be deleted, then mysqld service can be started.

Is there any way to avoid manual deletion of this file? Boot script

The only way to avoid manual deletion is relocate mysql.sock
Add this to /etc/my.cnf
[mysqld]
socket=/tmp/mysql.sock

/tmp would be a good choice since /tmp gets wiped out on each server reboot
Make sure mysqld has chmod permission to write mysql.sock to /tmp
Give it a Try !!!
UPDATE 2014-03-21 11:37 EDT
If you cannot get /tmp/mysql.sock to be made, there is a hack you can try.
Run which mysqld_safe
This will tell you where the mysqld_safe is located. When I run it I get
sh-4.1# which mysqld_safe
/usr/bin/mysqld_safe

Open the file in vi, got lines 612-620. I see this
safe_mysql_unix_port=${mysql_unix_port:-${MYSQL_UNIX_PORT:-/var/lib/mysql/mysql.sock}}
# Make sure that directory for $safe_mysql_unix_port exists
mysql_unix_port_dir=`dirname $safe_mysql_unix_port`
if [ ! -d $mysql_unix_port_dir ]
then
  mkdir $mysql_unix_port_dir
  chown $user $mysql_unix_port_dir
  chmod 755 $mysql_unix_port_dir
fi

You could add this line before it
rm -f /var/lib/mysql/mysql.sock

This might do it for you
CAVEAT : Remember, this is a hack. Should mysql not be able to start for you, I will disavow any knowledge of your actions going forward.


Upgrading SQL Server 2008 failover cluster node to SQL Server 2012 failover cluster node Please I want someone to help me with step by step method of upgrading SQL Server 2008 with failover cluster node on two different machines to SQL Server 2012 without losing the existing settings/configurations

It's really not THAT different to Service Pack patching whereby you'll need to do passive nodes first, then give cluster resource group ownership to one of the upgraded nodes, in order to finally upgrade the active node.

Map out your upgrade sequence first and test it if you have the luxury of a dev environment.

In place upgrades are supported and the information is already out there from Microsoft.

See here for example.

You'll probably want to perform what's called a rolling update, which can be best explained by the following paragraph from the article:

  To upgrade a SQL Server failover cluster to SQL Server 2012, you must run setup with upgrade action on each failover cluster node, one at a time, starting with the passive nodes. As you upgrade each node, it is left out of the possible owners of the failover cluster. If there is an unexpected failover, the upgraded nodes do not participate in the failover until cluster resource group ownership is moved to an upgraded node by SQL Server Setup.

Honestly, the biggest headache I've had with this is with application compatibility and agreeing downtime. The process was simple for me and my nodes...


Using Percentile_Disc to compute Median/Interquartiles SQL Server Developer Edition 2014

I have a table that looks like:

+ ID + CC +  Xa  +
| 1  | 0  | 53.1 |
| 1  | 1  | 46.1 |
| 1  | 2  | 107.5|
| 2  | 0  | 36.5 |
| 2  | 1  | 41.5 |
| 2  | 2  | 61.8 |

Xa are floats in the table, but I rounded to make it less messy. I had heard of PERCENTILE_DISC from MSDN, and made a query that I thought would give me the median of Xa for each ID:

SELECT DISTINCT s.ExperimentID,
    PERCENTILE_DISC(.5) WITHIN GROUP( ORDER BY s.Xa) OVER (PARTITION BY s.ExperimentID)
FROM Simulations s

I threw in the DISTINCT ExperimentID because when I ran the query without it, it was looking to return the median of all ~150M rows for each individual row, which doesn't really make any sense. I did get results, and I think they're right, but I'm not certain. I compared the results of some individual IDs vs what I got from MEDIAN() in LibreOffice, and the results are slightly different. Any idea why?

Median functions vary in a few ways, particularly when there's an even number of items.

I've blogged about medians, Aaron Bertrand has, Paul White has... I'm in good company. :) I'd recommend you find Aaron's material, which shows better ways to do median than PERCENTILE_CONT.

PERCENTILE_DISC will only choose a value from the list. This is fine if you have an odd number of values, but what if your list is (1, 2, 3, 4)? And then, do you choose 2 or 3? It's valid, but you should define what you need.

If you go with PERCENTILE_CONT, the answer is 2.5.

But if your set is (1, 2, 3, 3) then some would argue that your answer should be weighted by the two threes.

So you should make sure that you've defined what you mean by median. :)


Howto find out details and reasons for idle transactions? I have started to use the "Idera SQL check" tool. It shows floating bubbles for each process active on the Sql Server system:

Those bubbles are surrounded by a red border in case the transaction is idle. The legend says this is dangerous:

Those are some particular sample information related to spid 200:

spid           : 200
kpid           : 0
blocked        : 0
waittime       : 0
lastwaittype   : MISCELLANEOUS
waitresource   :
dbid           : 5
database       : DBNAME
uid            : 1
cpu            : 0
physical_io    : 0
memusage       : 3
login_time     : 29.02.2016 09:37:17
last_batch     : 29.02.2016 09:37:17
ecid           : 0
open_tran      : 1
status         : SERVERNAME
program_name   : PROGRAMNAME
host_process   : 5380
cmd            : AWAITING COMMAND
nt_damain      :
nt_username    :
net_address    : 99887766
net_library    : TCP/IP
loginname      : sqlusername
stmt_start     : 0
stmt_end       : 0
input_buffer   :

It's obvious it is dangerous.
But I have trouble finding out the reason. Looking at the sys.sysprocesses DMV to find out the queries executed in the context of those spids.
The queries executed are running very fast and are no candidates for long running. So I don't understand why they are getting idle within their transactions? How to find out the backgrounds?

EDIT:
The query behind the scenes getting the processes is the following:

exec sp_executesql N'
            SELECT p.spid, p.kpid, p.blocked, CAST(p.waittime AS BIGINT) as waittime, p.lastwaittype, p.waitresource, p.dbid, db.name as [database], p.uid, CAST(p.cpu / 1000 AS BIGINT) as cpu,
            CAST(p.physical_io AS BIGINT) as physical_io, p.memusage,
            p.login_time, p.last_batch , p.ecid , CAST(p.open_tran AS BIGINT) as open_tran, p.status, p.hostname, p.program_name, p.hostprocess, p.cmd, p.nt_domain, p.nt_username, p.net_address,
            p.net_library, p.loginame, p.stmt_start, p.stmt_end
            FROM master.dbo.sysprocesses p join master.dbo.sysdatabases db on p.[dbid] = db.[dbid]
            Where hostprocess <> '''' AND program_name <> @application',N'@application nvarchar(17)',@application=N'Idera.Application'

It looks like the OPEN_TRANS indicates processes in a transaction and having a red border. And the thickness of the red border increases over time meaning the longer this process is part of the resultset, the thicker the red border gets.

This is the query I am using to identify idle transactions holding locks.

SELECT
    CAST(SERVERPROPERTY('ServerName') AS sysname) AS ServerName
    ,s.session_id
    ,s.status
    ,s.login_time
    ,s.host_name
    ,s.program_name
    ,s.host_process_id
    ,s.original_login_name
    ,s.last_request_end_time
    ,CAST(t.text AS nvarchar(4000)) AS [text]
    ,CAST(s.context_info AS varchar(128)) AS [context_info]
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_connections AS c
    ON s.session_id = c.session_id
CROSS APPLY (
    SELECT MAX(DB_NAME(dt.database_id)) AS database_name
    FROM sys.dm_tran_session_transactions AS st
    INNER JOIN sys.dm_tran_database_transactions AS dt
        ON st.transaction_id = dt.transaction_id
    WHERE is_user_transaction = 1
    GROUP BY st.session_id
    HAVING s.session_id = st.session_id
) AS trans
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS t
WHERE s.session_id NOT IN (
        SELECT session_id
        FROM sys.dm_exec_requests
    )
    AND s.session_id IN (
        SELECT request_session_id
        FROM sys.dm_tran_locks
        WHERE request_status = 'GRANT'
    )
    AND STATUS = 'sleeping'
    AND is_user_process = 1
    AND program_name NOT LIKE '%SQLAgent - Job Manager%'
OPTION (RECOMPILE, FORCE ORDER);

The reson why they are dangerous is simple: they are holding locks but doing nothing, waiting for the application to either commit or rollback the transaction.

This typically happens when the application performs some heavy task on the client side, without interacting with the database, still keeping transactions open. The worst scenario is applications showing error messages or waiting for user input during a transaction.

If you want to know what happens, you need to hunt down these applications and understand what they are doing while keeping transactions open.


Do I have to buy MySQL license? I've read in a forum that if your database capacity is more than 1G you should buy its license. Is this true? How much will it cost?

Could you post a link to the forum you're citing, I'd like to read the full context of the post.

I am not a lawyer, but legally speaking you don't need a license to use MySQL community edition. They do offer a paid-for enterprise edition. There are some additional tools you get, but I think the big thing people would use that for is support.

I've talked to some people that worked for companies that weren't allowed to use MySQL by veto from their legal dept, because they couldn't comprehend the concept of a license you don't pay for O_o.

MySQL Community Edition flat out says it's free to use and licensed under the GPL.


restore database from dmp file I have a situation:
I have a dmp file and a database with old data
server crashed and the latest data we have ar in the dmp file!
we managed to pull the server back but it has old data!
so we need to restore from the dmp!
how to import back the database from dmp file and replace all data and structure (tables schemes)...

I know it's RMAN's job but we do not have it in place, and it's urgent case!

version, OS, tool are helping things to help you solve this.

I guess OS is Windows.
version 9i.
export full.

If this is the case:
connect to the server,
set ORACLE_HOME=/where/is/oracle/home/
set PATH=%ORACLE_HOME%/bin;%PATH%
set ORACLE_SID=ORCL -> or your ORACLE_SID name

imp file=x.dmp logfile=imp_x.log full=y

imp help=y gives some clues about the commandline options.

study the errors. If there is a complaint about the dmp file, read the first few blocks. From those you can find who exported it, what type export it is and even some version info. It is a bit binary but just readable enough to get this info.

tip:

If your data has a real value for your company, hire a real dba to make a decent setup and have someone as a backup. It is easy to build a system that work happily for years and due to hardware issues suddenly become less happy. It's a pity if that costs your company the head. This is regardless for what brand of database you setup.


In which tables values will be inserted if I insert values into a view that its result is made out of inner join of 3 tables? Ι have the following view:
CREATE OR REPLACE VIEW public.my_view as
SELECT
  col1,
  col11
from
  table_a JOIN table_b on table_a.id=table_b.a_id
  join
   (
     select id,col56 from table_c where col56 > 3
   ) as filtered_c on table_a.id=filtered_c.a_id;

table_a is:
id: SERIAL PK
col1: INTEGER

table_b is:
id: SERIAL PK
col11: INTEGER
a_id FK table_a

table_c is:
id: SERIAL PK
col56 INTEGER
a_id FK table_a

As I saw upon postgresql wiki I can insert values to views that correspond to the appropriate tables. But in my case if I insert:
INSERT INTO my_view VALUES (7,88);

In which table the values will be inserted? As far as I know views are read-only tables.

A view that contains a join is not automatically updatable, that is, you cannot simply insert into such a view. The manual says (emphasis mine):

A view is automatically updatable if it satisfies all of the following conditions:

The view must have exactly one entry in its FROM list, which must be a table or another updatable view.
The view definition must not contain WITH, DISTINCT, GROUP BY, HAVING, LIMIT, or OFFSET clauses at the top level.
The view definition must not contain set operations (UNION, INTERSECT or EXCEPT) at the top level.

The view's select list must not contain any aggregates, window functions or set-returning functions.

The only way to insert into a view that does not meet these requirements is to create an INSTEAD OF INSERT trigger on it, in which case what valuees go to what tables is determined by the logic implemented in that trigger.


Why mongodb logs commands in the log file and how to avoid that? OS: Windows 10/ Server 2012

Mongodb verison : 3.4.9

I have set the mongod --logPath and --quiet command line parameter

The logs are just growing beyond 100mb and not rotating. Seeing the logs I see lot of entries example:

  I COMMAND  [conn9] command DBNAME.VERSIONS command: find {

How can I restrict mongodb to log these as I really do not care about the command

To increase the slowms value , follow the below steps:

For Exmple, If you want to increase the slowms vlaue to 500ms

db.setProfilingLevel(1, 500)

It will set the slowms to 500ms and will also turn on the profiling. So to stop the profiling, run the below command.

db.setProfilingLevel(0)

This can also be done by restarting MongoDB with the --slowms option.

Now, the commands will get logged in the log file only when the command takes more than 500ms to execute.


How to enable the Federated/FederatedX storage engine on MariaDB 5.5? I upgraded from MySQL 5.1 to MariaDB 5.3 and then to 5.5 on a Windows machine. Everything works well, except I can't enable the federated storage engine.
With MySQL 5.1 and MariaDB 5.3, I just added "federated" under [mysqld]. If I try to start MariaDB 5.5 with the same "federated" option, I get an error:

  C:\Program Files\MariaDB 5.5\bin\mysqld.exe: unknown option '--federated'

How can I enable the federated storage engine?

FederatedX is enabled by default in MariaDB 5.5


Insert (write) performance We are trying to speed up the queries in our application and in the process identified all queries which took more than 100ms. We noticed that there were few insert statements which took more than 200ms. For example we have an insert to the following table which is consistently taking more than 200ms. Is there some way of increasing the performance of the insert? Also there is another table called message_Details which has a foreign key relation with this table.

CREATE TABLE `message` (
       `id` int(11) NOT NULL AUTO_INCREMENT,
       `subject` varchar(255) DEFAULT NULL,
       `message` mediumtext,
       `message_type` int(3) NOT NULL DEFAULT '0',
       `posted_on` datetime DEFAULT NULL,
       `posted_by` bigint(20) NOT NULL DEFAULT '0',
       `posted_to` bigint(20) NOT NULL DEFAULT '0',
       PRIMARY KEY (`id`),
       KEY `posted_by` (`posted_by`),
       KEY `posted_to` (`posted_to`)
     ) ENGINE=InnoDB AUTO_INCREMENT=305845 DEFAULT CHARSET=latin1

Any advice is greatly appreciated as I am not a database guy.

Table structure-wise, you are not doing anything wrong that would slow down inserts:

You (presumably) insert in PRIMARY KEY order via the auto_increment value.
There are few secondary indexes on the table.  Both posted_by and posted_to are non-unique, which means InnoDB can use the insert buffer.

I would suggest looking at either configuration or hardware.  I would agree that 200ms is a lot, so it looks like there has to be some contention somewhere.


Can UPDATE lead to deadlocks in SQL SERVER? Can we experience deadlock if run the same update query concurrently?
Let's assume there's a table with millions of records and we need to update several thousand records at one shot.
UPDATE TABLEX
SET Column1 = '1'
WHERE Column2 BETWEEN 1 AND 10000

I'm wondering if running this query concurrently would lead to a deadlock as each update query may acquire update locks on rows in a different order.
In other words, is the following a possible scenario. The scenario is that two concurrent sessions run the same query to update the same set of records.
(My assumption is that each UPDATE statement will scan rows in a different order)

Session 1: Acquires Update lock on row 1 (Column2 = 1)
Session 2: Acquires Update lock on row 2 (Column2 = 2)
Session 1: Tries to acquire Update lock on row 2 but it fails as it's already held by Session 2. (Column2 = 2)
Session 2: Tries to acquire Update lock on row 1 but it fails as it's already held by Session 1. (Column2 = 1)
Deadlock is detected.

Here, my assumption is that each query may scan rows in a different order which implies locks on rows (resources) will be taken in different orders. Does this lead to a deadlock?

Here, my assumption is that each query may scan rows in a different order which implies locks on rows (resources) will be taken in different orders.

The scenario, as you've outlined it, is unlikely under normal circumstances.  The exact same query being run concurrently will most likely use the same execution plan - which means they will both scan the index on TABLEX in the same order.  This situation would most likely lead to blocking rather than deadlocks between the two identical queries.
This assumes you have an index on Column2 that can be used to seek to the right spot, and then scan the subset of rows that need to be updated.
If you don't have an index on Column2, you could end up with a parallel scan of the index / table, and thus your updates could be in an indeterminate order.  Consider a plan like this:

The order of rows coming out of the clustered index scan will vary depending on how many cores are being used by the plan, and how busy each core is doing other work - as well as disk-related waits and things like that.  Unless the "gather streams" is order-preserving, the rows will be updated in a sort of random order.  This could lead to deadlocks as you've described.
In summary, the answer to your question depends on several factors, including the details of your table / indexes, isolation levels, server and database level settings, etc.  Hopefully this gives you some idea of what's involved though.


Efficient count query for consecutive time windows I have a SQL Server Database: in one table, one of the columns represents the time of an event (of type datetime2(7)). Another column represents a product ID. My goal is to specify a flexible time unit and a time window by defining a start and an end time, and count the number of unique product IDs for each time window. A product ID may thus only appear in one time-window (e.g. the first time-window in which it occurence).

An example:

    EventDateTime            ProductID
2013-01-01 07:44:57.840         5
2013-01-02 07:45:10.093         5
2013-01-03 07:45:21.557         1
2013-01-04 09:07:21.253         2
2013-01-05 09:07:42.680         3
2013-01-06 09:07:49.007         5

How would the SQL-Query look like for this task? By flexible time unit I mean I would like to specify 'years', 'months', 'days' or 'hours'.

The parameters would thus be: time_start, time_end, time_unit.
The result should be a table:
1 -> Count of rows with event time in the first time window
2 -> Count of rows with event time in the second time window
, and so on.

First time occurrence is not clear

select min(EventDateTime), max(EventDateTime), count(distinct(ProductID ))
     , datepart(YY, EventDateTime), datepart(MM, EventDateTime)
     , datepart(DD, EventDateTime), datepart(HH, EventDateTime)
from table
group by datepart(yy, EventDateTime), datepart(MM, EventDateTime)
       , datepart(DD, EventDateTime), datepart(HH, EventDateTime)


PostgreSQL: Creating view of multiple tables in function I want to create view of multiple table in function using PostgreSQL 9.3 version.

Example:

I have following tables in the table name "AllTables":

table1, table2,table3

And I have following tables in the database:

table4,table5

Now I need to combine these tables into one and create view within a function.

create or replace function fun1()
returns void as
$body$
Declare
       que varchar;
       b varchar;
Begin
      Select 'select * from ROM ' ||  quote_ident("TableName") ||' UNION'
    FROM "AllTables" a
    INNER JOIN INFORMATION_SCHEMA.Tables so on a."TableName" = so.Table_name into b;

    que := 'Create view view1 as '|| LEFT(b,length(b)-length('UNION'));
    raise notice '%',que;
    execute que;
end;
$body$
language plpgsql;

Note: Able to get only one table. Not getting all tables view.

What you need to do is:

Build each individual query; then
string_agg them together with UNION ALL as the joining string.

UNION ALL is important, not just UNION, as UNION may force a sort-and-de-duplicate operation, which is slow.

I will use the format function and its %I and %L format-specififers instead of quote_ident and quote_literal respectively, because it's massively more readable that way.

I have no idea what the stray ROM in:

'select * from ROM ' ||  quote_ident("TableName") ||' UNION'

is. It doesn't make sense. I'm ignoring it. I assume it's probably an editing error, the end of an accidentally remaining FROM.

So, first, take a query that produces a list of queries for each table:

SELECT
   format('SELECT * FROM %I', "TableName")
FROM "AllTables" a
INNER JOIN INFORMATION_SCHEMA.TABLES so ON a."TableName" = so.TABLE_NAME;

then wrap it in an aggregate (string_agg) to combine the strings, prefix the CREATE VIEW and store the result in your query variable and execute it:

SELECT INTO que
   'CREATE VIEW view1 AS ' ||
   string_agg( format('SELECT * FROM %I', "TableName"), ' UNION ALL ')
FROM "AllTables" a
INNER JOIN INFORMATION_SCHEMA.TABLES so ON a."TableName" = so.TABLE_NAME;

raise notice '%',que;
execute que;

BTW, needing to do things like this often means you have a bit of a database design issue. You should not need tons of little tables with the same schema like this. It usually means you're doing this:

CREATE TABLE customer01_transactions (
    trans_id serial primary key,
    ...
)

CREATE TABLE customer02_transactions (
    trans_id serial primary key,
    ...
)

when you should usually be doing this:

CREATE TABLE customer_transactions (
    customer_id integer,
    trans_id serial,
    ...,
    PRIMARY KEY(customer_id, trans_id)
)


How to add sphinx engine to mysql 5.7? On my Ubuntu 18.04 machine using  mysql 5.7 I have installed sphinxsearch 2.2 and now I'd like to add SphinxSE as an engine so that I can connect to it without language-specific medium.
The docs on sphinix is rather old (for MySQL 5.1.x) and involves compiling mysql from source which I shy away from as it may break the existing stuff.
So I'm wondering if there is any straightforward solution for this which does not require source compilation?

Install MariaDB which will run on top of a MySQL-5.7 data directory. This has SphinxSE included in every mariadb-server package on Ubuntu.
You will need to install the plugin:
INSTALL SONAME 'ha_sphinx';

Please check the incompatibility documentation and make a backup before upgrading.
Note there is little effort being put into maintaining SphinxSE (note MariaDB is a few versions behind the latest 2.11, only minor differences looking at code), and that it has been dropped from SphinxSE 3.0 onward.
I agree its a beautiful piece of software, and I know of one large production use of SphinxSE from a former client of mine who's websites are still running and appear to be using it still.


DROP vs DELETE vs TRUNCATE for staging data I need to import data (>> 1 GB) into MS-SQL-Server. For several reasons I need to import this data into a staging table (not directly into the target). From staging the data is loaded into the target table. The data in the staging table is never used again. I have several ways to do:

Load the data into staging stable, mark this data as new, load only marked data into target, unmark data.
Delete all data in staging, load the data into staging table, load all data into target.
Truncate staging table, load into staging, load into target
Drop staging table, create staging table, laod into staging table, load into target.
Drop staging database, create staging table, load into staging table, load into target.

If the solution is not restricted  by your server role: What would be the recomended solution? Which is most robust?

Solution 1 is most likely nonsense if you don't need the data anymore. Solution 3 performances much better compared to 1 and 2. But what between solution 4 and 3? Actually I would prefer solution 5 as I can change nealy every parameter within the staging process within my load procedure but you need rights to drop&create database. This is quite a lot for a load process. So in most of the cases I use drop and recreate the staging table. But is this the recommended way?

The "recommended way" is to design the best solution given the relevant factors.

Options #1 and #2 are unlikely to be ideal as they result in a lot of unnecessary I/O.  #3 is what would be best in most situations, but as far as I/O is concerned it is not much different from #4.  So performance-wise, #3 and #4 can considered to be the same.  The additional I/O to drop/create a table as opposed to just truncating it is insignificant compared to the I/O of the typical data load.

Your comments seem to indicate that you need to make changes to the staging table quite often, and if that is the case, then #4 may be best for you.  Since it's a staging table, it is unlikely that permissions are complex, and if it's in a dedicated staging database where staging account is the only ID that has read/write, then you wouldn't even need to explicitly set permissions when the table is created.

Option #5 is a non-starter unless you have some specific reason to create a new database each time.


Deleting aged postgresql rows with tricky condition I have two postgresql tables I need to clean up as part of maintenance. The tables, as described in my Django front-end, are:

class Group(models.Model):
    owner = models.ForeignKey(User)
    unique = models.CharField(max_length=36, unique=True)
    created_at = models.DateTimeField(auto_now_add=True)

class Reply(models.Model):
    text = models.TextField(validators=[MaxLengthValidator(500)])
    which_group = models.ForeignKey(Group)
    writer = models.ForeignKey(User)
    submitted_on = models.DateTimeField(db_index=True, auto_now_add=True)

I essentially want to delete all groups where the most recent reply was submitted more than 14 days ago. What will be the correct sql queries to accomplish that? I'm thinking of:

DELETE FROM links_group WHERE id IN (SELECT which_group_id FROM links_reply WHERE "submitted_on" < now() - interval '14 days');

But this is missing the logic of most recent reply. How do go about with this?

Relevant portions of \d+ links_group; are:

                                                          Table "public.links_group"
     Column     |           Type           |                        Modifiers                         | Storage  | Stats target | Description
----------------+--------------------------+----------------------------------------------------------+----------+--------------+-------------
 id             | integer                  | not null default nextval('links_group_id_seq'::regclass) | plain    |              |
 owner_id       | integer                  | not null                                                 | plain    |              |
 unique         | character varying(36)    | not null                                                 | extended |              |
 created_at     | timestamp with time zone | not null                                                 | plain    |              |
Indexes:
    "links_group_pkey" PRIMARY KEY, btree (id)
    "links_group_unique_key" UNIQUE CONSTRAINT, btree ("unique")
    "links_group_owner_id" btree (owner_id)
    "links_group_unique_like" btree ("unique" varchar_pattern_ops)
Foreign-key constraints:
    "links_group_owner_id_fkey" FOREIGN KEY (owner_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "links_reply" CONSTRAINT "links_reply_which_group_id_fkey" FOREIGN KEY (which_group_id) REFERENCES links_group(id) DEFERRABLE INITIALLY DEFERRED
Has OIDs: no

Relevant portions of \d+ links_reply; are:

                                                          Table "public.links_reply"
     Column     |           Type           |                        Modifiers                         | Storage  | Stats target | Description
----------------+--------------------------+----------------------------------------------------------+----------+--------------+-------------
 id             | integer                  | not null default nextval('links_reply_id_seq'::regclass) | plain    |              |
 text           | text                     | not null                                                 | extended |              |
 which_group_id | integer                  | not null                                                 | plain    |              |
 writer_id      | integer                  | not null                                                 | plain    |              |
 submitted_on   | timestamp with time zone | not null                                                 | plain    |              |
Indexes:
    "links_reply_pkey" PRIMARY KEY, btree (id)
    "links_reply_submitted_on" btree (submitted_on)
    "links_reply_which_group_id" btree (which_group_id)
    "links_reply_writer_id" btree (writer_id)
Foreign-key constraints:
    "links_reply_which_group_id_fkey" FOREIGN KEY (which_group_id) REFERENCES links_group(id) DEFERRABLE INITIALLY DEFERRED
    "links_reply_writer_id_fkey" FOREIGN KEY (writer_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
Has OIDs: no

You need something like this (the table and column names may differ, check the actual SQL CREATE TABLE statements):

DELETE FROM links_group AS g
WHERE g.id IN
      ( SELECT r.which_group_id
        FROM links_reply AS r
          -- WHERE r.which_group IS NOT NULL
          -- not needed, the column is not nullable
        GROUP BY r.which_group_id
        HAVING MAX(r.submitted_on) < NOW() - INTERVAL '14 days'
      ) ;

Since there is a foreign key from links_reply that references links_group and does not have an ON DELETE CASCADE attribute set, the above will of course fail. To delete from both tables, you either need to run two statements or combine them in one with a modifying CTE:

WITH
  groups_to_delete AS
    ( SELECT r.which_group_id
      FROM links_reply AS r
      GROUP BY r.which_group_id
      HAVING MAX(r.submitted_on) < NOW() - INTERVAL '14 days'
    ),
  delete_replies AS
    ( DELETE FROM links_reply AS r
      WHERE r.which_group_id IN (TABLE groups_to_delete)
      RETURNING which_group_id
    )
DELETE FROM links_group AS g
WHERE g.id IN (TABLE delete_replies) ;


How to use SET operation in CASE clause in SQL? I have the following structure, and I'm doing a SET operation in a CASE clause but it is producing an error:

  Incorrect syntax near SET

Query:

DECLARE @tbl_Result TABLE
(
    RowID INT IDENTITY(1,1),
    TtlNOfCmpSale7to12 INT,
    TtlNOfCmpSale4to6 INT,
    TtlNOfCmpSale0to3 INT,
    TtlNOfCmpSaleSpanFirst SMALLINT DEFAULT 0,
    TtlNOfCmpSaleSpanSecond SMALLINT DEFAULT 0,
    TtlNOfCmpSaleSpanThird SMALLINT DEFAULT 0
);

DECLARE
    @Declining FLOAT,
    @StableStart SMALLINT,
    @StableEnd SMALLINT,
    @Increasing SMALLINT;

SELECT
    @Declining = 0.90,
    @StableStart = 90,
    @StableEnd = 110,
    @Increasing = 110;

DECLARE @TrendValue FLOAT;

UPDATE R
SET R.TtlNOfCmpSaleSpanFirst =
    CASE
        WHEN R.TtlNOfCmpSale7to12 != 0 THEN
            SET @TrendValue = R.TtlNOfCmpSale4to6*2/R.TtlNOfCmpSale7to12
            (
                CASE
                    WHEN @TrendValue > @Increasing THEN 0
                    WHEN @TrendValue >  @StableStart
                        AND @TrendValue <  @StableEND THEN 1
                    WHEN @TrendValue < @Declining THEN 2
                END
            )
    END,
    R.TtlNOfCmpSaleSpanSecond =
        CASE
            WHEN R.TtlNOfCmpSale4to6!= 0 THEN
                SET @TrendValue = R.TtlNOfCmpSale0to3/R.TtlNOfCmpSale4to6
                (
                    CASE
                        WHEN @TrendValue > @Increasing THEN 0
                        WHEN @TrendValue >  @StableStart
                            AND @TrendValue <  @StableEND THEN 1
                        WHEN @TrendValue < @Declining THEN 2
                    END
                )
        END,
    R.TtlNOfCmpSaleSpanThird =
        CASE
            WHEN R.TtlNOfCmpSale7to12 != 0 THEN
                SET @TrendValue = R.TtlNOfCmpSale0to3/R.TtlNOfCmpSale7to12
                (
                    CASE
                        WHEN @TrendValue > @Increasing THEN 0
                        WHEN @TrendValue >  @StableStart
                            AND @TrendValue <  @StableEND THEN 1
                        WHEN @TrendValue < @Declining THEN 2
                    END
                )
        END
FROM Result R;

It would be better if there is another way to store the result of the expression into @TrendValue so that there is no need to calculate that expression each time.

It would also be good to avoid division by zero errors (that's the reason I added the various R.TtlNOfCmpSale7to12 != 0 checks).

Based on your comment, you seem to be looking for a way to avoid repeating the same calculation over and over again.

You could use APPLY VALUES to calculate the required values once, then use the aliased column in the rest of your calculations.

DECLARE @Declining FLOAT,
@StableStart SMALLINT,
@StableEnd SMALLINT,
@Increasing SMALLINT

SELECT  @Declining = 0.90,
@StableStart = 90,
@StableEnd = 110,
@Increasing = 110

DECLARE @TrendValue FLOAT;

UPDATE R
SET
 R.TtlNOfCmpSaleSpanFirst =
            CASE
                WHEN R.TtlNOfCmpSale7to12 != 0 THEN
                  CASE WHEN Trends.TrendFirst > @Increasing THEN 0
                        WHEN Trends.TrendFirst >  @StableStart AND Trends.TrendFirst <  @StableEND THEN 1
                        WHEN Trends.TrendFirst < @Declining THEN 2
                  END
            END,
 R.TtlNOfCmpSaleSpanSecond =
            CASE
                WHEN R.TtlNOfCmpSale4to6!= 0 THEN
                  CASE WHEN Trends.TrendSecond > @Increasing THEN 0
                        WHEN Trends.TrendSecond >  @StableStart AND Trends.TrendSecond <  @StableEND THEN 1
                        WHEN Trends.TrendSecond < @Declining THEN 2
                  END
            END,

 R.TtlNOfCmpSaleSpanThird =
            CASE
                WHEN R.TtlNOfCmpSale7to12 != 0 THEN
                  CASE WHEN Trends.TrendThird > @Increasing THEN 0
                        WHEN Trends.TrendThird >  @StableStart AND Trends.TrendThird <  @StableEND THEN 1
                        WHEN Trends.TrendThird < @Declining THEN 2
                  END
            END

FROM Result R
CROSS APPLY (
    VALUES (IIF(R.TtlNOfCmpSale7to12 <> 0, R.TtlNOfCmpSale4to6*2/R.TtlNOfCmpSale7to12, 0)
              IIF(R.TtlNOfCmpSale4to6 <> 0, R.TtlNOfCmpSale0to3/R.TtlNOfCmpSale4to6, 0)
              IIF(R.TtlNOfCmpSale7to12 <> 0, R.TtlNOfCmpSale0to3/R.TtlNOfCmpSale7to12, 0)
) AS Trends(TrendFirst, TrendSecond, TrendThird);