How to get the difference in days between 2 timestamps in PostgreSQL I have a table named accounts. I am interested in retrieving those accounts whose status has not been updated since more than 10 days. I thought about this query and it seemed to work but I am not sure it's the best approach:

SELECT * from accounts
WHERE status = 'PENDING_PAYMENT'
AND (date_part('day', (now() - status_updated_at)) > 10);

One thing I don't like about this is the now() part. It would be ideal if now() was not calculated for each record and instead be a single timestamp value calculated at the beginning. Moreover, I would like this example to return 1:

Suppose that for one record the status_updated_at column has this value: 2015-10-05 23:00:00. And now() returns 2015-10-06 01:00:00. Despite being only some hours of difference I want the result to be 1.

You could use the function age() to simplify your expression (returns interval). But it's much more efficient to use a sargable expression to begin with.

This operates with the exact time difference (current time is relevant):

SELECT *
FROM   accounts
WHERE  status = 'PENDING_PAYMENT'
AND    status_updated_at < now() - interval '10 days'

To operate with whole calendar days (up to but excluding midnight, local time zone):

...
AND    status_updated_at < CURRENT_DATE - 10

You can just subtract integer from date, the result is still a date which can be compared directly to the data type timestamp of your column.

You seem to be asking for the behavior of the second variant. The difference is more than 10 days.

How does the evaluation work?

In reply to your comment.

CURRENT_DATE is a special function returning a date (implemented internally with ('now'::cstring)::date in pg 9.4). There are many variants of the operator -. I find 43 in the system catalog pg_operator in my current test DB. The right one is picked by means of the operands' data types.

For date - integer, the integer amount in days is subtracted from the date - just because it's defined that way. It makes sense for me, the unit of a date is "days". Modern Postgres stores dates and timestamps as integer numbers internally. It's very cheap to transform one into the other and it's very cheap to add/subtract days to/from a date and it's also very cheap to compare them.

The result of the expression is a date, while status_updated_at is a timestamp column. I was assuming an implicit assignment cast at first, but that step is not even necessary, date and timestamp share a binary compatible format and can be compared directly. There is variant of the < operator registered for timestamp < date. Column values can be tested "as is", the expression is sargable, a btree index can be used.

SELECT oprname, oprleft::regtype, oprright::regtype
FROM   pg_operator
WHERE  oprname = '<'
AND    oprleft = 'timestamp'::regtype;

Related:

Generate series of dates - using date type as input

With timestamptz, the matter of time zones would be added to the equation, which is complex but typically burns down to very simple evaluation:

Ignoring timezones altogether in Rails and PostgreSQL

Volatility of now()

  It would be ideal if now() was not calculated for each record and
  instead be a single timestamp value calculated at the beginning.

Your wish has been granted. The now() family of functions (including CURRENT_DATE id defined STABLE, i.e. now() returns the same value within the same transaction.

now() default values are all showing same timestamp
How do IMMUTABLE, STABLE and VOLATILE keywords effect behavior of function?


Has anyone connected to Oracle from SSIS using Oracle Driver ( 12.1.0 ) from VS 2017 Data Tools SSIS I have installed Oracle Driver ( 12.1.0 ) and I have VS 2017 Data Tools SSIS . The connection to Oracle Service from local machine via ODBC 64 bit on my machine is working.

Connection to Oracle Server from SSMS 17 via Linked Server is not working

I get 'Architecture Mismatch' error when I create a Oracle OLDEB connection from SSIS.
Any ideas how do I access Oracle from SSMS and SSIS  ..

Thanks,
Shuchi

Visual Studio and SSDT are 32bit programs, so they can't use the 64bit OleDB provider.  You can run your SSIS package in 64bit mode, but the designers don't work unless you also have the 32bit OleDB provider, and are running visual studio using a Path that allows it to load the 32bit OCI.DLL.


eliminate extra spaces in VARCHAR columns In a table, I have an ID column defined as BIGINT(8) and one VOUCHER column that is VARCHAR(100). No matter the actual length of the value in that field, the SQL always returns a full length value, which can be cumbersome in CLI, specially when selecting more fields.

Here is a sample of what I'm getting

ID                   VOUCHER
-------------------- ----------------------------------------------------------------------------------------------------
               26462 7083d8374831148f9e2271741529ee37
               26624 9170220e6971c2414613d0a69b420156
               26625 b75d12847a24981d36f7bfcd58e8a230

I tried TRIM / LTRIM / RTRIM functions, but without success.

What can I use to get something along these lines, at least for display purposes:

ID     VOUCHER
------ --------------------------------
26462  7083d8374831148f9e2271741529ee37
26464  9170220e6971c2414613d0a69b420156
26465  b75d12847a24981d36f7bfcd58e8a230

Thanks to @JNK's comment, select cast(trim(id) as varchar(6)) as id, cast(trim(voucher) as varchar(32)) as voucher, auth from my_table does the job.


How do I go about creating a script to compare , update and match structure of two databases I am comparing two databases using Opendiff which is similar to sqlCompare.

I have two databases which should match in structure so as I compare I would like to construct a script such that if the one DB has a specific table that the other DB doesn't have it creates that table so that the structure and collation in the two databases matches .

I am using SQL Server 2012.

Thanks

SQL Server Data Tools comes with Schema Compare (and a lot more).

1) Import either DB to a Database Project.

2) Bi-direction Schema Compare to remaining DB. Import and Deploy changes as necessary.

3) Save Solution to Source Control and deploy the exact same DB to any number of servers.

At any stage you can either script out the operations or simply execute it from the UI. The script includes checks to prevent data loss or other unwanted issues, but it's best to review especially if you're new to the tool.


Shrinking data file to save disk space before migration I know this is most discussed answered on various forums: I completely understand answer is no in most of the time or cases.
However want to know if it can be good approach under any scenario:
Lets say for current prod DB which in our case is over 30 TB and have retention of data for 1 year. Tables are mostly compressed and partitioned.
Development team came up with logic where they dont need data to stay upto year and want to change retention to 6 months. So it is expected data would be reduced to half after purge and just having 6 months data going forward.
This is where we have been requested to work on shrinking data files so that those LUNS with TB of space could be returned or if we migrate to new sql version we would be requesting lets say few (approx10) TB's less which do save some money, assuming we keep 5Tb extra for growth in database in future.
In this case would shrink help if we go with shrinking in small chunks assuming in low peak time blocking may be accepted or if shrink is run for longer duration can be canceled?
How much worth is the above shrink data file activity or is there a better approach?
Edit --> We are migrating to latest version possibly SQL 2017or SQL2K19. The tables are not partitioned based on filegroups, as for this databases all the 30+ files are on primary. I know, its just a vendor DB design and dont have much in my hand.

you can read about this here - https://www.brentozar.com/archive/2017/12/whats-bad-shrinking-databases-dbcc-shrinkdatabase/
and about when shrinking is a must what can you do here - https://www.brentozar.com/archive/2020/07/what-if-you-really-do-need-to-shrink-a-database/
When we shrink our database, it introduces both external and internal fragmentation, it causes blocking, it causes transaction log growth while it runs. Index fragmentation goes high. When we rebuild the index, the database size goes back up. Hence causing the endless cycle of shrink-rebuild.
Assuming you have the downtime in place, and if I must do a shrink to move the DB, I would script out all indexes and drop them. run checkpoint multiple times, take log backup,  Shrink log file then shrink the database, take full backup and restore this backup to new location. run the index script to create them again and let it grow in new server. Caution: creating index on a 30TB is going to be a pain.
There could be a better way of doing this, I would be happy to keep an eye on other answers.


Getting last row for each day in MySQL I have problem in selecting other columns for the max(datetime) records. In layman terms I need to get relevant columns where max(DialDateTime) records for all the dates in Mysql.

 mysql> select max(DialDateTime) as max from log_AP group by   date(DialDateTime) ;
 +---------------------+
 | max                 |
 +---------------------+
 | 2012-12-03 07:37:26 |
 | 2012-12-04 07:37:04 |
 | 2012-12-05 07:37:04 |
 | 2012-12-06 07:37:04 |
 | 2012-12-07 07:37:04 |
 | 2012-12-08 07:37:04 |
 | 2012-12-09 07:37:04 |
 +---------------------+

7 rows in set (0.00 sec)

You should be able to use a subquery to get the max date and then join that to you table to return the remaining columns:

select a1.*
from log_AP a1
inner join
(
  select max(DialDateTime) as max
  from log_AP
  group by date(DialDateTime)
) a2
  on a1.DialDateTime = a2.max


How many threads being used while query execution? I have AdventureWorks2014 database installed on my test SQL Server 2014. I am planning to execute the following query:
SELECT *
FROM Sales.SalesOrderDetail sod
INNER JOIN Production.Product p ON sod.ProductID = p.ProductID
ORDER BY Style

Before actually executing it, I have the following picture of my worker threads:

While query execution, I again checked the state of worker threads:

As you can see, total active_workers_count in the 2nd picture is higher than that of in the 1st screenshot. From the 2nd screenshot I can assume that total worker threads needed for query execution was 1+2+1+2=6. However when I take a look at properties of index scan operator in my execution plan, I see:

It seems that my degree of parallelism was 4 which means that 4 threads were parallelly processing rows.
I am confused and can't determine how many threads were running during query execution. Can someone explain to me why active_workers_count is different from DOP?

I am confused and can't determine how many threads were running during query execution.

To understand that, you really need to look at the execution plan.  Here's what I get for this query on SQL Server 2017 at DOP 4 (in the SQL Server 2014 compatibility mode):

Each parallel branch gets DOP threads.  So at first glance, it looks like 12 workers could be active for this query at once, maximum.
However, a Hash Match join has to consume the entire build (upper) input branch before it activates the probe (lower) input branch.  So here's how execution goes, generally:

4 worker threads start running the red branch (remember, execution starts at the upper left of a query plan)
The Hash Match asks for rows from the black branch
4 worker threads start running the black branch (a maximum of 8 worker threads are active now)
Once the black branch is done, the probe side of the execution plan is activated
The 4 workers threads used by the black branch are reused by the blue branch (for a maximum of 8 active worker threads still)

Thus this query will use at most 8 parallel worker threads at a time.  SQL Server is smart enough to realize that only two branches can be active at once (because of the blocking nature of the Hash Match), and only reserves 8 threads (instead of 12):

Notice that it says "2" for branches - that's the number of branches that can execute concurrently.
To address your screenshot of active workers, some threads might finish all of their work before others (because they have less rows to process, for example).  So at a given point in the query's execution, you might only have 6 threads running (out of the 8 possible threads).
Related reading:
Parallel Execution Plans – Branches and Threads
In SQL Server, is parallelism per operator, or something else?


