How to boost performance of virgin queries in MS SQL Server? I have ASP.NET web site that does it's own independent caching of data and data does not change for long periods of time, so it does not need to query SQL Server second time with same query. I need to improve performance of first time (virgin) queries that go to that  SQL Server. Some queries process so much data that they may cause SQL Server to use tempdb. I don't use temp table variables or temp tables, so SQL Server decides to use tempdb by itself whenever it needs to.

My db size is 16Gb, I have 32Gb of physical RAM available on my server machine.

I understand that MS SQL Server caching strategy tries to keep data in RAM to speed-up performance of similar queries if they need same data to be loaded again. In addition to that it will try to use available RAM instead of tempdb to speed-up performance without causing disk access.

I suppose that when query that needs to store something in tempdb SQL Server comes and there is not enough RAM available, SQL Server has 2 choices:

1) to unload some cached data and use spared RAM instead of tempdb to avoid disk writes

2) keep cached data for future queries and start using tempdb, which causes writes to slow disk.

I don't know what choice SQL Server will make in this situation, but I would like it to make choice #1 because I care only about performance of first-time (virgin) queries, because I never send same query to SQL Server again (though I may send similar query).

What is SQL Server caching strategy for this scenario ?

How does it balance usage of RAM between avoiding tempdb for virgin queries and speed of second time queries ?

Is it possible to configure SQL Server in such a way that it will make choice #1 ? If yes then how ?

How else can I boost performance of all virgin SQL queries ?

Since I don't know about SQL Server caching strategy I want to place database on RAM Disk. This will make sure that any virgin query has high speed of loading of uncached data even if SQL Server always makes choice #1. The risk of it is that SQL Server may start using more tempdb with less available RAM (only 16Gb left after I use 16Gb for RAM Disk) if it keeps making choice #2, which will slow down those virgin queries that cause spills into tempdb.

I am interested in solution for SQL 2008 R2, but I guess it's probably the same for SQL 2008, SQL 2005 and may be SQL 2000.

Clarifications:

There are no other applications running on that box, it's dedicated to SQL Server. Website runs on separate box.

It's SQL Server 2008 R2 Standard Edition 64 bit on Windows Server 2008 R2 Enterprise 64 bit.

I run only read-only queries and database is set to be read-only.

Let's assume that there are already good indexes. This question is about SQL Server making choice #1 vs choice #2, how does it make it, if there is a way to control it and if RAM Disk helps it to make the right choice for virgin queries.

Your question can be basically rephrased as 'How does the query memory grant work?'. A good read on the subject is Understanding SQL server memory grant. Before a query is launched into execution it may require a memory grant for sorts and hashes and other memory hungry operations. This memory grant is an estimate. Based on current system state (number of requests running and pending, memory available etc) the system grants the query a memory grant up to the required amount. Once the memory is granted, the query starts execution (it may have to wait in the dreaded 'resource semaphore' queue before it gets the grant). At execution it memory grant is guaranteed by the system. This amount of memory can be shared with data pages (since they can always flush to disk) but never with other memory usage (ie. it cannot be subject 'steal'). So when the query starts asking for committed memory from its grant, the engine will deploy what you call 'strategy #1': data pages may be evicted (flushed if dirty) in order to give the query the memory it was promised. Now if the estimate was correct and the grant was 100% of the requested memory, the query should not 'spill'. But if the estimate was incorrect (boils down to cardinality estimates, therefore is subject to stale stats) or if the query did not got the entire grant it had asked for, the query will 'spill'. This is when tempdb comes into picture and performance usually tanks.

The only knob you have at your disposal that controls something in this process is the Resource Governor.  Since the RG can be used to specify a MIN setting for a pool, it can be used to reserve memory for a certain workload so that it actually gets the memory grant it requests. Of course, after you did the proper investigation that shows that reduced memory grants are the culprit, and of course after the impact on other workloads was evaluated. And tested, of course.

Now lets go back to your original question. If your investigation is correct (a very big if) I would like to point out two problems:

you run in production queries that require memory grants for a web site. This is a big no-no. Memory grants are indicative of analytical queries which have no place in serving HTTP requests.
your queries are probably not event getting the memory grant they request. Again, even more of a no-no for a latency critical workload as web sites are.

So what that tells me is that you have a fundamental design and architectural problem. Web sites are latency driven and should create an OLTP like workload, with no memory grants and with no memory pressure on queries. Not to mention no spills. Analytical queries should be run in offline jobs and store the pre-processed results for quick availability when HTTP requests desire them.


MySQL replication: "Houston, We've Got a Problem" I ran into a problem with our replication server. Essentially, we have 2 databases (database1 and database2). Master server has both. Slave has only database1. There is a

Replicate_Do_DB: database1

set in CHANGE MASTER TO configuration.

Now what happened is - we are using code igniter, and one of the programers created database2 and started inserting info into it. Code igniter sets a default database to database1. Now the result is for every query he produced - I get an error on SHOW SLAVE STATUS\G:

Error 'Table 'database2.tbl40' doesn't exist' on query. Default database: 'database1'. Query: 'INSERT INTO `database2`.`tbl40` (`date`, `day`) VALUES ('2011-04-26', '2011-04-26')'

So essentially, I he fixed the problem afterwards, but the replication doesn't work as there is around 1000 queries that will produce that error for replication server.

My question is - is there some way to clear queries like that from the binlog?
Or I need to write a script that will do a

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

for every query that produces and error ?

If you really don't care about that table, you can use pt-slave-restart on the slave and have it skip those problems.  I would be conservative about running it and make sure that you are only skipping queries for the table/database that you don't care about or at least for only a specific error.

You didn't post what the error code was in the output from SHOW SLAVE STATUS, but I suspect it is error 1146.

For example, this will skip all errors for 1146:

pt-slave-restart -u root -p pass --error-numbers 1146

Or, you could try skipping all errors that reference that table

pt-slave-restart -u root -p pass --error-text 'database2'

Another way to do this would be to set replicate-ignore-db=database2 and restart MySQL on the slave, but there are some caveats to how that works that you should read about in the documentation


Fulltext stoplist replication In MS SQL Server 2008 R2 there is replication of table with fulltext index on it.
But stoplist that is associated with replicated fulltext index doesn't replicate.

Is there any possibility to replicate stoplist also?

stoplist can't be replicated but there is one ugly way to do it. Words in Stop lists are stored in sys.fulltext_stopwords . Using linked server you can query this table and compare it on secondary server . If there are any changes then run alter command on secondary to add\remove those changes from stoplist.

ALTER FULLTEXT STOPLIST YourStopLIstName ADD 'en' LANGUAGE 'Spanish';

Can be setup as recurring job. Not a decent method but should work fine.


Replicating Results of Queries on Oracle I wan to setup a database such that its data come from queries run on a remote database.  Basically the source database is very slow and we have no control over it.  So, I want to create a second database such that it would run the queries periodically on the source database, and saves the results into this second database.  Thus our team would only have to deal with this second database which is simpler and much faster.

Now, my question is what is the best way to run the queries and then save the result into the second database. I can use Java to do this, but I think there are better, and more efficient, database specific way of doing this.  Note that I don't want to just replicate tables, but result of queries.

Thanks,
Alison

Depends how much data you need on the second database. Solutions are not to save query results on a second DB but duplicate data for querying on another DB. Basically the following technologies:

Oracle Streams: it is a replication from one database to another one. Can be done by table or by schema.
Oracle Active Dataguard: it is basically a standby DB open in ReadOnly for query. It is the standard oracle solution for the real time query. This requires a supplemental license
ETL: as suggested in the comment you can setup some ETL jobs using either an ETL tool or manually creating some stored procedure access data on remote DB. ETL is not, usually, a realtime process.
MATERIALIZED VIEV: if data and tables are a very small set you can create some MVs on your second DB that update periodically. Again this is not likely a realtime process and also may add load on primary DB.

In any case you need a sort of access to the primary DB. If you do not have any access apart the standard used you use for normal access through you application probably you can implement only ETL with a stored procedure or materialized view.


MySQL: Create multiple tables with one query Can you list multiple table names in a MySQL CREATE TABLE query?

CREATE TABLE table1, table2, table3 LIKE table_template;

If so, can you do it with the IF NOT EXISTS clause?

CREATE TABLE IF NOT EXISTS table1, table2, table3 LIKE table_template;

I know you can do this with DROP TABLE:

DROP TABLE IF EXISTS table1, table2, table3;

The docs for DROP TABLE explicitly show that you enter a string of names:

DROP [TEMPORARY] TABLE [IF EXISTS]
    tbl_name [, tbl_name] ...
    [RESTRICT | CASCADE]

The docs for CREATE TABLE don't show a table name string:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

More Syntax Variations ...

Can anyone point me to a reference that states explicitly that multiple tables can or can't be created with one query?

I searched the CREATE TABLE Syntax doc for "string", "tbl_name", and "names", but no luck.

Looking at the docs, it looks like you can't create multiple tables with one CREATE. You can use IF NOT EXISTS and LIKE together, like this:

CREATE TABLE IF NOT EXISTS table1 LIKE table_template;
CREATE TABLE IF NOT EXISTS table2 LIKE table_template;
CREATE TABLE IF NOT EXISTS table3 LIKE table_template;

Here's the page from the MySQL docs: CREATE TABLE


Point in time forward MySQL Dump I have a MySQL table that stores a list of bookings that's made online. I need to have these bookings dumped to a CSV file which will then be picked up by a client server application through a SSH connection. Earlier we were dumping the entire table and so the CSV file had the transactions since beginning of time. Now we have to generate the dump several times in a day and the dump should contain only the newer records. Does my MySQL provide any options to generate a dump that contains only those records that were inserted after the last dump was taken?

If you know the time from where you need to start the backup then you can use mysqldump with --where clause like below to get the type of backup you are looking for

mysqldump --user=username --password=password db_name table_name  --where=date_pulled > '2014-07-21 16:25:00' > backup.sql

You can also do like

SELECT * FROM table_name
WHERE date_pulled > '2014-07-21 16:25:00'
INTO OUTFILE 'table_name.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

You can create a simple automated script for this and change the date_pulled dynamically.

Have a look at below links that may help you as well

 Mysqldump in CSV format.

How to output MySQL query results in csv format?


Creating Replicas Across Servers I'm having some issues creating a replica set of my MongoDB.

I have server A. Currently, it is setup as a local standalone replica.

rs0:PRIMARY> rs.status()
{
        "set" : "rs0",
        "date" : ISODate("2014-06-02T19:01:23Z"),
        "myState" : 1,
        "members" : [
                {
                        "_id" : 0,
                        "name" : "mongo-server-a:27017",
                        "health" : 1,
                        "state" : 1,
                        "stateStr" : "PRIMARY",
                        "uptime" : 14986,
                        "optime" : Timestamp(1401735683, 1),
                        "optimeDate" : ISODate("2014-06-02T19:01:23Z"),
                        "self" : true
                }
        ],
        "ok" : 1
}

