SSIS Derived Column Conditional Time Modification

So close. It'd be

time == "24:00:00" ? "00:00:00" : time

The Ternary Operator looks like (condition) ? true : false With the notable exception of the ForEach Enumerator, nowhere else in SSIS expressions are you able to use an assignment like time = "00:00:00". It'll basically be implied by whatever column or variables it's being assigned to.

If the data type of time column is actually detected as DT_TIME the above expression is likely to break because that would be outside the allowable domain for DT_TIME values but it'd actually break/fail/error at the source component level (OLE DB/ADO NET/ODBC source)


as dns domains are parsed from the end, it's useful to also store them that way - e.g:
For bar.foo.co.uk you'll keep uk.co.foo.Bar.

It makes both indexing and prefix searches simple and fast.
In the example above you'll search for uk.co.foo.% - a simple search for regular index.

Note: it's the same way many directory (ldap,dns, etc. ) searches work.


How does LEFT JOIN with WHERE clause works?

The query optimizer will decide and it is pretty smart.

SELECT * FROM tableA
LEFT JOIN tableB
  ON tableB.id = tableA.id
WHERE tableA.name = 'e'

There are many cases where doing the join first is more efficient.  If name is indexed and 'e' is somewhat unique then do that first is more efficient.

This is different

SELECT * FROM tableA
LEFT JOIN tableB
  ON tableB.id = tableA.id
WHERE tableB.school = 'EE'

That changes the left join into an inner join but since there is a match in your data you would get the same results.


Gap Locking in Read Committed isolation level in Mysql

Gap locks are necessary under the read committed (RC) isolation level to prevent potential integrity violations due to concurrent inserts -- this is what the documentation statement

  Gap locking is only used for foreign-key constraint checking and duplicate-key checking.

implies.

Suppose transaction T1, with the RC isolation, updates the value of a column that is part of a unique key (index). Clearly this is only possible if the new key value does not already exist. Suppose also transaction T2, also with the RC isolation, attempts to insert a new record with the key value equal to that just created by T1. Due to the RC isolation level T2 does not see the change made by T1, since it has not been committed yet, so the duplicate key error is not raised. If it weren't for a gap lock on the unique index in question, this would eventually result in two records with the same purportedly unique key.

With MySQL 8 you can see this in action using the views data_locks and data_lock_waits in performance_schema. I've created this sample table:

create table fruits (
  id int not null auto_increment primary key,
  name varchar(10),
  property varchar(20)
);
create unique index fruits_ux1 on fruits (name, property);
insert into fruits (name, property)
values ('apple', 'red'), ('apple', 'tart'),
       ('banana', 'yellow'), ('banana', 'sweet');

In transaction 1 I update banana properties:

T1> set transaction isolation level read committed;

T1> begin;

T1> update fruits set property = concat(property, '?') where name = 'banana';

I can see that key entries with new values have gap locks set on them (these rows don't really exist until the transaction is committed, so standard X locks can't be used here):

mysql> select thread_id, object_name, index_name, engine_lock_id, lock_type, lock_mode, lock_data  from performance_schema.data_locks;
+-----------+-------------+------------+---------------------------------------+-----------+---------------+------------------------+
| thread_id | object_name | index_name | engine_lock_id                        | lock_type | lock_mode     | lock_data              |
+-----------+-------------+------------+---------------------------------------+-----------+---------------+------------------------+
|        47 | fruits      | NULL       | 140491829069632:1066:140491735749880  | TABLE     | IX            | NULL                   |
|        47 | fruits      | fruits_ux1 | 140491829069632:5:5:1:140491735746840 | RECORD    | X             | supremum pseudo-record |
|        47 | fruits      | fruits_ux1 | 140491829069632:5:5:4:140491735746840 | RECORD    | X             | 'banana', 'yellow', 3  |
|        47 | fruits      | fruits_ux1 | 140491829069632:5:5:5:140491735746840 | RECORD    | X             | 'banana', 'sweet', 4   |
|        47 | fruits      | PRIMARY    | 140491829069632:5:4:6:140491735747184 | RECORD    | X,REC_NOT_GAP | 4                      |
|        47 | fruits      | PRIMARY    | 140491829069632:5:4:7:140491735747184 | RECORD    | X,REC_NOT_GAP | 3                      |
|        47 | fruits      | fruits_ux1 | 140491829069632:5:5:6:140491735747528 | RECORD    | X,GAP         | 'banana', 'sweet?', 4  |
|        47 | fruits      | fruits_ux1 | 140491829069632:5:5:7:140491735747528 | RECORD    | X,GAP         | 'banana', 'yellow?', 3 |
+-----------+-------------+------------+---------------------------------------+-----------+---------------+------------------------+

In transaction 2 I now try to insert a possible duplicate:

T2> set transaction isolation level read committed;

T2> begin;

T2> insert into fruits (name, property) values ('banana', 'sweet?');

The statement blocks, and I can see that it blocks on the gap lock for that unique index key (note the blocking_engine_lock_id value):

mysql> select requesting_engine_lock_id, blocking_engine_lock_id from performance_schema.data_lock_waits;
+---------------------------------------+---------------------------------------+
| requesting_engine_lock_id             | blocking_engine_lock_id               |
+---------------------------------------+---------------------------------------+
| 140491829070536:5:5:6:140491735753104 | 140491829069632:5:5:6:140491735747872 |
+---------------------------------------+---------------------------------------+

Similarly, if T1 deletes a row from a parent table, while T2 attempts to insert a new record to a child table referencing the row just deleted by T1 (which it still sees), the gap lock on the foreign key index prevents insertion of a potential orphan record.


Is there a way to determine locks that a given query might apply on SQL objects?

No there's no way of knowing without running it.

The problem with opening a transaction is that you still won't see all the locks taken. At read committed the shared locks are released as soon as the row is read not end of the transaction. And even for locks that are held till end of transaction if a lock is escalated you miss the higher granularity locks taken out before that.

If this is a dev machine you can use trace flag 1200 (undocumented)

DBCC TRACEON(1200,3604,-1);
SELECT ....
DBCC TRACEOFF(1200,3604,-1);

This will output the lock events to the messages tab, it is often best to execute the batch twice as the first run will contain lots of stuff related to compilation as well as the execution.

It is also possible to use SQL Server Profiler/Trace and extended events to see locks acquired and released.

Again this is best done on a dev server as these are potentially extremely frequent events and even with filtering would likely add a great deal of overhead on a busy server.


PowerPivot doesn't install in SharePoint mode on an Active/Passive Cluster

We just got off the phone with Microsoft about this very same question this morning.  According to MS, there is an internal document from the product team stating that this is an unsupported scenario.  Which really stinks because we wanted to leverage our existing cluster licensing for SSAS rather than paying for an additional stand alone instance.  At any rate, hope this helps.  I have requested additional guidance if this is going to be tested and supported in a future CU or SP or if MS is going to remove the capability of allowing someone to install a clustered instance of SSAS in SharePoint Mode through the installer.


sp_Blitzcache doesn't return as many results as sys.dm_exec_query_stats for a given query

Yes, there's a known issue with that parameter not returning all results due to spaces, and I wouldn't be surprised if that's also affecting you:

https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/2202

However, let's zoom out a little: you can't rely on a text search of the plan cache to find everything updating a table. You could have multiple spaces, tabs, even line returns or comments between the word UPDATE and the start of your table name. The table name might also be prefixed with the database name or schema name.

As a result, I wouldn't recommend this technique to find queries that update a particular table.


When you don't provide any location,SQL Server by default uses server level settings which are provided during installation.What this means is if you don't provide any location while creating database,SQL uses this location by default..This can be changed at any time and can be found here

Further while creating a database,You also can provide location like  below,if you want any database to be created at different location