Select IDs according to CSV file I have a CSV file with IDs of rows. I want to use that to make a SELECT statement selecting the IDs within that CSV file.

There is 1 million rows and I want to select about 95,000 of those.

I want to SELECT and import these rows to another SQL Server database using the SQL Server Import and Export Wizard.

I'm using MSSQL 2005 with SQL Server Management Console.

Probably best to:

Import the entire CSV to a table using Import and Export Wizard.
Run the query to pull the data you want from the table.
Use the Import and Export Wizard to import to the other database.
Clean up the CSV import, probably by truncating or deleting the table.

Unless you want to use SSIS? Then you can design a package to do it easily without importing the whole file directly.


Returning a json array slice in PostgreSQL Postgres 9.5

Given a table with one field of type json, about 700 rows, and where each row has about 4,000 elements in a single array...

my_db_field:

[0.44577, 0.4855, 0.45429, 0.54437,...]
[0.45012, 0.48698, 0.45715, 0.55337,...]
[0.47347, 0.49156, 0.46079, 0.56818,...]
[0.4936, 0.49835, 0.46086, 0.58195,...]
[0.51068, 0.50511, 0.46228, 0.59482,...]

The PostgreSQL docs show how you can query for a single element inside an array.

 SELECT my_db_field->2 AS test FROM my_db_table

results:

test (of type json)
--------------------
0.4855
0.48698
0.49156
etc.

What I would like to do, though, is select multiple elements in the array and return that as an array in the same format the rows are. By multiple, I mean around 300 elements in the array; e.g., from element 0 to element 300. Does Postgres have a nice syntax for such a query?

In PostgreSQL 12, you can obtain JSONB array slice using the jsonb_path_query_array function:

SELECT jsonb_path_query_array('["a","b","c","d","e","f"]', '$[2 to 4]');
 jsonb_path_query_array
------------------------
 ["c", "d", "e"]
(1 row)


Azure SQL database DTU compute utilization maxing out I have an Azure SQL database that has gone from a low DUT baseline between 0-3% (avg) to now maxing out at 100% utilization.

This is for a LOB application and we need to understand what is causing the utilization to be maxing out like this. The server is on standard with 200 DTUs and not in an elastic pool.
I've looked at Query performance insight in the Azure portal and while there are some long running queries, the top offender represents only 0.3% CPU and data IO of 0.23% over the past week.

Can anyone offer a good strategy for diagnosing the issue here?

the top offender represents only 0.3% CPU

A DTU is a package of CPU and IO time, so you most likely have queries performing a lot of read IO (index or table scanning most likely) or write IO.
The sudden change could be because of:

A new release of the application that has new SQL activity that hasn't been tested on data scaled/shaped like your production data. Look for long-running statements that consume a lot of IO. The solution here will be either in improving those queries and/or the indexing choices to support them.
Significant change in your data size (suddenly or over time) meaning that your common working set no longer conveniently fits in RAM meaning that the buffer pool is thrashing causing extra IO work.
Significant change in your data shape (suddenly or over time) meaning that the query planner is having a harder time choosing an optimal path. Refreshing index statistics can sometimes help with this if it has not been done for a large amount of time.
If your application allows users to define reports or filters they may have created something that has the same effect as point 1 (you'll probably detect this in the same way).
A sudden influx of active users, or existing users suddenly becoming more active. Check your application logs for that.

Or some mix of the above.
You do not state what service level your database is as currently (service tier, service level, is it in a pool, if so the tier/level of the pool and per-DB setting) - it would be a good idea to edit that into your question.
For a short term fix, bump up the DTU allocation for that database. If the load is mainly IO then consider moving from a Standard to a Premium tier (while they don't publicly document exactly how a DTU is composed/judged, a premium DTU is said to have an order of magnitude more IO then a standard one so going from standard-100 to premium-125 can sometimes be far more effective than moving from standard-100 to standard-400 but cost less).
Update after Extra Information in Question

the top offender ... data IO of 0.23%

You screen-grab is of the display sorted by CPU - so the top offender for CPU use is apparently not using much IO. But if you are looking for processes that are causing a lot of IO then you need to sort by IO as that may not also be consuming CPU (your CPU allocation could be sat waiting for the IO to complete).
Though from that display is would be worth checking that the behaviour of those four is as good as it could be, particularly the fourth which is taking an average of 22.5 minutes per execution. That could be due to locking issues hence not showing much CPU or IO activity though in any case it is worth investigation.

over the past week.

Your problem, according to the top graph, only started a couple of days ago so looking at the full week is not likely to be all that meaningful. The performance insights tab allows you to zoom in more finely than that.


idera - why it restores databases faster We have a 2gb live database that takes forever restore whenever I need some live data to test something. If I use idera it takes 5 mins at most. When I use the restore database command it can take almost an hour. How is idera able to do so much faster?

I use the following to restore:

RESTORE DATABASE dbname
  FROM DISK = 'path'
  WITH
  MOVE 'CMTS_dat' TO 'C:\db\MARTIN_Latest.mdf',
  MOVE 'CMTS_log' TO 'C:\db\MARTIN_Latest_log.ldf'
  ,REPLACE

If it has any data partitions then I'll use MOVE to do the partitions as well.

From Idera's website, it appears SQL Safe inserts a "shim" (a piece of code, be it CLR or native code) into SQL Server to support what they refer to as "Instant Restore":

  Instant Restore

  Patented Instant Restore technology brings your database online immediately—virtually eliminating application downtime. This can save you huge amounts of time when getting back online in a disaster or searching old backups for a piece of lost data. Once online, your database acts as a fully functional database, supporting all read and write operations. SQL Safe streams data from the backup file “on demand” to support applications and user requests while completing the restore operation in the background.

This indicates, to me, that the database is not actually fully restored prior to the database becoming accessible to client queries.

I'd be just a little concerned about this style of recovery for production since anything that has the possibility of reducing ACID compliance is a risk.  Using this restore capability for development or support seems like a great idea since you no longer need to wait for recovery to complete.


CLR/C# equivalent to XACT_STATE() I am porting a set of T-SQL stored procedures to C#/CLR. In the T-SQL, we use XACT_STATE() to determine if an error requires rolling back to a savepoint or rolling back the transaction entirely.

I cannot find an equivalent check of the transaction using the System.Data.SqlClient.SqlTransaction or System.Transactions.TransactionScope objects.

Does anyone know how to check the transaction state from within C#/CLR?

XACT_STATE() is not something that SqlClient has any special insight on, much like transaction level. So, just query it with SqlCommand.

// If you already have an open connection...
public int GetXactState (SqlConnection connection)
{
    using (SqlCommand command = new SqlCommand("Select XACT_STATE();", connection)) {
        return (int)command.ExecuteScalar();
    }
}


Export data using bcp to deal with corrupted data I am getting consistency errors while running DBCC CHECKDB on a table that has corrupted rows on SQL Server 2014. I am running into corruption errors after the first 30,000 rows (total 1.4 million (14 lakh) rows).

I want to try to bcp the data out until I get to the corruption. I want to skip the corrupted rows. I want to bcp after the corruption range and minimize the data loss.

How can I do it? Please explain in detail.

See also my previous question: Consistency Errors running CHECKDB on a couple of tables in SQL Server 2014

First, here's my checklist on how to tackle corruption issues. It's a big deal - bigger than I can cover here - but based on the sounds of this, I don't think you've covered some of the basics (like trying to restore from backup, or making sure you've even got backups.)

When you start getting corruption errors, every time you access storage, you may be making the problem worse. For example, I dealt with one broken storage array where the more we read data, the more it became corrupted. (I'd read one table and it was fine, and then fifteen minutes later, it was hosed too.) Just like a haunted house, you need to get off that storage device as quickly as possible, and tackle your recovery efforts somewhere else.

Once you're on another storage device, if you can isolate the corruption down to a single table (which isn't likely, but you're hinting that you've done that), then:

In CHECKDB's output, identify which indexes are impacted (you can see via the index ID - 0 is a heap, 1 is a clustered index, and 2 & higher are nonclustered indexes)
If you have corruption in index 0/1, then for bonus points, copy data out from each index separately. You may be able to get some of the columns back for the corrupted rows in the clustered index. (If you have corruption across multiple nonclustered indexes, you may be able to get all of the rows from some nonclustered indexes, but not others.)
If you can identify which pages are affected, then you'll need to design your WHERE clauses to work around the corrupted pages. (SQL Server won't gracefully skip corrupt pages - it's up to you to design your query to stop before it encounters corruption.)

Think about a phone book, for example, organized by last name, then first name. If you know that the page containing Ozar, Brent is corrupted, then you may have to select users up to my last name, but stopping before that - and then run a second query for users with a last name higher than mine.

Here's the trick, though: corruption is going to strike all over the table. You're going to be doing a ton of work trying to get around different corrupt pages.

It's not easy, and that's why I wrote my checklist - and why it doesn't try to tackle this portion. You're talking about days, if not weeks, of work when the corruption is widespread.


altering a db2 table with huge amount of data I have a table in DB2 which is having 99 columns and 900,000 records of data.

I want to alter the table with one extra column as below:

ALTER TABLE MYSCHEMA.PRODUCT ADD UPDATE_DT DATE

However, the query never completes and the status of the table shows locked against my id in the APPLICATION LIST window from where I have to force kill it.

I believe this is because of the huge amount of data present.

Am I missing anything here ? How can I achieve what I am trying to do ?

Did you check the db2 diag log? what does it say?

The first thing comes into my mind it is that probably, the database does not rollback when the transaction log is full, but instead your updates never finishes.

Check the database logging parameter to see if there is enough log space to record the transaction.

BLK_LOG_DSK_FUL http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.ha.doc/doc/c0006131.html


sp_Blitz Error on Azure SQL Database Getting the following error when trying to install sp_Blitz on a Azure SQL Database

  Msg 40515, Level 15, State 1, Procedure sp_Blitz, Line 16 [Batch Start
  Line 15] Reference to database and/or server name in
  'master.sys.all_objects' is not supported in this version of SQL
  Server.

I can't see anything in the comments on the SP to suggest that Azure SQL Database isn't supported

In the readme of the first responder kit there is a statement on Azure SQL DB

Azure SQL DB - It's a dice roll. Microsoft changes DMV contents in
here without warning, so no guarantees.

There is another explanation by Scott Hodgin and a possible workaround by Henrik Staun Poulsen in this question.
Per Henrik's words

I've tried to make a version of sp_blitz that works on Azure SQL DB.
See here  Now, I'm looking for people that will test it.

You could test his version of sp_blitz and give him some feedback.


Mapping a 1:1:N ternary relationship to a relational schema I have a question about mapping (conceptual) n-ary relationships to (logical) relations.

Assume we have three strong entity types: Member, Equipment, and Time Slot, and a relationship named Reserves between them.

The attributes do not matter (just assume we have an Id key attribute), but the cardinality ratios are as follows:

Meaning that:

A member can reserve a particular equipment at multiple time slots (the N),
An equipment can be reserved at a particular time slot by only one member (the 1 on the left),
A member can reserve only one equipment per time slot (the 1 on the right).

And there is no total participation constraint.

In look across notation (I believe):

I'm trying to map this relationship to a relation in a relational design.
We would have:

RESERVES
(MEMBER.Id, EQUIPMENT.Id, TIME_SLOT.Id)

Where the three attributes are foreign keys to, respectively, the relations that correspond to MEMBER, EQUIPMENT and TIME_SLOT.

But what should be the primary key?

Elmasri, Ramez, and Shamkant B. Navathe. 2015. Fundamentals of Database Systems (7th Edition). Pearson. reads, p. 296:

  The primary key of S [the relation resulting from the mapping of the n-ary relationship R to the relational model] is usually a combination of all the foreign keys that reference the relations representing the participating entity types. However, if the cardinality constraints on any of the entity types E participating in R is 1, then the primary key of S should not include the foreign key attribute that references the relation E' corresponding to E (…).

Applying this recipe blindly make that only TIME_SLOT.Id is the Primary Key, which does not make sense at all (two different equipments can be reserved at the same time!).

Having the three attributes being the primary key does not reflect the fact that one equipment cannot be reserved multiple times at the same time slot.

A candidate key of a relation is a set of attributes such that every tuple (record) of the relation must have values differing from those of all the other tuples.

A primary key can be either chosen among the different candidate keys of a relation, or can be a surrogate key, that is an artificial identifier invented to uniquely distinguish all the records of the relation.

Surely, a primary key should not be a strict superkey, that is a key from which we can remove one or more attributes so that the remaining attributes still uniquely identify all the tuples of the relation.

Now to find the primary key we must first ask: which are the candidate keys of this relation? In this case there are only two candidate keys: (EQUIPMENT_ID, TIME_SLOT_ID) and (MEMBER_ID, TIME_SLOT_ID). In fact, each of them must be unique for all the tuples of the relation. And there are no other candidate keys (in fact, (EQUIPMENT_ID, MEMBER_ID) can have duplicate elements, as the single attributes, as you have already noted, while (MEMBER_ID, EQUIPMENT_ID, TIME_SLOT_ID) is a strict superkey).

So the the primary key in your case could be: a) the couple (EQUIPMENT_ID, TIME_SLOT_ID); b) the couple (MEMBER_ID, TIME_SLOT_ID); c) a surrogate key like RESERVES_ID. If there are no particular reason to introduce another attribute as surrogate key, you can use any of the two couples of attributes as primary key, and declare the other pair as unique.

