Archival strategy for a table with 25 million records in SQL Server 2008 R2 Enterprise I have a table that stores some audit information and currently has 25 million records. There is a process that inserts to this table every 30 minutes. This is historical data and is not used for any processing.

Since this table already has huge number of records so these 30 minutes inserts are taking considerable long time to complete.

According to me I should archive this huge table and only have a day or so data depending on the business conditions and move rest of the data to a archival table.

One possible strategy that I thought was renaming the existing table to something else and creating a new table with the same name as the old one. Can somebody comment on this strategy or suggest a better solution?

Please see this answer: SQL Server database design for "archived but available" data

As Remus comments, you are probably barking up the wrong tree here. 25M rows is a very small table and the INSERT really shouldn't take long. Perhaps it is worth investigating this first..


How can I prevent so many aborted clients in MySQL (increasing aborted_clients count)? I am running MySQL 5.6.14 on an Ubuntu server and noticed the error log constantly filling with:

  Aborted connection 17201 to db: 'somedb' user: 'sdb' host: 'x.x.x.x' (Got timeout reading communication packets)

(These happen about 10 times per minute. The counter is up to 13,000 after a server uptime of only 24 hours.)

We have perhaps twenty or thirty instances of a Windows client program that uses the .NET framework via Connector/NET (6.2.2.0) to connect to the database and I've made sure that every time it creates a connection (MySqlConnection.Open()) it is also properly closed (MySqlConnection.Close()).

I've adjusted various timeouts, max_allowed_packet, and other settings based on research on the web, but these still seem to persist.

Here are (what I believe are) the relevant settings:

connect_timeout = 20
wait_timeout = 60
net_read_timeout = 60
net_write_timeout = 60
max_allowed_packet = 33554432 // 32M

What can I do to stop these events from constantly happening?

It turns out that the MySQL Connector/NET behavior is at the heart of the matter.
By default, it will automatically create a pool of connections and reuse them as necessary. When you call .Open() a connection might be created anew, or you might get a connection that already exists but was idle (SLEEP). When you call .Close() the connection is returned to the pool.
There are a lot of other questions and answers which deal with the concept of "Should I create new connections for every action or should I reuse the same connection?" - the Connector/NET deals with that for you, so you should create, open and close them. The pooling is automatic and behind the scenes.
Something of importance here: The maximum number of connections that Connector/NET can create by default is 100. If you've got a lot of clients doing concurrent queries or what have you, it's possible the server could exceed its max_connections limit. If necessary you can limit the number of connections that Connector/NET creates by specifying a Maximum Pool Size option in its connection string options.
So, why all the aborted clients? The connections in the pool managed by Connector/NET are held open for longer than your server's wait_timeout, so the server closes them and considers them a client abort.
How long are they held open? Well, it depends on your Connector/NET version, but:

Starting with MySQL Connector/Net 6.2, there is a background job that runs every three minutes and removes connections from pool that have been idle (unused) for more than three minutes. The pool cleanup frees resources on both client and server side. This is because on the client side every connection uses a socket, and on the server side every connection uses a socket and a thread.
Prior to this change, connections were never removed from the pool, and the pool always contained the peak number of open connections. For example, a web application that peaked at 1000 concurrent database connections would consume 1000 threads and 1000 open sockets at the server, without ever freeing up those resources from the connection pool. Note, connections, no matter how old, will not be closed if the number of connections in the pool is less than or equal to the value set by the Min Pool Size connection string parameter.

-- Reference
Since you're using version 6.2.2.0, they're closed after three minutes by the client, which doesn't cause the server to treat it as a warning.
Solution: Increase wait_timeout to five minutes or so. Note: watch the number of connections to the server to make sure that having connections able to SLEEP longer doesn't cause the total number of connections to approach max_connections.


How to determine cause of runtime increase given two query plans with SpillToTempDb warning Two actual query plans were captured for two executions of one query:

  Plan "Fast" took ~1s and occurs about 90% of the time.

  Plan "Slow" took ~16s.

Since the graphical plans look identical to me, I dumped the XML versions and performed a diff to look at the text differences to be sure.

There are 2 "SpillToTempDb" warnings on the fast side and four on the slow side.  Looking that the 2 extra on the slow side:

One SpillToTempDb warning on "Parallelism (Repartition Streams) Cost 1%":

    <SpillToTempDb SpillLevel="0" />

On SpillToTempDb warning on "Parallelism (Distribute Streams) Cost 1%":

    <SpillToTempDb SpillLevel="0" />

The cost is the same (1%) for both the slow and fast cases.  Does that mean the warning can be ignored?  Is there a way to show "actual" times or costs.  That would be so much better!  Actual row counts are the same for the operation with the spill.

Besides performing a manual text diff of xml execution plans to find the differences in warnings, how can I tell what the 1500% increase in runtime is actually due to?

Differences except "RunTimeCountersPerThread" lines:

Left file: C:\Users\chrisr\Desktop\fast.sqlplan     Right file: C:\Users\chrisr\Desktop\slow.sqlplan
10                    <ThreadStat Branches="10" UsedThreads="85">
     10               <ThreadStat Branches="10" UsedThreads="73">
------------------------------------------------------------------------
------------------------------------------------------------------------
19                    <MemoryGrantInfo SerialRequiredMemory="1536" SerialDesiredMemory="10816" RequiredMemory="124224" DesiredMemory="133536" RequestedMemory="133536" GrantWaitTime="0" GrantedMemory="133536" MaxUsedMemory="105440" />
     19               <MemoryGrantInfo SerialRequiredMemory="1536" SerialDesiredMemory="10816" RequiredMemory="124224" DesiredMemory="133536" RequestedMemory="133536" GrantWaitTime="0" GrantedMemory="133536" MaxUsedMemory="107840" />

------------------------------------------------------------------------
     276                                                    <Warnings>
     277                                                      <SpillToTempDb SpillLevel="0" />
     278                                                    </Warnings>
------------------------------------------------------------------------------------------------------------------------------------------------
     630                                                                <Warnings>
     631                                                                  <SpillToTempDb SpillLevel="0" />
     632                                                                </Warnings>
------------------------------------------------------------------------

For slow case, tempdb before/after (select * sys.fn_virtualfilestats(db_id('tempdb'),null))
( only showing a few 100ms of latency ):

Should I be looking at the differences in the RunTimeCountersPerThread lines?  It appears it is just the order that is different between the Threads.

Possible hint (2013/04/26):  The query performs multiple inner merge joins and I found this on Paul White's blog:

  Exchange Spills

  Posted by:    Paul White NZ (view profile) Posted
  on:   Monday, March 07, 2011 at 3:29 AM

  ...these are almost always
  caused by a sequence of exchanges operation in order-preserving mode,
  separated by one or more operators that require sorted input e.g.
  Merge Join, Stream Aggregate, Segment...

  In SQL Server 2008 there is a Profiler event to show when this type of
  exchange 'deadlock' is resolved by spilling exchange packets to disk.

  The primary workaround is to avoid these plan shapes (especially
  parallel Merge Join at high DOP, in my experience). It is sometimes
  enough to force a hash or loop join instead using a hint, but this is
  advanced tuning and really requires detailed knowledge of the data
  concerned.

The cost is the same (1%) for both the slow and fast cases. Does that
  mean the warning can be ignored? Is there a way to show "actual" times
  or costs. That would be so much better! Actual row counts are the same
  for the operation with the spill.

The cost shown is always the optimizer's estimated cost of the iterator, computed according to its internal model. This model does not reflect your server's particular performance characteristics; it is an abstraction that happens to produce reasonable plan shapes most of the time for most queries on most systems. There is no way to show 'actual' costs/execution times per iterator.

  Besides performing a manual text diff of xml execution plans to find
  the differences in warnings, how can I tell what the 1500% increase in
  runtime is actually due to?

Typically, you can't. Spill warnings (sorts, hashes, exchanges) are new in execution plans for 2012, but they are just an indication of something you should investigate and look to eliminate if possible. The impact of a particular spill is something that needs to be measured - it is not possible to say that a spill of a particular type will always result in an x% performance drop for example.

  For slow case, tempdb before/after (select *
  sys.fn_virtualfilestats(db_id('tempdb'),null)) (only showing a few
  100ms of latency)

Spilling to tempdb and back is certainly undesirable, but the overall impact is hard to assess. For sort and hash spills, the impact is largely due to the I/O and access pattern, which may be small-block synchronous I/O e.g. for sort spills. With ~100ms of latency, you don't need too many synchronous I/Os to introduce a significant delay. The nature of the process and I/O patterns means tempdb spills can still be a problem on very low latency storage systems like fusion-io.

For exchange spills, there is an extra delay. The intra-query deadlock must be detected by the regular deadlock monitor, which by default only wakes up once every 5 seconds (more frequently if a deadlock has been found recently).

The resolver must then choose one or more victims, and spool exchange buffers to tempdb until the deadlock is resolved. The amount of spooling needed and the complexity of the deadlock will largely determine how long this takes.

Ultimately, preserved ordering is a Very Bad Thing for parallelism in general. Ideally, we want multiple concurrent threads operating on data streams with no inter-dependence. Preserving sort order introduces dependencies, so producer and consumer threads in different parallel branches can become deadlocked waiting for order-preserving iterators to receive rows to decide which input sorts next in sequence.

The precise nature of the deadlock depends on data distribution and per-thread sort order at runtime, so it is typically very hard to debug. Hence my recommendation to avoid order-preserving iterators in parallel plans, especially at high DOP. I do explain a very simplified example of an order-preserving parallel deadlock in some talks I do, but real examples are always more complex, though the underlying cause is the same.

In case the concepts are not familiar, it may help to follow the following example, reproduced from the (somewhat epic) 1993 paper Query Evaluation Techniques for Large Databases by Goetz Graefe:

  If a different partitioning strategy than range-partitioning is used,
  sorting with subsequent partitioning is not guaranteed to be
  deadlock-free in all situations. Deadlock will occur if (i) multiple
  consumers feed multiple producers, and (ii) each producer produces a
  sorted stream and each consumer merges multiple sorted streams, and
  (iii) some key-based partitioning rule is used other than range
  partitioning, i.e., hash partitioning, and (iv) flow control is
  enabled, and (v) the data distribution is particularly unfortunate.

  Figure 37 shows a scenario with two producer and two consumer
  processes, i.e., both the producer operators and the consumer
  operators are executed with a degree of parallelism of two. The
  circles in Figure 37 indicate processes, and the arrows indicate data
  paths. Presume that the left sort produces the stream 1, 3, 5, 7, ...,
  999, 1002, 1004, 1006, 1008, ..., 2000 while the right sort produces
  2, 4, 6, 8, ..., 1000, 1001, 1003, 1005, 1007, ..., 1999.

  The merge operations in the consumer processes must receive the first
  item from each producer process before they can create their first
  output item and remove additional items from their input buffers.
  However, the producers will need to produce 500 items each (and insert
  them into one consumer’s input buffer, all 500 for one consumer)
  before they will send their first item to the other consumer. The data
  exchange buffer needs to hold 1000 items at one point of time, 500 on
  each side of Figure 37. If flow control is enabled and the exchange
  buffer (flow control slack) is less than 500 items, deadlock will
  occur.

  The reason deadlock can occur in this situation is that the producer
  processes need to ship data in the order obtained from their input
  subplan (the sort in Figure 37) while the consumer processes need to
  receive data in sorted order as required by the merge. Thus, there are
  two sides which both require absolute control over the order in which
  data pass over the process boundary. If the two requirements are
  incompatible, an unbounded buffer is required to ensure freedom from
  deadlock.


