When should a primary key be meaningful? Excuse the looseness of any definitions here, but I am trying to explore a simple concept.

A Primary Key uniquely identifies a row. There are possibly other columns in a table with unique values, so they can also uniquely identify a row (Candidate Keys), but the Primary Key is the one designated for the task.

Properties which make a Primary Key more useful include:

guaranteed to be unique; some other unique column values may be duplicated as the table grows
unlikely to change; although Foreign Key constraints can include cascading, it is better not to require this
will not be recycled; a row which is deleted for some reason should not free the old PK value to be reused

For these reasons, I generally recommend that the Primary Key have no intrinsic value, so there would never be a reason to change or recycle a value. That is, it should be otherwise meaningless.

I have seen Primary Keys which include some sort of code, such as a client code based on the name. The obvious problems are (a) if the client name changes then the PK should change too, and (b) there is too much of a risk of conflict with clients with similar names.

A half exception is the use of an auto-incremented number, which has a minor meaning of a sequence number. However, it is still stable.

The question is, under what circumstances, if ever, is preferable to use a primary key with some other real meaning? That is, is there anything wrong with the advice that the PK should be arbitrary, and you can generally get by with a sequence number?

under what circumstances, if ever, is preferable to use a primary key with some other real meaning?  (emphasis added)

Given that the focus of this question is "preferable" and not "acceptable", and accepting that this is still a highly subjective topic, I will say that I cannot think of a situation where it is best for the system to have a truly natural key for a variety of reasons (most of which has been said before in other answers that Paul linked to in a comment on the question):

What is thought to be unique is not always unique (e.g. Social Security Numbers / SSNs in the U.S.)
Sometimes things change, either in value or uniqueness (we don't control the external world)
Even when something should be "stable" in value and uniqueness (e.g. SKU, perhaps), can the incoming value be guaranteed to be correct? Humans often mistype stuff doing data entry. There are also bugs in export processes that might cause values in a file imported by your system to be incorrect. There are also bugs in other systems that feed data into yours that can allow for the data itself to not be entirely correct, even if their export process worked correctly.

I emphasize "truly" because there are two situations where I prefer to not have a new surrogate key:

Bridge Tables

(or whatever you like to call tables used only, or mainly, to represent many-to-many relationships)

Thing                          ThingXTag                Tag
------                         ---------                ---
ThingID INT AutoMagic PK --->  ThingID INT PK, FK
Stuff   SomeType               TagID   INT PK, FK  <--- TagID   INT AutoMagic PK
                                                        TagName VARCHAR

When modeling a bridge table (a table which doesn't exist in the logical model but is needed in the physical model), the PK there should be the existing Primary Key columns of the tables being related via this table. This allows for enforcing the proper uniqueness and non-NULL-ness of the values without needing a separate unique index / constraint. In the rare case of needing to foreign key to this relationship, it will:

be meaningful in that each of the key columns will actually point back to the original source tables without needing yet another join, and
prevent someone or something from updating the key columns that form the relationship between the two primary tables without updating the value being linked to via a foreign key.

WackyTable                           ThingXTag
----------                           ---------
WackyTableID INT AutoMagic PK
ThingID      INT FK            --->  ThingID     INT PK, FK (to Thing.ThingID)
TagID        INT FK            --->  TagID       INT PK, FK (to Tag.TagID)
AttributeX   VARCHAR                 InsertDate  DATETIME
InsertDate   DATETIME

I have worked on a system where these bridge tables had their own auto-incrementing, surrogate key PKs, and the single column surrogate key of the bridge table was referenced in other tables via FK:

WackyTable                           ThingXTag
----------                           ---------
WackyTableID INT AutoMagic PK
ThingXTagID  INT FK            --->  ThingXTagID INT AutoMagic PK
AttributeX   VARCHAR                 ThingID     INT FK (to Thing.ThingID)
InsertDate   DATETIME                TagID       INT FK (to Tag.TagID)
                                     InsertDate  DATETIME

It was a horrible, confusing mess that we wasted way too much time on for debugging, etc.

Sibling Tables

These are tables that are truly a single entity and thus have a 1-to-1 relationship. They are only split into two (or more, as is needed) tables for performance reasons. I have done this with tables having 1 million (or more) rows where it was either very wide, or if it was moderately wide and there were columns that were either not used very frequently or were string of over 50 bytes. Stuff like that. This keeps the core properties of the entity in a narrower table that fits more rows on each data page.

In these cases, the "sibling" table is at the exact same level as the initial table and should have the same PK. There is no use in giving it an auto-incrementing surrogate key since each row has what amounts to a natural key from the initial table.

Product                            ProductProperty
-------                            ---------------
ProductID  INT AutoMagic PK  --->  ProductID        INT PK, FK (to Product.ProductID)
Name       VARCHAR                 ShortDescription VARCHAR
SKU        VARCHAR                 SomethingElse    SomeType
Quantity   INT                     UpdateDate       DATETIME
CreateDate DATETIME
UpdateDate DATETIME

To be clear, I am speaking in terms of the physical model, not the conceptual model. I assumed that the focus of this question was the physical model since it is framed in the context of issues that do not exist conceptually: surrogate keys, issues dealing with usage of the primary key value, etc. With that in mind, I was not implying that natural keys should not be stored and used for identification. On the contrary, natural keys are great "alternate keys" and should have unique constraints / indexes placed on them. However, the idealism of the conceptual model does not always translate directly to the physical model. Data integrity (i.e. the stability and reliability of the data model) is a top, if not the top, priority of the physical model. And so practical considerations, such as using surrogate keys, must be made to ensure that this goal is met and not compromised. Meaning, if you have SSNs or SKUs, etc, then absolutely store them in a column that has a unique constraint on it, and have the system do a lookup on that value since auto-generated numbers shouldn't be used externally anyway. Users don't need to know the auto-generated ID number of a record: they should pass in a value that they do know (e.g. email address as a lookup for UserID / CustomerID, flight confirmation codes in combination with flight dates, etc) and the system should translate that into the auto-generated value that it uses from that point forward.

Yes, the problems noted at the beginning of this answer are still potential issues when using natural keys as alternate keys. However, the difference is that the problem is isolated to (usually) just one table. If someone made a mistake and created a unique index on "flight locator", it might be a while before they get a violation. But once they do, it is easy enough to drop that unique index and recreate it to include the flight dates as well. Or, if you change your email address (often used as the Login) on a system and get an error because it was in use by someone else years ago (legitimately), that can most likely be handled by support without any impact / risk to existing related records. In both cases the rest of the data model is untouched for the necessary changes.

Again, this is a pragmatic approach to:

minimizing potential data loss that can happen when migrating a primary key that has been referenced by one or more foreign keys (and minimizing the scope of the project also reduces the maintenance window :-). While not all PKs (or Unique Constraints/Indexes) have FKs, using a surrogate should certainly reduce the number of columns that have such dependencies
making the system as durable, resilient, and efficient as possible. The physical model is the "in practice" to the "in theory" of the conceptual model. And we already make quite a few adjustments and considerations given that the conceptual model doesn't care about implementation. Yet we make choices to utilize vendor-specific features, for performance (e.g. denormalization, lookup tables, "sibling" tables as noted above, etc), to create the "bridge" tables (as noted above), and so on.

I don't know how many systems used SSNs (Social Security Numbers in the U.S.) as PKs, but for any that did, some of them (many perhaps) may have avoided issues with them not being as unique as they should have been. But, none of those systems were able to avoid changes over the years regarding the need to handle them much more securely. Systems treating SSNs as an alternate key require very little development time to switch over to encrypting those values, and the system requires little downtime (or none) to make the changes at the data layer. Given that we all have backlogs of projects that we will likely never get to, businesses tend to prefer that these annoying yet inevitable changes will cost them 5 hours instead of 20 - 40 (don't forget that changes need to be tested, and hence the scope of the changes also directly impacts how much QA time is needed for the project).

To be explicit, there are some scenarios where it is "acceptable" to have natural keys, though I don't think I would go so far as to say "preferable".

State / Region and Country codes: If you use codes maintained by the International Organization for Standardization (ISO) (e.g. "US" = United States, "FL" = Florida, etc) then these are probably reliable enough to use, and I have used them as they are short (i.e. not bad for performance) and human-readable. These codes are also used often-enough in a variety of other ways, even outside of computer systems, that people have a general familiarity with them, even ones that don't initially make as much sense to some folks (e.g. "DE" = Germany might not be intuitively obvious to those who are unaware that "Germany" == "Deutschland" in German).
Internal lookup value codes: You can't control external sources, but (hopefully) you are in control of your own system. If your system has department codes, status codes, etc that are used internally, then it should be fine to come up with short codes for them (2 - 4 bytes). At 4 bytes it would be using the same amount of space as an INT and if using a binary Collation (one ending in _BIN2, or even _BIN, but _BIN2 is preferred) then it should compare just as fast. Having relatively meaningful values for such codes can make support / debugging easier. However, you still get into the situation where over time, department names, etc might change and the codes might not be meaningful anymore.


How do you fix Autogrowth being set to 12800% We're running SQLServer 2005 SP1 and for some weird reason our one database transaction log automatically changed it's Autogrowth to 12800% which isn't even a valid number. I cannot change the growth % because of the invalid number.

I've scoured the Internet and saw a lot of other people also had this problem but with no clear solutions.

What caused this to happen and how can I fix it?

Unfortunately it's a bug.

You can find all the affected databases by looking in sys.master_files. Write ALTER DATABASE scripts to change it.


Counting number of total results for each column name I have a query. I just would like to get the number of orders for each distinct customer.
But the results give me very high numbers, most probably somehow summing up all ids. What am I missing?

SELECT
  COUNT(b.order_id) AS number_of_orders_for_each_distinct_customer,
  a.customer_id
FROM messages a
  INNER JOIN orders b ON a.customer_id = b.customer_id
  INNER JOIN order_items c ON c.order_id = b.order_id
  INNER JOIN products d ON c.product_id = c.product_id
GROUP BY a.customer_id

By JOINing to order_items that query returns one line for each row in the order_items table. The COUNT is therefore counting rows from order_items, not from table orders. This is why you think you are over counting.

Solutions would be to remove the joins to tables which are not referenced directly in the resultset, or to use the SELECT COUNT(DISTINCT ..) syntax.


Optimizing queries doing filtering on unindexed fields in large MongoDB collection We have a large MongoDB collection, 6TB and growing a lot. The collection is used for user and automated feedback, and as such will be used for all sorts of analytics. One document has about 20 fields. Since collection is used for analytics, filtered fields can be of any combination and as such, creating indexes for every combination would be too much.

Is there a good way to index collection for such (random-like) queries? If not, is there a way to optimize queries themselves?

If this can help with optimizing, queries are paged.

Example query:

db.usage.find({"appVersion": "4.0.0.0", "expression": "abcd"})

where appVersion is not indexed.

Analyze what are "most common" queries and create indexes for "top ten" queries, including only two first "columns" (keys). Remember to create only one index for key pair (if you have keys A and B, create only index A,B and not B,A). And keep only one key as "first" in index. (so If you have that A,B index and you should make A,C, make it as C,A).

After that create "single key indexes" for those keys what are NOT created on at the first step. (So single key index for key B, because it's not "first" key in indexes A,B or C,A)

You can use DEX to analyze "missing indexes".


Speeding up insert in SQL Server I have a stored procedure which inserts some records in a few  tables. In couple of tables at least , the inserted records number 10000+. Not more than 15K though. Takes 3-5 mins for this procedure to complete. Also same procedure can be called from multiple user sessions which results in some sessions waiting for 20 mins to get a response. Is there something I can do to reduce this time?

The database recovery mode is FULL(cannot be changed) and hence per my understanding sqlBulkCopy wont help here. Would love to hear your thoughts on this.

The table contains 15 columns. Among which  5 columns are foreign key to this table. There is no identity column but clustered index in combination of all the 5 foreign key columns. I have couple of index on other key columns . The rest of columns are decimal and varchar(50). I do have one varchar(max) column though.

Trying to get query or query same for your reference . A screen grab of most expensive operation(54) in the query:

If the clustered index of the table is on combination of columns instead of an identity column , will the inserts be impacted?

Basically the query is 'insert into this_table select from this table join with 5 other tables whose primary keys are foreign keys here to update those values. Joins are all on keys between the tables. I wouldn't mind posting the query but would you need the schema all tables involved in query as well?

EDIT2:
First of all thanks all of you guys for your answers,comments and thoughts. The object from which read happens turned out to be a heap with no clustered index. Modifying it did give significant improvement in read operation which improved overall .

I marking David Spillet answer as accepted as it gave a methodical way to approach the problem. Learned a few things about posting questions :)

Also thanks for Frisbee for comments and answers. I know i kept on modifying the question :)