Finally, note that applying the normalization theory, you have two non-trivial functional dependencies MEMBER_ID, TIME_SLOT_ID -> EQUIPMENT_ID and EQUIPMENT_ID, TIME_SLOT_ID -> MEMBER_ID from which can be derived that the relation is in Boyce Codd Normal form and that the two couples are the unique candidate keys.

EDITED

If your question really is: in case of a generic ternary relationship, with the cardinality as in the example, represented as a relation with three foreign keys, which are the candidate keys of such relation?

The answer can be given by considering the functional dependencies inferred by the cardinality. Let’s call the three attributes (foreign keys) A, B and C, and suppose that you can have at most a value of A for a couple of values BC, at most a value of B for a couple of values AC, and any number of values of C for a couple of values AB (this models what you have called 1:1:N relationship in your example, with A being the member, B the equiplement and C the time slot). This can be translated in terms of functional dependencies as:

BC -> A
AC -> B

(technically, this is called a cover of the set of dependencies holding on the relation, meaning that there are no other non-trivial functional dependencies holding in this case).

From this, we can easily derive that the relation has only two candidate keys, AC and BC. So, in terms of a relational database system, you can choose any one of them as primary key, and declare the unique constaint for the other.


MySQL - How to optimize multiple OR conditions in a query on a large table I have a table with the following structure:

Configuration (
  id: binary(16) Primary key,
  htId: binary(16),
  raId: binary(16),
  date,
  amount
);

I have already created these indexes:

idx_ht (htId) BTREE, idx_ra (raId) BTREE, idx_date (date) BTREE, idx_ht_ra_date (raId, htId, date) BTREE.

The table has nearly 150M records.

Here is the slow query with multiple htId-raId pairs that I want to optimize.

Select htId, raId, SUM (amount) as totalAmount
From Configuration r
Where ((r.htId = htId_1 AND r.raId = raId_2)
OR (r.htId = htId_3 AND r.raId = raId_4) ... ~1000 pairs more (r.htId = htId_X AND r.raId = raId_X))
AND date Between fromDate
AND toDate
Group by r.htId, r.raId
Having Count(1) = DATE_DIFF(fromDate, toDate)
Order by totalAmount ASC LIMIT 0,100

Is there any way to optimize the query above? Seem that the indexes do not have any affect on my query when the query has multiple ORs like that. It take a lot of time to query..
Any help will be much appreciated.
BR.

OR is hard to optimize, especially 1000 of them.
See if you can through the values in a table, then JOIN to that table.
Then provide EXPLAIN SELECT ....


Problem with shortest distance calculation in PostGIS While working with PostGIS pgrouting, for calculating the distance between two roads (lines) I'm using the shortest_path function.

The logic is based on start_point(Start_id) and end_point(end_id) but in my data the line string contains many internal points like (linestring(1 1,2 2,3 3,4 4,5 5) for example.)

So it is taking the start point as (1 1) and the endpoint as (5 5). If another line starts with (5 5) it shows as route like linestring(5 5,6 6).

But a line which has a crossing point inside the linestring like (2 2,3 3,4 4) is not considered connected.

For example, using this roads table:

id         name                way
1          A                   linestring(1 1,2 2,3 3,4 4,5 5)
2          B                   linestring(5 5,6 6)
3          c                   linestring(2 1,2 2,2 3)

If I apply the shortest_path function from point (1 1) to (6 6), it does determine a path. But for (1 1) to (2 3) it doesn't find anything. It does not find the (1 1,2 2,2 3) route.

What do I need to do so that the function considers each segments internal points rather than only the start and end points of the lines?

Maybe this will help start you out but here is how I am thinking through this.

I can't think of any case, re spherical trig where two straight lines would be closer in the middle than at the edges, unless they cross so I think you can narrow your search down to the vertices of each road plus the nearest point to that vertex on the other road.  I am assuming that your roads are modelled as line segments along great circles.

So I don't think that this is a complete solution but, what I would be looking at doing is:

Do these lines intersect?  If you assume that all intersections are listed as such in your db, you can assume no.
If so that's the closest point.
If not, then grab the vertices on both roads, draw any lines perpendicular to another line segment on the other, and calculate distance to intersection.  Your shortest point will be the minimum of these lines.

That seems the best answer.  You have to calculate points.  I don;t know of any magic way to do this in PostGIS but it is something where spherical trig may come to the rescue.  If the distances are sufficiently small, plane trig may be sufficient as an approximation, of course.


Why is query using Clustered Index when it shouldn't? Let us presume I have a table named Category in a SQL Server 2005 database.  Category has category_id (bigint, identity) as its primary key and name (nvarchar(50)).  There is obviously a clustered index on category_id, and I have also added a non-clustered index on name.  If I run the query

SELECT *
FROM Category
WHERE [name] like '%zz%'

and look at the Execution Plan via Management Studio 2005, it says it is using a Clustered Index scan.  The clustered index is on category_id, not on name.

Why is it saying that?

The "like '%zzz%'" largely negates the possible benefit of the index since it will need to examine each entry to determine whether it matches; and doing the "SELECT *" means it would have to do a lookup to the clustered index to get all of the column data on the matched records; the optimizer can't estimate how many matches there will be so chooses the clustered index.

Try changing it to "like 'zz%'" and see what you get.


Deadlock debugging with node: log responsible queries I have a system of many node scripts that read and/or write automatically on a Postgres database all the time. One of those scripts randomly gets a deadlock. I would like to debug this, but the problem is that I don't know which other query cause the deadlock. (I use pg)

So my question is:

If I catch an error like this

{ error: deadlock detected
at Connection.parseE (/data/jenkins/workspace/03-10-Lotti-watcher-lotti-mod/import/node_modules/pg/lib/connection.js:604:13)
at Connection.parseMessage (/data/jenkins/workspace/03-10-Lotti-watcher-lotti-mod/import/node_modules/pg/lib/connection.js:403:19)
at Socket.<anonymous> (/data/jenkins/workspace/03-10-Lotti-watcher-lotti-mod/import/node_modules/pg/lib/connection.js:123:22)
at Socket.emit (events.js:197:13)
at addChunk (_stream_readable.js:288:12)
at readableAddChunk (_stream_readable.js:269:11)
at Socket.Readable.push (_stream_readable.js:224:10)
at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:150:17)
  name: 'error',
  length: 336,
  severity: 'ERROR',
  code: '40P01',
  detail:
   'Process 2376 waits for ShareLock on transaction 55837412; blocked by process 22585.\nProcess 22585 waits for ShareLock on transaction 55837411; blocked by process 2376.',
  hint: 'See server log for query details.',
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: 'while locking tuple (226684,50) in relation "lotti"',
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'deadlock.c',
  line: '1146',
  routine: 'DeadLockReport' }

can I get the query and/or user of the other process in order to isolate and identify the problem? Or anything like this, if it makes sense.

Sure. Just look into the PostgreSQL log file.

This information is not sent to the client for security reasons: otherwise, you could craft SQL statements that deadlock with other queries to see somebody else's statements, which might contain confidential data.


unable to run postgres with pg_ctl I was reading a postgresql book (PostgreSQL from Novice to Pro) and it says you can start the postmaster daemon by invoking the postgres binary which was part of the installation:

/usr/lib/postgresql/9.3/bin/postgres "-D" "/var/lib/postgresql/9.3/main" "-c" "config_file=/etc/postgresql/9.3/main/postgresql.conf"

Notice we had to pass both the data directory and configuration as parameters to the postgres binary.