Soft-deleting change-tracked user content To begin with, I distinguish between tables containing user-generated content and tables containing information about the user. The former are associated with the user profile, whereas the latter are directly associated with the user. In particular, foreign keys referencing the user profile are set to cascade deletions. Thus, user-generated content is deleted along with the profile, whereas information about the user is retained.

Furthermore, any modification to the content tables is kept track of in separate "change tables". This is accomplished using triggers. After inserting user content, the newly created entry is copied to the corresponding change table. When the entry is updated, only the modified fields are stored in the change table while the rest is left empty. The entry in the change table also references the content entry whose change it tracks as well as the user who authored the change (see figure 1). When a content entry is deleted, the foreign keys referencing that entry in the change table are set to null (for example the user_content_id field in the user_content_change table).

Figure 1: Example Database Schema.

Now this is what I am not happy with at all. Consider the following scenario: a user creates a ticket to report an inappropriate comment. The ticket references the comment in question. However, the comment author then decides to delete his or her profile and hence the comment. Although the comment text is still stored in the change table, the reference to the comment is lost, making it hard to review the ticket. (It should be noted that this is a rather typical use case for the change tables. Most of them are queried very occasionally.)

So the alternative I came up with are traditional soft-deletes. By this I mean that the change tracking remains the same but the content tables are each equipped with a deleted_at column. When querying user content, the results where this column is null are filtered out. Since no entry is ever physically deleted, the change tables don't lose their references to the content tables. Moreover, I can merge the user_profile table back into the user table, as it only served as an anchor point for cascading deletions. Regarding performance, I think that I can partition the table on the deleted_at column, so that the table indices aren't cluttered with deleted entries.

Nevertheless, I am also unhappy with this approach. First, it makes querying much more cumbersome and prone to error because one always has to exclude the already deleted entries. Secondly, the convenience of cascading deletions is lost. This applies above all to content that has a lot of metadata and derived content attached to it. An adequate delete trigger may have to update the deleted_at column of 10 to 15 tables -- not to speak of a user profile being deleted!

Could you provide some guidance on how to improve this design, or maybe suggest a different approach? Please note that this is the first time I am confronted with designing a schema at a large scale.

Alright, so my solution is to cascade soft-deletions in the after update triggers. Here's an example:

create trigger `tr_user_content_au` after update
    on `user_content` for each row
begin
    if new.`deleted_at` <> null then
        -- The row has been soft-deleted. We assume that no
        -- other fields have been modified.

        -- Soft-delete related rows.
        update `user_content_derived` set `deleted_at` = now();
    else if
        -- Check if any other fields have been modified.
        old.`col_1` <> new.`col_1` or
        old.`col_2` <> new.`col_2` then
        -- Copy modified fields to the change table.
        insert into `user_content_change` (
            `user_content_id`,
            `col_1`,
            `col_2`
        )
        values (
            new.`id`,
            if (old.`col_1` = new.`col_1`, null, old.`col_1`),
            if (old.`col_2` = new.`col_2`, null, old.`col_2`)
        );
    end if;
end;

Also, I use views to filter out already deleted rows:

create view `vw_user_content_visible` as
    select
        `col_1`,
        `col_2`
      from `user_content`
     where `deleted_at` = null;

And finally, I turn even the simplest insert, select or update statements into stored procedures. That's how I mask whether an entry is physically deleted or just soft-deleted. Apart from this, I suppose that people working on application code are less likely to forget about soft-deletions if they are forced to look into the database code to alter queries.


Disable automatic login in pgAdmin I have enabled automatic login in pgAdmin and would like to disable it to make it possible to connect to the servers using different roles.

How do I do this?

The root nodes in the "Object browser" are actually connections, not servers. And a connection requires a user name. There is a bit of mis-labelling floating around, I have raised this issue on the developer mailing list before.

However, you can just edit the properties of a connection before you open it. If you don't want to store your password for automatic login, don't check the box at login time. Once you change the username in the properties you have to enter a new password automatically.

You can also save multiple connections to the same server with different usernames.


Is it possible to build PostGIS extensions without building the entire project? PostGIS has an extension called postgis_tiger_geocoder, I have a few questions about it (all pretty tied together),

Is it possible to build that without building the rest of PostGIS?
If so, can I use the copy I build with my distro-installed PostGIS.

Ubuntu packages required to buildpostigs

xsltproc libgeos-dev libproj-dev libgdal-dev libjson0-dev docbook-xsl

I think the answer to this is simply no. While this may get fixed at some point in time, and it should be fixed soon for the sanity and great glory, until then you may find this useful

What is the easiest way to install the latest postgis_tiger_geocoder?


Requesting data of 2 tables I want to let users 'mark' tickets for later use.

So I created a table 'marked'.

All tickets are located in the table 'ticketdata'.

I have the following query to request all tickets for the logged in account:

$select = mysql_query("SELECT * FROM ticketdata WHERE member_id IN (SELECT member_id FROM users WHERE id=".mysql_real_escape_string($_SESSION['id']).") AND status <='3'");

This is working fine.

Now I want to let users 'mark' a ticket.

What I have done is create a mysql table (marked) with the following columns:

id, member_id, ticketid, active.

So now is the question how to execute both queries in order to get the 'marked' tickets. I tried the following query:

EDIT: this is returning ALL results of the specific member_id in ticketdata, but I only need tickets that are listed in tbl 'marked'.

$select = mysql_query("select a.title, a.discription, a.supportername, a.reportername, a.member_id FROM ticketdata a, marked b WHERE a.member_id IN (SELECT member_id FROM users WHERE id=".mysql_real_escape_string($_SESSION['id']).") AND b.active ='1'");

Unfortunatly with my basic mysql knowledge I am unable to get this query working.

Is there someone who can help me out ?

Thank you in advance.

Try this query, I've reformatted it to make it easier to read.  You really should specify the type of join (INNER, LEFT OUTER, etc.) and specify how they are related.

SELECT a.title
    ,a.discription
    ,a.supportername
    ,a.reportername
    ,a.member_id
FROM ticketdata a
    INNER JOIN marked b ON b.ticketid = a.ticketid
    INNER JOIN users u on u.member_id = a.member_id
WHERE u.id = ".mysql_real_escape_string($_SESSION['id'])."
    AND b.active = '1'


Problems opening standard reports I am using MS Sql Server Mgt Studio.

I select a database.
I right click and select Report / Standard Reports / Index Usage Statistics
I get Error: Incorrect syntax near '('

I check some of the other reports display ok.  Others, for example: Top transactions, All Block Transactions,  I get Error: Index(zero baseD) must be greater than or equal to zero and less than the size of the argument list.

Any ideas what could be wrong?

My suspicion here is that your database is still running in SQL Server 2000 compatibility mode. Right click on the database, go to properties and then options and see. If that is the case you'll have to look at the index usage statistics manually with queries, certain constructs used in these reports and using operators like the APPLY operator won't work against DBs in 2000 compatibility mode. You should consider bringing up to 2005/2008 mode but this needs to be a dev project and properly tested/vetted in a pre-production environment.

If it isn't the compatibility mode, I'd also look at what version of SSMS you are running and what version/SP the database engine is. If they aren't the same this could also be a part of the issue.. Help about on SSMS will show it's version and may show the instance version but you can also run Select @@version to get the build number of your instance for sure.


Making sense of INNODB buffer pool stats After having read this page in the mysql documentation, I tried to make sense of our current InnoDB usage. Currently, we allocate 6GB of RAM for the buffer pool. Our database size is about the same. Here's the output from show engine innodb status\G (we're running v5.5)

----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 6593445888; in additional pool allocated 0
Dictionary memory allocated 1758417
Buffer pool size   393215
Free buffers       853
Database pages     360515
Old database pages 133060
Modified db pages  300
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 7365790, not young 23099457
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1094342, created 185628, written 543182148
0.00 reads/s, 0.00 creates/s, 37.32 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 360515, unzip_LRU len: 0
I/O sum[2571]:cur[0], unzip sum[0]:cur[0]

I wanted to know how well we're utilizing the buffer cache. After initially glancing at the output, it appeared that we are indeed using it, based off of the Pages made young and not young have numbers in them and Buffer pool hit rate is 1000 / 10000 (which I saw elsewhere on the web that this means it's being used pretty heavily. True?)

What's throwing me through a loop is why the young-making rate and not are both at 0/1000 and the young/s and non-young/s accesses are both at 0. Those would all indicate that it's not being used at all, right?

Can anyone help make sense of this?

The Buffer pool size   393215 This is in pages not bytes.

To see the Buffer Pool size in GB run this:

SELECT FORMAT(BufferPoolPages*PageSize/POWER(1024,3),2) BufferPoolDataGB FROM
(SELECT variable_value BufferPoolPages FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_total') A,
(SELECT variable_value PageSize FROM information_schema.global_status
WHERE variable_name = 'Innodb_page_size') B;

Database pages     360515 This is the number of pages with data inside the Buffer Pool

To see the amount of data in the Buffer Pool size in GB run this:

SELECT FORMAT(BufferPoolPages*PageSize/POWER(1024,3),2) BufferPoolDataGB FROM
(SELECT variable_value BufferPoolPages FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_data') A,
(SELECT variable_value PageSize FROM information_schema.global_status
WHERE variable_name = 'Innodb_page_size') B;

To see the percentage of the Buffer Pool in use, run this:

SELECT CONCAT(FORMAT(DataPages*100.0/TotalPages,2),' %') BufferPoolDataPercentage FROM
(SELECT variable_value DataPages FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_data') A,
(SELECT variable_value TotalPages FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_total') B;

Modified db pages  300 This is the number of pages in the Buffer Pool that have to be written back to the database. They are also referred to as dirty pages.

To see the Space Taken Up by Dirty Pages, run this:

SELECT FORMAT(DirtyPages*PageSize/POWER(1024,3),2) BufferPoolDirtyGB FROM
(SELECT variable_value DirtyPages FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_dirty') A,
(SELECT variable_value PageSize FROM information_schema.global_status
WHERE variable_name = 'Innodb_page_size') B;

To see the Percentage of Dirty Pages, run this:

SELECT CONCAT(FORMAT(DirtyPages*100.0/TotalPages,2),' %') BufferPoolDirtyPercentage FROM
(SELECT variable_value DirtyPages FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_dirty') A,
(SELECT variable_value TotalPages FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_total') B;

As for the other things in the display, run this:

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';

You'll see all the status variables for the Buffer Pool. ou can apply the same queries against whatever you need to examine.


SQL Server: How much space needed to restore? SQL Server 2016 Std

Our DB is 1TB of MDF, but there are 500GB free space in that MDF.

When we do a restore on a secondary (log shipping target), will the MDF there be 1TB, or ~500GB ?

The size of the data and log files as seen by the operating system when the database is backed up, is what will be restored, regardless of how much free space is in a file. You can see exactly how large, in bytes, the data file will be when restored by executing this:

restore filelistonly from disk = 'location_of_backup_file.bak';

The [Size] column, 5th column along, has the value you can use to check whether you'll have enough drive space to restore a database.


Error 1241 When Calculating Percentages I am trying to get my percentages to equal 100%. As of right now they far surpass 100%.

I need to account for the percentages in two different tables, ua_money_allocation and ua_expenses_by_school.

I am not sure how I can add these two tables in properly without getting:

error code 1241, "Operand should contain 1 column(s)"

Query:

SELECT 'Salaries and Wages' as 'Category',
CONCAT('$',ROUND(SUM(Value_USD),0)) AS 'Amount',
CONCAT('$',ROUND(SUM(Value_USD)/(COUNT(DISTINCT ua_money_allocation.school_ID_FK)))) AS 'Mean',
(ROUND(SUM(ua_money_allocation.Value_USD)/
    (SUM(ua_money_allocation.Value_USD)*100,1 +
    SUM(ua_expenses_by_school.Amount_USD)))) AS 'Percent of Total',
CONCAT('$',ROUND(Min(Value_USD),0)) AS 'Minimum',
CONCAT('$',ROUND(MAX(Value_USD),0)) AS 'Maximum'
FROM ua_money_allocation
Join ua_expenses_by_school
ON ua_money_allocation.school_ID_FK = ua_expenses_by_school.school_id_fk
WHERE ua_money_allocation.Report_yr='2015'
AND Value_USD !=0
AND Amount_USD!=0
AND Spending_Activity='Total Spent'

UNION ALL

SELECT Category,
CONCAT('$',ROUND(SUM(Amount_USD),0)) AS 'Amount',
CONCAT('$',ROUND(SUM(Amount_USD)/(COUNT(DISTINCT ua_expenses_by_school.school_ID_FK)))) AS 'Mean',
(ROUND(SUM(ua_expenses_by_school.Amount_USD)/
    (SUM(ua_expenses_by_school.Amount_USD)*100,1 +
       SUM(ua_money_allocation.Value_USD)))) AS 'Percent of Total',
CONCAT('$',ROUND(Min(Amount_USD),0)) AS 'Minimum',
CONCAT('$',ROUND(MAX(Amount_USD),0)) AS 'Maximum'
FROM ua_expenses_by_school
JOIN ua_money_allocation
ON ua_expenses_by_school.school_id_fk = ua_money_allocation.school_ID_FK
WHERE ua_expenses_by_school.Report_yr='2015'
AND Amount_USD !=0
AND Value_USD !=0
AND Spending_Activity='Total Spent'
group by category

Stare at this:

/
(   SUM(ua_expenses_by_school.Amount_USD) * 100 ,
1 + SUM(ua_money_allocation.Value_USD) )


Efficient way to move 170 GB table from production to another server I have a 170 GB table that is no longer referenced.

I want to move that table to another server. I tried these options in staging:

select into 8 GB table to another db - 1.47 min
SSIS package - 6 min

Can anyone suggest best way to do it? How can I break table into parts so I can make small packages and upload it?

I would use:

1) backup (copy-only) & restore the database on the same server to make this db available locally -> copy records into the other database. No option if the database itself is too large.

2) use bcp out/in. Use QueryOut if  you want to specify the records to be exported yourself (partition on PK value)


How does merge replication work with SQL Server CE I have been trying out merge replication with SQL Server databases, and was just wondering how merge replication works on SQL Server CE?

For instance merge replication with SQL Server databases normally relies on triggers, and SQL Server CE doesn't have any triggers.

Also do tables such as MSmerge_conflicts_info and sysmergesubscriptions etc exist with the SQL Server CE version of merge replication?

SQL Server Compact has a built-in change tracking mechanism (similar to SQL Server Change Tracking), and when a database file is merge replicated, a number of system tables are added to the database, see http://blogs.msdn.com/b/sqlservercompact/archive/2007/04/18/merge-replication-system-tables.aspx - conflicts are only handled on ther server side. In addition to the tables mentioned in the blog post, some _sys columns are also added to each merge replicated table.


Reducing table scans using group by and window functions There is a code that I'm trying to improve that looks like this(simple example):

SELECT    DISTINCT a.col_a
         ,COALESCE(b1.col_c, b2.col_c, b3.col_c)
FROM      tab_a a
LEFT JOIN tab_b b1
          ON a.col_a = b1.col_a
             AND b1.col_b = 'blabla1'
LEFT JOIN tab_b b2
          ON a.col_a = b2.col_a
             AND b2.col_b = 'blabla2'
LEFT JOIN tab_b b3
          ON a.col_a = b3.col_a
             AND b3.col_b = 'blabla3';

You can use the following script to recreate those tables

CREATE TABLE tab_a(col_a int)
CREATE TABLE tab_b(col_a INT, col_b VARCHAR(10), col_c INT)

INSERT INTO dbo.tab_a ( col_a ) VALUES ( 1 ), ( 2 ), ( 3 );

INSERT INTO dbo.tab_b ( col_a
                       ,col_b
                       ,col_c )
VALUES ( 1, 'blabla1', 1 )
      ,( 1, 'blabla2', 3 )
      ,( 1, 'blabla2', 5 )
      ,( 2, 'blabla2', NULL )
      ,( 2, 'blabla3', 5 );

How can I change it to 1 join + maybe window function and how to rewrite coalesce part. Just to explain, current plan shows 3 tab_b scans, I want to reduce it to 1.

SELECT    DISTINCT a.col_a
         ,b.col_c
FROM      tab_a a
outer apply (select top 1 b.col_c
             from tab_b b
             where ((a.col_a = b.col_a
                     AND b.col_b = 'blabla1' )
                  or (a.col_a = b.col_a
                     AND b.col_b = 'blabla2')
                  or (a.col_a = b.col_a
                     AND b.col_b = 'blabla3'))
                  and b.col_c is not null
            order by b.col_b)b;

This solution has 1 tab_b scan but adds sort because you want to choose b.col_c as in your COALESCE. In the example above this order corresponds to the order given by your constants in join condition that correspond to values of c column. In case when the order should be different the thing will be more complicated as you should write customized order byclause.


Can't restore my distribution DB postgres 9.2 backup into my staging DB postgres 9.3 I keep getting this pg_restore: [archiver] did not find magic string in file header when i execute a pg_restore -Fc -U "username" -d "dbname" "filename".dmpon my staging DB.

These DB are running on two separate AWS instances with windows server 2008 for both environments(staging and distribution). The postgres version used on distribution is the 9.2 and the version on staging is 9.3.

I think this issue could be related to the fact that i get several pg_dump: [custom archiver] WARNING: ftell mismatch with expected position -- ftell used warnings when i execute a pg_dump -Fc -U "username" -w "dbname" > "filename".dmp on the distribution DB.

Any help is appreciated.

I found several bug reports indicating that pg_dump has problems writing to a pipe on Windows:

BUG #7794: pg_dump: errors when using pipes/streams
BUG #6726: pg_dump - ftell mismatch when using -Fc and split

Instead of writing to stdout and redirecting to a file, try using -f specify the output file:

pg_dump -Fc -U "username" -w "dbname" -f "filename".dmp


Does MySQL allows incoming queries while altering a table? I'm adding a new column to an existing table say ABC with the following query,

  ALTER TABLE ABC add column STATUS int(10) NOT NULL

This is done in master, slave and secondary slaves. Meanwhile, while altering the existing table (which does not contain any data) - other databases (other schemas and tables) were queried - both in master and slave databases.

After altering column in the table ABC has been completed I could see slowness in many app servers and few got killed stating "You have reached your max limit to DB Connections" and you could make no more DB connections and the app server got killed.

I also run a jsp in one of my servers - the thread gets hanged (stopped after making connection to one of secondary slave) in the mean time. I'm a no DB expert. I'm a novice MySQL learner and do not have any prior experience I do not know how to debug further.

I suspect whether my MySQL servers (either master or slave) does not accept any incoming connections while altering a table and once the table alteration gets completed it accepts the queued up requests and process them one by one.

Since the connections got queued up the app servers - which are waiting for previous query response - couldn't make any more requests. Can someone shed some insights to debug

on db connection getting exhausted and
the thread which invoked a jsp never ends (stops where it makes connection to mysql) - this happened during the timestamp when alter table happened.

During a DDL operation (e.g. alter table) MySQL locks only the objects in question - in this case the table.

Other tables and databases remain unaffected and new connections are accepted as usual.

On a slave, the DDL operation can't be executed in parallel, so it will delay the replication by whatever time it takes to execute.

The alter operation can increase the general server load CPU/MEM/IO, which can cause it to run out of resources. This rare (with a server with at least a few cores), and pretty much impossible with an empty table, because the alter takes < 1 sec.

The only way a DDL directly affects other connections is if they use the table/s which are modified.


Update several values from several ID's I have a SELECT request - it works fine:

select meta_value
from wp_posts v
left join wp_postmeta pm on (pm.post_id = v.id)
left join wp_posts p on (v.post_parent = p.id)
where meta_key in ('_price','_regular_price')
and v.post_type = 'product_variation'
and p.id = '1743'
limit 0,100

Request find several post's meta_value relative to post with id = 1743.

It returns me 4 (four) needed fields with values like

400
500
300
350

I need an UPDATE request for it.

I try

Update wp_posts
set meta_value = 1000
where id IN (select v.id
            from wp_posts v
            left join wp_postmeta pm on (pm.post_id = v.id)
            left join wp_posts p on (v.post_parent = p.id)
            where meta_key in ('_price','_regular_price')
                and v.post_type = 'product_variation'
                and p.id = '1743')

But it cause error

  1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use

  near 'FROM wp_posts WHERE meta_key in ( 'price' , '_regular_price' )
  and v.post' at line 2

This function as a part of UPDATE

select v.id
from wp_posts v
left join wp_postmeta pm on (pm.post_id = v.id)
left join wp_posts p on (v.post_parent = p.id)
where meta_key in ('_price','_regular_price')
and v.post_type = 'product_variation'
and p.id = '1743'

Returns me right ID'S like

1744 1744 1745 1745

But UPDATE function with this select make an error.

There is mistake??? Help me please

There's a bug or maybe lack of functionality in various versions of MariaDB/MySQL that prevents update of a table that's part of a sub-select. Small example:

create table A (x int not null, y int not null);
insert into A (x,y) values (1,1),(2,2),(3,3);
update A set y = 5 where x in (
    select x from A where y in (1,2)
);
ERROR 1093 (HY000): Table 'A' is specified twice, both as a target for 'UPDATE' and as a separate source for data

You can fool the DBMS by hiding the table in a nested table

update A
    set y = 5
where x in (
    select x from (
        select x from A where y in (1,2)
    ) as T
);
Query OK, 2 rows affected (0.01 sec)

MySQL on the other hand supports a non standard update where you can update a join, but since you did not provide create table statements I'll leave that out of the answer (I'm too lazy to figure out what your tables look like :-)


SELECT user_id,
       DATE(created_at) created_at,
       SUM(valid=1) valid,
       100*SUM(valid=1)/COUNT(*) pvalid,
       SUM(valid=0) invalid,
       100*SUM(valid=0)/COUNT(*) pinvalid
FROM test
GROUP BY user_id, DATE(created_at)
ORDER BY user_id, DATE(created_at);

fiddle


MySQL is not logging queries into log file I am using MySQL 5.5 with Amazon Linux 2013. Following is my configuration for log file.

slow_query_log = 1
long_query_time=0
log-error = /var/log/mysqld.log
slow_query_log_file = /var/log/mysqld_slow_query.log
general-log = 'ON'
log_queries_not_using_indexes = 1
general_log_file = /var/log/mysqld_normal.log
log = /var/log/mysqld_normal.log

But, It is not logging the queries in log file. particularly I have to find the queries which is not using Index at all.

Please help me on this. Thank you.

For logging slow queries do like below, It will log all queries those are not using indexes as well as which are taking more than 1 sec

slow_query_log                 = 1
slow_query_log_file            = /var/log/mysqld_slow_query.log
long_query_time                = 1
log-queries-not-using-indexes

For Error log

log-error                      = /var/log/mysqld.log

For general log

general_log_file               = /var/log/mysqld_normal.log
general_log                    = 1


Update my.cnf without restarting MySQL I want to update my my.cnf without restarting mysql, is it possible?

In fact I just want to set lower_case_table_names = 1, but I do not have the permission to restart MySQL.

You can always check MySQL manual if a variable is dynamic: http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_lower_case_table_names

Unfortunately lower_case_table_names is not. So you have to restart MySQL to make this variable picked up.


Calculate total size of transaction log backups in the last 24 hours I wanted to get the total backup sizes in KB, MB and GB for each database on an instance. I have pieced together the below but haven't been able to do the sum part.

SELECT
   A.[Server],
    A.DATABASE_NAME,
   A.last_db_backup_date,
   B.backup_start_date,
   B.expiration_date,
   CAST(CAST(b.backup_size / 1000 AS INT) AS VARCHAR(14)) + ' ' + 'kB' AS bkSize,
   CAST(CAST(b.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'mB' AS bkSize,
   CAST(CAST(b.backup_size / 1000000000 AS INT) AS VARCHAR(14)) + ' ' + 'gB' AS bkSize,
   B.logical_device_name,
   B.physical_device_name,
   B.backupset_name,
   B.description
FROM
   (
   SELECT
       CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
       msdb.dbo.backupset.database_name,
       msdb.dbo.backupset.backup_finish_date AS last_db_backup_date
   FROM    msdb.dbo.backupmediafamily
       INNER JOIN msdb.dbo.backupset
       ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
   WHERE   msdb..backupset.type = 'l'
   --GROUP BY
   --    msdb.dbo.backupset.database_name
   ) AS A

   LEFT JOIN

   (
   SELECT
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
   msdb.dbo.backupset.database_name,
   msdb.dbo.backupset.backup_start_date,
   msdb.dbo.backupset.backup_finish_date,
   msdb.dbo.backupset.expiration_date,
   msdb.dbo.backupset.backup_size,
   msdb.dbo.backupmediafamily.logical_device_name,
   msdb.dbo.backupmediafamily.physical_device_name,
   msdb.dbo.backupset.name AS backupset_name,
   msdb.dbo.backupset.description
FROM   msdb.dbo.backupmediafamily
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE  msdb..backupset.type = 'l'
   ) AS B
   ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date]
WHERE backup_start_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()
ORDER BY
   A.database_name

You need to avoid casting your backup size to varchar or you will not be able to sum the values.  If you need to then you can do that conversion afterwards.

Other than that it is just a matter of wrapping the whole lot up as a subquery, grouping the results by database name, and doing the sum (You may be able to do it other ways but that seemed the least amount of re-working for me).

    select subquery.DATABASE_NAME,
        SUM(bkSize_KB) as SumBkSize_KB,
        SUM(bkSize_MB) as SumBkSize_MB,
        SUM(bkSize_GB) as SumBkSize_GB
from
(
        SELECT
           A.[Server],
            A.DATABASE_NAME,
           A.last_db_backup_date,
           B.backup_start_date,
           B.expiration_date,
           CAST(b.backup_size / 1000 AS INT) AS bkSize_KB,
           CAST(b.backup_size / 1000000 AS INT) AS bkSize_MB,
           CAST(b.backup_size / 1000000000 AS INT) AS bkSize_GB,
           B.logical_device_name,
           B.physical_device_name,
           B.backupset_name,
           B.description
        FROM
           (
           SELECT
               CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
               msdb.dbo.backupset.database_name,
               msdb.dbo.backupset.backup_finish_date AS last_db_backup_date
           FROM    msdb.dbo.backupmediafamily
               INNER JOIN msdb.dbo.backupset
               ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
           WHERE   msdb..backupset.type = 'l'
           --GROUP BY
           --    msdb.dbo.backupset.database_name
           ) AS A

           LEFT JOIN

           (
           SELECT
           CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
           msdb.dbo.backupset.database_name,
           msdb.dbo.backupset.backup_start_date,
           msdb.dbo.backupset.backup_finish_date,
           msdb.dbo.backupset.expiration_date,
           msdb.dbo.backupset.backup_size,
           msdb.dbo.backupmediafamily.logical_device_name,
           msdb.dbo.backupmediafamily.physical_device_name,
           msdb.dbo.backupset.name AS backupset_name,
           msdb.dbo.backupset.description
        FROM   msdb.dbo.backupmediafamily
           INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
        WHERE  msdb..backupset.type = 'l'
           ) AS B
           ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date]
        WHERE backup_start_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()

) subquery

group by subquery.DATABASE_NAME


cannot repair database in mongodb I accidentally deleted 2 data (mydb.47, mydb.62) files from my /data/db of my mongodb. I have rest of the files in place...

Now my mongodb won't start up..I tried repairing it using this command

sudo -u mongod  mongod  --dbpath /data/db-new --repair --repairpath /data/db

Repair finished with no errors. Mongo docs say

  You may also wish to move the repaired files to the old database location or update the dbPath to indicate the new location

I copied the files from /data/db-new to /data/db and started mongo server. But it is not starting...

Is there any way I could reclaim my data?

As per my knowledge you cannot reclaim your data using MONGO. But you can go for some other third party tools that can help to recover data from your hard disk.

I hope this link will help linux data recover on unix.stackexchange.com


Setting up Always On Availability Groups with Windows Server Failover Cluster I'm trying to setup Availability groups for SQL Server with two nodes.

I have two SQL Servers at two different locations.

I ran through the failover cluster validation and everything looked good except that it complained about each node only having 1 pair of network interfaces.

I next ran through the wizard to create a cluster and now get the following errors.

  "This resource is marked with a state of 'Offline'. The functionality
  that this resource provides is not available while it is in the
  offline state. The resource may be put in this state by an
  administrator or program. It may also be a newly created resource
  which has not been put in the online state or the resource may be
  dependent on a resource that is not online. Resources can be brought
  online by choosing the 'Bring this resource online' action in Failover
  Cluster Manager."

I went to Failover Cluster Manager to try to bring it online and then get the following message.

  "The operation failed because either the specified cluster node is not
  the owner of the resource, or the node is not a possible owner of the
  resource"

Any suggestions of what I need to do to bring this second node online so I can continue with configuring Always ON?

If I understand your situation correctly, SQL Server isn't even in the picture right now and you're stuck at configuring Windows Server Failover Clustering?

If that's the case, then the best place to look for further clues (as this is quite the open ended question that could take a person many hours to investigate and depend upon settings in your specific environment) would be the cluster log. One of the easiest ways to gather the cluster log is to use the powershell command Get-ClusterLog it can take multiple inputs and I'd recommend using at least -UseLocalTime and -Directory to change the log from UTC to local and the directory that you'd like the logs to be output in, respectively.

Since this, currently, has no involvement with SQL Server the question may be closed to migrated (for future reference).


Altering SSAS writeback behavior? I'm trying to implement a writeback measure group for required quantities by month and part number. I'm puzzled by the behavior I am seeing in Excel 2013, but I don't know if SSAS or Excel is responsible.

If the dimension selection in Excel is not the native granularity of the the underlying Measure Group, 'Publishing' the data distributes the data as equally as it can among the dimension members. Which is not necessarily wrong, but a bit weird and definitely unexpected. e.g.

If I publish a value at the year level, it will divide it equally into quarters and months.

If I don't include the part dimension it will divide the quantity equally into all part numbers.

I need to prevent data from being published anywhere but at the intersection of Part and Month. My only thought so far is to control write access through the Role with an expression that includes the Measure and Dimensions, but I'm not having much luck with that due to my limited MDX skills. Is there any other mechanism to modify writeback behavior?

Writeback is always written to the leaf level of your dimension, and has to be because of the structure of your writeback table (where the difference between the original value and the new value is stored).

Have a look at the writeback tables in your data source and you'll see it contains fields for all your measures, and the key of all your dimensions, so the change always needs to be distributed to that level.

From the documentation on write-enabled partitions

  Changes to leaf and nonleaf cells are handled differently. A leaf cell
  represents an intersection of a measure and a leaf member from every
  dimension referenced by the measure group. The value of a leaf cell is
  taken directly from the fact table, and cannot be divided further by
  drilling down. If a cube or any partition is write-enabled, changes
  can be made to a leaf cell. Changes can be made to a nonleaf cell only
  if the client application provides a way of distributing the changes
  among the leaf cells that make up the nonleaf cell.

The only thing you can really influence is how the changes are distributed to the leaf level cells when you write back to another attribute.
This is documented in the UPDATE CUBE

You basically have 4 options:

USE_EQUAL_ALLOCATION
USE_EQUAL_INCREMENT
USE_WEIGHTED_ALLOCATION [ BY Weight_Expression]
USE_WEIGHTED_INCREMENT [ BY Weight_Expression]

I'm not sure how well Excel allows you to pick an allocation method however.


CAST throwing error when run in stored procedure but not when run as raw query Something very odd is happening here.

I have a query that looks like this.

SELECT CAST(FT.DOP AS SMALLINT) FROM TRACKING_DATA WHERE date > @mydate and identifier = 0000000000

When run as a raw query it returns data just fine.

When i place it in a stored procedure that alters the where clause it throws this error.

Msg 244, Level 16, State 2, Procedure myprocedure, Line 107 [Batch Start Line 2]
The conversion of the varchar value '58629' overflowed an INT2 column. Use a larger integer column.

So here is the oddity. I go through all the possible data for that where clause with a query like this.

SELECT DISTINCT DOP FROM TRACKING_DATA where identifier = 000000000000

and

SELECT DISTINCT CAST(DOP AS smallint) FROM TRACKING_DATA where identifier = 000000000000

And this is what i get back.

17
12
9
19
8
14
6
16
11
13
7
10
0
18
5
15
4

Nowhere does it have anything remotely too large for a SMALLINT. So i thought, ok maybe its a non-printable ASCII character. But i can't find any.

I'm a little perplexed at the moment. It runs find as a raw query, explodes as a procedure and all possible data based on the where is valid. My only suspicion is that the query plan is doing something odd with filtering or maybe runs with different validation when run as a procedure.

You're better off not depending on the indexing to avoid these errors, and instead writing the query to guard against this situation.  Since you're on SQL Server 2012, one option would be to use TRY_CAST:

SELECT TRY_CAST(FT.DOP AS SMALLINT)
FROM TRACKING_DATA
WHERE date > @mydate and identifier = 0000000000;

This will result in NULL being selected for values that fail to convert from varchar to smallint.  But as long as you know there aren't any results like that, or your application can handle the NULL results, you should be good.


Why would I need a third table in a one-to-many relationship? Good day,

Our db professor at the univeristy always said that one-to-many relationships with (0, M) in one side MUST have a third table to relate them. I did not ask him back then and now I cannot, but I would like to know why would he claim that? (specially the must part).

I am modeling a simple sensor-measure-campaign database and I am getting really puzzled with it, what do you think about my model, would it work as I expect? This relates to my question because I am doing something completely different to what I was taught, and I am afraid of building a broken model.

Sensor has 0 or M measures, a measure belongs exactly to one sensor.
Campaign has 0 or M sensors, a sensor may be in 0 or N campaigns.
Campaign has 0 or M measures, a measure belongs exactly to one campaign.

Using my professsor's approach, I get 6 tables (an intermediate table for each pair). I see no need for 2 of those tables, but understanding that is the purpose of this question.

Ignoring him, I got Campaign and Sensor related by both Measure and another table (third table from many-to-many, which I called Instrument). I think it looks wrong to have such a double relationship, noticing that both Measure and Instrument will have FK to Campaign and Sensor (Instrument would be like an empty measure, IMO), I decided to simply do what I did.

I potentially need to query any combination (sensors/measures for a given campaign and measures for a given sensor), and I think I can easily (?) do that (using subqueries when measure is involved). I also want that if I delete a campaign or a sensor, its measures get removed, I can easily achieve that with an on delete cascade. What would be the drawbacks?

Sorry for the newbie question, any help will be appreciated. I already googled and found nothing, maybe I used the wrong terms, I am not pretending to spam here. At least a smarter query to Google Search will be appreciated, thanks.

I don't know what your professor has really said I am not sure if I understand your model. But maybe the following is interesting.

In the article A Logical Design Methodology for Relational Databases
Using the Extended Entity-Relationship Model by T.J Teory, D. Yang and J.P. Fry is described how entity relationship diagrams can be transformed to relations (and tables). In Fig.8f (on the p 13 of the pdf) there is the following example for an 1:n relationship:

  Each engineer can have at most one
  secretary. One secretary could work
  for several engineers.

There is a relationship "works-for" between the entities "secreatry" and "engineer".

A "secretary" "works-for" an "engineer".
Each "engineer" has at most one "secretary" that "works-for" her. It may be that an "engineer" has not "secretary" that "works-for" her.
A "secretary" can "work-for" one, more or no "engeneer"

In the article the authors propose to represent this as

ENGINEER(*EMP-NO,...,SEC-EMP-NO)
SECRETAY(*EMP-NO,...)

the fields marked by * are keys. The field SEC-EMP-NO referenes a EMP-NO from SECRETARY and is nullabel.

But there are authors that have the opinon that null values should not be allowed in relations. If one wants to avoid null values one needs a third relation to create the following model:

ENGINEER(*EMP-NO,...)
SECRETAY(*EMP-NO,...)
WORKS-FOR(*ENG-EMP-NO,SEC-EMP-NO)

ENG-EMP-NO references an EMP-NO in ENGINEER and SEC-EMP-NO references an EMP-NO in SECRETARY.

If an "engineer" always has a "secretary" that "works-for" her then the first representation with two relations does not contain null values.

Edit:
I now found the comment in your post that links to the diagrams (http://i.imgur.com/gIG2D3h.jpg)  of your professor: Actually he introduces a third table  to avoid null values as described here.


Copy database wizard-generated job blows up log file; alternative? I've recently upgraded our deployment environment to include a staging server. Said server has a database on the same server as our production database. I am trying to set it up to copy data from the production database on a nightly basis.

The production database is set to full recovery model, with hourly trans logs backups and a nightly full backup. It is ~7gb, and its log file sits somewhere around 700mb.

I tried to use the "Copy Database" wizard to copy the prod db to staging and create a job to do so in the future. Upon the initial run from the wizard and any subsequent runs of the job the staging log file blows up to 15-17gb. I assume this is a result of it being in full recovery mode and the method with which it copies the database (an enormous amount of inserts?).

My question is, firstly, why would a built in function allow for log file growth like this? It seems dangerous. And, secondly, what is the best way to go about copying the data to the staging server? I would restore it from the nightly production backups, but the file name of the backup files is dependent on the date (Productiondb-2014-11-05-2205.bak), so I don't know how to script it.

The package created by the copy wizard can be modified to bring a little better performance possibly but your log growth is expected. Every bit of data changed for the table and indexes is being modified, that has to be logged no matter what recovery model you are in.

I would go with the restore from backup as this will be a bit cleaner. You can pull the last backup file name from the msdb backup tables. There are a ton of examples of this online, would check MSSQLTips.com for a start.

You would simply pull the last backup, verify it exist, robocopy it over (unless it exist on network share), then restore it.


Importance of ER Diagrams I am a student and I am developing several projects as a part of my academia.

While developing the database for one of the projects, we came across a situation where we thought about whether ERD is necessary or not. Right now, not every one of us are in agreement on developing the ERD first, and then developing database from it.

The majority of people prefers developing the database on the fly verbally according to the system requirementd on paper directly.

Now, I am strict follower of Database principles. I think the database should be developed from the ERD only. So, I just want to know the following:

Is the industry following these principles?
Am I just wasting my time on developing an ERD?
What are the benefits of developing an ERD?

Plain and simple, think of developing a database without an ERD as building a house without a building plan. It might be doable because you think that simply laying a brick one over another is enough to build something, however the moment somebody else takes responsibility over the project there is disaster potential.

In my experience you will not benefit much from ERDs unless you use them in conjunction with CASE tools (ERWin, MySQL Workbench etc.) which will additionally allow you to perform some really helpful operations such as forward and reverse engineering. Even without these functions having a centralized schematic of the complete database is useful because sometimes the constraints implemented in the database itself are not enough to tell the full story about the relationships between particular database entities.

Here is an example involving MySQL which, as you might know, implements several internal storage engines, most notably MyISAM and InnoDB. There are significant differences between them one of the most important one being that MyISAM doesn't support constraints. Despite that fact MyISAM is heavily used for web applications which means that the relational logic needs to be implemented either through business logic (application code) or in another way. The problem is when you forward engineer an ERD with MyISAM tables (entities) MySQL will silently ignore the constraints set by the ERD and you will end up with a database which doesn't clearly identify the nature of the relationships between entities. In other words after you develop the database layout there is no way for the code developers to implement proper business logic without an ERD.


How to purge old files from pg_xlogs directory I have an archive command copying WAL files from pg_xlogs to my archive directory.  However, I notice that the pg_xlogs directory is growing in size with (but is limiting itself to around 7 files).   What controls this "7" value?  Is there a risk it will grow to a huge number of files?

How do I limit the number of log files, or even better, limit to no more than 48 hours worth?  I found a similarly titled question here but it doesn't really apply.  I'm using PostgreSQL version 8.4.20 (so settings/commands from v9 won't apply)

Can I just create a cron job that deletes files from pg_xlogs older than 48 hours?  Will this crash/corrupt the pgsql server?  Is there a better way?

Can I use my archive_command to delete the source file after moving to my archive directory?

Firstly PG 8.4 is no longer supported (since July 2014), you should move your databases to a supported version as soon as possible.

The WAL configuration settings for the 8.4 version are documented here:

https://www.postgresql.org/docs/8.4/static/runtime-config-wal.html

In particular checkpoint_segments control the number of WAL files.

You must not delete WAL files, either manually or with a cron job or with archive_command. This would definitely corrupt your database.

WAL files may grow in number if archive_command is not working or lagging behind.


How to allow to execute stored procedures but not writes? I would like to set up a "reading" user on my SQL Server 2012 instance. I would like to allow him the right to execute any stored procedure, or function, or direct SQL statement, which retrieves data from all tables and views, but not to update or to insert (i.e. read anything and write nothing).

Can I set this up without specifically giving rights to each function or stored procedure by name, but rather grant him execution right on any function or stored procedure, just taking away the right to modify tables?

Will anything change if I run SQL Server 2008 instead?

Clarifications and additions:

If a stored procedure changes data, the user should receive an error message (either denying the modification or refusing access to the stored procedure completely).
If a potential solution involves denying permissions, can I just not grant certain permissions instead of denying?
Can a deny be applied on all tables, views etc. (existing now and in the future) in the database in one statement?

This isn't as easy to achieve as you might think. One way is to create a new user-defined database role, give all the permissions needed to that role, then add users to the new role. This at least makes it easier to give users (or other user-defined roles) this set of permissions in future. The following steps are a good start:

-- The user-defined role containing all required permissions
CREATE ROLE Readers AUTHORIZATION dbo;

-- Give read-only access to all tables,
-- views on those tables, and in-line
-- functions
ALTER ROLE db_datareader ADD MEMBER Readers;

-- Example: add a user (Bob) to the role
ALTER ROLE Readers ADD MEMBER Bob;

After this, Bob will have wide read-only privileges in the database. He will be able to read from all tables, views on those tables, and in-line functions. He will not be able to execute any procedures or use non-inline functions, however.

You will need to grant specific permissions to the Readers role for safe functions and procedures you wish Readers to have access to. There may be steps you can take to make this process easier (such as grouping objects into a schema and granting execute permission on the schema instead of individual objects) but there are too many details to cover here.

One thing to be aware of is that the lack of data modification privileges would not prevent a Reader from changing data via a stored procedure she has been granted execute permission on, if the procedure and object being modified share a common owner. Nor would an explicit deny be respected in this case. This feature is known as Ownership Chaining.


How to count specific products? I have a table product and its columns are maker, type and model. I want to get the makers who produce 6 product types and more than one model. Here is something I am trying:

select maker, type
from product
group by maker
having count(distinct type) = 6
   and count(distinct model) > 1 ;

It gives error.

If I say I need 1 type and query is:

select maker, min(type)
from product
group by maker
having count(distinct type) = 1
   and count(distinct model) > 1 ;

then it works. Why?

In your first query the error is produced by the attribute type, which is in the select clause, but not in the group by clause. This is due to the fact that there are different values of type for each group. So, instead of writing:

select maker, type from product group by maker
having count(distinct type) = 6 and count(distinct model) > 1

you should either remove type:

select maker from product group by maker
having count(distinct type) = 6 and count(distinct model) > 1

or use an aggregation function for it, like in the second query:

select maker, min(type) from product group by maker
having count(distinct type) = 6 and count(distinct model) > 1


SSISDB - Certificates expired I was doing an audit of certificates in our environments to find those that have either expired or are expiring in 30 days. I used the following query:
SELECT  *
FROM    sys.certificates
WHERE   expiry_date < DATEADD(DAY,30,GETDATE());

I noticed in SSISDB that there are a large number of expired certificates:

What are these certificates used for, is it an issue that they have expired? I have not heard any complaints from our development team that SSIS isn't working as expected.

All certificates have an expiry_date, but for most uses of certificates in SQL Server, the expiry is not checked.  A certificate that protects a symmetric key used for data encryption in a database (which is what I think these are), can be used indefinitely.  IE SQL Server doesn't check the expiry_date when you run OPEN SYMMETRIC KEY DECRYPTION BY CERTIFICATE.
From the docs:

EXPIRY_DATE = 'datetime'
Is the date on which the certificate expires.
If not specified, EXPIRY_DATE is set to a date one year after
START_DATE. EXPIRY_DATE is in UTC time and can be specified in any
format that can be converted to a date and time. SQL Server Service
Broker checks the expiration date. Backup with Encryption using
certificates also checks the expiration date and will not allow a new
backup to be created with an expired certificate, but will allow
restores with an expired certificate. However, expiration is not
enforced when the certificate is used for database encryption or
Always Encrypted.

CREATE CERTIFICATE


Dynamic SELECT and place result in variable columns Edit: I use Microsoft SQL Server Management Studio, I believe it's SQL 2008R2. Sorry, new to this.

I've got a complex question, at least, I think so since I can't find the answer after excessive googling.

Simplified situation:

Table | QuestionAnswers
id
user_id
question_id
answer

Table | Questions
id
question_id
question_name

Now I want to write a query that gives the following result:

user_id | question 1 | question 2 | question 3 | que...etc
1         Something
1                                   Something
2                      Something
3         Something
3                      Something

Or (but that shouldn't be too difficult if I have the above?):

user_id | question 1 | question 2 | question 3 | que...etc
1         Something                 Something
2                      Something
3         Something    Something

Where 'something' is the answer to the question.

Now I have a possible query:

SELECT [user_id]
      ,[answer] as Question1
      ,''       as Question2
      ,etc
FROM [QuestionAnswers]
WHERE [question_id] = 1

UNION ALL

SELECT [user_id]
       ,''       as Question1
       ,[answer] as Question2
       ,etc
FROM [QuestionAnswers]
WHERE [question_id] = 2

UNION ALL

SELECT etc...

Works like a charm. Downside is that this way, I have to make x SELECT statements with x rows, where x is the number of questions I have (50+).

How can this be done dynamically? Is that possible?

I hope I explained myself good enough...

Kind regards,
Tjab

Adding an example to Michael's answer.  The problem with the PIVOT is two fold. 1st, it wants to aggregate.  You can get around this by defining your dataset to be distinct and using MAX or MIN functions.  But your example above makes that impossible, due to a user being able to have multiple answer sets for a given question.  You would end up with only one row with the two datasets aggregated for the dupe user.  To get around this I added a "question_set" field, adding a date (distinct enough for this example) to each question/answer set.

2nd, you still have to define the pivoted fields.  If you have 50 questions, that's 50 definitions.  And if you add a question, you will have to add the definition to the query.  Using a loop, I created a question list dynamically and inserted it into the pivot query.  Hope this helps.

--> Create the test data
if OBJECT_ID('QuestionAnswers','u') is not null
    drop table QuestionAnswers

create Table  QuestionAnswers
(
id int identity,
question_set date, --> need something to delineate repeats
user_id varchar(255),
question_id int,
answer varchar(255)
)

if OBJECT_ID('Questions','u') is not null
    drop table Questions

create Table  Questions
(
id int identity,
question_id int,
question_name varchar(255)
)
go

with CTEquestion
as
(
    select 1 QID
    union all
    Select QID+1
    from CTEquestion
    where QID < 11
)
insert questions
select QID, 'Question'+cast(QID as varchar(50))
from CTEquestion

insert QuestionAnswers
values ('2015-04-23', 'a1', 1, 'Canswer1')
    ,  ('2015-04-23', 'a1', 2, 'Ianswer2')
    ,  ('2015-04-23', 'a1', 3, 'Canswer3')
    ,  ('2015-04-23', 'a1', 4, 'Canswer4')
    ,  ('2015-04-23', 'a1', 5, 'Ianswer5')
    ,  ('2015-04-23', 'a1', 6, 'Ianswer6')
    ,  ('2015-04-23', 'a1', 7, 'Canswer7')
    ,  ('2015-04-23', 'a1', 8, 'Canswer8')
    ,  ('2015-04-23', 'a1', 9, 'Canswer9')
    ,  ('2015-04-23', 'a1', 10,'Canswer10')

insert QuestionAnswers
values (CONVERT(DATE, GETDATE()), 'b2', 1, 'Canswer1')
    ,  (CONVERT(DATE, GETDATE()), 'b2', 2, 'Canswer2')
    ,  (CONVERT(DATE, GETDATE()), 'b2', 3, 'Canswer3')
    ,  (CONVERT(DATE, GETDATE()), 'b2', 4, 'Canswer4')
    ,  (CONVERT(DATE, GETDATE()), 'b2', 5, 'Canswer5')
    ,  (CONVERT(DATE, GETDATE()), 'b2', 6, 'Ianswer6')
    ,  (CONVERT(DATE, GETDATE()), 'b2', 7, 'Canswer7')
    ,  (CONVERT(DATE, GETDATE()), 'b2', 8, 'Canswer8')
    ,  (CONVERT(DATE, GETDATE()), 'b2', 9, 'Canswer9')
    ,  (CONVERT(DATE, GETDATE()), 'b2', 10, 'Ianswer10')

insert QuestionAnswers
values (CONVERT(DATE, GETDATE()), 'c3', 1, 'Ianswer1')
    ,  (CONVERT(DATE, GETDATE()), 'c3', 2, 'Ianswer2')
    ,  (CONVERT(DATE, GETDATE()), 'c3', 3, 'Canswer3')
    ,  (CONVERT(DATE, GETDATE()), 'c3', 4, 'Ianswer4')
    ,  (CONVERT(DATE, GETDATE()), 'c3', 5, 'Canswer5')
    ,  (CONVERT(DATE, GETDATE()), 'c3', 6, 'Ianswer6')
    ,  (CONVERT(DATE, GETDATE()), 'c3', 7, 'Canswer7')
    ,  (CONVERT(DATE, GETDATE()), 'c3', 8, 'Canswer8')
    ,  (CONVERT(DATE, GETDATE()), 'c3', 9, 'Canswer9')
    ,  (CONVERT(DATE, GETDATE()), 'c3', 10, 'Ianswer10')

insert QuestionAnswers
values (CONVERT(DATE, GETDATE()), 'a1', 1, 'Canswer1')
    ,  (CONVERT(DATE, GETDATE()), 'a1', 2, 'Ianswer2')
    ,  (CONVERT(DATE, GETDATE()), 'a1', 3, 'Canswer3')
    ,  (CONVERT(DATE, GETDATE()), 'a1', 4, 'Canswer4')
    ,  (CONVERT(DATE, GETDATE()), 'a1', 5, 'Canswer5')
    ,  (CONVERT(DATE, GETDATE()), 'a1', 6, 'Canswer6')
    ,  (CONVERT(DATE, GETDATE()), 'a1', 7, 'Canswer7')
    ,  (CONVERT(DATE, GETDATE()), 'a1', 8, 'Canswer8')
    ,  (CONVERT(DATE, GETDATE()), 'a1', 9, 'Canswer9')
    ,  (CONVERT(DATE, GETDATE()), 'a1', 10, 'Ianswer10')
-->End test data creation

--straight join
select qa.user_id, qa.question_set, q.question_id, qa.answer
from Questions q
    join QuestionAnswers qa on qa.question_id=q.question_id
order by qa.user_id

--dynamic pivot
DECLARE
    @questionList varchar(max)
, @maxQID int
, @qid int

select @questionList='',@maxQID = MAX(question_id), @qid= MIN(question_id)
FROM Questions

while @qid <= @maxQID
begin
    set @questionList=@questionList+'['+cast(@qid as varchar(10))+']'

    select @qid=min(question_id)
    from Questions
    where question_id > @qid

    if @qid<=@maxQID
        set @questionList=@questionList+', '
end

DECLARE @SQL NVARCHAR(MAX)

SET @SQL = N'
select user_id, '+@questionList+'
from
(select q.question_id, qa.question_set, qa.user_id, qa.answer
from Questions q
    join QuestionAnswers qa on qa.question_id=q.question_id) x
PIVOT
(
max(answer)
FOR question_id in ('+@questionList+')
) pvt
order by user_id'

exec sp_executesql @SQL


How to rollback when 3 stored procedures are started from one stored procedure I have a stored procedure that only executes 3 stored procedures inside them. I am only using 1 parameter to store if the master SP is successful.

If the first stored procedure works fine in the master stored procedure, but the 2nd stored procedure fails, then will it automatically roll back all the SP's in the master SP or do I have to make some command?

Here is my procedure:

CREATE PROCEDURE [dbo].[spSavesomename]
    -- Add the parameters for the stored procedure here

    @successful bit = null output
AS
BEGIN
begin transaction createSavebillinginvoice
    begin Try
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

   BEGIN

   EXEC [dbo].[spNewBilling1]

   END

   BEGIN

   EXEC [dbo].[spNewBilling2]

   END

   BEGIN

   EXEC [dbo].[spNewBilling3]

   END

   set @successful  = 1

   end Try

    begin Catch
        rollback transaction createSavesomename
        insert into dbo.tblErrorMessage(spName, errorMessage, systemDate)
             values ('spSavesomename', ERROR_MESSAGE(), getdate())

        return
    end Catch
commit transaction createSavesomename
return
END

GO

Given only the code shown in the question, and assuming that none of the three sub-procs have any explicit transaction handling, then yes, an error in any of the three sub-procs will be caught and the ROLLBACK in the CATCH block will roll back all of the work.

BUT here are some things to note about transactions (at least in SQL Server):

There is only ever one real transaction (the first one), no matter how many times you call BEGIN TRAN

You can name a transaction (as you have done here) and that name will appear in the logs, but naming only has meaning for the first / outer-most transaction (because again, the first one is the transaction).
Each time you called BEGIN TRAN, whether or not it is named, the transaction counter is incremented by 1.
You can see the current level by doing SELECT @@TRANCOUNT;
Any COMMIT commands issued when @@TRANCOUNT is at 2 or above do nothing more than reduce, one at a time, the transaction counter.
Nothing is ever committed until a COMMIT is issued when the @@TRANCOUNT is at 1
Just in case the information above does not indicate clearly: regardless of the transaction level, there is no actual nesting of transactions.

Save points allow for creating a subset of work within the transaction that can be undone.

Save points are created/marked via the SAVE TRAN {save_point_name} command
Save points mark the beginning of the subset of work that can be undone without rolling back the entire transaction.
Save point names do not need to be unique, but using the same name more than once still creates distinct save points.
Save points can be nested.
Save points cannot be committed.
Save points can be undone via ROLLBACK {save_point_name}. (more on this below)
Rolling back a save point will undo any work that happened after the most recent call to SAVE TRAN {save_point_name}, including any save points created after the one being rolled-back was created (hence the "nesting").
Rolling back a save point has not effect on the transaction count/level
Any work done prior to the initial SAVE TRAN cannot be undone except by issuing a full ROLLBACK of the entire transaction.
Just to be clear: issuing a COMMIT when @@TRANCOUNT is at 2 or above, has no effect on save points (because again, transaction levels above 1 don't exist outside of that counter).

You cannot commit specific named transactions. The transaction "name", if provided along with the COMMIT, is ignored and only exists for readability.
A ROLLBACK issued without a name will always rollback ALL transactions.
A ROLLBACK issued with a name must correspond to either:

The first transaction, assuming it was named:
Assuming no SAVE TRAN has been called with the same transaction name, this will rollback ALL transactions.
A "save point" (described above):
This behavior will "undo" all changed made since the most recent SAVE TRAN {save_point_name} was called.
If the first transaction was a) named and b) has had SAVE TRAN commands issued with its name, then each ROLLBACK of that transaction name will undo each save point until there are none left of that name. After that, a ROLLBACK issued of that name will rollback ALL transactions.
For example, assume the following commands were run in the order shown:

BEGIN TRAN A -- @@TRANCOUNT is now 1
-- DML Query 1
SAVE TRAN A
-- DML Query 2
SAVE TRAN A
-- DML Query 3

BEGIN TRAN B -- @@TRANCOUNT is now 2
SAVE TRAN B
-- DML Query 4

Now, if you issue (each of the following scenarios is independent of each other):

ROLLBACK TRAN B once: It will undo "DML Query 4". @@TRANCOUNT is still 2.
ROLLBACK TRAN B twice: It will undo "DML Query 4" and then error as there is no corresponding save point for "B". @@TRANCOUNT is still 2.
ROLLBACK TRAN A once: It will undo "DML Query 4" and "DML Query 3". @@TRANCOUNT is still 2.
ROLLBACK TRAN A twice: It will undo "DML Query 4", "DML Query 3", and "DML Query 2". @@TRANCOUNT is still 2.
ROLLBACK TRAN A thrice: It will undo "DML Query 4", "DML Query 3", and "DML Query 2". Then it will rollback the entire transaction (all that was left was "DML Query 1"). @@TRANCOUNT is now 0.
COMMIT once: @@TRANCOUNT goes down to 1.
COMMIT once and then ROLLBACK TRAN B once: @@TRANCOUNT goes down to 1. Then it will undo "DML Query 4" (proving that COMMIT didn't do anything) . @@TRANCOUNT is still 1.

Transaction names and save point names:

can have up to 32 characters
are treated as having a binary Collation (not case-sensitive as the documentation currently states), regardless of the Instance-level or Database-level Collations.
For details, please see the Transaction Names section of the following post: What’s in a Name?: Inside the Wacky World of T-SQL Identifiers

A stored procedure is not, in itself, an implicit transaction. Each query if no explicit transaction has been started, is an implicit transaction. This is why explicit transactions around single queries are not necessary unless there can be a programmatic reason to do a ROLLBACK, else any error in the query is an automatic rollback of that query.
When calling a stored procedure, it must exit with the value of @@TRANCOUNT being the same as when it was called. Meaning, you cannot:

Start a BEGIN TRAN in the proc without committing it, expecting to commit in the calling/parent process.
You cannot issue a ROLLBACK if an explicit transaction was started prior to the proc being called as it will return @@TRANCOUNT to 0.

If you exit a stored procedure with a transaction count that is either higher or lower than when it stared, you will get an error similar to:

  Msg 266, Level 16, State 2, Procedure YourProcName, Line 0
  Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = X, current count = Y.

Table Variables, just like regular variables, are not bound by transactions.

Regarding having transaction handling in procs that can either be called independently (and hence need transaction handling) or call from other procs (hence not needing transaction handling): this can be accomplished in a couple different ways.

The way that I have been handling it for several years now that seems to work well is to only BEGIN / COMMIT / ROLLBACK at the outer-most layer. Sub-proc calls just skip the transaction commands. I have outlined below what I put into each proc (well, each one that needs transaction handling).

At the top of each proc, DECLARE @InNestedTransaction BIT;
In place of simple BEGIN TRAN, do:

IF (@@TRANCOUNT = 0)
BEGIN
   SET @InNestedTransaction = 0;
   BEGIN TRAN; -- only start a transaction if not already in one
END;
ELSE
BEGIN
   SET @InNestedTransaction = 1;
END;

In place of simple COMMIT, do:

IF (@@TRANCOUNT > 0 AND @InNestedTransaction = 0)
BEGIN
   COMMIT;
END;

In place of simple ROLLBACK, do:

IF (@@TRANCOUNT > 0 AND @InNestedTransaction = 0)
BEGIN
   ROLLBACK;
END;

This method should work the same regardless of whether the transaction was started within SQL Server or if it was started at the app layer.

For the full template of this Transaction handling within the TRY...CATCH construct, please see my answer to the following DBA.SE question: Are we required to handle Transaction in C# Code as well as in stored procedure.

Moving beyond the "basics", there are some additional nuances of transactions to be aware of:

By default, Transactions are, most of the time, not automatically rolled-back / cancelled when an error occurs. This is usually not a problem as long as you have proper error handling and call ROLLBACK yourself. However, sometimes things get complicated, such as in the case of batch-aborting errors, or when using OPENQUERY (or Linked Servers in general) and an error occurs on the remote system. While most errors can be trapped using TRY...CATCH, there are two that cannot be trapped that way (can't remember which ones at the moment, though--researching). In these cases, you must use SET XACT_ABORT ON to properly rollback the Transaction.

SET XACT_ABORT ON causes SQL Server to immediately roll-back any Transaction (if one is active) and abort the batch if any error occurs. This setting existed prior to SQL Server 2005, which introduced the TRY...CATCH construct. For the most part, TRY...CATCH handles most situations and so mostly obsoletes the need for XACT_ABORT ON. However, when using OPENQUERY (and possibly one other scenario that I can't remember at the moment), then you will still need to use SET XACT_ABORT ON;.
Inside of a Trigger, XACT_ABORT is implicitly set to ON. This causes any error within the Trigger to cancel the entire DML statement that fired the Trigger.
You should always have proper error handling, especially when using Transactions. The TRY...CATCH construct, introduced in SQL Server 2005, provides a means of handling nearly all situations, a welcome improvement over testing for @@ERROR after each statement, which didn't help much with batch-aborting errors.

TRY...CATCH introduced a new "state", however. When not using the TRY...CATCH construct, if you have an active Transaction and an error occurs, then there are several paths that can be taken:

XACT_ABORT OFF and statement-aborting error: Transaction is still active and processing continues with the next statement, if any.
XACT_ABORT OFF and most batch-aborting errors: Transaction is still active and processing continues with the next batch, if any.
XACT_ABORT OFF and certain batch-aborting errors: Transaction is rolled-back and processing continues with the next batch, if any.
XACT_ABORT ON and any error: Transaction is rolled-back and processing continues with the next batch, if any.

HOWEVER, when using TRY...CATCH, batch-aborting errors do not abort the batch, but instead transfer control to the CATCH block. When XACT_ABORT is OFF, the Transaction will still be active the vast majority of the time, and you will need to COMMIT, or most likely, ROLLBACK. But when encountering certain batch-aborting errors (such as with OPENQUERY), or when XACT_ABORT is ON, the Transaction will be in a new state, "uncommitable". In this state you cannot COMMIT, nor can you do any DML operations. All you can do is ROLLBACK and SELECT statements. However, in this "uncomittable" state, the Transaction was rolled-back upon the error occurring, and issuing the ROLLBACK is just a formality, but one that must be done.

A function, XACT_STATE, can be used to determine if a Transaction is active, uncommitable, or doesn't exist. It is recommended (by some, at least) to check this function in the CATCH block to determine if the result is -1 (i.e. uncommitable) instead of testing if @@TRANCOUNT > 0. But with XACT_ABORT ON, that should be the only possible state to be in, so it seems that testing for @@TRANCOUNT > 0 and XACT_STATE() <> 0 are equivalent. On the other hand, when XACT_ABORT is OFF and there is an active Transaction, then it is possible to have a state of either 1 or -1 in the CATCH block, which allows for the possibility of issuing COMMIT instead of ROLLBACK (although, I cannot think of a case for when someone would want to COMMIT if the Transaction is commitable). More information and research on using XACT_STATE() within a CATCH block with XACT_ABORT ON can be found in my answer to the following DBA.SE question: In what cases a transaction can be committed from inside the CATCH block when XACT_ABORT is set to ON?. Please note that there is a minor bug with XACT_STATE() that causes it to falsely return 1 in certain scenarios: XACT_STATE() returns 1 when used in SELECT with some system variables but without FROM clause

Notes about the original code:

You can remove the name given to the transaction as it is not helping any.
You don't need the BEGIN and END around each EXEC call


MySQL INSERTs waiting for UPDATEs (MyISAM)? In show processlist;, MySQL 5.5, we see that INSERTs tend to wait for UPDATEs to complete before they can proceed. This is on MyISAM tables.

We tried INSERT HIGH_PRIORITY and UPDATE LOW_PRIORITY. However, this did not change the order. Whenever there is an UPDATE in the queue, INSERTs wait for them to finish.

Is this by design? I could not find any write-ups detailing how this ordering works, any references we can read on this?

UPDATE:
I'm posting how a typical show processlist; looks. It might clarify the question.
SELECTs are working just fine. LOW_PRIORITY on the UPDATE pushes it to the back of the list. But INSERTs are waiting as well. This is database has concurrent_insert set to ALWAYS. Therefore I expect SELECTS and INSERTS to work at the same time and UPDATE LOW_PRIORITYs to take affect only when there are no more INSERTs or SELECTs waiting around. When there are no UPDATEs, concurrent_insert kicks in just fine.

What is the lock level for a MyISAM table ? Table Level Locking
This is by design : (See http://dev.mysql.com/doc/refman/5.1/en/myisam-storage-engine.html)
I wrote about before : Benefits of table level locking
You may want to take a shot at INSERT DELAYED. Note what page 410 bulletpoint 5 of "MySQL 5.0 Certification Study Guide" says on INSERT DELAYED:

DELAYED may be used with INSERT (and REPLACE). The server buffers the rows in memory and inserts them when the table is not being used. Delayed inserts increase efficiency because they are done in batches rather than individually. While inserting the rows, the server checks periodically to see whether other requests to use the table have arrived. If so, the server suspends insertion of delayed rows until the table becomes free again. Using DELAYED allows the client to proceed immediately after issuing the INSERT statementrather than waiting until it completes.

There are risks to using INSERT DELAYED. Page 411 says

If you use DELAYED, keep the following points in mind:

Delayed rows tends to held for a longer time on a very busy server than on a lightly loaded one.
If a crash occurs while the server is buffering delayed rows in memory, those rows are lost.

Looking over the whole section of the book, HIGH_PRIORITY is only applied to SELECT queries.
For more information, "MySQL 5.0 Certification Study Guide" pages 408-412 Section 29.2.
CAVEAT : When it comes to MySQL Replication, INSERT DELAYED executed on a Master is treated as INSERT on a Slave. If a master crashes, missing rows are logged in the binary logs of the Master. It would be very possible that the missing rows on the Master replicated successfully to the Slave.
UPDATE 2012-08-20 11:18 EDT
Please note what page 410 bulletpoint 3 of "MySQL 5.0 Certification Study Guide" says on LOW_PRIORITY:

The LOW_PRIORITY modifier may be applied to statements that update tables (INSERT, DELETE, REPLACE, or UPDATE). A low-priority write request waits not only until all current readers have finished, but for any pending read requests that arrive while the write request itself is waiting. That is, it waits until there are no pending read requests at all. It is therefore possible for a low-priority write request never to be performed, if read requests keep arriving while the write request is waiting.

Given this description, low-priority UPDATEs definitely take a backseat to INSERTs that have normal priority. Notwithstanding, when a SELECT occurs on a MyISAM table, all write requests take a backseat regardless of priority. If the read traffic is high enough, the precedence of INSERT over UPDATE LOW_PRIOIRTY would be negligible, nominal at best.
UPDATE 2012-08-20 11:36 EDT
If the read traffic is hindering your INSERT over UPDATE LOW_PRIOIRTY situation, I would have to recommend switching your MyISAM table to InnoDB. In fact, InnoDB can be tuned for performing heavy INSERTs. Please see my posts about tuning InnoDB for this purpose:

Feb 12, 2011 : How do you tune MySQL for a heavy InnoDB workload?
Jan 18, 2012 : Insert-heavy InnoDB table won't use all my CPU
Jul 04, 2012 : Capabilities of InnoDB INSERT Performance
Aug 17, 2012 : When to switch from MyISAM to InnoDB?


Cascading REVOKE not working in MYSQL I've read about an SQL option of cascading permissions when issuing a REVOKE statement. I've googled for how this option is implemented in MySQL, but haven't found it at all.

This is the docs page about the REVOKE syntax and there is no sign of cascading option.

The SQL standard cascade syntax has the following format:

REVOKE [GRANT OPTION FOR] privileges ON object FROM user {RESTRICT | CASCADE}

Is it possible to cascade permissions revoking in MySQL?

Neither MySQL, nor Mariadb (https://mariadb.com/kb/en/library/revoke/), has implemented RESTRICT or CASCADE.

In general, a REVOKE can negate only an identical GRANT.  You cannot, for example, GRANT ALL ..., then REVOKE UPDATE ....

That is, the GRANT/REVOKE feature of MySQL and MariaDB is quite limited.

"Roles" are available in MySQL 8.0 and MariaDB 10.0.  (But I don't think that is relevant to your question.)

  Syntax naturally differs a little from dbms to dbms.

Change "a little" to "sometimes a lot".


Creating Oracle 12c R2 user with default collation binary_ci fails If anyone can give me some guidance on this one I'd really appreciate it.

I am attempting to get a user under Oracle 12c R2 configured with default collation binary_ci.

CREATE USER <some user> IDENTIFIED BY <some password> DEFAULT COLLATION BINARY_CI;

The output I get from the above is as follows:

CREATE USER <some user>IDENTIFIED BY <some password>DEFAULT COLLATION BINARY_CI
                                     *
ERROR at line 1:
ORA-43929: Collation cannot be specified if parameter MAX_STRING_SIZE=STANDARD is set.

Now, I am doing this under a Vagrant installation from https://github.com/totalamateurhour/oracle-12.2-vagrant. This essentially sets up a basic Oracle database in PDB/CDB mode. (last time I touched Oracle was 9... something so bear with me).

My DBAs have given some guidance on this but they said they were not able to get this to work in PDB mode.

Under SQL*plus execute:

CONN / AS SYSDBA
ALTER SYSTEM SET max_string_size=extended SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP UPGRADE;
ALTER PLUGGABLE DATABASE ALL OPEN UPGRADE;
EXIT;

At the command prompt:

$ cd $ORACLE_HOME/rdbms/admin/
$ $ORACLE_HOME/perl/bin/perl catcon.pl -d $ORACLE_HOME/rdbms/admin -b /tmp/utl32k_output utl32k.sql

Then back in SQL*plus execute:

CONN / AS SYSDBA
SHUTDOWN IMMEDIATE;
STARTUP;

After executing the above and attempting to create the user as I stated earlier I still get the same error.

If I query the configuration I see the following:

SQL> show parameter max_string_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      EXTENDED
SQL> alter session set container=ORCLPDB1;

Session altered.

SQL> show parameter max_string_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      EXTENDED
SQL>

As I said, any help would be GREATLY appreciated.

Works here.

SQL> alter session set container=pdb1;

Session altered.

SQL> CREATE USER a IDENTIFIED BY a DEFAULT COLLATION BINARY_CI;
CREATE USER a IDENTIFIED BY a DEFAULT COLLATION BINARY_CI
                            *
ERROR at line 1:
ORA-43929: Collation cannot be specified if parameter MAX_STRING_SIZE=STANDARD
is set.

SQL> show parameter max_string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      STANDARD

SQL> conn / as sysdba
Connected.

SQL> alter system set max_string_size=extended scope=spfile;

System altered.

SQL> shu immediate

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup upgrade

ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  8801008 bytes
Variable Size             306185488 bytes
Database Buffers          750780416 bytes
Redo Buffers                7974912 bytes
Database mounted.
Database opened.
SQL> alter pluggable database all open upgrade;

Pluggable database altered.

SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@o71 ~]$ cd $ORACLE_HOME/rdbms/admin/
[oracle@o71 admin]$ $ORACLE_HOME/perl/bin/perl catcon.pl -d $ORACLE_HOME/rdbms/admin -b utl32k_output utl32k.sql
catcon: ALL catcon-related output will be written to [/u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/utl32k_output_catcon_6969.lst]
catcon: See [/u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/utl32k_output*.log] files for output generated by scripts
catcon: See [/u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/utl32k_output_*.lst] files for spool files, if any
catcon.pl: completed successfully
[oracle@o71 admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Sep 26 20:47:57 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  8801008 bytes
Variable Size             306185488 bytes
Database Buffers          750780416 bytes
Redo Buffers                7974912 bytes
Database mounted.
Database opened.
SQL> alter pluggable database all open;

Pluggable database altered.

SQL> alter session set container=pdb1;

Session altered.

SQL> show parameter max_string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      EXTENDED

SQL> CREATE USER a IDENTIFIED BY a DEFAULT COLLATION BINARY_CI;

User created.

However, the behaviour is unchanged in the root container:

SQL> conn / as sysdba
Connected.
SQL> show parameter max_string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      EXTENDED

SQL> CREATE USER c##a IDENTIFIED BY a DEFAULT COLLATION BINARY_CI;
CREATE USER c##a IDENTIFIED BY a DEFAULT COLLATION BINARY_CI
                               *
ERROR at line 1:
ORA-43929: Collation cannot be specified if parameter MAX_STRING_SIZE=STANDARD
is set.

The below makes it possible to overcome this, but I do not recommend it, as it is neither documented nor supported (it even allows creating a common user without the common prefix):

SQL> conn / as sysdba
Connected.

SQL> alter session set "_ORACLE_SCRIPT"=true;

Session altered.

SQL> CREATE USER c##a IDENTIFIED BY a DEFAULT COLLATION BINARY_CI;

User created.

SQL> CREATE USER a IDENTIFIED BY a DEFAULT COLLATION BINARY_CI;

User created.


Compile Views in Oracle I am currently running a very long script in Oracle for our production but we are having discrepancies in the data.

As I checked the views that the script is using (I am using Toad), I see an X mark beside the view name, then I saw an option to compile it.

I am wondering if what does a compile in a view means? Is the view not updated which causes discrepancy in the data?

If you ALTER a table that is referenced by a view, or even DROP a table, the view is marked as invalid and can't be accessed even if the changes to the table(s) would not make the view's code incorrect.

In that case the view needs to be re-compiled:

ALTER VIEW some_view COMPILE;

More details in the manual:
https://docs.oracle.com/database/121/SQLRF/statements_4004.htm#SQLRF01104