Without more detail about the procedure[1] and the table it is inserting into we can't offer any specific help. Some general advice though:

If you are inserting the rows individually, try to arrange them so that they are inserted in large blocks, all at once if possible.
Confirm that the procedure is in fact being slow due to its own activity rather than waiting for locks held by other processes to clear.
If you are pulling the inserted rows from a view or complex inline statement make sure that this is optimised (perhaps there is something like an avoidable table scan happening when reading what it needs before it even tries to write the new rows?).
If you are using a complex statement and/or processing data in steps, check that you are not spooling stuff to tempdb unnecessary.

[1] if it is "top secret" so you can't just include the code directly[2] or (if it is long) via pastebin or similar then perhaps a sanitised version?
[2] though if it is that top secret maybe you should be paying a consultant rather than asking for free advice in public ;-)

Update

The query plan posted does suggest that for that particular statement you have a read issue not a write issue. It look like you have a correlated sub-query that is providing an aggregated figure per row and there isn't a suitable index to filter on (though there is one that allows it to avoid an extra sort operation, hence the index scan was not followed by a sort). More than that (which is educated guesswork anyway) I can't say without the details already requested. For this specific statement: the SQL for statement 54 and the table+index structure of "object8" and "object9", preferably without obfuscation[3].

[3] By disguising names you hide something that might give us clues about what you are actually trying to achieve. Help us help you, provide the details requested and don't actively hide details unless you really have to.

Regarding Cost Percentages

As Aaron said, the cost percentages are based on approximate times for a particular configuration of developer PC at Microsoft in the late 90s. Even then they were not intended to be anything other than estimates/indicators and IIRC the calculations have not been jiggled to account for changes in CPU and memory technology since. Also the calculation works on logical operations: they don't differentiate between work performed in memory and physical IO operations. Be careful too to consider the rest of the query and not just that one expensive chunk - it may be that the expense is caused by a missed optimisation elsewhere that results in an otherwise efficient operation to be run millions of times instead of a few or just one - the cost % may lead you to try to optimise that part when in fact the core problem is a step or a few away.


Couldn't install SSMS: A pending restart is blocking setup from completing SSMS version : 17.7

Log file: Log_file

I have SQL Server 2016 and Visual Studio 2017 Community Edition.
I tried reinstalling both tools but the error is still the same.
Some links mention registry values and I tried that as well, but the problem remains the same.

I have restarted the system multiple times, but the restart pending is still showing.
I found a couple of other methods on some sites, tried them as well, but still the error message is showing.

It stops at the point shown below--not sure about Visual Studio 2015 as I'm using VS2017. I have tried repairing VS and SQL Server 2017 as well.

Go to the Visual Studio Isolated Shell download page, fill out the form, and ask for permission to get VS Shell.  Download Visual Studio 2015 Shell (isolated), run it, repair, reboot, run it again, uninstall, reboot.

Now, SSMS 17.x should install without errors.

Alternatively, (and less safely): Search the registry for "visual studio 2015" and delete all the keys that are related to the shell or its prerequisites. Repair SQL Server 2016 and then install SSMS.


Benefit of filegroups and setting filegroups to read-only Can someone cite me a real world scenario on when changing multiple filegroups to read-only is a good option and when to use them? What benefits are there if you set it to read-only?

On a database with multiple filegroups, do you have to do backups for the whole database and also backup each of the files of that filegroup? Can you also give me an example of when filegroup backups will be used? I don't see why it would be beneficial to backup a filegroup when you can just backup the whole database. Hoping I could get a real world experience where this filegroup backup would be ideal

Your question is a bit wide-ranging, however some of the main points include the following.

Multiple file groups, in general:

can be a performance improvement, assuming they are physically isolated.  Typically, you might create one filegroup for clustered indexes, one filegroup for non-clustered indexes, and one filegroup for heaps.  Or you might create multiple filegroups in a partitioning situation.
can improve recoverability by enabling piece-meal restore.  Piece-meal restore can be used to bring critical production data online quickly while allowing the restore of archival data to not impact online activities.  Be aware online piece-meal restore is an "Enterprise-only" option.  I've written a blog post showing how to do piece-meal restores over at SQLServerScience.com
do not need to be backed-up individually, however they can be backed up individually, which might be necessary for VLDBs.  Note, when backing up individual filegroups you must also backup the primary filegroup.

Read-only file groups:

Can be used to reduce contention for reporting-only data.
Can be used, obviously, to prevent modifications to data.
Do not need to have the recovery process ran when the database is brought online.  This can substantially improve recovery time for VLDBs.
Also, while read-write filegroups cannot be used with NTFS filesystem-based compression, read-only filegroups can.  The implication here is that for a very limited set of systems you may be able to see a slight performance improvement for VLDB data that resides on slower disks by compressing the readonly filegroup files on disk.  This would of course need to be evaluated on a case-by-case basis vs the CPU overhead necessary to decompress the NTFS clusters as they are read off the disk.

Myths surrounding read-only filegroups:

"Statistics will never be updated for tables stored on a read-only filegroup."  This is totally incorrect, and can be proven by creating a test-bed as shown in my blog post on SQLServerScience.com.  Thanks are due to @PaulWhite for pointing this out to me.
"No locking occurs in read-only databases. This can cause faster query performance". This is incorrect unless the entire database is read only.  A read-only filegroup alone does not allow shared locks to be skipped.


Changing collation of SQL Database in Production environment to minimize downtime We have a scenario in which we want to change the collation of our production database (including columns) from SQL_Scandinavian_Pref_CP850_CI_AS to Finnish_Swedish_CI_AS. We have developed scripts to do that. But executing this script in a large database of size more than 100GB will take considerable time and we can't afford to take very long downtime. So we decided to reduce this downtime using the below strategy:

We will setup Transactional Replication and we will initialize the subscriber using the database backup method.
Publisher database will be live with the application and its transactions will be delivered to the subscriber database through Transactional Replication.
We will execute the Collation Change Script on the subscriber side, and it does allow us to execute this script when SQL Server is the same for publisher and subscriber databases. We recently found this in SQL Server 2019.
Now, the pain point is, it's not replicating correctly the varchar, char column's data when it contains special characters like 'åÅäÄöÖ'. On the subscriber side, we are getting weird characters like '†„Ž”™'

Can you please suggest how we can resolve this bug or any alternative architecture to minimize the downtime in production while changing the database collation(including columns)?
Furthermore, my collation change script is doing the following tasks on the subscriber database to change its collation:

Drop Foreign Key Constraints
Drop indexes including Primary Key
Drop check and default constraints
Drop user statistics
Drop views, computed columns, SPs to resolve Object Bound bugs
After execution of the above steps, the Tables are ready for collation change. So, the script will change the collations of the columns for every table one by one.
Recreate above listed constraints after successful execution of step 6.

I just tried this and it worked in Transactional Replication:

Server Level Collation is Finnish_Swedish_CI_AS
Database Level Collation of both sides of the databases are the same which is SQL_Scandinavian_Pref_CP850_CI_AS
Column Level Collation at publisher side database is SQL_Scandinavian_Pref_CP850_CI_AS and subscriber side database is Finnish_Swedish_CI_AS

The above setup delivered correct data from the publisher side database to the subscriber side database.
Its column-level collation change, which takes time and that depends upon how much data the table contains. Database level collation change can be managed in downtime by just dropping the dependencies first and recreating them. So, its the Database level collation which was causing this data issue, when it was differing, not column-level collation.


Is it suitable innodb_buffer_pool_size for MySQL Master-Slave I am wondering how to decide the value of innodb_buffer_pool_size for my Master-Slave style database.

I have a set of MySQL, 1 x master + 1 x slave.

As I know that the innodb_buffer_pool_size is generally for data storing in memory to speedup the data access (read). So can I set the different value between master & slave ?

For example, if I calculate the suitable innodb_buffer_pool_size value is 8G, I should set 8G in my slave but maybe 4G or less for my master ?

That's because the innodb_buffer_pool_size can benefit the read not the write process. Am I wrong ?

Thanks.

