MongoDB: RAM requirements Is it enough to have the entire index in memory/ram or does mongodb even try to allocate as much ram as possible to store even the data for fast reads?

I'd like to run mongodb + other applications and it looks like mongodb is the only one which does not allow me to define a range of RAM, to lets say "max_memory_allocated_or_reserved=8GB".

If there is no way to do so, I should explain to oom-killer that mongod is the "bad" process which is not best practise in my opinion...

The real reason why you can't do as you ask (limit the memory) is because MongoDB doesn't manage the memory it uses directly - it lets the OS do it.  MongoDB just memory maps all its data and then has the OS page it in and out of memory as needed.  As a result, there is no direct management of the amount used possible until MongoDB implements this in a completely different way, or the OS allows it (not possible in Linux since the 2.4 days).

The only way to truly segregate resources at present is to use a virtualization solution and isolate MongoDB in its own VM.  Yes, there are overheads involved (though hypervisors have gotten a lot better), but at the moment that is the price to be paid for that level of resource control.

In terms of the OOM Killer, even with no other processes on the host, as long as your data set and indexes overall exceed available memory, MongoDB can hit OOM Killer issues.  This is because of how the data gets paged out of memory - if there is no memory pressure (nothing else wants Resident memory), and you keep adding/touching new data and indexes, then eventually it will grow to consume all available RAM.  Hence the recommendation to always configure some swap when running MongoDB:

https://docs.mongodb.com/manual/administration/production-notes/#swap

Of course, LRU data will be paged out first, other processes can take up the res mem also, but the concept still applies unless you load your data set into memory and then it stays static.  Best thing to do if you are worried is get it into MMS and track the usage over time:

http://mms.mongodb.com

Update: August 2015

Since I wrote this answer things have moved on somewhat and the information is a little out of date.  For example, Linux now has cgroups and related technologies (Docker containers for example) that have matured to the point that they allow you to better isolate and limit the resources (including memory) consumed by any process in a production environment, even one that uses memory mapping like MongoDB.

Additionally, with the advent of new storage engines beyond MMAP like WiredTiger in MongoDB 3.0+ you can use built-in functionality to limit the cache size for MongoDB.  Hence, the RAM requirements now really do depend on how you choose to configure MongoDB, what environment you run it in, and what storage engine you choose.


How to setup sync between MongoDB and C# .NET Application What is the best practice to synchronize a MongoDB remote database with a C# .NET App that would allow queries to be responded to within the local file system and update itself when there is a change in the master database which is on a remote web server.

Some methods I came across are syncing MongoDB to a local JSON file using Node.js.

Please suggest a solution.

You need a tailable cursor (http://docs.mongodb.org/master/tutorial/create-tailable-cursor/) that will read the MongoDB oplog and propagate the changes. The oplog logs every CRUD operation and the tailable cursor will trigger on every change.


MySQL RAID configuration I attached 4 10GB disks to MySQL Server and I used RAID 0:

but still MySQL performance is the same. Do I have to change configuration in my.cnf to let MySQL know about RAID 0?

This are my mount results:

This is not development box, it is my production server. I used to have MySQL server on AWS but now I am using Azure, and I/O really hurts MySQL.

I am not a server admin expert, but I am trying to solve here my problem, a consultant recommended to me to do RAID 0.

I am using newrelic to test my performance. It shows how bad is MySQL, my server is 8 cores and 14 GB Azure server.

Firstly: unless this is a replication slave, development box, or similar, that can easily be reconstructed from other sources should the worst happen, RAID0 is not recommended. It provides good performance but potentially seriously bad reliability: if any one of your four drives fails then the whole array is toast.

Edit after new information: it would appear from a quick search that people do recommend RAID0 on Azure, on the assumption that the underlying (virtual) disks are reliable due to the redundancy in the platform. I'm not convinced this is a good idea, though I've not used the platform so might not be the best person to comment there.

Secondly: without knowing anything at all about your data size, basic data structure (for the parts being tested in your benchmarks), read/write load patterns, other details of server's specification (RAM, CPU, ...), your mySQL configuration (out of the box or some tweaks done?) (if out of the box, which box?), how you are testing performance to show it is the same, and so on, we can't really help you on the performance front at all.

The only general thing we can say is if your performance is being limited mainly by CPU resource then RAID is not going to make any difference because that only affects IO performance.

Thirdly: you don't show if that RAID volume is mounted anywhere as a file system or part of an LVM arrangement. Have you done this and told mySQL to store data on that array?


SQL Server bulk insert performance I’m running sql server 2012 and the installed ram on that machine is 128 GB.  The performance of the bulk insert is very low. The bulk insert of 10 million records takes about 2 hours. We have checked the cpu, disk io, memory and everything seems to be ok. I’ve just checked the wait_type and found out the CXPACKET wait_time_ms is   79346890761 and max_wait_time_ms is 2164694 and signl_wait_time_ms 2849080871. Are these numbers too high and could it be the reason for slow performance? The degree of parallelism is set to 0.

Below are some good ways to improve BULK INSERT operations :

Using TABLOCK as query hint.
Dropping Indexes during Bulk Load operation and then once it is completed then recreating them.
Changing the Recovery model of database to be BULK_LOGGED during the load operation.
If the target has Clustered Index then specifying ORDER BY clause in the bulk insert operation will increase the speed of BULK loading.
Using Trace Flag 610 at the beginning of BULK INSERT operation.

The max degree of parallelism should be configured on the server rather than the default. You can refer to my answer on how it configure it here.

Some good reference with interesting stats are given in The Data Loading Performance Guide here
. Have a Look at BATCHSIZE and ROWS_PER_BATCH as well in the guide.


Get top row in multiple dimensions? I have a table of jobs that has columns for unique id, app, env, the status of the job and the timestamp for it, along with other data not relevant to the question. Like so,

CREATE TABLE the_table (
    id       int           AUTO_INCREMENT,
    app      varchar(255),
    env      varchar(255),
    time_end datetime(3),
    status   varchar(255),
    PRIMARY KEY (id)
);

Each job belongs to an app in en environment, env, and the values for these are limited, currently around ten for each of app and env. Some example data (with changed values):

INSERT INTO the_table(app,env,time_end,status)
VALUES
    ( 'app1', 'env1', '2017-11-14 14:45:07.037', 'finished' ),
    ( 'app1', 'env2', '2017-08-01 08:30:03.951', 'finished' ),
    ( 'app1', 'env1', '2017-10-05 21:36:22.716', 'finished' ),
    ( 'app2', 'env2', '2017-05-18 11:26:58.122', 'finished' ),
    ( 'app1', 'env1', '2017-09-26 00:35:37.914', 'finished' ),
    ( 'app1', 'env3', '2017-11-05 20:27:00.762', 'finished' ),
    ( 'app1', 'env3', '2017-06-04 16:55:19.089', 'finished' ),
    ( 'app3', 'env3', '2017-10-05 04:36:00.820', 'finished' ),
    ( 'app3', 'env3', '2017-09-22 04:11:51.602', 'finished' ),
    ( 'app2', 'env1', '2017-10-09 15:36:38.642', 'finished' ),
    ( 'app1', 'env2', '2017-10-04 16:07:48.272', 'finished' ),
    ( 'app1', 'env1', '2017-09-16 15:35:22.239', 'finished' ),
    ( 'app1', 'env1', '2017-10-12 19:53:29.434', 'finished' ),
    ( 'app1', 'env1', '2017-08-23 07:07:56.983', 'finished' );

I want to get, for each combination of app and env, and status=finished, the latest record. My first solution would be something like:

xs = Query("SELECT DISTINCT app FROM the_table")
ys = Query("SELECT DISTINCT env FROM the_table")

for app in apps do{
  for env in envs do{
    r = Query("SELECT * FROM the_table WHERE status='finished' AND the_table.env=env AND the_table.env=env ORDER BY time_end desc LIMIT 1")
  }
}

This could potentially be a lot of queries though and I would like to find another solution more idiomatic to SQL. This is using MySQL 5.7, so I can't use limit in subqueries.

If you wanted only the last time_end value per (app, env), you could just use GROUP BY:
SELECT
  app,
  env,
  MAX(time_end) AS last_time_end
FROM
  the_table
WHERE
  status = 'finished'
GROUP BY
  app,
  env
;

However, you want in each case the entire row where time_end is last. That is a classic "greatest N per group" problem. One way to solve it is in two steps: first get the last values themselves, then join them back to the original table and get the corresponding complete rows. The query above, therefore, is the first step. Use it as a derived table and join it back to the_table to get the final output:
SELECT
  t.*
FROM
  the_table AS t
  INNER JOIN
  (
    SELECT
      app,
      env,
      MAX(time_end) AS last_time_end
    FROM
      the_table
    WHERE
      status = 'finished'
    GROUP BY
      app,
      env
  ) AS s ON t.app = s.app
        AND t.env = s.env
        AND t.time_end = s.last_time_end
;

Other database products support more advanced SQL syntax – specifically, window functions – that allows you to solve this category of problems without using a join, thus offering better performance. MySQL does not support window functions (yet?) but there is a way to work around that limitation using variables.
Specifically, you would use variables to rank rows within each (app, env) group based on the time_end value and then filter the rows on the ranking value:
SELECT
  derived.*
FROM
  (
    SELECT
      @rank := (app = @app AND env = @env) * @rank + 1 AS rank,
      ID,
      @app := app AS app,
      @env := env AS env,
      time_end
    FROM
      (SELECT @app := '', @env := '', @rank := 0) AS x,
      the_table
    WHERE
      status = 'finished'
    ORDER BY
      app ASC,
      env ASC,
      time_end DESC
  ) AS derived
WHERE
  derived.rank = 1
;

The query works like this:

The rows are sorted in such a way that identical (app, env) rows are arranged in groups, the last time_end row being the first row in each group.

Each row is then assigned a ranking value with the help of variables. Variables @app and @env hold the previous row's values of app and env and take part in calculating the @rank value: if the current row's app and env match the previous row's values, the @rank value is increased by 1, otherwise it is set to 1.
Thus, the first row in each group always gets the value of 1, because the previous row belongs to a different group or, in the case of the very first group, is simply non-existent (@app and @env in that case each hold the initial value, an empty string).

The results are used as a derived table and are filtered on the ranking value: only the rows with the ranking value of 1 are returned (they hold the last time_end value in each group).

My only issue with this method is that it is very MySQL-specific, which, in my opinion, is worth keeping in mind. No other RDBMS supports this syntax (unless that product is a MySQL fork, like MariaDB, for instance).
Both methods shown can be found and played with in this live demo at Rextester.


mysqld-multi with first DB as Slave and second DB as Master Yesterday, I setup mysqld-multi in my.cnf. It works fantastic, but there is still a further Problem.

The first DB on socket 1 running as Slave from a other server. The second one from socket 2 should run as Master for a further Slave. The replication for DB 1 running successful.

My question is it possible to setup the master-slave environment for every socket?
Is it basically possible to setup more than one master-slave configuration in my.cnf?

I couldn't found helpful hints in google or I doesn't have the correct search words.

I'm happy for any helpful hints.

My system is Debian 6 with mysql 5 and php.

[mysqld1] read-only # comment out only on the productive
dbmaster port = 3306
datadir = /var/lib/mysql
pid-file = /var/lib/mysql/mysqld.pid
socket = /var/lib/mysql/mysql.sock
user = mysql
log-error=/var/log/mysql1.log
innodb_data_home_dir = /var/lib/mysql
innodb_log_group_home_dir = /var/lib/mysqlinnolog
relay-log = /var/log/mysql/master-relay-bin
relay-log-index = /var/log/mysql/master-relay-bin.index
log-bin = /var/log/mysql/relaylog/master-bin

[mysqld2]
port = 3307
datadir = /var/lib/mysql-data2/mysqld2
pid-file = /var/lib/mysql-data2/mysqld2/mysql.pid
socket = /var/lib/mysql-data2/mysqld2/mysql.sock
user = mysql
log-error=/var/log/mysqld2.log
log-bin = /var/log/data2/mysql-bin

Both instance have a specific port and a separate Datadir. Is it possible to give each section in the snippet a unique ID? Can I put the Slave ID in section [mysqld1] and the master ID in [mysqld2]?

I address something like this back on May 31, 2011 : MySQL connection doesn't work anymore

Essentailly, you must make sure each mysql instance has a unique socket file, datadir, slow log, error log, and its fair share of memory.

You could look into setting up a separate mysqld service for a specific port : Running multiple instances on the same host

You should make sure db2 has a unique server_id. Furthermore, if the Slave is to be a Master also, make sure you have log-slave-updates in my.cnf for this Slave.


Switch from full to simple recovery This is a theoretical question to help my understanding. What is the process to switch from full to simple recovery model in sql server?

Is the process as follows:

Take a trans log backup before the switch to simple so I can do
point in time recovery up until the switch.
Switch to simple.
Possibly run DBCC SHRINKFILE with TRUNCATE ONLY to free up some disk
space.
Possibly alter the database to decrease the trans log file size.
Preform a full backup.

I guess my main questions about point 1. If I don't take a trans log backup will SIMPLE discard any data in the final trans log or does switching to SIMPLE backup the trans log (i don't think it does).