On another server, server B, I would like to replicate my data.

To do this, I run the following:

> rs.status()
{
        "set" : "rs0",
        "date" : ISODate("2014-06-02T19:05:40Z"),
        "myState" : 1,
        "members" : [
                {
                        "_id" : 0,
                        "name" : "mongo-replica:27017",
                        "health" : 1,
                        "state" : 1,
                        "stateStr" : "PRIMARY",
                        "uptime" : 35,
                        "optime" : Timestamp(1401735918, 1),
                        "optimeDate" : ISODate("2014-06-02T19:05:18Z"),
                        "electionTime" : Timestamp(1401735918, 2),
                        "electionDate" : ISODate("2014-06-02T19:05:18Z"),
                        "self" : true
                }
        ],
        "ok" : 1
}
rs0:PRIMARY>

Then I add server A

rs0:PRIMARY> rs.add("10.0.3.40:27017")
{ "ok" : 1 }
rs0:PRIMARY>

Then the log file tells the following:

2014-06-02T19:06:50.201+0000 [conn1] replSet replSetReconfig config object parses ok, 2 members specified
2014-06-02T19:06:50.202+0000 [conn1] replSet replSetReconfig [2]
2014-06-02T19:06:50.202+0000 [conn1] replSet info saving a newer config version to local.system.replset: { _id: "rs0", version: 2, members: [ { _id: 0, host: "mongo-replica:27017" }, { _id: 1, host: "10.0.3.40:27017" } ] }
2014-06-02T19:06:50.202+0000 [conn1] replSet saveConfigLocally done
2014-06-02T19:06:50.202+0000 [conn1] replSet info : additive change to configuration
2014-06-02T19:06:50.202+0000 [conn1] replSet replSetReconfig new config saved locally
2014-06-02T19:06:50.202+0000 [rsHealthPoll] replSet member 10.0.3.40:27017 is up
2014-06-02T19:06:50.202+0000 [rsHealthPoll] replSet member 10.0.3.40:27017 is now in state PRIMARY
2014-06-02T19:06:50.202+0000 [rsMgr] replSet total number of votes is even - add arbiter or give one member an extra vote
2014-06-02T19:06:50.202+0000 [rsMgr] another PRIMARY detected but it should step down since it was elected earlier than me

My problem is I have no clue as to how I should continue.

It seems that I missed the obvious part - adding the replica server should be done on Server A, not the other way around.

What I did was:

Start a clean MongoDB installation on server B with replSet option
On Server A I did rs.add('server-b:27017')
On Server A I controlled the addition of the replica with rs.status()

And it works.


List of databases which a user can access in SQL 2008 R2 Is it possible to create a list of all the databases where a specific user can access?

For example,

My user is 'Domain\MarySmith'. This user can access on a single database or can access many databases.

I need a query like this:

SELECT All_Db_Name_I_Can_Acces
FROM ServerSql
WHERE Username = 'Domain\MarySmith'

Try with this

USE master;
GO
EXECUTE AS LOGIN = N'LoginName';
GO
SELECT name
FROM sys.sysdatabases
WHERE HAS_DBACCESS(name) = 1
GO
REVERT;


SSIS Merge component performing a UNION instead of merging I have a package to combine data from multiple databases. I'm trying to bring together two data sets so they can be written to a single table.

There's a common SSIS_ID (DT_I4) used to match rows and the other columns I want to bring in to the same data set are also DT_I4.

Here's the section of the flow where the problem is; as you can see the data from both paths is sorted on SSIS_ID before entering the Merge:

And here is the configuration of the Merge component. The intention is to put the CRO_CompanyID and Sponsor_CompanyID for the same SSIS_ID in to a single row:

However the output of the merge is acting like a UNION; there are 1,532 rows entering from the left and 4,303 from the right resulting in 5,835 rows at the output.

A data viewer on the output shows the problem:

I can get around this problem by processing the data in a different way but it's really bugging me that I can't get this to work.

Any suggestions as to what I have done wrong or not done to be able to combine the data in this way?

I'm using SSIS for SQL Server 2008 but there wasn't a tag for that version.

The issue is that you're using the wrong transformation.

"The Merge transformation is similar to the Union All transformations." -MSDN Merge Transformation

You can use the Merge Join transformation and specify your join type, then select the input column used for each output.

Then you can get your desired output:

If your source(s) is an RDBMS system it's better to sort with an ORDER BY as opposed to using a Sort task, since it is a blocking component and the DB can likely perform the sort faster.


Unable to connect to SQL server 2008 R2 via Instance name? I don't understand :

I've installed sql server 2008 R2. the installation process asked me for the desired instance name  , so I wrote: MSSQLSERVER.

after the installation , running Setup Discovery Report does shows me the instance name :

I've also installed all the service packs.

Also , all the services are up :

Also , all the ports are open ( tcp:1433 + udp :1434)

Also , Named pipes are on :

So ,  where is the problem ?

I'm unable  to connect via the instance name :

Also unable   with :

user\MSSQLSERVER where user is the computer name.

Also unable  with : .\MSSQLSERVER

But (weird) :

Those 3 do work :

user ( "user" is my machine name)
(local)
.

Question :

why I can't use my instance name to connect to my sql server ?

Related info :

After connecting via one of the 3 who work  , I run a helpful info :

You selected the default instance name as the name of your instance.

Instance Configuration

"If you specify MSSQLServer for the instance name, a default instance will be created. For SQL Server Express, if you specify SQLExpress for the instance name, a default instance will be created."

To connect to the default instance you don't specify the instance name, only the server name.


Is there a tool or library to extract data from Mysql database files without having the database engine available? I have a bunch of *.frm, *.myd and *.myi files, which are a MySQL database store.

I need to extract the data kept in these files, but I do not have a MySQL database engine at my disposal.

Is there a tool or library, which can do this?

Please, do not suggest installing the database engine (be it MySQL or MariaDB or other MySQL forks). My question is specific about extracting the data without the full blown database engine. In essence, I am looking for an embedded database engine, which can be distributed with my code and which demands no installation.

I am not looking into doing complex queries, I just need to fetch all the data from one or two particular tables.

Thanks.

EDIT

I am aware of http://dev.mysql.com/doc/refman/5.0/en/libmysqld.html, but I do not understand how to download it. What is the licensing for commercial usage? Should I download and install the full blown database to get hold on the libmysqld files (headers, lib + dll)? Even though I do not need the full database? In short, it creates more questions than answers. I am still looking for an advice.

If you are brave and a little fool-hearty, try this crazy idea:

Since myisamchk can read, write, and analyze MyISAM tables independent of mysqld, get a hold of the source code for myisamchk, take a look around and see if you can borrow any C/C++ code, algorithms, structures, classes, or just any ideas from it.


SQL Server unique index, per subset of data, based on values of another column Say I have a Customers table, and an Emails table. Emails table has these fields:

Id
CustomerId
EmailValue

Now, I want to create a constraint so that no duplicate emails should be registered per customer. Ways that I can think of are:

Creating a function to ensure uniqueness of each new email value, and call that function in a check constraint
Create a trigger, and on each insert or update ensure that the email is not duplicate per customer
Forget about database at all, and make this part of the business in my application tier

However, I recently got familiar with SQL Server's filtered indexes. Though I can't find out if it can help me in this specific case or not. Most of the examples I've seen is to filter out NULL values in order to make a unique index for a nullable column.

Is there a way I can use a filtered index to achieve what I want?

Create a unique constraint over two columns.

alter table dbo.Emails add constraint UQ_Emails unique (CustomerId, EmailValue);

This will prevent duplicate emails per customer. Different customers can still have the same email.

From comments:

  What if I have an IsDefault field too? In that case, the requirement would be:

  There should be only one active email per customer, and each customer should not have duplicate emails"

You can do that if you add a filtered unique index:

create unique nonclustered index UX_Emails
on dbo.Emails (CustomerID)
where IsDefault = 1;


How to change default collation for any charset in MySQL? How to change the default collations listed by SHOW CHARACTER SET?

For utf8 I need to change it to utf8_unicode_ci.

Or is there any switch to enforce the _unicode_ci over _general_ci for any UTF8xx charsets?

This is futile.  Any user can explicitly state what collation to use, thereby defeating the 'default'.

A better solution is to upgrade to MySQL 8.0.  It defaults to utf8mb4 and (I think) utf8mb4_0900_ai_ci

mb4 is useful for Emoji and Chinese.

0900 comes from Unicode 9.0, which is newer than 5.20, which is newer than 4.0, which you are suggesting.


How to MySQLDump with For Channel to set up multi-source replication? I'm trying to set up multi-source replication.

But, when I try and import the second database I get the error:

ERROR 3079 (HY000) at line 28: Multiple channels exist on the slave. Please provide channel name as an argument.

How can I supply the channel name to mysqldump

I run the command:

mysqldump -h1.2.3.4 -P3306 -umysqldumpuser -ppassword --dump-slave --apply-slave-statements --include-master-host-port --single-transaction --databases db_name -E -R -q -c --max_allowed_packet=2G | mysql -S/var/run/mysqld/mysqld.sock -umysqldumpuser -ppassword

The first dump is fine, as I can then run:

RESET SLAVE ALL FOR CHANNEL "";

CHANGE MASTER TO
. . .
FOR CHANNEL '<channel_name>;

So it is now replicating over a channel. But I can't dump the second one as it gives me the above error.

The only way I can see this working, would be to dump the first one, stop replication, make a note of the logfile and pos, and then wipe the slave connection, leaving replication to the first database off while I dump the second one.

Only then can I start replication to both databases at the same time.

This is highly impractical, especially if you are looking to combine connections from numerous locations.

I have searched the documents, but can't find any reference to using mysqldump and channels.

I raised this with Oracle BUG 96072, and they have responded with

  "I have analysed it and I consider it a welcome feature request."

So I am taking it from that, that this is an oversight, and there is currently no option to use mysqldump to build a multi-source database.

So for the moment it seems the options are:

Export MySQLdump to a file, then manually edit the file, before importing it again (needs twice the space and twice the time)
Import the first database, remove the replication settings (saving for later), import the second database. Update the replication settings for the second one, then start replication on the first one. Also far from an ideal situation.


SQL Server, TOP versus ROW_NUMBER I'm learning about Execution plans and am trying out different queries and comparing their performance and stumbled upon this:

SELECT StatisticID
FROM (
    SELECT StatisticID, ROW_NUMBER() OVER (ORDER BY StatisticID) AS rn
    FROM FTCatalog.Statistic
    ) AS T
WHERE T.rn <= 1000
ORDER BY rn

SELECT TOP 1000 StatisticID
FROM FTCatalog.Statistic
ORDER BY StatisticID