The book then goes on to say that the pg_ctl executable, which also happens to be stored in the /usr/lib/postgresql/9.3/bin directory when installing via apt-get, is used to simplify the task. But I cannot get pg_ctl to run:

$ /usr/lib/postgresql/9.3/bin/pg_ctl -D "/var/lib/postgresql/9.3/main" start
server starting
postgres@estonia:/usr/lib/postgresql/9.3/bin$ postgres cannot access the server configuration file "/var/lib/postgresql/9.3/main/postgresql.conf": No such file or directory

It says it cannot find the configuration file, but there is no way to specify the configuration file here:

$ /usr/lib/postgresql/9.3/bin/pg_ctl start "-D" "/var/lib/postgresql/9.3/main" "-c" "config_file=/etc/postgresql/9.3/main/postgresql.conf"
pg_ctl: too many command-line arguments (first is "start")
Try "pg_ctl --help" for more information.

How do I get pg_ctl to run?

When used to start the instance, pg_ctl accepts options to the postgres executable, passed with -o

From its manpage:

  -o options
       Specifies options to be passed directly to the postgres command.

       The options should usually be surrounded by single or double quotes
       to ensure that they are passed through as a group.

So your last command in the question should be amended as:

/usr/lib/postgresql/9.3/bin/pg_ctl start \
   "-D" "/var/lib/postgresql/9.3/main" \
   "-o -c config_file=/etc/postgresql/9.3/main/postgresql.conf"


Excel --> MS SQL; skip blank calculated rows Trying to put the final touches on a workflow I've been setting up and am running into a bit of a problem.

What I am hoping to do is as follows:

Have an Excel spreadsheet with multiple tabs that I give to an "end user"
Have one tab on which the end user inputs data and have another tab that has a bunch of values that are calculated based on the user input tab and some other lookups, etc.
Have an SSIS package that imports non-blank rows from the calculated tab into a table in SQL.

The way the spreadsheet is currently set up is I have an IF check in the fields to either set them to a value or set them to nothing ('').

The problem I have is that when I do the import into SQL, the rows with '' fields are imported as empty rows, presumably because the fields aren't actually blank (they have formulas in them).

Anyone know of a way to fix / work around this?  I tried changing the formulas to set the fields to #N/A instead of '', but if I do that, the import fails.

Thanks!

I would import the entire spreadsheet into an empty "temp" table, then run a sql statement to copy the rows I want across to my permanent table.

This way it is easy to setup rules so that you only get the rows you want and it is easy to test both the import and the copy jobs separately.


How to do a kind of selective grouping in a SQL result? I have some query as select x1,x2,x3,x4 from foo where bar resulting something like:

CREATE TEMP TABLE foo
AS SELECT * FROM ( VALUES
 ( '00','01','03','AA' ),
 ( '01','03','03','BB' ),
 ( '02','09','05','CC' ),
 ( '03','08','05','DD' ),
 ( '03','08','03','EE' ),
 ( '03','08','03','FF' ),
 ( '03','08','03','FF' ),
 ( '03','08','03','FF' ),
 ( '03','08','09','GG' )
) AS t( x1, x2, x3, x4);

Note the x3 column: we have 03 twice then 05 twice then 03 again then 09. I need to calculate a route using the pgr_ksp function (PostGIS). Imagine the table here is the result and the x3 column is the road names. In this case I start on 03 street, walk on it for 2 segments  and take the 05 street just to take the 03 street again after.

My problem is I'm showing this segments to the user and I don't like to. The user know nothing about segments of roads and just want to see take 03 street, 05 street, 03 again and then 09.

If I just group by, I will lost the take 05 step between the two 03 and I'll see the all long 03 segments connected: take 03 street (all 6 segments together), 05 and then 09 and this is wrong.

My question: how can I group the x3 column as the two first 03, then group the two 05 and then group 03 again after 05 and then group all tuples left using this method.

Not so fast! I must have a sum of the x2 column as I group the x3. This will be the geometry column I must compute - concat all segments geometry to have the complete way representation. This is the result I want:

-----------
| x2 | x3 |
|----|----|
| 04 | 03 |
|----|----|
| 17 | 05 |
|----|----|
| 32 | 03 |
|----|----|
| 08 | 09 |
-----------

x2 is the way geometry and x3 is the name of the street or way. In this case I'll have the complete way segment (sum of small pieces) and the way name.

You will need some column value that differentiates the x3 values. For example you could group by both x1 and x3, then you will only lose rows where both x1 and x3 are the same.

select x1, sum(x2), x3 from foo where bar group by x1, x3.

This would look like:

_____________________
|    |         |    |
| x1 | sum(x2) | x3 |
|____|_________|____|
|    |         |    |
| 00 | 01      | 03 |
| 01 | 03      | 03 |
| 02 | 09      | 05 |
| 03 | 08      | 05 |
| 03 | 24      | 03 |
| 03 | 08      | 09 |
|____|_________|____|

For any other output you would need to use programmatic logic to loop through the SQL output, sum x2, and only present new info when x3 changes. You could do this in your application, or you might be able to write a function for it in PostGREs, but I'm pretty sure it is not supported by ANSI SQL.


What locks are taken during statistic operations in SQL Server? What locks are taken during the various statistic operations below in SQL Server assuming the default options of auto create stats and auto update stats are set to true?

Automatic creation of a statistic object
Automatic update of a statistic object
Manual creation of a statistic object
Manual update of a statistic object

Is there any difference in the locks taken in scenarios 1 & 2 above when statistics are set to update asynchronously?

AFAIK, the only real X lock that is taken is on the 'statistics resource'.  The rest of the locks can be read around and don't block.

  That is a resource that only statistics operations care about. It
  exists to limit the number of stats operations in progress on a single
  table to one at a time. Auto-statistics operations do not wait for it
  but just abort if they cannot get it. Manual statistics operations do
  wait for the resource.

https://connect.microsoft.com/SQLServer/feedback/details/174619/update-statistics-holding-exclusive-lock-and-blocking


granting access for remote account to local database instance i have two computers in a same domain, there is database instance on one of them, which is the DC, the question how can i add an account (reside on the other computer) to grant access to that DB!. i tried to add the account in login window but it didn't work! it is even not listed.
these screenshots will help:

You are going to want to create an AD service account on your SQL server. Then also make sure that AD service account is on application server, and is you local admin group on that box (I'm guessing on this part).

Create the AD Service account
Add the newly created AD account to your SQL Server, but wait a bit for the AD account to propagate. Give it a minute or five.
Create a new SQL login, keep Windows Auth selected, and click the Search... button
When the Select User or Group window appears, click the Locations... button If you are wanting to add a group, then you will want to click the Objects Type button, and select the Groups checkbox before you search

On the Locations window select Entire Directory, and click OK

Enter in the user name you just created, and click the Check Names button. If it finds the user you will know, and click the OK button
Now, assign it to a server role, or assign it a database with the permissions needed for your application.

Now try to connect your application server to your SQL Server.

Now, you can cheat. However, I wouldn't recommend this, but if you don't really care who is making the call from your application server. Run this script below. Change the domain to your domain, and replace SERVERNAME with the name of your server. Once this created on the SQL server give it perms to the server or database in question.

 CREATE LOGIN [DOMAIN\SERVERNAME$] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]


SQL Server 2012 Tuning process exited unexpectedly. DTAEngine I am using Windows Server 2012 x64 and SQL Server 2012.

I am running the Database Tuning Advisor with these settings :

Advisor plan cache.
Database workload master.
Check my database all the tables.

When tool is started after few seconds I am getting :

  SQL Server 2012 Tuning process exited unexpectedly. DTAEngine.

After some "googling" I found that I should install Service Pack 1 for SQL Server 2012. So I did that.

Same result.

If someone has experienced something like this please advise me.

Thx for your time.

As per KB 2728419

You have to install atleast SQL Server 2012 + SP1 + CU1.

On a sidenote, CU8 is out as well which is the latest one for SQL Server 2012 SP1.

Also, you need to be a member of SYSADMIN role. DB_OWNER wont work as per the above KB.


Create foreign key on foreign table in postgresql I need to link my table with another one which is in different database(say table logs in device db and table accounts in user db, both on same server). So using Foreign Data Wrapper I create a foreign table(I check it with running select * from accounts; in device db which works successfully).

But I could not create a foreign key to foreign table from device db still

CREATE TABLE public.logs
(
  id bigint NOT NULL DEFAULT nextval('logs'::regclass),
  ...
  account_id bigint,
  ...

  CONSTRAINT logs_account_id_fkey FOREIGN KEY (account_id)
      REFERENCES public.accounts (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION

)

The error message is

ERROR:  referenced relation "accounts" is not a table
********** Error **********

ERROR: referenced relation "accounts" is not a table
SQL state: 42809

You can't create a FOREIGN KEY constraint that references a table in either a different database or through a foreign data wrapper. A foreign key must reference a (base) table* in the same database (a base table: not a view, not a foreign data wrapper table).

If you can change your design to have these 2 databases as 2 schemas in the same database then you can create the foreign keys without any problem.

*: there's also another restriction: a permanent table cannot reference a temporary table.


Identity value jumps when restarting SQL Server We just switched from SQL Server 2008 R2 to SQL Server 2012. I am facing a problem with identity columns:

Whenever I restart SQL Server, the seed value for each identity column is increased by 1000 (for int identity columns it is 1000 and for bigint it is 10,000). For example, if the next int identity value for a table was 3, after restarting SQL Server it will be 1003. If I again restart SQL Server, it will be 2003 and so on.

After searching Google, I found that it is a new feature (don't know what is use of it) in SQL Server 2012, having only two solutions if you want the old identity behaviour:

Use a sequence object

This is not possible for me because:

a) I am using the same database in SQL Server 2008 and 2012. I can't use sequence in 2008.

b) If I go with sequence then I need to change the save procedure for each table, which would be a bulky task for us.
Use Trace Flag 272 (-T272)

I can go with this solution because there is no need to make any changes to my application. Someone suggested adding -T272 as a start-up parameter, after this SQL Server identity will work as in previous versions. I did the same but it is not working.

I don't want to make any changes to my database structure. Please suggest solutions or explain why -T272 is not working.