The buffer pool also benefits writing changes. When you change a row, it is first changed in the copy of the row's page, which resides in the buffer pool. InnoDB flushes that page back to disk later, and doesn't make you wait for the flush to finish before it calls the update complete (it only has to log the change in the InnoDB redo log to say it's safe).
The buffer pool also contains the change buffer for secondary indexes. This basically queues up updates to indexes, so when you update a table that has indexes, you don't have to wait for your updated values to be inserted into the indexes. This will also happen later in the background.
So both reads and writes benefit from having a buffer pool, and also both source and replica benefit from having a buffer pool. The larger the buffer pool, the more likely a given page may already be in RAM when you run a query either to read or write that page.
There is a point of diminishing returns on increasing the buffer pool size. If you only have a small database, say 10MB, it makes little sense to allocate many gigabytes of buffer pool.
But it's common to have a database that is larger than your RAM, so then we expect the buffer pool to be sized to hold the most frequently-accessed subset of database pages. Not all of your database is equally likely to be queried. Therefore it could be that a buffer pool 1/4 the size of your database is sufficient to make 95% of your queries run without needing to load more pages from disk. These ratios are just an example, because it depends completely on your application.


Command to add column to all tables in a schema Could you please give the command to add a column to all the tables if the table doesn't contain that column in a schema?

e.g. The schema schema1 contains 50 tables in which 20 tables contain a column named colTest.

I need to add the column colTest to the remaining 30 tables.

You can do this with a loop and dynamic SQL:

do
$$
declare
  tname record;
begin
  for tname in select t.table_schema, t.table_name
               from information_schema.tables t
               where table_schema = 'schema1' --<< change schema name here
                 and not exists (select *
                                 from information_schema.columns c
                                 where (c.table_schema, c.table_name) = (t.table_schema, t.table_name)
                                   and c.column_name = 'coltest')
  loop
    -- change column definition in the following string
    execute format('alter table %I.%I add column coltest integer', tname.table_schema, tname.table_name);
  end loop;
end;
$$


Problem processing quotes by T-SQL during Job creation This is my first post ever in this website, which I found very helpful and instructive.

I need to execute a job with this step (which checks the mirroring state and send the result to me via Database Mail)

DECLARE @tab char(1) = CHAR(9);

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Mail',
    @recipients = 'xxxxxxxx@xxxxx.com',
    @query = 'SET NOCOUNT ON;

SELECT CONVERT(NVARCHAR,d.name), ISNULL(m.mirroring_state_desc, "")
FROM sys.databases d
LEFT JOIN sys.database_mirroring m ON m.database_id = d.database_id
WHERE m.mirroring_role_desc <> 'MIRROR'
'
    , @subject = 'Mirroring state'
    , @query_result_width = 80
    , @query_result_separator = @tab
    , @exclude_query_output = 0
    , @query_no_truncate = 0
    , @query_result_no_padding = 1
    , @attach_query_result_as_file = 1
    , @query_attachment_filename = 'CSV_Extract.csv';

The problem I have is that when I save the step, and try to execute the job it gives me a syntax error, because apparently T-SQL doesn't process the quotes with the condition WHERE I put in there, the 'MIRROR' parameter.

How can i rewrite this code to make sure the parameter 'MIRROR' in the WHERE condition will be processed? (I managed to get it to work by deleting that line, but it sent me a result of all databases, even tempdb)

Your code is nearly there, just change:

'...
WHERE m.mirroring_role_desc <> 'MIRROR'

to

'...
WHERE m.mirroring_role_desc <> ''MIRROR''

Jonathan Fite also mentions this nice QUOTENAME() solution in the comments below:

'...
WHERE m.mirroring_role_desc <> ' + QUOTENAME(MIRROR, '''')


Should I manually VACUUM my PostgreSQL database if autovacuum is turned on? I use software which makes a big PostgreSQL database (there is a table with a million rows in it) and the developers says I should VACUUM and ANALYZE periodically. But the PostgreSQL database default is autovacuum turned on.

Should I vacuum/analyze at all? What are the benefits? What's the difference between automatic and manual vacuum

For example, in Pgadmin3, I have this:

I agree with ETL that there is no short answer. Size is not the only thing that matters - we run quite large PostgreSQL OLTP Databases (with some tables > 100.000.000 rows) under heavy load and currently we rely on autovacuum only.

Yet, two things seem important to me:

There seems to be a consensus, that autovacuum should never be switched off, unless you have a very well defined workload on your database and you know exactly what you are doing. But, naturally, you could do additional VACUUM and/or ANALYZE runs.
Before considering additional VACUUM runs, I would check how autovacuum keeps up. You can check whether any tables are beyond the autovacuum threshold by querying pg_stat_user_tables and pg_class. I posted such a query on another thread, that might be of interest: Aggressive Autovacuum on PostgreSQL.

Unfortunately, it is not as easy (i.e. not possible at the moment) to do a similar check for autoanalyze thresholds. However, autoanalyze kicks in long before autovacuum by default and it is much cheaper. So, basically if your database can keep up with autovacuum, it will probably be fine with autoanalyze too. The last autoanalyze dates can also be queried from pg_stat_user_tables.

Some parts of the (most excellent) PostgreSQL documentation, that I found helpful:

Routine Vacuuming
The Statistics Collector


Database Design - Voting System I'm creating a website in which people can submit there own lists, and then essentially others Up/Down Vote the items within the list. I'd like the list (If the submitter allows "votes") to be ordered by the amount of votes (Easy Enough to do in SQL I know).

However, my issue is with the DB Design, I don't know how to design it to make it work.

So far my Idea is:

Lists Table: ListID, ListDescription, ListItem(x [e.g. 1, 2, 3 4]), CreatedDate, UserID (if it is user submitted).

Votes Table: VoteID, ListID

I can't quite get my head round, how to setup the votes table to record votes for each item in the specified list?

Could anyone help please?

Thanks

I think I'm understanding basically what you're after.

Are you wanting to record each individual vote or maintain a single value in a +1/-1 fashion? This could be a start anyway:

UserLists Table

Userid
ListId
VotingAllowed
CreationDate

ListItems Table

ListId
ListDescription
InitialSequence
Votes


How to get difference in value over 15min interval? I have this table:

The 2 important columns here are domainid and Soldtickets.

Soldtickets show how many tickets were sold today.

I make a call to my server and it tells me that I made the call
at "2016-03-30 14:18:01.4130000" and that 0 tickets have been sold for the
Domain with DomainId 53.

I am trying to figure out how I can find the difference.

I call the server every two minutes and I get data.
I want to check the data that I have got the last 15 minutes and see if soldtickets has changes and how much.

Example:

  At :

  2016-03-30 14:18:01.4130000

  For DomainId

  53

  0 Tickets Sold

  At :

  2016-03-30 14:26:01.4130000

  For DomainId

  53

  5 Tickets Sold(5 tickest total for today)

  At :

  2016-03-30 14:30:01.4130000

  For DomainId

  53

  5 Tickets Sold ( same as before, NO change)

I want to know how many were sold the last 15 minutes.
In this case it would be 5 tickets but what if at

2016-03-30 14:44:01.4130000
I get back that I have sold a total of 7 tickets for today, then the difference for the last 15 minutes is 2 tickets.

So far I can get the inserts of the last 15 minutes like so:

DECLARE @LatestFifteenMinTickets TABLE
(
    DomainId bigint,
    SoldTickets bigint
)

DECLARE @FifteenMinDate datetime2

SELECT @FifteenMinDate = DATEADD(minute, -15, GETDATE())

INSERT INTO @LatestFifteenMinTickets(DomainId,SoldTickets)
            SELECT DomainId, SoldTickets
            FROM DomainDetailDataHistory
            WHERE [Date] > @FifteenMinDate

I have tried a few scenarios but I honestly don't know how to conntinue.

How can I do this?

EDIT:

  Lets say If i get back that I have sold 0 tickets today at 13:00. And
  the next 15 minutes I get 15 inserts where soldtickets is also 0. the
  diffirence here is nothing since 0-0 = 0 Which means I have sold 0
  tickets the last 15 minutes AND sold 0 tickets for the entire day (so
  far). But at 14:00 I get back info from server that 10 tickets are
  sold. The next 15 minutesI keep getting sold tickets = 10 which means
  that the last 15 min i have sold 0 tickets but for the entire day i
  have sold 10 tickets –

I Need to check the soldtickets columnt for every unique DomainId and see if there is any difference to the most current one. If there Is i want to know what the difference is in numbers

Just group your @LatestFifteenMinTickets table by DomainId and take the difference between MIN(SoldTickets) and MAX(SoldTickets). That will give you the number of sold tickets for each domain within the period collected in your table variable:

SELECT
  DomainId,
  SoldToday         = MAX(SoldTickets),
  SoldSince15MinAgo = MAX(SoldTickets) - MIN(SoldTickets)
FROM
  @LatestFifteenMinTickets
GROUP BY
  DomainId
;

Unless SoldTickets can decrease as well as increase (can tickets be returned?), in which case, rather than the minimum and maximum, you should probably take the first and last value. But in order to be able to do that, you should have the Date attribute in your table variable as well. Then the calculation could be organised using the analytic functions FIRST_VALUE and LAST_VALUE:

SELECT DISTINCT
  DomainId,
  SoldToday         = LAST_VALUE(SoldTickets) OVER (PARTITION BY DomainId ORDER BY [Date] ASC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
  SoldSince15MinAgo = LAST_VALUE(SoldTickets) OVER (PARTITION BY DomainId ORDER BY [Date] ASC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
                   - FIRST_VALUE(SoldTickets) OVER (PARTITION BY DomainId ORDER BY [Date] ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM
  @LatestFifteenMinTickets
;


drop table in a begin try begin catch block - is there any loss of performance? The code below is a dynamic sql that will give you a list of all alerts in your system.

It works.

There is a temp table in the code for reasons beyond this question.

This question is related to the way I make sure the temp table does not exist, before I create it.

I put a drop table in a try catch block as you can see in the code below.

My questions are:

Is there any loss of performance?
Any disadvantage in doing this?
Why is this not considered generally best practices?

I still have some servers sql-server-2008-r2 but mostly my servers are sql server 2016.

EXEC Sp_executesql
N'

begin try
 drop table #tmp_sp_help_alert
end try
begin catch
end catch

create table #tmp_sp_help_alert  (
id int null, name nvarchar(128) null,
event_source nvarchar(100) null,
event_category_id int null,
event_id int null,
message_id int null,
severity int null,
enabled tinyint null,
delay_between_responses int null,
last_occurrence_date int null,
last_occurrence_time int null,
last_response_date int null,
last_response_time int null,
notification_message nvarchar(512) null,
include_event_description tinyint null,
database_name nvarchar(128) null,
event_description_keyword nvarchar(100) null,
occurrence_count int null,
count_reset_date int null,
count_reset_time int null,
job_id uniqueidentifier null,
job_name nvarchar(128) null,
has_notification int null,
flags int null,
performance_condition nvarchar(512) null,
category_name nvarchar(128) null,
wmi_namespace nvarchar(max) null,
wmi_query nvarchar(max) null,
type int null)

insert into #tmp_sp_help_alert
exec msdb.dbo.sp_help_alert

SELECT * FROM #tmp_sp_help_alert AS tsha --WHERE (tsha.name=@_msparam_0)
--drop   table #tmp_sp_help_alert

',
    N'@_msparam_0 nvarchar(4000)',
    @_msparam_0=N'SQLPROD2 Alert - AG Role Change'

It's better to check for the table existence rather than trying to drop it and ignore any errors. This is generally considered a bad practice in all programming languages. In SQL Server and all RDBMSs you have metadata, so there are certainly better ways of doing this.

An example:

IF OBJECT_ID('tempdb..#tmp_sp_help_alert') IS NOT NULL
    DROP TABLE #tmp_sp_help_alert;

Moreover, temporary tables are automatically deleted when they get out of scope (for local temporary tables, when the procedure ends), so it might be overkill to test for table existence. For the same reason, it might also be unnecessary to drop the table at the end of the scope.


Why am I able to query a remote database without a dblink? While researching a security concern, I discovered that users are able to query a remote database without the need of a dblink.

However, this was only true for databases that had no value for DB_DOMAIN. If the local database's DB_DOMAIN was qualified with "foo.com", then a user could not query the remote DB and required a dblink.

I could not find any documentation that explains why this happens and am a very new DBA. What is it about Oracle that allows this to happen? Is the only way to prevent this to qualify every database's DB_DOMAIN with "foo.com"?

Example of accessing information from remote DB without a dblink below:

From DB: FooB

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Feb 26 16:05:21 2015

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter db_domain;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_domain                            string

From DB:FooA

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Feb 26 16:08:25 2015

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter db_domain;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------------
db_domain                            string
SQL> select sysdate from dual@FooB;

SYSDATE
---------
26-FEB-15

SQL> select db_link from all_db_links;

no rows selected

What you described here is absolutely normal and intended. This feature is called a global database link:

http://docs.oracle.com/cd/E24693_01/server.11203/e17120/ds_concepts002.htm#i1007888

It is a "side effect" when for example Oracle Internet Directory is in use, I have seen this at a few companies who have a lot of databases.

More and better explanation can be found in the support note: What are Global Database Link and How do you Disable them? (Doc ID 1632329.1)

  You are able to make a distributed connection using a Database link,
  However you have not created the database link used.

  You expect an ORA-2019 since the Database link Object itself does not
  exist but somehow the connection works. How is this possible?

  This is because of a default feature Called GLOBAL DATABASE LINKS. The
  feature is due to LDAP usage.

  Global Database links were introduced in earlier versions of Oracle
  when the Oracle Names Server was in use. You could create a Database
  Link that was stored in the Oracle Names server and accessed by those
  who accessed the Oracle Names Server. Once Oracle Names was deprecated
  the feature was moved to the replacement LDAP Naming.

  When a Database link is called the database link name will be checked
  against the PRIVATE link then PUBLIC links then GLOBAL links. If the
  Database link name matches the entry listed in the ldap server the
  actual Database link object is not required. The DBLINK will use the
  current user to connect therefore it only works if the user has access
  to both instances and has the same user id and same password on both.

  This is default behavior when LDAP naming is used.

  Currently the only way to disable the feature is to stop using LDAP
  naming.


SSMS 2016 forced connection Some developers use SSMS 2016 with db_owner permissions to database(s) on a shared SQL Server, but error logs show an attempted login to the first user database on the instance, which they don't have access to. The log error is the typical 18456, but the user didn't try to connect to that database (I watched). Also, SSMS clocked for about 5-10 seconds when trying to expand the databases folder. After some time all the databases on the server were present. This hasn't been an issue using SSMS 2014. As a work around we granted 'connect to all databases', for the user which did stop the errors in the log and removed the delay when expanding the database folders, but doesn't seem like the long term solution.

Does this sound like a SSMS 2016 bug or user permissions? Any thoughts?

Thanks in advance,

TJ

Default db is set to the user database with db_owner permissions. This has been tested on different servers with the same login error result, and login was to the first user database. This is a AD user.
Error: 18456, Severity: 14, State: 38

@sqlband I can confirm that this a bug ( or feature) of SSMS 2016.

I was able to reproduce this in SSMS version Microsoft SQL Server Management Studio    13.0.16106.4.

USE [master]
GO

CREATE LOGIN [ssmsspeed] WITH PASSWORD=N'ssmsspeed', DEFAULT_DATABASE=[WideWorldImporters], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [WideWorldImporters]
GO
CREATE USER [ssmsspeed] FOR LOGIN [ssmsspeed]
GO
USE [WideWorldImporters]
GO
ALTER ROLE [db_owner] ADD MEMBER [ssmsspeed]
GO

Now connecting to SSMS using ssmsspeed login. When I expand the database node it takes about 10 seconds to expand as explained by OP. I can see this in my error log and in my security audit file.

  Message Login failed for user 'ssmsspeed'. Reason: Failed to open the
  explicitly specified database 'AdventureWorks2014'. [CLIENT: ]

  Error: 18456, Severity: 14, State: 38.

If I list my user databases order by name AdventureWorks2014 is the first one.

AdventureWorks2014
GMODBA
hacked
test
Warehouse
WideWorldImporters

I also tested this by making Master database as Default_database but same result.

USE [master]
GO

CREATE LOGIN [ssmsspeed1] WITH PASSWORD=N'ssmsspeed1', DEFAULT_DATABASE=[Master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [WideWorldImporters]
GO
CREATE USER [ssmsspeed1] FOR LOGIN [ssmsspeed1]
GO
USE [WideWorldImporters]
GO
ALTER ROLE [db_owner] ADD MEMBER [ssmsspeed1]
GO

When I do the same test in Microsoft SQL Server Management Studio  12.0.4100.1 I cannot reproduce this. Expanding database node is instantaneous and no login fail message in error log.


Dropping person while their connection sleeping in SQL Server I have a C# application that communicates with SQL Server.

When I try to drop a user that recently had a connection with SQL Server, it gives me an error that I can't delete them because they have connections open in the database.

All SQL queries are appropriately wrapped in using statements but when I run sp_who, it tells me that that person has a connection in a sleeping state, I have researched this and found out that this is normal behavior meant for speed improvements.

To get rid of this connection, I used SqlConnection.ClearPool which works and I can now delete the user but then the queries becomes a decent amount slower, which I expected and know the reasons why it's happening.

What's the best way to deal with this? Force the connections closed only when trying to delete the person somehow? If so, how is this done?

Thanks in advance!

When I try to drop a person that recently had a connection with SQL Server

Why are you doing this?  What drove the need to have to do this?

It sounds like you're running into connection pooling.  But connection pooling is there to help you, it's a great optimization.  By clearing the pool you're wiping out this caching mechanism.

My recommendation, without knowing more about what you are trying to do or what you need, would be to leave it as it is and let connection pooling do what it does best.


Insert a 'NULL' string in a table in PostgreSQL I would like to know if it is possible to insert a 'null' string in a column which has a NOT NULL constraint.
I'm using PostgreSQL, although this probably applies to other database management systems as well.
The query would look like the below
INSERT INTO originators(originator, id, regexp)
VALUES ('null', 1, -1);

This is the table design:
CREATE TABLE originators
(
      originator    varchar(30)    NOT NULL
    , id            int            NOT NULL
    , regexp        int            NOT NULL
);

Here the originator column has a NOT NULL constraint, so there should be no NULL values in the table.
However, when I query the table, I'm seeing this:
╔════════════╦════╦════════╗
║ originator ║ id ║ regexp ║
╠════════════╬════╬════════╣
║ NULL       ║  1 ║     -1 ║
╚════════════╩════╩════════╝
How is this possible?

'NULL' is not the same as NULL, the first one is an string just like any other string, so your constraint (NOT NULL) won't work.

I think you are working with a programming language (like PHP or Java) that you put your value inside a string with a variable like

INSERT INTO originators(originator, id, regexp)
VALUES ('$variable', $id, $regexp);

so in this case you have to correct your code to prevent the quotes, or if you can't do that you constraint should be CHECK(upper(originator) != 'NULL').


SSRS and PowerBI Report Server on same server I've just installed the August 2018 release of PowerBI Report Server onto the same Win 2012 server as SSRS 2016. I can get it running but it clashes with SSRS with URL reservations.

I've configured them to use different Virtual directories and ports but SSRS keeps breaking.

SSRS: http://ServerName:80/Reports
PBIRS:  http://ServerName:8080/PBIReports

What have I missed?

Edit: I have SSRS 2017 and PBIRS working at the same time on my laptop. Both on Port 80 but with different virtual directories.

I now have this working on my Win-2012 server with PBIRS (August 2018) and SSRS 2016.

First they need to have thier own ReportServer Databases. I used:

SSRS: ReportServer
PBIRS:  PBIReportServer

The Webservice URL's use the same port number but different Virtual directories.

SSRS: http://ServerName:80/ReportServer
PBIRS:  http://ServerName:80/PBIReportServer

And the Webportal again uses the same port number, but different URLS.

SSRS: http://ServerName:80/Reports
PBIRS:  http://ServerName:80/PBIReports

And so far it seems to be behaving. I've restarted the server and both services started automatically and appear to be working.


Script to see running jobs in SQL Server with Job Start Time how to write script to see running jobs in SQL Server with Job Start Time?

SELECT sj.name,
 sja.run_requested_date,
 CONVERT(VARCHAR(12), sja.stop_execution_date-sja.start_execution_date, 114) Duration
FROM msdb.dbo.sysjobactivity sja
INNER JOIN msdb.dbo.sysjobs sj
ON sja.job_id = sj.job_id
WHERE sja.run_requested_date IS NOT NULL
ORDER BY sja.run_requested_date desc;

I posted a query a while back for getting a list of currently running jobs here.

SELECT
    ja.job_id,
    j.name AS job_name,
    ja.start_execution_date,
    ISNULL(last_executed_step_id,0)+1 AS current_executed_step_id,
    Js.step_name
FROM msdb.dbo.sysjobactivity ja
LEFT JOIN msdb.dbo.sysjobhistory jh
    ON ja.job_history_id = jh.instance_id
JOIN msdb.dbo.sysjobs j
ON ja.job_id = j.job_id
JOIN msdb.dbo.sysjobsteps js
    ON ja.job_id = js.job_id
    AND ISNULL(ja.last_executed_step_id,0)+1 = js.step_id
WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)
AND start_execution_date is not null
AND stop_execution_date is null;

This has job name, job id, start time and what step it's running on.  If you follow the link above you can get some more detail on how I came up with it and what else is available.


pg_upgrade fails with lc_ctype cluster values do not match I'm upgrading my PostgreSQL version 9.1.4 database to version 9.2.4. Both the old and the new version are the bundled versions of postgresapp.com for Mac OS X.

When trying to upgrade the database I get this error:

# pg_upgrade -b /tmp/Postgres.app/Contents/MacOS/bin \
-B /Applications/Postgres.app/Contents/MacOS/bin \
-d var-9.1 \
-D var

Performing Consistency Checks
-----------------------------
Checking current, bin, and data directories                 ok
Checking cluster versions                                   ok
Checking database user is a superuser                       ok
Checking for prepared transactions                          ok
Checking for reg* system OID user data types                ok
Checking for contrib/isn with bigint-passing mismatch       ok
Creating catalog dump                                       ok

lc_ctype cluster values do not match:  old "de_DE", new "de_DE.UTF-8"
Failure, exiting

I searched this error message and found no useful tip to fix this. Any idea?

You'll need to re-initdb the new database with LC_CTYPE set to de_DE (i.e. use a non-Unicode encoding); check the old DB's encoding and use the same one when you initdb the new database.

Alternately, use pg_dump and pg_restore to do a dump and reload of the old DB's contents into the new DB. This requires that you have the old DB running.


How secure is a linked server in SQL Server I am in a situation in which I should doubt everyone (even my developer team and database admin) so I want to Log all the logons made to my server.

I have two SQL Servers, say A and B. I want to log all logons using a logon trigger from server A in server B, and it is important that my database be able to insert the log into A, but it should not have delete or update access on B.

I want the log to be made, but I do not trust my sa or other people who have full access on A.

In brief, Server A can insert Logs but can not do anything more than that, and when I want to monitor the logons on A the only possible solution should be doing it on B.

How can I achieve this situation?

I want to log all logons using a logon trigger from server A in server B.

Ideally, if you are using Enterprise edition, then as Shawn mentioned, you can use SQL Server Audit.

You can even set up a light weight server side trace and then define a WMI event notification - TRACE_FILE_CLOSE to automatically load the trace data into a table when it rollsover.

Another option is - you can use the script for Logon Trigger posted by me. Note that it wont work for the existing connections and it a limitation of Logon triggers. All new connections will go through the Logon Trigger.

  it is important that my database be able to insert the log into A, but it should not have delete or update access on B.

You can use transactional replication by just replicating the LOGON Table to server B (in your case). Make sure you choose - Do not replicate DDL changes option.

  when I want to monitor the logons on A the only possible solution should be doing it on B.

You can create a view using Linked server and just give select permission to the view. This way you can control who can access the info.

As a side note, make sure you do not return a full data set in the view, rather just limit it with last 10 -15 days of login audit, so when a user queries the data, it will return a small amount of data only.


How can INDEX rebuilds be going parallel when MAXDOP is set to 1 I am periodically running into a THREADPOOL wait issue with a SharePoint data store of hundreds of databases using a SQL Server 2008R2 Standard Edition instance (recently migrated to an Azure VM). It is running a stored procedure named proc_DefragmentIndices in many (possibly all) of these databases at a time.
The stored procedure rebuilds every index in the database unconditionally. Of course, they are head blockers (because it is Standard Edition, each ALTER INDEX command runs with ONLINE=OFF). Because there are so many of them running at a time (each in a different database), and they are going parallel (which ties up even more workers), everything piles up. Just for extra noise, Azure Backup is taking backups of many of the databases while all this is going on, eating up even more workers. Activity Monitor shows 106 waiting tasks, and multiple instances of the same session Id for many of the ALTER INDEX commands (which is why I say they are going parallel).
What I find puzzling is that these ALTER INDEX statements are going parallel even though MAXDOP is set to 1 in the instance, as is recommended for SharePoint databases, and the ALTER INDEX statements executed by the stored procedure are not using a MAXDOP option to override it.
Q1: How can INDEX rebuilds be going parallel when MAXDOP is set to 1?
Q2: Activity Monitor shows the ALTER INDEX commands but sp_WhoIsActive does not. Does anyone know why?

It is running a stored procedure named proc_DefragmentIndices in many (possibly all) of these databases at a time.
The stored procedure rebuilds every index in the database unconditionally.

I assume you don't have any control of this, but I'd be remiss if I didn't suggest that you make this stop happening as soon as possible   Use Ola Hallengren's script, which will at list rebuild or reorg only at specific fragmentation thresholds.  Or considering avoiding index rebuilds in favor of statistics updates.

How can INDEX rebuilds be going parallel when MAXDOP is set to 1?

They shouldn't be.  And they really shouldn't be because you're using Standard Edition - and parallel index operations are an Enterprise Edition feature.  I wonder if there is just something weird about the way this is showing up in Activity Monitor.

Activity Monitor shows the ALTER INDEX commands but sp_WhoIsActive does not. Does anyone know why?

That's another red flag that something is off here.  Index rebuilds should show up just fine in sp_WhoIsActive.  Here's me rebuilding the Posts table (from the StackOverflow2010 sample database) on my laptop:
USE StackOverflow2010;

ALTER INDEX PK_Posts__Id ON dbo.Posts REBUILD;

As a further point of investigation, you could query sys.dm_os_tasks directly when you see one of these ALTER INDEX sessions that appears to have gone parallel, to be extra sure whether it's serial as it should be, or parallel.


SQL Server 2014 DBCC CHECKDB notification I want to include the results of DBCC CHECKDB in the notification email sent to me. I have seen some techniques in older versions of SQL Server. Is there any new / easier way to due this in SQL Server 2014?

Create a Job Step next to the DBCC CHECKDB Step, and set the Checkdb Step field 'On success action' to 'Go to Next Step'
In the Checkdb Step create an output file.
Now, in second Step use the log file to be sent as an email attachment using T-SQL

exec msdb.dbo.sp_send_dbmail @file_attachments = 'Path\Filename.txt'

This should work fine. I am not aware of any inbuilt solution for this.


Indexing a large static dataset My current situation is this: I have 328 million lines of data in a flat csv file. Not optimal. I want to be able to query this data (I'll explain in more detail). Grep is getting tired. This data is static and will not change.

I'm new to PostgreSQL and am working with this data on my 2010 MacBook Pro, 2,4 GHz Intel Core 2 Duo, 4 GB 1067 MHz DDR3. Storage size is not really a problem, the data is around 65GB on a 1TB disk, but read / write speed is not great (it's not an SSD unfortunately). From what I remember from my CS studies a few years ago, indexing could help me improve query speeds, and I'm reading up on how best to do that.

Why am I using PostgreSQL? Peer pressure. Not really but let's go with that. I have PostgreSQL 9.3.4 running on OSX 10.9.2.

Now, what I'm trying to do. The data includes date and time info and spans about 10 months. I want, for example, to be able to:

Find and summarize all activity on, say, all mondays in the data set for a specific user and/or for multiple users
Look at differences between weeks in the summertime vs weeks in the wintertime
Between months; and
Hours of day, etc.

It's mostly timestamp related summaries that I'm interested in.

Each data record includes, besides the timestamp, a userID and a servID. In another file (which is stored in another table), each servID is linked to a location as X and Y values. It's these locations that I'm interested in displaying on a map, so I would be exporting these to plot using matlab, in aggregate for many users or focusing on specific users at a time.

All values are repeated, i.e. there are about 700.000 unique userIDs and 1500 unique servIDs. Timestamps are not unique either, the resolution is only seconds, so there are repeated timestamps.

So far I've created two tables, one with a small subset of test data and the X-Y location table:

create table test (userID varchar, junk1 varchar,
     date varchar, time varchar, junk2 varchar,
     junk3 varchar, servID varchar, junk4 varchar, junk5 varchar);
copy test from '/Users/path/someuser.csv' delimiter ';' CSV;
create table locations (col1 int, servID_DEC int,
     Col2 varchar, servID_HEX varchar, Locname varchar,
     Locname2 varchar, SERV_LOCY float8, SERV_LOCX float8, junk1 float8, junk2 float8);
copy locations from '/Users/path/locations.csv' delimiter ',' header CSV;

I had to trim the data, one column at a time, which was annoying, but I repeated this for each value:

update test set userID=trim(userID);

Then I added a column for the actual timestamps I had to construct:

alter table test add column timestamp timestamptz;
update test set timestamp=to_timestamp(date || '-' || time, 'DD.MM.YYYY-HH24:MI:SS');

Now my question (finally! Sorry about that): How do I create an index for the data in Postgres such that a query similar to this would be fast:

select userID, date, time, servID, timestamp, servID_HEX, SERV_LOCY, SERV_LOCX
     from test, locations
     where servID=servID_HEX
     and userID='<someusers>'
     and extract(dow from timestamp)=2;

I would also like to be able to quickly summarize the frequency of the servIDs used by a userID over a specified timeframe, i.e. he uses servID=1 1000 times, servID=2 600 times, so on (similar to the tabulate command in matlab)

(I renamed some of the variables and path names for reasons, I hope I didn't mess that up).

Query cleanup

First, lets rewrite your query to be readable, by using table aliases, qualifying field names, and using an ANSI join:

select t.userID, t.date, t.time, t.servID, t.timestamp,
       l.servID_HEX, l.SERV_LOCY, l.SERV_LOCX
from test t
     inner join locations l on (t.servID=l.servID_HEX)
where t.userID='<someusers>'
  and extract(dow from t.timestamp)=2;

What the query needs

To start breaking this problem down you'll want to do is look at the fields used and where they're used:

Tables: locations, test

Fields output: test.userID, test.date, test.time, test.servID, test.timestamp, locations.servID_HEX, locations.SERV_LOCY, locations.SERV_LOCX

Terms used in filter predicates: test.servID, locations.servID_HEX, test.userID, extract(dow from test.timestamp)=2

Indexes

Now, the most important thing is to make sure that you have indexes that target high-selectivity columns used in predicates.

"high selectivity" just means that there aren't tons of the same value - there's no point indexing a column if 50% of the time it's 'a' and 50% of the time it's 'b', because you just don't gain much benefit from the index.

Assuming that all these columns have lots of well distributed distinct values, so they're highly selective and don't have any values that're massively common, you'll want to create an ordinary b-tree index. That's enough for locations because there's just one value used in a predicate:

CREATE INDEX locations_servid_idx ON locations(servID_HEX);

For test it's more complex.

You could create indexes separately for each column, but it's more efficient to create a single composite index that has all the columns you're using in this query's predicates for a given table. So you want servId, userID and timestamp. However, you don't use timestamp directly - you use it to get the day of week, and an index on timestamp can't be used to look up extract(dow from timestamp).

This is where expression indexes come in.

Indexes don't just cover columns. They can also cover arbitrary expressions. In this case, we'll create an index that includes extract(dow from timestamp).

So the index for test will look like:

CREATE INDEX test_custom_idx ON test (
    servID,
    extract(dow from timestamp),
    userID
);

As you'll be doing different kinds of timestamp based queries I'd consider creating multiple indexes for different timestamp expressions.

Which order to put the columns in depends on how selective each column is. Even though I'm sure the day-of-week will be more selective I've put the server ID first because it's used in a join condition that lets us completely disregard rows of the other table.

Since you didn't provide sample tables or sample data I can't really test it and I'm not keen on dummying some up. Adjustments may be required.

Partial indexes

Partial indexes can also be a big win, where the index only contains data for a subset of the data, e.g.:

CREATE INDEX test_custom_idx ON test (
    servID, userID
) WHERE (extract(dow from timestamp) = 2);

contains only data for that day, so it's a lot faster to search for queries involving just that day, but cannot be used at all for queries that might involve different days, or for which the day is determined at runtime from the output of another part of the query.

Index-only scans and covering indexes

Another thing you can consider is that all those unused columns still have to be read from disk to fetch your rows. In PostgreSQL 9.2 and newer there's a way to avoid that using an index-only scan if all columns you are fetching are in an index.

So you could create a covering index, where you include columns that aren't really required for the actual search but are there so they can be output without reading from the main table. The first thing to do for that is to get rid of useless values in your SELECT list, so we'll delete the redundant t.date and t.time from your query. Then create two new indexes:

CREATE INDEX locations_covering_idx
ON locations(
    servID_HEX,
    SERV_LOCY,
    SERV_LOCX
);

CREATE INDEX test_covering_idx ON test (
    servID,
    extract(dow from timestamp),
    userID,
    timestamp
);

Reducing repetition

Since your dataset is static, having tons and tons of indexes isn't a big problem.

Each index has a cost for insert/update/delete. So for non-static data you want to minimize that by getting rid of indexes that don't provide enough improvement to be worth the cost.

One way to do that can be to split up composite indexes. In your case for example I might take the timestamp stuff out of the main index on location and put it in a bunch of new indexes instead. That way all the different timestamp expression indexes can be updated without rewriting a whole lot of unrelated data for userid and serverid as well. PostgreSQL can still often use the indexes together, combining them with a bitmap index scan.

Not a concern for your current dataset, but worth learning for later.

Data cleanup

This is a static data set. So if you don't need a column, drop it. Or better yet, create a new table with just the subset of data you need, pre-joined, using something like:

CREATE TABLE reporting AS
select t.userID, t.servID, t.timestamp, l.SERV_LOCY, l.SERV_LOCX
from test t
     inner join locations l on (t.servID=l.servID_HEX)
ORDER BY t.userID, t.servID, t.timestamp;

which creates a new table that has the data pre-joined and sorted for the most useful grouping of access.

You can then write much simpler queries that'll be much, much faster, against the new table, and your indexes will be a lot simpler too.


SQL Difference Optimistic Reading and Optimistic Writing What is the difference between optimistic read and optimistic write?

Or probably more so, can someone define these 4?

(a) Optimistic Reading

(b) Pessimistic Reading

(c) Optimistic Writing

(d) Pessimistic Writing

Referring to question and answer here:
https://stackoverflow.com/questions/2741016/read-committed-snapshot-vs-snapshot-isolation-level

'READ COMMITTED SNAPSHOT does optimistic reads and pessimistic writes. In contrast, SNAPSHOT does optimistic reads and optimistic writes.

Or probably more so, can someone define these 4?

No.  Those are not terms with precise definitions.  "Optimistic Concurrency" means to make some change even though you might not be able to save or commit the change later.  And "Pessimistic Concurrency" means to acquire locks before you make the change to ensure you will be able to save/commit it.

In SQL Server no isolation level uses exclusive locking when reading data (though you can force it with a lock hint).

Both RCSI and SNAPSHOT use Pessimistic Concurrency.  You will always acquire the X locks needed to change and commit a row before actually making the change.

Client-Side libraries often use Optimistic Concurrency, reading data, changing it in memory, and then, on save, checking if those changes would conflict with another sessions changes.  If the changes are found to conflict, an error is thrown.  See eg Optimistic Concurrency

In the SQL Server Database Engine the only Optimistic Concurrency is the model introduced by Memory-Optimized Tables, which don't require locks to write data, and instead use a first-writer-wins Optimistic Concurrency model, described here.


Is there a way to recover a dropped mysql database? I accidentally dropped a MySQL database on my server.  Are there any ways to recover a dropped database?

If you act fast there is a high chance to get your database back. The chance is higher for InnoDB, for MyISAM it's non-zero, but close.

The matter is when MySQL executes DROP TABLE or DROP DATABASE (which is essentially the same) InnoDB doesn't wipe the data out. Pages with the data are still on disk.

Depending on innodb_file_per_table setting the recovery process differs. If innodb_file_per_table is OFF (default up until 5.5) then the dropped table remains in ibdata1. If innodb_file_per_table is ON (default as of 5.5) then the dropped table was in the respective .ibd file. MySQL removes this file when drops the table.

The very first thing to do is to stop any possible writes so your table isn't overwritten. If innodb_file_per_table is OFF it's enough to stop MySQL (kill -9 is even better, but make sure you kill safe_mysqld first). If innodb_file_per_table is ON then umount partition where MySQL stores its data. If the datadir is on the root partition I recommend to shut down server or at least take an image of the disk. Let me repeat, the goal is to prevent overwriting dropped table by MySQL or operating system.

There is a tool that allows to work with InnoDB pages at low level,
TwinDB data recovery toolkit. I will use it to illustrate undrop recovery.

You need to take the media with dropped table (either ibdata1 or disk image) and find InnoDB pages on it. stream_parser tool from the toolkit does it.

./stream_parser -f /path/to/disk/image

It will scan the file, find the InnoDB pages and sort them by type and index_id. index_id is an identifier that InnoDB uses to refer to an index. A table is stored in index PRIMARY. To find what index_id is your dropped table you need to recover InnoDB dictionary.

The InnoDB dictionary is stored in ibdat1 file. You need to scan ibdata1 file the same way as above:

./stream_parser -f /var/lib/mysql/ibdata1

Now you need to get records from the InnoDB dictionary tables SYS_TABLES and SYS_INDEXES(let's say your table is sakila.actor):

./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page -t dictionary/SYS_TABLES.sql | grep sakila/actor
000000000B28  2A000001430D4D  SYS_TABLES  "sakila/actor"  158  4  1 0   0   ""  0

158 is table_id, remember it.

./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page -t dictionary/SYS_INDEXES.sql | grep 158
000000000B28    2A000001430BCA  SYS_INDEXES     158     376     "PRIMARY"       1       3       0       4294967295
000000000B28    2A000001430C3C  SYS_INDEXES     158     377     "idx\_actor\_last\_name"        1       0       0       4294967295

So, index_id of your dropped table(sakila.actor) is 376.

Now you can fetch records of the dropped table from InnoDB index_id 376.
You need to have the table structure of the dropped table, exactly CREATE TABLE statement which the table was created with. Where you can get it? Either from old backup, or from elsewhere. It's also possible to recover the structure from the InnoDB dictionary, but I won't cover it in this answer. Let's just assume you have it.

./c_parser -6f pages-ibdata1/FIL_PAGE_INDEX/0000000000000376.page -t actor.sql > dump.tsv 2> load_cmd.sql

c_parser outputs records as tab-separated dump to stdout. The dump can be loaded with LOAD DATA command. c_parser prints it to stderr.

See more details in posts:

Recover after DROP TABLE, innodb_file_per_table is ON
Recover after DROP TABLE, innodb_file_per_table is OFF
Recover InnoDB dictionary


Separating a date column in Access into Month, Day and Year while keeping the new columns ordered by a seperate column(primary key) I have a column titled DateOpen which lists the date files were opened in d/m/yyyy format. I want to split the entries in that column into new columns titled MonthOpen, DayOpen and YearOpen while keeping the fields ordered by the primary key column titled FileNumber.  Thanks in advance.

Select FileNumber,
    DateOpen as DayOpen,
    Month(#DateOpen#) as MonthOpen,
    Year(#DateOpen#) as YearOpen
From [file]
Order by FileNumber

I assume by day you just want to show the date you have from the day open field. Month(DateOpen) will give you the month number.  You weren't specific about what you were looking for in this field, so I assume this is ok. Order By FileNumber ensures you maintain the order of the primary key field.


Optimization for table with text and time columns I have a simple table with the following columns:

id (int4)
time (timestamp)
name (varchar)
service_name (varchar)

For every service (there are not more than 500 different names), there are a lot of entries. The status varies, but basically only 'ok' is interesting.

I tried to optimize this query:

SELECT service_name, max(id) AS max_id
FROM mytable
where time >= '2019-07-23'
   and status = 'ok'
   GROUP BY service_name;

There exists already an index on the time column. I tried to add another one using the statement:

create index my_index on my_table(time desc, service_name, status);

But that didn't help. PostgreSQL (9.6) doesn't use this index. As I can see the database management system is doing an index scan that consumes most of the time.

Any Idea how I could improve the performance? Thanks a lot!
Right now the query needs more than four seconds. That is a bit too much.

PS

Some numbers: There are currently more than 500 million rows in the table.

PPS:
Added execution plan

HashAggregate  (cost=221449.30..221453.55 rows=425 width=24) (actual time=21852.251..21852.330 rows=409 loops=1)
  Group Key: service_name
  Buffers: shared hit=4202089 read=70842
  ->  Index Only Scan using my_table_time_service_name on my_table hh  (cost=0.70..187312.22 rows=6827415 width=24) (actual time=0.044..13948.546 rows=11192087 loops=1)
        Index Cond: ("time" >= '2019-07-23 20:58:52.886332'::timestamp without time zone)
        Heap Fetches: 4139020
        Buffers: shared hit=4202089 read=70842
Planning time: 0.233 ms
Execution time: 21852.411 ms

If the only reason for the index is the above query, then a partial index would better:

CREATE INDEX my_index ON my_table(time desc, service_name) WHERE status='ok';

VACUUM ANALYZE my_table; may help too.

The query plan depends on actual data in the table. In a table with few or zero records a seq scan is cheaper.

If you really dont want the seq scan (and nothin else helps), try to turn off seqscans with SET enable_seqscan = off; before the query.


Log Shipping between 2 AGs I'm having one AG with two nodes (active-passive) in one data center and another AG with two nodes (active-passive) in a different data center.

I need to have log shipping enabled between these two setups, so that a DR situation can be handled.

Most articles are advising to have 1 node in DR. however, for HA, i need to have a AG in DR.

So, with this config, can log shipping be achieved? can you please guide on how to achieve that as well?

Thanks

I believe, log-shipping would not work in your case (AG1 ot AG2), due to following reasons.

Database cannot be restored while it's already part of AG/Mirroring
Basically, Log-shipping does backups on primary replica, and restores on secondary replica with defined interval time

Log-Shipping would be good choice when Secondary replica in Log shipping topology is standalone and same version as primary SQL Instance. i.e. Your Data-center-1 AG act as primary replica and Standalone Instance at Data-center-2 act as secondary replica.

While you implementing Log-shipping for a Database that is already part of AG, you need to ensure that the Backup preference cannot be set to secondary within AG as the log-shipping cannot work with secondary backup copies from AG. In your case AG of Data-center-1.

Since you already have Availability Groups in-place with SQL 2016 in two sub-nets, distributed AG might be correct solution.

You may follow these reference in either case:
Distributed Availability Groups
Log-shipping from AG Database


Recommended Permissions for SQL Account that will be creating new SQL Accounts Seeking some recommendations on how to handle a SQL account, that will be creating additional SQL accounts in the future...

Scenario:

Our Admin production app connects to SQL using an account called: Admin_Main, as stated in the connection string. When somebody creates a new user in the Admin app and assigns them a Reporting license, a new SQL Account is created on the instance, for the new user. The Admin_Main account is used to create this account.

I, of course, do not want to add more permissions to the Admin_Main account than needed, so my question is;

What are the permissions that I should add to this account, so that it's able to create new SQL Server accounts, but doesn't have Admin permissions on the instance?

Thanks in advance!

You could assign the server role securityadmin to the Admin_Main account.

The securityadmin server role is documented as:

  Members of the securityadmin fixed server role manage logins and their properties. They can GRANT, DENY, and REVOKE server-level permissions. They can also GRANT, DENY, and REVOKE database-level permissions if they have access to a database. Additionally, they can reset passwords for SQL Server logins.

  IMPORTANT: The ability to grant access to the Database Engine and to configure user permissions allows the security admin to assign most server permissions. The securityadmin role should be treated as equivalent to the sysadmin role.

Reference: Server-Level Roles (Microsoft | Docs)

Let's give that a try and see how far we can get...

Creating SQL Server Login Admin_Main

We'll create a new SQL Server Login and assign that user the securityadmin server role:

USE [master]
GO
CREATE LOGIN [Admin_Main] WITH PASSWORD=N'MyShineyNewP@sswerd', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
ALTER SERVER ROLE [securityadmin] ADD MEMBER [Admin_Main]
GO

This is all done with an account that has the sysadmin server role and the result is:

Command(s) completed successfully.

Login With Admin_Main

You can then open up a new database query window using the Admin_Main SQL Serer Login and the corresponding password. Once you have done that...

Assigning Higher Privileges

...your new user is certainly going to try and increase his/her privileges by adding himself/herself to the sysadmin server role:

ALTER SERVER ROLE [sysadmin] ADD member [Admin_Main]

This results in the following:

Msg 15151, Level 16, State 1, Line 3
Cannot alter the server role 'sysadmin', because it does not exist or you do not have permission.

So the SQL Server Login can't assign himself higher privileges.

Creating a New User (Or: How Far Can I Get?)

Seeing as the Admin_Main is now allowed to create new SQL Server Logins, let's create a new user while still logged in as Admin_Main and add some server level roles. We'll do this in multiple steps to find out when assigning SQL Server roles will fail:

Create Login

Input

USE [master]
GO
CREATE LOGIN [New_User] WITH PASSWORD=N'MyShineyNewP@sswerd', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO

Output

Command(s) completed successfully.

Assign securityadmin Role

Input

ALTER SERVER ROLE [securityadmin] ADD MEMBER [New_User]
GO

Output

Command(s) completed successfully.

Assign sysadmin SQL Server Role

Input

ALTER SERVER ROLE [sysadmin] ADD MEMBER [New_User]
GO

Output

Msg 15151, Level 16, State 1, Line 13
Cannot alter the server role 'sysadmin', because it does not exist or you do not have permission.

Conclusion

You will have to assign the SQL Server role securityadmin to the Admin_Main SQL Server Login to allow this account to create new SQL Server Logins and/or Windows Authenticated SQL Server Logins.

...and you can't assign permissions/privileges to other accounts that your initial accounts doesn't already possess.


Your update statement, is not covering all possibilities.
See record id 6 and 22 ; these records are not marked.

To have an efficient query , you need also some indexes on your table. One that starts with column GroupID ...

-- preparation part

create table dbo.tblSource
(UniqueID int not null
,GroupID int not null
,Item1 char(1) not null
,Item2 char(1) null
,Result char(1) null
,Flag char(1) null
,FlagRes char(1) null)

insert into dbo.tblSource(UniqueID,GroupID,Item1,Item2,Result)
 select 1 , 1, 'x',NULL, '' UNION ALL
 select 2 , 1, 'b','c', 'Y' UNION ALL
 select 3 , 1, 'c','d', 'Y' UNION ALL
 select 4 , 1, 'd',NULL, 'Y' UNION ALL
 select 5 , 1, 'e','d', 'Y' UNION ALL
 select 6 , 1, 'e',NULL, 'Y' UNION ALL
 select 7 , 1, 'e','c', 'Y' UNION ALL
 select 8 , 2, 'a',NULL, '' UNION ALL
 select 9 , 2, 'b',NULL, '' UNION ALL
 select 10 , 3, 'x',NULL, '' UNION ALL
 select 11 , 3, 'y',NULL, '' UNION ALL
 select 12 , 3, 'a','b', 'Y' UNION ALL
 select 13 , 3, 'b',NULL, 'Y' UNION ALL
 select 14 , 4, 'x',NULL, '' UNION ALL
 select 15 , 4, 'y',NULL, '' UNION ALL
 select 16 , 4, 'x',NULL, '' UNION ALL
 select 17 , 4, 'c',NULL, 'Y' UNION ALL
 select 18 , 4, 'd',NULL, 'Y' UNION ALL
 select 19 , 4, 'd','c', 'Y' UNION ALL
 select 20 , 5, 'a','c', 'Y' UNION ALL
 select 21 , 5, 'b','c', 'Y' UNION ALL
 select 22 , 5, 'b',NULL, 'Y' UNION ALL
 select 23 , 5, 'c',NULL, 'Y'

your's updates

Update a SET a.Flag = b.Item1 from tblSource a inner join tblSource b on a.[GroupID] = b.[GroupID] where (a.Item1 = b.Item2 or b.item1 = a.Item2)

Update a SET a.Flag = b.Item1 from tblSource a inner join tblSource b on a.[GroupID] = b.[GroupID] and (a.Item1 = b.Flag or a.item2 = b.Flag)
where a.Flag is null

this is what I come with:

update s1
set flagRes = case when exists(select 1 from dbo.tblSource as s2
                           where s2.GroupID  = s1.GroupID
                             and s2.UniqueID <> s1.UniqueID
                             and
                             (
                             (s1.item2 is null and s1.item1 = s2.item1 and s2.item2 is not null)
                             or
                             (s1.item2 is null and s1.item1 = s2.item2)
                             or
                             (s1.item2 is not null and (s1.item2 = s2.item1 or s1.item2 = s2.item2))
                             )

                 ) then 'y'else '' end
from tblSource as s1

select of it, with the output:

select UniqueID,GroupID,Item1,Item2,Result as yourResult
,case when exists(select 1 from dbo.tblSource as s2
                           where s2.GroupID  = s1.GroupID
                             and s2.UniqueID <> s1.UniqueID
                             and
                             (
                             (s1.item2 is null and s1.item1 = s2.item1 and s2.item2 is not null)
                             or
                             (s1.item2 is null and s1.item1 = s2.item2)
                             or
                             (s1.item2 is not null and (s1.item2 = s2.item1 or s1.item2 = s2.item2))
                             )

                 ) then 'y'else '' end as queryRes
        ,flag
        ,flagRes
from dbo.tblSource as S1

UniqueID    GroupID Item1   Item2   yourResult  queryRes    flag    flagRes
1           1       x       null                            null    null
2           1       b       c       Y           y           c       y
3           1       c       d       Y           y           b       y
4           1       d       null    Y           y           c       y
5           1       e       d       Y           y           d       y
6           1       e       null    Y           y           null    y
7           1       e       c       Y           y           c       y
8           2       a       null                            null    null
9           2       b       null                            null    null
10          3       x       null                            null    null
11          3       y       null                            null    null
12          3       a       b       Y           y           b       y
13          3       b       null    Y           y           a       y
14          4       x       null                            null    null
15          4       y       null                            null    null
16          4       x       null                            null    null
17          4       c       null    Y           y           d       y
18          4       d       null    Y           y           c       y
19          4       d       c       Y           y           c       y
20          5       a       c       Y           y           c       y
21          5       b       c       Y           y           c       y
22          5       b       null    Y           y           null    y
23          5       c       null    Y           y           a       y

dbfiddle here


Verify mysqldump backup is corruption free We've got a cron task backing up our Database using mysqldump - my main concern is corruption, short of manually importing and checking the backups each time whats the best way to check / verify a backup is corruption free ?

Background: We are running mysql 5.5.* and InnoDB. We are running mysqldump straight from the server.  We are not currently using master / slave or master / master but could change if it would help. The DB is small currently <1mb, it will be <50mb for the foreseeable future, so scale dosnt really need to be taken into account in this instance.

There are two things you want to consider

BACKUP PROCESS

There really is no substitute of doing a restore. This is just a replay of a mysqldump. If you need to do PITR, you may need to run mysqlbinlog against applicable binlogs and play the events up to the date and time needed.

You should do this periodically in case the disk where the mysqldump is located goes bad or if the mysqldump contains a sequence of characters in a BLOB making it impossible to restore.

BACKUP INTEGRITY

You should be able to start mysqld without a problem. Once you have mysqld running, you need to test each table's usability. You can do that by running this query:

SELECT CONCAT('CHECK TABLE ',dbtb,';') FROM
(SELECT CONCAT(table_schema,'.',table_name) dbtb FROM
information_schema.tables WHERE table_schema NOT IN
('information_schema','performance_schema','mysql')) A;

This will create a CHECK TABLE command for every MyISAM, InnoDB, ARCHIVE and CSV table to check their integrity. You can output this to a script and execute the script. The CHECK TABLE command can also allow you to check all the tables as a single command. You can change the query to collect the table names as a comma-separated list and prepend:

SELECT CONCAT('CHECK TABLE ',dbtblist,';') FROM
(SELECT GROUP_CONCAT(table_schema,'.',table_name) dbtblist FROM
information_schema.tables WHERE table_schema NOT IN
('information_schema','performance_schema','mysql')) A;

WARNING: The GROUP_CONCAT() function has a default limit of 1024 characters. If you have a modest number of tables, you will not list all the tables, and the last table listed will be cut off, causing an error.  You can preface your command with SET SESSION group_concat_max_len = 10000; to increase the length of the return.

Here is a sample from MySQL 5.6.16 (Windows) on my laptop:

mysql> SELECT CONCAT('CHECK TABLE ',dbtblist,';') FROM
    -> (SELECT GROUP_CONCAT(table_schema,'.',table_name) dbtblist FROM
    -> information_schema.tables WHERE table_schema NOT IN
    -> ('information_schema','performance_schema','mysql')) A;
+----------------------------------------------------------------------------+
| CONCAT('CHECK TABLE ',dbtblist,';')                                        |
+----------------------------------------------------------------------------+
| CHECK TABLE ayman.articles,ayman.topics,test.nuoji,test.prod,test.prodcat; |
+----------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql>

Then, store that output to a variable and execute from the command line. Note that we have temporarily increased the group_concat_max_len before running the command.

MYSQL_USER=root
MYSQL_PASS=rootpass
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SET SESSION group_concat_max_len = 1048576;"
SQL="${SQL} SELECT CONCAT('CHECK TABLE ',dbtblist,';') FROM"
SQL="${SQL} (SELECT GROUP_CONCAT(table_schema,'.',table_name) dbtblist FROM"
SQL="${SQL} information_schema.tables WHERE table_schema NOT IN"
SQL="${SQL} ('information_schema','performance_schema','mysql')) A"
mysql ${MYSQL_CONN} -ANe"${SQL}" | mysql ${MYSQL_CONN}

Give it a Try !!!


Remove multiple joins to same table with where I am stucked at below situation. Need expert advice.
I have 3 tables, login_details,deleted_login_details and custom_fields as below.
login_details structure = deleted_login_details
custom-fields have : user_id,custom_name,custom_value

I want following result.
I want only those users who fulfilled all where conditions with multiple joins on custom_fields table. Example

select user_id from login_details union deleted-login_details  as login_table
join
custom_fields as f1 on f1.user_id = login_table.user_id
join
custom_fields as f2 on f2.user_id = login_table.user_id
where
f1.custom_name = '1' and f1.custom_value in (1,2,3)
and
f2.custom_name = '2' and f2.custom_value in (4,5,6)

So only those records should come who have custom_name =1 and value as above and custom_name=2 and value in above.
But I want this using only 1 join to custom_fields table.

Like as

So only 1 ,2 should get and no 3 as 3 dont have custom_name =1 record

Any advice?
Thanks in advance.

Append to your query GROUP BY user_id. This has the effect that multiple user_ids become one row entry.

Additionally append HAVING COUNT(user_id)=2. As you've GROUP BY user_id the count of them represents that the number of field matches.


How can I enable logging for a single function in PostgreSQL? I want to log the whole function runtime for a single particular function in PostgreSQL? Is this possible?

The value of log_min_duration_statement = 3000 in postgresql.conf and I've tried to do:
ALTER FUNCTION mychema.myfunc(json) SET log_min_duration_statement = 100, however I don't see any logs in /var/log/postgresql/postgresql-9.x-main.log

If the the log_min_duration_statement of the function is less than the log_min_duration_statement in the postgresql.conf does the function runtime get logged?

Can I add an exception somewhere to tell PostgreSQL that I want my function to get logged regardless if the global log_min_duration_statement value is < the log_min_duration_statement of the function?

I want the log_min_duration_statement of the function to take precedence over the global config value to see logs for that one function I'm interested in, not all the others, because enabling logging for all requests adds a significant overhead.

I tried the approach you described and it did not work for me, too.  As log_min_duration_statement can be set only by a superuser, I also tried defining the function with SECURITY DEFINER, logged in as a superuser - to no avail.

My only idea left (for pl/pgsql functions only) is to save clock_timestamp() into a variable as the very first statement in the BEGIN block, then do a RAISE LOG as the last one (before the RETURN statement, if there is any), with the difference of the current clock_timestsmp() and the saved one.

To my understanding, the result of this may be somewhat smaller than the time logged by the normal logging - the time spent on entering and exiting the function will not be taken into account for sure.

(Note that with some helper functions you could also do this in SQL functions.)


ORA-01830: date format picture ends before converting entire input string I am trying to run the query on an Oracle database

select
  to_date(trunc(sysdate) || '13:00:00','DD-MON-YY HH24:MI:SS')
from dual;

and get the error

ORA-01830: date format picture ends before converting entire input string

Please suggest.

SQL> alter session set nls_Date_format = 'DD-MON-YY HH24:MI:SS';

Session altered.

SQL> select trunc(sysdate, 'DD') + 13/24 from dual;

TRUNC(SYSDATE,'DD'
------------------
29-AUG-15 13:00:00


AWS MySQL RDS instance becomes unresponsive and getting restarted automatically We have a AWS MySQL RDS instance which is about 1.7T in size. Sometimes it becomes unresponsive and no operations can be performed.

CPU utilization, Write IOPS, read IOPS, queue depth, write throughput, write latency and read latency drops to zero.
Number of connections get piled up.
"Show engine innodb status" hangs
Lot of queries (around 25 for each) by rdsadmin which are in hang state.

SELECT count(*) from mysql.rds_replication_status WHERE action = 'reset slave' and master_host is NULL and master_port is NULL GROUP BY action_timestamp,called_by_user,action,mysql_version,master_host,master_port ORDER BY action_timestamp LIMIT 1;

SELECT NAME, VALUE FROM mysql.rds_configuration;

After sometime, instance gets rebooted automatically with following error.

MySQL restart initiated to address MySQL induced log backup issues. Note that as part of this resulution, a DB Snapshot will be performed after MySQL completes restarting.

We were able to resolve this issue by upgrading the instances to 5.6.34.


Mimicking cascading foreign key referencing system table I'd like to create a table to store some additional metadata about tables, but I'm running into a number of roadblocks since foreign keys and triggers cannot be used with system tables.

On INSERT it seems easy enough to create a trigger on the metadata table that checks for a matching row in pg_class, but I'm struggling to come up with a way to delete the row when the table is dropped.

I've looked into event triggers, but I can't find a way to determine the oid of the dropped table. Based on the information on the event trigger page and here https://www.postgresql.org/docs/current/plpgsql-trigger.html#PLPGSQL-EVENT-TRIGGER it doesn't seem like there's any data provided about the object that the event affects.

Is there any way of ensuring maintained integrity between my metadata table and the referenced rows in pg_class?

edit:
I found https://www.postgresql.org/docs/current/functions-event-triggers.html so it looks like I should be able to get the oid from an event trigger.

Here's what I ended up using

CREATE OR REPLACE FUNCTION delete_table_metadata()
RETURNS event_trigger AS $$
DECLARE
    table_oid oid;
    schema_nm varchar;
BEGIN
    FOR table_oid, schema_nm IN SELECT objid, schema_name FROM pg_event_trigger_dropped_objects()
    LOOP
        IF schema_nm = 'myschema' THEN
            DELETE FROM table_meta WHERE table_meta_id = table_oid;
        END IF;
    END LOOP;
END
$$
LANGUAGE plpgsql;

People seem to be curious why I want to do this.  I have a need for dynamically generated tables and associated application data models.  The metadata I need to store isn't actually about the tables per se, it's for the application models.  Since the models aren't hardcoded I need a way to persistently store some information about them. There's an argument to be made that I should instead control the creation and dropping of this data from the application rather than the database, but this is more foolproof and I can foresee times where the tables might be manipulated directly from the database rather than the application during admin work.

If you are just looking for a way to get ids of the droped table, maybe this code will help you out :

CREATE OR REPLACE FUNCTION master.ddl_trigger_drop_table_fct()
  RETURNS event_trigger
 LANGUAGE plpgsql
   AS $$
DECLARE
    all_variables character varying;
    r RECORD;
    schema_name varchar (250);
object_type varchar (100);
BEGIN
    FOR r IN SELECT * FROM pg_event_trigger_dropped_objects () LOOP
        if r.schema_name = 'master' and r.object_type = 'table'
         then
            schema_name := r.schema_name;
            object_type := r.object_type;
            all_variables := concat(
                'classid: ', r.classid, chr(10)
                , 'objid: ', r.objid, chr(10)
                , 'objsubid: ', r.objsubid, chr(10)
                , 'original: ', r.original, chr(10)
                , 'normal: ', r.normal, chr(10)
                , 'is_temporary: ', r.is_temporary, chr(10)
                , 'object_type: ', r.object_type, chr(10)
                , 'schema_name: ', r.schema_name, chr(10)
                , 'object_name: ', r.object_name, chr(10)
                , 'object_identity: ', r.object_identity, chr(10)
                , 'address_names: ', r.schema_name, chr(10)
                , 'address_args: ', r.address_args);

            RAISE NOTICE '%', all_variables;
        end if;
    END LOOP;

    ... [do some stuff] ...
END;
$$;

and after you have adjusted this code to fit your needs, you just need to bind your function too your event trigger :

CREATE EVENT TRIGGER ddl_trigger_drop_table ON sql_drop WHEN TAG IN ('DROP TABLE')
   EXECUTE FUNCTION [schema_name].ddl_trigger_drop_table_fct();

But same as others : why do you want to do this might interests me a bit ;)