They both return same result set - however first one executes faster and is less resource hungry (at least SSMS tells me that)
Here are execution plans:

Comparison from SQL Query Plan Explorer:

Could anyone give me some insight on what's actually happening behind the scenes and why results differ? If there's anything else you need - just let me know.

Thanks,
Evaldas.

I guess you are comparing the estimated costs for the queries. Those are just estimates based on (among other things) the estimated number of rows returned by the query. Not the actual number of rows.

Your first query estimated that it would return 30 rows and your second query estimated 1000 rows. That is where your difference in query cost comes from.

If you change the queries to fetch only 30 rows you will see that the estimated rows is the same for the queries and the first query actually is costed a bit higher, at least for me in SQL Server 2014.

Don't use the estimates when comparing performance of queries. Use things like duration, number of reads and size of memory grants instead.


rolling-up consecutive duplicates Is there a way to "roll up" duplicate records such as time-series data in MySQL? I'm currently doing this in the application code after retrieving full time-series but the transmission of mostly redundant rows seems a nuisance. Here is an example:

Original data:

 date | state
..30  | A
..29  | A
..28  | A
..27  | D
..26  | D
..25  | A

Resultset with "rolled up" consecutive duplicate records:

 date | state
..28  | A
..26  | D
..25  | A

Thanks!

This code assumes that there will only be one record for any given date. It also does not check to make sure that dates are consecutive, if that's important.

SELECT the_date, my_state as the_state
  FROM (SELECT the_date
              ,if(@laststate = the_state, '', the_state) as my_state
              ,@laststate := the_state
          FROM myTest
         ORDER BY the_date
       ) sq
 WHERE my_state <> ''
 ORDER BY the_date DESC
;

First, in the subquery, it sets the state to an empty string if it's the same as the state in the previous row. Then, we select only those records form the subquery where the state is not an empty string.

See in db-fiddle.com.


It looks like a bug. Did the set work with multi-column values?

SET t:3 0 0 5
a\tb\tc

(\t is a tabulator)


Howto remind and restore recovery mode during SSDT deployment? Due to deployment of a SSDT database project I would like to be able to switch the target database's recovery mode from FULL to BULK LOGGED and back after the deployment finished in order to do any data manipulation changes with minor invasive logging.
I can't simply ignore the setting because deploying to development or staging environment should keep the SIMPLE setting but in production the setting should be switched back and forth.

Is it possible to "save" the original setting in Pre-Deployment script and restore this setting after the deployment? Do variables taken in pre-deployment keep alive between those scripts?

I doubt seriously that the entire deployment happens in one batch and therefore variables wouldn't persist throughout the deploy (but you could test it).  What I think makes more sense is to just store any "pre" values in a temp table (maybe a global temp table) and then retrieve them during the post deploy.


MySQL Join, Force NULL Row I'm having a bit of trouble figuring out how to do this -- I need to select all rows from table t1, join table t2 -- but include a null row from t2 for each record in t1 (even if rows match in t2).

For my case, I actually need to do this with four tables:

Table t1 has t1_id, name

Table t2 has t2_id, name

Table t3 has t3_id, name

Table t4 has t4_id, name

I need to join them all to get something like:

-----------------------------
t1_id | t2_id | t3_id | t4_id
-----------------------------
1     | NULL  | NULL  | NULL
-----------------------------
1     | 1     | NULL  | NULL
-----------------------------
1     | 1     | 1     | NULL
-----------------------------
1     | 1     | 1     | 1
-----------------------------

This would be the final result of a join of the four tables, each having one row with an tx_id of 1.

All INNER JOINs and UNIONs maybe

PROPOSED QUERY

select id t1_id,NULL t2_id,NULL t3_id,NULL t4_id from t1
union select t1.id,t2.id,NULL,NULL from t1
    inner join t2 using (id)
union select t1.id,t2.id,t3.id,NULL from t1
    inner join t2 using (id)
    inner join t3 using (id)
union select t1.id,t2.id,t3.id,t4.id from t1
    inner join t2 using (id)
    inner join t3 using (id)
    inner join t4 using (id)
;

SAMPLE DATA

use test
drop table if exists t1;
drop table if exists t2;
drop table if exists t3;
drop table if exists t4;
create table t1
(
    id int not null,
    name varchar(20),
    primary key (id)
);
insert into t1 values (1,'name1');
create table t2 like t1; insert into t2 values (1,'name2');
create table t3 like t1; insert into t3 values (1,'name3');
create table t4 like t1; insert into t4 values (1,'name4');

PROPOSED QUERY EXECUTED

mysql> select id t1_id,NULL t2_id,NULL t3_id,NULL t4_id from t1
    -> union select t1.id,t2.id,NULL,NULL from t1
    ->     inner join t2 using (id)
    -> union select t1.id,t2.id,t3.id,NULL from t1
    ->     inner join t2 using (id)
    ->     inner join t3 using (id)
    -> union select t1.id,t2.id,t3.id,t4.id from t1
    ->     inner join t2 using (id)
    ->     inner join t3 using (id)
    ->     inner join t4 using (id)
    -> ;
+-------+-------+-------+-------+
| t1_id | t2_id | t3_id | t4_id |
+-------+-------+-------+-------+
|     1 |  NULL |  NULL |  NULL |
|     1 |     1 |  NULL |  NULL |
|     1 |     1 |     1 |  NULL |
|     1 |     1 |     1 |     1 |
+-------+-------+-------+-------+
4 rows in set (0.00 sec)

mysql>

All the columns have id as a common name. If you want, I can adjust my answer with the id names


How does name resolution work in Oracle 10g? In Oracle 10g (10.2.0), there are 4 types of names resolution:

Easy Connect(10g added this new method)
Local naming
Directory
External naming

It is said that if Easy connect is used by the clients, then it may overload the listener on the server (as client queue increases). How can this happen as it is the server's duty to distribute load from one listener to another? (Please correct me if my concepts are wrong.)

And how exactly does Directory and External naming work.

When you need to connect any database , you need to provide connection string. Oracle is the same. What you need to provide is following information basically.

Database Server Name or IP.
Database Name, Oracle SID or Service Name
Datapase Port
Username and password

First connection method is Local naming which uses Oracle Net. Before TCP/IP was standardized a lot of third party networking protocols existed. To overcome this problem, oracle used Oracle NET.  According to Oracle Documentation Local Naming : "Resolves a net service name stored in a tnsnames.ora file stored on a client". tnsnames.ora file contains 1-3 of above. Therefore you only need to provide (4) username and password. Below an example line of tnsnames.ora can be seen.

(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=my-server) (PORT=1521)))

Second easy connect is same. You provide information contained in tnsnames.ora in command line. Here host = 1 , port (2), service_name = (3). As you can see it is same as first one. You give same information to connector as before. You may skip default information like port.

CONNECT username/password@host[:port][/service_name]

http://docs.oracle.com/cd/B13789_01/network.101/b10775/naming.htm#i476040

Third is Directory Server, LDAP Server like Microsoft Active Directory. Here you provide a key and directory server gives you back connection string. You use this connection string to connect to oracle.

Fourth one is same as Directory server , you only use other network services. As explained in oracle documentation.
"Configuring External Naming Methods

External naming refers to the method of resolving a net service name, stored in a non-Oracle naming service, to a network address."
According to NIS wikipedia: "Network Information Service, or NIS is a client–server directory service protocol for distributing system configuration data ...over time other modern and secure distributed directory systems, most notably LDAP, came to replace it."
As you can see, External Naming is same as Directory Server, only uses a different server technology (older) than LDAP.

http://docs.oracle.com/cd/B13789_01/network.101/b10775/naming.htm#i476040

These are four connection methods. I do think other than where do they get connection string information, they differ. Therefore easy connect should not have less performance than local naming.


Install PostGIS v2 for PostgreSQL v9.2 I have installed the latest stable version of PostgreSQL v9.2 and I want to install the PostGIS v2 extension.

I've been searching for it for 3 days now, however can't find it.

I did however find PostGIS v2 for PostgreSQL v9.1, thanks to this site: http://linfiniti.com/2012/05/installing-postgis-2-0-on-ubuntu/

I'm an Ubuntu novice, I'm more accustomed to Windows.

The PostgreSQL v9.1 package doesn't work.  Where can I get the correct package?

From this listing it looks like there is a package named postgresql-9.2-postgis2.  You may have to add a new apt repository to the existing ones.  In this, you can find help here.


Is it possible to log mysql queries from other hosts in general log? I'm having a task to figure out what are the hosts that are still running scripts that accessing our database directly so we can prevent that from happening(we only want our web app to access the database but nothing else). I just learnt that enabling general_log would give me a nice table of access log. My problem is that the table grows too fast and easily lost control.

For my specific use case, my question would be: is there a mysql feature to make general_log only record database access from other hosts so general_log could record less data? I know it's pretty unlikely to happen, so my last option is to periodically delete the records that are local access. But this might cause performance issue. Do you think it makes sense to do that? Is there a better way to do it? Thanks.

You may use the mcaffee audit plugin https://github.com/mcafee/mysql-audit/wiki/Installation there you can filter hosts in logging.

You could also refuse all hosts specifying a mysql User with an appropriate Host. ('user'@'allowedHostIpRange').


Explanation of Extended Events xml I queried an Extended Events session and returned the XML. I am confused on what frame level 1, 2, 3 mean.  As well as line, offsetStart and offsetEnd?
I am however able to convert the sql handle to the sp name.

The level seems to be in reverse order of how they are called.  So the object listed in level 3, calls the object in level 2, which calls the object in level 1.

As the name of the "action" indicates, you are looking at the execution stack of a nested series of calls in a single Session. As you noted, one object calls another which calls another. As each successive module is executed, the calling module is put on hold and pushed down the "stack", with the current / active module being on top (level 1). When the top level module completes, if there are any calling modules in the stack (level > 1) then they all move up by one and what was at level 2 is now at level 1 and is active again, continuing where it left off when it called the sub-module.

Level 1 is what you see in sys.dm_exec_requests and other DMVs. You cannot see Levels above 1 (at least as far as I have ever seen).

What exists at each level is the query batch (one or more T-SQL statements encapsulated in either a module -- Stored Procedure, Triggers, not sure if Multi-statement Table-Valued Functions and Scalar UDFs would get their own level -- or submitted as ad hoc / Dynamic SQL). The handle (i.e. "sql_handle") attribute refers to the query batch (all statements within the batch). The line attribute should be the starting line number of the current executing statement within the batch. The offsetStart and offsetEnd attributes should be the starting and ending byte positions of the current executing statement within the batch. Because the offsets are expressed as byte positions, you need to divide by 2 in order to get the character positions (if you want to do something like SUBSTRING the batch to extract that particular statement). I am not sure if a value of -1 ever shows up here for offsetEnd, but it does show up in sys.dm_exec_requests and indicates "end of the batch".