I found that it is a new feature (don't know what is use of it)

Before SQL Server 2012, identity allocations were always individually logged (as each value was used). This per-row logging activity could limit throughput in scenarios where many identity values are generated in a short space of time. To improve efficiency, SQL Server 2012 (and later) logs only the allocation of a batch of identity values. The allocated range is cached and issued on demand until a new batch of values is needed.
If SQL Server is restarted without the database containing the identity object being checkpointed on shut down, any remaining unused values in the cached range are lost, resulting in a jump in the values on restart.
Unfortunately, common ways of shutting down SQL Server 2012 do not currently automatically checkpoint databases (this contradicts the documentation, so it should be fixed at some point in the future). To avoid this particular cause of jumps in assigned identity values on SQL Server 2012, always shut down SQL Server using the T-SQL command SHUTDOWN (without the NOWAIT option).
The SHUTDOWN command will checkpoint all user databases correctly before shutting down the server. Do not use the Windows Service Control application, SQL Server Configuration Manager, the SQL Server Management Studio UI, or any other method.
You could also manually CHECKPOINT all databases before shutting down the SQL Server using any other method, but this requires you ensure no identity-allocation activity occurs in any database after your checkpoints, and before the shutdown completes. This may not be easy to achieve reliably.
More information and background in Lost Identity by Kalen Delaney.

Trace flag 272 is now documented in DBCC TRACEON - Trace Flags (Transact-SQL):

Disables identity pre-allocation to avoid gaps in the values of an identity column in cases where the server restarts unexpectedly or fails over to a secondary server. Note that identity caching is used to improve INSERT performance on tables with identity columns.
Note: Starting with SQL Server 2017, to accomplish this at the database level, see the IDENTITY_CACHE option in ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL).
Scope: global only


Gather values from different related rows with JSONB fields Say I have the following table structure:

jobs
+----+------------+
| id | some_field |
+----+------------+
|  1 |  some_val  |
|  2 |  some_val  |
|  3 |  some_val  |
+----+------------+

events
+----+--------+----------------------------------------+
| id | job_id |            payload (JSONB)             |
+----+--------+----------------------------------------+
|  1 |      1 | {'type':1, 'a':'some_val', ...}        |
|  2 |      1 | {'type':2, 'c':'some_other_val1', ...} |
|  3 |      2 | {'type':1, 'a':'some_other_val2', ...} |
|  4 |      2 | {'type':1, 'a':'some_other_val3', ...} |
|  5 |      3 | {'type':1, 'a':'some_other_val4', ...} |
|  6 |      3 | {'type':1, 'a':'some_other_val5', ...} |
|  7 |      3 | {'type':2, 'c':'some_other_val6', ...} |
|  8 |      3 | {'type':3, 'd':'some_other_val7', ...} |
+----+--------+----------------------------------------+

I want to create a query/view that ...

... selects one row for each job IF it has an event with payload->>'type' = 2
... in that row, shows values from other events that are associated with this job.
...  in that row, shows values from other events that are associated with this job if that event type occurs multiple times.

For example, with the above data I'd like to select all jobs that have an event of type 2, and find out the value of the d field from events with type 3 and some aggregate (say, COUNT()) of the a field from events with type 1. The result would be:

Results
+--------+------------------+----------+
| job_id |        d         | COUNT(a) |
+--------+------------------+----------+
|      1 |  NULL            |        1 |
|      3 |  some_other_val7 |        2 |
+--------+------------------+----------+

As a bonus: Each job can have only one event of type 2, so the basic query can be structured like:

SELECT
  job.id
FROM
  event
LEFT JOIN
    job ON event.job_id = job.id
WHERE
  event.payload ->> 'type' = 2;

How can I get the result table? Do I need to do sub queries for each field/event type, which would result in relatively bad performance, or can I use some GROUP BY job_id on the event table and maybe join the JSONs? (Note that my data is more complex and has many more fields in the payload that I would like to select).

Here is a DB Fiddle with the example data and my test query:

CREATE TABLE job (
      id SERIAL PRIMARY KEY,
      some_field TEXT
);

CREATE TABLE event (
      id SERIAL PRIMARY KEY,
      job_id INTEGER,
      payload JSONB
);

INSERT INTO job (some_field) VALUES ('val1'), ('val2'), ('val3');
INSERT INTO event (job_id, payload) VALUES
    (1, '{"type":1, "a":"some_val"}'::json),
    (1, '{"type":2, "c":"some_other_val1"}'::json),
    (2, '{"type":1, "a":"some_other_val2"}'::json),
    (2, '{"type":1, "a":"some_other_val3"}'::json),
    (3, '{"type":1, "a":"some_other_val4"}'::json),
    (3, '{"type":1, "a":"some_other_val5"}'::json),
    (3, '{"type":2, "c":"some_other_val6"}'::json),
    (3, '{"type":3, "d":"some_other_val7"}'::json);

SELECT job.id, job.some_field
FROM event
LEFT JOIN  job
     ON event.job_id = job.id WHERE event.payload ->> 'type' = '2';

id | some_field
-: | :---------
 1 | val1
 3 | val3

db<>fiddle here

As per comments, you need a timestamp field and I've added it to your sample data.  Now IMHO you should first unnest your jsonb data. I've used a SUM(CASE for this purpose and it calculates too the minimum timestamp value.

SELECT
    event.job_id,
    job.some_field,
    event.payload->>'type' AS type,
    SUM(CASE WHEN event.payload->>'a' IS NOT NULL THEN 1 ELSE 0 END) AS a,
    SUM(CASE WHEN event.payload->>'b' IS NOT NULL THEN 1 ELSE 0 END) AS b,
    SUM(CASE WHEN event.payload->>'c' IS NOT NULL THEN 1 ELSE 0 END) AS c,
    SUM(CASE WHEN event.payload->>'d' IS NOT NULL THEN 1 ELSE 0 END) AS d,
    MIN((event.payload->>'ts'::text)::timestamp) as min_ts
FROM
    event
JOIN
    job
    ON event.job_id = job.id
GROUP BY
    event.job_id,
    job.some_field,
    event.payload->>'type'
ORDER BY
    1, 2;

This is the result:

job_id | some_field | type |  a |  b |  c |  d | min_ts
-----: | :--------- | :--- | -: | -: | -: | -: | :------------------
     1 | val1       | 1    |  1 |  0 |  0 |  0 | 2004-10-19 10:23:54
     1 | val1       | 2    |  0 |  0 |  1 |  0 | 2002-10-19 10:23:54
     2 | val2       | 1    |  2 |  0 |  0 |  0 | 2003-10-19 10:23:54
     3 | val3       | 1    |  2 |  0 |  0 |  0 | 2004-10-19 10:23:54
     3 | val3       | 2    |  0 |  0 |  1 |  0 | 2005-10-19 10:23:54
     3 | val3       | 3    |  0 |  0 |  0 |  1 | 2006-10-19 10:23:54

db<>fiddle here

Starting at this point you can easily filter it by type and get the desired result.


MySQL 5.6.17 running out of memory when opening table with many partitions We are currently setting up a MySQL database (version 5.6.17) on an Amazon db.m1.large RDS instance for storing time series data. Each time series property of an object is stored in a separate InnoDB table (e.g. inside temperature of building 42 is stored in table building__inside_temperature). There are about 100 time series tables and each has three columns:

timestamp in milliseconds as bigint
ID of object (as char(22))
numerical value (as double)

The primary key is (timestamp, ID). We use range partitioning on the timestamp column with one partition per day for 2.5 years and key sub-partitioning on the ID with 8 sub-partitions. This results in about 800 partitions with 8 sub-partitions each per table.
We use innodb_file_per_table=OFF since there would be too many open files otherwise. The other MySQL parameters are left untouched.

When connecting with 100 threads (and connections) in parallel and opening a time series table, the MySQL instance runs out of memory. The process list shows all threads in 'Opening table' state before crashing. With less partitions or less parallel connections opening the table, the problem disappears.
We use the following mysqlslap command for that:
mysqlslap --create=create_timeseries.sql --delimiter=";" --query=insert_datapoint.sql --number-of-queries=1 --concurrency=100

Content of the files is here: http://pastebin.com/x4YMMF3h

The error is the following:

2014-08-12 06:25:53 26178 [ERROR] InnoDB: InnoDB: Unable to allocate memory of size 1208.

2014-08-12 06:25:53 2b119160d7002014-08-12 06:25:53 26178 [ERROR] InnoDB: InnoDB: Unable to allocate memory of size 1248.

2014-08-12 06:25:53 2b11913c4700 InnoDB: Assertion failure in thread 47354451085056 in file ha_innodb.cc line 17080
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
06:25:53 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

key_buffer_size=16777216
read_buffer_size=262144
max_used_connections=102
max_threads=604
thread_count=102
connection_count=102
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 333908 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x13aeb5a80
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
2014-08-12 06:25:53 26178 [ERROR] InnoDB: InnoDB: Unable to allocate memory of size 3784.

2014-08-12 06:25:53 2b1192035700 InnoDB: Assertion failure in thread 47354464130816 in file ha_innodb.cc line 17080
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.

http://dev.mysql.com/doc/refman/5.6/en/table-cache.html just tells, that each MySQL thread opens a table independently.

Why is opening a table with many partitions so memory-intensive?
How can I see how many times a table is opened and how much memory this consumes? show open tables from mysqlslap; does not give me per-thread information and no memory consumption.

I found out that due to this bug, MySQL 5.6.17 uses 6kb memory per open subpartition. This would result in about 500 GB of memory usage for 100 connections each having 100 tables with 800 partitions each, with 8 subpartitions each.


Why is a max degree of parallelism 1 recommended and used for Sharepoint in SQL Server? I am new to Sharepoint, but I used to work with SQL Server a lot.
I realized that Sharepoint changes some of the SQL Server settings to non default values.
One is the max degree of parallelism, why doesn't sharepoint like parallel plan executions?
Also why should I turn off "auto update statistics?
I couldn't find answers to these two questions.
I am on Sharepoint 2013

A busy SharePoint site is expected to have a lot of users which generate a huge volume of simple database requests.

If you had 8 cores and 200 users, you'd fill the queues parallelising just a few user's worth of workloads. By forcing each workload onto one core, you are ensuring a better "minimum standard" experience for everyone while reducing the time wasted due to context switching where it wasn't strictly necessary (as those queries were simple and would finish quickly enough on one core anyway).

That's my understanding of it.

Regarding the indexing, that's done by Sharepoint when it installs/upgrades and you don't need to touch it. As Shanky and Max posted, Sharepoint (and some other high-end Microsoft products like SCOM) manages maintenance on itself.

I still do weekly maintenance on top of that to keep it standard across my servers. It doesn't break anything.


SSMS Disk Usage report does not show auto grow events I have for some time suspected that the Disk Usage report in SSMS have stopped showing autogrow events. To test I created an empty database with a very small auto grow setting. I then inserted a lot of data into a table in the DB so that I knew it would have to grow. The file was bigger when looking at file properties of the DB, but the Disk Usage report did not show any autogrow events.

I then tried running Aaron Bertrand's script from this post, and that clearly shows every autogrow event.

So, why will not the Disk Usage report show the same growth? Any suggestions?
 Btw, it is SQL Server 2014 SP2.

The logic for the disk usage report is baked into SSMS and while we can't know what the RDL looks like (and if any filtering is done) I grabbed this query sent by SSMS 2016 using Profiler:

if (select convert(int,value_in_use) from sys.configurations where name = 'default trace enabled' ) = 1
begin
declare @curr_tracefilename varchar(500) ;
declare @base_tracefilename varchar(500) ;
declare @indx int ;

select @curr_tracefilename = path from sys.traces where is_default = 1 ;
set @curr_tracefilename = reverse(@curr_tracefilename);
select @indx  = patindex('%\%', @curr_tracefilename) ;
set @curr_tracefilename = reverse(@curr_tracefilename) ;
set @base_tracefilename = left( @curr_tracefilename,len(@curr_tracefilename) - @indx) + '\log.trc' ;

    select  (dense_rank() over (order by StartTime desc))%2 as l1
,       convert(int, EventClass) as EventClass
,       DatabaseName
,       Filename
,       (Duration/1000) as Duration
,       StartTime
,       EndTime
,       (IntegerData*8.0/1024) as ChangeInSize
from ::fn_trace_gettable( @base_tracefilename, default )
left outer join sys.databases as d on (d.name = DB_NAME())
where EventClass >=  92      and EventClass <=  95        and ServerName = @@servername   and DatabaseName = db_name()  and (d.create_date < EndTime)
order by StartTime desc ;

This isn't substantially different to Aaron's script and I don't see how it could return different results. It looks to me that even if the traces roll over, it will still be iterating all of them (and they wouldn't roll over 5x in the space of a few minutes or even hours of testing).