CREATE DATABASE [sdsd] ON  PRIMARY
( NAME = N'sdsd', FILENAME = N'new path ' , SIZE = 2048KB , FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'sdsd_log', FILENAME = N'new path' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

If you are looking to change the existing database location,here is an answer which goes in depth ..

How do I attach a database in SQL Server?


Matching string with LIKE derived from sub-query


Named point in the transaction log? Does it exist?

You can use a Log Mark to enable a restore to specific logical point-in-time of a database, but you can't use that in queries to the main database.

You can take a Database Snapshot to present a point-in-time consistent copy-on-write snapshot of an existing database.


Attach database from SQL Express 2008 Edition to SQL Server 2008 Enterprise Edition

I always like to take the cautious approach to administration.

Before making any changes to the environment, I would suggest your friend take database backups of all relevant databases at source.

Then, rather than using attach/detach. Restore the database to the new instance.


Maatkit shows the MySQL replication error in one table, but won't fix it

I think a chunk is a set number of rows to be scanned and checksummed.

I never did like chunks because if the table order matters, each chunk could be completely different if just one row was written ahead or behind another.

Try not setting a chunk size and see if the entire table is checksummed as a whole as a opposed to each chunk checksum.

You can also run mk-table-sync using the --print option and redirect to a text file to see if there is any SQL created that would have been executed to sync the slave had you used the --execute option. If nothing comes out into the text file from mk-table-sync, you will have to resort to doing physical copying (for MyISAM) or mysqldump the table and loading it on the slave (for InnoDB).

Give it a Try !!!


Couple of options :

A. Native to sql server (no third party):

-- make sure to have it in order due to Foreign keys + if your tables have Identity columns then Identity insert should be ON + you have to order by date to get matching data from all the tables.

select 'select top(100) * from '+ name +char(10)+ 'go' from sysobjects
where type = 'U' -- for user tables

Option 2: BCP OUT and BULK Insert.

See my script at https://dba.stackexchange.com/a/43232/8783

Option 3: SSIS wherein you can specify top rows to be extracted from the source database using T-SQL Script task. Sill here you have to manage FK's , Identity columns, etc.

Option 4: Use third party or tools from codeplex (all free)

SSMS Tool pack. Very good and freebie (till SQL Server 2008R2) and it has Insert statement generator
DBScripter
Sql Script Generator

  Licensing only applies to SSMS 2012 and higher versions.
  For previous SSMS versions the SSMS Tools Pack is still FREE.


Test this:

select sum_duration,
       app_name,
       app_id,
       user_id
from ( select get_data.*,
              if( @user_id = get_data.user_id,
                  @rownum := @rownum + 1,
                  @rownum := 1 + least(0, @user_id := get_data.user_id)) row_number
       from ( select sum(duration) sum_duration,
                     app_name,
                     app_id,
                     user_id
              from timesheet
              group by app_id ) get_data,
            ( select @rownum := 1, @user_id := 0 ) init_vars
       order by user_id, sum_duration DESC
     ) subquery
where row_number <= 5


Some queries are very slow after index rebuild

Fragmentation on its own is not necessarily the evil demon it is made out to be.  If your database files are stored on a SAN, or some types of RAID array, or perhaps on SSDs, index defragmentation will make very little difference since pages will be spread all over the disks no matter if they are fragmented or not.  See this answer for a discussion of that:  Is there any benefit to defragmenting SQL indexes in a SAN environment?

When you rebuild indexes, you should not indiscriminately set FILL_FACTOR = 80 - that setting needs to be evaluated on a table-by-table basis.  Assuming the tables were at FILL_FACTOR = 100 prior to your rebuild, you have effectively increased the storage required for each table by 20%.  That may be a good part of the reason why things are slower now.

Using OPTION (MAXDOP 1) on ALL queries is a bad idea as well.  You should allow the query engine to decide whether to execute a parallel query or not.  Set the appropriate MAXDOP option at the server level using EXEC sp_configure 'max degree of parallelism', X where X is the maximum number of cores to use for parallel queries.  See this question for a discussion on MAXDOP:  What is a good, repeatable way to calculate MAXDOP on SQL Server?

Adding a non-clustered index on a field (or fields) will certainly make the query run faster if the database engine decides to use the index.

You may want to ensure the statistics on the affected tables are up to date.  See http://technet.microsoft.com/en-us/library/ms187348.aspx for the Books Online description of how to do that.  SQLSkills also has an excellent article on statistics, here: http://www.sqlskills.com/blogs/erin/understanding-when-statistics-will-automatically-update/


You can use analytic functions to achieve this:

select distinct pm.city cityid,
    to_char(trunc(pm.sampledate, 'MM'), 'YYYY-MM') as timepoint,
    count(distinct me.id)
        over (partition by pm.city, trunc(pm.sampledate, 'MM')) as mecount,
    count(distinct me.hotpointid)
        over (partition by pm.city, trunc(pm.sampledate, 'MM')) as hpcount,
    count(distinct me.id)
        over (partition by trunc(pm.sampledate, 'MM')) as totmecount,
    count(distinct me.hotpointid)
        over (partition by trunc(sampledate, 'MM')) as tothpcount
from upme me
join appmday pm on pm.apid = me.id
where pm.sampledate between to_date('2011-11-01', 'yyyy-MM-dd')
    and to_date('2012-1-31', 'yyyy-MM-dd');

Or instead of a distinct, use a subquery and a row_number() calculation to remove the duplicates:

select cityid, to_char(timepoint, 'YYYY-MM') as timepoint,
    mecount, hpcount, totmecount, tothpcount
from (
    select pm.city cityid,
        row_number() over (partition by pm.city, trunc(pm.sampledate, 'MM')
            order by pm.city, trunc(pm.sampledate, 'MM')) as rn,
        trunc(pm.sampledate, 'MM') as timepoint,
        count(distinct me.id)
            over (partition by pm.city, trunc(pm.sampledate, 'MM')) as mecount,
        count(distinct me.hotpointid)
            over (partition by pm.city, trunc(pm.sampledate, 'MM')) as hpcount,
        count(distinct me.id)
            over (partition by trunc(pm.sampledate, 'MM')) as totmecount,
        count(distinct me.hotpointid)
            over (partition by trunc(sampledate, 'MM')) as tothpcount
    from upme me
    join appmday pm on pm.apid = me.id
    where pm.sampledate between to_date('2011-11-01', 'yyyy-MM-dd')
        and to_date('2012-1-31', 'yyyy-MM-dd')
)
where rn = 1;

If your existing query is slow you may see a performance difference between the two options, otherwise it's a matter or preference.

(Also you may be aware of this, but if your sampledate has a time component, having your where clause end on '2012-1-31' means you'll only get data up to the start of that day, and not for example anything at 2012-01-31 01:00.)


Cannot find either column "dba" or the user-defined function or aggregate "schema.functionName", or the name is ambiguous

If this is for SQL Server (and maybe others as well?), then you cannot SELECT a TVF, you need to SELECT FROM a TVF ;-) And, you need to use those silly parenthesis when calling functions:

SELECT * FROM [dba].ufn_track_dailies();

If it were a scalar function, that would be:

SELECT [dba].ufn_track_dailies();


Which is the better schema design for a voting system?

Approach 1 is the cleanest with no redundancy. However I can understand your worries about scanning through a huge table just to count a post votes.

So approach 2 which features some "caching" seams fair.
The downside is that you add some redundancy but this is not even the main issue. You also lose consistency. It's possible to have a post with 500 votes while the votes column in the post table only says 300. This is a potential risk.

So it's the choice between a clean and elegant approach and increased performance that though comes along with redundancy and loss of consistency.

In my opinion it'd be OK to go with the 2nd option though it's somewhat crappy.
I think though that the performance difference will not be that huge as one could expect.
For example every time you're displaying a Question and it's answers you are doing a SELECTION on ALL Answers ever given to all questions.
Best way would be to put the 1st and 2nd option to a test on a massive amount of test data but for the time being I'd go with the first option. It is the best and cleanest and performance optimization can be done better elsewhere.

Note you save yourself the time to update 2 places with every vote.


MySQL. Capture Cis program makes incorrect query: SELECT DISTINCT "PartType" FROM "Capacitor" ORDER BY "PartType"

Solved, the problems was the ODBC connector. I have to use the ODBC MySQL 5.1 instead of 3.5, due to my MySQL server uses 5.5.

This solve the problem with the "


GROUP BY with date ranges when values change but then change back

A common way of doing this is to compare the total order (regarding date) vs the relative order within each value, let's call the difference grp. If the grp changes, it means that date from another partition of value interfered with the current group. So by picking the min date for each value, grp we can achieve what you want. I'll leave days_at_this_val as an exercise for the reader;-)

select v, min(d) as mind, max(d) as maxd
from (
    select v, d
         , row_number() over (order by d)
         - row_number() over (partition by v order by d) as grp
    from t
) as tmp
group by v, grp
order by min(d);

DB<>fiddle

For future postings, you will get more (and thereby better) answers if you simplify for those trying to answer, by posting ddl and sql instead of ascii tables:

create table t (v int not null, d date not null);

insert into t (v,d) values ( 3   , '1/1/2019'),
                           (3   , '1/2/2019'),
                           ...
                           (3   , '1/14/2019');

or by creating a fiddle like the one I provided in the link.


How to efficiently count rows in a query by limiting the number or found rows?

First, it's almost never OK to use LIMIT without ORDER BY, because you will not get consistent results across different executions of the query. So, especially for the pagination queries, I'd use:

SELECT <list of columns>
FROM <tables>
WHERE <conditions>
ORDER BY <some columns>
LIMIT rows_per_page
OFFSET x ;

Now, to find the number of pages this query could return - and with the extra condition that you will not use more than 50 pages - I'd try a separate query;

SELECT CEIL( COUNT(*) / 50 ) AS pages
FROM
  ( SELECT NULL
    FROM <tables>
    WHERE <conditions>
    LIMIT 50 * rows_per_page
  ) AS x ;

(and that is an exception to the "never use LIMIT without ORDER BY" rule above :)


What options are there for security on shared data sources for SSRS?

We are in the exact same scenario.

What you could do here is to deploy the projects into different environments / targets on the report server. For example you could create different "folders" in the reportserver and deploy it multiple times. Then configure the sql login with different passwords there once and prevent to overwrite the datasources during deployment to protect it from getting overwritten. This can be configured in the project's settings.


In SQL Server Management Studio (SSMS) there is a command to Delete Blank Lines, though it is not available on a menu, toolbar icon, or keyboard shortcut by default.

If you have to do this regularly, edit your menus, toolbars, or shortcuts to add this command. For example, to add this command to the Edit/Advanced menu:

From the Tools menu, choose Customize:

In the window that appears, choose the Commands tab, then drop down the Menu Bar list to select Edit | Advanced:

Then click Add Command

Now select the Edit category and scroll the Commands list to select Delete Blank Lines:

Once this is done, Delete Blank Lines will appear in the Edit/Advanced menu. There are other options in the Customize dialogs shown above to position the command in the menu, add a keyboard shortcut, or add an icon for it to a toolbar.


I reached out to another DBA, and they said they had seen similar issues in the past, without a clear cause, but that adding the articles 1 at a time had resolved the issue, that seemed to work in this case as well.  Adding 1 table at a time vs adding 10 tables at once allowed the tables to be created, and they are now replicating successfully.


MySQL Error Code 3019 (Undo Error Log, no space left)

Is possible to TRUNCATE TABLE ? If yes, please try that.

Is MySQL server binary log enabled ? If YEs, Purge binarylogs & then delete records.

Is MySQL Server enabled with general log or Audit Log ? If Yes, Purge general log

Check error log size & path if needed purge it.

Note: Delete statement will not release space & if your server is INNODB_FILE_PER_TABLE=OFF then TRUNCATE or DROP statement will also not reclaim the space


Shopping cart database, should order object have a relationship with the product, or should I duplicate the product information

If the item changes or gets updated, it should get a new ItemID. You need the historic record. So say you have a shirt and it comes in blue ItemID Shirt. If you offer it in Green change the new ItemId to be ShirtGreen. iF it is an upgrade, say offering blue shirt with a  pointed collar: ItemID: ShirtPointedCollar.


MongoDB Stitch triggers enable you to execute application and database logic automatically, either in response to events or based on a pre-defined schedule.

As per MongoDB official documentation, Stitch supports three types of triggers:

  Database triggers, which can automatically respond when documents are
  added, updated, or removed in a linked MongoDB collection.

  Authentication triggers, which execute additional server-side logic
  when a user is created, authenticated or deleted.

  Scheduled triggers, which execute functions at regular intervals
  according to a pre-defined schedule.

The Scheduled triggers may help you in achieving your expected behavior.


Two tables, one foreign key, and two primary keys

Just to clarify:  Every student can have either 0 or 1 associated TeamName value, with this setup.

Having StudentTeams without StudentID as a foreign key would allow records with invalid student IDs, so that is presumably a pro. Similarly, if StudentID were not the primary key of StudentTeams, then a student could be attached to multiple team names, also presumably a pro (the assumption being that those are your business rules).

However, if this is literally the table structure, then it would actually be better for TeamName to be a field in the Student table, with NULL values allowed. Each student could have 0 or 1 TeamName values, and no separate relationship (or table) is required. So, that's a con.

In my experience, I've only seen a couple of situations where it made sense to have TableA with a primary key of A_ID, and then to have TableB which uses A_ID as both a primary and a foreign key:

Performance: If TableA is used in many places, by many parts of the application, and the data in TableB is only used in a isolated part of the application (a set of commands that only 2% of the user base have access to, or that are only used at year-end), and each row in TableB is large (for instance, if Student.Name is, on average, 40 characters long, and StudentTeams.TeamName is, on average 4000 characters long), then queries that don't need TeamName might perform much more quickly if it's not in the Student table.
Parallel table: If TableA's structure is not under your control, so you cannot add TeamName to it, then maintaining TableB as a "parallel table" (my own term; a separate table that will only have a row if there's a matching row in TableA) might be the only way to do things. I once set up a number of these tables, when I needed to built an application that used data from a third-party vendor's application, but where our app needed to supply additional data that didn't exist in the vendor's application. The foreign key is necessary, as it can keep your information properly tied to the vendor's data (cascading updates and deletes), without modifying the vendor's application.
Logical Separation: If the data in TableA and TableB are related, but are sharply divided in purpose, then it may make sense to store them in separate tables. As with the previous example, Making TableA's primary key be primary key and foreign key in TableB guarantees that you'll only have a TableB entry if the TableA entry exists, and you have to eliminate the TableB row to delete the TableA row.  I've worked with a system with a Task table (with a list of work to be done), and a separate TaskHistory table (with information about the conditions under which the task was done, consumables used by the task, etc.). This can simplify business rules; a field could be NOT NULL in TaskHistory, even if it could not possibly be filled in until the task was done; if the field were in the Task table, then a special business rule would have to exist, guaranteeing that certain fields were not NULL once the task had reached completed status. Note that this will often be a mix of logic and performance: there were a number of queries in the application that only had to hit the Task table.
Further Relational Requirements: As noted by ypercubeᵀᴹ in the comments, one could use this to allow a third table to use the StudentID from the StudentTeams table as a foreign key, guaranteeing that the third table only linked back to students who were on a team. Personally, I'm not sure that I would implement a structure this way. This could lead to confusion as to the meaning of StudentID; it would always link back to a Student record, but it would not always link back to a StudentTeam record (although, admittedly, in the context of this third table, it would always link back to both a Student and a StudentTeam). I would be inclined to create a StudentTeamID value in StudentTeams, and link to that, as its meaning would be immediately clear (it would only link to students on a team).


Problem with shortest distance calculation in PostGIS

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

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

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

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

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


Mysql/InnoDB says it doesn't have access rights to create temp tables, but it does

Found a solution:

Apparently we needed to use k to give lock file permission. So now usr.sbin.mysqld looks like this:

  /var/mysql_tmpfs/ r,
  /var/mysql_tmpfs/** rwk,

We found the answer on the oracle blog: https://blogs.oracle.com/jsmyth/entry/apparmor_and_mysql


CREATE TABLE AS SELECT creates non-nullable columns

Either create the table manually beforehand, or specify the column names an NULLability in the CTAS statement:

create table blah2
(
  ctascolumn1 not null,
  ctascolumn2 null
)
as
select col1, col2 from blah;


Upgrading to MySQL 5.6, benefits to switching to GTID replication?

I would say that you benefit from future proofing your environment, because inevitably you will want to add more slaves.

I also don't like having to remember specific coordinates of what statements have applied/haven't when doing a restore.  Having the server know the current set of applied statements is very useful to prevent duplicate processing/rogue corruption.  In 5.7 mysqlbinlog even has a --idempotent mode.

Operationally GTIDs are much easier to manage, and are the future :)

I recommend row-based replication as well - I think it's a great option:
http://www.tocker.ca/2013/09/04/row-based-replication.html


How to convert Postgres from 32 bit to 64 bit

You can't use pg_upgrade for this, as it can only upgrade clusters from versions that are built with the same options on the same architecture. You must either:

dump and reload, with the associated down-time; or
Run both the 32-bit and 64-bit versions concurrently and set up a 3rd party replication tool like Bucardo, Londiste or Slony-I to stream rows across. Once they're in sync, stop the 32-bit DB and remove the replication.

Also, there's no PostgreSQL 8.5. Did you mean 8.4? "v9" isn't very specific either - PostgreSQL 9.0, 9.1, and 9.2 are all quite distinct releases. See the version policy, which outlines PostgreSQL's quirky versioning.


Mongoimport 'error connecting to db servers' for cluster

Replacing mongodb:// at the start with ClusterADS-shard-0/ (which is the replica set) and removing /admin?... made it work.


SQL query help: Calculating days between observations

I think in this case, you best best since you are on SQL 2012 is to use Window Functions and specifically the LEAD function.

Here is an example to try and match your data.

Say I created a table with the columns you have here in the following way and then insert rows into that:

CREATE TABLE #AnimalBehavior
(
    ID int identity(1,1) primary key clustered
    ,AnimalID int
    ,DateID int
    ,Behavior1 int
    ,Behavior2 int
    ,Behavior3 int
);
GO

insert into #AnimalBehavior (AnimalID, DateID, Behavior1, Behavior2, Behavior3)
Values (1, 20150101, 0, 1, 1)
    ,(1, 20150201, 0, 1, 1)
    ,(1, 20150301, 0, 1, 1)
    ,(1, 20150401, 0, 1, 1)
    ,(2, 20150101, 0, 1, 1)
    ,(2, 20150301, 0, 1, 1)
    ,(2, 20150501, 0, 1, 1);
GO

From here I will want to only look at the times where your behavior3 is greater than 0 (so that we know it occurred during that period) and take the difference between the LEAD of the 2nd compared to the initial row, and the LEAD of the 3rd compared to the LEAD of the 2nd.  You can do that in this way:

With ab
as
(
Select AnimalID
, DateID
, LEAD(DateID, 1, 0) over (Partition By AnimalID order by DateID) DateID_1
, Lead(DateID, 2, 0) over (Partition By AnimalID order by DateID) DateID_2
, row_number() over (Partition By AnimalID order by DateID) as rown
from #AnimalBehavior
where Behavior3 > 0
)
Select AnimalID, Convert(date, Convert(varchar(8), DateID))
, DateDiff(dd,  Convert(date, Convert(varchar(8), DateID)),  Convert(date, Convert(varchar(8), DateID_1)))
, DateDiff(dd,  Convert(date, Convert(varchar(8), DateID_1)),  Convert(date, Convert(varchar(8), DateID_2)))
from AB
where rown = 1;

I believe this covers what you are looking for.  But feel free to ask questions if not.


Generate English sentence and omitting zeros

One way you could achieve the goal is by presenting each row as a column (or unpivoting each row) and applying string_agg to it with and as a delimiter. More specifically, it could be implemented like this:
WITH
  tmp(a, b) AS
  (
    VALUES
      (0, 1),
      (2, 3),
      (4, 0)
  )
SELECT
  a,
  b,
  (
    SELECT
      string_agg(word, ' and ')
    FROM
      (
        VALUES
          (nullif(a, 0) || ' A'),
          (nullif(b, 0) || ' B')
      ) AS v (word)
  ) AS sentence
FROM
  tmp
;

As you may have noticed, there is slightly more going on here than just unpivoting and aggregation. In particular, the query is also applying nullif to each value in order to replace a 0 with a null. That way, when a null is concatenated with a string, the result will be a null as well. And when string_agg comes across a null, it simply omits it.
So, for example, unpivoting the first row – (0, 1) – results in a column that looks like this:
null
1 B

Aggregating this column with string_agg yields 1 B, because the null is omitted and there is only one value left, so the delimiter is not used.
In contrast, when unpivoting the second row, we get:
2 A
3 B

We have more than one value here, and when string_agg is applied, it uses the specified delimiter  and  to delimit the values, yielding the string 2 A and 3 B.
And in the last row, we again have only one non-null value, so we get another string without an and.
This solution may not look much more elegant than the CASE solution in your question when applied to only two columns. However, it scales better in case you have more columns to concatenate, as you would only need to add more rows to the correlated query's VALUES constructor:
VALUES
  (nullif(a, 0) || ' A'),
  (nullif(b, 0) || ' B'),
  (nullif(c, 0) || ' C'),
  (nullif(d, 0) || ' D'),
  ...

You can check this query out here at db<>fiddle.uk.


Five tables or a single table with an identifier column?

Tables with the same structure but different sets of rows are sometimes called orthogonal tables. I don't believe in universal truths in the database world (which means: your use case could have peculiarities that I am not considering), but in general I consider orthogonal tables a bad idea.

From a theoretic perspective I can tell you that I especially don't like using metadata (table names) as data (age range). But what does it mean?

Tables designed in this way prevent you from running easily multi-table queries, like these:

SELECT AVG(age) FROM big_table;
SELECT up, COUNT(*) FROM big_table GROUP BY up;

Of course you will be able to obtain the same information with multiple queries, or with UNION. But it seems to me less clean and, should you ever need to add or remove a table, or change the usage of up (from 20,25,30 to 20,30,40), you'll need to make much more changes.

There are even more obvious case when the "one table" solution is better: maybe one day you'll need a query which returns people whose age is less than 23.

Flexible designs are usually better. For specific use cases, you can always create views, if really needed.


Multiple SQL queries fired asynchronously progressively slow down

In order to understand what is happening, I would gather more metrics. First, don't use Profiler, it adds it's own overhead. Assuming you're on 2012 or better use Extended Events. Further, using Extended Events create a session that not only captures rpc_completed and batch_completed for your queries, but also add wait_info_external and wait_completed. With all this add causality tracking. Here you'll be able to see exactly what the query is waiting on as well as how long it takes to run.

On top of this, get the execution plans for when it runs well and when it runs slowly. Compare the two to find any differences.

All this should show you where the problem lies.


Disable parameter sniffing for better performance - good idea?

Query/index tuning is always a good idea but independent of parameter sniffing. Ideally, every execution plan would optimal and reusable but trade-offs must be made when the optimal plan varies depending on the supplied values (non-trivial queries).

You could use the OPTIMZIE FOR UNKNOWN query hint (or local variables) for non-trivial queries so that the plan is generated based on average density estimates rather than the stat histogram of the provided parameter value. This will generate a more stable plan that can be cached and reused. However, this compromise plan could be less efficient for certain parameter values and very costly is some cases. Another option is the OPTIMZIE FOR hint with specific values, which is appropriate when the plan generated for unknown values isn't satisfactory.

OPTION RECOMPLE avoids plan caching and the risk of reusing an inappropriate plan at the cost of compilation upon each execution. That is not a concern for infrequently executed queries but can be very expensive for an OLTP workload where the query is executed many times per second. Consequently, you need to consider your workload, variation in parameter values, and execution frequency to determine the optimal approach for your workload.


Installing Oracle Forms and Reports on a 64bit machine

This couldn't have been easier

Forms and Reports here (Fusion Middleware) and alternative (non-Fusion) here. You may need an app server - not sure.

This was a relatively easy Google - that normally merits a -1, but since it's your first post here, I'll let it slide. Do they not have similar rules about question research on StackOverflow (where you have a rep. of nearly 1.5k)?


mysqld_pre_systemd fails when starting MySQL on Fedora

As mentioned in the comments, the /usr/sbin/semanage file was not working properly. After using the dnf provides command (dnf provides /usr/sbin/semanage), dnf told me that this file was from the policycoreutils-python-utils-3. 1-4.fc33.noarch, so I reinstalled it with dnf reinstall (dnf reinstall policycoreutils-python-utils-3.1-4.fc33.noarch), rebooted, and my problem was solved.
However, I ran into another problem, again in the same place, which I've listed here.


MySQL Using a trigger to update quantity on another table if part is used

You need to have DELIMITER directives both before and after the CREATE TRIGGER statement, you need to end individual simple statements with semicolons, and you need to end the complete CREATE TRIGGER with the delimiter specified in the first DELIMITER directive:

DELIMITER //

CREATE TRIGGER subtract_quantity
AFTER INSERT ON parts_used FOR EACH ROW
BEGIN
  UPDATE parts
    SET parts.quantity = parts.quantity - 1
    WHERE parts.idParts = NEW.idParts;
END
//

DELIMITER ;

However, in this case your entire trigger body is a single UPDATE statement, so you can omit both the BEGIN ... END statements and the DELIMITER directives:

CREATE TRIGGER subtract_quantity
AFTER INSERT ON parts_used FOR EACH ROW
  UPDATE parts
    SET parts.quantity = parts.quantity - 1
    WHERE parts.idParts = NEW.idParts;

(The DELIMITER directives allow the parser to distinguish between the end of a compound-statement trigger or procedure body and the ends of the individual simple statements it contains, and are only required by some MySQL clients.)


Should "Long Running with Low CPU" be expected for an SPROC that performs millions of INSERTS?

The calcuation of the "long running query with low cpu" metric in sp_BlitzCache at the time of writing is

long_running_low_cpu = CASE WHEN AverageDuration > AverageCPU * 4 AND AverageCPU < 500. THEN 1 END

With AverageCPU = (total_worker_time / 1000.0) / execution_count

And  AverageDuration  = (total_elapsed_time / 1000.0) / execution_count

Both from the dmv sys.dm_exec_query_stats;

So this means that your average cpu time for this query is lower than 0.5 seconds while your AverageDuration is higher than 4 times your AverageCPU for this query.

The difference between these two metrics would be a next step to look at to see if there is an actual problem at hand.

The reason for the difference between the two is something for you to figure out by conducting workload monitoring.

One of these common reasons include blocking, such as the insert being blocked by another query, causing low cpu time with a higher duration.

Another reason to look into would be resource limits on the database engine or the application server.


Two thoughts come to mind.

If you are concerned that this update may not properly affect the table the way you think, have you thought about putting the update inside a transaction.
You could do the update, query the data, and if it all looks okay, Commit the Transaction.  If it fails, you could perform a Rollback.

Alternative

You may want to look at the BCP utility to extract the table to a flat file outside of the SQL Server.

Presumably, you could store the contents of the table in a location where you aren't under as much storage pressure.  If the update process fails, you could attempt to restore the contents back into your table.


Using intermediate result in backend with node-postgres

You will need to store the intermediate result in a temporary table, but there is no way to avoid the two queries:
create temporary table temp_result
as
SELECT task_id
FROM _tasks
WHERE onetsoc_code IN
    (
    SELECT onetsoc_code
    FROM _alternate_titles
    WHERE alternate_title ILIKE '%Beta Tester%'
    );

Then, using the same (physical) connection to the database, you can run the query to retrieve the intermediate result:
select *
from temp_result;

And the actual result you want:
SELECT dwa_id
FROM _tasks2dwa
WHERE task_id IN (select task_id from temp_result);

If you only want (or get) each dwa_id only once, another option is to aggregate the task_ids into an array:
Something along the lines:
SELECT td.dwa_id, array_agg(t.task_id) as task_ids
FROM _tasks2dwa td
  JOIN _tasks t on td.task_id = t.task_id
WHERE t.onetsoc_code IN (SELECT alt.onetsoc_code
                         FROM _alternate_titles alt
                         WHERE alt.alternate_title ILIKE '%Beta Tester%'
GROUP BY td.dwa_id;


Restarting Management Studio fixed it.

It looks like it was some sort of glitch - for some reason, it was acting like I was using AppUser to run the query. I double checked and I was definitely logged in using sa, so I'm not sure what happened.

I realised when I tried to change permissions for the new schema, which had previously completed successfully and was now giving the same message. I tried to remove the login and just try setting it up again, and it gave the error that the user was still logged in, despite me closing all instances of Management Studio aside from the one I was currently using with the sa account. I tried to kill the session using the advice from this post, but it gave an error about not having permission (at this point I double checked yet again that I was definitely using the sa account). In the end I closed the current instance too, logged back in as sa, and it has worked fine since - I can grant/deny on the new schema and dbo for AppUser.


"IN ( SELECT ... )" optimizes very poorly; switch to a JOIN.
CREATE/DROP TABLE is slow, especially on Windows.
ReportThreeStamp seems static; why not build it as a non-TEMPORARY TABLE, and do so only once.
(case when (SUM(count) is null) then 0 else SUM(count) end) --> IFNULL(SUM(count), 0))
Your temp tables seem to have no indexes; this leads to slow table scans.  Suggest this syntax to create an index while creating and populating:

CREATE TABLE x ( PRIMARY KEY (y) ) SELECT ...
How many rows in the tables?  If millions, we should talk about "summary tables" and how they could probably speed up the processing 10-fold.


SSMS 17.1 stopping autocomplete suggestion of variable types

For example, if i type NVARCHAR, the autocomplete suggests nvarchar
  and once i continue typing, it automatically correct the word.

This autocompletion is not just a "convention", SQL Server types that you can see querying sys.types are all lowercase, and this is a good reason to use them ALWAYS lowercase:

if one day your code migrates to a server with case sensitive collation, no one of your UPPERCASE types will be recognized as system type.

So yours is not a good convention and I think you cannot change this at least in SSMS because they(MS) are aware of this possible issue.

You can look for some customizable code formatter in internet


Display the the result of a substraction in a MySQL query (1 table)

I'd try this :

SELECT ROUND(b.price - a.price, 2) AS Returned
FROM stop a,(select price from stop  where cityName = 'B') as b
WHERE a.cityName = 'A' AND a.`routeNo ` = '1';

  OR even this :

SELECT ROUND(b.price - a.price, 2) AS Returned
FROM stop as a join stop as b
ON a.cityName = 'A' AND b.cityName = 'B'
WHERE a.routeNo = 'x';

  (Updated)


tsql: table data size won't shrink after large row deletion

Try ALTER INDEX ALL ON mytable REBUILD

With the index size value being so high, this points to a non-clustered index. Clustered index size is very small usually.

You can check with the allindexes parameter on the DBCC

dbcc showcontig ('Risk') with allindexes, tableresults;

For example, one of my tables. A billion rows, 70GB table, 450MB index

name      rows       reserved  data     index_size  unused
BigTable  1041302384 71256288  70793880 460832      1576

Edit, Jan 2012

Note that deleted records are not physically removed on DELETE. A "ghost clean up" process does that afterwards.


Error trying a manual's example in DB2 11.1.4.4

The problem is that the database must be encoded in UTF-8.

https://developer.ibm.com/articles/sql-json-publishing-functions/

  JSON data consists of a sequence of Unicode characters that are arranged in a hierarchical form based on the JSON value syntax and grammar


After researching, I found that we need to find the pg_hba.conf file under

C:\Program Files\PostgreSQL\9.3\data

Run the notepad++ as administrator (or the editor you usually use to edit the files)
Open the file and at the very bottom, look for

# TYPE  DATABASE        USER            ADDRESS                 METHOD

For all the entries for TYPE host, change the METHOD from md5 to trust. Save the file. (Make sure there are no interactions with the database at this moment.)
Press Windows+R to get the run dialog, type services.msc and press enter.
Once you see the services(Local) window, choose a service named
"postgresql-x64-9.3 - PostgreSQL Server 9.3" and on the left hand side, restart the service.
Now go back to the pgAdminIII and run the server using default password.
Once you could connect to the server, flip back the settings on the pb_hba.cfg file and save it. Remember before quitting pgAdminIII, do not forget to change the password.

Choose File->Change password to change the password (old password would be a default of postgres).


Same query, Same linked server. Different result

Thank you for your response. Before post this question I already double checked the settings for both linked server and TNS name file. There is nothing different between them.

Fortunately, I resolved it by just re-create the linked server at Server B. It might need to refresh some cache.

Thanks again and have a great one:)


Group fields obtained after join as attributes of the main entity object

In PostgreSQL, it is possible to create an array containing tuples:

SELECT name,
       (SELECT array_agg((image, type))
        FROM image
        WHERE item_id = item.id
       ) AS images
FROM item;

Alternatively, use an array containing another array:

SELECT name,
       (SELECT array_agg(ARRAY[image, type])
        ...

(Whether your language's database driver supports any of these types in the result is another question …)


Alter column fails due to "hidden" statistic

After reaching out to Azure support, I have managed to solve the issue.
Part of the support engineer's answer included the following:

I've reviewed internal telemetry and can confirm that the statistic listed in the error does not exist. This is likely a compute-node-level statistic that wasn't cleaned up properly.

I was able to confirm and fix this issue by doing the following steps:

Run a CTAS to create a copy of the table
Rename the previous table to something different (eg: ) and the new one to the original table name
DROP the old table

After that, the ALTER TABLE statement worked as expected.


The problem is that your query object uses the same key ($ne) twice, so the second value is overwriting the first. Most languages do not support duplicate keys in a standard object / hash / dictionary representation.

The outcome is more evident if you evaluate your query object in the mongo shell:

> query = {'referral.money':{'$ne':0, '$ne':null}}
{
  "referral.money": {
    "$ne": null
  }
}

However, you could use a different operator with equivalent outcome in an implicit $and.

For your example query, $exists: true is equivalent to $ne: null so the following would work:

 db.user.find({'referral.money':{'$ne':0, $exists: true}})

Alternatively you could rewrite the query to use an explicit $and:

 db.user.find({$and: [{'referral.money': {'$ne':0}}, {'referral.money':{'$ne':null}} ]})

Note: if you are storing currency values as floating points you should be wary of rounding with floating point numbers. The MongoDB manual has examples of how to Model Monetary Data.


The fact that 4 out of the 5 queries are in the BIND state make me think you have a problem with probing the endpoints of an index.  This can often be problem if the planner is deciding if it wants to use a merge join.  Note that even if it decides against the merge join, it has still called get_actual_variable_range and if that is slow then it still suffers the slowness. However, I don't think that this mechanism even exists in 8.3.  Is that really what you are running?  If so, you might be better off spending time on getting an upgrade done, rather than trying to tune obsolete software.

One thing to try is to make sure you don't have long running transactions open, and to then vacuum the database.


Ideal frequency to gather table statistics in Oracle for database of 1GB in size

Here are some issues to consider:

Are you using an appropriate level of estimation in the statistics gathering? People often manually set the estimation to a value without considering whether they can get a similar level of accuracy with a lower amount. A good test is to gather statistics at different estimation percents, starting very low and increasing: eg. 0.1%, 0.5%, 1%, 3%, 6%, 10%. Use the stattab argument to direct the stats results to different tables, and compare them.
Do you need histograms? If you are using an OLTP application then quite possibly not.
How frequently does the data change? Table monitoring can be used to help judge when statistics should be gathered -- if a table changes very rapidly and frequently (eg a temporary table used in data loading) then deleting the stats and locking them can be helpful, as dynamic sampling will do the job for you and you won't waste time in a scheduled job analysing data that you have no use for.

Or just use the automatic option, monitoring it to see if there are situations worth intervening in manually.

Given that these are DAC metadata tables, I'd recommend that you set monitoring on for them and use the automatic statistics gathering job to gather statistics when they're stale: http://docs.oracle.com/cd/E11882_01/server.112/e16638/stats.htm#i41282


Problems with Listener in AlwaysOn AG in AWS

When I connect using the listener name, I randomly connect to the secondary node (which I would not expected to happen).

It really shouldn't happen, so it tells me something is not compliant in the environment. Given this is networking related, it could be to do with the DNS/cache settings or the way AWS does their internal networking and updates.

  Is this a known problem with SQL Server 2016 SP2 CU6?

No and I highly doubt it would even fall under SQL Server's purview. You'll want to take a network trace, at a minimum, from the client.

  AG connection resolution functions at the database level, not the server level.

Not quite. When using read only routing a database name is required, but to just connect to an availability group it's not needed. Connection "resolution" is not at the database level, in fact the listener is backed by your typical Network Name + IP resources in Windows Server Failover Clustering, so technically most of the listener functionality isn't even within SQL Server (since the DNS/IP point to the server).

  [...] imagine if you had two AG's set up with the same two nodes - how would the driver know which server to connect to?

The drivers asks DNS what addresses it has for the name, then depending on the driver and version of the driver it will attempt to connect to one or more of those addresses.


Check if the value from one table exists in another tables in SQL Server

I think you could get it with a single statement.

CREATE TRIGGER t1_insert ON T1
AFTER INSERT
AS
BEGIN

    IF EXISTS(SELECT
                  1
              FROM
                  inserted
              JOIN
                  (
                   SELECT ID FROM T2
                   UNION ALL
                   SELECT ID FROM T3
                   UNION ALL
                   SELECT ID FROM T4
                  ) others
                  ON others.ID = inserted.ID)
    BEGIN
        PRINT 'YES';
    END
    ELSE
    BEGIN
        PRINT 'NO';
    END

END

db<>fiddle here


ODBC Driver tracing not capturing any logparser events

Try specifying the -driver option explicitly, providing the name of the native client.


GROUP_CONCAT accepts ORDER BY ,so your query becomes

select id,group_concat(action ORDER BY action SEPARATOR ' ') from table1;


postgres finding child tables for particular table

The schema name of a table is not stored in the column table_name, it's stored in the column table_schema, so you need to change the condition:

and ccu.table_name in ('plugin')

to

and (ccu.table_schema, ccu.table_name) in (('a', 'plugin'))


PostgreSQL data type text vs varchar without length

There is no difference between the two data types.  They use the exact same storage and the same operators.


How do I bypass the typing of `GO` in `sqlcmd`?

Reference: https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility

With -q you can execute multiple query, as you do not get your prompt back until you hit ctrl+c.

  -q" cmdline query " Executes a query when sqlcmd starts, but does not exit sqlcmd when the query has finished running.
  Multiple-semicolon-delimited queries can be executed. Use quotation
  marks around the query, as shown in the following example.

This is in my workstation.

 of -q.

  -Q" cmdline query " Executes a query when sqlcmd starts and then immediately exits sqlcmd. Multiple-semicolon-delimited queries can be
  executed. Use quotation marks around the query, as shown in the
  following example.


Translating the Relational Algebra output

P2(salary) = πsalary(ρT1(s)(P1) thetaJoin(s>salary) P1)

From the Wikipedia entry for Relational Algebra:

The result of the θ-join is defined only if the headers of S and R are disjoint, that is, do not contain a common attribute.
The simulation of this operation in the fundamental operations is therefore as follows:
R ⋈θ S = σθ(R × S)

To ensure the headers are disjoint, salary must be renamed (as 's' here). To ensure the relations are uniquely named for this self-join, P1 must also be renamed (as 'T1' here).
In rough SQL terms:
DECLARE @P1 table (salary integer NOT NULL);
INSERT @P1 (salary) VALUES (1), (2), (3);

WITH T1 (s) AS (SELECT DISTINCT salary FROM @P1)
SELECT DISTINCT
    P1.salary
FROM @P1 AS P1
JOIN T1
    ON T1.s > P1.salary;

-- or, closer to the fundamental operations:

WITH T1 (s) AS (SELECT DISTINCT salary FROM @P1)
SELECT DISTINCT
    P1.salary
FROM @P1 AS P1
CROSS JOIN T1
WHERE T1.s > P1.salary;

dbfiddle

| salary |
| ------ |
|      1 |
|      2 |

P2 therefore contains all the salary values that are smaller than some other salary value.
P1 minus P2 therefore contains the top salary at PWC.
See also Finding a Maximum Value with Relational Algebra


Is adding index on bit column significantly slow down inserts?

An index on a bit for 1 million records is useless. The optimizer will never ever use it, you'll just pay for maintaining it. A much better alternative is to add this bit as the leftmost key on the clustered index.

But I will make a blind shot in the dark and guess that what you have is a queue pattern: records are dropped in the table with the bit set to 'TRUE' (ie. 'needsprocessing = true') and then a background process looks for these records, does some processing, and updates the bit to FALSE. This is a omnipresent pattern, also affectionately know as the 'performance disaster recipe pattern'. I would recommend dropping the records in to the table and dropping a notification (could be as simple as the newly inserted record ID), at the same time, into a queue. See Using Tables as Queues.


I came across the postgres docs regarding locking , which states that

  Although tuples are a lockable type of object, information about row-level locks is stored on disk, not in memory, and therefore row-level locks normally do not appear in this view. If a transaction is waiting for a row-level lock, it will usually appear in the view as waiting for the permanent transaction ID of the current holder of that row lock.

I got my answer.


weird postgresql query runnning on aws postgres db

It looks like the kind of query that would be run by a DB management tool, such as PgAdmin or DBeaver, so that it can populate its navigation pane with a list of database objects or offer a context menu when a specific object has been drilled down into.  It does not appear to be malicious, although it is possible someone could be using such a tool maliciously.


What is difference between starting Instance with -T3608 and with -f?

Say that you allocated something way way too big for the memory in the machine. Locks, for instance. SQL Server tries to create that much memory, but it isn't available, so SQL Server doesn't start. This is the scenario there -f comes in. Nowadays, there are some safety built into sp_configure to avoid ridiculous values (like way too low max server memory), so it might not be easy to actually get to that point in the first place. Another thing that -f does is to create tempdb where master.mdf is, instead of where you said it should be created (imagine a crashed tempdb disk - and SQL server can't live without tempdb).
Above is very different from not performing startup recovery of the databases at startup, which is what 3608 means.


First, get rid of the subquery.  It is not needed and it can interfere with optimization.  Second, you don't need a left join, because the having clause is requiring matches.

SELECT p .*,
       IF(sum(c.course_id = 2) > 0, 'Yes', 'No') as cTrained,
       IF(sum(c.course_id = 4) > 0, 'Yes', 'No') as aTrained
FROM person p JOIN
     learners_to_classes ltc
     ON p.person_id = ltc.person_id JOIN
     classes c
     ON ltc.class_id = c.class_id
GROUP BY p.person_id
HAVING sum(c.course_id = 4) > 0
ORDER BY p.firstname asc

For this query, you want the obvious indexes:  learners_to_classes(person_id, class_id) and classes(class_id).  The first seems to be missing.


Here is how I would do it. First, create a couple of spare schemas:

CREATE SCHEMA HoldingTank AUTHORIZATION dbo;
CREATE SCHEMA Swapper AUTHORIZATION dbo;

Now, when it's time to refresh the view and change the filter predicate, create it in the HoldingTank schema:

CREATE VIEW HoldingTank.MyIndexedView
WITH SCHEMABINDING
AS
  SELECT ...
GO

CREATE UNIQUE CLUSTERED INDEX [CIX_MyIndexedView]
ON HoldingTank.MyIndexedView(SomeColumn);

Now, start a transaction, and swap the objects.

BEGIN TRANSACTION;
  ALTER SCHEMA Swapper TRANSFER dbo.MyIndexedView;
  ALTER SCHEMA dbo TRANSFER HoldingTank.MyIndexedView;
COMMIT TRANSACTION;

Then at a later time you can simply drop the old copy of the view from Swapper:

DROP VIEW Swapper.MyIndexedView;

I go into more detail about this solution in these blog posts:

T-SQL Tuesday #33 : Trick Shots : Schema Switch-A-Roo
Schema Switch-A-Roo : Part 2


How is SQL Server host identity ensured?

I'm not familiar with Azure but I can speak to SQL Server in general. SQL has a property called Force Encryption (which is set through SQL's Configuration Manager), which as it sounds will force clients to connect over encrypted sessions. This is done with certificates just like SSL. This guarantees the authenticity of the server as well as providing for encrypted communications.

It's a bit nuanced - you can use a certificate issued by a CA that the clients trust or SQL can use a self signed certificate (less secure). There's also client side settings you can set in the connection string that will influence the connection - depending on ODBC or OLEDB the pertinent properties are something like "Encrypt" and "TrustServerCertificate".


Issues with a Table having 120 fields in mysql

You need to stop and think about the design of the table. If you have that many columns, then chances are you may need to do some normalization of the table.

Although denormalized data can help you bypass the need to do JOIN queries to retrieve many columns of data, try to imagine what is happening to MySQL internally during data retrieval.

If a 120 column table undergoes SELECT * FROM ... WHERE, you would potentially cause the following:

generate large temp tables
fetch columns that are unnecessary
handle many variable length fields

This would easily cause communication packets (sized by max_allowed_packet) to be pushed to the brink on every query.

In my earlier days as a developer, I worked at a company back in 1995 where DB2 was the main RDBMS. The company had a single table that had 270 columns, dozens of indexes, and had performance issues retrieving data. They contacted IBM and had consultants look over the architecture of their system, including this one monolithic table. The company was told "If you do not normalize this table in the next 2 years, DB2 will fail on queries doing Stage2 Processing (any queries requiring sorting on non-indexed columns)." This was told to a multi-trillion dollar company, to normalize a 270 column table.

Please see my other posts around this subject

Jul 30, 2011 : More than 20 columns in database table MySQL
Jul 20, 2011 : Too many columns in MySQL


Update from PHP 5 to PHP 7.0 - PHPmyAdmin will show two server logins: localhost / localhost:3306

There are two ways to connect to the mysql on the localhost. One is the connection via network @localhost:3306 and another is the connection through the filesocket like /tmp/mysql.sock. Filesocket connection is preferred due to the lower overhead and mysql library choose it if available when connection to the localhost requested. I have no clue why they are separated now.

If you want to connect to the mysql only locally then networking can be disabled completely in the my.cnf so all connections are forced to be filesocket-based. That is also a bit more secure as far as no one can connect to the mysql over the network.


Can the default minimum date range for 'datetime' be changed in SQL Server 2008 R2?

A datetime2 goes back further - http://msdn.microsoft.com/en-us/library/bb677335.aspx - to 0001. If you want to go back further still, then a custom data type may be an option, although given the potential confusion and lack of precision even before 1753, it is doubtful how precise you could expect to be.


Why are these two HADR DMVs reporting different states?

Sadly, sys.dm_hadr_availability_replica states is not a reliable indicator of replica health. Here's the Connect item on one of the bugs we've run into where that DMV stops refreshing - note in the comments that log_send_queue_size in the DMV sys.dm_hadr_database_replica_states shows 0 even when there's log data to be sent.

Note that the Connect item is marked as Won't Fix. Sad trombone.


How do you get the graph plugin for Squirrel SQL?

As per Installation documentation the correct version of each available plugin is always provided in the SQuirreL SQL Client install jar.

Graph Plugin by Gerd Wagner
Installer Category: Standard
This plugin allows you to create and print charts of database tables and their foreign key relations. If you use it together with the SQL Scripts plugin you are able to script tables from within the charts.

You can download it from http://mvnrepository.com/artifact/net.sf.squirrel-sql.plugins/graph/3.2.1


Design for active/passive Master-Master mysql Setup

I will start with only 2 server with an active/passive master-master setup and a loadbalancer in front. I don't see any benifits for the cluster and I don't want a 3rd node or the active master waiting for any other nodes.


What is the recommended method to move databases?

Do a back-up/ restore - takes longer but appears to be a favoured
  method.

This is what we prefer. -- Backup and Restore. Safer Method.

Option to detach the database using sp_detach_db and then attach it using CREATE DATABASE ... FOR ATTACH; or detach and attach using the GUI will actually detach the database from the source server.

Many things can go wrong here, which is why we prefer backup and restore. The backup process is online and since the source database was never made un-available in the first place, you don't risk destroying anything in case of mistakes, data corruption, or whatever other things that can happen.

Also for data move, depending upon how frequent you're data needs to be moved from Dev to pre-prod to prod, RedGate's SQL Compare tool can be used to keep the schema of 2 databases in sync.(Includes cost but depends upon you're needs and wallet)

http://www.red-gate.com/products/sql-development/sql-compare/

Also, something similar to Redgate as free open source tool you can find something :https://code.google.com/p/sql-dbdiff/


As you've described the situation, no — if you have the original file or even access to the original database, you could compare what he sent you with what you're able to get yourself. However, with only the file he's provided, there's no way to determine whether it's been edited. The .sql file is simply a plain text file and there's no audit trail that can ensure — after the fact — that he hasn't modified anything.


Design a relational database (Access) that identifies breaches in records based on user defined limits

Initial observations are that Index Type and Index Composition are attributes of Index, not of Acceptable_indices. There will be relationships between a security and an index; whether this is important to your use case or not can't be determined from the current information. Concentration should relate to another entity type saying what it is a concentration of.

More generally you need to distinguish what is an acceptable set of values from what was actually in the transaction executed. The acceptable set is your risk and exposure policies documented in the form of a normalised data model. You have to think carefully of all the conditions, including the obscure, infrequent and exceptional ones. For each executed transaction the model must capture sufficient information to determine if it meets the risk policies or not.

There must be a link between an individual transaction and the set of rules that apply to it. The easiest way is to set the rules by security type e.g. one set for bonds, one for equities, one for futures, one for options etc.

Another complication is the time-dependent nature of the data. I'm guessing this application will be for analysis after the transactions are complete, rather than pre-transaction authorisation. Likely there will be a number of days between transaction and analysis. So you need to know what rules were in place when the transaction occurred. Everything must have start and end dates (or datetime values, depending if things can change intra-day).

For a physical database design you could implement one table per entity type. This is the usual way in the absence of a strong incentive to do otherwise. Queries may be longer from this approach (if there are many limiting factors the query will have to bring in the table with limit's allowable values and the table including the actual executed value for each factor). Queries are very obvious, however, and easy to implement and maintain. The runtime engine should have not problem executing them.

Alternatively each limit could be considered as a feature of the deal. The tables reduce to bags of feature values. To check if an executed trade breaks a limit one compare the "allowed" bag of values to the "executed" bag of values. This would be a variation on the Entity Attribute Value (EAV) design pattern. Often this pattern is considered dangerous. Queries are obscure as everything is a "bag" or a "feature"; it is never obvious what each particular feature represents. Writing queries to ensure the limit's feature is compared to the corresponding feature on the trade can be painstaking work. Maintenance even more so. Performance tends to be worse because this is adding an extra layer of abstraction on top of how the engine expects to work. It is wonderfully flexible, however. If you get things just right adding another feature can reduce to a simple cut-and-paste.

A third option would be to hold each value as an attribute. This make the tables very wide. The DBMS will be able to cope with this (up to a limit!) but makes things difficult to read. The real problem comes when there can be multiple acceptable values. For your example there may be two acceptable indexes in the limits and two indexes involved in the trade. You created columns index1 and index2 on each of Limit and Trade table. To check validity one must compare all possible combinations i.e.

(
    limit.index1 = trade.index1
and limit.index2 = trade.index2
)
or
(
    limit.index2 = trade.index1
and limit.index1 = trade.index2
)

This becomes ugly real quick.

My suggestion is to implement it one table per entity. Write the queries and test them. They can be written incrementally, checking first for, say, country validity, then country and index, then country, index and counterparty and so on. If this approach surfaces a particular problem, pause and re-evaluate in the light of the new circumstances.


100% I/O utilization when mysqlcheck is running

"Universally acceptable time interval ..." -- "never".  Seriously, change your app tables from MyISAM to InnoDB, and you won't need to ever run mysqlcheck again.

Also, set innodb_buffer_pool_size to about 70% of RAM, not the paultry 128MB.

How much RAM do you have?


Can't connect to MySQL NDB manager using ndb_desc

There is nothing in the description that points to the error.
Everything looks fine. So would be good if you could post
the error messages written in the cluster log when you
start ndb_desc.


What logical structure is used for storing rows in Oracle?

The block is the structure that holds row data. The Logical Storage Structure documentation has a detailed overview of all this.

Note that data for a single row can reside on more than one block (this is called row chaining), so it need not be contiguous on disk. Row migration can also leave just a "forwarding address" in the row's original block that points to the row's new block of residence.

All I/O to and from the SGA is done in block-sized chunks (possibly more than one block at a time, but, for the SGA, never smaller than a block).
The block size of the SYSTEM tablespace determines the database's "natural" block size. You can have other tablespaces with different block sizes but:

you can't mix block sizes inside a same tablespace
you will need to set up separate cache areas for each block size (you can't have for example 8k blocks and 16k blocks in the same block cache)

So in general, it is simpler to use only one block size, if only to avoid the administrative and tuning overhead of managing the size of multiple block buffers.

How you determine the optimal block size for a database is a compromise just like pretty much everything else.
Smaller blocks means your SGA is more granular, but each block has relatively more overhead. And wide rows in small blocks will be more likely to chain or migrate which can be a source of performance problems.
Larger blocks means less chances of chaining (assuming PCTFREE and PCTUSED are reasonably set). But it also means large single-block I/Os and less granularity in your SGA. Since a change in any row of one block requires the whole block to be written out to disk, larger block sizes could lead to more write I/O volume than smaller ones.
Another factor to take into account is that traditional (not bigfile) datafiles are limited to 4M blocks. That's about 32G for 8k blocks, but only 8G for 2k blocks. Since the number of files per (non-bigfile) tablespace is limited (to 1022), this could be important. (There is also a maximum of 65533 files per database, possibly less. See physical database limits.)

Transactional systems tend to use relatively smaller block sizes than datawarehousing systems, but that really isn't anything like a hard and fast rule. (e.g. current Oracle best practice guidelines on Exadata is 8k blocks regardless of scenarios.).


Select Column value based on two values in another column

If I understand, you want to get all trip_id values that appear in the table, against one stop_id on one or more rows, and against another stop_id on other rows. This should do it:

select trip_id
from stop_times
where stop_id = 1538
or stop_id = 1540
group by trip_id
having count(distinct stop_id) > 1;

Example: http://sqlfiddle.com/#!9/e78aa/1

Notice that trip_id 0051ML66220600132483 matches on one stop but not the other, so is not returned.


will replication from 5.5.20 to 5.0.XX server work?

It is impossible. The reason?

The binary logs are incompatible.

To be more specific, the binary logs are not backwards compatible.

Here is why:

An empty binary in ...

MySQL 5.0 and back is 98
MySQL 5.1 is 106
MySQL 5.5 is 107

I have made reference to this subtle difference in ServerFault

https://serverfault.com/questions/231369/mysql-master-binlog-corruption/231386#231386
https://serverfault.com/questions/273407/yum-update-mysql-from-5-1-to-5-5/278829#278829

You can setup replication from an older MySQL version to a newer MySQL version, not the other way around.

If you ever need data from MySQL 5.5 ported back to an older version, your only recourse to mysqldump all databases EXCEPT the mysql schema.

DBLIST=`mysql -u... -p... -AN -e"select group_concat(schema_name separator ' ') from information_schema.schemata where schema_name not in ('information_schema','mysql')"`
MYSQLDUMP_OPTIONS="--add-drop-database"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --single-transaction"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --routines --triggers"
mysqldump -u... -p... ${MYSQLDUMP_OPTIONS} --databases ${DBLIST} > MySQLData.sql

You can extract the grants as SQL using pt-show-grants. Here is my personal emulation if it:

mysql -u... -p... --AN -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -u... -p... --skip-column-names -AN | sed 's/$/;/g' > MySQLUserGrants.sql
echo "flush privileges;" >> MySQLUserGrants.sql

The reason you have to dump the grants as SQL is the fact that the columns in the mysql.user table changes upon upgrade.

When you do desc mysql.user; or select count(1) from information_schema.columns where table_schema='mysql' and table_name='user'; you get the following:

37 columns in MySQL 5.0
39 columns in MySQL 5.1
42 columns in MySQL 5.5

All the mysql upgrade does is morph mysql.user to accommodate new permissions. Performing the dump of the grants yields SQL that is independent of the mysql.user layout.

Once you have made these two dump files (MySQLData.sql and MySQLUserGrants.sql), simply load them into a new installation of an older MySQL version.


How can you tell if a table in DB2 has a Primary Key?

Disclaimer: I do not know DB2.

I simply googled these with "db2 table definition".

Source:

SELECT *
FROM SYSIBM.SYSTABLES TAB,SYSIBM.SYSCOLUMNS COL
WHERE TAB.CREATOR = COL.TBCREATOR
AND TAB.CREATOR = 'xxxx'
AND TAB.NAME = 'xxxxxxxxxxxxx'
AND TAB.NAME = COL.TBNAME
AND TAB.TYPE = 'V' ( OR 'T' )
ORDER BY 1,2;

Source:

SELECT * FROM syscat.tabconst WHERE type = 'P';


Potential schema for product failures and repairs

Model

Here's a possible model. After I drew it I noticed that the difference with yours is that the product part number is  now in the Product table; I think it should be this way as it's a characteristic of the product. Then the Job refers to a Product via its part number, and not viceversa.

Each table uses a surrogate primary key for clarity. This is not really necessary in several cases e.g. in RepairComponent, so feel free to replace it with a composite primary key.

The Product table stores a product. The field Product.pn (part number) should identify your product since it is manufactured; the product is then assigned a DMR when it fails and then, if it is repaired, a S/N.

Product
------------------
pn (PK)
DMR (IDX unique)
SN

Job table:

Job
------------------
id (PK)
Product.pn (FK)
due_date
quantity_to_ship

  A product can fail multiple times and can be repaired multiple times (many to many relationship).

A product may fail multiple times but each repair concerns only one product, so it's a one-to-many relationship. You can hence have simply one table Repair (that stores a repair event) referring to Product.

Failure
------------------
id (PK)
Product.DMR (FK)
description
operation
date
Technician.id (FK)

Repair
------------------
id (PK)
Product.DMR (FK)
disposition
code
date
Technician.id (FK)

RepairComponent
------------------
id (PK)
component_pn
Repair.id (FK)

Technician
------------------
id (PK)
name

Enum vs table

  I'm a bit unsure when to use an enum vs creating another table (e.g. disposition can be scrap, repaired, or use-as-is). Am I on the right track?

If you are dealing with an immutable set of operations (i.e. you're absolutely positive that Repair.disposition will always be one and only one of the options you listed) then it's ok to use an enum. Otherwise, your ER model will be much more flexible if you create another table:

DispositionTypes
------------------
id (PK)
description

as now you can create all kinds of new disposition types e.g. "Repaired", "Use as is", "Scrap", "Sell to China", or "Bury in landfill". In this case, you remove Repair.disposition and replace it with a FK DispositionTypes.id instead.

The advantage of this approach is that you can set up authorization levels for Technicians to perform a specific disposal (it is not mentioned in your question but it shows in your model, if I'm not wrong). This is done via a join table Authorizations:

Authorization
------------------
Technician.id      (PK) (FK)
DispositionType.id (PK) (FK)

Same talk for RepairCode:

RepairCode
------------------
id (PK)
description

Personally I like to design databases that allow for flexibility, but the choice here it's up to you.

Choice of the DB engine

A MySQL database with InnoDB (which is the standard engine in MySQL) will work fine, so it's a good choice.


MSSQL Logon Error in /var/opt/mssql/log/errorlog

The login packet used to open the connection is structurally invalid; the connection has

SQL Server listens on a TCP/IP port and expects clients who connect to use the TDS network protocol.  If a client connects and sends data that doesn't conform to the TDS protocol, you'll get an error like this.  Could be a port scanner (possibly malicious), or some client trying to connect to SQL Server using FTP, Telnet, HTTP or some other TCP/IP-based network protocol that SQL Server doesn't use.


Find out what setting were used for a given stored procedure

This can be determined from sys.sql_modules:

USE tempdb;
GO
SET ANSI_NULLS ON;
GO
CREATE PROCEDURE dbo.foo1 AS SELECT 1;
GO
SET ANSI_NULLS OFF;
GO
CREATE PROCEDURE dbo.foo2 AS SELECT 1;
GO

SELECT name = OBJECT_NAME([object_id]), uses_ansi_nulls
  FROM sys.sql_modules
  WHERE OBJECT_NAME([object_id]) IN (N'foo1', N'foo2');

GO
DROP PROCEDURE dbo.foo1, dbo.foo2;

Results:

name   uses_ansi_nulls
----   ---------------
foo1   1
foo2   0

Some other useful columns:

object_id
definition
uses_ansi_nulls
uses_quoted_identifier
is_schema_bound
uses_database_collation
is_recompiled
null_on_null_input
execute_as_principal_id

Note that some settings (e.g. arithabort, ansi_warnings, ansi_padding) are not stored with the object. Also you will need to recompile the procedures with the correct settings for any that have been identified as "wrong."


Does PostgreSQL support online schema modification (DDL)?

TL;DR: No, except for some basic cases.

Some lock-strength reductions for ALTER TABLE have been added to PostgreSQL 9.5. You can't do anything that requires a full table rewrite without an exclusive lock though, in 9.5 or below.

Some operations, like ALTER TABLE ... DROP COLUMN or ALTER TABLE ... ADD COLUMN ... without a DEFAULT and NOT NULL can be done with a very short exclusive lock even in older versions. A brief moment is needed where queries don't run, but it's almost instantaneous, since no table rewrite is required.

There are some things that could be optimised, like ALTER TABLE ... ADD COLUMN ... DEFAULT ... NOT NULL, by storing the default for old rows in the table metadata and looking it up when reading old rows. This has not been implemented in PostgreSQL.

In theory PostgreSQL could use its MVCC catalogs to support what you describe by background-writing new versions of rows into a table while continuing to use the old versions for queries. I'm not aware of anyone attempting to do that, and it'd require some special handling because PostgreSQL's code currently assumes that table rows only have one possible structure at any given time.


How to Locate Resource System Database in MS SQL Server

The mssqlsystemresource database is hidden intentionally. The mssqlsystemresource is largely an implementation detail that allows the SQL Server installation and upgrade process to deploy new system objects versions without recreating objects in the master database as was done in SQL Server 2000 and earlier.

The physical location the mssqlsystemresource database files is a well-known documented location. In SQL Server 2008 and later versions, this is the instance BINN folder (e.g. C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Binn) together with other SQL Server binaries. In SQL Server 2005, the mssqlsystemresource were located in the same data folder as the master database files.

For learning, you can copy the mssqlsystemresource.mdf and mssqlsystemresource.ldf to a user data folder, change file permissions, and attach the database using a different database name. This should only be done on an isolated test instance and will allow you to easily view the system objects in the mssqlsystemresource database using SSMS.

CREATE DATABASE mssqlsystemresource_copy
    ON(NAME='data', FILENAME='D:\SqlDataFiles\mssqlsystemresource.mdf')
    LOG ON(NAME='log', FILENAME='D:\SqlDataFiles\mssqlsystemresource.ldf')
    FOR ATTACH;
GO


Very slow JOIN + ORDER BY query only in dev environment

I noticed in mysql_error.log hundreds of entries like this:

[Warning] InnoDB: Table mysql/innodb_index_stats has length mismatch in the column name
table_name. Please run mysql_upgrade

I ran this:

/Applications/MAMP/Library/bin/mysql_upgrade --host=localhost -uroot -proot

The problem now seems to be resolved – the initial query above is now taking about 20ms.

Perhaps it was corrupted on installation?


Sybase ASE 15 "reorg rebuild" fixes performance issues - why and how to tell when it's needed?

When a table is first indexed, especially with a clustered index, the records are in order, and there are a minimal number of index pages to find each record.  As update, insert and deletes happen, the number of index pages increases.  Over time this will impact query performance.  It is recommended that dba's run reorgs periodically to collect garbage, compact the database, and compact the index pages.  The downside to reorgs, is that prior to 15.7, they block access to the table, so typically must be run during off hours.

  1 - What is the underlying cause?

Insert/Update/Delete activity in the database creates additional database pages, and index pages.  The more pages the query has to read to get to the data it's looking for, the longer the query will take.

  2 - Should reogs be performed on a regular basis?

Probably.  Static, or rarely changing tables will need it infrequently, but as the activity on a table increases, the need for reorgs increases as well.

  How should I determine when to run?

This depends on the environement you are in.  The need for reorgs is directly related to the Insert/Update/Delete activity of the table.  You can use the optdiag command to check the health of your tables, take a close look at some of the following values:
Index page cluster ratio;
Data page cluster ratio;
Empty data page count;
Empty leaf pages;
Deleted row count;
Forwarded row count;
Index empty leaf page count.

Check the Sybase documentation on optidag and the Sybase documentation on reorg to get a good idea of how to determine when to run reorgs in your environment.


Adding a ROW_NUMBER() with no column to ORDER BY?

The canonical way to do this is the following: ROW_NUMBER() OVER(ORDER BY (SELECT NULL)). If you're golfing, you might try something like this:

SELECT value, n = ROW_NUMBER() OVER(ORDER BY (SELECT 1))
FROM STRING_SPLIT('one,two,three,four,five',',')

It works for the simple case you posted in the question:

I should say that there is not a documented guarantee that the ROW_NUMBER() value will be in the precise order that you expect. But it's code golf, so this seems good enough.


Transfering records with foreign key referring to new value

There could be few approaches you can take to achieve this... based onhow big is the data set.

Use of cursors
Using cursor you can insert each row is new warning table capture the new_warningid created for that row and use it to insert respective Acknowledges referring to old_warning id and replacing with new_warningid.
use of temporary column in New_warning table which will hold old_warningid for lookup.
Add a column "old_warningid" to new_warnings table and use that column as lookup while inserting rows in new_acknowldges tables to get corresponding "new_warningid".once the migration is complete you can drop the columns from new_warnings table.


How do I use an Ispell dictionary with Postgres text search?

This example uses the Canadian English dictionary, but you can try it with others as well.

These are the steps needed for Windows:

Open http://src.chromium.org/svn/trunk/deps/third_party/hunspell_dictionaries/en_CA.dic
Select all of the text, copy it, and paste it to Text Mechanic: http://textmechanic.co/Sort-Text-Lines.html. Add a line break at the end.
Open http://src.chromium.org/svn/trunk/deps/third_party/hunspell_dictionaries/en_CA.dic_delta
Select all of the text, copy it, and paste it below the previously pasted text in Text Mechanic.
Scroll to the top, and select and cut the first line (should be a five digit number), and get rid of the line break.
Click the Alphabetical button, and wait for the text to sort.
Select all of the text and copy it to the clipboard
Open Windows Notepad as an administrator
Paste the text from Step 7 into Notepad
Save the file as en_ca.dict (with UTF-8 encoding) to your Postgres text search folder. Mine is C:\Program Files\PostgreSQL\9.3\share\tsearch_data .
Open http://src.chromium.org/svn/trunk/deps/third_party/hunspell_dictionaries/en_CA.aff , select all, copy, and paste to Notepad. Save the file as en_ca.affix to your Postgres text search folder.

In PgAdmin, run the following SQL:

create text search dictionary ispell_en_ca (
  template  =   ispell,
  dictfile  =   en_ca,
  afffile   =   en_ca,
  stopwords =   english
);

--make sure it works:
select * from ts_lexize('ispell_en_ca', 'colours');

/*
result:
ts_lexize
text[]
{coloured,colour}
*/