Why does COUNT() aggregate return 0 for 'NULL'? A SELECT statement returns several rows:

SELECT
  ColA
FROM
  TableA
WHERE
  ColA IS NULL

I get 47 rows that have 'NULL' for ColA within TableA.

ColA
NULL
NULL
NULL
etc...

If I add an aggregate to this query:

SELECT
  ColA,
  COUNT(ColA) AS theCount
FROM
  TableA
WHERE
  ColA IS NULL
GROUP BY ColA

I get

ColA | theCount
NULL | 0

Why is this happening, and what can I do to avoid this?

Aggregate functions ignore null values.

So

SELECT COUNT(cola) AS thecount
FROM tablea

is equivalent to

SELECT count(*) AS thecount
FROM tablea
WHERE cola IS NOT NULL;

As all of your values are null, count(cola) has to return zero.

If you want to count the rows that are null, you need count(*)

SELECT cola,
       count(*) AS theCount
FROM tablea
WHERE cola is null
GROUP BY cola;

Or simpler:

SELECT count(*) AS theCount
FROM tablea
WHERE cola is null;

If you want to count NULL and NOT NULL values in a single query, use:

SELECT count(cola) as not_null_count,
       count(case when cola is null then 1 end) as null_count
FROM tablea;


What all files are required for restoration process of SQL Server in Full + Diff + Transactional backup policy I am reading about different types of backup policy in sql server. I am not clear with backup policy for Full + Differential + Transactional backiup

My main attention is towards what all files would be required at the time of restoration. As per the diagram given on SQL Authority, it seems like I need all the files i.e.

last full backup file 1 + log file 1.1 + log file 1.2 + differential 1.1 + log file 1.4 + log file 1.5 + Differential 1.2 + ...all log files..

or did i interpret it wrong and I would need
last full backup file + differential 2 + log file 2.1 + log file 2.2....

EDIT:

What is contained in differential backup, is it just mdf + ndf backup or ldf + mdf + ndf backup since last full backup was taken?

or did i interpret it wrong and I would need last full backup file + differential 2 + log file 2.1 + log file 2.2....

I have not read the blog but above one seems correct approach. Differential backup is mainly there to reduce Recovery Time Object(RTO) it reduces the time taken to bring database up and running after disaster strikes.

Example:

If you have

Daily full backup
Every 4 hr differential backup
Every 15 mins log backup

Suppose your database crashes today at 5 PM.

The restore sequence would be

First restore Today's full backup with no recovery then 4 PM differential backup with no recovery and the  all log backup with no recovery, just the last log backup with recovery to bring database online.

If in above scenario you dont take diff backup then restore sequence would be

Todays full backup and then all log backups taken after this full backup witn no recovery just the last backup with recovery to bring DB online.

  What is contained in differential backup, is it just mdf + ndf backup or ldf + mdf + ndf backup since last full backup was taken?

You added above part of question after my answer so I will include this as well.Differential backup includes are all the data extents that have changed since the last full backup. Yes they also include (if required) few amount of transaction log to bring database into consistent state after restore is done. For more details read this


Creating tablespace on network location in Postgres I have a specific requirement where I need to store all the database files on a network drive.

I have tried creating a new tablespace using the following command

CREATE TABLESPACE test
  OWNER postgres
  LOCATION E'z:\\test';

Here z is a network mapped drive on a windows system. I can create a new tablespace on usb drive, but not on network drive. Is it at all possible to store database files on a network location for Postgres. I am getting permission denied error for network location.

Storing database files on network storage is probably not a good idea with regard to performance; and probably security. Make sure your network is fast enough, and the connection very reliable.

Anyhow, it is feasible.

You seem to be working on Windows. Depending on version and mode of installation, the user running your PostgreSQL service might be different.

First thing to do: check which one it is. Use the "Services" administration tool, find out a service with a name similar to Postgres, and find out which user is the one that will be running PostgreSQL. In most recent versions and installation modes, it is commmon that this user is "Local System" or "NetworkService". In previous versions, it used to be a Postgres local user (i.e.: not an Active Directory domain user).

At this point, you have two options:

Change the user (and password) to another one that has permissions to access your network share. Create one if you must. This user must not have administrative privileges; and it should have privileges only to be able to handle the database (i.e.: access to the part of the filesystem that it requires, which will be the PostgreSQL data and log directories that you chose while installing and all the tablespaces you define).
Give the existing user read/write/modify permissions to access the network share. You shouldn't (and you probably just won't be able to) if the user is one of the standard Windows, that would most probably open the whole Windows to lots of security risks.


Mysql get records more then 3 in interval of 1 minute I currently developed a cron that runs every 1 minute to analyze the last 60 seconds of the bot records in database, I need to group the conversation ID's that have more than 3 records within 60 seconds in the same url and client_session_id.
follows the SQL I'm running:
select
    count(session_id),
    client_session_id,
    GROUP_CONCAT(id) as talkIds,
    origin_url
from
    bot_talk
where
    created_date > now() - interval 60 second
group by
    client_session_id, origin_url
having
    count(session_id) >= 3

This query works as I expect, but sometimes my cron service is sometimes down, and I lose those repeated records.
I thought about making an SQL(Cron) at the end of the day to analyze the last 24 hours, and look for the records that are repeated according to the rule I mentioned above?
Currently my database looks like this:

created_date
origin_url
client_session_id

2021-01-18 11:02:24.0
https://chat-app.ttttttt.com/
znkjoc3gfth2c3m0t1klii

2021-01-18 11:02:35.0
https://someurltestenter1502211068.zendes.com/
znkjoc3gfth2c3m0t1klii

2021-01-18 11:11:03.0
https://chat-app.testett.com/
znkjoc3gfth2c3m0t1klii

2021-01-18 11:44:28.0
https://someurltestenter1502211068.zendes.com/
znkjoc3gfth2c3m0t1klii

2021-01-18 11:49:36.0
https://someurltestenter1502211068.zendes.com/
znkjoc3gfth2c3m0t1klii

2021-01-18 11:51:05.0
https://someurltestenter1502211068.zendes.com/
znkjoc3gfth2c3m0t1klii

2021-01-18 11:51:15.0
https://someurltestenter1502211068.zendes.com/
znkjoc3gfth2c3m0t1klii

2021-01-18 11:51:19.0
https://someurltestenter1502211068.zendes.com/
znkjoc3gfth2c3m0t1klii

2021-01-18 11:51:43.0
https://someurltestenter1502211068.zendes.com/
znkjoc3gfth2c3m0t1klii

2021-01-18 11:51:50.0
https://someurltestenter1502211068.zendes.com/
znkjoc3gfth2c3m0t1klii

2021-01-18 12:01:24.0
https://someurltestenter1502211068.zendes.com/
znkjoc3gfth2c3m0t1klii

2021-01-18 12:04:48.0
https://someurltestenter1502211068.zendes.com/
znkjoc3gfth2c3m0t1klii

2021-01-18 13:40:50.0
https://someurltestenter1502211068.zendes.com/
znkjoc3gfth2c3m0t1klii

2021-01-18 15:54:38.0
https://someurltestenter1502211068.zendes.com/
znkjoc3gfth2c3m0t1klii

2021-01-18 15:54:45.0
https://someurltestenter1502211068.zendes.com/
znkjoc3gfth2c3m0t1klii

2021-01-18 15:55:08.0
https://someurltestenter1502211068.zendes.com/
znkjoc3gfth2c3m0t1klii

2021-01-18 15:58:07.0
https://someurltestenter1502211068.zendes.com/
znkjoc3gfth2c3m0t1klii

2021-01-18 15:58:11.0
https://someurltestenter1502211068.zendes.com/
znkjoc3gfth2c3m0t1klii

2021-01-18 15:59:56.0
https://someurltestenter1502211068.zendes.com/
znkjoc3gfth2c3m0t1klii

2021-01-18 16:08:32.0
https://admin.testete.com/
znkjoc3gfth2c3m0t1klii

fiddle:
https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=b8ce65bad5d39ea85fc5b57d7dc0f729

To answer your question, I did the following (works for MySQL 8 - window functions used - see fiddle here):
Created the table:
CREATE TABLE test
(
  created_date TIMESTAMP(1) NOT NULL,
  origin_url   VARCHAR (200) NOT NULL,
  client_session_id VARCHAR (50) NOT NULL
);

Populate it (sample):
INSERT INTO test VALUES
('2021-01-18 11:02:24.0', 'https://ttttttt.com/', 'znkjoc3gfth2c3m0t1klii'),
('2021-01-18 11:02:35.0', 'https://zendes.com/', 'znkjoc3gfth2c3m0t1klii'),
('2021-01-18 11:11:03.0', 'https://testett.com/', 'znkjoc3gfth2c3m0t1klii'),
('2021-01-18 11:49:28.0', 'https://zendes.com/', 'znkjoc3gfth2c3m0t1klii'),
('2021-01-18 11:50:36.0', 'https://zendes.com/', 'znkjoc3gfth2c3m0t1klii'),
('2021-01-18 11:51:05.0', 'https://zendes.com/', 'znkjoc3gfth2c3m0t1klii');

Then I used the LEAD() window function - the syntax is as follows:
LEAD(<expression>[,offset[, default_value]]) OVER (
    PARTITION BY (expr)
    ORDER BY (expr)
)

The initial SQL is:
SELECT
  created_date,
  LEAD(created_date, 2) OVER (PARTITION BY origin_url, client_session_id
                            ORDER BY created_date DESC, origin_url, client_session_id) AS l_3,
  TIMESTAMPDIFF
  (
    MINUTE,
    created_date,
    LEAD(created_date, 2) OVER (PARTITION BY origin_url, client_session_id
                            ORDER BY created_date DESC, origin_url, client_session_id)
  ) AS min_diff,
  origin_url,
  client_session_id
FROM test;

Result:
created_date    l_3 min_diff    ut_d    origin_url  client_session_id
2021-01-18 11:11:03.0                                       https://testett.com/    znkjoc3gfth2c3m0t1klii
2021-01-18 11:02:24.0                                       https://ttttttt.com/    znkjoc3gfth2c3m0t1klii
2021-01-18 11:51:05.0   2021-01-18 11:49:28.0   1   97.0    https://zendes.com/ znkjoc3gfth2c3m0t1klii
2021-01-18 11:50:36.0   2021-01-18 11:02:35.0   48  2881.0  https://zendes.com/ znkjoc3gfth2c3m0t1klii
2021-01-18 11:49:28.0                                       https://zendes.com/ znkjoc3gfth2c3m0t1klii
2021-01-18 11:02:35.0                                       https://zendes.com/ znkjoc3gfth2c3m0t1klii