MySQL performance of applying unique constraint single column vs combination column I have a large MySQL InnoDB table containing around 137 million rows.

I need to add a unique constraint to the table. We have two options:

1) A combination index on three columns, one of which will be new and initially entirely NULL. The column definitions in combination the index would be:

`identifier` int(6) unsigned NOT NULL,
`value` varchar(255) NOT NULL,
`is_unique` tinyint(1) DEFAULT NULL, -- this is the new column

2) A unique index on a new column containing a unique md5 hash (calculated in the code) The definition would be:

`unique_key` CHAR(32) DEFAULT NULL

It would be easier for us to take option one as the code would be simpler, however I am wondering if there is a difference in how long it would take to apply the index in both cases. Does it take significantly longer to apply a combination unique index to a table (one column completely NULL) vs applying a unique index to a single column (completely NULL)? Or is there no difference? I know that MySQL will consider NULL values in unique indexes to not be the same (we need that behavior). If there is a big difference in the time that will affect the solution we go for.

Other Info

There are currently five indexes (including one combination index) on the table and it has four foreign key constraints to other tables.

MySQL version 5.1.69-enterprise-commercial-advanced-log.

The duration of the creation of the index will be directly related to the size of the index. I don't think it would be wildly different for the 2 options provided. Under the hood MySQL is going to copy the table to recreate it in the new structure, so consider the amount of time MySQL will take to read the data and write the data. The process is single threaded too. If your storage is slow this will take some time.

Tips:
- Ensure that fast_index_creation is enabled
- Group multiple ALTERs
- use pt-online-schema-change tool to perform it online. This will take longer but will permit you to continue to use the table.


How can streaming replication be kept lagged by a certain 'delay' time? Postgres has had streaming replication since 9.0 and since 9.1 it is possible to pause and resume the application of the streamed WAL records using pg_xlog_replay_pause() and pg_xlog_replay_resume(). As far as I can tell, there is no built-in way of keeping the application of WAL deliberately lagged by a specific time period (eg 24h).

Have I missed a built-in way of achieving this and if not, how should I approach doing it some other way?

Streaming replication cannot do this (but see Craig Ringer's comment above about a possibly upcoming feature).

The good news is, if you make the standby getting the WAL files and apply them, you can apply an arbitrary delay. We push the WALs to a NFS volume and the restore command copies them from there to the local WAL dir, checking if the files are old enough.  Then the normal restore process applies them as usual.

Of course, the delay will match the preset value only approximately, as a single WAL file may contain changes from quite different time intervals, depending on the master's workload.

What you propose with pausing the restore process depending on the value returned by pg_last_xact_replay_timestamp() may work if run from a scheduled job (probably you want to schedule it to run quite frequently).  So far I haven't seen such a setup and am wondering if it can be a production-grade solution.


Can I Create a Database and Tables in One SQL Command From PHP? I tried separating the CREATE DATABASE and CREATE TABLES commands into different mysqli_query statements. The Database gets created but I get an error with:

$create_tables = "CREATE TABLE T2T_$grouporg.Memebers LIKE T2T_Main.Members;
     CREATE TABLE T2T_$grouporg.Events LIKE T2T_Main.Events;
     CREATE TABLE T2T_$grouporg.Taskers LIKE T2T_Main.Taskers";
     $creat_tables = mysqli_query($dbconnect,$create_tables) or
     die("Create Table Error: " . mysqli_error($dbconnect) .
     "

" . $create_tables);

You cannot execute multiple MySQL statements from PHP with mysqli_query, but you can with mysqli_multi_query!


Which version of linux OS has support for MongoDB installation? I would like to learn how to install MongoDB in my Windows machine. Currently I have a Windows 8 64 bit OS laptop and I installed MongoDB 64 version for Windows.

My question is: which version of linux (64 bit) supports my machine to install MongoDB.
I known Ubuntu linux only. Is it suitable for installing on my machine? Currently my machine has 64 bit version.

Thanks in advance for clarifying my doubt.

You should be able to easily install on most (if not all) flavours of x86-based Linux distributions.

There are officially packaged versions for:

Ubuntu
Debian
RPM (Redhat, CentOS, Fedora, Amazon Linux)

You can also install from tarball, or check if there are packages available in the "ports" equivalent for your distribution.


Unexpected behaviour with InnoDB Transaction retry This is going to be difficult to reproduce but hopefully someone can shed some light on my problem based on the logic involved.  After having some intermittent issues with deadlocks during a transaction I implemented a retry strategy that goes goes something like this (PHP):
public function execute_query($sql) {
    $try     = 1;
    $log_msg = '';
    while (true) {
        $query     = $this->link->query($sql);
        $error_no  = $this->link->errno;
        $error_msg = $this->link->error;
        if (!$error_no){
            if ($try > 1) {
                $this->debug_log->write('Deadlock Succeeded after ' . $try . ' Tries ::: ' . $sql);
            }
            return $query;
        } else {
            $log_msg = 'Error: ' . $error_msg . ' ::: Error No: ' . $error_no . ' ::: ' . ($try > 1 ? $try . ' Tries ::: ' : '') . $sql;
            $this->debug_log->write($log_msg);
            if ($error_no == 1213 && $try < self::DEADLOCK_RETRY) {
                // retry when deadlock occurs
                sleep($try * 2);
                $try++;
            } else {
                throw new ErrorException($log_msg);
                exit();
            }
        }
    }
}

This seems to have had the exact opposite affect that I'd hoped for - which is that it appears the first half of my transaction gets rolled back but then the second half commits.
For example:
START TRANSACTION;
Query 1
Query 2
Query 3 (deadlock occurs, query gets retried and succeeds on 2nd attempt)
Query 4
Query 5
COMMIT;

At the end of all of this I'm left with Query 3, 4, and 5 running successfully but Query 1 and 2 do not.  I don't understand how this is possible but it's happened twice now and I haven't been able to reproduce the deadlock to test or develop a working strategy.
Can anyone explain why retrying a failed query within an InnoDB transaction would cause half of the transaction to get rolled back and the other half to commit?

When the deadlock occurs at query 3, the deadlock may involve query 1 or 2.  You must start over with the START TRANSACTION and replay all the queries.
If any of those are SELECTs and the result of the SELECT controls what will be subsequently UPDATEd, DELETEd, etc, then do you have FOR UPDATE on the SELECT?
You must check for errors after each and every query in the transaction.  If a deadlock occurs, start over, as mentioned above.
What happened

The error occurred, so it rolled back the transaction as far as it had gone.
The client code ignored the error and plowed ahead.
Queries 4,5,6 were run and committed.

What was needed

The error occurred, so it rolled back the transaction as far as it had gone.
The client code noticed the error.
The client code should have code to ROLLBACK and jump back to the START.

(The explicit ROLLBACK is probably redundant but harmless.  However, branching back to the START is up to the client code.)
A "deadlock" should be handled that way, but any other "error" should be handled with an abort.
And, since a deadlock can occur after virtually any query, you should test everywhere.


Can a database detect that data is out of sequence if each datapoint has a sequential ID Can a database detect that data is out of sequence if each datapoint has a sequential ID?
E.G A trading DB server receives trade ID 9, and next receives trade ID 11. In this case, I would like the DB to return an error because trade ID 10 is missing. Is this possible? I don't need the code, just some context and the name of the solution. E.G. would this be some sort of DB input validation command, and how would it be enabled?

Weird question. If you don't set a sequential ID as autonumber/autoincrement but sequence must not break, you should code it yourself.a) Don't allow user-entry for the field and add a (max(field)+1) either by an insert trigger or Stored Proc. (BTW it's always a good idea to deny direct access to tables and all insert/update/etc is done by e.g. SPs).
b) If must allow users to add field's value, validate it (insert trigger or even better if it's an "insert SP" with validation logic -- just as I described above) and rollback insert if ID is not prevID+1.But what's the point? What is your goal with this 'sequence logic' you are about to implement?If "trade data" you mention must be in sequence but it's not always true you should look into the business logic to see1) why and how the sequence breaks2) what is the best behavior of app/db if it happens (from a business point of view! - e.g. renumber? throw error but still save data? save data with timestamp and maintain data integrity at the end of the day? etc)3) how to avoid this -if possible- to happen in the futureSo IMHO focus on business logic and customer requirements, figure out what fits best their needs then code that.


how to simulate a low-memory state when running a query I am trying to better understand how SQL Server's execution engine behaves in a low-memory state.  Speaking in terms of memory grants, I am wondering if there is some way to force GrantedMemory to be equal to RequiredMemory.  (My guess is that there's an undocumented trace flag which will do that.  Does anyone know what it is?)

