Select ONLY Max(date) when there's repeating dates Here is a sample of the data contained in my Tracking table:

tracking_id   date_action  action_id    staffing_id
------------- -----------  -----------  -----------
 1             2019/03/04  4             2
 2             2019/03/04  3             2
 7             2018/10/25  67            3
 4             2018/10/25  8             3
 5             2019/05/05  10            5
 6             2019/05/05  6             5
 8             2019/03/02  55            6

The result I want is the following:

  tracking_id   date_action  action_id    staffing_id
    ------------- -----------  -----------  -----------
     2             2019/03/04  3             2
     4             2018/10/25  8             3
     6             2019/05/05  6             5
     8             2019/03/02  55            6

I need to find the max(date_action_taken) group by staffing_id and join it with other tables to get the columns in the Action table using action_id and all of the columns in the Staffing table using the staffing_id.

I first try to get the the max date in the Tracking table for each staffing_id:

SELECT staffing_id, Max(Tracking.date_action_taken) AS MaxOfdate
FROM Tracking
GROUP BY staffing_id

When I join the query above back to the same table to get the other colmns of the Tracking table. It still returns the max(date_action) but it also returns the repeated ones. The query I used is below:

Select  tracking_id, t2.mxdate, t1.action_id, t1.staffing_id
FROM Tracking t1
inner join
(select  max(date_action) as mxdate, staffing_id
From Tracking
Group by staffing_id
) t2
on t1.staffing_id = t2.staffing_id and t1.date_action_taken = t2.mxdate

I even tried another query to get the max(tracking_id) instead using this query:

Select Tracking.*
From Tracking
Where tracking_id IN
(Select Max(tracking_id) as t_id
From Tracking
Group by staffing_id
)

This works great until I realize that the max(tracking) is not necessarily the max(date_action).

I even used DISTINCT. And it still returns the repeated max(date_action).

Select  DISTINCT tracking_id, t2.mxdate, t1.action_id, t1.comment, t1.staffing_id
FROM Tracking t1
inner join
(select  DISTINCT max(date_action_taken) as mxdate, staffing_id
From Tracking
Group by staffing_id
) t2
on t1.staffing_id = t2.staffing_id and t1.date_action_taken = t2.mxdate

I am not sure what to do. Is there a way, I can first get the max(date_action) by staffing_id and if two of them are repeated to get the max(tracking_id) between the max(date_action).

I am thinking of adding another column to act as a "flag". A data type yes/no, column named (isTheLastAction) so on each insert in the tracking table. It find  the previous  isTheLastAction, put it at false, and the new insert to true within the same staffing_id that way, it doesn't depend on the date_action nor the tracking_id. I am just not sure how to do that.

Any thoughts on either helping me solve the max(date_action) or using the flag on insert?

Akina has already pointed out critical problems with the data... that there is no existing way to determine the proper ordering of the Tracking rows.  It is tempting to just skip this question, but despite the problems, there are some concepts and SQL queries patterns which can help here.

In a traditional relational database, the storage order of records should never be assumed.  Although it may be natural for records to be stored in the order they are added, there is actually no guarantee of this.  A database file can become segmented, then later compacted, etc., and the rows may not be reconstructed in the same order.  Although Access might usually store the records in a particular order, it is a mistake to rely on any such insertion or storage order.

The example [Tracking] data implies a given order by showing [tracking_id] = 7 before [tracking_id] = 4, and comments further indicate that one was inserted before the other.  This mistaken assumption is likely causing some of the confusion.
A related principle of SQL is that query rows should not be assumed to be in a particular order except when an ORDER BY clause is specified.  Once again, it is a mistake to rely on the default order just because it usually matches the insertion order.
Specification of various constraints and indexes can be used to facilitate efficient ordering.  For example, if one specifies a primary key on the table, it is likely that the rows will be stored by that primary key, but they could very well be stored in segmented groups/patterns to facilitate quick searches.  It is still a mistake to expect an SQL query to always return the rows in primary-key order without an explicit GROUP BY clause.

At least within Access, logical order can be implemented by using a long-integer Autonumber column.  Access keeps track of used Autonumber values and it will consistently assign a unique number to new rows.  In this case, it can be trusted that a higher number was inserted after a lower number.  (Note:  This behavior is not universally true, since some database systems by default will reuse numbers of deleted records.)

It is such common practice for tables to use an Autonumber column, that it was natural to assume that [tracking_id] was such a column.  Most users will assume at first read that [tracking_id] = 7 comes AFTER [tracking_id] = 4.  Honestly, the fact that they are not in such an order is perplexing, especially without some other field to indicate the insertion order.
Another alternative is to use a date field, a "timestamp" field.  If the only reason for this field is to indicate insertion/creation order, then I suggest a Date/Time column with Default Value = Now().  (Since Access dates only have resolution down to a second, this is only valid if records will not be inserted faster than each second.)
The "flag" column as you described is discourage and should be unnecessary if one of the other above schema is used.  Manually changing flag values upon insertion and edits can become a maintenance nightmare and without proper constraints (Validation Rules and/or unique indexes), it can lead to contradictory data.

For the following queries, I will assume that [tracking_id] is a unique Autonumber field and that it consistently represents insertion order so that a higher number is the "latest" record with the same [date_action].

I assume that it is possible for each [staffing_id] to have multiple [date_action] values.  The example data shows only one date (albeit multiple rows) for each [staffing_id]. It is tempting to simplify the queries based only on the sample data, but that is a mistake which can return incorrect aggregate values.  The existing two answers make this mistake.

The question already contains all of the various parts of a solution, but never combines them to produce a complete solution.  The mistake is in assuming that the desired results can be achieved with only one aggregate subquery.  In fact, there needs to be two subqueries which find the max date and the max tracking_id separately!  (Honestly, the question text outlines the following three steps using words, but then fails to match the logic in the queries.)

Query 1:  First get the maximum [date_action] for a given [staffing_id],
Query 2:  then get the maximum [tracking_id] for the maximum date and id found in query 1.  (You can't find the maximum [tracking_id] value separately or at the same time--in the same aggregate query--before knowing the maximum date.)
Query 3:  Finally get the record details (other fields) by linking to the found maximum [tracking_id].  (Because the staffing_id and date_action have already been selected in the inner queries AND because tracking_id is considered a unique key, it is only necessary for final selection on that key value.  In other words, it is redundant to include the other fields in the outermost ON clause.)

SELECT t1.*
FROM Tracking AS t1
  INNER JOIN (
    SELECT Max(t2.tracking_id) AS MaxOfTracking_id
    FROM Tracking AS t2
      INNER JOIN (
        SELECT t3.staffing_id, Max(t3.date_action) AS MaxOfdate
        FROM Tracking AS t3
        GROUP BY t3.staffing_id
      ) AS tad
      ON t2.staffing_id = tad.staffing_id AND t2.date_action = tad.MaxOfdate
    GROUP BY t2.staffing_id, t2.date_action
  ) AS tai
  ON t1.tracking_id = tai.MaxOfTracking_id
ORDER BY t1.staffing_id

Returns:

tracking_id   date_action  action_id  staffing_id
2             3/4/2019     3          2
7             10/25/2018   67         3
6             5/5/2019     6          5
8             3/2/2019     55         6


Memory management scenario with MongoDB I'm implementing a medium scale marketing e-commerce affiliation site, which has following estimates,

Total Size of Data: 5 - 10 GB
Indexes on Data: 1 GB approx (which I wanted to be in memory)
Disk Size (fast I/O): 20-25 GB
Memory: 2 GB
App development: node.js

Working set estimation of Query: Average 1-2 KB, Maximum 20-30 KB of text base article

I'm trying to understand whether MongoDB would be right choice for database or not. Index is going to be fairly downsize of Memory but I have noticed that after querying that MongoDB, it has occupied the memory (size of result set) for caching query. In 8 hours I'm expecting that all queries' depth would cover almost 95% of data, in that scenario how will MongoDB manage limited memory scenario also app instance of node.js running on same server.

Would a MongoDB a right choice for this scenario or I should go for other JSON based no-SQL Databases.

Mongodb uses mmap for memory management, relies 100% on the OS memory management. This means whatever memory you have left it has a really high possibility will be eaten up by mongo cause the entire datafile structure is mapped to virtual address space (It is not certainly a problem while the paging between physical and virtual memory is not that dumb to break your system down).

While Linux will page out some parts of the physical memory, so not the whole virtual segment have to be in physical memory just the recently accessed parts (check this), depend on the usage pattern mongo can run without problem.  If the OS will have to extensively page things mongodb will be slow.  You can restrict the usage of the memory. I would recommend to use cgroups. For your probable workload memory usage you can check also this documentation to have some idea.


Please check the disk space on target machine


Incorrect results when combining full-text search and regular comparison create table "Foo"
(
"id" integer identity (1000, 1) not null,
"name" nvarchar(100) not null
)
alter table "Foo" add constraint "Foo_PK" primary key ("id");
create fulltext index on "Foo" ("name" language 0x0) key index "Foo_PK" on "catalog" with stoplist = off;

The following query returns no rows
select * from "Foo" where contains("name", ' "پ*" ') and "id" > 1082;

While there are 26 rows matched by contains("name", ' "پ*" ') and 87 matched by "id" > 1082 individually. The following two queries have 23 rows in common, oddly, their intersection returns no rows.
select * from "Foo" where contains("name", ' "پ*" ');
select * from "Foo" where "id" > 1082;

And more strangely, the following query returns exactly 23 rows. All "id"s are above 1082.
select * from "Foo" where contains("name", ' "پ*" ') and "id" > 1081;

Someone please explain what is going on in SQL Server.

I think your primary problem is that you are using non-Unicode string delimiters for Unicode data.
Compare:
SELECT  ' "پ*" ';
SELECT N' "پ*" ';
-------^ this N is important


Deleting duplicates with group by and count What is the fastest method to convert the following query:

SELECT COUNT(*) as c FROM tbl_fields
WHERE fieldnotes IS NULL
GROUP BY fieldno,fieldserial,id,fielddate,fieldsid
HAVING COUNT(*) > 1;

... into one that will delete duplicated records? The table does not have any primary keys and contains several million entries.

According to your query, you have fieldno,fieldserial,id,fielddate,fieldsid as a rule for uniqueness in the GROUP BY clause.
You can try this :
CREATE TABLE tbl_fields_unique LIKE tbl_fields;
ALTER TABLE tbl_fields_unique
ADD UNIQUE KEY unq (fieldno,fieldserial,id,fielddate,fieldsid);
INSERT IGNORE INTO tbl_fields_unique
SELECT * FROM tbl_fields;
ALTER TABLE tbl_fields RENAME tbl_fields_old;
ALTER TABLE tbl_fields_unique RENAME tbl_fields;

This will filter rows with duplicate fieldno,fieldserial,id,fielddate,fieldsid fields. Look over the new table. Once you are satisfied with the contents of the tbl_fields_unique table, do this:
ALTER TABLE tbl_fields RENAME tbl_fields_old;
ALTER TABLE tbl_fields_unique RENAME tbl_fields;

Give it a Try !!!


When to create multiple-column index? When to create multiple-column index in Mysql?

Should I measure the slow queries and create multiple-column index for them?

MySQL can use multiple-column indexes for queries that test all the columns in the index, or queries that test just the first column, the first 2 columns, the first 3 columns, and so on. If you specify the columns in the right order in the index definition, a single composite index can speed up several kinds of queries on the same table.

SELECT * FROM test WHERE first_name='Andrew';

SELECT * FROM test
  WHERE last_name='Smith' OR first_name='Andrew';

Suppose that you issue the following SELECT statement:

mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

If a multiple-column index exists on col1 and col2, the appropriate rows can be fetched directly. If separate single-column indexes exist on col1 and col2, the optimizer attempts to use the Index Merge optimization..

Source: http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html


SSRS - Graph Line Value for Parameter For the purpose of reporting database growth, I am created everything except one and most important thing. In SSDT I'm created report, which contains graph line which need to be show in depend of parameters. I have drop-down list of databases, graph where are in sections: VALUES (Fields!dataSize.Value), Category Groups(date), Series Groups (databaseName).
I have parameter databases_id (dataset1, value field: id, label field: databaseName).

When I start report and select some database and click on VIEW REPORT, I get lines for every parameters(databases) on list. How to get only lines for selected parameters(databases)?

You need to refer to the parameters in your main sql query.

Something like:

Where databaseName = @databases

Or if you want to be able to select multiple databases.

Where databaseName in (@databases)

Here is an example using date parameters and multi-select parameters in a report https://technet.microsoft.com/en-us/library/aa337396(v=sql.105).aspx

If you Google " ssrs parameter sql query" there are lots of working examples and even some youtube videos giving step by step instructions.


How to output to same line with relation information same table? This is my current SQL Server query and almost what I want to do:

select "dokumentgruppe" + ', ' + convert(varchar(10), dokumentgruppenr)
    + ', ' + 'Parent:' + convert(varchar(10), parentgruppenr)
    as "Dokumentgruppe"
from "dbo"."dokumentgruppe"
where aktiv = '1'
order by "Dokumentgruppe"

It gives me this result:

Budsjetter, 29, Parent:0
E-post, 36, Parent:0
Fyllearrester, 40, Parent:38
Inngående, 42, Parent:36
JuksOgFanteri, 38, Parent:0
Legeattester, 39, Parent:38
Ligning, 26, Parent:0
Periodiske regnskapsrapporter, 30, Parent:0
Permanente opplysninger, 23, Parent:0
Produksjon Fakturering, 33, Parent:0
Produksjon Lønn, 32, Parent:0
Produksjon Regnskap, 31, Parent:0

The thing is, I want to show the actual name of the Parentgruppe instead of ParentgruppeID. The Parentgruppe has an ID and a corresponding name to it.

Like this example:

Legeattester, 39, Parent:*JuksOgFanteri

You can get this result by joining the table to itself.

select c.dokumentgruppe
    + ', ' + convert(varchar(10), c.dokumentgruppenr)
    + ', ' + 'Parent:' + isnull(p.dokumentgruppe,'')
    + ', ' + convert(varchar(10), c.parentgruppenr)
    as Dokumentgruppe
from dbo.dokumentgruppe as c
  left join dbo.dokumentgruppe as p on c.parentgruppenr=p.dokumentgruppenr
where c.aktiv = '1'
order by c.Dokumentgruppe

This is an adjacency list style heirarchy, you can find a good introduction to them here: Sql Server Bible Sample Chapter 17: Traversing Hierarchies


Query system specs (cpu, memory, data disks, free space) using T-SQL Is there an ability to query below system specifications using T-SQL?

cpu (show number of processors / cores / frequency)
memory (show total amount of RAM on a machine; show allocated RAM; show really used RAM)
data disks (show total number of disks, their volume, free space available)
show Windows edition and version

Yes, you bet. Let's start with the open source First Responder Kit (disclaimer: I'm one of the script authors) - install sp_Blitz, and then:

sp_Blitz @CheckServerInfo = 1

And at the bottom, you get a bunch of information about your system:

I've taken the liberty of rearranging the columns a little in that screenshot because I want to show you the finding, details, and the CheckID.

When you see a CheckID that has information you want to use, open up the sp_Blitz script and do a search for that CheckID. For example, if you want to know how we're determining that you have 4 logical processors and 31GB RAM, do a control-F for 84 (the CheckID), and you'll see the dynamic management views (DMVs) where we're gathering that data from.

Check 84 is a great example because it requires different logic based on what version of SQL Server you're trying to check, too.

Rather than giving you the specific answers for each of the 4 questions you asked, I'm just teaching you this process because I bet if you need those 4 things, you probably need a lot more, too - and you'll find a lot of 'em in sp_Blitz. Hope that helps!


Connect analysis services to oracle database I am looking to connect analysis services to an oracle database, and when I hit Process in AS, it gives me a generic Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error: [DataSource.Error] Oracle: ORA-12154: TNS:could not resolve the connect identifier specified.

Things I've tried:

When I create the tables in VS2017, the connection works
When I do a tnsping from the command line, that works
I replaced all of the default tnsnames.ora files in ./app with the one that I know works (its what the TNS_ADMIN env variable is set to

I don't see where the location of the actual utilities used by the service are detailed, so I can't test it using those.

How can I make SSAS use the tnsnames.ora file that I need?

How can I make SSAS use the tnsnames.ora file that I need?

The best answer is don't bother.  Use the Easy Connect naming method, and bypass the TNSNAMES.ORA file entirely.

Alternatively you can set the TNS_ADMIN environment variable to the folder containing your TSNNAMES.ORA file and reboot your server for the SSAS service to see get the changed environment.


Ola Hallengren backup systax when excluding a database I would like to backup most of the databases except the database DBA.
I use below syntax in the job step:

EXECUTE [dbo].[DatabaseBackup]
    @Databases = 'USER_DATABASES', '-DBA'
    @Directory = N'G:\BACKUP',
    @BackupType = 'DIFF',
    @Verify = 'Y',
    @CleanupTime = 336,
    @CheckSum = 'Y',
    @LogToTable = 'Y'

But it gives an error:

  Msg 102, Sev 15, State 1, Line 3 : Incorrect syntax near '@Directory'. [SQLSTATE 42000]

I also tried:

@Databases = 'USER_DATABASES', -'DBA'

does not work either.

Can anyone help? Thanks.

it has to be

@Databases =  'USER_DATABASES, -Db1'

See https://ola.hallengren.com/sql-server-backup.html

Assuming you want to backup all User Databases except one named DBA, your statement should be:

EXECUTE [dbo].[DatabaseBackup]
    @Databases = 'USER_DATABASES, -DBA',
    @Directory = N'G:\BACKUP',
    @BackupType = 'DIFF',
    @Verify = 'Y',
    @CleanupTime = 336,
    @CheckSum = 'Y',
    @LogToTable = 'Y'


How to track specific item as part of a kit Here is my situation. I have over 50 boxes each with a unique ID number. That number is tracked for inventory purposes. My problem is that inside each box there is one specific part that is tracked independently of the box. What I am trying to accomplish is to be able to track the serial number from the part and link it up to which box it goes in such that the following rules are followed:

Each box cannot contain more than one primary part
Not every box has a primary part (some were broken and never replaced)
If a primary part needs to be moved from one box to another the original box it was taken from shows up as having no primary part.

I thought this would be fairly easy to accomplish with MS Access since it is already deployed across the network, but I am having a hard time preventing multiple boxes from being assigned the same primary part. If I just put the part SN as a field in the same table as the box then I have to remember to change 2 things any time one of the parts moves.

Put the Box ID against the primary part, instead of the primary part serial number against the box. To see what's in the box you need to search the Primary Part table for the Box ID. If you move which box a part is in, you just update it once.

You could make a unique index on BoxID as well to ensure that there are never 2 primary parts in the same box.


SQL error: Unknown column in 'where clause' I have following very simple PHP MySQL Query:

$sql = 'SELECT * FROM bundesland WHERE bkuerzel = ' . $_GET['q'];
$result = mysql_query($sql, $con);

When I execute the query with a click on a item with the value "Wien" on a dropdown menu I get "SQL error:Unknown column 'Wien' in 'where clause' in", but as you can see in the picture below I have the column.

The value for $_GET['q'], which must be Wien, should be in quotes

$sql = "SELECT * FROM bundesland WHERE bkuerzel = '" . $_GET['q'] . "'";
$result = mysql_query($sql, $con);

Without putting quotes around $_GET['q'], mysqld thinks the value of $_GET['q'] is a field.


Why some rows are missing in my recursive CTE? These are my 2 tables id_hierarchy and hierarchy_link:

CREATE TABLE id_hierarchy
(
    id integer NOT NULL,
    hierarchy integer
);
insert into id_hierarchy values (11,2),(22,3),(44,5),(77,8),(170,11),
(190,13),(240,18),(255,20);

CREATE TABLE hierarchy_link
(
    hid integer NOT NULL,
    parent_hid integer
);
insert into hierarchy_link values (1,0),(2,1),(3,2),(5,3),(8,5),(11,8),
(13,11),(15,13),(18,15),(20,18);

With id_hierarchy.hierarchy=hierarchy_link.hid

For the below CTE query:

with recursive rt1 as(
    select id,hid,parent_hid||'->'||hid as str
    from rt2
    where rt2.parent_hid=1
    union
    select t.id,t.hid,s.str||'->'||t.hid as str
    from rt2 t
    inner join rt1 s on (s.hid=t.parent_hid)
),
rt2 as (
    select id, hid, parent_hid, hierarchy
    from hierarchy_link h
    inner join id_hierarchy u on (u.hierarchy=h.hid)
)
select id, hid, str as hierarchy
from rt1
order by 1

I am getting this result:

id;  hid; hierarchy
11;  2;   "1->2"
22;  3;   "1->2->3"
44;  5;   "1->2->3->5"
77;  8;   "1->2->3->5->8"
170; 11;  "1->2->3->5->8->11"
190; 13;  "1->2->3->5->8->11->13"

Rows 240,18 and 255,20 are missing from id_hierarchy.

Where is my mistake?

If we slice your query, the first cte (rt2) returns this:

id   hid     parent_hid  hierarchy
11    2         1            2
22    3         2            3
44    5         3            5
77    8         5            8
170  11         8           11
190  13         11          13
240  18         15          18
255  20         18          20

The first SELECT ([root]) in the second cte (rt1) returns this:

id   hid     parent_hid  hierarchy
11    2         1            2

This is indeed the only row with parent_hid = 1. The recursive CTE starts with this single value and goes all the way down the hierarchy until there is no match anymore.

From this, the second SELECT recursively joins rt2 with rt1 on rt1.hid = rt2.parent_id:

id   hid     parent_hid  hierarchy
11    2         1            2 -+                1->2                   => start
22    3         2            3 -+ -+             1->2->3                => previous + 3
44    5         3            5    -+ -+          1->2->3->5             => previous + 5
77    8         5            8       -+ -+       1->2->3->5->8          => previous + 8
170  11         8           11          -+ -+    1->2->3->5->8->11      => previous + 11
190  13         11          13             -+    1->2->3->5->8->11->13  => breaks here
240  18         15          18
255  20         18          20

It breaks on 190 because there is no match on parent_hid = 13 where hid = 13 (row for id = 190).

It breaks because your are only looking for hierarchy starting with a root at 1.

I am not sure what your expected output should be but if you are looking for hierarchies with no parent (i.e. parent_hid not in hid [the root]), you will also get the hierarchy 15->18->20.

Replace where rt2.parent_hid=1 by WHERE NOT EXISTS  (SELECT 1 FROM rt2 WHERE hid = r.parent_hid) and starting roots will be:

id  hid hierarchy
11  2   1->2
240 18  15->18

And output will be:

id  hid hierarchy
11  2   1->2
22  3   1->2->3
44  5   1->2->3->5
77  8   1->2->3->5->8
170 11  1->2->3->5->8->11
190 13  1->2->3->5->8->11->13
240 18  15->18
255 20  15->18->20

It seems that only hierarchy_link is needed to built up the hierarchy. Therefore rt2 should be remove:

;WITH rt1 AS (
    SELECT hid, parent_hid, CAST(parent_hid as varchar(50)) + '->' + CAST(hid as varchar(50)) as [str]
    FROM hierarchy_link r
    WHERE r.parent_hid = 1
    UNION ALL
    SELECT l.hid, l.parent_hid, CAST(r.[str] as varchar(50)) + '->' + CAST(l.hid as varchar(50)) as [str]
    FROM hierarchy_link l
    INNER JOIN rt1 r ON r.hid = l.parent_hid
)
SELECT h.id, r.hid, r.[str] as hierarchy
FROM rt1 r
INNER JOIN id_hierarchy h ON h.hierarchy = r.hid;

The id from id_hierarchy is only added at the end once the hierarchy from rt1 is ready:

id  hid hierarchy
11  2   1->2
22  3   1->2->3
44  5   1->2->3->5
77  8   1->2->3->5->8
170 11  1->2->3->5->8->11
190 13  1->2->3->5->8->11->13
240 18  1->2->3->5->8->11->13->15->18
255 20  1->2->3->5->8->11->13->15->18->20


Veryfing pg_restore database with pg_dump database I just did a migration from Postgres 9.4 to 10 database.
How to verify if everything is okay?

I tried to look into huge tables, the rows are different (somehow in Postgres 10 then are more rows than in 9.4)

PSQL 9.4: 14 583 936
PSQL 10:  14 880 545

here are my pg_dump and pg_restore commands which were executed without errors, but took very long process, dump was 12hours, while pg_restore 10hours:

pg_dump    -U postgres -d hive -Z1 -Fc -f /mnt/offline_backups/hive_full_2019-03-01_1630.dmp 2> /mnt/offline_backups/hive_full_2019-03-01_1630.log
pg_restore -U postgres -d hive -v         /mnt/offline_backups/hive_full_2019-03-01_1630.dmp

both commands were without errors whatsoever.

Here some screenshots from pgadmin 4v:

In both DB I just sorted rows count in descending order.

As you can see, rows inserted is quite different.

Here is summary:

Thanks to A_horse_with_no_name for a suggestion, i did some of the tables select count(*) and returned identical rows for each table.


Excluding the standard MySQL databases from replication I have a prod database, say appdb, and I only care about the data in that db.  Is it safe to declare replicate-ignore-db for each of mysql,information_schema,performance_schema,test, as some tutorials show?  I define my own analytics users on the slave and manage permissions there.  I don't want extra users or tuning artefacts from master, in fact I may tune the slave separately...  However, I cannot recite the perils of replicate-do-db and replicate-ignore-db in my sleep, as some answers here advise, so am wondering what else should I care about in this case!

There's no need to avoid replicating performance_schema or information_schema -- they are not real databases with real tables, they are generated by server internals to interact via SQL, but you'll notice from their table definitions that they aren't normal.  They can't be altered, modified, dropped, created, updated, inserted into, or deleted from... and they don't cause replication events. The performance_schema does not have to be configured the same on master and slave.

The mysql database, you need to allow to replicate, because the users on the master need to be valid users on the slave -- otherwise, you will encounter potential problems with definitions and execution of views, triggers, stored procedures, stored functions, and events, all of which have "definer" attributes that need the defining users to exist on the slave in many cases.

However, you are free to create additional users on the slave as long as you don't, later, generate events on the master that would conflict with those users.

A slave needs to be run in global read_only mode, which prevents users with write permissions granted to them from actually modifying data on the slave, unless they have the SUPER privilege, which bypasses this restriction (which is also why I, as a DBA, have two accounts... I connect to slaves with my account that lacks SUPER unless I have a specific reason to do otherwise).

There is no "tuning" that I can think of that's stored in the system tables in the mysql schema.

All of these things in mind, I'd suggest that the best/easiest/safest approach is to let replication do it's thing and cross any problematic bridges when you come to them... which is typically far less likely when you replicate everything than if you try to be selective.


SSMS registered Servers I am currently trying to migrate my ssms registered servers from one machine to another.  This is complicated by the fact that I am running ssms 17 on the source, and ssms 16 on the destination.  I would run 17 on the second computer but it is only a 32 bit machine, so i am sol for that.

I have well over 100 machines saved so I am hoping there is some way to move the configuration rather then have to start building it from scratch.

You should use dbatools - Copy-DbaCentralManagementServer

This command copies all groups, subgroups, and registered servers from one SQL Server to another. By default, all objects are copied.
To migrate all groups, subgroups, and server instances are copied from sqlserver’s Central Management Server to sqlcluster’s Central Management Server.

Copy-DbaCentralManagementServer -Source sqlserver -Destination sqlcluster


Calculating the elapsed time of Oracle Database Startup operation I'm trying to calculate the elapsed time of Oracle Database startup command. The view V$INSTANCE contains the STARTUP_TIME column which tells at what time the database is started. But, i need way to find the time elapsed from the time we enter startup till the database is opened.

Since, we can't query SYSDATE or SYSTIMESTAMP when the database is not open, im finding it difficult to come up with a logic.

Is there any other way we can get the elapsed time for startup operation?

[oracle@o71 ~]$ time sqlplus -s / as sysdba<<EOF
> startup
> exit
> EOF
ORACLE instance started.

Total System Global Area  536867640 bytes
Fixed Size                  8659768 bytes
Variable Size             322961408 bytes
Database Buffers          197132288 bytes
Redo Buffers                8114176 bytes
Database mounted.
Database opened.

real    0m14.578s
user    0m0.014s
sys     0m0.016s
[oracle@o71 ~]$


Why do some write sometimes go faster than expected for MEDIUMTEXT We are running a MySQL Innodb database, our server has 4gb memory.  We are trying to improve the speed at which a 800KB XML File is stored in a MEDIUMTEXT column.  Currently, we have this query

CREATE DEFINER=`username`@`localhost` PROCEDURE `API_LogApiRequest`(sRequest
  MEDIUMTEXT, sResponse MEDIUMTEXT)

BEGIN
   DECLARE sUUID VARCHAR(36);
   SELECT UUID() INTO sUUID;

   INSERT INTO ApiRequest
   (
       Id,
       Request,
       Response
   )
   VALUES
   (
       sUUID,
       sRequest,
       sResponse
   );

   SELECT sUUID AS 'UUID';
END;

The sResponse data can be as much as 800kb, and we are seeing it takes the following on 10 repeat calls.  These calls were all made with the same data.

  0.687 sec

  0.078 sec

  0.188 sec

  0.718 sec

  0.047 sec

  0.031 sec

  0.610 sec

  0.313 sec

  0.141 sec

  0.032 sec

Here is the Innodb status

INSERT BUFFER AND ADAPTIVE HASH INDEX
Ibuf: size 1, free list len 0, seg size 2, 560 merges
merged operations:
insert 560, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 49919, node heap has 1 buffer(s)
0.00 hash searches/s, 23.83 non-hash searches/s

----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 25772032; in additional pool allocated 0
Dictionary memory allocated 130139
Buffer pool size   1536
Free buffers       1024
Database pages     511
Old database pages 0
Modified db pages  11
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1902256, not young 73968112
3.33 youngs/s, 52.77 non-youngs/s
Pages read 23821066, created 4126940, written 19043946
25.28 reads/s, 6.89 creates/s, 41.11 writes/s
Buffer pool hit rate 903 / 1000, young-making rate 12 / 1000 not 204 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead
0.00/s
LRU len: 511, unzip_LRU len: 0
I/O sum[3805]:cur[242], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Main thread id 7228, state: sleeping
Number of rows inserted 1340396, updated 304, deleted 1287534, read 12921295
2.78 inserts/s, 0.00 updates/s, 1.33 deletes/s, 4.11 reads/s

Here is my table structure

CREATE TABLE `ApiRequest` (
 `ApiRequestId` varchar(36) COLLATE utf8_unicode_ci NOT NULL,
 `Request` mediumtext COLLATE utf8_unicode_ci NOT NULL,
 `Response` mediumtext COLLATE utf8_unicode_ci NOT NULL,
 `EventTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ApiRequestId`),
  KEY `IDX_EventTime` (`EventTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

What do I need to change to improve the performance?

I suspect the answer goes something like this...

Approach 1:  800KB takes a lot of disk activity to write.  This takes perhaps 1000ms.  When you "wrote" a row in 31ms, that was because of most of the I/O being delayed until after acknowledging the action.  When it took 718ms, the disk was busy writing other things, so it was slow at accepting the current row.

Approach 2:  A rule of thumb for I/O with a HDD is 100/second.  800KB = 50 * 16KB blocks to write (plus some overhead).  That would compute out to 500ms per row.  So 718ms sounds a little high, and 31ms sounds like a lot of caching going on.

So the question should be "Why does this write sometimes go faster than expected?


How to include Innodb_trx_id in MySQL slow log Evaluating MySQL's slow query log with pt-query-digest from the Percona Toolkit, I need to group queries by transaction. As it seems, the easiest way to do this is to group by the Innodb_trx_id attribute.

However, my logs don't include this attribute. I tried with vanilla MySQL, MariaDB and Percona Server -- so I figure, you probably need to enable logging of this attribute. Any pointers on how to do this?

Thanks,
Victor

The extended fields that Percona added are not available in stock MySQL.

The only other option is to add a comment to your SQL query in your app. The comments are added to the log, and pt-query-digest can parse fields out of comments with the --embedded-attributes option.

It's a bit tricky to discover the transaction id. You can query the transaction for your current thread:

SELECT TRX_ID FROM INFORMATION_SCHEMA.INNODB_TRX
WHERE TRX_MYSQL_THREAD_ID = CONNECTION_ID();

But you find no row in that table until your transaction has created any locks. That is, a transaction that does nothing but SELECT will not show up in that table.

You could simply embed CONNECTION_ID() into your query comments, on the assumption that grouping by connection is as good as grouping by transaction.


Linked servers login failure: Impersonate works, Username/password fails I'm trying to set up a linked server between 2 instances of Sql Server 2008 R2.

The login works perfectly when I select the impersonate option. However when I try to use the remote user/password option it fails, even though the remote user is the same as the local user being impersonated.

The user account I'm trying to connect with is a windows admin on both machines.

Unfortunately, I need to be able to connect to machines that the local user doesn't have admin rights to (though I will have the username/password of an admin when this occurs), so impersonation won't work.

Where am I going wrong?

user/password option it fails, even though the remote user is the same
  as the local user being impersonated

You can't possibly impersonate a SQL login, and a linked server user/password option only allows for SQL logins. Sounds like you are attempting to pass in a Windows user/password credential, something that is not supported.


ETL process causes database to rapidly grow - Shrinkfile reduces size from 140GB to 2GB - Keep shrinking? I have a database (SQL Server 2012) that is used mainly for ETL of data. Every hour SSIS packages run that truncates/deletes and reloads full datasets. The database keeps growing even though the data is wiped out and replaced.
I ran a SHRINKFILE and the database went from 140 GB down to 2 GB. As the days pass, the database increases in size 2+GB a day.
I am trying to figure out how to prevent the database from growing so rapidly.
Questions

Should I run truncate command or delete command? I try to use truncate but I was having issues with getting a lock on the tables so I switched to a delete
Is it better if I completely delete the table and recreate the object each time instead? Would this help with the database growth?
I have read where "shrinking" is not a good thing, but sometimes it is necessary. Would setting up a shrinkfile command to run weekly during off hours solve my problem?

Should I run truncate command or delete command? I try to use
  truncate but I was having issues with getting a lock on the tables so
  I switched to a delete • Is it better if I completely delete the table
  and recreate the object each time instead? Would this help with the
  database growth? • I have read where "shrinking" is not a good thing,
  but sometimes it is necessary. Would setting up a shrinkfile command
  to run weekly during off hours solve my problem?

First things first, are you using the full recovery model and not scheduling log backups? When are your log backups scheduled?

If yes, either change your recovery mode to simple (ALTER DATABASE ... SET RECOVERY SIMPLE or start taking log backups (more frequently).
Consider the RPO of your database in this decision.

If not, then the delete is probably increasing your database's log file size as it is a logged operation.

The truncate table is a metadata only operation and should be instantaneous even for huge tables (as long as it is not being blocked).

MS Docs on TRUNCATE TABLE

  Removes all rows from a table or specified partitions of a table,
  without logging the individual row deletions. TRUNCATE TABLE is
  similar to the DELETE statement with no WHERE clause; however,
  TRUNCATE TABLE is faster and uses fewer system and transaction log
  resources.

&

  When the DELETE statement is executed using a row lock, each row in
  the table is locked for deletion. TRUNCATE TABLE always locks the
  table (including a schema (SCH-M) lock) and page but not each row.

When running truncate table, the table will be locked. This includes a SCH-M lock. Nonetheless you should be using truncate if you are deleting all the data in the table.

Why and what is locking the table when running the truncate?

EDIT

  Recovery Model= simple. The truncate gets blocked mainly from users
  that have Power BI data refreshes setup or users that are running sql
  queries against it (via Power BI, excel, etc) Also, I have a small
  window of time to try and get the refresh completed so any locks were
  causing too much of a delay;

If you want to keep the delete, you should look into deleting in batches.
This way, the log will not grow as much when using the simple recovery model.

If you don't want to do that, you will have to live with the increased size of the database log file. If the log grows out every week it serves no purpose to shrink it.

  The Log file increases some but not like the .mdf file which grows to
  140GB+ and shrinks down to 2GB. Is the log file the same as the .ldf
  file?

Correct, the log file corresponds to the .ldf file. If the .mdf file is the one growing, the only answer is looking at the data & datatypes.

If the data is deleted and inserted every load, the size should stay the same. If this is not the case, adding the datatypes, table definition and amount of rows to the question could be helpful.


SQL Server De-duplication Trigger I have a table that contains columns of Country, City, Location, Description. Due to the size of the description field it's not possible to bind the four fields into a composite key to prevent duplication. I have been trying to prevent duplication by way of a trigger but no matter what I try it rolls back every insert, including inserts of unique data.

IF Exists ( Select      a.*
            From        [dbo].[Table] a
                JOIN    Inserted b
                    on  a.Country = b.Country
                    and a.City = b.City
                    and a.Location = b.Location
                    and ltrim(rtrim(a.Description)) = ltrim(rtrim(b.Description))
            )

    BEGIN
        Raiserror   ('The description that you are trying to load already exists.', 16, 1)
        Rollback
    END

Does anybody have any ideas what is wrong with the above, and what changes I need to make in order to get it to only roll back duplicate inserts?

I think the condition you are checking will always be true as for every insert query executed there will be an entry in the dbo.Table and inserted as well so it will rollback the transaction.

From MSDN

  The inserted table stores copies of the affected rows during INSERT
  and UPDATE statements. During an insert or update transaction, new
  rows are added to both the inserted table and the trigger table. The
  rows in the inserted table are copies of the new rows in the trigger
  table.

I would advice you to check the duplicate before insert as

IF NOT EXISTS (Select a.* FROM [dbo].[Table] a WHERE..............)
  INSERT INTO [dbo].[Table] .......

More Readings : http://msdn.microsoft.com/en-in/library/ms191300.aspx


Join query with different column types in "on" part In need to make a JOIN on 2 tables based on columns which have different column types.

On table A, I have a DATETIME field and, on table B, I have a DATE and a TIME field, which combined would match the DATETIME field on table A.

What would be the recommended syntax for best performance on such join clause?

BD: MySQL 5.5.43-0+deb8u1-log

PD: Any extra info needed?

You can use ADDTIME() function:

tableA AS a JOIN tableB AS b
  ON a.datetime_column = ADDTIME(CAST(b.date_column AS DATETIME), b.time_column)

This might use an index on tableA (datetime_column) but not an index on tableB. The reverse might use an index on tableB (date_column, time_column) but not on A:

tableA AS a JOIN tableB AS b
  ON  CAST(a.datetime_column AS DATE) = b.date_column
  AND CAST(a.datetime_column AS TIME) = b.time_column

It won't hurt testing both versions. If one table is much larger than the other, then prefer to have the larger table's columns exposed (not cast) so their index might be used.

If you move to MariaDB (any version 5.3+) or MySQL 5.7 (when it's released), you can define a VIRTUAL column (or two) in one of the two tables to hold this conversion/calculation that can be persisted and indexed.

In 5.5, if efficiency is not good, which is expected with large tables, you could add a computed column yourself but it would have to be populated during inserts and kept in sync during updates by you (e.g. using triggers).


How to configure connection accessing Sybase Anywhere from Sybase IQ via ODBC on Linux I'm trying to access an ASA16 server from IQ16 via ODBC driver. Both servers are installed on the same Linux box.

I've installed unixodbc-bin and added a file .odbc.init into home of user running the databases:

[mes_input]
Driver=/opt/sqlanywhere16/lib64/libdbodbc16_r.so
uid=dba
pwd=SQL
EngineName=mes_input
CommLinks=tcpip(host=127.0.0.1;port=49152)
AutoStop=no
DatabaseName=mes_input

The ASA is running on 49152 on that box; IQ is running default 2638.

Also I've added an entry into interfaces file locking like that:

mes_input
        master tcp ether myserver 49152
        query tcp ether myserver 49152

Back on IQ I can add via Sybase Central the remote server as well as tables from this box. Also testing the connection and is showing no error.

But when I'm now trying to copy some data from ASA via e.g.

INSERT locale_table LOCATION 'mes_input.mes_input' 'SELECT * FROM table'

I'm getting:

Connectivity libraries cannot be found (check your dynamic library
search path).  Selects from attached databases are not possible without
these libraries.
-- (hqmlib/hqm_cpm.cxx 146)
SQLCODE=-1003005, ODBC 3-Status="HY000"
Zeile 1, Spalte 1

Something I've missed or missconfigured?

Checked official documents for this error and could see it as below:

Source the SAP IQ setup scripts using the same user that starts the
SAP IQ server.
This will set the LD_LIBRARY_PATH and other important environment
variables.

The error could be occurring due to not setting the appropriate environment variables on the host on which SAP IQ is running.
You need to set the environment variables on Linux Box.
You my do below for setting these variables:
Change to the SAP IQ parent installation directory and execute one of the following:
For the tcsh or C (csh) shell, enter:
source ./IQ.csh

For the Korn (ksh), Bash, or Bourne (sh) shells, enter:
source ./IQ.sh

Hope this helps.


Convert a Unix timestamp to a DATETIME in a View I have a table that stores a unix timestamp. To query this as a date, I'm attempting to convert this timestamp to a datetime type in a view. Unfortunately, I can only seem to get the DATE portion out.

This link describes how to do the conversion, but requires changing the nls_date_format to include the time portion. The default currently only shows the date portion. I need the date and time.

Unfortunately, that solution only works at a session level. As a developer, I'd rather not have to go running off to our managed service provider to ask them to change the value at a database level - especially since it may impact other applications.

Is there a way I can convert the timestamp, in sql, to a datetime without modifying the system?

the result of the expression

to_date('1970-01-01','YYYY-MM-DD') + numtodsinterval(1244108886,'SECOND')

is a value of the oracle datatype DATE. This type contains date and time. If you want to display this value to a user you must convert it to a string. This conversion can be done by an application or you can let oracle convert it to a string type as it is done in this example. This automatic conversion to the oracle stringtype VARCHAR2 is controlled by nls_date_format and some server settings. If you want your view to return a VARCHAR2 value with the calculated date in the format 'YYYY-MM-DD HH24:MI:SS' and not a DATE value you can do this by using a conversion function: expression

to_char(to_date('1970-01-01','YYYY-MM-DD') + numtodsinterval(1244108886,'SECOND'),'YYYY-MM-DD HH24:MI:SS')

But in this case the return value of the view is a VARCHAR2 and not a DATE value.


MSSQL Mirror Configuration Troubleshooting: Witness server forcibly disconnecting every 100 seconds I recently set up a three servers running MSSQL Server 2016 in a high safety synchronous mirroring configuration.  Right now, everything is working normally.  Any changes made in the principal are replicated on the mirror consistently.

However, both the Principal and the Mirror server constantly have the error:

"Database mirroring connection error 4 'An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)'.' for 'TCP://WITNESS_SERVER:5022'."

The Witness server also has the exact same error toward both the Principal and the Mirror.  There are no errors between the Principal and Mirror, and there are no other accompanying errors (Handshake rejected, etc...).  Interestingly, these disconnects occur repeatedly exactly 100 seconds apart without cease.  They do not happen at the same time for the Mirror and Principal, and currently are offset by about 12 seconds.  Has anyone run into a similar issue or know exactly what sort of issue can lead to 100 second forced disconnects?

We reported the issue to Microsoft support and got it fixed. I have also tested SP1 CU1 and the database mirroring error messages have disappeared. So I think that all is good.
https://support.microsoft.com/en-us/help/3210699/fix-error-messages-are-logged-if-database-mirroring-is-configured-by-transact-sql-in-sql-server-2016-and-no-database-activity-occurs-for-more-than-90-seconds


The certificate chain was issued by an authority that is not trusted some time ago I installed SQL server 2016 developer edition on a windows 10 home edition environment (a laptop to be precise) and everything was fine.

then someone - an administrator on the box - decided without telling me, to rename said box.

after that when attempting connection to SQL server we have been experiencing the following error messages:

this is a very similar question to this one:

“The certificate chain was issued by an authority that is not trusted” when connecting DB in VM Role from Azure website

I have been also here:

The target principal name is incorrect. Cannot generate SSPI context. (Microsoft SQL Server, Error: 0)

and I have been using the Kerberos Configuration Manager, which gives me a different error message

I should say that I Am able to connect to this instance of SQL server by SQL server authentication but I want to connect using windows authentication.

just to be clear - this is all a local machine, that does not belong to any domain.

I am not quite sure as how to proceed from here, what I want is to use windows authentication.

The error message related to certificates makes me think about re-issuing certificates here.
I am not sure as for how to get this done, or if this is a valid way to go about this situation.

I will not be around this machine for the day, but I will catch up as soon as possible. I will carry on with my research when time permits.

On connection pop up click on options

Then make sure the "Trust server certificate" is checked


What is included in ALL permissions for functions in PostgreSQL? I am trying to find references to what the difference between EXECUTE and ALL is in:

GRANT { EXECUTE | ALL [ PRIVILEGES ] } ON { FUNCTION | ALL FUNCTIONS IN SCHEMA }...

but all I can find is what the docs say:

  EXECUTE
  Allows the use of the specified function and the use of any operators that are implemented on top of the function. This is the only type of privilege that is applicable to functions. (This syntax works for aggregate functions, as well.)

For example, I know that for schemas ALL = CREATE + USAGE but I wanted to double check that in reference to functions:

  This is the only type of privilege that is applicable to functions.

in the above snippet from the docs implies that for functions ALL = EXECUTE and that:

GRANT ALL ON ALL FUNCTIONS...

is equivalent to:

GRANT EXECUTE ON ALL FUNCTIONS...

The only permission applicable to functions is EXECUTE, so when you use ALL postgresql will try to apply all the permissions available for functions, and for now this is just "EXECUTE".

So it means exactly the same.

  EXECUTE

  Allows the use of the specified function and the use of any operators
  that are implemented on top of the function. This is the only type of
  privilege that is applicable to functions.

https://www.postgresql.org/docs/10/static/sql-grant.html

But be carefully if more kind of permissions for functions be implemented in new postgresql versions this can do wrong (but i can't see other than execute).


mongo shard balancer not working In our production system, we have shard cluster with -

2 shards (each one is replica set of primary + secondary + arbiter)

3 Config servers

4 mongoS instances

A couple of days back, we faced a network outrage in which a secondary failed to connect to its corresponding primary. This issue was resolved in some time but mongo balancer is stuck from that point of time.

All of the data is being moved to a single shard and nothing is transferred. In mongoS logs, we monitored that lock was acquired by a mongoS instance but not released till date -

    configsvr> db.locks.find({state:2}).pretty()
    {
    "_id" : "balancer",
    "process" : "mongosHost3:27017:45654643:-54656",
    "state" : 2,
    "ts" : ObjectId("57c29902a443254e2d8e1b27"),
    "when" : ISODate("2016-08-28T07:55:46.350Z"),
    "who" : "mongosHost3:27017:45654643:-54656:Balancer:37654778",
    "why" : "doing balance round"
    }

We tried restarting this MongoS instance but nothing changes. We tried stopping the balancer but that failed with error -

     Error: Error: assert.soon failed, msg:Waited too long for lock balancer to unlock

How can we resolve this balancer issue?

Is there any way we can manually relinquish this lock and re-enable balancer?

First, restart the mongos process on mongosHost3, then recheck the locks as you did in the question.

If the same lock (make sure it is the same and not a new lock by the same process - check the ts field etc.) is still present after that restart it is safe to remove it manually, it is basically stale and should be cleaned up eventually in any case. You can remove it just like any other document (assuming you have permissions to edit the config DB of course). MongoDB 3.2 introduced the deleteOne command which I would recommend using here.

It is possible that this is not an issue with a stale lock and is instead an issue with balancing (i.e. it is seeing errors or is stuck/slow for some reason), take a look in the mongos logs of whatever process holds the lock once balancing kicks off again and see what it reports as progress for balancing.

You can also look in the the changelog for more information related to balancing and migrations. If balancing is seeing issues I would recommend getting the logs, changelog information and posting a separate question to troubleshoot that issue.


Why is OS authentication considered poor security for Oracle databases? Oracle is deprecating OS authentication according to the Oracle Database Security Guide, which says

  Be aware that the REMOTE_OS_AUTHENT
  parameter was deprecated in Oracle
  Database 11g Release 1 (11.1), and is
  retained only for backward
  compatibility.

In addition, most security information and tools consider
OS (external) authentication to be a security problem.  I am trying to understand why this is the case.  Here are some advantages I see of OS authentication:

Without OS Authentication applications must store passwords in a variety of applications each with their own security model and vulnerabilities.
Domain authentication already has to be secure because if it is not then database security just slows down access to the database, but cannot prevent it.
Users that only have to remember one domain password can be made to create more secure domain passwords more easily than they can be made to create even less secure database passwords as the number of different databases they must connect to increases.

Consider the following scenario:

There is a Unix user named gaius on the Oracle server with external authentication, so in Oracle there is a corresponding user called ops$gaius. When logged into a shell, I can also log straight into my Oracle schema, and my cron jobs don't need a password embedded in script either.
Remote OS authentication is permitted, on the assumption that the LAN is 100% secure and the clients can be trusted (same as rlogin/rsh used to be normally allowed)
An attacker gets his or her laptop onto the LAN by whatever means, knows that I work there, and creates a local user on their laptop called gaius and runs SQL*Plus as that user
Oracle sees (i.e. OSUSER in V$SESSION) is gaius and logs that remote user in as ops$gaius

That's not only laughably easy to spoof, but putting on my cynic's hat, Oracle can't make any more money selling you their fancy single sign-on product... Which by the way does fulfill all the points you raise as advantages of OS-level auth. Two passwords better than one is entirely spurious; most people will set them to be the same anyway (there's no mechanism in Oracle to prevent this).

The general principle is that it is extremely difficult to defend in software when an attacker has physical access. And never trust the client.


How to rotate trace logs in SQL Server We are using 4032 & 3605 trace flags in SQL Server 2012 to get the expected log output.

Now, we would like to rotate the log files on daily basis to archive it in Amazon Glacier storage facility.

Any pointers on log rotation would be appreciated,

To rotate the logs you just need to execute

EXEC master.sys.sp_cycle_errorlog;

You can schedule the execution daily or as often as it makes sense in your scenario with a SQL Server agent job.


Best idea to deal with historic data ( Partitioning? ) We have a database with almost 1 billion rows.

90% of this table is historical data. We don't use it, BUT if we need to use it, it needs to be right here, right now (we work with banks, so, they need things pretty fast).

I'm reading about partitioning in Brent Ozar blogs etc. but I don't know if this is a good idea.

I would like to partition this table but I'm concerned about backups. If doing backups of the database, without backing up this partition, am I going to have a corrupt database in case of a restore, right?

Am I able to restore only the database, without the partitioned file?

I'm thinking about partitioning the table, throw this file created in a different disk, and just do a backup of the database without this file.

I really don't think this is a good idea, so I would like to hear about some experiences with this.

We have several huge log tables partitioned by dates, the typical scenario where we just use current month to store data and the rest is queried for support & troubleshooting. We run backups for the whole database, not just single partitions/filegroups as that will imply piecemeal restores later on. And you don't want to be in the middle of the night dealing with piecemeal restores, unless you are really aware of all the steps involved...I'm not. So if in doubt, use partition at will, analyze your scenario to find the best partition function and run a normal backup of the whole database.


Add business days in PostgreSQL I am trying to find a way to add business days to any date.
An example would be:

date = '2017-04-28'  (It was a Friday)

date + 2 = '2017-05-02' (It skipped Saturday and Sunday)

Is there a way to do this without custom queries?

You can use generate_series() to generate a series of dates, and extract() to get day of week.

Then simply filter those dates where day of week are not 0=Sunday, 6=Saturday.

with days as
(
    select dd, extract(DOW from dd) dw
    from generate_series('2017-04-28'::date, '2017-05-02'::date, '1 day'::interval) dd
)
select *
from   days
where  dw not in (6,0);

dd                     | dw
:--------------------- | :-
2017-04-28 00:00:00+01 | 5
2017-05-01 00:00:00+01 | 1
2017-05-02 00:00:00+01 | 2

dbfiddle here

If you have to exclude public holidays and other non-business days, you can build a business_day table.  Just insert the output from above and then remove all days that have to be excluded (in certain countries, like Hungary, there might be additional replacement days (typically Saturdays) which have to be added, too).  Of course, this has to be maintained (for example, you can prepare the next year every December), but as there is no built-in functionality that knows about those days, you have no better option.

Using a calendar table

Let me create a sample calendar table and insert some values:

create table calendar
(
    id serial primary key,
    cal_day date not null,
    bussines_day bool not null
);

insert into calendar (cal_day, bussines_day) values
('20180101', false), ('20180102', true),
('20180103', false), ('20180104', true),
('20180105', false), ('20180106', true),
('20180107', false), ('20180108', true),
('20180109', false), ('20180110', true),
('20180111', false), ('20180112', true);

Now you can use a function to obtain the next Nth business day in this way:

create or replace function add_business_day(from_date date, num_days int)
returns date
as $fbd$

    select max(cal_day) as the_day
    from (select   cal_day
          from     calendar
          where    cal_day > $1
          and      business_day = true
          order by cal_day
          limit    $2) bd;

$fbd$ language sql;

or

create or replace function add_business_day2(from_date date, num_days int)
returns date
as $fbd$

    select cal_day
    from   (select cal_day,
                   row_number() over (order by cal_day) rn
            from   calendar
            where  cal_day > $1
            and    business_day = true
            limit  $2) bd
    where  rn = $2;

$fbd$ language sql;

Both return same result:

select add_business_day('20180103', 4);

| add_business_day |
| :--------------- |
| 2018-01-10       |

select add_business_day2('20180103', 4)

| add_business_day2 |
| :---------------- |
| 2018-01-10        |

db<>fiddle here


How to GRANT/REVOKE user permissions for user on Firebird 1.5? I need to create a user in firebird 1.5 and give him permissions to access only some specific tables.
I have managed to create the user, but apparently GRANT/REVOKE operations only work on version 2.1 or above.
SO, how to do it?

UPDATE
Actually i was mistaken, GRANT and REVOKE both work fine in version 1.5.

Firebird has had GRANT and REVOKE since its initial version. See the Interbase 6.0 Language Reference, available from http://www.firebirdsql.org/en/reference-manuals/ (near the end of the page).

Eg for GRANT:

GRANT <privileges> ON [TABLE] {tablename | viewname}
      TO {<object> | <userlist> | GROUP UNIX_group}
  | EXECUTE ON PROCEDURE procname TO {<object> | <userlist>}
  | <role_granted> TO {PUBLIC | <role_grantee_list>};
<privileges> = {ALL [PRIVILEGES] | <privilege_list>}
<privilege_list> = SELECT
  | DELETE
  | INSERT
  | UPDATE [(col [, col ...])]
  | REFERENCES [(col [, col ...])] [, <privilege_list> ...]
<object> = PROCEDURE procname
  | TRIGGER trigname
  | VIEW viewname
  | PUBLIC
  [, <object> ...]
<userlist> = [USER] username
  | rolename
  | Unix_user}
  [, <userlist> ...]
  [WITH GRANT OPTION]
<role_granted> = rolename [, rolename ...]
<role_grantee_list> = [USER] username [, [USER] username ...] [WITH ADMIN OPTION]

And for REVOKE:

REVOKE [GRANT OPTION FOR] <privileges> ON [TABLE]
          {tablename | viewname}
       FROM {<object> | <userlist> | <rolelist> | GROUP UNIX_group}
   | EXECUTE ON PROCEDURE procname
       FROM {<object> | <userlist>}
   | <role_granted> FROM {PUBLIC | <role_grantee_list>}};
<privileges> = {ALL [PRIVILEGES] | <privilege_list>} <privilege_list> = {
SELECT
   | DELETE
   | INSERT
   | UPDATE [(col [, col ...])]
   | REFERENCES [(col [, col ...])] [, <privilege_list> ...]}}
<object> ={
   PROCEDURE procname
   | TRIGGER trigname
   | VIEW viewname
   | PUBLIC
   [, <object>]}
<userlist> = [USER] username [, [USER] username ...]
<rolelist> = rolename [, rolename]
<role_granted> = rolename [, rolename ...]
<role_grantee_list> = [USER] username [, [USER] username ...]

The Firebird documentation used to be fragmented in the original Interbase 6 documentation and a language reference update which only described the changes introduced in newer versions.

However there now is a complete Firebird 2.5 language reference.


Concatenating two mysql queries to achieve desire results Here I am trying to show one item per subcategory from a single mysql query.
This is my table "PRODUCTS" shown below :

Using a query I am able to show data which I want to get display in a more formatted way. I mean joining table and show data based on id. Image is shown below:

In this pic you can see multiple items are shown for a single subcategory which is table b. Three items are displayed for a single subcategory "Bag Packs", like wise 12 items are displayed for another single subcategory "Gents Wallets".

I just want to avoid these multiple items being displayed, instead want to show one item per subcategory.
Using a different query I able to get display the table which I want (shown below) :

Now the problem which I am facing is how to get his items shown by concatenting these two queries :

Query 1 :

SELECT p.pid, p.ctid, p.scid, s.name, p.type, p.pdesc
FROM products as p
    inner join b as s
where p.scid=s.scid and p.type='Gents' and p.featured='1'
order by name;

Query 2 :

SELECT *
FROM products
WHERE scid IN (SELECT DISTINCT scid
               from products)
GROUP BY scid ;

"Concatenating 2 Queries" : This part I really don't have any idea how to achieve. Please help.

To join the results of 2 queries into one result set, use

UNION

As in

SELECT Col1 FROM tableA
UNION
SELECT Col2 FROM tableB;

NOTE:  Only one semicolon, after the combined statement.


How to get last inserted id and insert it into another table using stored procedure? Here is my SP.

ALTER PROCEDURE [dbo].[My_SP]
    -- Add the parameters for the stored procedure here
    @ClientID bigint,
    @CreatedBy int =null,
    @CreatedDateTime datetime = null,
    @ModifiedBy int = null,
    @ModifiedDateTime datetime = null,
    @Name nvarchar(255)

    SET NOCOUNT ON;

    INSERT INTO dbo.Clients
    (
    CreatedBy,
    CreatedDateTime,
    ModifiedBy,
    ModifiedDateTime,
    Name,
    Active
    )
    VALUES
    (
    @CreatedBy,
    @CreatedDateTime,
    @ModifiedBy,
    @ModifiedDateTime,
    @Name,
    1
    )

    SELECT @Name AS ClientName

    INSERT INTO BusinessRules (
                        ClientId,
                        ProjectId,
                        TemplateName,
                        Revised,
                        Serial,
                        Topic,
                        QualityItem,
                        CriticalToQC,
                        [Description],
                        OwnerNotes,
                        RelevantDocs,
                        WhereToCheck) SELECT
            @ClientID,
            null,
            b.TemplateName,
            b.Revised,
            b.Serial,
            b.Topic,
            b.QualityItem,
            b.CriticalToQC,
            b.[Description],
            b.OwnerNotes,
            b.RelevantDocs,
            b.WhereToCheck
    FROM BusinessRules AS b WHERE  ProjectID = 394

I need to use the inserted @ClientID in the next INSERT INTO statementas ClientId.

How can I achieve this?

Simply. Just add SET @ClientID = SCOPE_IDENTITY() after first INSERT statement. So you don't need @ClientID input parameter.

ALTER PROCEDURE dbo.My_SP
-- Add the parameters for the stored procedure here
--@ClientID         BIGINT,
@CreatedBy        INT           = NULL,
@CreatedDateTime  DATETIME      = NULL,
@ModifiedBy       INT           = NULL,
@ModifiedDateTime DATETIME      = NULL,
@Name             NVARCHAR(255)

AS
     SET NOCOUNT ON;

     DECLARE @ClientID as BIGINT

     INSERT INTO dbo.Clients
     (CreatedBy,
      CreatedDateTime,
      ModifiedBy,
      ModifiedDateTime,
      Name,
      Active
     )
     VALUES
     (@CreatedBy,
      @CreatedDateTime,
      @ModifiedBy,
      @ModifiedDateTime,
      @Name,
      1
     );

     SET @ClientID = SCOPE_IDENTITY();

     SELECT @Name AS ClientName;

     INSERT INTO BusinessRules
     (ClientId,
      ProjectId,
      TemplateName,
      Revised,
      Serial,
      Topic,
      QualityItem,
      CriticalToQC,
      Description,
      OwnerNotes,
      RelevantDocs,
      WhereToCheck
     )
            SELECT @ClientID,
                   NULL,
                   b.TemplateName,
                   b.Revised,
                   b.Serial,
                   b.Topic,
                   b.QualityItem,
                   b.CriticalToQC,
                   b.Description,
                   b.OwnerNotes,
                   b.RelevantDocs,
                   b.WhereToCheck
            FROM BusinessRules AS b
            WHERE ProjectID = 394;


What databases or styles of database can use git (or another dvcs architecture) to manage cross-site replication? I'm looking for a database software package for a dictionary (like the hunk(s) of dead tree that you might have somewhere at home).  I would like the database to

scale well to a million key-value pairs.  The pairs would normally be fewer than 20 characters, and the normally fewer than 1000 characters.
have perfect UTF-8 support.
deal with distributed version control.  I would like it to deal well with having lots of branches and doing merges of branches.  This could be built-in or provided independently by something like git.
have a well-supported interop with Ruby.  I plan to write a desktop program in Ruby that talks to the database.  (Ruby would also work for writing the web part if it ever happens.)
be fairly responsive (though I would like it to be fast).
deal well with changes both while just on a desktop and while providing data for something on the web.  (Hopefully this project will get to the web part.)
be free software (as defined by RMS) and to be available at no charge.

All of this data would be entered by hand.

It wouldn't hurt if there were something like github for this database.  I don't know if there is a database for which someone provides something like that, though.

No one's job, career, or company rides on this decision.

Any suggestions?

For simple key/value storage, you might like to consider Berkeley DB, though I would not think Git would be the best for 'version control'. You can use Ruby bindings with BDB.

Since your edit, I'm left wondering If you want an open-source implementation of something like Amazon S3


How to set cache on windows 10 pro 64bit machine When running Mongod I get the following WARNING: The file system cache of this machine is configured to be greater than 40% of the total memory. This can lead to increased memory pressure and poor performance. This is on a windows 10 pro 64 bit machine.  I have 16 gigs of ram.  I have a 225 gig ssd drive c: and 2 1 terabyte drives d: and e:

I've set the virtual memory as follows:

drive     initial size       max
c:         800               1000
d:         400                800
e:         400                800

it says space available is 134538 MB

The mogod reference says the following:

Check the Windows file system cache limit. Having a too high or unbound
upper limit can impact performance. Specifically, having a file system cache
that uses 40% or more of the total memory can lead to increased memory
pressure and poor performance. Use SetSystemFileCacheSize to limit the
amount of memory that the file system cache can use.

Could someone provide directions on how to do that?  The answer from @Md Haidar Ali Khan does not work

I found a free tool that will set SetSystemFileCacheSize here with 16 gigs of RAM I set it to 1024 4096 and the mongod warning went away


How to use Unique key via combinations of table fields? Take a look at the following sqlfiddle: http://sqlfiddle.com/#!2/dacb5/1

CREATE TABLE contacts
    (
     id int auto_increment primary key,
     name varchar(20),
     network_id int,
     network_contact_id int
    );

INSERT INTO contacts
(name, network_id, network_contact_id)
VALUES
('John', 4, 10),
('Alex', 4, 11),
('Bob', 4, 12),
('Jeff', 4, 45),
('Bill', 7, 11),
('Walter', 7, 45),
('Jessie', 7, 360) ;

I have a basic table of contacts. The network_id and network_contact_id fields contain id numbers that link to other tables.

I want to be able to run INSERT IGNORE queries to this table, but I want to use the combination of the network_id and network_contact_id as the unique key to match against.

So for example, if I tried to insert a contact that had network_id = 4 and network_contact_id = 12, the INSERT IGNORE query would see that entry already exists, and ignore any error that was thrown.

So basically, network_id is not unique. network_contact_id is not unique. But the combination of the two is unique. How do I set this up? Would I have to have a single other field that is the concatenated values of the two other fields? Or is there a way to setup the keys for this table so it will do what I need?

Did you try

CREATE TABLE contacts (
 id int auto_increment primary key,
 name varchar(20),
 network_id int,
 network_contact_id int,
 UNIQUE KEY (`network_id`, `network_contact_id`)
);


Why does "GRANT SELECT ... TO Role;" not allow members of Role to SELECT? Please consider the following scenario on a Oracle 11g Database.
The user ADMIN performs the following:
CREATE USER Alice IDENTIFIED BY pwdalice;

GRANT CREATE SESSION TO Alice;

CREATE ROLE Viewer IDENTIFIED BY pwdviewer;

GRANT Viewer TO Alice;

GRANT SELECT ON Table_1 TO Viewer;

Then Alice logs into the database as 'Alice' and executes the following command:
SELECT * FROM Table_1;

Alice gets the following error:
SELECT * FROM Table_1
              *
ERROR at line 1:
ORA-00942: table or view does not exist

I thought that granting privileges to a role would enable its member-users to get those privileges. However, this scenario shows that it is not so. What am I missing here? How can I grant SELECT to Alice using a role?
Update:
Following the answers, tried 3 fixes with no success
1) Using Fully-qualified Table Names
I missed to include the schema name in  SELECT * FROM Table_1; command. However, even after adding the schema name as shown below, still the error comes.
Alice executes:
SELECT * FROM ADMIN.Table_1;

Gets the error:
SELECT * FROM ADMIN.Table_1
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

2) Using a synonym for the fully-qualified table name
Unfortunately, this does not seem to solve the problem either.
Alice executes the following:
CREATE SYNONYM Syn_Table_1 FOR ADMIN.Table_1;
CREATE SYNONYM Syn_Table_1 FOR ADMIN.Table_1
*
ERROR at line 1:
ORA-01031: insufficient privileges

3) Altering the session
ALTER SESSION SET current_schema = ADMIN;

Session altered.

SELECT * FROM Table_1;

SELECT * FROM Table_1
              *
ERROR at line 1:
ORA-00942: table or view does not exist

In addition to ensuring you're in the right schema, try this one;
Once logged in as Alice, do: SET ROLE Viewer IDENTIFIED BY pwdviewer;

For more see here


Oracle SQL Summing Values From Two Different Tables Hey all so I am trying to add to (or subtract from) a column of different values based on a separate column in another table. Here is a example of what I am tying to do:

Table 1: (Current Inventory)

ItemName  |  Quantity
----------|----------
Baseball  |         5
Football  |        10
Jersey    |         3

Table 2: (New Shipment)

ItemName  |  Quantity
----------|----------
Baseball  |        70
Football  |       100
Jersey    |        50

How can I update my all items in Table 1 (current inventory) by adding Table 2's quantity to them?

You can do this using an UPDATE:

UPDATE inventory i
   SET quantity = ( SELECT s.quantity+i.quantity
                     FROM shipment s
                    WHERE s.itemname = i.itemname );

or a MERGE:

MERGE INTO inventory
USING
(
  SELECT itemname, quantity
  FROM shipment
) ship ON (ship.itemname = inventory.itemname)
WHEN MATCHED THEN UPDATE
    SET inventory.quantity = ship.quantity + inventory.quantity;

Example SQL Fiddle here if you want to play.


Sanity Test on Oracle Upgrade Database My oracle sever has been upgraded from Oracle 10g to 12c. DBA has created a box for that now my question is how developers will perform testing on that..
    Can anyone provide me the sanity or unit test document or scripts for an oracle upgrade database with respect to developer perspective.

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

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

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


Mysql/InnoDB says it doesn't have access rights to create temp tables, but it does For mysql  Ver 14.14 Distrib 5.6.33, for Linux (x86_64) using  EditLine wrapper

in my.cnf:

tmpdir          = /var/mysql_tmpfs

in /etc/apparmor.d/usr.sbin.mysqld:

  /var/mysql_tmpfs/ rw,
  /var/mysql_tmpfs/* rw,

Permissions for folder:

drwxrwxrwt  2 mysql    mysql     4096 Nov 28 19:46 mysql_tmpfs

Which, looks right to me. However, it is failing. Right now, mysql will start. However, when I load certain pages of my application, this is the error message that I see:

2016-11-28 20:23:41 39465 [ERROR] InnoDB: Unable to lock /var/mysql_tmpfs/#sql9a29_2_0.ibd, error: 13
2016-11-28 20:23:41 39465 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2016-11-28 20:23:41 7fd2105d3700  InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
2016-11-28 20:23:41 39465 [ERROR] InnoDB: Cannot create file '/var/mysql_tmpfs/#sql9a29_2_0.ibd'

However, I do not believe there is another process running. When I do a ps aux | grep mysqld, I get this:

mysql    38943  0.0  0.0   4396   736 pts/4    S    20:21   0:00 /bin/sh /usr/bin/mysqld_safe
mysql    39465 10.0  4.0 73330944 5301532 pts/4 Sl  20:21   0:16 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --log-error=/var/log/mysql/error.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306

In addition, I was able to create a file in that directory as the mysql user, which is why I'm extra sure that the user should have the required permissions.

I'm guessing this is something that I'm missing, but I've now had three other people take a look and we have not gotten anywhere. Please help.

Found a solution:

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

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

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


autonumber and one-to-many relationship I have a main table with an autonumber key.  I need to introduce a new table, related to the main table in a one-to-many relationship (i.e. a record in the main table could conceivably link to several records in the new table).  If the new table links to the main table via that autonumber key, then I (or the user) has to supply the corresponding number for each new record in the new table.  Is there a way to automate any aspect of this, so the user doesn't have to keep cross-checking this number when he makes a new entry?

Most  brands provide methods to retrieve the key an insert created in an identity or auto_increment column.

Unfortunately there is no general method for this, it will depend on the brand.

For example in MySQL it's called LAST_INSERT_ID(), in Transact SQL (SQLServer) @@identity (better: scope_identity()).


How do I update my database generated pg_basebackup through WAL files? Following on from this post...

I have a backup generated by pg_basebackup regularly, I would update it through the WAL files.

What is the best way to do it?

See the documentation on point-in-time recovery and WAL archiving for how to manage this.

Short version:

Set archive_mode = on in master
Set an archive_command in master that stores the WAL archives in a location accessible to the replica
Create the replica from a pg_basebackup
Create a recovery.conf on the replica that sets a restore_command to fetch the archived WAL. You may also want to set a recovery_target_time if you wish to stop replay at a certain point.
Start the replica and let it replay WAL

You may find tools like pgbarman useful as well.


When I implement database mirroring in SQL server it shows an error When I click the start mirroring button it shows the below error message. When I try to connect the mirror and witness server through management studio it's connecting and working fine. The user I am logging to the machine is with the domain user.

I would suggest you to verify the service accounts you are using:

SQL Server service account requires CONNECT permissions to partners’
endpoints
Best option is to use domain accounts for all partners
If using Local Service or Local System, must use certificate
authentication.

  Ensure that the start date of the certificate is the current date or a
  day in the past relative to all servers. Due to time zone differences,
  if the current date on the server is earlier than the start date of
  the certificate, endpoint authentication will fail. Set the START_DATE
  option of the certificate to the day prior to when you are creating it
  to ensure it will work in all time zones.

If using Network Service, must use the computer account
(domain\Computer$) Windows permissions are irrelevant. Do NOT add to
local admins group
Check the SQL log for errors

Also check for  SQL Server service account in case it  do not exist in logins on both Principal & Mirror

You can fix for above case by adding the service account in logins on both the servers and grant the connect permission as below;

GRANT CONNECT ON ENDPOINT::Mirroring TO [domain\loginname]

For further troubleshooting in case above does not help read here


Run a count query for every row ID in results I'm trying to count the number of cars at each parking lot in my app, along with some other info. But, I'm not too sure how to approach this.

Tables

I have 3 tables.

lot - this table contains information for a parking lot
manager - this table contains information for a parking lot's manager
car - this table has a list of cars that can be at a given parking lot

lot
 id | name | manager_id
----+------+------------
  1 | foo  | 1
  2 | bar  | 1

manager
 id | name
----+--------
  1 | alice
  2 | bob

car
 id | lot_id
----+--------
  1 | 1
  2 | 1
  3 | 2
  4 | 2

Desired output

Given a manager_id, I want a list of:

the name of the lot they manage
the name of the manager
the number of cars in that lot

This is what I want to end up with.

 id | lot_name | manager_id | manager_name | num_cars
----+----------+------------+--------------+----------
  1 | foo      | 1          | alice        | 2
  2 | bar      | 1          | alice        | 2

I've figured out how to get the manager name in the results, but I'm not sure how to get the number of cars for each lot.

This is what I have so far.

-- manager_id 1 is alice.

select
    id, name as lot_name, manager_id,
    (select name from manager where id = 1) as manager_name
from lot where manager_id = 1;

I'm thinking I need a join somewhere to bring in the car table and somehow do a count. Not exactly sure how though.

Im not sure if i understood correctly are you referring to a group by?

SELECT lot.id,lot.name,manager.id,manager.name,count(car.id)
FROM lot
LEFT JOIN manager ON lot.manager_id = manager.id
LEFT JOIN car ON car.lot_id = lot.id
GROUP BY lot.id,lot.name,manager.id,manager.name;


Database mirroring, "WITH NORECOVERY" Recently I have started to implement database mirroring and faced with some trouble. According to Books online, full database backup and T-Log backup must be restored in the mirror instance in WITH NORECOVERY mode:

  The mirror database must be in the RESTORING state for mirroring to
  work. When preparing a mirror database, you must use RESTORE WITH
  NORECOVERY for every restore operation. Minimally, you will need to
  restore WITH NORECOVERY a full backup of the principal database,
  followed by all subsequent log backups.

But when I start mirroring, I receive following exception:

  "Database "MyDB" cannot be opened. It is on the middle of
  restore(Microsoft SQL Server, Error 927)

Can anyone explain, whether I misunderstood something ?

Create endpoints on all servers:

CREATE ENDPOINT EndPointName
STATE=STARTED AS TCP(LISTENER_PORT = PortNumber, LISTENER_IP = ALL)
FOR DATA_MIRRORING(ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE,
                   ENCRYPTION = REQUIRED ALGORITHM RC4)

For a witness server, change ROLE = PARTNER to ROLE = WITNESS in the witness's endpoint.

Run the first SET PARTNER command on the mirrored server:

ALTER DATABASE DatabaseName SET PARTNER = N'TCP://PrincipalServer:PortNumber'

Notice that it is pointing to the principal server in the command, but you are running it on the mirror.

Then run the next two commands on the principal server, pointing the SET PARTNER at the mirror:

ALTER DATABASE DatabaseName SET PARTNER = N'TCP://MirrorServer:PortNumber'
ALTER DATABASE DatabaseName SET SAFETY OFF --OFF=async, FULL=sync

If you are setting up a witness for automatic failovers, use SET SAFETY FULL above instead of SET SAFETY OFF and then run this command on the principal:

ALTER DATABASE DatabaseName SET WITNESS = N'TCP://WitnessServer:PortNumber'


Possibly tempdb out of space or a system table is inconsistent While running DBCC CHECKDB I got this error

  Msg 8921, Level 16, State 1,   Line 1 Check terminated. A failure was
  detected while collecting facts. Possibly tempdb out of space or a
  system table is inconsistent. Check previous errors.

After that a coworker showed me this error, while executing a stored procedure.

  The Database ID 85, Page (1:3403), slot 0 for LOB data type node does
  not exist. This is usually caused by transactions that can read
  uncommitted data on a data page. Run DBCC CHECKTABLE

Then I tried to run DBCC CHECKTABLE for the table inside that procedure, Command Executed successfully for all table except one. I had to restart the server in order to overcome this problem... what if it occur in production server, how to over come it then.

DBCC CHECKDB makes extensive use of tempdb. It relies on a mechanism similar to snapshot isolation to make sure it always sees a transactionally consistent image of the database, independent what else might be going on.

The error you receive is most likely due to the fact that there is not enough room on the drive that tempdb is on. However, the error also states to check any prior errors. Were there any?


There is a mismatch between the data and results in your question but I think you need a query like this. It's not going to be very efficient if the orders table is big:

SELECT
    o.*,            -- pick the columns you want
    p.*             -- in the result
FROM orders AS o
  LEFT JOIN price AS p
    ON p.date =
       ( SELECT pi.date
         FROM price AS pi
         WHERE pi.date <= o.date
         ORDER BY pi.date DESC
         LIMIT 1
       ) ;

Tested (with modified data in the orders table) in: SQLfiddle.


Isolating a Distributed Replay environment I'm a little nervous replaying a captured trace/workload for performance analysis...

I'm using an isolated service account to run the SQL Services, including the Distributed Replay controller & clients....Is that all that is needed to ensure replayed transactions won't jump across to the prod database?

IE, is it possible that procedures or CLR objects could reach out and connect to another database, even though no linked servers are defined?

TIA!

Since the target SQL Server could have a stored procedure using a hard-coded SQL Server account and password to connect to some other server, I'd be unwilling to be 100% certain nothing could touch production unless I had a truly separate network setup to run the Distributed Replay batches against.

One could go to great lengths to try to ensure nothing hits production, but what if you miss something?

I'd setup a bunch of VMs for the controller, replay machines, and the target SQL Server(s), and configure them on their own virtual network, just to be sure.


High cpu usage on SQL Server We have a customer server containing SQL Server 2008R2 with about 50 databases. The issue is that the CPU usage reaches 100% (or close to it) and it lasts for a couple hours. This typically happens in the morning (usually somewhere around 6am - 9am). Average CPU usage is 30%

We looked at the scheduled reports and couldn't find anything - we did decide to schedule them earlier in the morning and that didn't do anything.

I'm going to remote in tomorrow to investigate the issue. We've rebuilt the indexes and that didn't help. I have a set of DMVs to check for queries with highest worker time and longest running time.

Do the SQL Server track all the queries run so that the DMVs can access the information? What other sql snippets would be helpful to diagnose the problem?

edit: Memory usage is pretty high too - about 80-90%. Actually another server is experiencing the same issue at the same time, I believe they're both hosted on a citrix server.

We've also done an index rebuild.

Do the SQL Server track all the queries run so that the DMVs can access the information?

I would suggest taking a look at a free e-book Red Gate published here:
Performance Tuning with SQL Server Dynamic Management Views. It is free and a good read. It will point out those DMVs that provide cumulative data versus point-in-time (what is currently in the buffer). As long as the server has not been rebooted you are in good shape to capture some good information.

  What other sql snippets would be helpful to diagnose the problem?

You can also use sp_whoisactive that will provide much more information if you can run it during the hours noted. Returns CPU, Memory, and I/O usage of each query. You can even have it pull the execution plan of the offending query as well.

Do a basic Bing/Google search for SQL Server DMV and high CPU queries or something will likely bring up some other good scripts/blog post on researching your particular situation.

Just a note on your comment regarding moving the scheduled reports, I would also look at ad-hoc reports on those that users may run on their own. Especially if you are running SSRS on the same server as the database engine. I usually take a look at the ExecutionLog table in the SSRS database (default name ReportServer). There is a wealth of information in there on execution time and rendering time of the reports.


How to print out the query SQL text of a transaction by querying the "fn_dblog" or DBCC LOG('DataBaseName')? At some point in the last 24 hours some changes were made to a customer SQL server 2008 (v 9.0 SP4) by one of a couple if possible applications that use the DB.

Its not particular critical, but it would represent a useful insight into how these applications are using the tables if I could dump out SQL statements corresponding to the time periods.

The tables I am interested in, do not change very often and hence transactions logs applying to those particular tables should not be very large in total rows.

I see the "fn_dblog" function and DBCC LOG('DataBaseName')  commands, and seem to have quite a bit of related information, but I am not sure how to take the next step and get them to produce a report of the actual changes.

There are some details in this blog post;
http://janiceclee.com/tag/fn_dump_dblog/

USE AdventureWorks
    GO
    SELECT [Transaction ID], count(*)
    FROM fn_dblog(DEFAULT, DEFAULT)
    where AllocUnitName LIKE '%Production.TransactionHistory%'--table name
    GROUP BY [Transaction ID]
    HAVING COUNT(*) >= 113443 --(estimated/actual) number of deleted records

However I don't have any details like that, just the table name (and the primary keys if necessary I can get that)

and another blog post that is using a similar example data given their table naming;
http://www.sqlskills.com/blogs/paul/post/Search-Engine-QA-6-Using-fn_dblog-to-tell-if-a-transaction-is-contained-in-a-backup.aspx

    USE AdventureWorks;

    GO

    SELECT [Current LSN], Operation, [Transaction ID], AllocUnitName FROM fn_dblog (NULL, NULL);

    GO

The log record at that LSN is:

    00000058:00001870:0001  LOP_MODIFY_ROW     0000:00001338  HumanResources.Employee.PK_Employee_EmployeeID

This is obviously in the middle of my contrived transaction - showing that it isn't all in the full backup. The end of the transaction isn't until way later in the log:

    0000005e:00000628:01b1  LOP_MODIFY_ROW     0000:00001338  HumanResources.Employee.PK_Employee_EmployeeID
    0000005e:00000628:01b2  LOP_COMMIT_XACT    0000:00001338  NULL

The transaction log does not not contain statements, it contains the physical changes occurred in the database. If you see a log record that indicate a delete you cannot know if this was a DELETE statement, a MERGE statement or a wide (split) UPDATE statement. If you see an operation indicating an INSERT you cannot know if it was an INSERT (...) VALUES (...) or it was an INSERT (...) SELECT (...) or it was an INSERT (...) EXEC or it was a MERGE or it was a wide (split) UPDATE. And so on and so forth. Specifically, the transaction log does not intend to substitute for an audit trace.

The transcriptional replication agent has means to reconstruct T-SQL operation with identical effect as those that changed a published article, but how it does it is not public information.

If you want to monitor data changes, use Change Tracking or Change Data Capture. If you want to monitor T-SQL activity, use profiler traces.


Are Blob Sizes Fixed? I'm using sqlalchemy to store objects in a database.  The kind of object I'm creating is unknown until run time.  I could be storing something as simple as a string.  I could be storing a jpg.  I might create my own file format and store that.  The point is it's flexible.

The table I envision contains at least two columns.  One describes the file type (.jpg, .txt, .py, etc), and the other is a blob with the file data.  What I want to know is whether I need to define the blob size when I create the table.  Is it possible to let mysql dynamically set the size of blob?  This would simplify my design nicely.

I'm aware there are other ways to do it, but I have good reasons to do it this way.  I don't mind going down this path and doubling back later if it turns into a dead end.

No, as defined, a Blob is "a binary large object that can hold a variable amount of data." It can hold between 0 and 2^16 - 1 bytes (65535). A LONGBLOB can hold up to 4,294,967,295 bytes (or 4GB).

In most engines, blobs are implemented dynamically, meaning that it will only take its actual size plus some extra bytes for lenght/addressing, etc. In fact, fixed-row size formats are normally not compatible with blobs (MyISAM fixed, Memory storage engine).

However, there are some reasons to avoid storing "files" on a relational database (and MySQL and InnoDB in particular, mainly due to the overhead on the transaction and binary logs, plus multiversioning and its incompatibilities with temporary tables on memory). The actual decision varies depending on the needs -the most common alternative is to store those on the filesystem and just add a URL on the database.

If you store those objects inside the database, the size will not be your biggest problem (unless they are over 4GB), but the performance on complex queries and writes. However, a pure key-value store may work well with MySQL model. There is also specialised document-based databases.


mysterious memory problem We have TWO of: your standard 2-node active/passive Windows Server 2008 R2 with SQL Server 2008 SP1 Cluster with 1 TB of RAM on the active node and 512 GB on the passive (same on both clusters).

Both OS and SQL "see" the 1 TB of memory and additionally set the following: "min memory" setting to 450 000 (MB) and "max memory" setting to 950 000 (MB)

The problem: every possible monitoring (perfmon metric; red gate sql monitor, task manager, resource monitor etc....) and every possible DMV query show that BOTH clusters cap at exactly 432.6 memory used (PerfMon reports the following for "SQLServer:Memory Manager - Total Server Memory " = 432.6 GB)

I understand the concept behind "it will only use as much as it needs", but I'm certain it can stuff more in there, because the clusters each host 2000+ databases for a total of more than 2 TB worth of often accessed data.

PLUS why would BOTH cluster need exactly 432.6 GB?

Sounds right to me 432.6 GB = 443,000 MB

The SQL Servers are consuming less than "min memory" because that is all they need.

  SQL Server is not guaranteed to allocate the amount of memory specified in min server memory. If the load on the server never requires allocating the amount of memory specified in min server memory, SQL Server will run with less memory.

That is, your working set of data does not exceed min memory and this is false:

  total of more than 2 TB worth of often accessed data.