You will need to create a new text search configuration to use the dictionary.


why binlog doesn't log the DML statement?

Do you use statement based or row based logging? If it is statement based then you most likely have binlog-do-db set in your replication filters e.g. binlog-do-db=test so that only queries where the default database is test are written to your binary log.

e.g.

USE test;
CREATE table t . . .

USE test;
CREATE table otherdb.table . . .

both appear in your binary logs, but

USE otherdb;
CREATE table t2. . .

USE otherdb;
CREATE table test.t . . .

Don't appear.

In the first instance your default database is test, in the second it is otherdb.

see: https://dev.mysql.com/doc/refman/5.5/en/replication-rules-db-options.html


Unless your development or testing team already has a set of regression test cases, you are out of luck.  The possible things to test are astronomical.  Now, as others have pointed out, there are some things to look out for, like more restrictive privileges, and reviewing the "What's New" for both 11g and 12c.

One big example from the past that burnt a lot of people (including me) was upgrading from 9i to 10g; where developers relied on a "group by" clause in a select would always return the results sorted by the group by columns in 9i, but not always in 10g.  Not a bug; the SQL standard for group by says nothing about ordering of results; so a lot of code broke.  But again, only way to flush out those types of things are good regression test cases.

Now, after all that, I have not seen or heard about any 10g to 12c surprises, but there are a lot of wonderful new features that should be used when moving to  12c.