If you're on SQL Server 2012 SP3 or better, you can use the MAX_GRANT_PERCENT hint to cap a single query's memory grant. However, you'd have to mess with the number to figure out which percent gives you the correct value (to force GrantedMemory to be equal to RequiredMemory).

See here for more details.

Prior to that, you'd have to use Resource Governor (Enterprise only though).


What Mechanism does MySQL use to schedule Backups on Windows Server 2008? When scheduling database backups on MySQL Server (v5.1.45-community) running on Windows Server 2008, what mechanism is used to trigger the backups to run?  I think it's using the Windows Task Scheduler, but I don't see any obvious tasks for these backups.  SQL Server uses a separate agent service. Is there something similar in MySQL?

ADDITIONAL INFO: The backups were setup with MySQL Administrator.  I'm aware of how to schedule the backups, but they're not running and I'm looking for insight as to where to begin trouble shooting.

Are you running MySQL Administrator? That includes a backup scheduling mechanism.


What is the most effective way to compress and store a SQL Server backup? I've been doing some testing of different methods for compressing and storing SQL Server  backups (using SQL Server 2008 R2 Enterprise edition), and I'm wondering what the most effective compression algorithm is for long term storage of those backups, outside of SQL's internal compression algorithms.

I'm not worried about the physical storage or tape drives or anything, just trying to turn our 3TB of data and log files into the smallest single file I can.

So, for example, would a .zip or .7z? Or are there too many variables within my database to be able to accurately estimate what will be the most effective and I'll just need to do some tests? Or is SQL Server's internal compression the best I'll get?

I've been doing some testing of different methods for compressing and storing MS SQL Backups (using MS SQL 2008 R2 Enterprise edition), and I'm wondering what the most effective compression algorithm is for long term storage of those backups, outside of SQL's internal compression algorithms.

Since you are using SQL 2008 R2 Enterprise edition, you can/must leverage

Data Compression - Row and Page compression. at Data level
Backup Compression at taking backup to minimize the disk footprint of the backups.

USE master;
GO
EXEC sp_configure 'backup compression default', '1';
RECONFIGURE WITH OVERRIDE;

Backup compression uses CPU cycles to compress the data before it leaves the server, and that’s why in the vast majority of scenarios, compressed backups are faster than uncompressed backups.

Note that when you use Open source tools you need to uncompress the database backup file before you can start the restore process it self.

e.g: When you receive a SQL database backup of 50 Gb which is compressed to 5 GB. To restore this database, you need much more diskspace:

5 Gb for the zip file
50 Gb for the backup file
50 Gb for the restored database. (assume their is no empty space in the database)

In total 105 Gb of diskspace is needed.

You can still use opensource compression tools like gzip , 7Zip, bzip2 or QuickLZ after the backup compression to benefit.

Also, have a look at MSSQL Compressed Backup on codeplex.

Good references for comparison stats

File Compression in the Multi-Core Era.
comparison of compression methods for database dumps


Multiple left joins or subquery? I'm working on joining multiple tables together to extract some data related to user "runs", this includes basics like runID, username etc but also joins to the run times and favourites.

I have the query below which uses a LEFT JOIN for the user_run_times and run_favourites table.

The query should show all runs for the user, the latest date ran, if it's a favourite, and the quickest run time - All of which is displayed, but if more than one time is logged the favourites count goes above one (should only ever be one or zero).

SELECT
    user_runs.userID,
    user_runs.runID,
    `createdBy`,
    `username`,
    DATE_FORMAT(`dateCreated`,'%d-%m-%Y') `dateCreated`,
    COUNT(runFavouriteID) AS favourite,
    DATE_FORMAT(MAX(`runDate`),'%d-%m-%Y') AS `lastRan`,
    MIN(`runTime`) AS `fastestTime`
FROM
    `user_runs`
    INNER JOIN `runs` ON user_runs.runID = runs.runID
    INNER JOIN `users` ON users.userID = runs.createdBy
    LEFT OUTER JOIN `user_run_times` ON user_run_times.runID = user_runs.runID
    LEFT OUTER JOIN `run_favourites` ON run_favourites.runID = user_runs.runID
WHERE
    user_runs.userID = 4
GROUP BY
    runID, user_run_times.runID;

I'm looking for some suggestions on how to improve this query? The current output is:

UserID   runID    createdBy   username   dateCreated   favourite   lastRan       fastestTime
4        3        3           bob        11-12-2011    3           27-01-2012    36920030

The column favourite should only display one (as there is one row in run_favourites) but it shows three (two for user_run_times and one for run_favourites)

Hopefully I haven't made this too confusing, if somebody could suggest how I can output the table structure in a "text" format like most on here then I will also post those.

Any advice appreciated, thanks! :)

This is my best shot without seeing any table structures

SELECT
    user_runs.userID,
    user_runs.runID,
    `createdBy`,
    `username`,
    DATE_FORMAT(`dateCreated`,'%d-%m-%Y') `dateCreated`,
    COUNT(DISTINCT runFavouriteID) AS favourite,
    DATE_FORMAT(MAX(`runDate`),'%d-%m-%Y') AS `lastRan`,
    MIN(`runTime`) AS `fastestTime`
FROM
    (SELECT * FROM `user_runs` WHERE userID=4) user_runs
    INNER JOIN `runs` ON user_runs.runID = runs.runID
    INNER JOIN `users` ON users.userID = runs.createdBy
    LEFT OUTER JOIN `user_run_times` ON user_run_times.runID = user_runs.runID
    LEFT OUTER JOIN `run_favourites` ON run_favourites.runID = user_runs.runID
GROUP BY
    user_runs.runID,`createdBy`,`username`,`dateCreated`;

You will need these indexes

ALTER TABLE user_runs ADD INDEX userID_runID_ndx (userID,runID);
ALTER TABLE runs      ADD INDEX runID_createdBy_ndx (runID,createdBy);
ALTER TABLE runs      ADD INDEX createdBy_runID_ndx (createdBy,runID);


Select into existing table where ID is in another table I have one table containing ~35k IDs (TableC), one table that features multiple columns (TableA), amongst others the ID from the before mentioned table and a third empty table, featuring the same columns as the second table (TableB).

Now I'm required to copy every entry from TableA to TableB where the ID of TableA is in TableC. I came up with this query:

SELECT * INTO TableB
FROM TableA
WHERE ID IN
(SELECT ID FROM TableC)

But this leads to the error There is already an object named 'TableB' in the database... I'm not allowed to delete and re-create TableB for this.

Working on Microsoft SQL Server Enterprise Edition v9.00.3042.00, i.e. SQL Server 2005 Service Pack 2

How can I copy  every entry from TableA to TableB where the ID of TableA is in TableC?

The SELECT ... INTO syntax is only for new tables.
I think you want:

INSERT dbo.TableB ( <column list> )
SELECT <column list>
FROM dbo.TableA
WHERE ID IN
  (
    SELECT ID
    FROM dbo.TableC
  ) ;


How to order the where clause and join columns in a covering index? Simple query example:

Select t1.A, t1.B, t1.C, t1.CustomerID
from table1 t1
join table2 t2 on t1.CustomerID = t2.Id
where t1.A = 'x'
and t1.B = 'xx'
and t1.C = 'xxx'

I created a non clustered with A, B, C, CustomerID for table1

I turned on statistic io. I recreated the same index and moved the order of the columns around and ran the queries at least twice for each order and checked the logical read count. After several trials, I noticed that one order:
A, B, CustomerID, C produced the least number of logical reads, instead of something like A, B, C, CustomerID. I know this is a simple query and I haven't indicated the schema.

In general, it seems the order matters but I thought if I placed the where clause columns together, it doesn't matter in what order as SQL Server will figure it out. In the case above, the join column came between the where clause columsn and had an effect. I am not sure why? Does column selectivity matter in deciding where to place the column in the order? What are other considerations? Right now I am just doing it by trial and error.

If all your filters are equality matches (such as =, rather than >, LIKE, !=, etc), then it's going to useful to list them first. Then the data that matches those three conditions will be easily locatable, and in CustomerID order, which gives good options for being used in a join. Remember that your matching on CustomerID needs to join on several things.

Oh, and if your CustomerID is non-nullable and has a FK, then the Query Optimizer might not even be looking at t2.


MySQL Difference between BEGIN and START TRANSACTION I'm using RDS Aurora (MySQL 5.6)

By default Auto Commit flag is enabled.
I have a transaction as follows

BEGIN;
INSERT INTO .....;// STATEMENT 1
INSERT INTO ......;//STATEMENT 2
COMMIT;

I'm expecting both the Inserts to be commited only when COMMIT is executed.
But Statement 1 is Commiting as soon as it got executed.

So I wanted to know What does BEGIN do here ??

I was assuming that BEGIN does the task of disabling Auto Commit , Can someone also explain the difference between BEGIN and START TRANSACTION as well.

The manual has this to say:

BEGIN and BEGIN WORK are supported as aliases of START TRANSACTION for initiating a transaction. START TRANSACTION is standard SQL syntax, is the recommended way to start an ad-hoc transaction, and permits modifiers that BEGIN does not.
The BEGIN statement differs from the use of the BEGIN keyword that starts a BEGIN ... END compound statement. The latter does not begin a transaction. See Section 13.6.1, “BEGIN ... END Compound Statement”.


Peculiar Oracle outer join syntax case I have seen the following in a query that was supposed to be ported from Oracle outer join syntax to SQL standard outer join syntax:

SELECT ...
FROM A, B, C, D, E
WHERE A.A_ID = B.A_ID
AND B.B_ID = C.A_ID(+)
AND B.B_KEY = C.B_KEY(+)
AND C.C_ID = D.C_ID(+)
AND B.A_ID = E.A_ID(+)
AND B.B_KEY = E.B_KEY(+)
AND 'CONSTANT' = C.X_ID(+)

Now translating the outer join syntax is normally quite a mechanical process, but that last line had me baffled.  What does it mean?  What effect does it have?

I tried to perform the mechanical process. I hope I remember it right.

This leads to:

SELECT ...
FROM A
         join B on A.A_ID = B.A_ID
    left join C on B.B_ID = C.A_ID and B.B_KEY = C.B_KEY and 'CONSTANT' = C.X_ID
    left join D on C.C_ID = D.C_ID
    left join E on B.A_ID = E.A_ID and B.B_KEY = E.B_KEY

In short I think Leigh Riffel's answer is correct.

Note

in old days the rule to memorize was:
oracle where A.a = B.b (+)
becomes A.a *= B.b in SQL-Server old syntax the plus goes to the opposite side and becomes a star,
which means A left join B on A.a = B.b