So, it was noticed that UNIXTIMESTAMP (gives the difference in seconds) might be better than TIMESTAMPDIFF - (see here) - so, in the end, I used (see the fiddle for the result):
SELECT
  created_date,
  LEAD(created_date, 2) OVER (PARTITION BY origin_url, client_session_id
                            ORDER BY created_date DESC, origin_url, client_session_id) AS l_3,
  ABS(TIMESTAMPDIFF
  (
    MINUTE,
    created_date,
    LEAD(created_date, 2) OVER (PARTITION BY origin_url, client_session_id
                            ORDER BY created_date DESC, origin_url, client_session_id)
  )) AS min_diff,
  UNIX_TIMESTAMP(created_date) -
  UNIX_TIMESTAMP
  (
    LEAD(created_date, 2) OVER (PARTITION BY origin_url, client_session_id
                            ORDER BY created_date DESC, origin_url, client_session_id)
  ) AS ut_d,
  origin_url,
  client_session_id
FROM test;

The final SQL and result are:
SELECT * FROM
(
  SELECT
    created_date,
    LEAD(created_date, 2)
           OVER (PARTITION BY origin_url, client_session_id
                   ORDER BY created_date DESC, origin_url, client_session_id) AS l_3,
    created_date -
    LEAD(created_date, 2)
           OVER (PARTITION BY origin_url, client_session_id
                   ORDER BY created_date DESC, origin_url, client_session_id) AS l_diff,
    UNIX_TIMESTAMP(created_date) -
    UNIX_TIMESTAMP
    (
      LEAD(created_date, 2) OVER (PARTITION BY origin_url, client_session_id
                            ORDER BY created_date DESC, origin_url, client_session_id)
    ) AS ut_d,
    origin_url,
    client_session_id
  FROM test
) AS t
WHERE t.ut_d  < 180;

Result:
         created_date                     l_3   l_diff  ut_d    origin_url  client_session_id
2021-01-18 11:51:05.0   2021-01-18 11:49:28.0    177.0  97.0    https://zendes.com/
znkjoc3gfth2c3m0t1klii

You mention that you have MySQL 5.7 - which doesn't support window functions. You can either upgrade or use various simulation methods - my advice is to upgrade.


Data Truncated for Column DECLARE float_one, float_two, my_result NUMERIC(7,2)
my_result = CONVERT(float_one/float_two, DECIMAL(7,2));

In this mysql query, I do this type of operation in a stored procedure, but the Linux environment phpmyadmin throws the following warning:

  Note: #1265 Data truncated for column 'float_one' at row 5

Does anyone have an idea how can I solve this problem?

CREATE TABLE IF NOT EXISTS `float_sample` (
      `id` int(11) NOT NULL auto_increment,
      `first_number` float(10,3) default NULL,
      `second_number` float(10,3) default NULL,
      PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

INSERT INTO `float_sample` (`id`, `first_number`, `second_number`)
VALUES    (1, 2.900, 1.900),    (2, 3.100, 22.100);

and the procedure

DELIMITER $$
DROP PROCEDURE IF EXISTS float_test$$
CREATE PROCEDURE float_test(IN my_ID INT)

BEGIN
DECLARE first_float, second_float DECIMAL(10,3);

DECLARE done INT DEFAULT 0;

DECLARE myCursor CURSOR FOR
        SELECT `first_number`, `second_number`
        FROM `float_sample`
        WHERE `id` = my_ID;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN myCursor;
my_loop:LOOP
    FETCH myCursor INTO first_float, second_float;

    IF done = 1 THEN
        LEAVE my_loop;
    END IF;

END LOOP my_loop;
CLOSE myCursor;

-- SELECT first_float, second_float;
END;$$
DELIMITER ;

and the result

1 70 10:41:36    CALL mytests.float_test(1) 0 row(s) affected, 2 warning(s):
1265 Data truncated for column 'first_float' at row 2
1265 Data truncated for column 'second_float' at row 2  0.032 sec

What is the the value of float_one at the time of conversion ???

Note this example from MySQL 5.5.12 in Windows

mysql> select convert(20000,decimal(7,2));
+-----------------------------+
| convert(20000,decimal(7,2)) |
+-----------------------------+
|                    20000.00 |
+-----------------------------+
1 row in set (0.00 sec)

mysql> select convert(200000,decimal(7,2));
+------------------------------+
| convert(200000,decimal(7,2)) |
+------------------------------+
|                     99999.99 |
+------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------------------------------+
| Level   | Code | Message                                                               |
+---------+------+-----------------------------------------------------------------------+
| Warning | 1264 | Out of range value for column 'convert(200000,decimal(7,2))' at row 1 |
+---------+------+-----------------------------------------------------------------------+

1 row in set (0.00 sec)

It may be possible that data was truncated if a number bigger than 99999.99 was in float_one. Perhaps, mysql was converting float_one and float_two to DECIMAL(7,2) individually before performing division. Try using DECIMAL(10,2) or greater to accommodate large values.

UPDATE 2011-07-25 15:05 EDT

There are definite truncation problem going on here

According to MySQL Reference Manual Page 442 Paragraphs 2,3

  DECIMAL and NUMERIC values are stored as strings, rather than as
  binary floating point numbers, in order to preserve the decimal
  precision of those numbers. One character is used for each digit of
  the value, the deciaml point (if scale > 0) and the - sign (for
  negative numbers). If the scale is 0, DECIMAL and NUMERIC values
  contain no decimal point or fractional part.

  The maximum range of DECIMAL and NUMERIC is the same as for DOUBLE,
  but the actual range for the given DECIMAL or NUMERIC column can be
  constrainted by the precision and scale for a give column, When such a
  column is assigned a value with more digits following the decimal
  point than are allowed by the specified scale, the value is rounded to
  that scale. When a DECIMAL or NUMERIC column is assigned a value whose
  magnitude exceeds the range implied by the specified (or defaulted)
  precision and scale, MySQL stores the value representing the
  corresponding endpoint of that range.

You need to accommodate a larger precision and/or scale.

Here is an example as to why

I wrote this stored procedure using your specifications for DECMIAL and NUMERIC.

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`NumTest` $$
CREATE PROCEDURE `test`.`NumTest` (num1 NUMERIC(7,2), num2 NUMERIC(7,2))
BEGIN

  DECLARE float_one,float_two,my_result NUMERIC(7,2);
  DECLARE f1,f2 DOUBLE(7,2);

  SET f1 = num1;
  SET f2 = num2;

  SET float_one = num1;
  SET float_two = num2;

  SELECT f1 / f2;
  SELECT float_one / float_two;
  SELECT CONVERT(float_one / float_two,DECIMAL(7,2));
  SET my_result = CONVERT(float_one / float_two,DECIMAL(7,2));
  SELECT my_result;

END $$

DELIMITER ;

I used two values: 290.0 and 14.5 for this test.

Before calling the NumTest stored procedure, I manually calculated 290.0 / 14.5

mysql> select 290.0 / 14.5;
+--------------+
| 290.0 / 14.5 |
+--------------+
|     20.00000 |
+--------------+
1 row in set (0.00 sec)

I divided each number by 100, 10000, and 1000000 and tried again

mysql> select 2.9 / .145;
+------------+
| 2.9 / .145 |
+------------+
|   20.00000 |
+------------+
1 row in set (0.00 sec)

mysql> select .029 / .00145;
+---------------+
| .029 / .00145 |
+---------------+
|    20.0000000 |
+---------------+
1 row in set (0.00 sec)

mysql> select .00029 / .0000145;
+-------------------+
| .00029 / .0000145 |
+-------------------+
|      20.000000000 |
+-------------------+
1 row in set (0.00 sec)

So far, so good !!! Now for the stored procedure.

mysql> call numtest(290.0,14.5);
+-----------+
| f1 / f2   |
+-----------+
| 20.000000 |
+-----------+
1 row in set (0.00 sec)

+-----------------------+
| float_one / float_two |
+-----------------------+
|             20.000000 |
+-----------------------+
1 row in set (0.00 sec)

+---------------------------------------------+
| CONVERT(float_one / float_two,DECIMAL(7,2)) |
+---------------------------------------------+
|                                       20.00 |
+---------------------------------------------+
1 row in set (0.00 sec)

+-----------+
| my_result |
+-----------+
|     20.00 |
+-----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

So my orignal two numbers work fine. Let's divide them by 100 and try again.

mysql> call numtest(2.9,0.145);
+-----------+
| f1 / f2   |
+-----------+
| 19.333333 |
+-----------+
1 row in set (0.00 sec)

+-----------------------+
| float_one / float_two |
+-----------------------+
|             19.333333 |
+-----------------------+
1 row in set (0.00 sec)

+---------------------------------------------+
| CONVERT(float_one / float_two,DECIMAL(7,2)) |
+---------------------------------------------+
|                                       19.33 |
+---------------------------------------------+
1 row in set (0.00 sec)

+-----------+
| my_result |
+-----------+
|     19.33 |
+-----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+-------------------------------------------+
| Level | Code | Message                                   |
+-------+------+-------------------------------------------+
| Note  | 1265 | Data truncated for column 'num2' at row 2 |
+-------+------+-------------------------------------------+
1 row in set (0.00 sec)

WAIT, we lost some precision. What happened ??? How did this happen ??? You need to accommodate more decmial digits (> 2)

UPDATE 2011-07-25 17:37 EDT

I substituted (7,2) with (10,7) in the stored procedure and got back the proper precision

mysql> call numtest(2.9,0.145);
+----------------+
| f1 / f2        |
+----------------+
| 20.00000000000 |
+----------------+
1 row in set (0.00 sec)

+-----------------------+
| float_one / float_two |
+-----------------------+
|        20.00000000000 |
+-----------------------+
1 row in set (0.01 sec)

+----------------------------------------------+
| CONVERT(float_one / float_two,DECIMAL(10,7)) |
+----------------------------------------------+
|                                   20.0000000 |
+----------------------------------------------+
1 row in set (0.03 sec)

+------------+
| my_result  |
+------------+
| 20.0000000 |
+------------+
1 row in set (0.05 sec)

Query OK, 0 rows affected (0.06 sec)

mysql>


Initial recovery Postgres with backup in sync commit mode I've got a master and a slave postgres 9.1.3 database. I would like to init the backup database for the first time.

I did the following steps:

Stopped master
Copied all files to Slave
Create recory.conf on slave
started master
started slave

This process seems to work, but I've got the feeling something is wrong, because it's been a few hours now and there are still postgres: wal receiver process   streaming 1/1FAA88 and postgres: startup process   recovering 000000010000000100000000 processes on the slave.

The slave should be up after a few seconds because it already consists of all needed data...

I don't archive my WALs.

Do I have to use pg_start_backup before copying the files to the slave?

I tried this:

Disable sync commits
restart master
master pg_start_backup
stop master
copy files
start master
master pg_stop_backup (docs say "don't do that when sync commit = on", I guessed the master resends all files in pg_xlog when it starts?)
Enable sync commit
restart master
create recovery file on slave
start slave

But then I get an error on he slave about some missing WALs.

I found alot of tutorials with using WAL archive, but I don't have a WAL archive.

Can anyone give me the correct steps?

If you don't archive wal's then how do you suppose the slave is going to get them?

You have to archive, and then recovery.conf should read from the archive.


Restore SQL Server database Using VB.NET, I want to restore a SQL Server database from a .BAK file without the "log file".

I tried using:

Restore database dbName
from disk = '" & path & "'"

but it didn't work, exception that refers to

"not finding _log file"

more specifically:

Cannot open backup device 'D:\AlomohasebSQL.bak'.
Operating system error 2(Invalid path).
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

From the more complete error message:

  Cannot open backup device 'D:\AlomohasebSQL.bak'. Operating system error 2 (Invalid path). Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.

Do you know the exact SQL restore statement you are performing? No matter what front-end language you are using, it should be executing something like this against the SQL Server:

RESTORE DATABASE myDatabaseName
FROM FILE = 'D:\myDatabaseBackup.BAK'
WITH (various options here)

In your case, I think the error message indicates that the backup file location ("D:\AlomohasebSQL.bak" in your error above) is incorrect. Obviously, make sure this is a valid drive, path and filename.

IMPORTANT NOTE: This path needs to be visible to the SQL Server, not to wherever you are running this app. (Thanks @ColinMackay for bringing this up). In a related note, check to see that the SQL Server service account has the proper permissions to read this file at that location.

There is a (smaller) possibility that it doesn't know where to place the database files after it restores them. SQL does have a "default" path, but this is sometimes out of date. If necessary you can specify the path to restore the files to:

RESTORE DATABASE myDatabaseName
FROM FILE = 'D:\myDatabaseBackup.BAK'
WITH MOVE 'primarydatafilename' To 'D:\DB\data.mdf',
     MOVE 'logfilename' To 'D:\DB\log.ldf'

You'd obviously need to know the files that are part of that database. If you don't, just use the SSMS restore wizard. There are ways to find that via the RESTORE command, let me know if you need that.


How to transform Streaming Replication Master-Slave to Cascaded Master-Slave1-Slave2 setup? I've setup streaming replication as part of HA solution on my production enviroment. I've two ubuntu servers PROD1 and PROD2. PROD1 is master and PROD2 is slave. In case of failover the slave database on PROD2 would be promoted to master database.

What I want is to make the slave database (on PROD2) to upstream server and setup another database on PROD1 as downstream server. The idea is to use the upstream server in case of failover and downstream server in case of fallback.

Please advise what configuration changes would be required to achive the above.

Thanks

For a standard cascading replication setup, you need to be on at least PostgreSQL 9.2.

You will also need to set the listen_addresses to something other than localhost, adjust the max_wal_senders parameter in your postgresql.conf to accommodate the additional connection(s), and allow replication connections in your pg_hba.conf.

You'll also want to have WAL archiving set up on your primary, moving the WAL segments to somewhere accessible to the other replicas. This is to ensure that your replicas don't get so far out of sync with your primary that they can no longer catch up due to WAL segment recycling. Also, with WAL archiving, versions before 9.3 can follow the timeline switches without issue.

From there, in your recovery.conf, change the primary_conninfo parameter to point at the cascaded replica.

standby_mode      = 'on'
primary_conninfo  = 'host=<parent_streaming_replica_ip> port=5432 user=replication password=********* sslmode=require' # remove sslmode if you're not enabling ssl
trigger_file      = '/tmp/failover.postgresql.5432'
# To keep in sync with the primary in case of network issues
restore_command   = 'cp /path/to/my/wal/segment/archive/%p %f'
# Follow the latest timeline in the WAL archive in case of a switch.
recovery_target_timeline = 'latest'

As I understand your question, you're proposing a replication topology that looks like this:

Server 1                                                      Server 2
-------------------------------------                         _______________________________________
|PROD1 (primary)                    |------------------------>| PROD2 (secondary streaming replica) |
|                                   |                         |                                     |
|PROD3 (cascaded streaming replica) |<------------------------|                                     |
-------------------------------------                         ---------------------------------------

This would create extra contention for resources between the cascaded replica and the primary, and not give you any extra guaratees of high availability, since two of your three PostgreSQL instances are sharing the same hardware.

It would be better to have a third server, and have that follow the second server's timeline, like this:

Server 1 -----> Server 2 ----> Server 3

So that when you fail over from Server 1 to Server 2, you still have a streaming replica available via Server 3. Once you've fixed the issues with Server 1, you can set it up as a cascading replica of Server 3, as you had when you originally set up Server 3, so that your topology now looks like this:

Server 2 -----> Server 3 ----> Server 1

Alternately, if you don't have a third server available, you can take a new basebackup of the primary PROD2, and set up the former primary as a new secondary PROD1. You could also use pg_rewind, depending on your version of PostgreSQL (9.3+), which would save needing to run a basebackup if that is available to you.

You can find more information out about following timeline switches and why having a WAL archive with streaming replication is a great idea:

Waiting for 9.3: Allow a streaming replication standby to follow a timeline switch

Offsite replication problems and how to solve them -- Streaming replication with WAL shipping example, showing failure modes.

And basics on setup of replication (both streaming and log shipping) are listed on the PostgreSQL Wiki: Binary Replication Tutorial


Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction I received the error that is in question, but do not have a north where to start investigating the cause of this error, ie not a North where to start. I'm getting worried about the situation. can anybody help me?

My execution stack is like this:

TARGETSITE: System.Data.DataTable GetDataTable(System.String, System.Data.CommandType, System.Collections.Generic.Dictionary`2[System.String,System.Object])

STACKTRACE:    at IUS.SQL.GetDataTable(String strQuery, CommandType Tipo, Dictionary`2 parametros)
   at IUS.VerificacaoCliente.Areas(ProfileCommon Profile)
   at Relatorio_Prazos.Page_Load(Object sender, EventArgs e)
   at System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e)
   at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e)
   at System.Web.UI.Control.OnLoad(EventArgs e)
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