Also I believe that it is best practice to perform a full backup post switch to SIMPLE so new transactions can be pushed from the trans logs to the data files at check point.

Once you switch to Simple the Trans Log will no longer maintain a log of operations since your last (database files) backup - at the point of the switch all transactions will have been committed to the main database files so will not be discarded - they are already effective in the database files.

Your point 1 is not (in my opinion) worth doing - once switched to SIMPLE, you should backup the Database files - which will contain all data.

Remember that the FULL recovery model only exists so that you don't have to backup the database files every time (so in FULL recovery model your recovery process is: Restore Main Database files, then Restore Transaction Log Backups). With the SIMPLE model the recovery process is only Restore Main Database files.

What I'm trying to say is: As quickly as possible (depending on performance and the type of transaction) the transactions that can be found in the Transaction Log are Committed to the Main Data files (mdf, ndf) which are stored in the .bak file when Backed up.
So SIMPLE model allows you to restore to the point in time when you took a .bak
FULL model allows you to restore to the point in time when you last took a .trn (transaction log backup) - in the case of multiple trn backups that would be a restore of Bak + trn + trn + trn (if you had taken 3 trn backups since your last bak).

The only purpose of the differing recovery models is backup strategy and time saved by not taking a bak every time.

Now I've typed it out, I see why you've asked the question!

Although not perfect English, I think this gives a relatively good breakdown of the different models and why you'd use them: http://www.todo-backup.com/backup-resource/sql-backup-software/three-sql-server-recovery-models.htm

This is how I would do your list:

Switch to simple.
Perform full backup of data files (.bak)
Possibly run DBCC SHRINKFILE with TRUNCATE ONLY to free up some disk space.
Possibly alter the database to decrease the trans log file size.
Perform a full backup.
Switch to full (I assume you're switching back!??!)

Notes: If you're worried, then do a Backup before 1 as well. I'll leave 3 and 4 as they are, because they're a whole different question! (http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/)


Output parameter in stored procedure Dim rs4 As Recordset = B1Connections.diCompany.GetBusinessObject(BoObjectTypes.BoRecordset)
Dim qry4 As String = "SELECT Name FROM [@CCS_LOGINDET]"
rs4 = obgn.Return_RecordSet(qry4)
Dim constr As String = "Data Source=" + B1Connections.theAppl.Company.ServerName + ";initial catalog=" + B1Connections.theAppl.Company.DatabaseName + ";User Id=" + B1Connections.diCompany.DbUserName + ";Password=" + CType(rs4.Fields.Item(0).Value, String)
Dim con As SqlConnection = New SqlConnection(constr)
con.Open()
Dim cmd As SqlCommand = New SqlCommand("CCS_SP_PYRL_BONUS_CALCULATION_AGAPPE", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@EMPID", txt_empid.Value.Trim)
cmd.Parameters.AddWithValue("@DATE", txt_cdate.Value.Trim)
Dim outParam As New Data.SqlClient.SqlParameter("@NETBONUS", AMOUNT)
outParam.Direction = Data.ParameterDirection.Output
cmd.Parameters.Add(outParam)
bonusamount = cmd.ExecuteNonQuery()
txt_bonus.Value = bonusamount

This code always returns -1.00.
The procedure works correctly in backend and it returns (numeric(19,6)).  I think the problem is with my front end...  Can anyone help me?

You need to specify the data type of the parameters.


Best practice for entry position indicators My problem is, that I have no idea how I could cleanly make an index (not a real Table index, just an indicator) for each table row representing the entries position while each position must be unique but inserting a new entry may not cause everything to change its positions.

Here is an example:

Let's assume we have a table products, where each product has clearly its product-id as primary key. Now I am making a catalog of all products and another categorized by anything. Now I certainly could take the product-id or the product name to sort the lists but I want some of them to appear earlier and some not so I am going to give them indexes.

My first solution would be:

Each product gets its own position index like product p-2151 => position 1, p-553 => position 2 etc. No problem so far until I insert a new product between p-2151 and p-553. Now I had to reposition all following products what is no good idea, because I would have to refresh the complete product-cache of my application to recognize it.So this is definitely not the way to go.

Normally I have no problems with such almost trivial things but somehow I just don't get it here.

What would be the cleanest way to go here?

No matter what solution you choose, you're going to have to "refresh your product cache" anyway, since cached information won't reflect your new product either.  You should let the DBMS do its job and just query against the base table.

In terms of managing arbitrary (manually imposed) sequences, one solution to consider which allows for easy inserts with only periodic recalculation of the whole sequence is to use double instead of int for your sequence number.

When you insert a record between two existing records, set the sequence number of the new record to half way between the existing records.  This can go on for a very long time before you have to break down and recalculate the whole sequence.

You could have a nightly/weekly/monthly process that recalculates all of your sequence numbers so that they use natural numbers again.


A possible infinite recompile was detected for SQLHANDLE I have been finding strange error messages on the sql error log:

Bocss: same deadlock taking place every hour – needs investigating

Also lots of recompiles are listed in the error log for other SPIDs as per following examples:

  09/04/2015 14:30:10,spid64,Unknown,A possible infinite recompile was
  detected for SQLHANDLE
  0x0200000059631A288882589E0C54B76404CAE1B97E08D3680000000000000000000000000000000000000000
  PlanHandle
  0x0600040059631A2860A62B654100000001000000000000000000000000000000000000000000000000000000
  starting offset 1038 ending offset 2600.  The last recompile
  reason was 2. 09/04/2015 14:30:10,spid150,Unknown,A possible infinite
  recompile was detected for SQLHANDLE
  0x02000000EF886F018C4E0B163812B8B20150FE8FC7E6A06A0000000000000000000000000000000000000000
  PlanHandle
  0x06000400EF886F01901A816E0600000001000000000000000000000000000000000000000000000000000000
  starting offset 998 ending offset 2520.  The last recompile reason
  was 2. 09/04/2015 14:30:09,spid67,Unknown,A possible infinite
  recompile was detected for SQLHANDLE
  0x0200000057C4C632D9052275CFF2B683B80F29501EE91D730000000000000000000000000000000000000000
  PlanHandle
  0x0600040057C4C63200EAC2BE3000000001000000000000000000000000000000000000000000000000000000
  starting offset 1064 ending offset 2652.  The last recompile
  reason was 2. 09/04/2015 14:30:09,spid163,Unknown,A possible infinite
  recompile was detected for SQLHANDLE
  0x02000000E7C7BF0E5D70DE55759C7842860272AD474D69AB0000000000000000000000000000000000000000
  PlanHandle
  0x06000400E7C7BF0EF0EB68A52C00000001000000000000000000000000000000000000000000000000000000
  starting offset 1028 ending offset 2580.  The last recompile
  reason was 2.

what could have caused this?

Looks like I don't have the plans in cache anymore.

following the advice of this post
http://www.sqlservercentral.com/Forums/Topic1479420-146-1.aspx

  then as a safety measure disabled the full text catalogs, this made
  no difference, so then I fully rolled back the changes (dropped the
  new objects etc). This also made no difference, in the end the only
  thing that seemed to stop it was a restart of the SQL instances, this
  immediately resolved the issue.

this sorted me out as well, however, I am still to find what caused this mess at the first place?

According to Infinite recompile message in the errorlog on the SQL Programmability & API Development Team Blog, this message is triggered when a statement in a batch recompiles 100 times in a row.
This message does not necessarily mean there is a problem; it exists to help troubleshoot statements that might legitimately be recompiling that often (for example, due to rapid changes in statistics), as well as real infinite compilation loops (which would be rare in the extreme).
You should start by identifying the triggering statement from the information provided, and evaluate it in the context of the numeric code giving the reason for the recompilations. There is a table of these codes and their meanings in several places in Books Online, including under the SP:Recompile Event Class.
There is more information available in Plan Caching and Recompilation in SQL Server 2012.


MongoDB Keys and casting between types This is more of a theoretical question:

Let's say I have a collection of documents, my_collection:

{
    "_id": ObjectId("52c679ac7a23e6225b62c51f"),
    "date": ISODate("2014-01-06T03:48:30.737Z")
}
{
    "_id" : ObjectId("52c679b63cdecc6508c54fe5"),
    "date": "2014-01-06T03:48:30.737Z"
}
{
    "_id" : ObjectId("52ca15677a23e6d3354fb7b4"),
    "date": "2014-01-03 19:49:48"
}

and I then create an index

> db.my_collection.ensureIndex({date: 1})

Does Mongo automagically figure out it should be a date and then do a cast to date before it hashes for the index? Or does it treat the field as a string serialisation or something else?

What sort of performance impact would I get (if any) not having all the fields as Date BSON objects, or is there minimal to no impact?

EDIT: I think I failed to clearly communicate another part of the question, but your answer might have already addressed it. Dates stored as string will not be included in results where the query uses a Date()/IsoDate() and vice versa, right?

Since a query that would utilize the "date" index will implicitly contain a criteria for that "date" field (value(s) of certain type(s)), Mongo will decide what index/part of the index to use, making the fact of having multiple types of values in the same field moot (as far as indexing is concerned).

Here's an example:

I've added the following data to my Mongo 2.4.4 test db:

{ "_id" : ObjectId("52c679ac7a23e6225b62c51f"), "date" : ISODate("2014-01-06T03:48:30.737Z") }
{ "_id" : ObjectId("52c679b63cdecc6508c54fe5"), "date" : "2014-01-06T03:48:30.737Z" }
{ "_id" : ObjectId("52ca15677a23e6d3354fb7b4"), "date" : "2014-01-03 19:49:48" }
{ "_id" : ObjectId("42c679ac7a23e6225b62c51f"), "date" : ISODate("2014-02-06T03:48:30.737Z") }

Before index creation, any query on the date field (be it with a ISODate or a string criteria) will search through all the 4 documents, this can be confirmed by using explain():

> db.t1.find({date:ISODate("2014-02-06T03:48:30.737Z")}).explain();
{
        "cursor" : "BasicCursor",
        "isMultiKey" : false,
        "n" : 1,
        "nscannedObjects" : 4,
        "nscanned" : 4,
        "nscannedObjectsAllPlans" : 4,
        "nscannedAllPlans" : 4,
        "scanAndOrder" : false,
        "indexOnly" : false,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "millis" : 0,
        "indexBounds" : {

        },
        "server" : "P09104:27017"
}

> db.t1.find({date:"2014-01-03 19:49:48"}).explain();
{
        "cursor" : "BasicCursor",
        "isMultiKey" : false,
        "n" : 1,
        "nscannedObjects" : 4,
        "nscanned" : 4,
        "nscannedObjectsAllPlans" : 4,
        "nscannedAllPlans" : 4,
        "scanAndOrder" : false,
        "indexOnly" : false,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "millis" : 0,
        "indexBounds" : {

        },
        "server" : "P09104:27017"
}

Once I create the index via ensureIndex("date"), doing either a query of type date:"some string", date:ISODAte(...) or date in some range of ISODate values will only look through the pertinent values (2 for each of these queries):

> db.t1.find({date: {$gte: ISODate("2014-01-05T03:48:30.737Z"), $lt: ISODate("2014-02-07T03:48:30.737Z")}}).explain();
{
        "cursor" : "BtreeCursor date_1",
        "isMultiKey" : false,
        "n" : 2,
        "nscannedObjects" : 2,
        "nscanned" : 2,
        "nscannedObjectsAllPlans" : 2,
        "nscannedAllPlans" : 2,
        "scanAndOrder" : false,
        "indexOnly" : false,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "millis" : 0,
        "indexBounds" : {
                "date" : [
                        [
                                ISODate("2014-01-05T03:48:30.737Z"),
                                ISODate("2014-02-07T03:48:30.737Z")
                        ]
                ]
        },
        "server" : "P09104:27017"
}

This is made more clear if we add even a 3rd type of value for date (numerical) and create something like this:

{ "_id" : ObjectId("52c679ac7a23e6225b62c51f"), "date" : ISODate("2014-01-06T03:48:30.737Z") }
{ "_id" : ObjectId("52c679b63cdecc6508c54fe5"), "date" : "2014-01-06T03:48:30.737Z" }
{ "_id" : ObjectId("52ca15677a23e6d3354fb7b4"), "date" : "2014-01-03 19:49:48" }
{ "_id" : ObjectId("42c679ac7a23e6225b62c51f"), "date" : ISODate("2014-02-06T03:48:30.737Z") }
{ "_id" : ObjectId("52ca15677a23e6d2354fb7b4"), "date" : 100 }
{ "_id" : ObjectId("52ca15677a23e6d2254fb7b4"), "date" : 400 }

Doing an $or seach for (let's say) ISODate and numerical values (exact match) and then doing an explain on that search will show that only 2 documents are scanned (out of the 6 indexed), showing that having mixed types of values for a field does not lower the efficiency of the index:

> db.t1.find( { $or: [ {date: ISODate("2014-01-06T03:48:30.737Z")}, {date: 100} ] } ).explain();
{
        "clauses" : [
                {
                        "cursor" : "BtreeCursor date_1",
                        "isMultiKey" : false,
                        "n" : 1,
                        "nscannedObjects" : 1,
                        "nscanned" : 1,
                        "nscannedObjectsAllPlans" : 1,
                        "nscannedAllPlans" : 1,
                        "scanAndOrder" : false,
                        "indexOnly" : false,
                        "nYields" : 0,
                        "nChunkSkips" : 0,
                        "millis" : 0,
                        "indexBounds" : {
                                "date" : [
                                        [
                                                ISODate("2014-01-06T03:48:30.737Z"),
                                                ISODate("2014-01-06T03:48:30.737Z")
                                        ]
                                ]
                        }
                },
                {
                        "cursor" : "BtreeCursor date_1",
                        "isMultiKey" : false,
                        "n" : 1,
                        "nscannedObjects" : 1,
                        "nscanned" : 1,
                        "nscannedObjectsAllPlans" : 1,
                        "nscannedAllPlans" : 1,
                        "scanAndOrder" : false,
                        "indexOnly" : false,
                        "nYields" : 0,
                        "nChunkSkips" : 0,
                        "millis" : 0,
                        "indexBounds" : {
                                "date" : [
                                        [
                                                100,
                                                100
                                        ]
                                ]
                        }
                }
        ],
        "n" : 2,
        "nscannedObjects" : 2,
        "nscanned" : 2,
        "nscannedObjectsAllPlans" : 2,
        "nscannedAllPlans" : 2,
        "millis" : 0,
        "server" : "P09104:27017"
}

Doing a similar $or search with ranges (for both ISODate and numerical) will show that 4 results are obtained after only scanning 4 documents (the string values to not influence the utilization of the index):

> db.t1.find( { $or: [ {date: {$gte: ISODate("2014-01-05T03:48:30.737Z"), $lt: ISODate("2014-02-07T03:48:30.737Z")}}, {date: {$gte: 50, $lt: 500}} ] } ).explain();
{
        "clauses" : [
                {
                        "cursor" : "BtreeCursor date_1",
                        "isMultiKey" : false,
                        "n" : 2,
                        "nscannedObjects" : 2,
                        "nscanned" : 2,
                        "nscannedObjectsAllPlans" : 2,
                        "nscannedAllPlans" : 2,
                        "scanAndOrder" : false,
                        "indexOnly" : false,
                        "nYields" : 0,
                        "nChunkSkips" : 0,
                        "millis" : 0,
                        "indexBounds" : {
                                "date" : [
                                        [
                                                ISODate("2014-01-05T03:48:30.737Z"),
                                                ISODate("2014-02-07T03:48:30.737Z")
                                        ]
                                ]
                        }
                },
                {
                        "cursor" : "BtreeCursor date_1",
                        "isMultiKey" : false,
                        "n" : 2,
                        "nscannedObjects" : 2,
                        "nscanned" : 2,
                        "nscannedObjectsAllPlans" : 2,
                        "nscannedAllPlans" : 2,
                        "scanAndOrder" : false,
                        "indexOnly" : false,
                        "nYields" : 0,
                        "nChunkSkips" : 0,
                        "millis" : 0,
                        "indexBounds" : {
                                "date" : [
                                        [
                                                50,
                                                500
                                        ]
                                ]
                        }
                }
        ],
        "n" : 4,
        "nscannedObjects" : 4,
        "nscanned" : 4,
        "nscannedObjectsAllPlans" : 4,
        "nscannedAllPlans" : 4,
        "millis" : 0,
        "server" : "P09104:27017"
}


Managing multiple SQL Server databases My company has many branch offices and each branch has a DB server. Some have SQL Server 2005 and some others have SQL Server 2008. The schema is the same for all these servers.

Whenever I want to do DDL like adding column, etc., I do it either by connecting to each server one by one, or by running the DDL script with OSQL through a batch file.

Is there a built in tool to do DDL at once on several servers? How about third party tools? (preferably free ones)

One approach:

In Management Studio 2008 R2, you can add your servers into groups called Registered Servers. I have groups for prod, dev, Prod-SQL2000 etc. You start this by going View - Registered Servers, and then adding them one by one (you can share with colleagues by exporting them)

This will then allow you to click on one of your groups (eg Prod), and selecting New Query..... Management Studio will then connect to all the SQL Servers in that group, and you can issue a query across all of them in that group. I personally do read only operations, as changing a number of servers at the same time is more risky.


Joining two tables with unrelated where clauses I have two tables: (EMPAssignments) and (EMPloyees). Both have an employee id column.

EMPAssignments has columns for employeeid, assignment, and ftepercentage:

employeeid  assignment  ftepercentage
emp001      sales       0.5
emp001      shipping    0.3
emp001      marketing   0.2
emp002      sales       0.2
emp002      shipping    0.7
emp002      marketing   0.1
emp003      sales       0.5
emp003      shipping    0.5
emp003      marketing   0.5

EMPloyees has columns for employee id and enabled

employeeid  enabled
emp001      1
emp002      1
emp003      1
emp004      0
emp005      1

I want my query to return the assignment with the highest FTEpercentage or a blank assignment if they do not exist in the EMPAssignments table.  I should have rows for employees 1,2,3,and 5, not 4.

In the case of a tie, I don't care which assignment is returned as long as it is only one, but there should always be a row for every active Employeeid.

I really am not much with SQL but I have searched about and tried various things.

I can use this to get the highest ftepercentage for each employeeid in the EMPAssignments table.

SELECT "employeeid", MAX("ftepercentage")
AS top_assignment
from EMPAssignments
group BY "employeeid"

And I can use:

SELECT Employeeid
FROM EMPloyees
WHERE enabled !=0

to return the enabled employees.

But what I can't figure out is how to join the two because emp004 and emp005 don't exist in EMPAssignments.
My join attempts either return all the assignments for all the active employeeids, or the top ftepercentage for only those employeeids in the EMPAssignments table.

Can anyone point me in the right direction?

Thanks in advance,

Bill

LEFT JOIN one subquery to get the maximum of FTE per employee and another to get the maximum (or minimum, ...) assignment for each employee and FTE.

SELECT e1.*,
       a4.*
       FROM employee e1
       LEFT JOIN (SELECT a1.employeeid,
                         max(a1.ftepercentage) ftepercentage
                         FROM empassingments a1
                         GROUP BY (a1.employeeid)) a2
                 ON a2.employeeid = e1.employeeid
       LEFT JOIN (SELECT a3.employeeid,
                         a3.ftepercentage,
                         max(a3.assignment) assignment
                         FROM empassignments a3
                         GROUP BY a3.employeeid,
                                  a3.ftepercentage) a4
                 ON a4.employeeid = e1.employeeid
                    AND a4.ftepercentage = a2.ftepercentage
       WHERE e1.enabled = 1;


Need to Send a formatted HTML Email via Database Mail in Sql Server 2008 R2 My requirement is to create the automated email as ( A formatted HTML Email via Database Mail) The basic requirement has been completed where when the Job is triggered it inserts the data into a Table and that information has been scheduled to be sent as an Email,but now the client has requested in this format.

Current Format that I have done

 PO Date:2014-11-26 PO ID:PO1 SAP Ref:0001106102 GRN:1067 DealerID:045 Dealer Name:ABC(Pvt)Ltd. 2 Status:New

Format Requested By Client in a table like format

PO Date |PO ID| SAP Ref| GRN|DealerID|Dealer Name|Status

This is the SQL Query that I use to Insert the Data in to the Email_Table(_ERROR_MAIL) and this needs to be coveted according to the requirement

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ImpCancelledGRN')
BEGIN
    DROP PROCEDURE [dbo].[ImpCancelledGRN]
END
GO

CREATE PROCEDURE [dbo].[ImpCancelledGRN]
WITH ENCRYPTION
AS

SET NOCOUNT ON
BEGIN
BEGIN TRY
    -- Begin transaction
    BEGIN TRAN

            INSERT INTO _ERROR_MAIL(Recipients, Subject, CreationDate, IsNew, LastModifiedBy, Body)
            SELECT 'noreply-Email@Adress.com', 'SAP CANCELLED GRN', GETDATE(), 1, 'sapws',
                'PO Date:' + CONVERT(VARCHAR(10),P.Date,120) +
                ' PO ID:' + P.ID +
                ' SAP Ref:' + P.ID2 +
                ' GRN:' + G.ID +
                ' Dealer ID:' + D.ID +
                ' Dealer Name:' + D.Name +
                ' Status:' + CASE WHEN G.SubmittedDate IS NULL THEN 'New' ELSE 'Dealer Submitted' END
            FROM I_CancelledGRN I
                INNER JOIN TxnGRN G ON G.ID = I.ID
                INNER JOIN Distributor D ON D.UID = G.DistributorUID
                INNER JOIN POTxn P ON P.SiteUID = G.POTxn_SiteUID AND P.UID = G.POTxnUID
            WHERE IsCancelled IS NULL;

            UPDATE TxnGRN
            SET ExpiryDate = GETDATE()
                , SAPCancellationDate = I.Date
                , SAPCancelledBy = 'SAP'
                , IsCancelled = 1
            FROM I_CancelledGRN I
                INNER JOIN TxnGRN G ON G.ID = I.ID
            WHERE IsCancelled IS NULL;

    -- Commit transaction
    COMMIT TRAN

END TRY
BEGIN CATCH
    -- Get error message, severity and satus information
    DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT, @ErrorState INT;
    SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(),    @ErrorState = ERROR_STATE();
    -- Rollback transaction
    ROLLBACK TRAN;
    -- Log error message details
    INSERT INTO _ERROR_LOG(Module, SubModule, Text, Date)
    VALUES('SAP', '[ImpCancelledGRN]', @ErrorMessage, GETDATE());
    -- Raise error
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH
RETURN 0;
END
SET NOCOUNT OFF
GO

Here how can you create HTML body part of your mail.

Step 1:

 DECLARE @Body NVARCHAR(MAX),
    @TableHead VARCHAR(1000),
    @TableTail VARCHAR(1000)

SET @TableTail = '
' ;
SET @TableHead = '' + '' + '' + '' + 'Report generated on : '
    + CONVERT(VARCHAR(50), GETDATE(), 106)
    + '

'
    + '
 PO Date	'
    + 'PO ID	'
    + 'SAP Ref	'
    + 'GRN	'
    + 'DealerID	'
    + 'Dealer Name	'
    + 'Status
' ;

SET @Body = ( SELECT    td = CONVERT(VARCHAR(10), P.Date, 120), '',
                        td = P.ID, '',
                        td = P.ID2, '',
                        td = G.ID, '',
                        td = D.ID,'',
                        td = D.Name,'',
                        td = CASE WHEN G.SubmittedDate IS NULL THEN 'New'
                                  ELSE 'Dealer Submitted'
                             END, ''
              FROM      I_CancelledGRN I
                        INNER JOIN TxnGRN G ON G.ID = I.ID
                        INNER JOIN Distributor D ON D.UID = G.DistributorUID
                        INNER JOIN POTxn P ON P.SiteUID = G.POTxn_SiteUID
                                              AND P.UID = G.POTxnUID
              WHERE IsCancelled IS NULL
            FOR   XML RAW('tr'),
                  ELEMENTS
            )

SELECT  @Body = @TableHead + ISNULL(@Body, '') + @TableTail

Step 2: create database email profile

Step 3: Send Email

EXEC sp_send_dbmail
  @profile_name='DatabaseEmailProfile',
  @copy_recipients ='aasc@stackexchange.com',
  @recipients='aa.sc@outlook.com',
  @subject='Query Result',
  @body=@Body ,
  @body_format = 'HTML' ;


Why mysql use only one index for table? Index merge is used very seldom and often is not efficient.
But search engines like Elastic, Sphinx can use few indexes efficiently.

Either "index merge" is inefficient that most other techniques because of what it has to do:

Gather PRIMARY KEYs based on one index.
Gather PRIMARY KEYs based on the other index.
Sort each list
Merge the lists
Look up the desired rows based on the PKs
Then move on to whatever else is going on.

Furthermore, "Index merge intersect" is almost always less efficient than a composite index that combines the columns being searched on.  See my discussion here:  http://mysql.rjweb.org/doc.php/index1


ORA-01017: invalid username/password; logon denied I've come across the abnormal situation, and can't understand what happens.

I am connecting databases using PL/SQL Developer 8.0.4.1514(I've also tried v7,but same) . Sometimes(When I am trying to log on, or when I open another session, or when I am trying to run query in previously opened session) this error message appears

Sometimes after 3 tries error resolves(using the same password on each try), sometimes it needs one try to resolve...

What may be the reason?

Thank you ...

PL/SQL Developer will remember your most recent windows. If you close the application without closing your windows, it will attempt to authenticate to restart your old sessions. This can become a nuisance if you work in several environments. Try making it habit to close everything before closing the application. You may also try upgrading the application as user21081 suggested.


How to create an object and then alter it I'm using sql server 2012:

I would like to script out creation of an object(if it doesn't exist first), and then alter it.

I'm trying to avoid the usual drop/create as it would impact stats.  It also has to do with how we're scripting out changes to the DB.

What I have:

Go
If object_id(N'My_Function', N'FN') IS NULL
Begin
Create Function My_Function
(
    @SomeParameter int
)
Returns int
As
Begin
    -- Intentionally empty
End

 End

Go
-- A header/comments
Alter Function My_Function
(
@SomeParameter int
)
Returns int
As
Begin
Declare @returnValue int
    -- A bunch of sql code
Return @returnValue
End
Go

The problem I'm having is "Create" must be first in the script(or first after a "GO"), but I can't put a "GO" inside an "If" conditional.  Is there a way to conditionally create something if it doesn't exist, and then alter it later?

You can't put CREATE FUNCTION inside IF logic since it needs to be in its own batch.

I would do it this way. If the function doesn't exist yet, create an empty stub. Now you can always safely issue an alter.

IF OBJECT_ID('dbo.function_name') IS NULL
BEGIN
  DECLARE @sql NVARCHAR(MAX) = N'CREATE FUNCTION dbo.function_name()
    RETURNS INT
    AS
    BEGIN
      RETURN (1);
    END';

  EXEC sp_executesql @sql;
END

ALTER FUNCTION dbo.function_name
(
  ... more complex logic here ...

Unfortunately I know of no easy way to get SSMS to script it out this way for you, and even doing that through SMO or other methods is going to be a pain IMHO. Simply because the function has to be of the same type (IF, TVF, MSTVF), so your simple stub can't always be the same.

Wouldn't it be great if SQL Server supported CREATE OR REPLACE?


What are the things to looks for or capture when there are blockages in Sybase Currently i look for SPID which are blocking other spid's,enable traceon(3604) to capture SQLTEXT, Showplan.

If blockages keep re-occuring over time especially when certain query is executed, what are the next steps forward?

Thanks

when i posted this question, i was quite not aware of what to do next after you have captured information related to blockages like, SPID, Showplan and SQLText.

answering this question, just in case someone comes across same doubt.

Once you have found the details during blockages, next steps is to find out what caused it in first place.

sp_sysmon is tool you need to look into.
sp_sysmon provides a detailed report of the activity on your system, and
provides you with a number of ways to specify the type of information you
want to receive, the time interval during which data for the report will be
collected, and other options that determine how the report is generated.

The sp_sysmon report consists of a number of separate sections. You can
run sp_sysmon to generate the complete report or only one of the
individual sections. You can also specify a time interval over which the
report should run or execute the stored procedure yourself at the beginning
and end of the desired time period.


Clearing SQL Server 2005 event log I need to delete the SQL Server 2005 logs, but can't figure out how to do it. I'm talking about the logs that are found in Management -> SQL Server Logs in SSMS object explorer. I can't seem to detete them from there, so I assume there is another way or a command to do it.

Also, I noticed that at least some log entries can be found in Windows Event Log (the ones about failed logins, for example). Will those get deleted too if I find a way to clear the SQL Server logs? Or should I clear the event log separately?

I looked for an answer but people seem to ask mostly about clearing transaction logs which is not what I need.

Log files in question are named ERRORLOG, ERRORLOG1 etc. and are usually stored in LOG folder somewhere inside MSSQL installation directory. In the beginning of the log there is a message like this:

  Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL
  Server\MSSQL.1\MSSQL\LOG\ERRORLOG'

It can help to determine the exact path.

If the need to delete these logs is caused by a security concern, it's good to execute sp_cycle_errorlog proc to make log cycle. This way the ERRORLOG file will be empty and all the events will be in ERRORLOG1, ERRROLOG2 etc which can be deleted at any time.


SQL server Principal and Mirror logins and permissions Issue In a mirroring setup where server A is my principal and server B is my mirror, I had some issues related to logins and permissions at the time of fail-over.

During fail-over Server B became my principal and Server A my mirror but I was facing login issues on server B and I had to recreate the logins on server B with the same permissions they had on server A.

What is the correct way to handle this scenario? Do I have to create the logins on server B every time I create a login on  server A or is there a better way to handle this?

Are there any scripts available to compare and synchronize logins?

See the article Transferring Logins to a Database Mirror by Robert Davis, where he writes:

There are two main issues with transferring SQL Logins. Foremost is the SID (security identifier) for the login. The SID must be transferred with the login or it won’t automatically map to the database user. Secondly, the password is not available in clear text, so you can’t just read it from the database.
This script addresses both of these issues. It creates the SQL login with the same SID and it uses the stored hash of the password to recreate the password using the HASHED property to indicate that we are supplying the password already hashed.

There is a link at the bottom to download the script dba_CopyLogins.sql. This will sync your logins between principal and mirror.


Is it good practice to create tables dynamically? Let's say that I'm making an IRC bot, and in addition to all the other stuff that should be stored in the DB, I want to log all the messages in each channel in which the bot is present.

I already have a channels table with columns id, channel_name, and some other info.

For logging each message, what seems the most sense to me is to create a messages_channelname table for each channel, and in there store the message along with whatever other information is associated with it.

That elicits a problem, though - the name of the table is now dissociated from the channel's id (although I could alleviate this by making the table name messages_channelid).

I feel like the best practice here, rather than what makes the most sense, would be to have a master messages table that contains the messages from all channels, with columns channel_id, id and message, where id would be the index of the message per channel. But then I'd have (unfounded) concerns about table size and possibly search speed.

What's the best way to go about this?

There isn't any definitive answer for this problem.

You've correctly identified some of the possible tradeoffs (single table=simpler/possibly slower/more contention?, multiple tables=more complicated/faster, maybe?/harder to use in other contexts?).

The general advice is to start with the more straight-forward single-table solution, then break them out if testing shows problems.

Most of the time you're going to get good performance on a single table as long as you have decent hardware and proper indexing design.

Depending on the specific platform, you might have other technical solutions like filtered indexed views and stuff like that.


MongoDB - Unable to change mongodb storage engine to WiredTiger I have installed MongoDB in Ubuntu 14.04 but I am unable to change its storage engine to WiredTiger.

I have added the necessary changes to the /etc/mongod.conf file which is namely as the following-:

storage:
  dbPath: /var/lib/mongodb
  journal:
    enabled: true
  engine: wiredTiger

I have started the mongod process using the following command-:

mongod - f /etc/mongod.conf

The server starts up but I still get the warning when I connect to my shell-:

2017-01-10T15:36:54.866+0530 I STORAGE  [initandlisten] ** WARNING: Using the XFS filesystem is strongly recommended with the WiredTiger storage engine

Am I missing something over here ? The changes seem to have no effect.

Based on the warning message included in your description, you are already using the WiredTiger storage engine but your dbPath is using the ext file system (in theory ext4, but the Linux filesystem magic number doesn't distinguish ext2/ext3/ext4). Note: WiredTiger is the default storage engine for MongoDB 3.2+, so there is no need to specify it in your configuration file.

This warning was added for the MongoDB 3.4 production release per SERVER-22606: Startup warning if ext4 is used with WiredTiger. There are some known performance issues with ext4 (in particular, periodic stalls) so this warning is meant to proactively ensure administrators are alerted to potentially problematic configurations. Filesystem and other production caveats are also included in the Production Notes in the MongoDB manual.

To suppress this warning you could either:

Host your dbPath on a supported filesystem other than ext4 (XFS is recommended)
Start your mongo shell with the --quiet parameter (which might suppress other interesting startup info / warnings)

You can certainly choose to ignore startup warningd (particularly for a development environment), but should consider using XFS with WiredTiger if this is a production environment.

You may want to watch/upvote SERVER-19790: Provide mechanism to clear/acknowledge startup warnings, which could allow startup warnings to be ack'd so they do not appear on every new mongo session.


Is there a way to copy/initialize values in a set What I want to do (I know this does not work) is:

update stats
set BandwidthLastMonth = BandwidthThisMonth,
    BandwidthThisMonth=0
where myPk = 3

Is there a way to do this in a select statement? Or do I need to write a stored procedure and hit each record twice (I sure hope not).

My solution needs to work for Sql Azure and Sql Server 2008.

Until we see the actual code, we can only speculate but for what is worth, I'll do a guess.

Assuming that your code involves only a table and its columns (no @variables involved) and is something like:

UPDATE
    tablename
SET
    column_a = column_b,
    column_b = 0
WHERE
    (condition) ;

It is valid SQL and 100% correct. Correct, meaning that after the update column_a will have the values that column_b had before the update and column_b will have 0, for all rows that pass the (condition).

SQL-Server follows this behaviour strictly and there is no reason to assume that it will deviate from the standard in a future release.

SQL works in sets and the updates are done "simultaneously" on the whole row. (they may not be done simultaneously but the result is the same as if they were.) You could change the code to this and the result will be (guaranteed) the same:

...
SET
    column_b = 0,
    column_a = column_b
...

In fact, the following is valid and equivalent SQL as well (although not yet implemented in SQL-Server);

UPDATE
    tablename
SET
    (column_a, column_b) = (column_b, 0)
WHERE
    (condition) ;

I haven't tested Azure but if it deviated from this basic SQL behaviour, it would better be documented somewhere.


Mysql Master-Master Replication Error Configured Master-Master Replication
But both servers slave shows the below error !!

Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from
binary log: 'Could not find first log file name in binary log index
file'

How to fix this

According to this

This error occurs when the slave server required binary log for replication no longer exists on the master database server. In one of the scenarios for this, your slave server is stopped for some reason for a few hours/days and when you resume replication on the slave it fails with above error.

When you investigate you will find that the master server is no longer requesting binary logs which the slave server needs to pull in order to synchronize data. Possible reasons for this include the master server expired binary logs via system variable expire_logs_days – or someone manually deleted binary logs from master via PURGE BINARY LOGS command or via ‘rm -f’ command or may be you have some cronjob which archives older binary logs to claim disk space, etc. So, make sure you always have the required binary logs exists on the master server and you can update your procedures to keep binary logs that the slave server requires by monitoring the “Relay_master_log_file” variable from SHOW SLAVE STATUS output. Moreover, if you have set expire_log_days in my.cnf old binlogs expire automatically and are removed. This means when MySQL opens a new binlog file, it checks the older binlogs, and purges any that are older than the value of expire_logs_days (in days). Percona Server added a feature to expire logs based on total number of files used instead of the age of the binlog files. So in that configuration, if you get a spike of traffic, it could cause binlogs to disappear sooner than you expect. For more information check Restricting the number of binlog files.

In order to resolve this problem, the only clean solution I can think of is to re-create the slave server from a master server backup or from other slave in replication topology.


Using a NULL value to signify that the value does not mattter. Not that it isn't known Say I have a table with computers where one column is a boolean "fan". If true the computer has a fan, if false it does not. It can never be null.
Now I have another table with a list of recommendations for improvements to computers.
I want some recommendations to apply to all computers which have a fan. For this I have a column "fan" which when true will only apply to computers with a fan. When false will only apply to computers without a fan.
Is it good database design to have the column set to null when the recommendation should not care if the computer has a fan or not? As in the recommendation should apply whether the computer has a fan or not.
The purpose of null values is that the column is not known. Which is not the case here. It's not that I don't know if the recommendation is for a computer with a fan or without a fan, it's that I specifically don't care either way. This makes me think that it's not a good idea and is just changing the meaning of null.
On the other hand I think it makes sense that if the "fan" filter on the recommendation isn't defined it means that it doesn't need to filter based on that.
The other way to define the recommendation is have a applies_to_fanned_computers and applies_to_not_fanned_computers columns. If both are true then it applies to either, if only one is true then it applies to one of them.
Which option is best? Am I right that the first option is a bastardization of the purpose of null values in a database?

My approach for this is to think of what the fields are, which is storing information on computers. There is no case ever where having a fan would result in NULL, just a yes or no. In your case of using it as a major category for computers, I would avoid that, and instead use your queries and stored procedures to consider the filters. In the stored procedure you can have the WHERE clause look for the different combinations;
Only Computers with fans -- SELECT [recommendations] FROM table WHERE [has_fan]=1
Only computers without fans --SELECT [recommendations] FROM table WHERE [has_fan]=0
All computers\don't care -- don't use a WHERE clause;SELECT [recommendations] FROM table


Connect to remote EC2 Postgresql database I want to setup a connection between a webserver and a PostgreSQL 9.3 server. Both are hosted on Amazon EC2 servers and are part of the same security group. I can't get the connection working. The same goes for a direct connection from my desktop to Postgres, something we have done with another Postgres server, the one which is going to be replaced by this one.

This is what I did up til now:

Checked Amazon Security Groups: Server is part of the Default group, and the Default group accepts traffic from my local IP for all ports: this works for other servers and I can connect to the server using SSH.
IPTables firewall service is stopped on the server.
PostgreSQL is running: I can setup an SSH tunnel and then connect using PGadmin, open databases etc. So the server is running, and the login is correct. Even if this would work for connecting from my desktop, it is not a solution for the connection between the servers. I suspect though that if I get this working, the other connection will work as well.
pg_hba.conf: I copied the connection list from the other server, and my local IP is in here, as well as the local IP (10.x.x.x) of the Amazon webserver.

After installing the server I changed the database password for the postgres user using MD5. As the connection over the SSH tunnel worked, I guess this should work for all connections automatically, but I'm not sure. I haven't setup the other server, and when I query the postgres users table (pg_catalog.pg_user), I don't see any difference between the two servers.

I'm not 100% sure about the security groups, so I'm going to look into that again, but besides that I don't know where to look.

How can I get the connection working?

Update

Netstat output

netstat -an | grep 5432
tcp        0      0 127.0.0.1:5432              0.0.0.0:*                   LISTEN
unix  2      [ ACC ]     STREAM     LISTENING     63088  /tmp/.s.PGSQL.5432
unix  2      [ ACC ]     STREAM     LISTENING     63087  /var/run/postgresql/.s.PGSQL.5432

Telnet

Unable to connect to remote host: Connection refused

You need to work your way up the networking stack to determine where the issue is.

Can you ping the destination from the source?

Can you telnet to the postgres port(5432 by default) from the source?

Can you connect to the postgres service with a management tool(pgadmin or psql) from the source?


Is there a way to speed up restoring data from MySQL to PostgreSQL? OS: Windows10 x64
HDD: 1TB
RAM: 8GB
CPU: Intel Core i5-4460
PostgreSQL: 9.6

I want to import almost 37GB data from MySQL into PostgreSQL, and the data is sql file, which full of INSERT INTO tbl_1 VALUES(x, x, x); statements.

I'm using \i data.sql to import it, but after 15 hours work, the PostgreSQL's data size is only 1GB. While during this 15 hours, there are no other tasks running, only the importing.

So, is there anyway i can do to speed up the procedure?

You should try pgloader : http://pgloader.io/howto/mysql.html It can load data from MySQL to PostgreSQL using COPY mode, which is faster than INSERT.


MySql takes too long with matching index I have the following Table with 774.462 rows.

Its running on mysql server 5.6.16, which has nothing other to do (running local)

But a simple query is running slow:

SELECT serial FROM `event` WHERE id=5586929 AND minute=71;
/* Affected rows: 0  Gefundene Zeilen: 1  Warnungen: 0  Dauer von 1 query: 0,422 sec. */

The explain seems to use the index

Why does this Query which perfecly matches my index take 400ms?

CREATE TABLE `event` (
    `serial` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `type` VARCHAR(255) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
    `name` VARCHAR(255) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
    `id` VARCHAR(255) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
    `minute` BIGINT(20) NULL DEFAULT NULL,
    `running` TINYINT(1) NULL DEFAULT NULL,
    `home` VARCHAR(255) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
    `away` VARCHAR(255) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
    `starttime` BIGINT(20) NULL DEFAULT NULL,
    `period` INT(11) NULL DEFAULT NULL,
    `timeReference` BIGINT(20) NULL DEFAULT NULL,
    `timeOffset` BIGINT(20) NULL DEFAULT NULL,
    `goalsHome` INT(11) NULL DEFAULT NULL,
    `goalsAway` INT(11) NULL DEFAULT NULL,
    `twH` DOUBLE NULL DEFAULT NULL,
    `twX` DOUBLE NULL DEFAULT NULL,
    `twA` DOUBLE NULL DEFAULT NULL,
    `region` INT(11) NULL DEFAULT NULL,
    `redCardsTeam1` INT(11) NULL DEFAULT NULL,
    `redCardsTeam2` INT(11) NULL DEFAULT NULL,
    `yellowCardsTeam1` INT(11) NULL DEFAULT NULL,
    `yellowCardsTeam2` INT(11) NULL DEFAULT NULL,
    `offsidesTeam1` INT(11) NULL DEFAULT NULL,
    `offsidesTeam2` INT(11) NULL DEFAULT NULL,
    `goalKicksTeam1` INT(11) NULL DEFAULT NULL,
    `goalKicksTeam2` INT(11) NULL DEFAULT NULL,
    `throwInsTeam1` INT(11) NULL DEFAULT NULL,
    `throwInsTeam2` INT(11) NULL DEFAULT NULL,
    `penaltiesTeam1` INT(11) NULL DEFAULT NULL,
    `penaltiesTeam2` INT(11) NULL DEFAULT NULL,
    `substitutionsTeam1` INT(11) NULL DEFAULT NULL,
    `substitutionsTeam2` INT(11) NULL DEFAULT NULL,
    `cornersTeam1` INT(11) NULL DEFAULT NULL,
    `cornersTeam2` INT(11) NULL DEFAULT NULL,
    `freeKicksTeam1` INT(11) NULL DEFAULT NULL,
    `freeKicksTeam2` INT(11) NULL DEFAULT NULL,
    `overQuote` DOUBLE NULL DEFAULT NULL,
    PRIMARY KEY (`serial`),
    UNIQUE INDEX `id` (`id`, `minute`),
    INDEX `id_minute_index` (`id`, `minute`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
AUTO_INCREMENT=1343356
;

I think that in your case a hidden type conversion is the cause:

`id` VARCHAR(255) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci'

but in query:

WHERE id=5586929

It means INT

Change this to:

WHERE id='5586929'

Also from my point of view (not related to SELECT performance, but generally):

UNIQUE INDEX `id` (`id`, `minute`),
INDEX `id_minute_index` (`id`, `minute`)

is a duplicated construction. UNIQUE is already an index. There is no need for the second one.


Ola Hallengren Index Optimize - Skip "unused" Indexes Is there a way to skip "unused" indexes based on the total reads of that index during an index maintenance w/ Ola's scripts?

I understand that

Index stats are only since the last restart, so monitoring over a business cycle is important
Skipping a reorg or rebuild on an "unused" index which may be needed later, even once, in the business cycle could cause performance issues
Everyone's thinking "why don't you just remove it"

To answer the latter, it's a vendor supplied application w/ an OLTP DB of ~2TB. To stay compliant, we can't change much and I can't drop their indexes among other things. Thus, I thought it'd be beneficial to reduce the maintenance time by skipping these indexes once I've confirmed they aren't ever used. This would be beneficial for situations where, like this, my hands are mostly tied.

Follow on for comments would be if this isn't able to be done, is it worth tossing to Ola as an enhancement?

I don't think there is such an option, and you could submit a feature request (or a pull request) to Ola, but you can however submit a list of indexes that you want to exclude.

For example if you pass ALL_INDEXES, -Db1.Schema1.Tbl1.Idx1  into the @indexes parameter the script will skip the index Idx1 on the object Schema1.Tbl1 in the database Db1.

The @indexes parameter is nvarchar(max) so you should be able to pass as many indexes as you like.

I realize that this may become very tedious to maintain, but you should be able to generate the list from the query you use to determine unused indexes.

This approach would however allow for a more fine-grained control over which indexes you skip and this way you could avoid skipping indexes that are only used every once in a while or you could also create different jobs so you skip large unused indexes in your normal job but still defragment some of them when you have a larger maintenance window etc.


Most efficient way to retrieve a sub-query COUNT grouped by top table? Given the following schema

CREATE TABLE categories
(
    id UNIQUEIDENTIFIER PRIMARY KEY,
    name NVARCHAR(50)
);

CREATE TABLE [group]
(
    id UNIQUEIDENTIFIER PRIMARY KEY
);

CREATE TABLE logger
(
    id UNIQUEIDENTIFIER PRIMARY KEY,
    group_id UNIQUEIDENTIFIER,
    uuid CHAR(17)
);

CREATE TABLE data
(
    id UNIQUEIDENTIFIER PRIMARY KEY,
    logger_uuid CHAR(17),
    category_name NVARCHAR(50),
    recorded_on DATETIME
);

And the following rules

Each data record references a logger and a category
Each logger will always have a group
Each group can have multiple loggers
I only want to count the most recent data recorded

category_name isn't unique per row, it's just a way to associate a given data record under a category, id is really just a surrogate key.

What would be the optimum way to achieve the a resultset like

category_id | logger_group_count
--------------------------------
12345          4
67890          2
.....          ...

i.e. count the no. of groups for each category where a logger has recorded data?

As an initial stab I came up with:

SELECT g.id, COUNT(DISTINCT(a.id)) AS logger_group_count
FROM categories g
  LEFT OUTER JOIN data d ON d.category_name = g.name
  INNER JOIN logger s ON s.uuid = d.logger_uuid
  INNER JOIN group a ON a.id = s.group_id
GROUP BY g.id

But is extremely slow (~45s), data has 400k+ records - here's the query plan and here's a fiddle to play with.

I want to make sure I'm eeking the most out of the query before I start looking at other things i.e. hardware utilization etc. Azure SQL costs can go up substantially (even though you maybe just need a little more juice off your current tier).

You're on a newer version of SQL Server so the actual plan gives you a lot of information. See the caution sign on the SELECT operator? That means that SQL Server generated a warning which could affect query performance. You should always look at those:

<Warnings>
<PlanAffectingConvert ConvertIssue="Seek Plan" Expression="[s].[logger_uuid]=CONVERT_IMPLICIT(nchar(17),[d].[uuid],0)" />
<PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(100),[d].[name],0)=[g].[name]" />
</Warnings>

There are two data type conversions caused by your schema. Based on the warnings I suspect that name is actually an NVARCHAR(100) and logger_uuid is an NCHAR(17). The posted table schema in the question may not be correct. You should understand the root cause of why these conversions are happening and fix it. Some types of data type conversions prevent index seeks, lead to cardinality estimate issues, and cause other problems.

Another important thing to check is wait stats. You can see those in the details of the SELECT operator as well. Here's the XML for your wait stats and the time spent by the query:

<WaitStats>
<Wait WaitType="RESOURCE_GOVERNOR_IDLE" WaitTimeMs="49515" WaitCount="3773" />
<Wait WaitType="SOS_SCHEDULER_YIELD" WaitTimeMs="57164" WaitCount="2466" />
</WaitStats>
<QueryTimeStats ElapsedTime="67135" CpuTime="10007" />

I'm not a cloud guy but it looks like your query isn't able to fully engage a CPU. That's probably related to your current Azure tier. The query only needed about 10 seconds of CPU when executing but it took 67 seconds. I believe that 50 seconds of that time was spent being throttled and 7 seconds of that time was given to you but used on other queries that were concurrently running. The bad news is that the query is slower than it could be due to your tier. The good news it that any reductions in CPU could lead to a 5X reduction in run time. In other words, if you can get the query to use 1 second of CPU then you might see a runtime of around 5 seconds.

Next you can look at the Actual Time Statistics property in your operator details to see where the CPU time was spent. Your plan uses row mode so the CPU time for an operator is the sum of time spent by that operator as well as its children. This is a relatively simple plan so it doesn't take long to discover that the clustered index scan on logger_data uses 6527 ms of CPU time. The loop join that calls it uses 10006 ms of CPU time, so all of your query's CPU is spent at that step. Another clue that something is going wrong at that step can be found by looking at the thickness of the relative arrows:

A lot of rows are returned from that operator so it's worth looking at detail. Looking at the actual number of rows for the clustered index scan you can see that 14088885 rows were returned and 14100798 rows were read. However, the table cardinality is just 484803 rows. Intuitively that seems pretty inefficient, right? The clustered index scan returns far more than the number of rows in the table. Some other plan with a different join type or access method on the table is likely to be more efficient.

Why did SQL Server read and return so many rows? The clustered index is on the inner side of a nested loop. There are 38 rows returned by the outer side of the loop (the scan on the logger table) so the scan on logger_data executes 38 times. 484803*38 = 18422514 which is pretty close to the number of rows read. So why did SQL Server choose such a plan that feels so inefficient? It even estimates that it'll do 57 scans of the table, so arguably the plan that you got was more efficient than it suspected.

You might have been wondering why there's a TOP operator in your plan. SQL Server introduced a row goal to when creating a query plan for your query. This might be more detail than you want, but the short version is that SQL Server does not always need to return all rows from a clustered index scan. Sometimes it can stop early if it only needs a fixed number of rows and it finds those rows before it reaches the end of the scan. A scan isn't as expensive if it can stop early so the operator cost is discounted by a formula when a row goal is present. In other words, SQL Server expects to scan the clustered index 57 times, but it thinks that it will find the single row that it needs very quickly. It only needs a single row from each scan due to the presence of the TOP operator.

You can make your query faster by encouraging the query optimizer to pick a plan that doesn't scan the logger_data table 38 times. This might be as simple as eliminating the data type conversions. That could allow SQL Server to do an index seek instead of a scan. If not, fix the conversions and create a covering index for the logger_data:

CREATE INDEX IX ON logger_data (category_name, logger_uuid);

The query optimizer chooses a plan based on cost. Adding this index makes it unlikely to get the slow plan which does many scans on logger_data because it'll be cheaper to access the table through an index seek instead of a clustered index scan.

If you aren't able to add the index you can consider adding a query hint to disable the introduction of row goals: USE HINT('DISABLE_OPTIMIZER_ROWGOAL')). You should only do this if you feel comfortable with the concept of row goals and understand them. Adding that hint should result in a different plan, but I can't say how efficient it'll be.


Deleting rows from multiples tables at once I have a question about this piece of code I just wrote. I have a problem with the syntax, the first query executes its purpose, the problem is in the second query which does not.

if(isset($_POST['deletecontr'])){
  $dcquery = $_POST['dni']; // This get the ID of the person who I want to delete.
  $catchempr = ("SELECT `empresa` FROM `contratistas` WHERE dni='$dcquery'"); //This catch the name of the company of that person
  $query = ("DELETE FROM `contratistas` WHERE dni='$dcquery'"); // This works fine
  $query2 = ("DELETE FROM `empleados` WHERE empresa='$catchempr'"); This does not.
  mysqli_query($link,$query);
  mysqli_query($link,$query2);
  echo "La empresa a sido eliminada de la base de datos";

I hope you guys can see something that I'm not seeing.

If you change your code to this, it'll work:

if(isset($_POST['deletecontr'])){
  $dcquery = $_POST['dni']; // This get the ID of the person who I want to delete.
  $catchempr = ("SELECT `empresa` FROM `contratistas` WHERE dni='$dcquery'"); //This catch the name of the company of that person
  $query = ("DELETE FROM `contratistas` WHERE dni='$dcquery'"); // This works fine
  $query2 = ("DELETE FROM `empleados` WHERE empresa in ($catchempr)"); This does not.
  mysqli_query($link,$query);
  mysqli_query($link,$query2);
  echo "La empresa a sido eliminada de la base de datos";

By doing this, I have changed the query from roughly:

DELETE FROM `empleados` WHERE empresa='SELECT `empresa` FROM `contratistas` WHERE dni='$dcquery'' -- // ignore the $dcquery part

to:

DELETE FROM `empleados` WHERE empresa in (SELECT `empresa` FROM `contratistas` WHERE dni='$dcquery')

By expanding the query strings you can see that the first query is not valid for a couple of reasons.

I'll also add that this code is susceptible to SQL Injection because you are not verifying or filtering the input string $_POST['deletecontr']. To mitigate this, use bind variables/prepared statements.


How we can recover MySQL database (Innodb,myisam) I have found some recovery tool innodb-recovery but it works only on linux machine, I would like to know how to recover innodb tables on windows.

Transaction ID based recovery
Cancel based, Change based recovery
Point in time based recovery

Let me know if any of the blogs are available for this, and looking for good information.

First of all don't use this version. The recovery toolkit migrated to LaunchPad and since then version 0.4 and 0.5 were released. But even 0.5 is outdated because I never had a chance to release a lot of useful features and bugfixes which are still in the trunk. The current home of Percona Data recovery toolkit is https://launchpad.net/percona-data-recovery-tool-for-innodb

After I quit Percona I forked the data recovery toolkit and made undrop-for-innodb ( https://github.com/twindb/undrop-for-innodb ) which includes Percona's features + performance optimizations and even more bugfixes.

But it still works on Linux only.

To use it on Windows there are two options:

Create a virtual machine and run it on Linux. InnoDB files format is same on Linux and Windows (and any other OS InnoDB supports) so the tool will perfectly work with InnoDB files from Windows environment.
Use online service https://recovery.twindb.com/ . It's a frontend to the toolkit and of course you can use it on Windows. However not all possible scenarios are implemented so depending on your case you may be left with option #1 only.


Strange JOIN ON clause in T-SQL I am untangling some legacy code and never in all my years have I seen this:

Select *

FROM GLAccounts
INNER JOIN GLCharts
    ON glaGLChartID = glcGLChartID
LEFT JOIN GLCategories
    ON glcGLCategoryID = gltGLCategoryID
INNER JOIN GLDepartments
    ON glaGLDepartmentID = gldGLDepartmentID
INNER JOIN GLDivisions
    ON glaGLDivisionID = glvGLDivisionID
        ,GLFiscalYearPeriods                --<this comma>
INNER JOIN GLFiscalYears
    ON glfGLFiscalYearID = glzGLFiscalYearID

ORDER BY glcGLCategoryID
    ,glcParentGLChartID
    ,glaGLChartID

the join works, but I don't find this use of a comma in any T-SQL syntax reference for the ON clause.

What's happening here?
TIA

GLFiscalYearPeriods is a table and this comma implies a cross join. (Cartesian product)

It seems that the query returns some values for each Fiscal Year.

Given this tables:

create table a (id int, foo int);
create table c (id int);
insert into a values (1,1),(2,2),(3,3);
insert into c values (10),(20);

select * from a, c;

select * from a cross join c;

id | foo | id
-: | --: | -:
 1 |   1 | 10
 2 |   2 | 10
 3 |   3 | 10
 1 |   1 | 20
 2 |   2 | 20
 3 |   3 | 20

db<>fiddle here

Just another example:

create table a (id int, foo int);
create table b (id int, foo int);
create table c (id int);
create table d (id int);
insert into a values (1,1),(2,2),(3,3);
insert into b values (1,1),(2,2),(3,3);
insert into c values (10),(20);
insert into d values (1),(2);

select * from a join b on a.id = b.id       ,     c join d on d.id = a.id ;

  Msg 4104 Level 16 State 1 Line 1
  The multi-part identifier "a.id" could not be bound.

but if you cross-join it:

select * from a join b on a.id = b.id  cross join  c join d on d.id = a.id ;

id | foo | id | foo | id | id
-: | --: | -: | --: | -: | -:
 1 |   1 |  1 |   1 | 10 |  1
 1 |   1 |  1 |   1 | 20 |  1
 2 |   2 |  2 |   2 | 10 |  2
 2 |   2 |  2 |   2 | 20 |  2

db<>fiddle here


Is it possible to separate the application of statements inside database transactions? This is a simple scenario that I encountered recently.

I will fabricate a minimal scenario to illustrate the problem.

We have a database table with a column of IDs used as a foreign key to something (but which is not specified as a foreign key; it's being used to hold an ephemeral mapping index temporarily, in a really rather dangerous way that relies on external state). Each row has a value populated here. This column of data subsequently must be updated with a new mapping.

An example of a valid mapping:

0 -> 1
1 -> 4
2 -> 0

An example dataset:

2
1
0
2

Obvious desired result:

0
4
1
0

Implementation (pseudocode):

for (mapping in map) {
    sql("update table set value = " + mapping.to + " where value = " + mapping.from + ";")
}

Pretty straightforward but almost as obvious is that this is completely wrong because the result we see is

0
4
4
0

The third row got the mapping applied twice.

I was thinking that maybe if the N (where N is the number of mappings) statements are executed in a transaction that it may solve the problem. But I'm not so sure, and I don't really see why it would. The transaction would just guarantee that either row 3 got zero or both mappings applied, but what I need is for only the one mapping. It basically seems like I need a whole new column to even accomplish such a simple operation in a straightforward way.

How is this sort of situation dealt with usually? I feel like I'm missing something trivial.

You need something like

UPDATE tab1 set value =
   CASE value
     WHEN 0 THEN 1
     WHEN 1 THEN 4
     WHEN 2 THEN 0
   END;

If you need to treat values which are not listed in CASE in a different way, say don't change it ,  you need to add ELSE to CASE as  ypercubeᵀᴹ pointed out:

UPDATE tab1 set value =
       CASE value
         WHEN 0 THEN 1
         WHEN 1 THEN 4
         WHEN 2 THEN 0
         ELSE value
       END;

Or you can create a new table, insert  all mappings there,
and then (assuming mapping is unique)

UPDATE  tab1 t SET value =
 (SELECT new_val from mapping_table t2 WHERE t2.old_val = t.value)


New user creation option not available in SSMS sql server 2106 , how to enable? i need to create New user through SSMS in SQL server 2016

My guess is that your installation of SSMS is messed up. Try using SSMS on a different machine and see if you get the same behaviour there.
If it is SSMS that is messed up, then I suggest you just install the most recent version of SSMS and work with that. Hopefully that will sort things out for you.
(I initially thought that you might not have permissions to create a user, but I tried that and SSMS still gave me a "new user" option, but at a later stage I would get an error that I don't have permissions to create a user.)
You can of course always use the CREATE USER command from a query window.


What real-world problems have you solved with Extended Events? I have a confession to make: I don't use extended events nearly as much as I've been led to believe I should. Any time I see a blog post on them or read about them in documentation, I say "that's neat, but seems contrived". So I'm wondering what real-world problems have you solved with extended events? In order for it to be useful to everyone, detail the problem and the events session that you set up in as much detail as possible.

There are TONS of theoritical ones but I'll stick to 1 that I used by using extended events, then another that a tool I demo'd found with extended events:

Had to build a system that met the SISR and PCI tier 1 requirements for a tier 1 telco.  They required us to log every SA log in.  Using extended events was a lot more lightweight and easy to manage than having to worry about profiler always being on.

2
-TempDB Contention.  A query would run 1 second, half a second, 30 seconds, it was all over the place.  The tool used extended events to break down the wait types and I saw PAGELATCH_SH was really high.  Turns out tempdb did not have enough data files to optimize all the CPUS (32 cores) that the server had running and we had tempdb contention.  Using the wait stats per query really helped figure that issue out fast.

-Was migrating a back office SQL cluster to a different data center, trying to keep downtime to 1 minute or less.  While testing the new data center I would get major timeouts.  Timeouts were usually caused by sql server however looking at the wait type of NETWORK_IO we were able to trace it to a firewall between the DCs that was pegged at 100% CPU dropping packets faster than monica lewinsky.  Yea that's right, my last example had Monica Lewinski lol.


SQL Server Certificate I suddenly start to get the message while performing backup, I know i have taken backup of the certificate and stored it in my password manager which I still have access to.
My question is that do we need to have backup cycle of certificate(s) backup? is there any default limit set from Microsoft that we need to take backup every x days. I know ,I took backup of certificate on '2017-02-03 15:57:53.000' i.e. 164 days ago.

Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.

That warning is just checking to see if you've ever backed up your encryptor certificate using T-SQL.  This could put you in situation where the database backup would be unreadable if you were to lose that SQL Server instance for whatever reason.

No, you don't need a regular backup cycle for the cert. You wouldn't need to back it up after the initial one unless you've lost your copy.

That warning is just checking to see if the pvt_key_last_backup_date IS NULL.

    select pvt_key_last_backup_date, *
   from master.sys.certificates
   where name = 'BackupCertifcateName'

Perhaps that backup was taken with a different certificate than the one you've backed up?

You can check to see which Certificate was used with this query:

SELECT
    c.name as backupCertName,
    key_algorithm,
    encryptor_thumbprint,
    encryptor_type,
    backup_start_date,
    type as backupType
FROM msdb.dbo.backupset bks
JOIN master.sys.certificates c on c.thumbprint = bks.encryptor_thumbprint
WHERE database_name = 'DB_NAME'
AND Encryptor_type = 'CERTIFICATE'
AND type <> 'L'
ORDER BY backup_start_date  DESC


How can I skip reboot check and remove command line appearing during install of SQL Server? While i am installing SQL Server 2008 R2 using the configuration file method I sometimes get an error in installation due to system reboot checking.

After restarting my PC, SQL Server installs properly.  I want SQL Server setup to not check if the PC is rebooted or not. In SQL Server 2005 I can do this by using /norebootchk. How can I do this in SQL Server 2008 R2?

Also while installing in silent mode by using quiet=true I am getting a SQL Server command prompt while installing.

How can I avoid this command prompt appearing when I am installing in quiet mode?

To skip the reboot check:

SkipRules=RebootRequiredCheck

To hide the console window:

HIDECONSOLE=True

Full command line/.ini file options can be found here.  The SkipRules option is not documented there and appears to be part of the general installer options.


relation between customer and product with review I draw this ERD but I'm not sure if the relation in customer and product with review is correct. also, is it right that I made Rate, Date and time this way ?

this is my Description for review

Each  customer  can  write  as  many  reviews  of  the  products  he/she  bought  along  with  the  date and time of posting the review. The review will carry a unique ID. Also  the customer can give rating  from  1  to  5  which  are  illustrated  by  showing  glowing  gold  stars,  and  every  review  is welcomed and encouraged to be writing. So the website can be an open area to discuss the best option to buy. Also every customer can choose to writes as many reviews on the product or not.

Your relationships are correct, however, they would allow for a customer to have records of products they've never bought. Is this something you want? Otherwise, you'd be better off establishing the relation between Review and OrderDetails (the intermediate table for Order-Product which in your ERD doesn't have a name), rather than to Product directly. That being said, your ERD would probably be clearer if it'd be [Order]---*[OrderDetail]*---[Product], where OrderDetail includes quantity, price used, discount, special notes, etc.

Also, Rate should perhaps not be a separate entity, but rather be Review's attributes.


Backup with date to azure blob storage url Im trying to do backups to an azure blob storage that have the date when the backup is made, that way the job does not fail when the backup is generated with the same name, I am concatenating convert(varchar(500),GetDate(),112) + '.bak') but im getting the next error in the job execution

Incorrect syntax near '+'. [SQLSTATE 42000] (Error 102).  The step failed.

The script:

Backup database model
to url = 'https:containerurl/azurecontainer/model' + convert(varchar(500),GetDate(),112) + '.bak')
go

You can't do concatenation in the backup command. But you can have a variable for the file name, so construct the file name first into the variable and use that variable in the backup command.


How to update primary key table based on foreign key with ON DUPLICATE KEY UPDATE I have a users table with autoincrement id as primary key, and unique key on email. The email and name fields are from facebook.

+----+-------+------+
| id | email | name |
+----+-------+------+

And a facebook users table,

+--------+----------+
| userId | fbUserId |
+--------+----------+

The userId in the facebook table will be a foreign key value, referencing id in the users table. The fbUserId is guaranteed to be unique by facebook. The reason I split these tables up is I plan to have more social logins in the future, and I'm trying make my schema future proof. Each additional table would also have a userId, linking it back to users.

Right now my insert update query is this

BEGIN;
    INSERT IGNORE INTO users
    (id,email,name)
    VALUES (0,?,?);

    INSERT IGNORE INTO users_facebook
    (userId, fbUserId)
    VALUES (LAST_INSERT_ID(), ?);
COMMIT

From what i understand, ON DUPLICATE KEY UPDATE only applies to a single row in a single table. What i'd like to do is if email or name is different for an existing fbUserId, i'd like to update the changed fields. How would i do this?

Also, any thoughts on my current query? I'm quite new to mysql.

Read the comments in the manual on this page https://dev.mysql.com/doc/refman/5.6/en/insert-on-duplicate.html  The technique involves id=LAST_INSERT_ID(id) in the UPDATE clause.

Also near the end of the section on the function: https://dev.mysql.com/doc/refman/5.7/en/information-functions.html#function_last-insert-id


How to avoid losing information via UPDATEs due to database design issues Business requirements
I have a database where information about documents and their related packages are kept. Each document can have multiple packages, and each package has its details and product list.
When documents become certified they cannot be further edited, but users are allowed to edit package details before attaching it to another document, but in certified documents old data should be kept.
Considerations
So my issue is that, when users update package details to create a new document, in front-end views package details get updated even in certified documents because I have only one row for package details in my database.
I am evaluating the possibility of keeping multiple rows for each update, so as to prevent losing the data retained before the updates in already-certified documents, but it will cause too much work. I want to know how to effectively design the relevant database in order to avoid this kind of issues.
My database diagram so far
This is my current design diagram.

The simplest way to implement the requirements inside the database would be to create two separate tables.  Then, you just don't GRANT UPDATE on the set of tables used to store the "final documents".  This assumes that the end-users are logging in with an account that does not own the tables.

create table not_final_documents ( document_id int );
grant insert,update,select,delete on not_final_documents to end_user_role;

create table final_documents ( document_id int );
grant insert,select on final_documents to end_user_role;

This enforces the requirement of "can't modify final documents" even if the users use a different application to work on the document/packages.

You'll want to create database side procedures (T-SQL, PL/SQL, etc) to handle the data management between not_final_documents and final_documents.


Why has mongorestore stopped recognizing the --ssl option I've been using a mongorestore command to move data into our production db. But it's recently stopped working. The command is

mongorestore --db import --collection intent --host Cluster0-shard-0/cluster0-shard-00-00-wr3bj.mongodb.net:27017,cluster0-shard-00-01-wr3bj.mongodb.net:27017,cluster0-shard-00-02-wr3bj.mongodb.net:27017 --username usr --password pswd --ssl --authenticationDatabase admin intent.bson

And I'm getting a strange message saying

error parsing command line options: unknown option "ssl"

This doesn't make much sense as the option is listed in the docs

I think I may have upgraded from 3.2 to 3.4 since I last ran the command.

I'm running mongo on OS X and I installed using the instructions here (with curl not brew).

Ewen-Macbook:~ aidan$ mongorestore --version
mongorestore version: r3.4.2
git version: 3f76e40c105fc223b3e5aac3e20dcd026b83b38b
Go version: go1.7
   os: darwin
   arch: amd64
   compiler: gc

What am I missing?

The instructions for installing on OS X manually via curl currently use the non-SSL version as an example and do not actually link to the latest version (eg. 3.4.2 is included at the moment but 3.4.4 is the latest production release). I've raised DOCS-10176 to correct this information.

In the interim, if you visit the MongoDB Download Center which is referenced directly above the curl step you can find links for of the available packages to download. The OS X downloads for MongoDB 3.0+ should all default to a version with SSL support, or explicitly highlight if a selected distribution does not include SSL encryption.


Why does pg_hba.conf sometimes have random rules added to it? (postgresql) I have postgres 10.4 set up on a Linux machine, and sometimes there will randomly be rules at the top of pg_hba.conf that only allows an MD5 connection by a user "pgdbadm". I'm not sure if the database got hacked but after changing the password and checking that a pgdbadm doesn't exist, I still see the new rules from time to time. Is it possible that the database is compromised?

The three rules, if I recall correctly are as follows:

local all all 0.0.0.0/0 reject
host all postgres 0.0.0.0/0 reject
host all pgdbadm 0.0.0.0/0 md5

Any explanation as to what is going on would be appreciated, as well as potential solutions.

EDIT: I have discovered that when setting up pg_hba.conf I used a rule where all connections didn't require a password. This probably allowed a bot to log in and create unwanted databases/other things, so I will do a clean install of Ubuntu and backup the databases.

As you seem to have surmised, "pgdbadm" is an account created by hackers. It is the known account created a recent crypto-mining attacker who exploits unsecured postgresql superuser accounts.  Changing the pg_hba.conf is also part of his MO.


How do I query based on percentages of a subset of values? I have a table with a column named Type that can have some set of values.  For instance, A, B, C, D, etc.  Behind the scenes this is mapped to an Enum.

I need to query this table to get a percentage of some subset of the values.

For example, I may want to get 400 total rows where 25 percent are Type = 'A', 25 percent are Type = 'B', and 50 percent are 'Type = 'C'. If the total quantity of rows is not 400 it should return up to 400.  Ideally, this would return 100Arows, 100Brows and 200C` rows.

Or, perhaps, I want 100 rows where 50 percent are A and 50 percent are C. Etc.

If it makes the query easier to write, I can pass in a count of each value I want instead of percentage, e.g.: 200 of A, 200 of C.

What is the best way to perform this query?

WITH  RequiredAmounts
        AS ( SELECT 'A' AS [TYPE] ,
                    200 AS Amount
             UNION ALL
             SELECT 'B' ,
                    200
           )
  SELECT  t.*
  FROM    RequiredAmounts
          CROSS APPLY ( SELECT TOP ( RequiredAmounts.Amount )
                                *
                        FROM    YourTable AS t
                        WHERE   t.[TYPE] = RequiredAmounts.[Type]
                      ) AS t

RequiredAmounts can select from a temporary table, table variable, a TVP. It can be anything. Just replace this sample CTE with whatever suits your needs.


The provided statistics stream is corrupt, when running UPDATE Statistics When running
    UPDATE STATISTICS mytable

I'm getting following error:
The provided statistics stream is corrupt.

When our application is inserting rows to mytable we are getting the same error.

However
DBCC CHECKDB
Doesn't give any errors.

Any ideas on how to fix this issue?

We are running Microsoft SQL Server Express Edition with Advanced Services 2008 R2

It's a bug.  You need CU11 for Server 2008 R2 SP2
http://support.microsoft.com/kb/2829845

VSTS bug #: 1790001
FIX: "The provided statistics stream is corrupt" when you run the
"UPDATE STATISTICS" statement against a table in SQL Server 2008 R2 or
SQL Server 2012


Zip array with values from JSON Say I have '{1, 2, 3, 4, 5}' (just an array with the numbers 1-5), and {"1": 0, "2": 3, "5": 0} (comes from a column). With only SQL, is it possible to output this? {"1": 0, "2": 3, "3": 15, "4": 15, "5": 0}. The 15 would be like a default value I pull from another column.
Another acceptable output would be an array with the values '{0, 3, 15, 15, 0}'.
Sounds too complex for pure SQL? If not possible, help with a PL/pgSQL would also be appreciated.

Return jsonb object
If there are never additional keys in the JSON object, it can be as simple as:
SELECT jsonb '{"1": 15, "2": 15, "3": 15, "4": 15, "5": 15}' || (json->'key')
FROM   tbl;

Parentheses are required to overrule operator precedence!
This does not remove additional keys from the JSON object.
Returns NULL if the JSON object is not found.
See example in the fiddle.
Consider notes for jsonb concatenation in the manual:

jsonb || jsonb → jsonb
Concatenates two jsonb values. Concatenating two arrays generates an
array containing all the elements of each input. Concatenating two
objects generates an object containing the union of their keys, taking
the second object's value when there are duplicate keys. All other
cases are treated by converting a non-array input into a
single-element array, and then proceeding as for two arrays. Does not
operate recursively: only the top-level array or object structure is merged.

Return int[]
Expanded the JSON object to a set with jsonb_each_text(), RIGHT JOIN to the full set (generated with generate_series() in a CTE), and aggregate with ARRAY constructor (fastest):
WITH a(key) AS (SELECT generate_series(1, 5)::text)
SELECT ARRAY (SELECT COALESCE(value::int, 15)
              FROM   jsonb_each_text(t.json->'key') RIGHT JOIN a USING (key)
              ORDER  BY key) AS int_arr
FROM   tbl t;

This trims any additional keys from the JSON object.
Returns an array of default values if the JSON object is not found.
See example in the fiddle.
db<>fiddle here
Related:

Why is array_agg() slower than the non-aggregate ARRAY() constructor?