SSIS Raw Data Import Into SQL Server 2008 R2 - Truncation and Condensed Data Type [Intro]
I have written a two pack SSIS solution to perform a scheduled hourly update of data from multiple tables in a single db (SRC) to single table on another db (TRG).

I get the following errors and I understand them:

[ADO NET Destination [328]] Error: An exception has occurred during data insertion,
    the message returned from the provider is:
* String or binary data would be truncated.
* The statement has been terminated.

I Know that the data from SRC in some columns is too large for the data in the TRG matching column.  For instance SRC.CompositeAddress is nvarchar(50) and TRG.Addr is char(6)  (same size as Condensed Data Type) which does not make sense for an address

[Question]
My question revolves around not really understanding the condensed data type.  Aside from data in SRC being too large for data in TRG I am wondering if condensed data type is affecting my SSIS import?

AFAIK a condensed type is only an alias used in Management Studio, it's nothing else then the definition of the Type including length information (instead of having two fields: Type and Length - Varchar and 60, you have only one = Varchar(60)).

Now, that error of yours happens obviously because of data truncation. In order for it to pass, you need the original and the source to be of the same type and length (either NVARCHAR(50) or CHAR(6)). For the length it's more important that the source is smaller than the destination.

So you need to pick your necessary type (according to the input data or the destination) and make them both similar. If the input data needs a cleanup, then you better prepare it before inserting it.


Where is my blob stored? Using oracle 11,

Although a lot was already written about blobs, there is something that is not still clear for me.

I was told that by default blobs stored in row if the are < 3900. Does it means that for a single table, some blobs will be stored in row and some others won't?
Is there a way to check whether they are stored in row or not?
If I set ENABLE STORAGE IN ROW, does it means that whatever the size the blob has, it will be save within the same segment than the other data?

Oracle Doc says:

  "When the size of the LOB stored in the given row is small,
  approximately 4000 bytes or less, and you either explicitly specify
  ENABLE STORAGE IN ROW or the LOB storage clause when you create the
  table, or when you do not specify this parameter (which is the
  default)."

I got something finally,

After further reading the DOC and making some test:

Yes, by default, depending on the size of the row, some blobs will be stored in row and other out of row.
Yes, following this you should be able to determinate it:

  LOB values are stored out-of-line when any of the following situations
  apply:

  If you explicitly specify DISABLE STORAGE IN ROW for the LOB storage    clause when you create the table.
  If the size of the LOB is greater than approximately 4000 bytes (4000    minus system control information), regardless of the LOB
  storage    properties for the column.
  If you update a LOB that is stored out-of-line and the resulting LOB    is less than approximately 4000 bytes, it is still stored out-of-line.

  LOB values are stored inline when any of the following conditions
  apply:

  When the size of the LOB stored in the given row is small,    approximately 4000 bytes or less, and you either explicitly specify ENABLE STORAGE IN ROW or the LOB storage clause when you create the table, or when you do not specify this parameter (which is the
  default).
  When the LOB value is NULL (regardless of the LOB storage properties    for the column).

No, this is just the default option, when the blob is too big(arpox 3800) it will be stored out of row


Is there a limit on the number of blobs storable in SQL Server with filestream? I use filestream to store blobs (files). I use SQL Server 2008 R2

Some customers have 60k files (50GB). They ask me if there is a limit after which, also because of NTFS, there will be problems.

Somehow the doubt is: if the number of blobs grows too much is there a limit beyond which SQL Server will not handle them properly or not?

I think SQL Server handles this well, but does anyone has a better reply?

Thanks

60.000 files and 50 GB should be absolutely no problem. You might want to read "Special Consideration for Large Environments".

A challenge could be to come up with a recovery plan.


Surrogate key / Primary key: Better to use an existing unique data field or create a key field? I am not sure if this question has been asked or not. At least I couldn't find it.

I am curious about a primary key in terms of efficiency with data searching and retrieval.

This is a hypothetical example...
I have a list of students with unique StudentID (say 10 digits long, given that the school will never have that many students) and StudentName (which is unique too).

Would it be better to use StudentID as primary key or create a new field for primary key (may be 6 - 8 digits or composite of characters and numbers. i.e. ATC1002)?

If I use StudentName as the primary key, will I see any retrieval performance degradation? If so, approximately how much in general? Are there any other factors which will have an impact on the selection of the primary field?

You should never assume that a data point which is outside of the control of your system will never change.  This means you shouldn't assume student names won't change.  There are lots of reasons in the real world why names might change.  Anything that is at reasonable risk of changing is a bad candidate for a primary key.  Also, names are very unlikely to be unique over a student population of any reasonable size.

Some exceptions to this might be things which are controlled by an external standards body which can be reasonably well trusted to maintain consistency.  This could include something the the IATA code for an airport or the symbol for an atomic element.

Regarding the efficiency of textual (natural) keys vs. integer (surrogate) keys, there is no easy answer to this as it depends on many factors.  On balance, it is fair to say that surrogate integer keys are more efficient than natural textual keys - especially if your textual values are much larger than a few characters.

Nevertheless, there are advantages to natural keys beyond raw file I/O and CPU cycles, as long as you can trust the natural keys to be stable.  A school's student ID is probably a pretty good candidate because you can institute an internal policy that says student IDs are granted for life and never change.


I've see at least two problems:
1. fields list doesn't match values.
2. serialized data doesn't quoted properly.

May be you have try native mysqldump instead of "Backup wizard"?

mysqldump \
-u USERNAME -pPASSWORD \
--opt                  \
--no-create-db         \
--comments             \
--complete-insert      \
--order-by-primary     \
DATABASENAME  > ./dbdump.sql

Keep in mind that there is no space between -p and PASSWORD.


Can't run pcs -f cluster1.xml stonith create with PostgreSQL 9.6 on CentOS 7.3 Follow this guide to do PostgreSQL Automatic Failover:

https://dalibo.github.io/PAF/Quick_Start-CentOS-7.html

At this step:

pcs -f cluster1.xml stonith create fence_vm_srv1 fence_virsh pcmk_host_check="static-list" pcmk_host_list="srv1" ipaddr="192.168.122.1" login="<username>" port="srv1-c7" action="off" identity_file="/root/.ssh/id_rsa"
pcs -f cluster1.xml stonith create fence_vm_srv2 fence_virsh pcmk_host_check="static-list" pcmk_host_list="srv2" ipaddr="192.168.122.1" login="<username>" port="srv2-c7" action="off" identity_file="/root/.ssh/id_rsa"
pcs -f cluster1.xml stonith create fence_vm_srv3 fence_virsh pcmk_host_check="static-list" pcmk_host_list="srv3" ipaddr="192.168.122.1" login="<username>" port="srv3-c7" action="off" identity_file="/root/.ssh/id_rsa"
pcs -f cluster1.xml constraint location fence_vm_srv1 avoids srv1=INFINITY
pcs -f cluster1.xml constraint location fence_vm_srv2 avoids srv2=INFINITY
pcs -f cluster1.xml constraint location fence_vm_srv3 avoids srv3=INFINITY

What I edited:

pcs -f cluster1.xml stonith create fence_vm_node1 fence_virsh pcmk_host_check="static-list" pcmk_host_list="node1" ipaddr="192.168.33.1" login="root" port="5432" action="off" identity_file="/root/.ssh/id_rsa"
pcs -f cluster1.xml stonith create fence_vm_node2 fence_virsh pcmk_host_check="static-list" pcmk_host_list="node2" ipaddr="192.168.33.1" login="root" port="5432" action="off" identity_file="/root/.ssh/id_rsa"
pcs -f cluster1.xml constraint location fence_vm_node1 avoids node1=INFINITY
pcs -f cluster1.xml constraint location fence_vm_node2 avoids node2=INFINITY

Got error:

[root@node1 ~]# pcs -f cluster1.xml stonith create fence_vm_node1 fence_virsh pcmk_host_check="static-list" pcmk_host_list="node1" ipaddr="192.168.33.1" login="root" port="5432" action="off" identity_file="/root/.ssh/id_rsa"
Error: Agent 'fence_virsh' not found, use --force to override
[root@node1 ~]# pcs -f cluster1.xml stonith create fence_vm_node2 fence_virsh pcmk_host_check="static-list" pcmk_host_list="node2" ipaddr="192.168.33.1" login="root" port="5432" action="off" identity_file="/root/.ssh/id_rsa"
Error: Agent 'fence_virsh' not found, use --force to override
[root@node1 ~]# pcs -f cluster1.xml constraint location fence_vm_node1 avoids node1=INFINITY
Error: Resource 'fence_vm_node1' does not exist
[root@node1 ~]# pcs -f cluster1.xml constraint location fence_vm_node2 avoids node2=INFINITY
Error: Resource 'fence_vm_node2' does not exist

There is not this IP: 192.168.33.1, but exist 192.168.33.111.

I think the port means PostgreSQL's port, changed it to 5432. Maybe not that meaning. So what's the right meaning?

Where was wrong?

Did you install the fence-agents package? That would provide you with the fence_virsh binary that pcs is complaining isn't there.


MySQL uses more than 500% of the CPU Everything was going fine, but since a couple of hours ago MySQL is using from 400% to 550% of the CPU.

I'm getting a lot of these errors:

1003 14:03:17 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/var/mysql_tmpfs/#sql_10ff_8.MYI'; try to repair it
141003 14:03:17 [ERROR] Got an error from unknown thread, /build/buildd/mysql-5.5-5.5.38/storage/myisam/mi_write.c:226

I have no broken tables.

Also, using iotop, I discovered that MySQL was writing files inside the temp directory at a speed of around 150MB/s, that seems too high to me.

After googling a while I discovered that the problem could be a full /tmp directory, so I've created a 4GB ramdisk mounted on "/var/mysql_tmpfs".

After I setted this ramdisk as MySQL's default temp folder the server was able to slowly process some requests. but still not even an half of what it usually handles.

What can I do to solve the problem?

All the software are updated.
The OS is Ubuntu Server 14.04.1 64-bit.
The machine has 6 cores and 32gb of RAM.

EDIT 1:

I've tried to manually run this query:

SELECT * FROM my_table WHERE ID = 400000

And it takes one second or more to output a result, obviusly I have an index in that column and there are around 450000 rows in that table.

The profiler says that 100% of the time has been spend on "Waiting for Table Level Lock", isn't it strange?

Create folder mysql_tmp on the biggest partition you have.
Add the line tmpdir = /path/to/the/mysql_tmp to the section [mysqld] of the mysql.conf.
restart mysql
perform REPAIR for all the tables in your working base