Postgres: Purpose of archiving in master? I am reading tutorials on how to setup streaming replication in Postgres from master to slave.

Some tutorials recommend setting up archiving in the master (and restoring in slave) . In the master, this needs to be set in the conf file:

wal_level = hot_standby
archive_mode = on
archive_command = 'some rsync command to rsync logfile segments from master to slave'

What is the purpose of this archiving? When is it not necessary when setting up streaming replication?

WAL archiving is useful when you're running streaming replication, because there's a limit to how much WAL the master will retain.

If you don't archive WAL, and the replica gets s far behind that the master has discarded WAL it still needs, it cannot recover and must be replaced with a fresh base backup from the master.

It's also useful for PITR for disaster recovery purposes.


Writing a query to COUNT all the services involving a particular Learning PHP and SQL on an online MOOC, this is a homework that I've got to do.

I need to write a query in SQL to count all the services which involve a particular person in my database.

The particular's name is Gary Crowley located in the 'staff' table.
Below is my table:

My question is: What is the actual SQL query I should write to get the exact count of all the services which involve Gary Crowley? As you can see in the picture, the name appears four times. I have written a query that output the services only involving Gary Crowley but I don't know how to make the COUNT query.

select count(distinct service_id)
from staff
where staff_firstname = 'Gary'
  and staff_surname = 'Crowley';

The distinct is only necessary if your database design allows to assign the same service multiple times to the same staff member.


Should I index an integer column which is already ordered? I have a table like the picture below, I want to index two columns of this table (user_id, question). The type of all columns is int. The user_id and the     question_id columns are foreign keys.

