A minimum of a 3 Node Galera Cluster is Recommended, but is it better to have 5 nodes? Right now I have a 400GB database, with a 5 node Galera Cluster. They are all RAID 10 SSDs.

I've read the following:

  If the node goes missing due to a network problem or otherwise leaves
  without telling the rest of the cluster, then problems can arise. For
  the cluster to function, it needs a quorum, a majority of nodes active
  in the cluster. The two other nodes will continue to function normally
  since their partition has more than half of the known nodes but the
  node that left will stop accepting queries when it realizes that it is
  no longer in contact with the active partition. In this case, assuming
  an application can access the two active nodes, the failure can go
  mostly unnoticed.

I am trying to reduce my cost and make things more optimal. My cluster is handling a few thousand queries per minute. Is it safe to have a 3 node cluster?

What happens if 1 or 2 of the nodes go down, would there be a total outage of the database?

Is it recommended to have a 5 node cluster over a 3 node cluster?

Should I put them on RAID 1, RAID 0, or RAID 10? What is suffice?

If the criteria for "safe" is "no single point of failure", then 3 suffices.

If "safe" means surviving "any two points of failure", then there is no solution.  5 only handles the case where two servers go down, not arbitrary combinations of things.

RAID 10 (or 5, but not 1 or 0) provides recovery from a single disk failure on a single machine.  Since a 3+ node cluster can survive the failure of one entire disk subsystem, RAID is not required; it just gives you an extra level of comfort.

I do like RAID 10 with Battery Backed Write Cache -- this has the bonus of making writes virtually instantaneous.

Here is a situation that can happen with a 3-node cluster (N1, N2, N3).  Let's say N1 dies.  After putting a new (or repaired) N1 into the cluster, it will rebuild the data.  This uses N2 to as the 'donor' to rebuild N1.  That leaves only N3 at full functionality.  (N2 will be somewhat busy sending data to N1.)  The Cluster is still alive, though "slowed".


How to lock a Row while I'm using it I want to be able to Lock a row, select it, increment its value, and then release the lock. (without lockin the other rows, so that other connections can work with the rest of the table)

I've found this

BEGIN TRAN SELECT * FROM tablename WITH (HOLDLOCK, ROWLOCK)
WHERE ID = 1

My problem is I cant do

UPDATE tablename
SET columnName = -1
WHERE ID = 2

until I commit my previous transaction, why is rowlock locking the whole table?

edit:

Does this code guarantees that the data of the selected row is not update during this update command?

UPDATE [tablename] WITH (ROWLOCK)
SET columnName = columnName + 5
WHERE ID = 1

Based on your edit, all writes lock exclusively.

No other process can read the row(s) being written unless

you've been silly and are using NOLOCK
you have snapshot isolation where the last committed row is read

There is usually no need to add lock hints in day to day code.

As to why you (apparently) have a table lock, this can be caused by no index on ID. The table is being locked because all rows have to be looked at.


Autoincrement Primary Key For Constants We are having an issue that is probably due to some poor design choice or lack of understanding of how to use primary keys.

We have a simple constants table

CREATE TABLE IF NOT EXISTS `constants` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `category` varchar(20) NOT NULL,
  `subcategory` varchar(30) NOT NULL,
  PRIMARY KEY (`id`)
)

We reference the unique rows using the id column.

E.g. if(id == 30) do something

Now, our issue arises when we sync between dev and production. On dev, we are constantly trying our new things/testing features. We are adding and deleting constants. So our ID column doesn't map into our production database. So, if we tried the same code again on production:

e.g. if(id == 30) do something

It points to a different record.

My initial thought is that we just make the category/subcategory columns the primary key but I have read that you should almost always use a GUID for a primary key.

Primary keys is a key concept of the relational model. From a pure practical way, in general you have 2 options:

Choose as a primary key one of the candidate keys: that is, a column or combination of columns that unequivocally identifies the row. For example, in Twitter, we could think that the user name or the user email in a users tables is a candidate key, so both could be used for the primary key
Create an arbitrary identifier that is created on insertion time (MySQL's AUTO_INCREMENT is a paradigmatic example, which we could generate on the same users table)

There are some reasons to choose one over the other (some good and some bad): For example, setting meaningful columns as primary key makes later harder to change those values, as many other tables or logic could depend on it. That could be a reason for or against it. For example, most sites like Twitter actually use an internal id so that you can easily change your username and email. Some ORMs require, or make the logic easier if you all your tables have an auto_increment int or bigint called id. Numeric ids are shorter, which in some specific cases may make the foreign keys or other parts shorter, and maybe more efficient (that is the case for MySQL's InnoDB in many cases due to its internal structure). On the other side, having and arbitrary id as a primary key may not be adequate for your logic, as there is not direct connection between it and the actual row.

Having said that, in my opinion -and that is without knowing your internals- you should give a human-recognizable identifier to your constants and query though that column:

if (name == "kill_all_humans"))
  do something

Whether name should be a primary key or a secondary key (and having a SERIAL in addition) is probably going to be of lesser importance, assuming your table has just a few values (not millions). That way, you can set and read the value independently of it having an arbitrary identifier. That way your code is 100% portable.

As side notes:

If identifiers are important to you, make sure you import them correctly, those may be important in the event of a later update
I do not think int(5) means what you think it means


Unable to update MySQL table from linked server on SQL Server 2014 I'm using SQL Server 2014, with a linked server to a MySQL database on AWS. I need to update rows on that linked server, using data that exists on another linked server. And if that wasn't complicated enough, I need the server and the database names to by parameterized, so I can easily configure my SQL Server Agent job to run against test or prod... so everything is dynamic-SQL.

I tried this:

exec (@remoteSql);

where @remoteSql contains a would-be-valid openquery update statement. This blows up with this error:

  The OLE DB provider "MSDASQL" for linked server "AWS" could not UPDATE table "[MSDASQL]". The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized.

So I tried this:

    declare @remoteSql nvarchar(max);
    declare c cursor for select RemoteSql from @updates;
    open c;
    fetch next from c into @remoteSql;
    while @@fetch_status = 0
    begin
        exec (@remoteSql) at AWS;
        fetch next from c into @remoteSql;
    end;
    deallocate c;

where @updates contains a handful of records and @remoteSql contains a perfectly valid and rather simple update statement (no joins, just 3 fields and a where clause) to run on MySQL. This blows up with this ever so helpful error message:

  Could not execute statement on remote server 'AWS'.

I get the same error if I take the statement, escape the double-quotes and go exec ('update ... where ...') at AWS;, which worked here.

I have the Return matched rows instead of affected rows checkbox checked on my ODBC data source, and quadruple-checked that my linked server has RPC enabled.

The same stored procedure has a rather complex dynamic-SQL update statement involving a join to a local table and a join to another remote table through openquery, and that one works perfectly - however that same approach fails for this table.

Why is it refusing to update that remote table (the login I'm using does have the necessary permissions), and what else can I try?

Configure the MSDASQL provider

I believe the following should fix it. Turn on the following for SQL Servers MSDASQL provider:

AllowInProcess
IndexAsAccessPath
LevelZeroOnly
DynamicParameters
NestedQueries

Other options that seemed to work was creating a primary key:

https://www.sqlservercentral.com/Forums/657910/OPENQUERY-UPDATE-problem-with-MySQL-as-linked-server


how to edit a sql server profile template using a text editor like notepad or ssms? I am running a trace where I need to filter the behaviour or about 50 users, and some 50 database objects.

I would like, for this purpose to create a sql server profile template and later edit it to add the events I want and the filters I want. How can I achieve that?

I have tried to save my template, but when I then try to open it using a text editor like notepad I get the result below:

On the other hand, if from inside sql server profile, I import my template, and use it to run a trace that is fine, but when I want to add my filters I would need to go through a manual process and there are too many filters and each needs to be added individually.

how can I achieve this by editing the template file?

Set up the trace for one user , start it and then on SQL Profiler File Menu

File --> Export --> Script Trace Definition

Though it is now recommended to use extended events instead of traces.


How to troubleshoot poor trigram (gin) index performance? I have a compound LIKE in a query that would have seemed to have benefited from a gin index however on two practically identical servers I see vastly different performance, with the worst case being almost as slow as a Seq Scan:

Bad:

EXPLAIN (ANALYZE, BUFFERS) SELECT nodes.node_path FROM nodes WHERE nodes.node_path LIKE 'company%/path%';
                                                                    QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on nodes  (cost=2114.03..17149.31 rows=3875 width=42) (actual time=11000.379..11036.533 rows=52 loops=1)
   Recheck Cond: ((node_path)::text ~~ 'company%/path%'::text)
   Rows Removed by Index Recheck: 46597
   Heap Blocks: exact=27913
   Buffers: shared hit=73481
   ->  Bitmap Index Scan on nodes_node_path_gin_idx  (cost=0.00..2113.06 rows=3875 width=0) (actual time=10992.576..10992.576 rows=46649 loops=1)
         Index Cond: ((node_path)::text ~~ 'company%/path%'::text)
         Buffers: shared hit=45568
 Planning time: 0.245 ms
 Execution time: 11036.769 ms
(10 rows)

Good:

EXPLAIN (ANALYZE, BUFFERS) SELECT nodes.node_path FROM nodes WHERE nodes.node_path LIKE 'company%/path%';
                                                                   QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on nodes  (cost=2528.05..20571.91 rows=4651 width=43) (actual time=1329.024..1354.603 rows=56 loops=1)
   Recheck Cond: ((node_path)::text ~~ 'company%/path%'::text)
   Rows Removed by Index Recheck: 48704
   Heap Blocks: exact=16106
   Buffers: shared hit=126080
   ->  Bitmap Index Scan on nodes_node_path_gin_idx  (cost=0.00..2526.89 rows=4651 width=0) (actual time=1324.801..1324.802 rows=48760 loops=1)
         Index Cond: ((node_path)::text ~~ 'company%/path%'::text)
         Buffers: shared hit=109974
 Planning time: 0.237 ms
 Execution time: 1354.748 ms
(10 rows)

For index:

CREATE INDEX nodes_node_path_gin_idx ON public.nodes USING gin (node_path gin_trgm_ops)

Where node_path stores slash delimited file paths.

These hosts otherwise exhibit very similar performance, and there's seemingly no disk I/O or other factors obviously different between these executions.

I enabled track_io_timing, I/O Timings is not emitted, and BUFFERS indicates Buffers: shared hit=43783. I dumped this column to a local 9.6.16 instance and it runs in milliseconds, but on the RDS Postgres 9.6.11 instance it's always 10 seconds:

EXPLAIN (ANALYZE, BUFFERS) SELECT nodes.node_path FROM nodes WHERE nodes.node_path LIKE 'company%/path%';
                                                                QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on nodes  (cost=403.14..15246.51 rows=4018 width=42) (actual time=76.402..76.421 rows=52 loops=1)
   Recheck Cond: ((node_path)::text ~~ 'company%/path%'::text)
   Rows Removed by Index Recheck: 48576
   Heap Blocks: exact=506
   Buffers: shared hit=8530
   ->  Bitmap Index Scan on nodes_node_path_gin_idx  (cost=0.00..402.13 rows=4018 width=0) (actual time=71.494..71.494 rows=48628 loops=1)
         Index Cond: ((node_path)::text ~~ 'company%/path%'::text)
         Buffers: shared hit=8024
 Planning time: 0.188 ms
 Execution time: 76.452 ms
(10 rows)

How do I get information on where the cost is for the Bitmap Index Scan itself - I assume it's something specific to the implementation of the ~~ operator for the gin index type, but is there additional instrumentation I can enable to understand the unexplained time?

Substantial improvements were made to pg_trgm in PostgreSQL v9.6, pg_trgm v1.3.

But if you upgraded your server from before 9.6 to 9.6 or later using "pg_upgrade", you will not benefit from these improvements until you do alter extension pg_trgm update TO "1.3".


Would re-importing reduce the DB/backup size? My MariaDB DB size is constantly growing. So is the backup file size.

As I am not a DB admin (just a programmer using this DB) I wonder if I could just export the whole DB, then delete the existing one and re-import it again to decrease its size/backup size?

Idea: I wonder if this would reduce the overall size as all update and change operations would be no longer in the changelog. Some of the increased size can be explained by the number of tuples (growing over time), but I have also tables which have a lot of insert delete operations.

Maybe I am completely naive, but I wonder if this would work?

--- edit ----

As asked below, I create the dump with mysqldump ("my backup") and import it with source mydumfile.sql

Short answer:  It depends.

Long answer:

It depends on what Engine you are using.

It depends on the value of innodb_file_per_table when you created the tables.

It depends on whether you have inserted about as many rows as you have deleted.

It depends on what you mean by "changelog".

It depends on what you do to clear the disk space between the dump and the reload.

But most likely the answer is:  The disk footprint won't shrink any, or at least not much.

YMMV -- Try it.


Get all the queries executed in the last week in SQL Server How can I get all the queries executed on a SQL Server in last week.

I don't expect you to be on 2016 yet as it's been released only very recently (at the time of writing) but if you have a 2016 server and you have Query Store enabled for your database, you can review your queries there.

Have a look at the documentation for Query Store which lists the following example use cases:

  Determine the number of times a query was executed in a given time window, assisting a DBA in troubleshooting performance resource
  problems.
  Identify top n queries (by execution time, memory consumption, etc.) in the past x hours.

If you want to have a log of your queries for future review you could look into Extended Events or server side traces, depending on your server version.

If you are running SQL 2005+ you can log queries using this article explaining server side trace.

If you are running a version more recent than 2008 you can use Extended Events which would be recommended over server side traces.

Look at the query posted here by Paul Randal showing you how to log expensive queries using Extended events. You could modify the WHERE from that query to log all queries.

That being said I don't think it's a good idea to log ALL of your queries. If you have a fairly active production system that would really slow your system down. Have at least a few filters, and log as few data as possible.

If you don't have any logging set up there is no way you can retroactively and reliably get that information.

The closest you can get is querying the plan cache with something like:

SELECT
sql_text.text,
st.last_execution_time,
DB_NAME(qp.dbid) as databasename
FROM sys.dm_exec_query_stats st
CROSS APPLY sys.dm_exec_sql_text(st.sql_handle) AS sql_text
INNER JOIN sys.dm_exec_cached_plans cp
ON cp.plan_handle = st.plan_handle
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) as qp
WHERE st.last_execution_time >= DATEADD(week, -1, getdate())
ORDER BY last_execution_time DESC;

That would list you all queries executed in the last week that still have the plan in cache along with the last execution time and database name (unless it's prepared statement).

But you need to realize this is no guaranteed way to get you ALL of your queries as there are multiple reasons a query could have been executed but never or no longer have a plan in cache.

For example there are ways to drop the entire plan cache or evict a single plan

If optimize for ad hoc workloads is active, a query which has only been executed once will not be retrieved by this query and plans that were once stored in cache can be evicted from the cache by anything that cause a plan recompile such as updated statistics, DDL statements, index rebuilds etc. Read through this whitepaper listing all circumstances that cause plan recompilation.


Embedding JSON in a string Wondering if I could get some insight on a design I saw recently. A collection can contain a few or tens of thousands of documents.

Each document has the same structure of _id and JSONValue. The latter just contains a large string which contains the JSON for that document.

I can’t quite understand why this is useful? You can’t query it easily. It seems to me like MongoDB is just a data store with the application doing all the grunt work.

Am I missing the point? Can you suggest some use cases for this design approach?

In MongoDB, documents stored in a collection require a unique _id field that acts as a primary key. If an _id does not exist in the document, MongoDB will create an ObjectID field. Generally, _id can itself be a complex document, any BSON data type (still, it must be unique). ObjectID() is described Here.

As MongoDB internally to store documents in BSON format. BSON extends the JSON model to provide additional data types and to be efficient for encoding and decoding within different languages. MongoDB BSON implementation supports embedding objects and arrays within other objects and arrays – MongoDB can even reach inside BSON objects to build indexes and match objects against query expressions on both top-level and nested BSON keys.

As MongoDB documented Here Data in MongoDB has a flexible schema. Collections do not enforce document structure. Decisions that affect how you model data can affect application performance and database capacity.

This document describes a data model that uses embedded documents to describe relationships between connected data.


selecting table1 and table2 with single data row and table 3 with multiple data rows I want to retrieve data from table1(payments), table2(properties), and table3 (property.assessments). See my visual representation below:

tables screenshot

Snippets:

SELECT a.payment_id, a.payment_arp_number, a.payment_pin, b.property_id, b.property_arp_number,
    b.property_location, c.property_id, group_concat(c.pa_classification separator ', ')
    as pa_classification, sum(c.pa_market_value) as pa_market_value
FROM `property.payments` a, properties b, `property.assessments` c
WHERE a.payment_arp_number = b.property_arp_number
AND b.property_id = c.property_id

The problem with this is it only fetch one row, if not it will fetch all but the classification and market_value are just the same with other rows. I want something like the expected result.

Expected result:
property.assessments can have multiple row of classification and market value with the same property_id.

result screenshots

The answer to my question is, all I need to do is add the GROUP BY.

SELECT a.payment_id, a.payment_arp_number, a.payment_pin, b.property_id, b.property_arp_number,
b.property_location, c.property_id, group_concat(c.pa_classification separator ', ')
as pa_classification, sum(c.pa_market_value) as pa_market_value
FROM `property.payments` a, properties b, `property.assessments` c
WHERE a.payment_arp_number = b.property_arp_number
AND b.property_id = c.property_id
GROUP BY a.payment_id


NOLOCK or Read Uncommitted locking / latching behaviours I've read many, many different takes on how NOLOCK or the read uncommitted isolation levels function in terms of locks / latches taken.

When using a SELECT with NOLOCK or in the Read Uncommitted Isolation level, is the only lock taken out a Schema Stability Lock, or do shared locks get taken on a rolling basis as the query works through the rows? (Obviously these locks would need to be dropped straight away)

What about latches? How are the pages in memory dealt with as I assume referencing an in-memory object that's in the middle of being modified isn't allowed?

Reads under READ UNCOMMITTED isolation only take Sch-S. No shared locks are acquired (there are a couple of rare exceptions).

Reads under locking READ COMMITTED take S locks that are usually released as soon as the query processor moves on to the next row (again, there are some exceptions).

Pages are always latched while being read or written to ensure physical consistency, generally a shared latch on read, and an exclusive latch on write. For details see Inside SQL Server Latches by Bob Ward via PASStv.

Locks are an implementation detail used by SQL Server to deliver the guarantees provided by the isolation level.


SQL Backups - Full vs Differential vs Log Shipping I'm looking to refine our SQL backups utilizing Full, Differential, and Log Shipping.  Currently, I am only performing full backups once at night.  This process is taking so long that it is running throughout most of the work day.  As we would like to use our standby server for reporting, this is not ideal.

I would like to increase the speed of our backups, but at the same time make things a bit more reliable.  What would be the best way to accomplish this with the above 3 methods?  Full backups once weekly, differential backups once daily, and log shipping every 15-30 min?

What considerations should I be making here? (Sorry this is a bit vague, just trying to get some input)

I am using SQL Server 2005

By "log shipping" I'm guessing you're meaning log backup?  Log shipping is a high availability strategy.

If your full backups are taking the better part of a day, yes I recommend that you switch things up a bit.  But that all depends on a lot of environment specific parameters.  For instance, do you have a big window on the weekend where you will be able to do a full backup without interfering with users? (If not, you may need to look into filegroup backups)

A full backup once a week, and diff backups daily (again, in a maintenance window) is pretty common.  As for your t-log backups, you need to determine the maximum amount of allowable data loss.  In other words, if you back up your transaction logs every 30 minutes, you will have a maximum of 30 minutes data loss if the tail of the log is corrupt and unrecoverable.  So determination will need to be made for transaction log backups.

(P.S. If you actually did mean Log Shipping then please clarify and I'll rework this answer.  But by judging by your question I am assuming you just mean log backups)


Which should I use on my DB? - Clustered Index or Non Clustered Index or Both I just need advice on how I am going to optimize my database.

| Date | Time | Area | Block | Data1 | Data2 | Data3 | DataN |

I have that format of tables on my database. Each tables has 30-days of records with hundred thousands of records each data. Uploading of data to database is every morning. The data of yesterday will be uploaded.

The most common queries to run is grouped by Date or Date and Area or Date and Area and Block. Now, in order to make queries faster, I found out using indexes. I used indexes before. But that was when I am using a primary key. In this table, I didn't use primary key because it is totally unnecessary.

Now, I am really confused on how I am going to optimize this. Based on this reference on Column Considerations part, clustered index is not a good choice on Columns that undergo frequent changes. I am really confused.

Please help me if I need to use clustered index here or just non clustered or both.

1/ If after upload you do not update the data, only delete the rows older than 30 days.

2/ And if you can sort your file for the upload by Date, Area, Block

=> Then a clustered index on Date,Area,Block will greatly benefit your aggregations on Date, Date + Area, Date+ Area+Block .

It will also help any request with a where condition on Date, Date + Area, Date + Area + Block , whatever the selected columns for the request.


Mysql Cluster Ndb Restore Foreign key constraint violated I am trying to restore mysql cluster backup on slave cluster.

When i run ndb_restore command it gives me following error:

$ /usr/local/mysql/bin/ndb_restore -n 6 -b 15 -r ./6

Opening file './6/BACKUP-15.6.log'
File size 1210464 bytes
execute failed: 255: Foreign key constraint violated: No parent row found

NDBT_ProgramExit: 1 - Failed

How can i skip foreign key errors ?

With any more complex data it is almost impossible to restore data in MySQL Cluster in one step. Usually one needs two steps:

ndb_restore <other_options> --disable-indexes

ndb_restore <other_options> --rebuild-indexes

First step has to be run on all nodes of the cluster. Second step though - just on one node which will rebuild all the indexes.

For foreign keys you can try to split first step into two:

ndb_restore <other_options> --restore_meta

ndb_restore <other_options> --restore_data --disable-indexes

--restore_meta has to be run on one node. --restore_data on all the nodes in cluster.


Count from a count (distinct) I have a query like this:

select * from questions as q
    left join question_filters as qf on q.id = qf.question_id
    left join users as u on q.user_id = u.id
    left join trainers as t on q.trainer_id = t.id
where q.deleted_at is null
    and qf.filter_id in (3,4)
    and q.status = 'published'
    and q.published_at < now()
    group by q.id
having count(distinct qf.filter_id) = 2

I have an "ALL IN" case, and I found this links works for me: https://stackoverflow.com/questions/7505045/mysql-in-operator-must-match-all-values.

But now I want to make a count for that same query:
My problem:
This query gaves me the error "Duplicate column name "ID" (all the tables in the join have ID)

select count(*) from ((select * from questions as q
    left join question_filters as qf on q.id = qf.question_id
    left join users as u on q.user_id = u.id
    left join trainers as t on q.trainer_id = t.id
where q.deleted_at is null
    and qf.filter_id in (3,4)
    and q.status = 'published'
    and q.published_at < now()
    group by q.id
having count(distinct qf.filter_id) = 2)) as first_query

Another attempt:

select count(q.id) from questions as q
    left join question_filters as qf on q.id = qf.question_id
    left join users as u on q.user_id = u.id
    left join trainers as t on q.trainer_id = t.id
where q.deleted_at is null
    and qf.filter_id in (3)
    and q.status = 'published'
    and q.published_at < now()
    group by q.id
having count(distinct qf.filter_id) = 1

returns many results, one for each result.
Thank you

The first query has a group by q.id. While MySQL allows you to use select * in a group by query, it is not the best practice, unless you are 100% sure that you want the undesired side effects. If you wan to not have ill-determined results, you should only include in the select list columns are are functionally dependent on the group by columns/expressions.

In your case, that would be any column from questions, users or trainers because you group by the primary key of questions and the joins to the other 2 tables are based on foreign key relationship from questions. But you should not put any column from the question_filters because that join is on a reverse foreign key relationship.

If/when you move to version 5.7, you would get an error with the 1st query otherwise.

Also, the left join to question_filters is actually treated as inner join, due to the where condition (and qf.filter_id in (3,4)). You could also change that to be explicit. The query becomes:

select q.*, u.*, t.*
from questions as q
    join question_filters as qf on q.id = qf.question_id
    left join users as u on q.user_id = u.id
    left join trainers as t on q.trainer_id = t.id
where q.deleted_at is null
    and qf.filter_id in (3,4)
    and q.status = 'published'
    and q.published_at < now()
    group by q.id
having count(distinct qf.filter_id) = 2 ;

Now for error you got with the count query: this is caused by the multiple columns with identical alias (id) in the subquery's select list. You can easily solve that by using select 1 or select q.* (or just select null).

What's more, the joins to  users and trainers are redundant for that query (because they are left joins) and so can be removed. The query becomes:

select count(*)
from
   (select 1 from questions as q
        join question_filters as qf on q.id = qf.question_id
    where q.deleted_at is null
        and qf.filter_id in (3,4)
        and q.status = 'published'
        and q.published_at < now()
        group by q.id
    having count(distinct qf.filter_id) = 2
   ) as first_query ;


Can I match two tables in postgresql, sum the data in the number column and create a new column? I have two tables. I have about 6500 rows in one table and about 450 rows in other table. The data in the two tables are the same.

What I really want to do is to collect the data in the column "adet" in my table of 450 rows(the table name is "depoyaeklenecek") and the "adet" column in the 6500-row table(the table name is depoadetleri"). Thus, I would like to refresh my table in a way that I can write the number that I have collected in the "adet" column in the 6500-row table.
However, there is a problem, I think I have to match the ids in the "kapino" column to be able to add them. Because the data in both tables are not sorted in the same way, the capino data are in mixed order. I want it to collect the data in the "adet" column in two tables when the "kapino" ids in both tables are equal.
They are not tables with the same rows anyway. One is about 450 lines and the other is 6500 lines. Since the data in one of them will not be in the other, I need to check if the data in the kapino column is equal, then I need to collect it, I think I'm not sure.
Can I ask for help for this? Thanks

As Lennart suggested, you should start with working example, which would help us better understood what the problem is, and what you need to be done. And please use english names in columns and table names.
(I wanted to response in comment, but apparently I have too less reputation to do that).
Basing on you description, though, it seems like simple join between these two tables, like:
select *
from depoyaeklenecek d1
inner join depoadetleri d2 on d2.kapino = d1.kapino

Going further, you should specify what to you understand by

collecting the data in the column "adet" - do you want to aggregate (sum?) them somehow? Or just fetch them to be used later?
refreshing my table in a way that I can write the number - do you mean that you want to update values in one table by values from the other one (per proper kapino number?) Or just want to sum these values and put them (where?)


Restore Diff with overwrite? We had developer restore a .diff with overwrite, the .bak was about 115 GB  for compressed for a  600+ GB database; the .diff was about a GB compressed.
Seems some data may be missing, does it only overwrite the changes?

Differential database backups contains pages that have changed since the last full backup.
From MSDN:

A differential backup is based on the most recent, previous full data backup. A differential backup captures only the data that has changed since that full backup. The full backup upon which a differential backup is based is known as the base of the differential. Full backups, except for copy-only backups, can serve as the base for a series of differential backups, including database backups, partial backups, and file backups. The base backup for a file differential backup can be contained within a full backup, a file backup, or a partial backup.

You cannot restore a differential backup over anything other than the freshly restored full backup used as a base for the differential.

At restore time, before you restore a differential backup, you must restore its base. Then, restore only the most recent differential backup to bring the database forward to the time when that differential backup was created. Typically, you would restore the most recent full backup followed by the most recent differential backup that is based on that full backup.

If you attempt to restore a differential backup, without first restoring the Full backup used as the base for the differential, you'll receive an error message, such as

Msg 3117, Level 16, State 1, Line 1
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Regarding your question:

What is the difference between restoring a diff and restoring a diff with overwrite?

Presumably you are talking about the WITH REPLACE clause on RESTORE DATABASE? RESTORE DATABASE xxx FROM DISK = 'C:\yyy.bak' WITH REPLACE, RECOVERY; will only touch modified pages when restoring a differential backup.
As a test, I performed the following:
USE master;
GO
IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = 'DiffTest')
BEGIN
    DROP DATABASE DiffTest;
END
CREATE DATABASE DiffTest;
GO

BACKUP DATABASE DiffTest
TO DISK = 'D:\SQLServer\Backups\DiffTest.bak'
WITH INIT;
GO

USE DiffTest;
GO
CREATE TABLE dbo.SomeDDL
(
    ID INT NOT NULL
);
INSERT INTO dbo.SomeDDL (ID)
VALUES (1);
GO

BACKUP DATABASE DiffTest
TO DISK = 'D:\SQLServer\Backups\DiffTest_DiffBackup.bak'
WITH INIT
    , DIFFERENTIAL;
GO

USE master;
GO
RESTORE DATABASE DiffTest
FROM DISK = 'D:\SQLServer\Backups\DiffTest.bak'
WITH REPLACE
    , RECOVERY;
GO
/*
    This select will fail because the dbo.SomeDDL table does not exist.

    Msg 208, Level 16, State 1, Line 5
    Invalid object name 'dbo.SomeDDL'.
*/
USE DiffTest;
SELECT *
FROM dbo.SomeDDL;
GO

USE master;
GO
RESTORE DATABASE DiffTest
FROM DISK = 'D:\SQLServer\Backups\DiffTest.bak'
WITH REPLACE
    , NORECOVERY;
GO
RESTORE DATABASE DiffTest
FROM DISK = 'D:\SQLServer\Backups\DiffTest_DiffBackup.bak'
WITH REPLACE,
    RECOVERY;
GO

/*
    This select runs because the dbo.SomeDDL table has been restored from the differential backup.
*/
USE DiffTest;
SELECT *
FROM SomeDDL;

The above code does the following:

Create a database
Take a backup of that database
Add a table and a row into that table
Take a differential backup of the database.
Restore the full backup.
See if the dbo.SomeDDL table exists (it doesn't, and an error is shown)
Restore the full backup.
Restore the differential backup.
See if the table and row exist - they do.

This proves restoring a differential backup will always replace the pages in the database with those contained in the backup file.


Return type of JOINing stored procedure - PostgreSQL Is it possible to express return type of stored procedure that returns joined tables using table type of one of joined tables?

My stored procedure looks like this:

CREATE or REPLACE FUNCTION selectJoin()
RETURNS SETOF ???
AS $$
BEGIN
    RETURN QUERY SELECT t1.*, t2.name
                 FROM t1, t2
                 WHERE t1.t2_id = t2.id;
END; $$
LANGUAGE plpgsql;

Table t1 has many (approx 70) columns so I'd like to somehow reuse the t1 type in RETURNS clause. Something like (t1, varchar(30)) or composite type inheritace. Is is possible?

PostgreSQL version 9.3

No, I am afraid that's not possible. I have been wishing this was possible myself on several occasions. Either you have a registered row (composite) type that matches the return type or you have to list columns individually.
CREATE or REPLACE FUNCTION select_join()
  RETURNS TABLE (col1 int, col2 date, ...) AS ...

There is a somewhat awkward workaround with returning a nested composite type, but it's not exactly the same. See:

Easy way to have return type be SETOF table plus additional fields?

Or you create a composite type:
CREATE TYPE foo AS (col1 int, col2 date, ...);

CREATE or REPLACE FUNCTION select_join()
  RETURNS SETOF foo AS ...

A VIEW also registers its row type automatically. For the simple test case you present, you might just use a view instead of the function to begin with (like @a_horse commented):
CREATE OR REPLACE VIEW my_view AS
SELECT t1.*, t2.name
FROM t1 JOIN t2 ON t1.t2_id = t2.id;

Or you create the view for the purpose of registering the row type conveniently. You might add LIMIT 0 to document that the view is not meant to return rows, but that's optional.
CREATE or REPLACE FUNCTION select_join()
  RETURNS SETOF my_view AS ...

Be aware that this introduces a dependency (like with any other view) and Postgres will prevent dropping the table and some other manipulations, unless you drop depending views first.


UTL_RAW "invalid identifier" error message When I execute the following:
select utl_raw.cast_to_raw('8') from dual;

I receive the error message below:

invalid identifier

...while in other PC with the same Oracle version, i.e., 11g, the query is ok.
My questions

What's the origin of this error message?

How can I fix it?

I found no related docs in the web.

Comment interactions

Is "other PC" connecting to the same Database Instance? – @Michael Kutz

I installed the same version of Oracle in two PCs, but only in one of them I received this error when I tried to make a backup.

By default, execute privilege on UTL_RAW package is granted to PUBLIC, meaning any database user can execute it.

Getting "invalid identifier" error means the package or referenced function does not exist, or you do not have the necessary privilege to execute it.

Either way, you have a non-standard environment.

Maybe it has fallen victim of overzealous security hardening and the execute privilege was revoked from PUBLIC.

Be Cautious When Revoking Privileges Granted to PUBLIC (Doc ID 247093.1)

Or it was revoked following documents such as:

Master Note for Oracle XML Database (XDB) Install / Deinstall (Doc ID 1292089.1)

To view the above documents, you need an Oracle account with a valid support contract associated with it.

As a DBA, you should know why the privilege was revoked. If you are not the DBA, ask the DBA who is responsible for this database.

If you want to restore the default state of this:

grant execute on sys.utl_raw to public;

That is how the package was originally created, you can view this in its definition:

$ grep utl_raw $ORACLE_HOME/rdbms/admin/prvtrawb.plb
CREATE OR REPLACE PACKAGE BODY utl_raw wrapped
create or replace public synonym utl_raw for sys.utl_raw
grant execute on sys.utl_raw to public


How to prevent the creation of Mysql columns with default values that are not defined Is there a Mysql server setting that will prevent the creation of columns that are NOT NULL but do not have a default value specified?

I'm trying to prevent the creation of fields like this:

ALTER TABLE mytable ADD mydate DATE NOT NULL;

I know I can prevent INSERTs by setting sql_mode=STRICT_ALL_TABLES, but I want to prevent the creation of the columns in the first place.

I don't think there is such an option.  You could request such at bugs.mysql.com.

I disagree with making NULL (or, for that matter) NOT NULL mandatory.  NULL and NOT NULL have important uses in schema design and execution.  PRIMARY KEYs must be NOT NULL, and that is implicitly provided.  Due to business logic, some columns should not be left out; some should be allowed to be optional.

NULL has a lot of different semantics, and arbitrarily forcing all columns to be NULL begs the issue of which semantics you want.  Examples:  Optional (product specifications); not yet provided (an end_date); unknown (due to partial data received).


mysqld: Can't create/write to file '/home/mysqltmp/ibXXX' Permission denied My main volume is a 20GB SSD disk mounted on /. I have a 4TB HDD mounted in /home. So to save space I want to set the mysql temporary directory to /home/mysqltmp.

However when I set to this directory mysql won't start. I get error:

mysqld: Can't create/write to file '/home/mysqltmp/ibm7cVN0' (Errcode: 13 "Permission denied")

I checked the permission and it is correct:

# ls -ld /home/mysqltmp/
drwxrwxrwx 2 mysql mysql 4096 Apr  4 03:19 /home/mysqltmp/

Running mysqld as root it starts without problem.

Server:

# lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 16.04.6 LTS
Release:    16.04
Codename:   xenial

Note: Problem arose after updating to 16.04, was working on 14.04

MySQL details:

# mysql -V
mysql  Ver 15.1 Distrib 10.1.38-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

From What to Do if MariaDB Doesn't Start

MariaDB's systemd unit file restricts access to /home, /root, and /run/user by default. This restriction can be removed by setting the ProtectHome option to false for the MariaDB systemd service. For example, you can reconfigure the MariaDB systemd service to allow access to /home by executing the following commands:

tee /etc/systemd/system/mariadb.service.d/dontprotecthome.conf <<EOF
[Service]

ProtectHome=false
EOF
systemctl daemon-reload

ref

After this fix your permission:

chmod go-rw /home/mysqltmp/


Time based query help I can't seem to get this to work the way I need it to. I have two tables Assignment and Event.

Assignment
StartDate     EndDate       Equipment
4/1/2016      4/23/2016     1001
4/3/2016      4/10/2016     1002
3/30/2016     4/20/2016     1003

Event
StartDate     EndDate       Equipment    Event
4/2/2016      4/3/2016      1001         maintenance
4/8/2016      4/10/2016     1001         maintenance
4/4/2016      4/5/2016      1002         maintenance

I need to join on the equipment and have an output that divides the Assignment using the events. For this sample data I would have an output that looks something like this:

StartDate     EndDate       Equipment    EventStatus
4/1/2016      4/2/2016      1001         Active
4/2/2016      4/3/2016      1001         maintenance
4/3/2016      4/8/2016      1001         Active
4/8/2016      4/10/2016     1001         maintenance
4/10/2016     4/23/2016     1001         Active
4/3/2016      4/4/2016      1002         Active
4/4/2016      4/5/2016      1002         maintenance
4/5/2016      4/10/2016     1002         Active
3/30/2016     4/20/2016     1003         Active

So for Equipment number 1001; it was put into an assignment then had maintenance twice. That maintenance split the assignment into 5 parts like so:

---------------------------------------------------------------------
|  Active  |maintenance|  Active  |maintenance|       Active        |
---------------------------------------------------------------------

Each of those parts must have its own row with the start and end date for that section. How can I write a query to make this work?

Another possible solution: (thanks John Eisbrener for the sample data)

DECLARE @Assignment TABLE
(
    StartDate DATE,
    EndDate DATE,
    Equipment VARCHAR(50)
);

INSERT INTO @Assignment VALUES
    ('4/1/2016', '4/23/2016', '1001')
   ,('4/3/2016', '4/10/2016', '1002')
   ,('3/30/2016', '4/20/2016', '1003');

DECLARE @Event TABLE
(
    EventID INT NOT NULL IDENTITY(1,2),
    StartDate DATE,
    EndDate DATE,
    Equipment VARCHAR(50),
    [Event] VARCHAR(50)
);

INSERT INTO @Event
    (StartDate, EndDate, Equipment, [Event])
VALUES
    ('4/2/2016', '4/3/2016', '1001', 'maintenance')
   ,('4/8/2016', '4/10/2016', '1001', 'maintenance')
-- Uncomment out prove it still works when stacking events back-to-back
-- ,('4/11/2016', '4/12/2016', '1001', 'maintenance')
   ,('4/4/2016', '4/5/2016', '1002', 'maintenance');

WITH AllEvents AS (

    -- Unpivot all the Assignments
    SELECT Equipment, V.EventDate, V.DateType, 'Active' AS EventType
    FROM @Assignment
    CROSS APPLY (
        VALUES
            (StartDate, 'start'),
            (EndDate, 'end')
    ) AS v(EventDate, DateType)

    UNION ALL

    -- Unpivot all the Events
    SELECT Equipment, V.EventDate, V.DateType, [Event] AS EventType
    FROM @Event
    CROSS APPLY (
        VALUES
            (StartDate, 'start'),
            (EndDate, 'end')
    ) AS v(EventDate, DateType)

)
SELECT AE.Equipment,
    AE.EventDate AS StartDate,
    NextEvent.EventDate AS EndDate,
    EventStatus =
        CASE
            WHEN AE.DateType = 'end'
                AND AE.EventType = 'maintenance' THEN 'Active'
            ELSE AE.EventType
        END
FROM AllEvents AE
CROSS APPLY (
    -- Next Event by date
    SELECT TOP(1) EventDate, EventType, DateType
    FROM AllEvents
    WHERE Equipment = AE.Equipment
        AND EventDate > AE.EventDate
    ORDER BY EventDate
) AS NextEvent
ORDER BY AE.Equipment, AE.EventDate


Starting postgresql service and specifying data directory I have recently installed pg 8.4 on Ubuntu 10.0.4 using apt-get install.

It installed pg as a service which starts ehenever the machine starts. However I have created a db cluster in a specific location that I want postgres to use.

This is the command line output of how I created the cluster:

username@jupiter:~$ sudo /usr/lib/postgresql/8.4/bin/initdb --encoding=UTF8 --pgdata=/mydata/pgdbdata/
initdb: cannot be run as root
Please log in (using, e.g., "su") as the (unprivileged) user that will
own the server process.
username@jupiter:~$ /usr/lib/postgresql/8.4/bin/initdb --encoding=UTF8 --pgdata=/mydata/pgdbdata/
The files belonging to this database system will be owned by user "username".
This user must also own the server process.

The database cluster will be initialized with locale en_US.utf8.
The default text search configuration will be set to "english".

fixing permissions on existing directory /mydata/pgdbdata ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 24MB
creating configuration files ... ok
creating template1 database in /mydata/pgdbdata/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the -A option the
next time you run initdb.

Success. You can now start the database server using:

    /usr/lib/postgresql/8.4/bin/postgres -D /mydata/pgdbdata
or
    /usr/lib/postgresql/8.4/bin/pg_ctl -D /mydata/pgdbdata -l logfile start

username@jupiter:~$

Now, I try to start the postgresql service:

Here is the command line output:

username@jupiter:~$ /usr/lib/postgresql/8.4/bin/pg_ctl start -D /mydata/pgdbdata/
server starting
username@jupiter:~$ FATAL:  could not create lock file "/var/run/postgresql/.s.PGSQL.5432.lock": Permission denied

So I have two questions:

How can I start the service and specify a data location (what am I doing wrong?)
I want the service started when the machine starts/reboots, to use the new data directory. Which file do I need to edit to ensure that I always have the service using the correct data folder?

There error is telling ... The problem is that you ran initdb as your local login, but I'm pretty that your login (username) doesn't have access to /var/run/postgresql. If you have a clean install, may I recommend that you remove the data directory and start all over, this time by su-ing into the postgres account:

username@jupiter:~$ sudo su - postgres

Then you can initdb

postgres@jupiter:~$ /usr/lib/postgresql/8.4/bin/initdb --encoding=UTF8 --pgdata=/mydata/pgdbdata/

Also, as Catcall mentioned, make sure you're using the correct location for your data.


set Windows authentication for postgres I'm trying to set Windows authentication for a postgres server.

The process is not well documented and makes the whole thing, obscure.

I found this doc but it is quite unclear.

In the necessary steps, the doc says that you need to create a domain user. Yet in the screenshot, it says DomainServiceAccount

Once, you've created the domain user and that you set your postgres service with the domain user, you need to create a Service Principle Name to your service account with the following command line in the AD server

setspn -S POSTGRES/[fully.qualified.domain.name] DOMAIN\[service_account_name]

Questions are the following

Is service_account_name = DomaninServiceAccount = Domain user?
If my first question is correct, that I create a user called SuperPostgres, I set the service as mentioned in the doc with the user SuperPostgres, that I create a SPN with the command line spn and set the relevant role as mentionned in the doc, is it certain that Postgres will allow an AD user like AndyKw to connect to the postgres db?

Yes

Service_account_name = DomaninServiceAccount = Domain user

A service account is actually a domain user, just with some extra settings like "no interactive login" and "password does not expire".
Dunno why they would use "DomaninServiceAccount" too. It's just Service_account_name

Is it certain? Just have to test, but it looks usual for non-Windows applications (Atlassian does the same, and I have other Java apps that have AD authentication too)


Two facts, two dimensions, one query, one cube, can it be done and how? I have four tables, two Facts and two Dimensions. Below I've listed the relevant columns of these tables, and the results I would like to see in the Cube.

Fact Order table
----------------
SK_Order
FirstEverOrder

Dim Order
---------
SK_Order

Fact Product Order
------------------
SK_Order
SK_Product
Number of Products

Dim Product
-----------
SK_Product
Product Name

In the results I would like to see the First Ever Order Rate. That is to say, out of all the times a product appears on an order, how many of those orders were the first ever order the customer placed?

Products      First Ever Order Rate (%)
--------      --------------------------
Product 1     3%
Product 2     2%

An example situation for the use of this report might be that you want to advertise those products that appear frequently on first orders.

So far I have my tables related together in the cube as follows:

Please, I do want to do this in the cube. I know it can be done via a stored proc with an SSRS report, but I want to do it in the cube.

I don't know what to do on either the Calculations or the Dimension Usage relationships tabs, or if I need to do anything more in the Cube Structure tab. Please be detailed in your answer.

In the Dimension Usage tab click the ... In the cell that is Fact Order and Dim Product. Switch that cell to a many-to-many relationship.

Then add a Sum measure on the FirstEverOrder column called First Ever Order Count. Also add a Count measure which is a count of rows called Order Count.

Now create a new calculated measure which is:

IIF([Measures].[Order Count]=0,Null,[Measures].[First Ever Order Count] / [Measures].[Order Count])

Now you should be able to build your report as you have shown. That's the power of many-to-many.


How to renew or extend the CMK Certificate in Always Encrypted feature? I have implemented Always Encrypted feature in SQL Server 2016. The CMK is valid for one year. How do I renew it, or extend the validity?

Always encrypted does not check for validity of the CMK certificate explicitly. Always Encrypted treats the CMK certificate purely as (PK, SK) pair.  so you can use the expired cert without running into any issues. However, the best practice is to rotate the CMK at regular intervals. Always Encrypted provides a mechanism to rotate CMK without incurring any application downtime. Please read the following official articles for details.
Article 1
Article 2

How to do CMK rotation without powershell:

Provision a new CMK (CMK2) (Create a new Certificate or create a new AKV key)
Use an appropriate instance of SqlColumnEncryptionStoreProvider.DecryptColumnEncryptionKey(String, String, Byte[]) method to decrypt your current encrypted CEK (CEK1) value (CekVal1)
Use an appropriate instance of SqlColumnEncryptionStoreProvider.EncryptColumnEncryptionKey(String, String, Byte[]) method to encrypt the cek with the new CMK (CMK2) to get new encrypted CEK value (CekVal2).
Use Create Column Master Key T-SQL to specify the new CMK (CMK2)
Use Alter Column encryption key T-SQL to ADD CekVal2 to your current CEK (CEK1)
At this point, your clients will be able to decrypt CEK1 with both CMK1 and CMK2.
After you have successfully distributed CMK2 to all clients that need it, Use Alter Column encryption key T-SQL to DROP CekVal1 from your current CEK (CEK1)
After this point, your clients will no longer be able to decrypt CEK1 using CMK1


What is the best way to determine, without an Oracle support contract, the release date of an Oracle Database 12c patchset? This is regarding a set of databases that I'm working with (I'm not a DBA, I just verify various settings and check for patching). The DBA is unable to provide the information I need.

What I run is SELECT patch_id, version, action, status, timestamp, description from dba_registry_sqlpatch;

What I need is a way to know when the patch identified by this query was released by Oracle. I have an ordinary Oracle SSO account, but no paid support within my account. The DBA doesn't either, as patches are provided to him and his counterparts at other locations after testing elsewhere.

Or if I'm running down the wrong path here altogether, my end goal is to be able to prove that the database has been patched to either the most current available, or the one released prior to that one.

Patchsets? That is easy.

12.1.0.1 is a base release
12.2.0.1 is a base release
12.1.0.2 is the one and only 12c patchset, it was released in July 2014: https://blogs.oracle.com/imc/oracle-database-12c-release-1-12102-generally-available

For PSU/RU/RUR/one-off patches, opatch lists the date they were packaged, e.g.:

$ /u01/app/oracle/product/12.2.0/dbhome_1/OPatch/opatch lsinv | grep Created -B 1

Patch description:  "Database Jul 2018 Release Update : 12.2.0.1.180717 (28163133)"
   Created on 6 Jul 2018, 08:05:03 hrs PST8PDT

Note that this is not the release date, they can be packaged weeks before the actual release.

Another thing to note, Oracle includes the release date in PSU/RU/RUR versions since 2016. So the version 12.2.0.1.180717 above means it is the RU for 12.2.0.1 that was released on 2018-07-17.

The list of most recent or the one released prior PSU/RUs:

12.1.0.2.180417
12.1.0.2.180717
12.2.0.1.180417
12.2.0.1.180717

The patching for 12.1.0.1 ended 2 years ago (2016-08-31).


Queries gaining by adding a new index How would i be able to find out which queries would gain by adding a new index.

Ex: Lets assume i add a new NCI on a table , how would i be able to find out which queries would benefit from adding this NCI( or any Index ) for that matter of fact?

I'd second Jonathan's comments. There are many resources (to show the DBAs)showing that automated "missing indexes" analysis is very limited and often misleading.

In addition to sp_blitzindex, if you get them to run the following query against each database (after a representative uptime), you can get an idea of which tables are the cause of possibly unnecessary IO due to high number of scans (considering the row counts) and lookups. And you can start to consider the indexes suggested just for those tables, and/or find the offending queries and consider indexing for them.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT  OBJECT_SCHEMA_NAME(p.object_id) AS [Schema]
    , OBJECT_NAME(p.object_id) AS [Table]
    , i.name AS [Index]
    , i.type_desc AS [Index Type]
    --, p.partition_number
    , p.rows AS [Row Count],
       USER_SEEKS,
       USER_SCANS,
       USER_LOOKUPS,
       USER_UPDATES
FROM sys.partitions p
INNER JOIN sys.indexes i ON p.object_id = i.object_id
                         AND p.index_id = i.index_id
left join (select * from SYS.DM_DB_INDEX_USAGE_STATS  where DATABASE_ID = DB_ID(DB_NAME())) b ON b.object_id = i.object_id and b.index_id = i.index_id
WHERE OBJECT_SCHEMA_NAME(p.object_id) != 'sys'


Database design: self referencing vs separate tables So, i have 1 table which contain payments with different type

id| reference_id | amount | type
1 | null         | 200    | debit
2 | 1            | 25     | refund
3 | 1            | 175    | refund
4 | null         | 0      | regist
5 | 4            | 100    | debit

as you can see, this is one entity named payment, each row MAY has relation to another row,

There is another way to represent this data by separating each type to their own tables. I will need debit_payment, refund_payment, regist_payment, and so on.

Which one is the best for this ? especially when we need to show list of the payment, and we also need to get their relation.

but there is another suggestion that i should separate each type to their own tables, so i will need debit_payment, refund_payment, regist_payment, and so on

You should use two tables because in this case, the "payment" is a transaction. And though the transaction includes a ledger entry for credit and debit those are two separate things. Further there isn't anything below (more atomic or subordinate to) the ledger entry.

A transaction is a batch of ledger entries in which the sum of those entries is 0. It requires no other information.
A ledger entry is attached to an transaction and one account, and exists only inside a transaction.

Here is an example,

CREATE TABLE transaction ( id int PRIMARY KEY );

CREATE TABLE ledger (
  ledger_id int PRIMARY KEY
  txn_id    int REFERENCES transaction,
  account   int REFERENCES account,
  amount    numeric(16,2)
);


Oracle Databases - need to export nightly for backup/DR purposes I know nothing about Oracle databases, so if my question's path seems odd, feel free to tell me the right way to do things.

I have 2 ways I see currently that I can backup a server running 3 Oracle databases for a 3rd party app we have.

I can use Symantec BE 2010 R3's oracle agent.  Which I downloaded and configured but it tells me that in order to backup the databases "when it is in NOARCHIVELOG mode, the database must be MOUNTED but not OPEN".  So I guess the question here would be how to change the databases to ARCHIVELOG mode so that they can be open when I back them up or how can I pass a command to CLOSE the database prior to backing them up and then OPEN it back up after the backup completes?
I can use the "exp" command.  The problem here is that I'm not sure what parameters/options to pass for each DB.  The 3rd party has steps to take, but they include choosing "(2) tables" and some other choices that don't seem to be default choices in the EXP command help.  For instance here's a screenshot of their exp "choices":

Any assistance with either one of these methods or if there's another/better method, please let me know.

Thanks!

if your database is a serious production database, it should be running in archivelog mode, no question about that. This is assuming that users are constantly doing transactions and that you don't want to lose them. If your database is only bulk loaded once a day, an export dump might be a valid solution as backup, if you know how to use it to re-create a new database, once you lost the prod database.

For now, logon as a database administrator on your server and switch to archivelog mode

sqlplus / as sysdba
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;

database is open for production and you can make online backups. This does not mean your database is out of the danger zone, there are still a few things that need to be setup to keep it running without problems.

First thing that will happen is that the filesystem where your archivelog files are written will get filled up, causing a full halt of the database.

best advice: hire a dba to do the job for you, it is easier to make a disaster than to prevent, or even worse, to recover from one. Without the proper config recovery can be close to impossible. There are lots of consultancy firms that offer adhoc services via some pre-paid system. Maybe giving the application to a service provider might be smarter. This al depends on what you do and what kind of protection against disaster[s] you want.

second best: start reading the docs Oracle® Database 2 Day DBA


SQL Server stored procedure keeps restoring to previous one There's a Stored Procedure in my database which keeps restoring to the old one, I Alter the stored procedure using Management Studio, however some time later, unexpectedly, the stored procedure changes to the one before Alter! This has happened several times during past hours!

I am certain that nowhere inside the program or manually I change the Stored Procedure. I checked all Agent Jobs, There's only one Maintenance job for hourly backup, and another one for Replication, however Replication Schema Changes is set to false, and Stored Procedures are not defined in the list of Publication Articles

Is there any other possible reason for this behavior? Do I need to manually set a CHECKPOINT in the SQL Server database?

A common problem when developers involves the schema that SQL Server assumes when creating or altering an object. This is especially likely in the following circumstances:

The developer has recently been removed from the db_owner role The
developer is working on a server that they normally don't.
They are likely to see the problem on a production server (where their
permissions are tightly controlled) rather than a development server (where they might be a member of db_owner or even sysadmin).

This problem is often described by developers as "SQL Server is using the old version of my proc". What really happens is that the developer is modifying a copy of the procedure and executing a different copy of the procedure.

The quick way out of this is to ensure that you are providing a schema for the stored procedure with your drop, create, alter and execute statements. In SQL Server, the vast majority of the time, that schema is "dbo". In other words, prefer this:

alter procedure dbo.foo
as
select 1
go
exec dbo.foo

and avoid this:

alter procedure foo
as
select 1
go
exec foo


Two diferent time mysql databases with same uniqueid autoincrement values need to be merged I am a beginner at SQL, so I will try to ask for help.
As I have a database where is primary key where values are incremented by +1.
And I made this database backup, and have another database same as that one.
Where unique id also auto increments. But now I need to insert the old database into an old one.
The only way to separate from old to new is the DateTime colon where is the date of each database value.

INSERT INTO new_table
    (id, ...)
    SELECT id + 1000, ...
        FROM old_table;

This assumes the target table has ids no larger than 1000; adjust if needed.


Large Data Long Query A Lot of Joins horibble performance I have 3 Tables Person, Family, City

Person Table (6 Million Rows)

id (primary key auto_increment unsigned)
family_id (index)
city_id (index)
full_name
first_name (index)
last_name (index)
date_of_birth
record_number
page_number

Family Table (100k Rows)

id (primary key auto_increment unsigned)
area
street
house

City Table (120 Rows)

id (primary key auto_increment unsigned)
city_name

The Query

SELECT SQL_CALC_FOUND_ROWS
a.id,a.full_name,a.date_of_birth,a.record_number,a.page_number,
family.area,family.street,family.house,
family.family_number,city.city_name
FROM person a
INNER JOIN family ON a.family_id = family.id
INNER JOIN city ON a.city_id = city.id AND a.first_name like '%term%'
JOIN person c ON a.family_id = c.id
JOIN person d ON a.family_id = d.id
JOIN person e ON a.family_id = e.id
AND c.first_name like '%term%'
AND d.first_name like '%term%' AND e.first_name like '%term%' LIMIT 0, 30

This query is taking about 14 seconds! on a Core i7 PC with 8GB RAM
is there any way to improve it ?

this is the EXPLAIN for the query

Is there something wrong with the query ? can i improve it
I added indexes on all rows in the JOIN Clause

Looking at your comments, I can see that the problem is not the JOINs, but the LIKE '%term%' operators.

There are several options here, but assuming you are using MyISAM for your tables, or InnoDB and a MySQL version equal or newer than 5.6, you may use FULLTEXT indexes. MySQL implementation is not perfect, but it will work way better than using '%LIKE%'.

mysql> CREATE TABLE person (id SERIAL, first_name VARCHAR(100));
Query OK, 0 rows affected (0.13 sec)

mysql> insert into  person (first_name) VALUES('AARON'), ('ABDUL'), ('ABE'), ('ABEL'), ('ABRAHAM'), ('ABRAM'), ('ADALBERTO'), ('ADAM'), ('ADAN'), ('ADOLFO'), ('ADOLPH'), ('ADRIAN'), ('AGUSTIN'), ('AHMAD'), ('AHMED'), ('AL'), ('ALAN'), ('ALBERT'), ('ALBERTO');
Query OK, 19 rows affected (0.01 sec)
Records: 19  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE person ADD FULLTEXT INDEX(first_name);
Query OK, 0 rows affected, 1 warning (0.73 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> SELECT * FROM person WHERE MATCH(first_name) AGAINST ('+al*' IN BOOLEAN MODE);
+----+------------+
| id | first_name |
+----+------------+
| 17 | ALAN       |
| 18 | ALBERT     |
| 19 | ALBERTO    |
+----+------------+
3 rows in set (0.00 sec)

Please note that it is not exactly the same query- it will only match whole words, it may have length restrictions, controlled by ft_max_word_len and ft_min_word_len (or innodb_ft_min_token_size and innodb_ft_max_token_size) and stop words are into play.


how to update a column from another database? I would like to update a field that has been delete from the backup database.
how can I update the field from an other database

If you want to write an SQL statement that affects tables from different databases, the best option is to use a postgres_fdw foreign table.

But perhaps it is simpler to extract the data from one database to a file using COPY and to load the file into the other database.


Oracle enterperise manager start error I have Oracle database 11GR2 on Windows 7.

When I want to start enterprise manager the error below is shown:

  Microsoft Windows [Version 6.1.7601]
  Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

  C:\Users\SHOJAIE>emctl start dbconsole
  Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0
  Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
  https://SHOJAIE-PC:1158/em/console/aboutApplication
  Starting Oracle Enterprise Manager 11g Database Control ...System error 5 has occurred.

  Access is denied.

a) you need a command prompt with elevated privileges (run as administrator) to do this

b) alternatively, you can start the service from Windows GUI (services.msc)


Negative numbers in InnoDB Status There are some negative numbers displayed in the InnoDB Engine Status output.
Anybody has encountered this or know why this happens ?

----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 1467883704; in additional pool allocated 26603520
Dictionary memory allocated 7134560
Buffer pool size 79552
Free buffers 0
Database pages 74624
Modified db pages 15997
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 12607789, created 151074, written 3737768
-7.21 reads/s, -0.17 creates/s, -1.10 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
1 queries inside InnoDB, 0 queries in queue
6 read views open inside InnoDB
Main thread id 7652, state: sleeping
Number of rows inserted 10757341, updated 2461678, deleted 1189294, read 7916897438
-14.00 inserts/s, -5.18 updates/s, -0.00 deletes/s, -14185.96 reads/s

I am using MySQL Version 5.1.51 (64 bit) on Windows Server 2008

I was able to replicate a similar condition by setting the system clock on a server (not Windows) to a time before the time when the MySQL Server process was started.  The numbers in my SHOW ENGINE INNODB STATUS are small since this is a test machine, but they're suddenly negative after changing the clock.

-0.00 inserts/s, -0.00 updates/s, -0.00 deletes/s, -0.01 reads/s

You might try this:

mysql> SHOW STATUS LIKE 'uptime';

This is a value in seconds that appears to be calculated as the difference between the current system time and what the system time was when the server was started... and may be the same number used to calculate these stats.

If you see a massive number (an artifact of unsigned subtraction) that seems completely inconsistent with the server's true uptime then that would be confirmation that this is the cause.

If this is what you find, then I suspect the only remedy is to restart MySQL Server with the system clock set accurately.


SQL Server's aggregate functions ignore nulls. How do I correct this? According to MS's knowledgebase,

  Except for COUNT, aggregate functions ignore null values.

Does anyone know how I can get it to work correctly, that is, return a null if it attempts to aggregate with a null in the values?

Example:

SELECT SUM("col")
FROM (
    SELECT NULL "col"
    UNION
    SELECT 1 "col"
) test

I would expect this query to return NULL, not 1.

You can simulate this by applying a where clause to your query:

with test_data (col) as (
   select null union all
   select 1 union all
   select 2
)
select sum(col)
from test_data
where not exists (select 1
                  from test_data
                  where col is null);

Edit

Andriy is right, this can also be written as:

with test_data (col) as (
   select null union all
   select 1 union all
   select 2
)
select case
          when count(*) = count(col) then sum(col)
          else null
       end
from test_data;


Implementing gMSA account for SQL FCI I have a question on implementing gMSA account for managing authentication to SQL server.

Our goal is to use gMSA account to manage the auto password change activities for SQl server service and it's agent service.

Few questions-

Does implementing gMSA works for 2 node SQL FCI and 3 or 4 Node Always on AG,s?
Since I just want to run SQl server with that gMSA account is there any special permissions needs to be granted at server or database level  while running both SQL and agent services?

Our AD team gets all the background work related to creation of those accounts. Once ready we are just handed over the abc$ account e.g. to use them for running SQL services without entering the need of any password.

Please advise any catch of implementing gMSa here.

Does implementing gMSA works for 2 node SQL FCI and 3 or 4 Node Always on AG,s?

Yes it does work.

  Since I just want to run SQl server with that gMSA account is there any special permissions needs to be granted at server or database level while running both SQL and agent services?

Nope, the installer will take care of that.

  Please advise any catch of implementing gMSa here.

If certain windows services don't start before SQL Server, then authentication can't take place and SQL Server will fail to start up. This doesn't happen very often and generally when it does there is some other issue with the server that is causing this symptom and behavior.


Function is accepting varchar when input is set to INT I have a function that is suppose to have INT as input. However, when I pass any string, the input is accepted.

Why does the function not error out because of the input not matching the input type?

This is the function

DROP FUNCTION IF EXISTS erbaccess.canAdminIRB;

DELIMITER $$
CREATE FUNCTION erbaccess.canAdminIRB(_userID int) RETURNS int

BEGIN

set @canPerformAction = 0;

if (select count(*)
    from erbaccess.usersVsGroupsVsActions
    where (actionCode in ('ADMSYS', 'ADDIRB')
            or privilegeGroupCode  in ('SYS', 'REG')
    and userID = _userID) > 0 then

    set @canPerformAction = 1;
end if;

RETURN @canPerformAction;

END $$

The RETURN for @canPerformAction is also returned as 1, when it should be 0. IF I run just the if statement outside of this function with a fake username (not INT, but VARCHAR) then the return is 0.

This is how MySQL liberally performs implicit type conversion. Seeing that the function wants an integer parameter, the server tries to convert whatever you supply (a character string) into an integer. If the string only contains digits, the conversion is trivial. If the string contains digits first and then some non-digits, those latter are truncated at the first non-digit, and the remainder is converted to an integer. If the string begins with non-digits, it is converted to a 0.

dbfiddle


Oracle RAC Maintenance Is it possible to keep one node running in 2 node cluster while keeping  one node down for Maintenance?.I want to do this as we are planning some hardware upgrades.

It is actual quite easy if you are obvious of some caveats:

configure your services to run on one node only: srvctl modify service -db $DATABASE -service $SERVICENAME -preferred $INSTANCE1 -available $INSTANCE2 -modifyconfig
wait for the sessions to wander to the node running $INSTANCE1. This may take some time, it's heavily dependent on the type of application you are running. JAVA Applications tend to reconnect quite frequently. So check with your database, e.g. query gv$session.
Take down your node and do whatever maintenance there is to do
Reconfigure your services to run on the second node:

srvctl modify service -db $DATABASE -service $SERVICENAME -preferred "$INSTANCE1,$INSTANCE2" -modifyconfig
srvctl start service -db $DATABASE -service $SERVICENAME -node $NODE This step is crucial, if you omit it, the whole procedure won't work!
srvctl modify service -db $DATABASE -service $SERVICENAME -preferred $INSTANCE2 -available $INSTANCE1 -modifyconfig

wait again for the sessions to wander to your patched node
Take down your second node and do whatever maintenance there is to do
reconfigure your services to run on both nodes and start the services

srvctl modify service -db $DATABASE -service $SERVICENAME -preferred "$INSTANCE1,$INSTANCE2" -modifyconfig
srvctl start service -db $DATABASE -service $SERVICENAME

The procedure must be tested in your environment as there may be dependencies e.g. if you use a shared ORACLE_HOME on ACFS. The success of reconfiguration of services must be tested, especially if you have client-server applications.


Are there any fully declarative implementations of SQL I was recently bitten by a bug ( in my code ) where these two queries had drastically different runtimes:

select * from smalltable st
    inner join bigtable bt on st.btid = bt.btid

select * from bigtable bt
    inner join smalltable st on bt.btid = st.btid

As well as the individual queries finding different execution plans for different strings in the where filters.

Are there any optimizing, fully declarative implementations of the SQL standard, such that the two above queries would have equal execution plans?

Any database with a cost-based optimizer (which includes basically any enterprise-class database) would generally treat those two queries as identical.  But no cost-based optimizer is or can be perfect-- they will all occasionally pick a poor plan.

If you're using a cost-based optimizer and the optimizer has reasonably accurate information about the tables and indexes (this information is generally gathered asynchronously and generally gathered in the evening/ morning when other activity is relatively light), it should be relatively clear (for some values of smalltable and bigtable and assuming proper indexes) that the most efficient plan is to do a full table scan of smalltable and then do index lookups on bigtable for those rows using the join condition.  If the database thinks that smalltable has 10% of the rows in bigtable rather than 0.1% of the rows in bigtable, on the other hand, there are a lot of different plans that have to be considered.  As the number of tables, the number of predicates, and the number of access paths the database supports grows, the potential number of query plans grows exponentially.  But the time the optimizer has to find the best plan does not grow-- you're generally going to be upset if the database takes a couple minutes to figure out the best plan before it even starts to execute the query.  So cost-based optimizers will never be perfect-- they don't have the time to fully explore every path and they rely on statistics that may be stale in order to figure out which plans are optimal.  Most of the time, however, they're quite good at figuring out the best way to join two tables when a query has no predicates and a simple join condition.


How to speed up group by I am trying to speed up the following query, which currently takes 14 seconds:

SELECT
  COUNT(*) AS employee_count, e.jurisdiction_id, e.year
FROM
  employee_employee e
GROUP BY
  e.jurisdiction_id, e.year
ORDER BY
  employee_count ASC;

I add an index by running:

CREATE INDEX ON employee_employee (jurisdiction_id, year);

But that did very little to improve the timing because the GROUP BY is taking the longest to execute. Is there a way to index a GROUP BY function?

Taking a simple approach:

Execute inside psql, enable \timing and run your query. Also check query execution plan:

explain select <your query>;

Then run vacuum for your table:

vacuum (full, analyze, verbose) employee_employee;

And check if query execution plan was improved, you'll see what's the costly stage:

explain select <your query>;

Considering that your query is a simple query, there's no much to do beyond that inside the database.
Optionally evaluate if a materialized view fits your need.


What is the error in this TRIGGER? The MySQL version I'm using is 5.1.46.
The query is

CREATE DEFINER = CURRENT_USER TRIGGER `movimentacao_before_del_tr` BEFORE DELETE ON `movimentacao`
  FOR EACH ROW
 IF (OLD.stMov IN ("E", "C", "R")) THEN
    INSERT INTO `controle` (
      `tabela`,
      `data`,
      `hora`,
      `registro`
    ) VALUES (
      MD5("movimentacao"),
      OLD.dtEmissao,
      CURTIME(),
      CONCAT_WS("|",
        OLD.idMovimentacao,
        OLD.idEcf,
        OLD.numFabricacao,
        OLD.CCF,
        OLD.stMov,
        OLD.tpNota,
        OLD.modoMov,
        OLD.dtMovimentacao,
        OLD.dtEmissao,
        OLD.hrEmissao,
        OLD.numNF,
        OLD.idPessoa,
        OLD.cpfCnpj,
        OLD.rgIe,
        OLD.nomePessoa,
        OLD.codcidade,
        OLD.uf,
        OLD.cidade,
        OLD.bairro,
        OLD.logradouro,
        OLD.numero,
        OLD.complemento,
        OLD.cep,
        OLD.totalBruto,
        OLD.valAcrecimo,
        OLD.valDesconto,
        OLD.valOutrasDespesas,
        OLD.valFrete,
        OLD.valSeguro,
        OLD.bcICMS,
        OLD.valICMS,
        OLD.bcICMSST,
        OLD.valICMSST,
        OLD.valPIS,
        OLD.valCOFINS,
        OLD.totalLiquido,
        OLD.idNatOp,
        OLD.idAidf,
        OLD.especie,
        OLD.serie,
        OLD.docProprio,
        OLD.chaveNFe,
        OLD.motivoCancelamento,
        OLD.hrMovimentacao,
        OLD.IdTransportador,
        OLD.modFrete,
        OLD.qtdeVolumes,
        OLD.especieVolumes,
        OLD.marcaVolumes,
        OLD.pesoL,
        OLD.pesoB,
        OLD.infAdFisco,
        OLD.infCpl,
        OLD.enderecoTransp,
        OLD.cidadeTransp,
        OLD.UfTransp,
        OLD.finNFe,
        OLD.placaVeicTransp,
        OLD.ufVeicTrans,
        OLD.RNTC,
        OLD.idOperador,
        OLD.chk,
        OLD.hash,
        OLD.hash2
      )
    );
  END IF;

The returned is #1064 - Você tem um erro de sintaxe no seu SQL próximo a '' na linha 79. Translation: You have a MySQL syntax error near to '' on line 79.

The Workbench show an erro at the last line: syntax error, unexpected END_OF_INPUT, expecting ';'

You need to change the delimiter to accept semicolons properly while creating the trigger

DELIMITER $$
CREATE DEFINER = CURRENT_USER TRIGGER `movimentacao_before_del_tr` BEFORE DELETE ON `movimentacao`
  FOR EACH ROW
 IF (OLD.stMov IN ("E", "C", "R")) THEN
    INSERT INTO `controle` (
      `tabela`,
      `data`,
      `hora`,
      `registro`
    ) VALUES (
      MD5("movimentacao"),
      OLD.dtEmissao,
      CURTIME(),
      CONCAT_WS("|",
        OLD.idMovimentacao,
        OLD.idEcf,
        OLD.numFabricacao,
        OLD.CCF,
        OLD.stMov,
        OLD.tpNota,
        OLD.modoMov,
        OLD.dtMovimentacao,
        OLD.dtEmissao,
        OLD.hrEmissao,
        OLD.numNF,
        OLD.idPessoa,
        OLD.cpfCnpj,
        OLD.rgIe,
        OLD.nomePessoa,
        OLD.codcidade,
        OLD.uf,
        OLD.cidade,
        OLD.bairro,
        OLD.logradouro,
        OLD.numero,
        OLD.complemento,
        OLD.cep,
        OLD.totalBruto,
        OLD.valAcrecimo,
        OLD.valDesconto,
        OLD.valOutrasDespesas,
        OLD.valFrete,
        OLD.valSeguro,
        OLD.bcICMS,
        OLD.valICMS,
        OLD.bcICMSST,
        OLD.valICMSST,
        OLD.valPIS,
        OLD.valCOFINS,
        OLD.totalLiquido,
        OLD.idNatOp,
        OLD.idAidf,
        OLD.especie,
        OLD.serie,
        OLD.docProprio,
        OLD.chaveNFe,
        OLD.motivoCancelamento,
        OLD.hrMovimentacao,
        OLD.IdTransportador,
        OLD.modFrete,
        OLD.qtdeVolumes,
        OLD.especieVolumes,
        OLD.marcaVolumes,
        OLD.pesoL,
        OLD.pesoB,
        OLD.infAdFisco,
        OLD.infCpl,
        OLD.enderecoTransp,
        OLD.cidadeTransp,
        OLD.UfTransp,
        OLD.finNFe,
        OLD.placaVeicTransp,
        OLD.ufVeicTrans,
        OLD.RNTC,
        OLD.idOperador,
        OLD.chk,
        OLD.hash,
        OLD.hash2
      )
    );
  END IF $$
DELIMITER ;

Give it a Try !!!


Database Mirroring login attempt failed with error: 'Connection handshake failed. The handshake verification failed. State 36.' While performing a rolling upgrade of an Availability Group from SQL Server 2016 CU1 to 2016 SP1 we noticed an issue. Immediately after we updated the secondary replica replication stopped working for that node and the SQL logs indicated there was an issue with the Database Mirroring login:

  Database Mirroring login attempt by user 'DOMAIN\SERVICEACCOUNT.'
  failed with error: 'Connection handshake failed. The handshake
  verification failed. State 36.'. [CLIENT: 10.1.2.3]

What causes this issue and how can we resolve it?

This is an issue with the encryption method used on the database mirroring endpoint. In SQL Server 2014 the default encryption type was RC4, but in SQL Server 2016 the new default is AES and it looks like the CU2 update may have removed support for RC4.

To detect and fix this you can run the following on your nodes:

-- Looking at encryption_algorithm - RC4 is deprecated in 2016 CU2+
Select * From sys.database_mirroring_endpoints

Alter Endpoint Hadr_endpoint
 STATE=STARTED
 AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
 FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE
 , ENCRYPTION = REQUIRED ALGORITHM AES RC4)
 GO

After which replication should start working again and you can continue with the rolling upgrade.


postgresql 9.* - user cannot SELECT from VIEW inside a SCHEMA as DEFAULT PRIVILEGE hope you can help me.

I have 3 users

First is me, the db admin. I run the GRANT queries, I can do anything. (name doesn't matter now)
Second is the data manager user. = manager_u
Third is the data viewer user. = viewer_u

The "data viewer" should read from views created by "data manager", without managing privileges afterwards
For that reason I create a schema, owned by manager, that should be full open to the viewer.

The problem/actual situation is (after running my following code) this.

When manager_u create a named view, viewer_u cannot SELECT from it.

If I run this, AFTER the creation:

GRANT SELECT ON ALL TABLES IN SCHEMA views_for_viewer_u TO viewer_u;

he can read. Everything works.
But I like that he can read any further views without managing his privileges.
I tried with that, with no success:

ALTER DEFAULT PRIVILEGES FOR ROLE viewer_u IN SCHEMA views_for_viewer_u GRANT SELECT ON TABLES TO viewer_u;

Here, the complete code:

--
-- QUERIES as db admin
--

CREATE ROLE manager_u LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
CREATE ROLE viewer_u LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

ALTER ROLE manager_u WITH PASSWORD 'yyy';
ALTER ROLE viewer_u WITH PASSWORD 'xxx';

GRANT CONNECT ON DATABASE mydb TO manager_u;
GRANT CONNECT ON DATABASE mydb TO viewer_u;

---
---                     schema
---
CREATE SCHEMA IF NOT EXISTS views_for_viewer_u;

ALTER SCHEMA views_for_viewer_u OWNER TO manager_u;

GRANT ALL PRIVILEGES ON SCHEMA views_for_viewer_u TO manager_u;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA views_for_viewer_u TO manager_u;
ALTER DEFAULT PRIVILEGES FOR ROLE manager_u IN SCHEMA views_for_viewer_u GRANT ALL PRIVILEGES ON TABLES TO manager_u;

---
---   privileges on views_for_viewer_u schema
---   I think the problem is here but I'm going crazy
---   I beg help
---

GRANT USAGE ON SCHEMA views_for_viewer_u TO viewer_u;
GRANT SELECT ON ALL TABLES IN SCHEMA views_for_viewer_u TO viewer_u;
ALTER DEFAULT PRIVILEGES FOR ROLE viewer_u IN SCHEMA views_for_viewer_u GRANT SELECT ON TABLES TO viewer_u;

--
-- QUERIES as data manager:  manager_u
--

CREATE OR REPLACE VIEW views_for_viewer_u.proxy_table
    AS SELECT * FROM another_schema.a_table;

--
-- QUERIES as viewer:  viewer_u
--

SELECT * FROM views_for_viewer_u.proxy_table; -- this is what is not working
--
--
-- thanks to anyone can give an hint
--
--

Your mistake is in the ALTER DEFAULT PRIVILEGES command. It should be

ALTER DEFAULT PRIVILEGES FOR ROLE manager_u ...

This way, the views created by manager_u will get the permission granted.


Index Rebuilding Log File Growth I would like to start running regular index maintenance on a server that has been running for a while without it. The database is running in FULL recover with log shipping. The problem I am having is log space. The partition holding the log does not have the room to hold all of the growth that will occur during the index maintenance.

So far I have come up with two solutions. The first is rebuilding the big ones manually during my weekly maintenance window until I get them under control. This will take a very long time and be time consuming to do. The second is switching the database to SIMPLE during my maintenance window. This is not ideal but seems to be my best bet for getting this done in a short period of time.

Are there any other solutions to this problem?

So your database is in full recovery model then?  You can script out the rebuild commands for the index and have a pause command to wait for the next log backup to complete.  This would rebuild at most one index at a time.  It would be slow but could be done during the evening and would get you caught up faster.


SQL Server to MySQL sync, using Workbench, FreeTDS, and iODBC on 10.9 I've been struggling to get the MySQL Workbench to behave properly to complete the following:

With Mavericks (OS X 10.9), connect to a SQL Server and create a migration job to a MySQL server.

The main issue seems to be with ODBC drivers. iODBC was deprecated with 10.8, and the header files removed complete in 10.9 (Some discussion on this here. More in-depth discussion here).

Despite my best efforts, this is the farthest I have gotten in Workbench, when trying to Test the connection to a SQL Server on a local subnet:

  The ODBC driver that was selected for the source connection was not found by the ODBC manager.
  Ensure that you have the proper ODBC driver installed and retry.

  Error Text: Error("('00000', '[00000] [iODBC][Driver Manager]dlopen({FreeTDS}, 6): image not found (0) (SQLDriverConnect)')"): error calling Python module function DbMssqlRE.connect

Here is what I have done up to this point:

installed FreeTDS .92.XX from their site (homebrew install of FreeTDS wasn't acceptable, as it uses .91. Apparently .92 or greater is required, according to the Docs I have been following on MySQL, located here
Installed the latest iODBC files I could find on iodbc.org (seemingly forgotten since 10.6)
Using homebrew, installed unixODBC, just in case that provided some alternative, which did not seem to be of any use either,
installed ODBC Administrator App, v 1.1.0 from Apple

Has anyone successfully connected to a SQL Server database as a source, using the Workbench on Mac OS 10.9?

About to give up and opt for the Windows app, because this iODBC stuff is becoming pretty frustrating 5+ hours in :(

Edits

It seems that PuTTY is showing simple telnet and ssh commands over ports 3606 and 22 are not accepted either.

The firewall is completely open on Windows, from what I can tell. Private, Public, and Corporate settings allow for all inbound and outbound traffic.

root and users with a wildcard host are not working either :-/

Was trying to solve the same problem and stumbled here...  It's a year late, but better late than never...

OSX uses iODBC as ODBC layer, so FreeTDS must be compiled against iODBC for MySQL Workbench to connect.

Since I struggled with iODBC as well, what I ended up doing was to create a custom "library installation" to run this.

Steps are as follows:

Make sure you have XCode and command line development utilities installed.  You will compile lots of stuff!
Download and compile iODBC (I used 3.52 from www.iodbc.org).  Make sure you install somewhere "isolated" with "--prefix=/usr/local/iodbc" for instance;
Download and compile FreeTDS:

cd to freetds source directory;
type export CFLAGS="-arch i386"
type export LDFLAGS="-arch i386";
run config with ./configure --prefix=/usr/local/freetds --with-iodbc=<path_to_iodbc_in_step_1> --enable-odbc-wide
(MySQL Workbench NEEDS 32-bit archtecture; the exports will ensure that, since Mavericks - my OSX - is 64 bit.)

Download and install ODBC Administrator from Apple (https://support.apple.com/kb/DL895?locale=en_IS - version 1.0 worked for me);
Run ODBC Admin, go to "Driver" tab and create a "TDS" driver; Point "driver file" to /usr/local/freetds/lib/libtdsodbc.so (it should be there after step 3!)
Go to MySQL Workbench Migration Tools and choose as Connection Method "ODBC (FreeTDS)".  In "Driver", type the name you used in step 5 ("TDS") and fill the other info as needed.
"Test Connection" and go have a cup of your favorite drink before moving on! :)

One entire workday "invested", but it did work.

UPDATE

After my cup of coffee, I moved on with my test migration, only to find out my SQL Server connection was NOT actually connecting (correct user, correct password).  Another morning's work and here are the adjustments:

Make sure you compile iODBC to 32-bit architecture.  To do so:

use the same export you will use when compiling FreeTDS (CFLAGS,LDFLAGS);
use --prefix=/usr/local/iODBC32.  Make sure you compile to different directory or you may break your current ODBC install.

Make sure you compile your "32-bit" FreeTDS version pointing to the "right" iODBC with:

./configure --with-iodbc=/usr/local/iODBC32

This should do the trick.

Another piece of advice:

MySQL Migrations will use exec sys.sp_databases to fetch info about the schemas to be migrated.  If you're not using sa to migrate (which you probably shouldn't!), make sure your login has enough privileges on the master database (I myself created a "temporary" clone of sa to migrate); otherwise, MySQL Migrations will have trouble fetching and copying.

All these things followed and I finally completed a successful migration (minor manageable collation problems).

Hope this helps,


Disk usage spikes: good, bad or indifferent? I'm working on a project where we're receiving data from multiple sources, that needs to be saved into various tables in our database.

Fast.

I've played with various methods, and the fastest I've found so far is using a collection of TableValue parameters, filling them up and periodically sending them to the database via a corresponding collection of stored procedures, inside a transaction.

The results are quite satisfying. However, looking at disk usage (% Idle Time in Perfmon), I can see that the disk is getting periodically 'thrashed' (a 'spike' down to 0% every 13-18 seconds), whilst in between the %Idle time is around 90%. I've tried varying the 'batch' size, but it doesn't have an enormous influence.

Is this 'normal'? Given that the overall average throughput is ok, I could just leave it, but I'm a bit worried about exploding buffers.
Should I be able to get better throughput by (somehow) avoiding the spikes while decreasing the overall idle time

Other details:

The database is in Simple recovery mode, and pre-sized to 'big', the log file is not growing
The ldf and mdf files are on different disks (the mdf on a striped array, ldf on a 'normal' disk, all I had available)

Indifferent.

SQL Server batches updates - and especially dirty writes with so called checkpoints. This is pretty much normal behavior and only a problem when it turns into a problem (i.e. too large, slowing down the system etc.)


How to get default locking hint of a table Is there is any way to get default locking hint using a query. I want to know whether the locking use by my transaction is tablock | rowlock | nolock etc.

You can run you query in a transaction and run sp_lock @@spid after running the query, you will know all the locks held.


Why is Clustered Index Scan Number of Executions so high? I have two similar queries that generate the same query plan, except that one query plan executes a Clustered Index Scan 1316 times, while the other executes it 1 time.

The only difference between the two queries is different date criteria. The long running query actually narrower date criteria, and pulls back less data.

I have identified some indexes that will help with both queries, but I just want to understand why the Clustered Index Scan operator is executing 1316 times on a query that is virtually the same as the one that where it executes 1 time.

I checked the statistics on the PK that is being scanned, and they are relatively up to date.

Original query:

select distinct FIR_Incident.IncidentID
from FIR_Incident
left join (
    select incident_id as exported_incident_id
    from postnfirssummary
) exported_incidents on exported_incidents.exported_incident_id = fir_incident.incidentid
where FI_IncidentDate between '2011-06-01 00:00:00.000' and '2011-07-01 00:00:00.000'
    and exported_incidents.exported_incident_id is not null

Generates this plan:

After narrowing the date range criteria:

select distinct FIR_Incident.IncidentID
from FIR_Incident
left join (
    select incident_id as exported_incident_id
    from postnfirssummary
) exported_incidents on exported_incidents.exported_incident_id = fir_incident.incidentid
where FI_IncidentDate between '2011-07-01 00:00:00.000' and '2011-07-02 00:00:00.000'
    and exported_incidents.exported_incident_id is not null

Generates this plan:

The JOIN after the scan gives a clue: with less rows on one side of the last join (reading right to left of course) the optimiser chooses a "nested loop" not a "hash join".

However, before looking at this I'd aim to eliminate the Key Lookup and the DISTINCT.

Key lookup: your index on FIR_Incident should be covering, probably (FI_IncidentDate, incidentid) or the other way around. Or have both and see which is used more often (they both may be)
The DISTINCT is a consequence of the LEFT JOIN ... IS NOT NULL. The optimiser has already removed it (the plans have "left semi joins" on the final JOIN) but I'd use EXISTS for clarity

Something like:

select
    F.IncidentID
from
    FIR_Incident F
where
    exists (SELECT * FROM postnfirssummary P
           WHERE P.incident_id = F.incidentid)
    AND
    F.FI_IncidentDate between '2011-07-01 00:00:00.000' and '2011-07-02 00:00:00.000'

You can also use plan guides and JOIN hints to make SQL Server use a hash join, but try to make it work normally first: a guide or a hint probably won't stand the test of time because they are only useful for the data and queries you run now, not in the future