Need to intentionally create blocking processes for testing

BEGIN TRANSACTION
  SELECT * FROM YourTable WITH (TABLOCKX, HOLDLOCK)
    WAITFOR DELAY '00:05:00' -- 5 minutes
ROLLBACK TRANSACTION

And then in another Query Window:

SELECT * FROM YourTable


As a general rule, and speaking very broadly, each "thing" in a database is represented by a row in a table. Each seller is a row in a table. Each purchaser is a row in a table.

The simple, obvious, and arguably wrong approach is to have one table of sellers and another table of purchasers. The problem is that one real-world human can be both a seller and a purchaser. In other applications, one real-world person might be a doctor, a patient, and a hospital board member, all at the same time.

The problem with those two tables is that an update to, say, the email address for me in the sellers table might not be matched with a corresponding update to my email address in the purchasers table. And, in fact, that's almost certain to happen, because the row that corresponds to me in the sellers table probably won't have the same primary key as my row in the purchasers table.

For a working solution to the general case, see this SO answer. In your case, you're only talking about people. If you want to model people, sellers, and bidders, you're probably looking for something along these lines. This kind of structure allows one person to be both a seller and a bidder. (I think bidder is a better word than purchaser, if we're going along eBay lines.)

create table people (
  person_id integer primary key,
  full_name varchar(35) not null
);

create table sellers (
  person_id integer primary key references people (person_id),
  other_attributes_of_this_person_as_a_seller char(1) not null
);

create table bidders (
  person_id integer primary key references people (person_id),
  other_attributes_of_this_person_as_a_bidder char(1) not null
);


How to change SQL sever configuration manager settings using TSQL?

Most settings that can't be done via sp_configure are registry based

So, you can use xp_instance_regread etc to change them. You'll have to find a list of registry keys yourself sorry but most are under HKLM\Software\Microsoft\Microsoft SQL Server

One examples , protocols is under

...
...(instancename)\MSSQLServer\SuperSocketNetLib\Tcp
...(instancename)\MSSQLServer\SuperSocketNetLib\Sm
...

and each of these has an Enabled value

One important note: you can stop SQL Server from xp_cmdshell or using SHUTDOWN but not start it of course...


Available space differs between sys.database_files and database properties dialog in SQL Server Management Studio

Tracing the query used by ssms

One way to do this is by taking the profiler's 'tuning' template and then tracing your login

This query pops up

SELECT
(SELECT SUM(CAST(df.size as float)) FROM sys.database_files AS df WHERE df.type in ( 0, 2, 4 ) ) AS [DbSize],
(SUM(a.total_pages) + (SELECT ISNULL(SUM(CAST(df.size as bigint)), 0) FROM sys.database_files AS df WHERE df.type = 2 )) AS [SpaceUsed]
FROM
sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id left join sys.internal_tables it on p.object_id = it.object_id

The query gets a DbSize and  SpaceUsed column from some DMV's.

The DbSize column

A straightforward query, get the size where the file type

= 0 = Rows
= 2 = FILESTREAM
= 4 = Full-text

This query gets all file sizes except the log size.

I'm assuming this is added later as it is added to the total size in the properties pane.

SELECT SUM(CAST(df.size as float)) FROM sys.database_files AS df WHERE df.type in ( 0, 2, 4 ) ) AS [DbSize]