CREATE TABLE user (
    user_id int(10) NOT NULL AUTO_INCREMENT,
    username varchar(50) DEFAULT NULL,
    password varchar(60) DEFAULT NULL,
    PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE question (
    question_id int(10) NOT NULL AUTO_INCREMENT,
    question varchar(60) NOT NULL,
    answer varchar(60) NOT NULL,
    PRIMARY KEY (`question_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE answer (
    user_id int(10) NOT NULL,
    question_id int(10) NOT NULL,
    score int(10) NOT NULL DEFAULT 0,
    timer int(10) NOT NULL DEFAULT 0,
    FOREIGN KEY (user_id) REFERENCES user(user_id),
    FOREIGN KEY (question_id) REFERENCES question(question_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

A lot of "updating" and "reading" query goes to this table. As you can see these two columns are already ordered because every time a user signup I will add the user's id in the user_id column and the numbers from 1 to 300 to the question column. the query is something like this:

UPDATE score SET user_score_for_question = 20 WHERE user_id=400 AND  question=100

Question1: since "user_id" and "question" columns are already ordered, Is it necessary to index them or not?

Question2: If I should index those columns, since I always use "user_id" and "question" together, how is the correct way to index these columns? I not familiar with "hash index" and "unique index" which one should I use?

Inserting in an order does not mean that is a natural order in the table.

Put a composite primary key on:

user_id, question_id

A second index on question_id is optional.
It would only be used if you are searching on question_id only.


Why there is a start (*) after Nodegroup:0 in MySQL cluster? What does it means? I am new to MySQL and MySQL cluster setup. However going through several blogs and Web links I've finally able to manage the cluster up and running. I have 1 management, 1 SQL and 3 data nodes (currently without any replicated SQL or data nodes as I have set NoOfReplicas=1 in the config.ini file). After running ndb_mgm> show I can see there is a start just after the first data node, what does it means? I searched over google but didn't find any proper answer. Kindly help.

ndb_mgm> show
Cluster Configuration
[ndbd(NDB)]     3 node(s)
id=3    @10.10.10.3  (mysql-5.5.34 ndb-7.2.14, Nodegroup: 0, *)
id=4    @10.10.10.4  (mysql-5.5.34 ndb-7.2.14, Nodegroup: 1)
id=5    @10.10.10.5  (mysql-5.5.34 ndb-7.2.14, Nodegroup: 2)
[ndb_mgmd(MGM)] 1 node(s)
id=1    @10.10.10.1  (mysql-5.5.34 ndb-7.2.14)
[mysqld(API)]   1 node(s)
id=6    @10.10.10.6  (mysql-5.5.34 ndb-7.2.14)

It shows which node is master. It is internal thing and probably the only visible effect of it is when exactly half of the nodes goes down. Then if one of the remaining nodes is master - cluster keeps working. If not - cluster aborts.

http://dev.mysql.com/doc/refman/5.1/en/faqs-mysql-cluster.html#qandaitem-B-10-1-8

Updated link:
http://dev.mysql.com/doc/refman/5.1/en/faqs-mysql-cluster.html#faq-cluster-what-show-nodegroup-star


Joining Products and Orders so that every product is listed along with the quantity a specific Buyer has ordered Table A is a list of "products" and table B is a list of "orders" referencing products (using product_id), along with buyers informations (buyer_name ecc) and the quantity of each product buyed.

An order has many products and a single buyer, so that when a buyer issues an order, a row with the same buyer_name and order_no is inserted into table B for each product ordered along with the desired quantity.

Which are the two fastest queries to join "products" and "orders" so that for every "products" row I have also the quantity that a specified buyer has ordered:

in a single order (order_no is specified)
in all his orders (sum quantity in every order issued by the buyer)

To be efficient, all the "orders" rows not belonging to the specified buyer (and not having the specified order_no for the first query) should be excluded before the join, but every row from "products" should be returned, even if the product has not been ordered from the buyer (quantity should be 0 in this case).

UPDATE (from comments):

Current query:

SELECT p.*, o.buyer_name, coalesce(o.quantity,0)
  FROM products p
         LEFT JOIN (SELECT *
                      FROM orders
                     WHERE buyer_name='Joe'
                      AND order_no=123
                   ) AS o USING(product_id)

I'm not having a performance issue now, and am not looking to tightly tune this specific query, but to ensure I'm writing queries efficiently in general. Similarly, current DBMS is PostgreSQL 9.4.4 - but looking for generic advice that would apply to most SQL variants.

In general, it's best to leave the specifics of how the data is joined together to the DB engine - usually, there is an optimizer that looks at table/index statistics, and figures out the most efficient way to eliminate rows from consideration. If you're having issues with performance on a specific query, you can sometimes force the engine to use a particular method/option on a given query - but this can lead to good performance in specific cases and bad performance in everything else.

That said, the logic of your query is such that rows from your "right" table should be excluded from consideration if they don't belong to the targeted buyer/order, so they do need to be included as a part of the LEFT JOIN clause. I would rework that as follows:

  FROM products p
         LEFT JOIN orders o ON (    p.product_id = o.product_id
                                AND o.buyer_name = 'Joe'
                                AND o.order_no = 123
                               )

If you simply join the two tables, the DB engine will figure out what columns it needs out of those tables from the rest of the query. I can't think of a good reason to use a sub-query that just selects specific columns from a table in a join, rather than using the table itself directly. I won't say there isn't such a reason, just that I have never encountered one.

Some versions of SQL may materialize a subquery - basically, running the subquery before the main query is evaluated, then evaluating the main query from those results. If you do choose to use a subquery, I'd recommend not using SELECT *, but rather specifying just the actual columns you need in the main query (SELECT product_id, order_qty from orders...), to avoid pulling in data you aren't actually going to use.

That same advice holds for the main query; rather that using SELECT *, specify the actual columns you need. As noted before, the DB engine will figure out what columns it needs from each table by looking at the main query's SELECT list, the WHERE clause, the JOIN criteria, and so forth. If you specify more columns in the SELECT list than you really need, then the engine will give them to you. If each row of data in your table has two varchar fields that average 250 characters each, but all you really need is a varchar(25) field and three integers, you'll be returning a result set that's around 10 times as large as it needs to be (which will take about ten times as much time to reach the machine that made the request).

Limiting your columns to those you actually need also will allowing the DB engine's optimizer to apply certain shortcuts that can improve performance. For example, if your table's rows are (on average) 1000 bytes wide, but you have an index that includes just the columns you need, and those columns only average 100 bytes per row, the optimizer can use that index as if it were the table, and thus may be able to reduce the number of database pages it has to read to get your data by a factor of 10. the term for this is a covering index.

It's also a good idea to assign column names to computed columns - it makes it easier to refer to them elsewhere.

So, we'd end up with the following queries (note that I'm assuming you only will be using a column named product_name in your results; if other columns from products apply, add them to the SELECT list (and to the ORDER BY clause):

For a specific order:

SELECT p.product_name
      ,o.buyer_name
      ,COALESCE(o.quantity,0) as Qty_Ordered
  FROM products p
         LEFT JOIN orders o ON (    p.product_id = o.product_id
                                AND o.buyer_name = 'Joe'
                                AND o.order_no = 123
                               )

For a buyer's total activity:

SELECT p.product_name
      ,o.buyer_name
      ,COALESCE(SUM(o.quantity),0) as Qty_Ordered
  FROM products p
         LEFT JOIN orders o ON (    p.product_id = o.product_id
                                AND o.buyer_name = 'Joe'
                               )
 GROUP BY p.product_name, o.buyer_name

A few final notes:

In some SQL variants, every column that is not in an aggregate function in the SELECT list must appear in the GROUP BY clause. Other versions allow you to leave columns out - the value from a "random" row will be returned. If you've got a unique value from a table in the GROUP BY, it would be safe to leave other columns from that table out if using one of these other versions, as all possible values from the rows being aggregated would have to be the same.

Also, your query makes an assumption: any order will only have one line item for a given product. Now, that seems like a reasonable assumption on the surface; however, I have seen systems where someone might place an order for 10 widgets on the 1st of the month, and an additional 10 on the 15th. If there's any chance that a product might show up twice in the same order, you can use the second query to pull the order-specific information as well; you just need to put AND o.order_no = 123 back in, like in the first query. This would probably take slightly longer than the query without the SUM and the ORDER BY - but I would expect, in most cases, the difference would be too small for a human being to tell.


What is difference between SELECT * FROM table and SELECT * FROM table WHERE 1 What is difference between SELECT * FROM table and SELECT * FROM table WHERE 1

  Query 1:

 SELECT * FROM table

Number of records : 103
Load time : 0.0005 sec

  Query 2:

 SELECT * FROM table WHERE 1

Number of records : 103
Load time : 0.0003 sec

  Question 1 : What is the difference between these 2 queries, if it
  loads the same data.

  Question 2 : Why there is these much time difference, if it loads the
  same data.

  Question 3 : Why phpmyadmin by default loads SELECT * FROM table WHERE 1 query ?

  Question 4 :What MySQL do for ABOVE queries in MySQL Query Caching?

Note :

The difference is minor here because number of records are small
(103),

  but when number of records increases then time difference is    also
  increase.

- So let's when we have 100000 number of records then we can see
   notable time difference.

Question 1 : What is the difference between these 2 queries, if it loads the same data.

None.

  Question 2 : Why there is these much time difference, if it loads the same data.

Within margin of error and your first query may have put them in a cache making it slightly faster.

  Question 3 : Why phpmyadmin by default loads SELECT * FROM table WHERE 1 query ?

Because it's a bit special. There are many odd behaviors of what phpMyAdmin manipulates queries in a browser to before it gets to SQL. Programmatic convenience maybe.

  Question 4 :What MySQL do for ABOVE queries in MySQL Query Caching?

Query caching is based on the literal text of the query, and some session variables. They would be cached differently.
PS. Query cache is evil.


Database structure for product attributes on orders I'm making a POS and currently kind of stuck on the order proccess due to the product attributes.

As of now the DB looks something like this:

tbl_products

tbl_attributes (attr id, name, extra price)

tbl_sales ( where i store the time, total price etc)

tbl_salelines ( where i store what items have being order for a certain sale).

Problem is storing the attributes for an item order. There isn't a fix amount of attributes one item can have. It could go from 1 to 10-15 therefor i don't think making some columns in the tbl_salelines with attr_1 attr_2 etc etc would solve this.

How should i approach this?

Assuming that the attributes are for records in the tbl_salelines.

If the relationship is such that one salesline can have many attributes, then add a column for salesline_id in the tbl_attributes table.

If the relationship is such that one salesline can have many attributes AND one attribute can have many saleslines, then add an intersect table with attribute_id and salesline_id, where both of these id's are primary keys in their own table and are foreign keys in the intersect table. It may look like this...

select * from tbl_salesline_i_attribute;

SALESLINE_ID  ATTRIBUTE_ID
------------  ------------
800           22
800           89
555           45
764           45

select * from tbl_salesline;

SALESLINE_ID  COLUMN
------------  ------------
800           foo
555           bar
764           foobar

select * from tbl_attribute;

ATTRIBUTE_ID  NAME               EXTRA_PRICE
------------  ------------       -------------
22            blue               3
89            glow-in-the-dark   3
45            extra large        10

In the example above, salesline 800 has two attributes on it (blue and glow-in-the-dark). Likewise, attribute 45 is on two saleslines.


Microsoft Sql Server - How to replicate a table from Linked Server? I have an Origin Database - #1 with very restrictive access due to security.

I provisioned a new Windows desktop machine with Microsoft Sql Server Management where I created a local database with all privileges, so I can create a Linked Server to database #1.

I have access to the Database #1 via Linked Server but I need to replicate some tables in realtime; I tried using the Replication option in the Object Explorer, but it does not allow me to do anything with the Linked Server.

How can I replicate some of those tables via Linked Server? So, I can copy those tables and updates and store them in local database?

It doesn't sound like any of the built-in SQL features are going to meet your needs here:

SQL replication makes a lot of changes to the source db, and probably won't work over a linked server
Mirroring and/or always-on availability groups likewise require lots of underlying configuration to the source server
Log shipping, if you had access to transaction log backups, would bring over the entire database state, not just the tables you want

You're going to have to either use a 3rd-party software (like Redgate SQL Compare), or roll your own.

Personally I'd build an SSIS package to pull down new or updated data from the required tables (hopefully you have some easy "last_update" fields to go off of) and run it via a SQL job every few minutes. There is no SQL wizard for this, you'll have to know what you're doing or hire someone who does.


Is there a way to convert a custom format Pg dump file to a directory format? We create a "custom" format (-Fc) dump file of our production DB each night. One of the things this is used for is restoring into a test DB.  We then run a number of update queries to anonymise the data, but because there are millions of rows this is rather a time-consuming task.
I had the idea that if we used the "directory" format (-Fd) dump then I could run some scripts to directly modify the data in that dump. Then instead of the two step (pg_restore+updates) we'd simply be restoring anonymised data in a single step.
However I still need the custom dump file for feeding into other processes, and I'd prefer to avoid dumping twice if possible.  So I was wondering if it's possible to take a custom format dump file and convert it into a directory format dump.

No, that is not possible. But there is no advantage in a custom format dump over a directory format dump (except that the latter is not a single file), so you might just use the directory format dump. You'd gain parallel pg_dump in the bargain.


Service Broker Using Wrong Certificates After configuring two Service Broker instances that successfully send msg to remote server on separate databases, I am trying to set up a second service broker instance on one of the same, successful, databases.

I am receiving errors on ssbdiagnose, only. No other tools show errors.
2 Errors from ssbdiagnose, when running on the source server.

'target IP' 'targetDatabaseName' The certificate 'source cert name' used by user '' for dialog security is not deployed in the database.
'target IP' 'targetDatabaseName' The user '' from sourceDatabase '' on 'source IP' cannot be mapped into this database using certificates.

The source cert name from the first error msg is my previously existing certificate on the source server that is used to send msg to a different database on the target server.

So it appears to be looking at the wrong certificate.

When running ssbdiagnose on the target server, I get the following error:

Login Failed. The login is from an untrusted domain and cannot be used with Windows Authentication.

Thank you

It appears you're using Dialog Level Security. This means you have deployed remote service bindings, users and certificates, successfully exchanged certificates and and granted SEND permission on the target service. For example, if you have a DB1 and a DB2, and a service S1 in DB1 and a service S2 in DB2, then, to begin a secure dialog from S1 to S2, you must have also:

create a certificate C1 in DB1 owned by user that owns S1, lets say U1 (usually dbo)
create a certificate C2 in DB2 owned by the user that owns S2, say U2 (again usually dbo, but this time dbo of DB2)
export public key of C1 and C2
create in DB1 an user, say U2' that will represent the S2 identity in DB1
import C2 public key as a certificate C2' in DB1, owned by U2'
create, in DB1, a remote service binding object that binds S2 to U2'. This way BEGIN DIALOG will know to use C2 public key to encrypt messages for S2
create in DB2 an user U1' that represents U1's identity in DB2
import C1 public key in DB2 as a certificate C1', owned by U1'. Because messages from S1 are signed with C1 private key (owned by U1), they will be identified in DB2 as being sent by U1' (the owner of C1', the corresponding public key).
grant SEND on service S2 to U1'

Again, if you say that messages between S1 and S2 are flowing fine, then all the above steps are already correctly done (not a small feat!).

If I understand correctly now you want to add a new service, say S3, also located in DB2, if I understand correctly, that needs to also accept messages from S1. I will make a simplifying assumption that S3 is owned by the same user as S2 (since usually the owner is dbo, this assumption is usually true). If so then you already have the necessary users/certificates exchanged, all you need to do tell SSB to use them:

in DB1 create a new remote service binding, RSB3, for service S3, using the same user U2'. BEGIN DIALOG will use the same certificate C2' to encrypt messages for S3 as for S2, but this is OK since both S3 and S2 are owned by the same user
in DB2 grant SEND permission on service S3 to user U1'.

This should be it, now S1 can talk with S2 and also with S3.

This certificate exchange and user ownership logic is used to 'map' a user from DB1 to DB2, without relying on Windows domains. This mapping is done via exchanging public key certificates and then following the certificate ownership: In DB1, U1 (ie. the owner of S1) owns a certificate with a private key, C1. The same certificate's public key exists in DB2 and is owned by U1' (a user in DB2). Therefore U1 'maps' to U1'. Whenever ssbdiagnose complains that a user cannot be 'mapped using certificates' it means that this chain is broken somewhere.


How (and why) does TOP impact an execution plan? For a moderately complex query I am trying to optimize, I noticed that removing the TOP n clause changes the execution plan.  I would have guessed that when a query includes TOP n the database engine would run the query ignoring the the TOP clause, and then at the end just shrink that result set down to the n number of rows that was requested.  The graphical execution plan seems to indicate this is the case -- TOP is the "last" step.  But it appears there is more going on.

My question is, how (and why) does a TOP n clause impact the execution plan of a query?

Here is a simplified version of what is going on in my case:

The query is matching rows from two tables, A and B.

Without the TOP clause, the optimizer estimates there will be 19k rows from table A and 46k rows from table B.  The actual number of rows returned is 16k for A and 13k for B.  A hash match is used to join these two results sets for a total of 69 rows (then a sort is applied).  This query happens very quickly.

When I add TOP 1001 the optimizer does not use a hash match; instead it first sorts the results from table A (same estimate/actual of 19k/16k) and does a nested loop against table B.  The estimated number of rows for table B is now 1, and the strange thing is that the TOP n directly affects the estimated number of executions (index seek) against B -- it appears to always be 2n+1, or in my case 2003.  This estimate changes accordingly if I change TOP n.  Of course, since this is a nested join the actual number of executions is 16k (the number of rows from table A) and this slows down the query.

The actual scenario is a bit more complex but this captures the basic idea/behavior.  Both tables are searched using index seeks.  This is SQL Server 2008 R2 Enterprise edition.

I would have guessed that when a query includes TOP n the database
  engine would run the query ignoring the the TOP clause, and then at
  the end just shrink that result set down to the n number of rows that
  was requested. The graphical execution plan seems to indicate this is
  the case -- TOP is the "last" step. But it appears there is more going
  on.

The way the above is phrased makes me think you may have an incorrect mental picture of how a query executes.  An operator in a query plan is not a step (where the full result set of a previous step is evaluated by the next one.

SQL Server uses a pipelined execution model, where each operator exposes methods like Init(), GetRow(), and Close().  As the GetRow() name suggests, an operator produces one row at a time on demand (as required by its parent operator).  This is documented in the Books Online Logical and Physical Operators reference, with more detail in my blog post Why Query Plans Run Backwards.  This row-at-a-time model is essential in forming a sound intuition for query execution.

  My question is, how (and why) does a TOP n clause impact the execution
  plan of a query?

Some logical operations like TOP, semi joins and the FAST n query hint affect the way the query optimizer costs execution plan alternatives.  The basic idea is that one possible plan shape might return the first n rows more quickly than a different plan that was optimized to return all rows.

For example, indexed nested loops join is often the fastest way to return a small number of rows, though hash or merge join with scans might be more efficient on larger sets.  The way the query optimizer reasons about these choices is by setting a Row Goal at a particular point in the logical tree of operations.

A row goal modifies the way query plan alternatives are costed.  The essence of it is that the optimizer starts by costing each operator as if the full result set were required, sets a row goal at the appropriate point, and then works back down the plan tree estimating the number of rows it expects to need to examine to meet the row goal.

For example, a logical TOP(10) sets a row goal of 10 at a particular point in the logical query tree.  The costs of operators leading up to the row goal are modified to estimate how many rows they need to produce to meet the row goal.  This calculation can become complex, so it is easier to understand all this with a fully worked example and annotated execution plans.  Row goals can affect more than the choice of join type or whether seeks and lookups are preferred to scans.  More details on that here.

As always, an execution plan selected on the basis of a row goal is subject to the optimizer's reasoning abilities and the quality of information provided to it.  Not every plan with a row goal will produce the required number of rows faster in practice, but according to the costing model it will.

Where a row goal plan proves not to be faster, there are usually ways to modify the query or provide better information to the optimizer such that the naturally selected plan is best.  Which option is appropriate in your case depends on the details of course.  The row goal feature is generally very effective (though there is a bug to watch out for when used in parallel execution plans).

Your particular query and plan may not be suitable for detailed analysis here (by all means provide an actual execution plan if you wish) but hopefully the ideas outlined here will allow you to make forward progress.


SQLite3 simple database design question I'm an ruby hobbist with no experience at all related to databases. I'm writing an application that is going to gather and backup tweets and blog posts (via rss feed) in an SQLite3 database.

The first version had support just for backing up a specfic user's tweets as I wanted to create a backup for my twitter account. So the SQLite3 schema was easy: 1 database, 1 table per user.

Now I want to add backup support for: Location based tweets (i.e. Tweets from Germany), username based tweets, and hashtag based tweets plus support rss feeds.

Since SQLite3 does not support any sort of sub-tables and I'd like to create a schema that will not need change as soon as I begin testing, I'd like to have opinions on how to approach this.

I've read a lot or articles online about SQL db design and found a couple of book also. Didn't purchase any yet, I'm just considering reading some basics about db design since I don't need an extensive book just an intro I'm evaluating. I'm not sure if these articles confused me more or not.

What I came up with a db schema is an INDEX table which user's info will be held and then a table per option per user, like:

➜  lib git:(dev) ✗ cat sqlite_scheme.txt
SQLite3 Table Scheme

MRParent
--------
db_key
table_id
twitter_id
twitter_option {what and how}
feed_url
short_description

MRChild-id-tweet-usr
---------
db_key
tweet_id {from MRParent}
table_id
tweet_date
tweet_location
tweet_text

MRChild-id-tweet-location
-------------------------
db_key
table_id {from MRParent}
tweet_id
tweet_date
tweet_location
tweet_text

MRChild-id-tweet-hashtag
------------------------
db_key
table_id {from MRParent}
tweet_hashtag
tweet_id
tweet_date
tweet_location
tweet_text

MRChild-id-rss
--------------
db_key
table_id {from MRParent}
post_author
post_date
post_title
post_text

So all tables will link to the INDEX table via table_id (which I've not deciced if it's gonna be a sort of hash or a combination of user's data, ideas here also welcomed).

Does this schema look 'rational' for an SQLite3 db or not? Any comments and/or questions are welcome.

Thanks for your time!

PA

After reading a lot around and receiving a hint about SQL data normalization I came with this schema. The rest will be handled by simple requests:

Table MRindex - All details about a entry will be listed here: key db | user_made_id | description | date | blob_picture | rss_feed | twitter_alias/keyword/hashtag |
Table MRtwitter - All twitter related data will be stored here: key db| index-at-table1| tw_option|tw_user | tw_date | tw_location | tw_name |tw_text
Table MRrss - All rss data will be stored here: key db | index-at-table1| rss_post_author | rss_post_date | rss_post_title | rss_post_text

Any comments & hints would be more than welcomed :-)

EDIT: To clarify a bit...

MRIndex (also referred as table1) holds basic data, filled by the user: Might be user tweets, hashtags or location based tweets. Once this entry is filled up

MRtwitter will hold the tweets and related info. The field index-at-table1 is the "link" to table MRindex.

MRrss it's the the same for blog posts. Same thing applies for 'index-at-table1'.

For MRindex:

description: short user description
date: date of creation
blob_picture: a picture if any (jpg, png, etc)
rss_feed: An RSS_feed URL belonging to this user
twitter_alias/keyword/hashtag: either @user, Greece (for location) or #hashtag


How to change SQL sever configuration manager settings using TSQL? SQL server configuration manager is used to configure certain settings like connection protocols, service start up,  etc...
Is it possible to make these changes that is done in SQL server configuration manager by using TSQL statements, or in the SSMS?

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

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

One examples , protocols is under

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

and each of these has an Enabled value

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


check storage engine from shell I'm upgrading to 3.0 and ran into some issues with the upgrade. Specifically, I got an error when trying to start up mongod via ssh, it tried to use the default dbpath instead of the one I specified in my new YAML config file. I went ahead and rebooted the machine and now mongod is up and running again. I'm a bit paranoid at this point and would like to know if there's a way to make sure the storage engine is wiredtiger from the shell.

Easiest way to find the storage engine being used currently.

Inside mongo console type

db.serverStatus().storageEngine

It returns the storage engine being used currently

{ "name" : "wiredTiger" }

Once it is confirmed that wiredTiger is being used then type

db.serverStatus().wiredTiger

to get all the configuration details of wiredTiger.


Create a temp table that consolidates start and end dates plus generates a uniqueid for each consolidated record I have a table called projecthistory,
it is actually a view so I could take advantage of the Rank function to get a unique record number.

From this view I need to create a temp table that has a uniqueid field which would be calculated bases on these rules:

Rule 1 - The next sequential record for the id changes from one
project to another or
Rule 2 - If the id and project match for the next sequential record
, but the days between the end date of the current record and start
date of the next record for the is more than 7 days.

It would also consolidate the start and end dates appropriately for each group.

I believe this would be best done in a stored procedure that can call the data when needed, but I'm fairly new to stored procedures.

Here is an example of the table data, as well as the desired output

Projecthistory

╔════════╦══════╦═════════╦═══════════╦═══════════╗
║ Record ║  Id  ║ Project ║   Start   ║    End    ║
╠════════╬══════╬═════════╬═══════════╬═══════════╣
║      1 ║ 1111 ║ A       ║ 1/1/2016  ║ 1/7/2016  ║
║      2 ║ 1111 ║ A       ║ 1/10/2016 ║ 1/20/2016 ║
║      3 ║ 1111 ║ B       ║ 1/21/2016 ║ 1/25/2016 ║
║      4 ║ 1111 ║ A       ║ 1/26/2016 ║ NULL      ║
║      5 ║ 2222 ║ A       ║ 1/1/2016  ║ 2/1/2016  ║
║      6 ║ 2222 ║ B       ║ 2/2/2016  ║ NULL      ║
║      7 ║ 3333 ║ D       ║ 1/15/2016 ║ 2/1/2016  ║
║      8 ║ 4444 ║ B       ║ 2/10/2016 ║ NULL      ║
║      9 ║ 5555 ║ A       ║ 2/1/2016  ║ 2/5/2016  ║
║     10 ║ 5555 ║ A       ║ 2/20/2016 ║ 2/27/2016 ║
║     11 ║ 5555 ║ A       ║ 2/28/2016 ║ 3/10/2016 ║
║     12 ║ 5555 ║ A       ║ 3/11/2016 ║ NULL      ║
╚════════╩══════╩═════════╩═══════════╩═══════════╝

Desired output

╔════════╦══════╦═════════╦══════════════╦════════════╗
║ unique ║  Id  ║ Project ║ ProjectStart ║ ProjectEnd ║
╠════════╬══════╬═════════╬══════════════╬════════════╣
║ 1111A1 ║ 1111 ║ A       ║ 1/1/2016     ║ 1/20/2016  ║
║ 1111B1 ║ 1111 ║ B       ║ 1/21/2016    ║ 1/25/2016  ║
║ 1111A2 ║ 1111 ║ A       ║ 1/26/2016    ║ NULL       ║
║ 2222A1 ║ 2222 ║ A       ║ 1/1/2016     ║ 2/1/2016   ║
║ 2222B1 ║ 2222 ║ B       ║ 2/2/2016     ║ NULL       ║
║ 3333D1 ║ 3333 ║ D       ║ 1/15/2016    ║ 2/1/2016   ║
║ 4444B1 ║ 4444 ║ B       ║ 2/10/2016    ║ NULL       ║
║ 5555A1 ║ 5555 ║ A       ║ 2/1/2016     ║ 2/5/2016   ║
║ 5555A2 ║ 5555 ║ A       ║ 2/20/2016    ║ Null       ║
╚════════╩══════╩═════════╩══════════════╩════════════╝

Here are some notes about the desired output and some of
more difficult scenarios in this data.

Take note that records 1-2 became 1111A1,
record 3 becomes 1111b1,
record 4 became the second instance of that project for id 1111 so its uniqueid became 1111A2, be careful this doesn’t get lumped
in with records 1&2.

Record 9 become 55555A1 and because of the date gap
between records 9&10, record 10-12 became uniqueid 5555A2

I look forward to any solutions, feedback or questions. I’m
truly grateful in advance.

ok I think I got it. I was able to get the results I was looking for using the following code, but it is slow when executing it to get the results. One thing I forget, was the location column, so I was sure to account for this in my code. I did end up breaking the uid and instance into two separate fields, but I know I could have merged them into one. I figured this would give me greater flexibility when pulling data against it in the future. Feel free to improve, I know I would appreciate it. I'm also happy to be posting my first answer. Thanks!

ALTER PROCEDURE [dbo].[ProjectTracking]
    AS
    BEGIN
        -- Insert statements for procedure here
    SET NOCOUNT ON;
    DECLARE @max int
    DECLARE @i int
    Declare @tinstance int
    Declare @tuid varchar(20)
    DECLARE @puid varchar(20)
    Declare @pinstance int
    DECLARE @pid int
    DECLARE @pproject varchar(10)
    DECLARE @plocation varchar(10)
    DECLARE @pstart datetime
    DECLARE @pend datetime
    DECLARE @cuid varchar(20)
    Declare @crecord int
    DECLARE @cid int
    DECLARE @cproject varchar(10)
    DECLARE @clocation varchar(10)
    DECLARE @cstart datetime
    DECLARE @cend datetime

    select @max = count([Record])+1 from [ProjetHistory]
    print @max

    -- Insert the first record so there will be a previous record for the first cycle of while
    SELECT * INTO #PTEMP
    FROM (SELECT CONVERT(varchar(15),[id])+[project]+rtrim([location]) as Uid,1 as instance, [id],[project],
                        [location],[Start],[End]
      FROM [ProjectHistory]
      WHERE [RECORD] = 1) As x

    select @tuid = [Uid],@tinstance = [instance] from #PTEMP where [instance] = 1
    set @i = 1

    While @i < @max
    BEGIN --Begin while
        SELECT @puid=[uid],@pinstance = [instance], @pid = [id],@pproject = [project],
                        @plocation = [location], @pstart=[ProjectStart], @pend = [ProjectEnd] FROM #ptemp
        Where [instance] = @tinstance and [uid]=@tuid
        Select @crecord =[record], @cid = [id], @clocation = [location], @cproject = [project], @cstart = [Start], @cend=[End]
        from [ProjectHistory] where [record] = @i
            SET @cuid = CONVERT(varchar(15), @cid)+@cproject+@clocation
        IF @pid = @cid
        Begin --First check if its the project instance
            IF(@cuid = @puid and DATEDIFF(day,@cstart,@pend)<=7)
            Begin --this happens if its the same project period
                update #PTEMP set [ProjectStart] = @cstart, [ProjectEnd] = @cend where [Uid] = @puid and [instance] = @pinstance
                set @i=@i+1
                set @tinstance = @pinstance
                set @tuid = @puid
            END -- done with updating existing project instance
            ELSE
            BEGIN  -- Since it didnt match pervious record must be new project instance
                select @tinstance = COUNT([instance])+1 from #ptemp where [uid] = @cuid
                insert into #PTEMP ( uid, instance,id, project, location, ProjectStart, ProjectEnd)
                values(@cuid,@tinstance,@cid,@cproject,@clocation, @cstart,@cend)
                set @i=@i+1
                set @tuid = @cuid
            End -- Done with the new project instance for same id
        END -- Done with same id
        ELSE -- this is a new id so insert uid for id project combo
        begin -- new id so need to insert first record
            select @tinstance = COUNT([instance])+1 from #ptemp where [uid] = @cuid
            insert into #PTEMP ( uid, instance,id, project, location, ProjectStart, ProjectEnd)
            values(@cuid,@tinstance,@cid,@cproject,@clocation, @cstart,@cend)
            set @i=@i+1
            set @tuid = @cuid
        END -- Done inserting new record for next id
    End -- End While

    select * from #ptemp

    drop table #PTEMP

    END


change journalCompressor to none - not default snappy Quote from ObjectRocket Support

  Hi, Fyodor! The journalCompressor setting is specifically related to
  the journal files. Journals are typically not compressed because
  compressing them causes additional latency when flushing the data from
  the WiredTiger memory cache to the disk. This latency trickles down to
  the write concern.
  journalCompressor none is a default value in our configurations.

ObjectRocket seems to understand something about DBaaS

  ObjectRocket is a cloud database (DBaaS) company based in Austin,
  Texas, specializing in NoSQL datastores including MongoDB and
  Redis. In 2013, they were acquired by Rackspace

What do you think about journalCompressor not snappy? Is this a valid config for DBaaS provider and best for most use cases?

storage:
   dbPath: "/data/mongodb"
   journal:
       enabled: true
   engine: "wiredTiger"
   wiredTiger:
       engineConfig:
           journalCompressor: none
           directoryForIndexes: "/indexes/mongodb/"
       collectionConfig:
           blockCompressor: snappy
       indexConfig:
           prefixCompression: true

What do you think about journalCompressor not snappy? Is this a valid config for DBaaS provider and best for most use cases?

In general I would leave MongoDB settings at the default unless variations have proven to be beneficial for your common workloads (i.e. through actual testing and measurement). Snappy compression/decompression typically does not have much overhead and I/O is often more of a resource constraint than CPU. The default settings are intended to be "best for most use cases" and are more widely tested/used in MongoDB deployments and continuous integration tests.

The size limit on WiredTiger journal files is 100MB; when the journal file size limit is reached WiredTiger creates a new journal file and syncs the previous journal file to disk. With the default journalCompressor setting of snappy individual journal files can contain more data than with no compression. Without compression, more journal files will be created and there will be more journal I/O.

The right tradeoff for CPU vs I/O usage will vary based on your deployments and workloads. If your deployments have CPU to spare, you could also consider testing zlib compression (expecting better compression but more CPU usage).

If you are concerned about I/O contention between the journal and data files in your dbPath or want to do finer tuning given journal writes are sequential rather than random, another option would be to symlink the journal directory to a separate mount point. This approach may affect your backup strategy (e.g. if you are relying on filesystem snapshots), however since you are already using the directoryForIndexes option I suspect you are already aware of this caveat.

For more configuration suggestions for production deployments, see the Production Notes in the MongoDB manual.


Avoid redundancy in a database for a tournament software? I'm about to write a tournament software. Therefor I have two db-tables in mysql database:
players and matches.
In matches I save every single match played in the tournament:

ID_player1, ID_player2, Goals_player1, Goals_player2

I would like to sum up all goals from all matches of every player, so I can display a ranking, where won mathes and goals and countergoals are displayed. Now the question is, how to realize this in the database: I see several options:

Save goals and countergoals in the players table. Every time a match is entered into the database, the goals and countergoals are added to the related player. (but what about redundancy..?)
Save goals and countergoals in the players table. But every time a match is entered into the database, run a query over the matches database and sum up all the goals every time again and again. This way I would at least avoid possible inconsistency.
Don't save goals and countergoals in players table. But every time I would like to display the players rank with all goals and so, there would be a humangous database query running over the whole matches table for every single player. This is probably not very smart when it comes to performance, is it?

So what is the best way to store that information..?
I appreciate every help or hint.
greets and thanks in advance!

Start by having a table with one row for each goal-like thingie.  It would include player_id, game_id, etc.  From that you can do SELECT COUNT(*) ... GROUP BY player_id, goal_type to get counts.

You can either do that 'live' when you need the goal counts, or you can use that SELECT to populate some kind of summary table.

After you have tried all of that, you will have all the data stored so that if the forumlas are wrong, you can repair the counts.

Then you can think about doing the alternatives.  Think of this as being a learning exercise.


Most Effificient Way to Store Ingredients in a Food Database I have a database that I've called "food" and I'm a bit stumped on how to store the ingredients for a recipe. Should I create a separate table for the ingredients or just have the ingredients insert into the Recipe table as a comma-delimited list? Different recipes will not be using the "same ingredients" in that, they won't be sharing a value such as onion. If 10 ten different recipes have onions, then onion will be in the database 10 times (is that smart?).

I think ingredients should go in a master table and another table to map ingredient to recipe. The following is the basic idea:

Create table recipe
(
    recipe_id int not null,
    recipe_name varchar2(50),
    constraint pk_recipe primary key (recipe_id)
);
Create table ingredient
(
    ingredient_id int not null,
    ingredient_name varchar2(50),
    constraint pk_ingredient primary key (ingredient_id)
);
Create table food_ingredient
(
    fk_recipe int not null,
    fk_ingredient int not null,
    measure number,
    unit_of_measurement varchar2(10)
);

Please note that the script above might have some errors due to syntax.

The advantage of this approach:

Finding food or recipe with similar ingredients
Finding food or recipe with a specific ingredient
Convert measurement, well you should incorporate some more tables to store unit of measurement and conversions. You've got the idea

Hopefully this would help.


Powershell: Does System.Data.SqlClient.SqlDataAdapter usange in powershell need some ADO.Net installation? In PowerShell, goal is to query some data from Sql Server and put that into a preformatted Excel file. Towards that, the following PowerShell script snippet, throws an error saying "assemblies are missing".

From what I could gather online, SqlClient.SqlDataAdapter is part of ADO.Net, which does not require including any assemblies (as it is always present in the OS .Net framework installation). Is this correct? How do I rectify this?

...
... other connection code
...
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand;
$SqlCmd.Connection = $SqlConnection;
$SqlCmd.CommandText = $ActiveTransactions_Count_Query;

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter;
$SqlAdapter.SelectCommand = $SqlCmd;

$DataSet_ActiveTransaction = New-Object System.Data.DataSet;
$SqlAdapter.Fill($DataSet_ActiveTransaction);
$DataSetTable = $DataSet_ActiveTransaction.Tables[0];
$DataSetTable

Error:

New-Object : Cannot find type [Sql.Data.SqlClient.SqlDataAdapter]: make sure
the assembly containing this type is loaded.
At M:\Build.ps1:21 char:25 + $SqlAdapter = New-Object <<<<
Sql.Data.SqlClient.SqlDataAdapter;
    + CategoryInfo          : InvalidType: (:) [New-Object],
    PSArgumentException
    + FullyQualifiedErrorId :     TypeNotFound,Microsoft.PowerShell.Commands.NewObjectCommand

Put this at the top of your script:

Add-Type -AssemblyName System.Data

Yes it's part of the OS, and no this is not normally required, but there must be some issue from the combination of your OS version + PowerShell version + SQL 2008 R2.


MySQL / MariaDB - Start multi-source replication with mariabackup (xtrabackup) Many times I successfully performed procedure described in the https://mariadb.com/kb/en/setting-up-a-replication-slave-with-mariabackup/ - it's fastest and easiest because replica is restored by copying files and binlog coordinates for CHANGE MASTER are saved in xtrabackup_binlog_info file.
With multi-source replication it seems to be not possible, because both backups from masters contain their own ibdata1 file - so I cannot restore them into single instance of MySQL (the future slave).
The only way I can think of is like that:

restore 1st master into slave
enable replication for selected databases from 1st master
restore 2nd master into second slave (temporary)
use mysqldump to move selected databases from temporary slave into main slave

The problem is that databases on both masters are large (~1 TB) and mysqldump/restore takes ages - and chasing few days of stalled replication (between backup and restore) takes additional significant amount of time. I'd really like to avoid this.
I know that with MyISAM tables I could just move table files but it's innodb and it cannot be changed.
Maybe there's some way to merge ibdata1 files? Or maybe export/import ibdata1 dictionaries related to chosen databases?

There's no tricks that can be done with ibdata1 files.
Look at using mydumper for parallel extraction and load.
pt-data-sync is a way to replicate the data with inbuilt protections against databsae delay. I don't know if it supports multisource


Log Drive getting filled up in SQL Server We are facing an issue with the Log Drive (E:) getting filled up frequently.

Our DBMS is SQL Server 2014 SP1 with AOAGs set up.

We have weekly maintenance tasks:

Rebuild Indexes (Runs close to 1 Hour)
Update Statistics (Runs Cloe to 30 Minutes)
Check DB runs close to 30 minutes
Full Backup runs close to 4 Hours
Differential runs close to 2 Hours

We are not seeing any heavy transaction processing.

But we can see E: getting filled up.

Below are the Auto-Growth Settings:

10% Growth with unlimited.

Note: Every 30 minutes we have transaction log backup scheduled as this is AOAG environment.

My questions:

In this case, what is making the E: getting filled up?
Why is my Log not getting truncated?
Do we need to expand E: in this case?
What should be the action plan to get my log truncated?

As of now we are shrinking file now and then to free up space before we decide next plan of expanding drive.

Please suggest.

I suggest you read this, as this is applicable in all scenario (even you have Always On Availability group) when your database is in full recovery model .

Why Does the Transaction Log Keep Growing or Run Out of Space?

Yes you should implement transaction log backup immediately.  Read this for details.

Best way to backup & truncate transaction logs on a schedule

Shrink will not help as your logs are not being truncated. If you are truncating manually and shrinking you will not be able to recover point in time. Moreover files will grow again and you have to keep doing this manually all the time. Once you pre grow your file based on your usage and set up scheduled transaction log backup you should not have to adjust file size, truncate manually or shrink file.

After you set this up then you can decide if you need to expand your E drive.

I also suggest change your transaction log autogrowth from percent to a fixed size. Read this.


Replace Character Without Using Looped REPLACE Function My SQL Server database input values follow a pattern of where the first position is a character and then followed by number. I want replace the first character with another character or characters . For example:

input

Q234567888
R264747848
B264712481

output

I234567888
I264747848
U264712481

Potential values for the first position are [A-Z].

I am looking for an option without using multiple REPLACE statement and not using CASE.

If I use CASE, I will have to check 26 cases or if I use REPLACE in a nested way, I may end up using  26 REPLACE statements.  I am trying to avoid these.

Please suggest any better way to do achieve the results.

Create a mapping between the old value and the new value in a table value constructor and use stuff to replace the character.

declare @T table(Value varchar(10));

insert into @T values
('Q234567888'),
('R264747848'),
('B264712481');

select stuff(T.Value, 1, 1, (
                            select V.NewValue
                            from (values('Q', 'I'),
                                        ('R', 'I'),
                                        ('B', 'U')) as V(OldValue, NewValue)
                            where V.OldValue = left(T.Value, 1)
                            ))
from @T as T;


Duplicate company id vs inner join Hello i'm designing a database for a saas. Lots of queries will be per company. I have hesitate between 2 way.
First way: Duplicated ids
Each table have a FK for the company id and select will contains a simple where on this row.
Second way: Using inner join
Each query will have some inner join to get the company id of the user.

First way seem cleaner to me, as we don't repeat the relation.
Second way seems more performant as there is less join.

Note that there is some tables that will require 5 or six joins to get to the company.
I'm looking for the best option in term of security, clean design, and good performances.
EDIT:
Added some examples as I was not that clear.
No many 2 many relation table to simplify
Company <- Contract <- Folder <- Document <- Note
``

Each table point to its parent. If I want all company's documents, or all company's notes, I need to make multiple join if I do not duplicate de company id in all table I must make multiple joins to accomplish this select.

One reason for normalization is to smartly decouple a table into multiple tables of related columns so only the needed set of columns are queried at a given time to maximize performance. If the CompanyId is a standard in most of your queries, then from a performance perspective it's not a bad idea to keep it in all relevant tables so you don't have to join multiple unrelated tables to the query, to get the Customer. But from a maintenance perspective it'll be more work to insert the same value into multiple tables as well. So it's a trade-off between performance and maintainability based on the number of tables your system will have.


SQL table design for primary keys (best practice) I have a User table that is made up of the the following columns:

ID (Auto-generated GUID)
UserName
Password
FirstName
LastName
Email
AddressID (GUID)

This is linked to the table Address (in a one-to-one [1-1] relationship) which contains the following columns

ID (auto-generated GUID)
Street
Other address fields

That seems to be the logical way of doing this (for me). But someone told me it may contain redundant column (AddressID) and that I should use EntityID instead of Address.ID.

There would be one less column, but the Address table would have its own ID column.

Which practice do you recommend? Is there one faster at execution? What's best in the context of parent/child relations? Is there an impact on performances?

I agree with Maess - if you have distinct entities, each of those ought to have their own ID columns. But I have a strong objections against using GUID's for your primary keys - or more specifically - for your SQL Server's clustering keys.

GUIDs may seem to be a natural choice for your primary key - and if you really must, you could probably argue to use it for the PRIMARY KEY of the table. What I'd strongly recommend not to do is use the GUID column as the clustering key, which SQL Server does by default, unless you specifically tell it not to.

You really need to keep two issues apart:

1) the primary key is a logical construct - one of the candidate keys that uniquely and reliably identifies every row in your table. This can be anything, really - an INT, a GUID, a string - pick what makes most sense for your scenario.

2) the clustering key (the column or columns that define the "clustered index" on the table) - this is a physical storage-related thing, and here, a small, stable, ever-increasing data type is your best pick - INT or BIGINT as your default option.

By default, the primary key on a SQL Server table is also used as the clustering key - but that doesn't need to be that way! I've personally seen massive performance gains when breaking up the previous GUID-based Primary / Clustered Key into two separate key - the primary (logical) key on the GUID, and the clustering (ordering) key on a separate INT IDENTITY(1,1) column.

As Kimberly Tripp - the Queen of Indexing - and others have stated a great many times - a GUID as the clustering key isn't optimal, since due to its randomness, it will lead to massive page and index fragmentation and to generally bad performance.

Yes, I know - there's newsequentialid() in SQL Server 2005 and up - but even that is not truly and fully sequential and thus also suffers from the same problems as the GUID - just a bit less prominently so.

Then there's another issue to consider: the clustering key on a table will be added to each and every entry on each and every non-clustered index on your table as well - thus you really want to make sure it's as small as possible. Typically, an INT with 2+ billion rows should be sufficient for the vast majority of tables - and compared to a GUID as the clustering key, you can save yourself hundreds of megabytes of storage on disk and in server memory.

Quick calculation - using INT vs. GUID as Primary and Clustering Key:

Base Table with 1'000'000 rows (3.8 MB vs. 15.26 MB)
6 nonclustered indexes (22.89 MB vs. 91.55 MB)

TOTAL: 25 MB vs. 106 MB - and that's just on a single table!

Some more food for thought - excellent stuff by Kimberly Tripp - read it, read it again, digest it! It's the SQL Server indexing gospel, really.

GUIDs as PRIMARY KEY and/or clustered key
The clustered index debate continues
Ever-increasing clustering key - the Clustered Index Debate..........again!
Disk space is cheap - that's not the point!


What's wrong with this update RANK query? It was updated only the first time I ran it, after that the users_tbl_rank stays the same.

$sql = "SET @r = 0; UPDATE users SET users_tbl_rank = @r := (@r + 1) ORDER BY users_tbl_points DESC";

In my table, users_tbl_rank and users_tbl_points are both integers.

Could anyone tell me what I'm doing wrong here? Thanks in advance.

I created a random sample of 30 rows based on the table above as follows:

DROP DATABASE IF EXISTS sample;
CREATE DATABASE sample;
USE sample
CREATE TABLE users
(id int not null auto_increment,
users_tbl_points int not null,
users_tbl_rank int not null default 0,
primary key (id),key (users_tbl_points));
INSERT INTO users (users_tbl_points) VALUES
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000));
SET @r = 0; UPDATE users SET users_tbl_rank = @r := (@r + 1) ORDER BY users_tbl_points DESC;
SELECT * FROM users ORDER BY users_tbl_points DESC;

Here is that output

mysql> DROP DATABASE IF EXISTS sample;
CREATE DATABASE sample;
USE sample
CREATE TABLE users
(id int not null auto_increment,
users_tbl_points int not null,
users_tbl_rank int not null default 0,
primary key (id),key (users_tbl_points));
INSERT INTO users (users_tbl_points) VALUES
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000));
SET @r = 0; UPDATE users SET users_tbl_rank = @r := (@r + 1) ORDER BY users_tbl_points DESC;
SELECT * FROM users ORDER BY users_tbl_points DESC;
Query OK, 1 row affected (0.04 sec)

mysql> CREATE DATABASE sample;
Query OK, 1 row affected (0.00 sec)

mysql> USE sample
Database changed
mysql> CREATE TABLE users
    -> (id int not null auto_increment,
    -> users_tbl_points int not null,
    -> users_tbl_rank int not null default 0,
    -> primary key (id),key (users_tbl_points));
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO users (users_tbl_points) VALUES
    -> (FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
    -> (FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
    -> (FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
    -> (FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
    -> (FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
    -> (FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
    -> (FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
    -> (FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
    -> (FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),
    -> (FLOOR(rand()*1000000)),(FLOOR(rand()*1000000)),(FLOOR(rand()*1000000));
Query OK, 30 rows affected (0.08 sec)
Records: 30  Duplicates: 0  Warnings: 0

mysql> SET @r = 0; UPDATE users SET users_tbl_rank = @r := (@r + 1) ORDER BY users_tbl_points DESC;
Query OK, 0 rows affected (0.00 sec)

Query OK, 30 rows affected (0.05 sec)
Rows matched: 30  Changed: 30  Warnings: 0

mysql> SELECT * FROM users ORDER BY users_tbl_points DESC;
+----+------------------+----------------+
| id | users_tbl_points | users_tbl_rank |
+----+------------------+----------------+
| 27 |           974504 |              1 |
|  3 |           942712 |              2 |
| 26 |           914442 |              3 |
| 23 |           867887 |              4 |
| 13 |           828882 |              5 |
| 12 |           809094 |              6 |
| 29 |           722458 |              7 |
| 14 |           717128 |              8 |
|  4 |           702457 |              9 |
|  1 |           691932 |             10 |
|  5 |           684147 |             11 |
|  8 |           631802 |             12 |
|  9 |           615874 |             13 |
|  7 |           514378 |             14 |
| 17 |           420973 |             15 |
| 24 |           360579 |             16 |
| 16 |           343592 |             17 |
| 22 |           326285 |             18 |
| 20 |           315427 |             19 |
|  6 |           313363 |             20 |
| 21 |           254513 |             21 |
| 30 |           224710 |             22 |
| 25 |           199236 |             23 |
| 10 |           183963 |             24 |
| 28 |           129194 |             25 |
| 19 |           107540 |             26 |
| 15 |            98995 |             27 |
| 18 |            74092 |             28 |
| 11 |            72197 |             29 |
|  2 |             3702 |             30 |
+----+------------------+----------------+
30 rows in set (0.01 sec)

mysql>

Let me update the scores

mysql> UPDATE users SET users_tbl_points = FLOOR(rand()*1000000);
Query OK, 30 rows affected (0.08 sec)
Rows matched: 30  Changed: 30  Warnings: 0

Let me rerank them

mysql> SET @r = 0; UPDATE users SET users_tbl_rank = @r := (@r + 1) ORDER BY users_tbl_points DESC;
Query OK, 0 rows affected (0.00 sec)

Query OK, 29 rows affected (0.06 sec)
Rows matched: 30  Changed: 29  Warnings: 0

Let me display them:

mysql> SELECT * FROM users ORDER BY users_tbl_points DESC;
+----+------------------+----------------+
| id | users_tbl_points | users_tbl_rank |
+----+------------------+----------------+
| 28 |           994943 |              1 |
|  7 |           983596 |              2 |
|  4 |           964909 |              3 |
| 24 |           921117 |              4 |
| 14 |           885399 |              5 |
|  8 |           881325 |              6 |
|  3 |           858830 |              7 |
| 11 |           808514 |              8 |
|  1 |           785523 |              9 |
| 26 |           751842 |             10 |
| 22 |           749931 |             11 |
| 21 |           705158 |             12 |
| 25 |           703038 |             13 |
| 27 |           650093 |             14 |
| 15 |           649229 |             15 |
| 10 |           635204 |             16 |
| 23 |           634182 |             17 |
| 16 |           589948 |             18 |
|  9 |           455836 |             19 |
|  2 |           443080 |             20 |
|  6 |           345553 |             21 |
| 13 |           259255 |             22 |
| 20 |           258620 |             23 |
|  5 |           248056 |             24 |
| 18 |           240429 |             25 |
| 19 |           195981 |             26 |
| 30 |           137355 |             27 |
| 12 |           136960 |             28 |
| 29 |            24437 |             29 |
| 17 |             2055 |             30 |
+----+------------------+----------------+
30 rows in set (0.00 sec)

mysql>

Looks like it works to me !!!

CAVEAT

Make sure you have an index on the users_tbl_points column


SQL Server Replication: "ALTER TABLE ALTER COLUMN" is not propagated to subscribers We are running SQL Server 2008 R2 SP1 as publisher & distributor, and SQL Server 2005 SP3 as subscriber. The replication of schema changes is activated, and the replication has been running for years, including frequent schema changes (new column, new constraints, etc).

The following instruction was sent on the publisher:

use myDatabase
alter table Tbl_TypeLignePaye
      alter column natureTypeLignePaye nvarchar(12)
go

where field natureTypeLignePaye was originally nvarchar(3) null

The query ran without errors on the main database. The result is the following:

The field natureTypeLignePaye still appears as nvarchar(3) in the object browser
The column properties screen indicates a nvarchar type with a length of 12
The change was not propagated to the subscribers

Any idea on what is going on with this database?

Publisher: object browser window vs property window give incoherent data

If anyone wants to increase the column width of the replicated table in SQL Server 2008, then no need to change the property of replicate_ddl=1. Simply follow the steps as given.

Open SSMS
Connect to Publisher database
run command

ALTER TABLE [Table_Name] ALTER COLUMN [Column_Name] varchar(22)

It will increase the column width from varchar(x) to varchar(22) and same change you can see on subscriber (transaction got replicated). So no need to re-initialize the replication

Hope this will help all who are looking for it.


SQL Server 2012 Availability Group is it "AlwaysON"? In a traditional SQL Server cluster when failover occurs, all clients that are connected to the SQL Server failed instance lose connections and each client must re-establish a new connection to failover cluster instance.

Does AlwaysON Availability Group mitigate this problem? Is failover in case of SQL Server 2012 AlwaysON availability group transparent to clients connecting to the SQL Server?

No.  During a failover, clients will notice.  Your connection to the SQL Server will be broken, and you'll have to retry your transaction.


SQL - Passing a Stored Procedure Variable as a Parameter in a crystal report for end users to select OK I know there are hundreds of posts out there that state how to pass a parameter from a stored procedure to a crystal report. But unfortunately with some research I haven't come across a specific answer to my question with regards to SQL Server variables and crystal report parameters.

Question: If I have a stored procedure and I am declaring a variable called "Location" and I want to reference that variable in my crystal report as a new parameter and end users to be presented a dialog box that allows them to select from various locations and that would in return results only for a specific location chosen from a drop down. How would that occur?

Methods tried so far:

Crystal Report Builder 11

In "parameter fields" under field explorer, select new and then choose "List of Values" to by Dynamic and choose the data source and column that's been used with my variable and it says "Parameters" is @location. But when I run the report I am presented with a dialog box for a location value but there are no values to select from. (To make a note the location field is being supplied by the users drop down selection via the report thus returning results from the stored procedure)
In "parameter fields" select new and then leave list of values as "static" and choose value field as column "location" and enter multiple values with regards to locations for the user to select from. (This does present the user with a dialog box suggesting drop down values for a location but no results returned so I was unsure if this was the proper way?)

Code:

ALTER PROCEDURE [dbo].[a_ras_TruckSummary_SP]

AS
BEGIN

IF (object_id('tempdb..#tempTruckSummary') IS NOT NULL)
BEGIN
    DROP TABLE #tempTruckSummary
END

DECLARE @location VARCHAR(10)

 SELECT O.order_no, L.shipped, L.price, L.[location] FROM orders O WITH
(NOLOCK)
INNER JOIN Ord_list L ON O.order_no = L.order_no
    WHERE O.routing NOT LIKE 'FEDX%'
    AND O.routing NOT IN ('UPS', 'UPS 1', 'UPS 2', 'UPS 3')
    AND ISNULL(O.void,'') <> 'V'
    AND L.location = @location
    GROUP BY O.order_no, L.shipped, L.price, L.[location]

Ok here are the steps I took to reference parameters from my crystal report to the stored procedure.

Add parameter declarations in stored procedure like so:

ALTER PROCEDURE [dbo].[a_ras_TruckSummary_SP]

@location VARCHAR(10),

@dateshipped DATETIME
Then navigate to crystal report builder and select "Database>>>>Database Verify". This will refresh the database connection and present your parameters as a dialog box. From there you will enter your values to test the parameters
If they test ok the dialog box will be closed and you can then navigate to "field explorer" and under "parameter fields" you will now see your new parameters.
Then you can simply drag them over to the report.


Replication Server version 15.7.1 - Sybase ASE 15.5 to Oracle EE 12.1.0.2 (64 bit) replication failing I have 2 Sybase DB and 1 Oracle DB, I would like to replicate one of the tables in Sybase DB X (table name: customer_order) into my Sybase DB Y (table name: customer_order), and from the Sybase DB Y replicate into Oracle. The replication linking is like this:-

Sybase DB X --> Sybase DB Y --> Oracle

The replication from Sybase DB X to Sybase DB Y is working fine, but replication failed from Sybase DB Y to Oracle. When I tried to insert/delete/update some records on Sybase DB Y, it has been replicated to Oracle, but...when I tried to insert/delete/update some records on Sybase DB X, these transactions are able to replicate to Sybase DB Y, but it couldn't replicate into Oracle. WHY??

Appreciate if anyone could solve my doubts. Thanks a lot.

FYI:-

Replication Server version 15.7.1
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
Adaptive Server Enterprise/15.5/EBF 19395 SMP ESD#5/P/Sun_svr4/OS 5.8/asear155/2568/64-bit/FBO/Fri Dec 9 09:16:52 2011

By default the repagent (in Sybase DB Y) will not replicate maintenance user transactions (ie, transactions replicated from Sybase DB X to Sybase DB Y).

To get the repagent (Sybase DB Y) to also replicate maintenance transactions (ie, transactions from Sybase DB X, once applied in Sybase DB Y, are then re-replicated to Oracle), you'll need to configure the Sybase DB Y repagent:

use <Sybase_DB_Y>
go
sp_config_rep_agent <Sybase_DB_Y>,'send maint xacts to replicate',true
go
sp_stop_rep_agent <Sybase_DB_Y>
go
-- wait for repagent to go down and then ...
sp_start_rep_agent <Sybase_DB_Y>
go


Mongodb: how to move a member to a backup site? It is stated in the docs, that any replica set should have an odd number of members. Let's say I have a primary site with 3 members. I need to move one member to a backup site. In order to do this, do I need to first add a new member on the backup site, and them remove a member on the primary site? What is the official procedure? I'm very confused, because by the time the member is added, there will have been an even number of members. Where will I go from there? How dangerous it is to add a single member to a replica set without doing any hanky-panky?

Should I do some magic tricks (e.g. set priorites of particular members to zero) before I add a new member?

Is this documented somewhere?

Where you found that information that replica should have even number of members, because it is ODD number. With even number of members (votes) you could end up situation that you don't have majority. IF you have even number of data nodes, you need arbiter as fifth.

OK, to the question how to move one member. Let's assume that you have three nodes (primary + 2x secondary). If you want to be sure that your replica set stays up even you lost one member during that time that when one secondary is "on-the-move" and not up'n'running, bring one Arbiter to replica set. You can install arbiter to "any" other server, because it will not take resources (basically). You give command rs.addArb("address:port") just before you shutdown that secondary. After you start again that secondary at new location, you give command rs.remove("address:port"). That way you always have three votes online.


Wrong estimated number of rows using TOP condition I guess this is a very basic question but I can't get my head around it..
I have perfect statistics in place for a column but while using a TOP condition the estimated number of rows are always a number very close to the number used for the TOP operator giving me a complete wrong number.
Is there any easy explanation for this knowing that the statistics for the InterviewerID on the application table know exactly that theID 12868 rows have more than 3K rows ? Why is telling me 13.83?

Version being used: Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64)

Thank you all!

How I call the SP ?
EXEC agy.sp_searchTest3 @interviewerId = 12868,@searchTerm = 'aar'

Query causing the issues :

ALTER PROCEDURE [agy].[sp_searchTest3] (
    @interviewerId INT
    ,@searchTerm VARCHAR(50) = NULL
    ,@searchApplicationId INT = NULL
    )
AS
BEGIN
    IF @searchApplicationId IS NOT NULL
        SELECT TOP 10 u.userId
            ,u.firstName + ' ' + u.lastName AS 'fullname'
            ,app.applicationId
            ,u.loginId AS email
            ,adi.URI AS imageurl
        FROM [app].[application] AS app
        INNER JOIN [app].[applicant] AS a ON a.applicantId = app.applicantId
        INNER JOIN [usr].[user] AS u ON u.userId = a.userId
        LEFT JOIN upl.applicationDocuments AS ud ON ud.applicationId = app.applicationId
            AND ud.documentTypeId = 5 -- (Portrait pic)
            AND ud.documentStateId NOT IN (
                3
                ,5
                ) -- (Rejected, Deleted)
        LEFT JOIN upl.applicationDocumentImages AS adi ON adi.documentId = ud.documentId
        WHERE app.interviewerId = @interviewerId
            AND u.isActive = 1
            AND app.applicationId = @searchApplicationId

    IF @searchTerm IS NOT NULL
        SELECT
        TOP 15 -- everytime I change the top value the estimated number of rows change as well
        u.userId
            ,u.firstName + ' ' + u.lastName AS 'fullname'
            ,app.applicationId
            ,u.loginId AS email
            ,adi.URI AS imageurl
        FROM [app].[application] AS app
        INNER JOIN [app].[applicant] AS a ON a.applicantId = app.applicantId
        INNER JOIN [usr].[user] AS u ON u.userId = a.userId
        LEFT JOIN upl.applicationDocuments AS ud ON ud.applicationId = app.applicationId
            AND ud.documentTypeId = 5 -- (Portrait pic)
            AND ud.documentStateId NOT IN (
                3
                ,5
                ) -- (Rejected, Deleted)
        LEFT JOIN upl.applicationDocumentImages AS adi ON adi.documentId = ud.documentId
        WHERE app.interviewerId = @interviewerId
            AND u.isActive = 1
            AND (
                (u.firstName LIKE @searchTerm)
                OR (u.lastName LIKE @searchTerm)
                OR (u.loginId LIKE @searchTerm)
                )
END

13.83 is the number of rows it thinks it needs to satisfy the 15 rows you're asking for. It guesses that after that it can stop.

Paul White goes into it a lot more in his post Inside Optimizer Row Goals in Depth.

Since he spends a lot of time at this site you'll probably see an extensive answer, going into a lot of detail about why it's 13.83 and not 13.84. ;)

I suggest you to read below blogs

Why Top1 Killed My Performance you'll also see some reasons why having row goals can hurt.
SSIS Tuning tips That Everyone Misses


Why is the Group by and Having clause very slow with this query? I have this query which is when run against 100K rows, takes forever to result in my system with 2 GB Ram and standard MySQL installation.

Now in my database , I have two tables, chitentry and chitentrygroup. Both contain more then 100K rows. When I run the following query the result is always pending and I do not get any output and the query keeps running.

    SELECT  SQL_CALC_FOUND_ROWS  chitentrygroupid,
                       chitentrygroupdate,
                       chitentryno,
                       chitbookno,
                       chitamount,
                       entryamount,
                       entryweight,
                       employeename,
                       accountname,
                       rate,
                       monthentry
             FROM   (SELECT chitentrygroup.chitentrygroupid,
           chitentrygroup.chitentrygroupdate,
           chitentry.chitentryno,
           chitentry.chitbookno,
           Round(( chitentry.chitamount ), 0) AS chitamount,
           chitentry.entryamount,
           chitentry.entryweight,
           employee.employeename,
           account.accountname,
           chitentry.rate,
           chitentry.monthentry
    FROM   chitentrygroup
           LEFT OUTER JOIN chitentry
                        ON chitentry.chitentrygroupid =
                           chitentrygroup.chitentrygroupid
           LEFT OUTER JOIN employee
                        ON employee.employeeid = chitentrygroup.employeeid
           LEFT OUTER JOIN account
                        ON account.accountid = chitentrygroup.accountid
         WHERE  chitentrygroup.status NOT IN ( 0, 3, 8 )  AND chitentrygroup.industryid IN ( 3 )
           AND 1 = 1
           AND 1 = 1
           AND 1 = 1
    GROUP  BY 1
    HAVING 1 = 1
    ORDER  BY chitentrygroup.chitentrygroupid DESC ) AS t
       LIMIT  0, 10

Now when I remove SQL_CALC_FOUND_ROWS , Group By and Having  Part of the query. The query is blazing fast. The presence of any of the above I stated makes the query stuck with this big table.

How should I optimize this query with Group By,Having and SQL_CALC_FOUND_ROWS  Clause?

Get rid out the outer layer:

SELECT  SQL_CALC_FOUND_ROWS  ...
    FROM (SELECT ...
            ORDER  BY chitentrygroup.chitentrygroupid DESC
         ) AS t
   LIMIT  0, 10

-->

SELECT  SQL_CALC_FOUND_ROWS  ...
   ORDER  BY chitentrygroup.chitentrygroupid DESC
   LIMIT  0, 10

This will also fix a bug you may not have noticed.  A derived table returns an unordered set of rows.  That is, the optimizer is permitted to ignore the ORDER BY in the formulation you have.  This gives you not the first 10 rows, but some arbitrary 10 rows.

FOUND_ROWS is passe.  Have you noticed that search engines first moved to "about 1,000,000 hits", then got rid of the count?  Do you really need the count?

What indexes do you have on chitentrygroup?  In particular, this one may be beneficial:

INDEX(industryid, status)

Are you using InnoDB?  What is the value of innodb_buffer_pool_size.  For a tiny 2GB machine (or VM), it should be no more than 400M.

What version of MySQL are your running?  Old versions defaulted that to 8M or 128M.  These are too small.

What are the mappings?  Is there one employee per chitentrygroupid?  That is "1:many".  What about account and chitentry?  If these are each 1:many, you don't need to GROUP BY -- which is the real villain.

That is, if you can SELECT the chitentrygroupids, get through the ORDER BY and LIMIT without needing the ORDER BY, you avoid the "inflate-deflate" that happens with JOIN + GROUP BY.


How to parse query plans to get a count of nested loop operators with unordered prefetch? My boss wants me to parse a set of query plans stored in a table and to determine how many nested loop operators with unordered prefetching are present in each plan. I only have around 100 query plans so performance isn't very important. I tried doing it myself but quickly got confused and couldn't make progress.

The structure of the table:

DROP TABLE IF EXISTS dbo.query_plans;

CREATE TABLE dbo.query_plans (
plan_name VARCHAR(100),
query_xml XML
);

I uploaded T-SQL to add three example query plans to the table on pastebin. This is the output that I'm looking for:

╔═════════════╦════════════════╗
║  plan_name  ║ OPERATOR_COUNT ║
╠═════════════╬════════════════╣
║ NO_PREFETCH ║              0 ║
║ 1_PREFETCH  ║              1 ║
║ 2_PREFETCH  ║              2 ║
╚═════════════╩════════════════╝

I can't answer any questions as to why I need to do this. Thanks!

This will Work Perfectly®

WITH XMLNAMESPACES ( 'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p )
SELECT qp.plan_name,
       qp.query_xml.value('count(//p:RelOp/p:NestedLoops/@WithUnorderedPrefetch)', 'int') AS operator_count
FROM dbo.query_plans AS qp;

If you prefer, you could use

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

...and omit the namespace prefix p: from the XQuery expression.


Mongodb 2.6 Server crash and not restarting We are using mongodb 2.6 with one Primary and 2 secondaries. One of the secondary has gone bad  and not restarting.

2014-04-18T21:13:34.765+0000 [rsSync] replSet still syncing, not yet to minValid optime 535185c4:fe
2014-04-18T21:13:34.766+0000 [rsBackgroundSync] replset setting syncSourceFeedback to server134:27017
2014-04-18T21:13:34.880+0000 [rsSync] replSet SECONDARY
2014-04-18T21:13:34.972+0000 [rsSync] SEVERE: Invalid access at address: 0x7f9694fdc13c
2014-04-18T21:13:34.982+0000 [rsSync] SEVERE: Got signal: 7 (Bus error).
Backtrace:0x11bd301 0x11bc6de 0x11bc7cf 0x7fb08d53fcb0 0xf1d43a 0x8aef63 0xf0e4a7 0xf14efe 0xf14f6f 0xf1e02f 0xf1d9c0 0x8afd8f 0x8b1c5a 0xe42251 0xe9b0eb 0xea52e4 0xea5c6a 0xea5da0 0xea6098 0x1201c99
 /usr/bin/mongod(_ZN5mongo15printStackTraceERSo+0x21) [0x11bd301]
 /usr/bin/mongod() [0x11bc6de]
 /usr/bin/mongod() [0x11bc7cf]
 /lib/x86_64-linux-gnu/libpthread.so.0(+0xfcb0) [0x7fb08d53fcb0]
 /usr/bin/mongod(_ZN5mongo17RecordStoreV1Base12deleteRecordERKNS_7DiskLocE+0xea) [0xf1d43a]
 /usr/bin/mongod(_ZN5mongo10Collection14deleteDocumentERKNS_7DiskLocEbbPNS_7BSONObjE+0x633) [0x8aef63]
 /usr/bin/mongod(_ZN5mongo16NamespaceDetails11cappedAllocEPNS_10CollectionERKNS_10StringDataEi+0x267) [0xf0e4a7]
 /usr/bin/mongod(_ZN5mongo16NamespaceDetails6_allocEPNS_10CollectionERKNS_10StringDataEi+0x2e) [0xf14efe]
 /usr/bin/mongod(_ZN5mongo16NamespaceDetails5allocEPNS_10CollectionERKNS_10StringDataEi+0x5f) [0xf14f6f]
 /usr/bin/mongod(_ZN5mongo19CappedRecordStoreV111allocRecordEii+0x3f) [0xf1e02f]
 /usr/bin/mongod(_ZN5mongo17RecordStoreV1Base12insertRecordEPKcii+0x60) [0xf1d9c0]
 /usr/bin/mongod(_ZN5mongo10Collection15_insertDocumentERKNS_7BSONObjEbPKNS_16PregeneratedKeysE+0x7f) [0x8afd8f]
 /usr/bin/mongod(_ZN5mongo10Collection14insertDocumentERKNS_7BSONObjEbPKNS_16PregeneratedKeysE+0x22a) [0x8b1c5a]
 /usr/bin/mongod(_ZN5mongo11_logOpObjRSERKNS_7BSONObjE+0x341) [0xe42251]
 /usr/bin/mongod(_ZN5mongo7replset8SyncTail15applyOpsToOplogEPSt5dequeINS_7BSONObjESaIS3_EE+0x4b) [0xe9b0eb]
 /usr/bin/mongod(_ZN5mongo7replset8SyncTail16oplogApplicationEv+0x4f4) [0xea52e4]
 /usr/bin/mongod(_ZN5mongo11ReplSetImpl11_syncThreadEv+0x14a) [0xea5c6a]
 /usr/bin/mongod(_ZN5mongo11ReplSetImpl10syncThreadEv+0x30) [0xea5da0]
 /usr/bin/mongod(_ZN5mongo15startSyncThreadEv+0xa8) [0xea6098]
 /usr/bin/mongod() [0x1201c99]

Is there a way by which we can start the secondry to work or workaround this bug?

I just know a workaround: manually resync the member of the replica set.

Basically, it is done like this:

Shutdown the mongod instance in question
Delete the contents of the dbpath directory
Restart the instance. It will sync then


TokuDB not much faster than MySQL I have converted a MySQL database with 80.000.000 rows to TokuDB.

Now when I run:

 select count(id) from xxx where active=1

it takes 90% of the time of the normal MySQL request.

What do I have to further optimize so that it runs faster?

The table definition:

CREATE TABLE `adsDelivered` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `uid` varchar(40) NOT NULL,
  `_adsDelivered` bigint(20) NOT NULL DEFAULT '0',
  `_campaign` bigint(20) NOT NULL DEFAULT '0',
  `_ad` bigint(20) NOT NULL DEFAULT '0',
  `session` varchar(44) NOT NULL,
  `referer` text NOT NULL,
  `refererDomain` varchar(256) NOT NULL,
  `pageTime` int(11) NOT NULL DEFAULT '0',
  `pageVisibleTime` int(11) NOT NULL DEFAULT '0',
  `browser` varchar(256) NOT NULL,
  `ip` varchar(15) NOT NULL,
  `clicks` int(11) NOT NULL DEFAULT '0',
  `clickTimeLast` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `tag` varchar(256) NOT NULL,
  `countryShort` varchar(2) NOT NULL,
  `timeCreated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `timeUpdated` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

  PRIMARY KEY (`id`),
  UNIQUE KEY `uid` (`uid`),
  KEY `_campaign` (`_campaign`),
  KEY `_ad` (`_ad`),
  KEY `_adsDelivered` (`_adsDelivered`),
  KEY `session` (`session`),
  KEY `tag` (`tag`),
  KEY `ip` (`ip`),
  KEY `countryShort` (`countryShort`),
  KEY `refererDomain` (`refererDomain`)
) ENGINE=TokuDB AUTO_INCREMENT=7420143 DEFAULT CHARSET=utf8;

I work for Tokutek. The answers here are mostly good. As Justin mentions, you need the right index, and your schema probably does not have the right index. I am happy to hear TokuDB was a little faster than InnoDB, but for table scans, assuming the table is not aged, it can go either way.

Here is a talk I gave on indexing that you may find helpful: http://www.youtube.com/watch?v=vaGAoK66ctM.

The first half is on indexing, the second half is a bit of a technical dive into fractal trees on a whiteboard. Hopefully this helps you with index design. I highly recommend understanding clustering secondary indexes, which TokuDB provides.

On other points. RolandoMySQLDBA is mostly correct on how InnoDB and TokuDB perform. Here is how to think about the performance of TokuDB. While the dataset fits in memory, TokuDB's fractal trees do not have any inherent advantage over InnoDB or other B-Tree based storage engines. The bottleneck, or rather the cliff, hits when data is big and does not fit in main memory. Where InnoDB's write performance, and write performance of other B-tree based storage engines, fall off a cliff, TokuDB's performance stays level. THAT is the indicator that you are getting something out of TokuDB. TokuDB will not take some well running existing system and supercharge its performance. TokuDB will take systems work well in memory but start to break down when falling out of memory, and ensures those systems perform well as data grows. That is what is going on with benchmarks that Percona shows, that is what is going on with iiBench (http://www.tokutek.com/resources/benchmarks/#iiBench).

Combine this write performance with TokuDB's compression, and suddenly clustering indexes, as explained in the indexing talk, become relatively cheaper. Maintaining more better indexes becomes cheaper. Use better indexes, a lot of I/O from queries may dissappear, and query throughput improves. This is how one can benefit from TokuDB.


Discover missing foreign keys and/or indexes We have a pretty big database (around 700GB of data), which has been inherited to us, and has had several DBAs. With time we've realized that there are several missing foreign keys and indexes.

I'd like to know if there is some automated way of discovering such potential missing keys/indexes using some kind of log parsing or something alike.

I remembered seeing a query sometime ago in a post so quick searching resulted in:

CREATE FUNCTION pg_temp.sortarray(int2[]) returns int2[] as '
  SELECT ARRAY(
      SELECT $1[i]
        FROM generate_series(array_lower($1, 1), array_upper($1, 1)) i
    ORDER BY 1
  )
' language sql;

  SELECT conrelid::regclass
         ,conname
         ,reltuples::bigint
    FROM pg_constraint
         JOIN pg_class ON (conrelid = pg_class.oid)
   WHERE contype = 'f'
         AND NOT EXISTS (
           SELECT 1
             FROM pg_index
            WHERE indrelid = conrelid
                  AND pg_temp.sortarray(conkey) = pg_temp.sortarray(indkey)
         )
ORDER BY reltuples DESC
;

From http://mlawire.blogspot.nl/2009/08/postgresql-indexes-on-foreign-keys.html

That will give you what you want.

And also found https://github.com/pgexperts/pgx_scripts/blob/master/indexes/fk_no_index.sql but I haven't used this myself.


Delete records from table A where col 1 and col2 are in table B I am wanting to delete records from a table if multiple columns in this table are in another table.
Currently, I usually just concatenate the columns and use the WHERE IN method:
DELETE FROM TableA
WHERE Col1+Col2 IN (SELECT Col1+Col2 FROM TableB)

Is there a better way to achieve the same result?
Thanks.

Typically, you'd want to join to the two tables together, and delete rows from TableA that match rows in TableB.  Something like this:
DELETE a
FROM TableA a
    INNER JOIN TableB b ON a.Col1 = b.Col1
        AND a.Col2 = b.Col2;

Using the method you show in your question might result in the wrong rows being deleted.  Here's an example to show what I mean:
IF OBJECT_ID(N'tempdb..#TableA', N'U') IS NOT NULL
BEGIN
    DROP TABLE #TableA;
END
IF OBJECT_ID(N'tempdb..#TableB', N'U') IS NOT NULL
BEGIN
    DROP TABLE #TableB;
END

CREATE TABLE #TableA
(
    i varchar(5) NOT NULL
    , j varchar(5) NOT NULL
);

CREATE TABLE #TableB
(
    i varchar(5) NOT NULL
    , j varchar(5) NOT NULL
);

INSERT INTO #TableA (i, j)
VALUES ('asdf', 'lk')
    , ('asd', 'flk');

INSERT INTO #TableB (i, j)
VALUES ('as', 'dflk')
    , ('asd', 'flk');

First, we'll see what values are in both tables:
SELECT *
FROM #TableA;

SELECT *
FROM #TableB;

╔══════╦═════╗
║  i   ║  j  ║
╠══════╬═════╣
║ asdf ║ lk  ║
║ asd  ║ flk ║
╚══════╩═════╝

╔═════╦══════╗
║  i  ║  j   ║
╠═════╬══════╣
║ as  ║ dflk ║
║ asd ║ flk  ║
╚═════╩══════╝
The intention would be to delete the bottom row, while retaining the top row, in TableA.  However, if we run this:
DELETE FROM #TableA
WHERE #TableA.i + #TableA.j IN (SELECT #TableB.i + #TableB.j FROM #TableB);

SELECT *
FROM #TableA;

We'll see both rows are gone from TableA.
This statement, however, results in only the correct row being removed from TableA:
DELETE a
FROM #TableA a
    INNER JOIN #TableB b ON a.i = b.i
        AND a.j = b.j;
SELECT *
FROM #TableA;

Results:
╔══════╦════╗
║  i   ║ j  ║
╠══════╬════╣
║ asdf ║ lk ║
╚══════╩════╝


How to count unique records within a time range in MySQL? I have a table with the below definition:

CREATE TABLE visit_log ( id INT NOT NULL , door_entry TIMESTAMP NOT NULL);

This table has the below records:

insert into visit_log values(1, '2009-04-15 15:00.000');
insert into visit_log values(1, '2009-04-15 15:20.000');
insert into visit_log values(1, '2009-04-15 15:55.000');
insert into visit_log values(2, '2009-04-15 15:00.000');
insert into visit_log values(2, '2009-04-15 15:40.000');
insert into visit_log values(2, '2009-04-15 16:20.000');
insert into visit_log values(3, '2009-04-15 15:00.000');

Now I want to find that how many times a person has visit_log within each 30 minutes, I mean all the visits at the range of 30 minutes should be counted as 1 visit, my desired result is shown below:

id         visits
 1            2
 2            3
 3            1

id 1 gets two visits because the first two visits are counted as one visit as they happened within the time span of 30 minutes, id 2 has three visits because all of three visits came in the time difference of more than 30 minutes and the id 3 has one visit.

Any help is appreciated in advance.

This query will give you the answer you are looking for:

select s.id, count(*) as visits from
(select distinct id, concat(hour(door_entry), floor(minute(door_entry)/30))
from visit_log) s group by s.id


Equivalent SQL Server's Backup COPY ONLY in DB2 In, DB2 when you have a recovery strategie, you have a Full backup, then some deltas or incrementals in between. They are creating based on the previous full (first delta, or any incre.

-F---D---D---I---D---D---D

However, if you perform a backup in the middle (let's suppose for a test, and the file will be deleted), then the following deltas or incrementals will be created based of that backup that was not part of the recovery strategie.

-F-
  F--D---D---I---D---D---D

I saw that SQL Server has an option calle COPY ONLY that prevents this problem: http://technet.microsoft.com/en-us/library/ms191495.aspx

How can I do that in DB2?

As far as I know you can't do that with DB2. Deltas and Incrementals will be based off of the last full backup taken (either offline or online). If anything it would cause one to adjust your recovery strategy for that week.

If you needed the data however, why not just copy the backups you need (the full and any deltas/incrementals) and restore those elsewhere, rather than taking a full backup? Or use db2look and db2move to extract the data for test purposes?

Otherwise I know IBM does have a product called IBM DB2 Advanced Recovery Feature (it was formerly an Optim product if I remember right). It has a subcomponent called  IBM DB2 Merge Backup for Linux, UNIX and Windows that allows you to combine the full backup with subsequent deltas and incrementals to create a full backup to restore to. This gives you the advantage of only needing to restore one backup, rather than progressing through the deltas and incrementals.


Oracle How to Generate Multiple Ranges Based on a Result Set Given a table like this:

with qtys as (
  select 3 as qty from dual union all
  select 10 as qty from dual union all
  select 9 as qty from dual union all
  select 7 as qty from dual union all
  select 9 as qty from dual union all
  select 3 as qty from dual
)

I want to find the greatest common value of QTY.

I have thought of transforming each of these into a range and then getting the value from the result set that has the same count as the count in the table QTYS.

Only I'm not certain how to do this, and I'd like to do it with plain SQL in Oracle 11g, not a DECLARE...BEGIN...END PL/SQL block.

I've searched around and found many different methods of generating a single range based on a number, but I haven't found anything that would generate multiple ranges from zero to the value in the table and union them together as a single result set.

For example, given the above table I think if I could turn it into this:

QTY
---
 1
 2
 3
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 1
 2
 3
 4
 5
 6
 7
 8
 9
 1
...

Then I could find the count(1) of each of these and match it to the count(1) of the table QTYS, and use that value:

select count(1)
, qty
from (
  -- resultant ranges
)
group by qty
having count(1) = (
  select count(1)
  from qtys
);

In the case of the example table above, QTYS, the result of this query would be 3.

Maybe there is a better way to do this that I'm not thinking of, but I'm not coming to any conclusions yet.

Any thoughts?

Solved this with help from https://stackoverflow.com/questions/3664903/how-can-i-return-multiple-identical-rows-based-on-a-quantity-field-in-the-row-it

The resulting answer with the original table looks like this:

with qtys as (
  select 3 as qty from dual union all
  select 10 as qty from dual union all
  select 9 as qty from dual union all
  select 7 as qty from dual union all
  select 9 as qty from dual union all
  select 3 as qty from dual
)
, results as (
  select n qty
  from qtys x
  join (
    select rownum n
    from dual
    connect by level <= (
      select max(qty)
      from qtys
    )
  ) y on y.n <= x.qty
)
select max(qty) qty
from (
  select count(1)
  , qty
  from results
  group by qty
  having count(1) = (
    select count(1)
    from qtys
  )
);

I'm certain I can condense it a little, but it produces the desired result.

EDIT

I suppose looking closer at this taking the minimum would have produced the same, desired result:

with qtys as (
  select 3 as qty from dual union all
  select 10 as qty from dual union all
  select 9 as qty from dual union all
  select 7 as qty from dual union all
  select 9 as qty from dual union all
  select 3 as qty from dual
)
select min(qty) qty
from qtys;