Resolving deadlocks can be tricky because you need to be knowledgable about all of the different types of objects and locks and how they can prevent each other's sessions from making any progress. Your stack trace can be used to indicate what resources your process was using, but not the survivor process. Some deadlocks have more than two sessions involved. You have several options to obtain the facts so that an investigation can be conducted.

Run DBCC TRACEON (1204, 1222) to enable the trace flags indicated so that the information is saved into the SQL Server Error Log
Use Profiler and select the Deadlock graph event under the Locks section, then recreate the deadlock
Use Extended Events to capture the deadlock if you have SQL Server 2008 R2 or above

There is also third party monitoring software like Spotlight or Idera that can be configured to capture the deadlock graph and send it to you in an email.

Once you have the information about which objects or resources are involved, you then have the challenge of resolving the problem. Since everything in the deadlock graphs and xml are object IDs and hobts, you will need to look them up using object_name(obj_id), selecting from sys.objects or, in the case of a hobt (heap or b-tree) you would select from sys.partitions.

That should help you get started.


Fastest way to delete matching rows in MySQL? I have more than 10 million records in a table. I want to delete the
records where the email column contains .ac..

What is the fastest method to perform the task?

Table Schema

CREATE TABLE `master` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `email` varchar(255) NOT NULL,
   PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

Delete Query

DELETE FROM `master` WHERE ( email LIKE "%.ac.%" );

You have not given much of a table description. Notwithstanding, I will try to answer your question.

For this example, let's use this table:

CREATE TABLE mytable
(
    id INT NOT NULL AUTO_INCREMENT,
    email VARCHAR(255),
    PRIMARY KEY (id)
);

First, try counting all the IDs that have .ac. in the email column.

SELECT COUNT(1) INTO @CompleteCount FROM mytable;
SELECT COUNT(1) INTO @DeleteCount FROM mytable WHERE LOCATE('.ac.',email) > 0;

There are two approaches to doing a DELETE:

APPROACH #1 : DELETE JOIN

If @DeleteCount < 5% of @CompleteCount, use a list of IDs to DELETE from mytable:

CREATE TABLE mytable_delete_id
SELECT id FROM mytable WHERE LOCATE('.ac.',email) > 0;
ALTER TABLE mytable_delete_id ADD PRIMARY KEY (id);
DELETE B.* FROM mytable_delete_id A INNER JOIN mytable B USING (id);
DROP TABLE mytable_delete_id;

This helps take the guess work out of which rows to DELETE by using a SELECT to get the IDs. The JOIN takes care of the DELETE.

APPROACH #2 : CREATE A NEW TABLE

If you have the following conditions:

@DeleteCount > 30% of @CompleteCount
your table does not have foreign keys/constraints

you could  just create a new table and filter out the rows with .ac. in the email column:

CREATE TABLE mytable_new LIKE mytable;
#insert rows which does not contain '.ac.' from mytable
INSERT INTO mytable_new SELECT * FROM mytable WHERE LOCATE('.ac.',email) = 0;
ALTER TABLE mytable RENAME mytable_old;
ALTER TABLE mytable_new RENAME mytable;
DROP TABLE mytable_old;

EPILOGUE

You can do either of these methods without checking table counta ahead of time. They will both work. If you are concerned that .ac. is in a huge number of rows, go with APPROACH #2. If there are not that many occurrences, go with APPROACH #1.