The SpaceUsed column

A bit bigger, first get the sum of total pages from the  sys.allocation_units DMV

...SUM(a.total_pages) + ...
FROM
sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
...

And then add any filestream data to this number (if none then add 0 ):

...(SUM(a.total_pages) + (SELECT ISNULL(SUM(CAST(df.size as bigint)), 0) FROM sys.database_files AS df WHERE df.type = 2 )
FROM
sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id ...

The total query

Running the total query gives me:

DbSize  SpaceUsed
2557952 886791

Both the DBSize and SpaceUsed column is the size in 8KB pages.

  What can I do to get exactly the same number the properties dialog
  shows for Space Available?

If I transform the query to be divided by /128.0 and subtract SpaceUsed from DBSize like your query

SELECT
(SELECT SUM(CAST(df.size as float)) FROM sys.database_files AS df WHERE df.type in ( 0, 2, 4 ) )/ 128.0 -
(SUM(a.total_pages) + (SELECT ISNULL(SUM(CAST(df.size as bigint)), 0) FROM sys.database_files AS df WHERE df.type = 2 ))/ 128.0 AS [FreeSpace]
FROM
sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id left join sys.internal_tables it on p.object_id = it.object_id

I can replicate the properties view of the space available:

FreeSpace
13055,945313

Still need to apply rounding with 2 digits after the comma

where your query gives me a lower amount on my database

FreeSpaceMB
13055.687500

Trying to find a why

Assumptions

We established that the DBSize column is the same for both queries, the difference should be between the total_pages column from the sys.allocation_units dmv and the FILEPROPERTY function

FILEPROPERTY(name, 'SpaceUsed') AS INT).

on the FILEPROPERTY documentation:

  Number of pages allocated in the file

Source

Where total_pages =

  Total number of pages allocated or reserved by this allocation unit.

Source

Guess

We are getting the sum of all these allocation units. My guess would be that the difference is based on the file having more overhead pages not directly in the allocation units dmv, such as File header, PFS, GAM, SGAM, DCM, BCM.

More on these here

Using sp_spaceused and @updateusage to update the space usage info (DBCC UPDATEUSAGE)

exec sp_spaceused

The sp_spaceused procedure gives the same result as the SSMS Properties window and the sys.allocation_units query.

unallocated space
13055.95 MB

But when running sp_spaceused with @updateusage = 'true'

  Indicates DBCC UPDATEUSAGE should be run to update space usage
  information. When objname is not specified, the statement is run on
  the whole database;
  Source

EXEC sys.sp_spaceused  @updateusage = 'true';

The values are updated:

unallocated space
13056.07 MB

The changed SSMS query that uses sys.allocation_units also shows this updated value:

FreeSpace
13056,070313

While the FILEPROPERTY query did not change!

SELECT         size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB
    FROM sys.database_files;

FreeSpaceMB
13055.687500


How to script XML Schema collection in SQL Server 2012?

If I understand the Question correctly, you need to use the XML_SCHEMA_NAMESPACE built-in function to extract the Schema Collection, and you can find the list of those in the sys.xml_schema_collections system catalog view. You can use something along the lines of:

SELECT CONVERT(NVARCHAR(MAX), XML_SCHEMA_NAMESPACE(sch.[name], xsc.[name])) AS [XSD]
FROM   sys.xml_schema_collections xsc
INNER JOIN sys.schemas sch
        ON sch.[schema_id] = xsc.[schema_id]
WHERE  xsc.[xml_collection_id] <> 1;

I put the WHERE  xsc.[xml_collection_id] <> 1 filter in there to avoid getting the following error:

  Msg 6314, Level 16, State 1, Line 1
  Collection specified does not exist in metadata : 'sys'


Oracle 12c create manual script for automatic startup on RHEL7

You need to create a system service pointing to start and shutdown shell scripts.

See Automating Database Startup and Shutdown on Linux on Oracle Base.

Another option is to download an Oracle Developer VM and explore how auto start was setup along with a system service (one of the options from the link) – Kumar


How to drop database that I can't get out of single user mode?

Stopping the server and disabling the sa account seems like overkill to drop a database. Next time this happens, the following should be sufficient:

USE [master];
GO

ALTER DATABASE [dbname] SET OFFLINE
    WITH ROLLBACK IMMEDIATE;
GO

DROP DATABASE [dbname];

The connection that could be sneaking in could be from anything - SQL Server Agent, a linked server, IntelliSense in your query window, etc. Setting the database offline is much more direct approach than even moving into single user mode, never mind unnecessarily tracking down some rogue session in deprecated compatibility views...