Archivelog file deleted, resulting in 'ORA-01113: file 1 needs media recovery' (This is not a production database. I'm practicing in a staging enviroment.)

I'm trying to set up RMAN for backup/recovery.

I did a full backup, which seems to be in a good state.

I then did an archivelog backup, which went well. And then, (here's where I f***ed up), I...

Created a new tmp directory.
Ran an RMAN archivelog only backup with the tmp directory as destination
Changed my mind and deleted that tmp directory.

I now realize that RMAN keeps track of paths to previously written backups, so I can't just go around and delete these files!

Now, after I do

RUN {
    RESTORE DATABASE;
    RECOVER DATABASE;
}

I get

ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

failover to previous backup
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/19/2018 20:21:01
RMAN-20506: no backup of archived log found
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 15 and starting SCN of 1361136 found to restore

And when I try to open the database, it looks like follows:

2018-01-19 20:23:04 SYS@... AS SYSDBA> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/path/to/a/file.dbf'

I've tried the solution posted here: ORA-01113: file # needs media recovery ORA-01110

But I can't get that to work. (I run into ORA-00308: cannot open archived log.)

Update: I tried CROSSCHECK BACKUP; followed by DELETE EXPIRED BACKUP; but this unfortunately results in this.

Ran an RMAN archivelog backup to a new directory
  Changed my mind and deleted that directory.

Assuming you still have the archivelog files and they were not deleted when they were backed up, you just need to tell RMAN that those files aren't coming back:

  Use the CROSSCHECK command to synchronize the physical reality of backups and copies with their logical records in the RMAN repository

If, on the other hand, the original archivelog files were deleted along with their backups, you do not have a full recovery set and it is a good thing you said "This is not a production database. I'm practising in a staging environment". A full backup run online is not a consistent backup — you need the archivelog files containing log records from just before the backup starts until just after it finishes or there is no way to restore the database consistently.


Convert Date and Filter by Year I have a date field that is in a 'yyyymmdd' format, and I need to filter the query by the year, using that field.

For example, in my table there is a "Posting Date" field that is in the '20180401' format, and I need to filter the query to only the records with a year of 2018.

It would be much better if you used a proper type for dates, i.e. DATE and not VARCHAR, as that would give you several advantages:

integrity: prevents nonsense data to be inserted in the column, like `20181301', '20190133', '2018ABCd', 'today', 'Xmas19', etc.
storage and index size: DATE is 3 bytes vs 9 of VARCHAR(8).
usability: let you use the ready made conversions and format functions (not for the specific query but possibly for other ones).

If you can't change the type, it's not so bad though as your format is at least an order preserving one, so you can use conditions that can utilize indexes:

WHERE PostingDate >= '20180101'
  AND PostingDate  < '20190101'

which by the way can still be used when (and if) you convert the column type to DATE. Nothing would need to change.


JOIN between 3 tables to show missing data from 2 tables I have 3 DB tables, a Computer table, a Patch table, and a ComputerPatched table. Below is a table mock up to assist my explanation....
DECLARE @Computer AS TABLE (
    [ComputerID] int
    , [ComputerName] nvarchar(50)
);

INSERT INTO @Computer ([ComputerID], [ComputerName])
VALUES (1, 'Computer A'), (2, 'Computer B'), (3, 'Computer C'), (4, 'Computer D');

DECLARE @Patch AS TABLE (
    [PatchID] int
    , [PatchName] nvarchar(50)
);

INSERT INTO @Patch ([PatchID], [PatchName])
VALUES (1, 'Security A'), (2, 'Security B'), (3, 'Security C'), (4, 'Security D');

DECLARE @ComputerPatched AS TABLE (
    [ComputerID] int
    , [PatchID] int
    , [InstallDate] datetime
);

INSERT INTO @ComputerPatched ([ComputerID], [PatchID], [InstallDate])
VALUES
    (1, 1, '20200708'), (1, 3, '20200115')
    , (2, 1, '20200615'), (2, 3, '20200615'), (2, 4, '20200215')
    , (3, 4, '20200427')
;

What I am trying to achieve is to JOIN all results from the Computer table AND Patch table to find out what patches have been installed on what computers and what patches have not been installed. The result I would be expecting would look like something as below...
+------------+--------------+---------+------------+-----------+
| ComputerID | ComputerName | PatchID | PatchName  | Installed |
+------------+--------------+---------+------------+-----------+
|          1 | Computer A   |       1 | Security A | 20200708  |
|          1 | Computer A   |       2 | Security B | NULL      |
|          1 | Computer A   |       3 | Security C | 20200115  |
|          1 | Computer A   |       4 | Security D | NULL      |
|          2 | Computer B   |       1 | Security A | 20200615  |
|          2 | Computer B   |       2 | Security B | NULL      |
|          2 | Computer B   |       3 | Security C | 20200615  |
|          2 | Computer B   |       4 | Security D | 20200215  |
|          3 | Computer C   |       1 | Security A | NULL      |
|          3 | Computer C   |       2 | Security B | NULL      |
|          3 | Computer C   |       3 | Security C | 20200427  |
|          3 | Computer C   |       4 | Security D | NULL      |
|          4 | Computer D   |       1 | Security A | NULL      |
|          4 | Computer D   |       2 | Security B | NULL      |
|          4 | Computer D   |       3 | Security C | NULL      |
|          4 | Computer D   |       4 | Security D | NULL      |
+------------+--------------+---------+------------+-----------+

I am having trouble envisioning how to write this query I believe I would need some form of OUTER JOIN but everything I have tried has failed. Any help would be much appreciated!

SELECT c.*, p.*, cp.InstallDate
FROM Computer c
CROSS JOIN Patch p
LEFT JOIN ComputerPatched cp ON c.ComputerID = cp.ComputerID
                            AND p.PatchID = cp.PatchID


How big is my oracle database? I need to determine the total size of an oracle database. Ideas on improving the following query?

SELECT 'DataFiles: ' AS TYPE, SUM (bytes / 1024 / 1024) AS MB
  FROM dba_data_files
UNION
SELECT 'Temp.: ', SUM (bytes / 1024 / 1024) AS MB FROM dba_temp_files
UNION
SELECT 'Redologs: ', SUM (bytes / 1024 / 1024) AS MB FROM v$log;

Redo logs can be mirrored, your query does not take into account that. Controlfiles are also part of the database.

select 'Datafiles', sum(bytes)/1024/1024 from dba_data_files union all
select 'Tempfiles', sum(bytes)/1024/1024 from dba_temp_files union all
select 'Redo logs', sum(bytes)/1024/1024 from v$log l join v$logfile lf on (l.group# = lf.group#) union all
select 'Controlfiles', sum(block_size * file_size_blks)/1024/1024 from v$controlfile;

There are other files that are not critical, but they need space, for example standby redo logs and the block change tracking file:

select 'Datafiles', sum(bytes)/1024/1024 from dba_data_files union all
select 'Tempfiles', sum(bytes)/1024/1024 from dba_temp_files union all
select 'Redo logs', sum(bytes)/1024/1024 from v$log l join v$logfile lf on (l.group# = lf.group#) union all
select 'Controlfiles', sum(block_size * file_size_blks)/1024/1024 from v$controlfile union all
select 'Standby logs', sum(bytes)/1024/1024 from v$standby_log l join v$logfile lf on (l.group# = lf.group#) union all
select 'Change tracking', bytes/1024/1024 from v$block_change_tracking;


Delete rows in a table that has multiple FKs I need to delete all the rows in the ContentData table that have the word 'DV95' in the htmldata column.

Here is what I am using:

BEGIN TRANSACTION

DELETE Contentdata
FROM Contentlinks INNER JOIN contenttasks
   ON contentdata.contentid = contenttasks.contentid
WHERE contentdata.htmldata LIKE '%DV95%'

Here are my questions:

Is there problem in the syntax?
It keeps showing me a number of FK contraint issues due to number of table dependencies. How do I delete these rows?

If I got you right - you need to delete referencing rows prior to deleting the referenced rows.

begin transaction
DELETE Contenttasks
FROM contentdata
 where contentdata.contentid = contenttasks.contentid
and contentdata.htmldata LIKE '%DV95%'

Delete from contentdata where htmldata like '%dv95%'
Commit


Table and query design options We have a alert table which have deviceID as a foreign key. At present one alert just refers to one devive so things works smoothly with join. The problem start now when one alert now is to refer to more then one device. Thus we plan to keep a separate table with this alertID as foreign keys and devices ID in a new table say alertDetails. The issue now comes with this method is that there is no direct method to gain all the devices for a particular alert. Is there any solution or we have to run two separate queries where first one check if a particular type of alertCode(referring to more then one device) then run next query.

While it is not exactly clear what you need but if you design your tables similar to below then you should be able to retrieve the details that you need.

create table alert
(
  id int,
  aname varchar(10)
);
create table device
(
  id int,
  dname varchar(10)
);
create table alertDetails
(
  alertId int,
  deviceId int,
  alertDate datetime
);

Then when you query, you could use the following:

select *
from alert a
left join alertdetails ad
  on a.Id = ad.alertid
left join device d
  on ad.deviceid = d.id
where a.id = 1

See SQL Fiddle with a Demo


How to track blocking that happen for less than a second - SQL Server I am trying to troubleshoot a blocking issue which happens for less than a second.
The OLTP application is very sensitive and has to have an response time of less than 200ms for some transactions as per the agreed SLA.
We had some lock escalation issues with the new code release which we were able to solve with reducing the batch size in the updates.
Even with the small batch size, we suspect that the new sp is blocking the same rows that the OLTP transactions is updating.

I need to find the session that is getting blocked and the resource its waiting on. As per my understanding "blocked process threshold" can set for a minimum of 1 second and so this will not capture the blocking.

I am experimenting with wait_info and wait_completed x events.

Is there any other way we could track this. Thanks

As you are specifically interested in locking rather than general waits the locks_lock_waits extended event sounds more suitable.

With a filter on increment >= 200

CREATE EVENT SESSION [locks_lock_waits] ON SERVER
ADD EVENT sqlserver.locks_lock_waits(
        ACTION(sqlserver.sql_text)
            WHERE  ( [sqlserver].[is_system] = 0
                     AND [increment] >= 200
                     AND [counter] <= 1000 )
    )
ADD TARGET package0.ring_buffer;

GO

ALTER EVENT SESSION [locks_lock_waits]
ON SERVER  STATE = start;

The above gathers the statements waiting on locks for the threshold amount of time but doesn't give the specific lock resource.

I have never used this event and have no insight into how much overhead this session would cause on your production server.

I found this video on the topic. That does strongly recommend filtering on counter to reduce the number of events collected and I have done so above.

It also mentions an old legacy undocumented command

dbcc lock(StallReportThreshold, 200) -- 200 is threshold in ms

Which (if trace flag 3605 is enabled) dumps out limited information such as the below to the SQL Server error log.

  Process 53 waited 6844 ms for S lock on RID: 2:1:120:2 result: OKWAIT

I just mention this in passing as extended events would be clearly preferable anyway as it is documented and much more powerful.


MongoDB - count current read/write operations is it possible to count the current read or write operations on a mongodb replica set? (including all databases not just a single database)
Thank you!
Best regards

The serverStatus command contains operation counts, which report on database operations by type since the mongod instance last started.
If you need to find the number of operations over a specific time period, retrieve the serverStatus result periodically for comparison over time.


Trouble understanding INITRANS in oracle I need to calculate a tablespace size and part of the calculation requires a value for INITRANS which I don't fully understand and I wasn't sure if I should just use the default value 2.

My business rules specify that data is inserted from up to 50 different locations so I am to assume some concurrency, is this an indication that I should use an INITRANS of 50 or am I misunderstanding what INITRANS is?

As always: it depends.
However in most cases the default values are adequate.

The business requirement of handling 50 concurrent inserts (transactions) alone does not imply the need for setting INITRANS 50. Are they really that concurrent? Will the duration of the transactions overlap each other so they really happen at the same time? All the 50?

INITRANS specifies the minimum number of transaction slots (ITL slots) in a database block. Of course, the number of ITL slots can go higher if needed, the database manages that. The maximum number depends on the database block size (as the ITL entries are not allowed to occupy 50% of more of a database block, and 1 ITL entry takes 24 bytes), and MAXTRANS (=255 from version 10.2).

So, if:

all the new data end up in the same database block (not necessarily a table, a common example is a PK index on an ID column, where the ID values come from a sequence)
changes are not committed immediately (or quick
enough), so locks will be held for a long time, and all the 50 sessions will hold the locks at the same time

then yes, the database block will need the 50 ITL slots. But even then, most likely you will not need to set INITRANS to 50, because the database will manage this automatically, and 50 is well below the actual limit in a standard 8K database block.

This may not be true for DELETE, UPDATE, SELECT ... FOR UPDATE, as when they happen, the database block is most likely full and the number of ITL slots may not increase in that case. Imagine a full database block that stores 50 rows and has 2 ITL slots, and 50 concurrent transactions want to lock 1 row each with SELECT ... FOR UPDATE. But with INSERT, that uses empty database blocks or continues writing to existing ones with free space in them, it should not be a problem.

Note that there are many ifs, mays and questions here, and as I said, in most of the cases, the default values are adequate. Yes, I can create an artificial example that works as above, but real scenarios are fortunately different.


SGA size not decreasing after reducing memory_target (Oracle 11.2.0.1) I have an Oracle 11.2.0.1 database using dynamic memory allocation with parameter MEMORY_TARGET. When I decrease it with SCOPE=BOTH, the size of the SGA doesn't seem to decrease along with it. I have tested this on multiple systems. Here is an example

SQL> show parameter memory_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------------
memory_target                        big integer 820M
SQL> sho sga

Total System Global Area  855982080 bytes
Fixed Size                  2180544 bytes
Variable Size             520096320 bytes
Database Buffers          327155712 bytes
Redo Buffers                6549504 bytes
SQL> alter system set memory_Target=720M scope=both;

System altered.

SQL> show sga

Total System Global Area  855982080 bytes
Fixed Size                  2180544 bytes
Variable Size             520096320 bytes
Database Buffers          327155712 bytes
Redo Buffers                6549504 bytes
SQL> show parameter memory_target;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------------
memory_target                        big integer 720M
SQL>

However, after I bounce the instance, the SGA seems to size itself appropriately.

SQL> sho sga

Total System Global Area  751595520 bytes
Fixed Size                  2179536 bytes
Variable Size             415239728 bytes
Database Buffers          327155712 bytes
Redo Buffers                7020544 bytes
SQL>

Do not use show sga for querying the current SGA size.

Total System Global Area in the output of show sga is computed as select sum(value) from v$sga, you can easily confirm this by enabling SQL trace.

SQL> select * from v$sga;

NAME                      VALUE
-------------------- ----------
Fixed Size              2258040
Variable Size         490736520
Database Buffers      356515840
Redo Buffers            6471680

SQL> select sum(value) from v$sga;

SUM(VALUE)
----------
 855982080

This includes Variable Size, and that is not the current usage. Use v$memory_dynamic_components. For an even more detailed list, use v$sgastat and v$pgastat, I will not post output of those here.

SQL> show parameter memory_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memory_target                        big integer 820M

SQL> show sga

Total System Global Area  855982080 bytes
Fixed Size                  2258040 bytes
Variable Size             494930824 bytes
Database Buffers          352321536 bytes
Redo Buffers                6471680 bytes

SQL> select component, current_size, min_size, max_size from v$memory_dynamic_components;

COMPONENT                 CURRENT_SIZE   MIN_SIZE   MAX_SIZE
------------------------- ------------ ---------- ----------
shared pool                  134217728  130023424  134217728
large pool                     8388608    8388608   71303168
java pool                      4194304    4194304    4194304
streams pool                   4194304          0    4194304
SGA Target                   515899392  515899392  515899392
DEFAULT buffer cache         352321536  289406976  352321536
KEEP buffer cache                    0          0          0
RECYCLE buffer cache                 0          0          0
DEFAULT 2K buffer cache              0          0          0
DEFAULT 4K buffer cache              0          0          0
DEFAULT 8K buffer cache              0          0          0
DEFAULT 16K buffer cache             0          0          0
DEFAULT 32K buffer cache             0          0          0
Shared IO Pool                       0          0          0
PGA Target                   343932928  343932928  343932928
ASM Buffer Cache                     0          0          0

Now, decreasing memory_target (and that is not necessarily the SGA).

SQL> alter system set memory_target=720M scope=memory;

System altered.

SQL> show parameter memory_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memory_target                        big integer 720M

SQL> show sga

Total System Global Area  855982080 bytes
Fixed Size                  2258040 bytes
Variable Size             494930824 bytes
Database Buffers          352321536 bytes
Redo Buffers                6471680 bytes

show sga still shows the old values.

SQL> select component, current_size, min_size, max_size from v$memory_dynamic_components;

COMPONENT                 CURRENT_SIZE   MIN_SIZE   MAX_SIZE
------------------------- ------------ ---------- ----------
shared pool                  134217728  130023424  134217728
large pool                     8388608    8388608   71303168
java pool                      4194304    4194304    4194304
streams pool                   4194304          0    4194304
SGA Target                   515899392  515899392  515899392
DEFAULT buffer cache         352321536  289406976  352321536
KEEP buffer cache                    0          0          0
RECYCLE buffer cache                 0          0          0
DEFAULT 2K buffer cache              0          0          0
DEFAULT 4K buffer cache              0          0          0
DEFAULT 8K buffer cache              0          0          0
DEFAULT 16K buffer cache             0          0          0
DEFAULT 32K buffer cache             0          0          0
Shared IO Pool                       0          0          0
PGA Target                   239075328  239075328  343932928
ASM Buffer Cache                     0          0          0

Notice how SGA Target did not change at all. Instead, PGA Target was decreased. Decreasing it further:

SQL> alter system set memory_target=420M scope=memory;

System altered.

SQL> show sga

Total System Global Area  855982080 bytes
Fixed Size                  2258040 bytes
Variable Size             629148552 bytes
Database Buffers          218103808 bytes
Redo Buffers                6471680 bytes

Notice how Variable Size has changed, but Total System Global Area has not.

SQL> select component, current_size, min_size, max_size from v$memory_dynamic_components;

COMPONENT                 CURRENT_SIZE   MIN_SIZE   MAX_SIZE
------------------------- ------------ ---------- ----------
shared pool                  134217728  130023424  134217728
large pool                     8388608    8388608   71303168
java pool                      4194304    4194304    4194304
streams pool                   4194304          0    4194304
SGA Target                   381681664  381681664  515899392
DEFAULT buffer cache         218103808  218103808  352321536
KEEP buffer cache                    0          0          0
RECYCLE buffer cache                 0          0          0
DEFAULT 2K buffer cache              0          0          0
DEFAULT 4K buffer cache              0          0          0
DEFAULT 8K buffer cache              0          0          0
DEFAULT 16K buffer cache             0          0          0
DEFAULT 32K buffer cache             0          0          0
Shared IO Pool                       0          0          0
PGA Target                    58720256   58720256  343932928
ASM Buffer Cache                     0          0          0

Variable Size is there because, memory_max_size and sga_max_size was computed from memory_target at instance startup time.

SQL> show parameter memory_max

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memory_max_target                    big integer 820M
SQL> show parameter sga_max

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 820M

This allows us to increase SGA back to that point. But those you can not change dynamically:

SQL> alter system set memory_max_target=420M scope=memory;
alter system set memory_max_target=420M scope=memory
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

SQL> alter system set sga_max_size=420M scope=memory;
alter system set sga_max_size=420M scope=memory
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

SQL> alter system set memory_max_target=920M scope=memory;
alter system set memory_max_target=920M scope=memory
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

SQL> alter system set sga_max_size=920M scope=memory;
alter system set sga_max_size=920M scope=memory
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified


Is there a limit to how many backup files I can restore from for a single database on RDS SQL Server? The AWS documentation for using native restore specifies that restoring from multiple files is allowed:

  To avoid errors while restoring multiple files, make sure that all the
  backup files have the same prefix, and that no other files use that
  prefix.

  The following example shows multiple-file restore.

  exec msdb.dbo.rds_restore_database @restore_db_name='database_name',
  @s3_arn_to_restore_from='arn:aws:s3:::bucket_name/bakup_file_*'

How many files can I restore from?

There is a limit of 10 backup files.

When attempting to restore from more than 10 backup files, the following error occurs:

  The Restore task is unable to restore from more than 10 backup
  file(s). Please reduce the number of files matched and try again.


Postgres files postgresql.conf and pg_hba.conf - which take priority? Which of the two postgres configuration files postgresql.conf and pg_hba.conf takes priority?
pg_hba.conf controls client authentication methods including 'md5' and 'scram-sha-256'
postgresql.conf includes an entry for password_encryption which can be 'md5' or 'scram-sha-256' (or blank, I guess)
So if these values aren't set to the same thing, which takes priority?

There is no priority to choose because they have different purposes.

password_encryption in postgresql.conf tells how to hash a new password when it's changed or a new user is created with a password.

the field in pg_hba.conf tells what kind of authentication scheme should be used when a client that matches the rules attempts to connect.

When connecting with a client that does not support SCRAM authentication:
If the first line that matches in pg_hba.conf for this connection attempt has, in the METHOD field:

scram-sha-256, the connection will be rejected.

md5 and the password of this account is stored with an
md5 hash (independently of password_encryption), the connection
will succeed.

md5 and the password of this account is stored with an
scram-sha256 hash (independently of password_encryption), the connection will be rejected.

A superuser can check what kind of password is assigned to existing accounts by looking at the hashed passwords in the system table pg_catalog.pg_authid.


Restoring Database when system can't find filepath specified I have a database backup file stored in a hard drive V: named someFile.bak. The file path is simply V:\someFile.bak. I have been trying to load this into SQL Server Management Studio via the command

RESTORE DATABASE someDatabase
FROM DISK = 'V:\someFile.bak'

but I keep getting the error:

Msg 3201, Level 16, State 2, Line 4
Cannot open backup device 'V:\someFile.bak'. Operating system error 3(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Line 4
RESTORE DATABASE is terminating abnormally.

This suggests to me that SSMS is having issues finding the file path, despite it being specified correctly. Does this have anything to do with the fact that this is from a hard drive rather than from the internal drive (like C:)? If so, is there a better way to restore a database from the hard drive? I am asking this because someFile.bak is too large to store on my internal drive.

The file path is simply V:\someFile.bak

V: relative to WHAT machine?

  I have been trying to load this into SQL Server Management Studio via the
  command

No, you have not. You have instructed SSMS to send this command to the server, which will execute it ON THE SERVER.

At no point is SSMS loading anything.

The typical mistake here would be that you have a V: drive attached to your local machine and then think that the server will find the file under V: - it will not. The file must be reachable for the SERVER process, not you.

And SSMS only acts as a glorified text editor in this case, sending your command to the database server.