Can I tell MySQL what to cache? Pretty straightforward question:

Let's say I have a specific query that I know is regularly used, can I tell MySQL to always cache it "automatically" ... i.e. once the cached query results are flushed the mysqld immediately runs that query again to have the cached data ready for the next user?

Or the other way round: can I tell MySQL to not cache specific queries?
What about other caching options, not necessarily related to queries?

I searched through the web on this but can't really find anything related.

MySQL Documentation has SQL_NO_CACHE option:

  Two query cache-related options may be specified in SELECT statements:

  SQL_CACHE

  The query result is cached if it is cacheable and the value of the
  query_cache_type system variable is ON or DEMAND.

  SQL_NO_CACHE

  The server does not use the query cache. It neither checks the query
  cache to see whether the result is already cached, nor does it cache
  the query result. (Due to a limitation in the parser, a space
  character must precede and follow the SQL_NO_CACHE keyword; a nonspace
  such as a newline causes the server to check the query cache to see
  whether the result is already cached.)

  Examples:

SELECT SQL_CACHE id, name FROM customer;
SELECT SQL_NO_CACHE id, name FROM customer;

You can set SQL_NO_CACHE as the default for your session by setting query_cache_type

SET query_cache_type = 'DEMAND';

or

SET query_cache_type = 2;

Afterwards, you must say SELECT SQL_CACHE to make it cache the SELECT


SQLite Data Types In Context of Disk Space I would like to better understand which data types in SQLite take up more disk space. I've looked through official documentation but could not find anything in context of storage space.

Any guide link or a quick explanation will do.

I searched for "SQLite storage space" and this was the second link:

https://www.sqlite.org/fileformat2.html

All the information you could possibly want there.

You cannot exactly work out how much storage space a given data set will use because it depends on how it was created (deletes tend to cause fragmentation for example, which means more space than would be optimal). It does explain the row format though.

SQLite is not strongly typed though. So getting a row size is out of the question. Also most databases created in the last 30 years don't use a packed row format.


No Data Replication in Progress mongodb Setup Information

One ConfigServer

mongod.exe --configsvr --dbpath C:\MongoDBCluster\ConfigSrv

--Mongos Server--

mongos.exe --configdb 10.133.1.130)

2 Shard Servers

mongod.exe --shardsvr --dbpath C:\MongoDBcluster\Server2\configdb --port 27020

mongod.exe --shardsvr --dbpath C:\MongoDBcluster\Server3\configdb --port 27021

Configured Sharding using mongos

mongos> sh.status()
--- Sharding Status ---
  sharding version: {
        "_id" : 1,
        "minCompatibleVersion" : 5,
        "currentVersion" : 6,
        "clusterId" : ObjectId("55dec7d254984cdf812cdf55")
}
  shards:
        {  "_id" : "shard0000",  "host" : "Server1:27020" }
        {  "_id" : "shard0001",  "host" : "Server2:27021" }
  balancer:
        Currently enabled:  yes
        Currently running:  no
        Failed balancer rounds in last 5 attempts:  0
        Migration Results for the last 24 hours:
                No recent migrations
  databases:
        {  "_id" : "admin",  "partitioned" : false,  "primary" : "config" }
        {  "_id" : "demo1",  "partitioned" : true,  "primary" : "shard0001" }
                demo1.demo
                        shard key: { "_id" : 1 }
                        chunks:
                                shard0001       1
                        { "_id" : { "$minKey" : 1 } } -->> { "_id" : { "$maxKey" : 1 } } on : shard0001 Timestamp(1, 0)

I have 2 problems

When i try to check the sharding status from demo1 db on server1 i get below error

  use demo1
  switched to db demo1
  sh.status()
  printShardingStatus: this db does not have sharding enabled. be sure you are connecting to a mongos from the shell and not to a mongod.

I have inserted couple of records which did not get replicated to server2. what am i doing wrong?

sh.status() can only be executed on your mongos. As you posted your "sharding status" with sh.status() in your question yourself. You can't do that on the separate shards.
Sharding is for distributing your documents. For example documents ABC on shard0000 and documents DEF on shard0001. So Sharding is not Replication. If you want to replicate your documents then you need to look at 'How to setup a mongodb replica set' : http://docs.mongodb.org/master/tutorial/deploy-replica-set/

Update :  a full answer you can find here : Difference between Sharding And Replication on MongoDB


Can you restore log file backups to a mirror database after failure? Purely a theoretical question as I've been considering possible DR scenarios...

In this scenario:

The link between a principle and a mirror server becomes unavailable for a period of time (e.g. network outage)
Incremental log backups are still being taken during this time
Then the principle database fails and is unrecoverable

Is it possible to restore the incremental log backups onto the mirror to bring it up to date and back online, or would you need to start from scratch with a restore of a full backup?

Depending on the size of the database the difference could be hours of downtime so it would be nice to know.  I'm predominantly interested in SQL Server 2008 R2 and 2012 but other people may be interested in other versions if there's a difference.

Short answer, yes.

Longer answer, you'd need to remove database mirroring without bringing the database online.  Then it's just a database in NORECOVERY so you can then restore transaction logs to it.  Then bring it online and use it as normal.


Improve performance of transactional replication I 'm testing implementing transactional replication . The replication works fine if I insert/update handful of rows. The problem occurs when I have a sql job running which deletes/updates & inserts 1000's 15000 plus rows in each database(more than 100) on the server. And this job runs every n minutes. It eventually causes locking issues and slows down replication.

The main objective of doing replication is to able to scale horizontally and also improve performance.
Following is a quick overview of test environment

2 database server
Each server has 100 unique databases & 5 shared database(which are replicated)
Any suggestion how to get around the locking issues?

Thanks

There are two steps to consider when tuning replication:

Reduce the number of commands to replicate
Reduce the time each command takes

The first one can be achieved by grouping multiple statements in a stored procedure and then replicate each procedure execution as a single command instead of multiple separate commands.
@ThomasStringer recommended http://msdn.microsoft.com/en-us/library/ms152754.aspx as a good starting point to learn about this feature.

The second can often be achieved by tuning the Distributor. The Distributor is doing most of the work in transactional replication. But it is also the most neglected part in replication. Often then Publisher is tasked to do the work of the Distributor too. If the Publisher has a lot to do already to keep up with the application workload, It cannot handle the additional work required to execute the distribution tasks. In high performance environments it is therefore a good idea to make the Distributor its own machine with its own disk. This will reduce CPU load, Disk load and network load on the Publisher.

If that is not possible make sure the distribution database is on a different spindle than the application database. You can also try to setup the subscription(s) as pull subscriptions instead of push subscriptions.

The above are general recommendations. As always, first make sure you understand what the root cause is. You can use sys.do_os_wait_stats and sys.dm_db_virtual_file_stats to see wether your disks are to slow, the processor is overloaded you have memory pressure or the network is to slow. Then look at the recommendations above to see what can help in your case.


How to run a SELECT query within while loop in PHP? Within a SELECT query via PHP while loop, I need to run a mysql SELECT query as

$result1 = $mysqli->query("SELECT * FROM table1");

while ( $row = $result->fetch_assoc() ) {

if ( $row['X'] == 'X' ) {
$result2 = $mysqli->query("SELECT * FROM table2");
.....
}

}

but this does not work. I cannot JOIN the tables, as the if statement is complicated to perform SELECT from different tables.

This is for all intents and purposes, a Cartesian Join.

It is definitely out of the question to join every row and pass through the data a single time since you do not know the running time or the amount of temp table space needed.

Perhaps you could do the following:

If table1 is small and table2 is small

Run SELECT * FROM table1; and load it into an array in memory
Run SELECT * FROM table2; and load it into an array in memory
Iterate the outer and inner loop over the arrays only

If table1 is large and table2 is small

Run SELECT * FROM table2; and load it into an array in memory
The outer for loop could still pass through table1 but the inner loop reads the data from table2 from the array.

If table1 is large and table2 is large

Run SELECT * FROM table1; and load it into a local text file
Run SELECT * FROM table2; and load it into a local text file
Iterate the outer and inner loop over the text files

If the DB Server has 60G of RAM (RISKY)

Performs the Cartesian Join anyway
SELECT A.*,B.* FROM table1 A,table2 B;

I also suggest not using SELECT * but using SELECT col1,col2,... reading only needed columns. This would significant reduce round trip times for queries.

The point behind these suggestions is to avoid a process of opening and close threads of data that is fetch repeatedly.

UPDATE 2013-06-04 18:47 EDT

Perhaps you could write a stored procedure, run the SP to collect all the data on the server side, then simply read the data in one pass. Again, this will reduce rounds trips of calls, opening and closing DB Connections. All the PHP logic would thus be done is the SP.


How do I get the adjacent value of MAX/MIN aggregate function? I have table applications and assets. Assets belong to applications. I want to get the highest last modified date and also the adjacent value (of who that value belongs to).
Application table
| application_id | name    |
| -------------- | --------|
| 101            | Weather |
| 102            | HR      |

Asset table
| asset_id | last_modified | last_modified_by | application_id |
| -------- | ------------- | -----------------| -------------- |
| 1        | 2013-01-20    | Toby             | 101            |
| 2        | 2014-03-25    | John             | 102            |
| 3        | 2015-05-30    | Mike             | 101            |
| 4        | 2016-06-25    | Toby             | 101            |
| 5        | 2017-07-26    | Mike             | 101            |
| 6        | 2018-08-27    | Lucky            | 102            |

I want
| application_id | name    | last_modified | last_modified_by |
| -------------- | --------| --------------| -----------------|
| 101            | Weather | 2017-07-26    | Mike             |
| 102            | HR      | 2018-08-27    | Lucky            |

Getting the last_modified is easy but I want to get the adjacent last_modified_by field as well.

This it a variation of a greatest-n-per-group problem.
I would do this with a join to a derived table:
select *
from application app
  join (
    select distinct on (application_id) *
    from asset
    order by application_id, last_modified desc
  ) ast on ast.application_id = app.id;


SSIS Data Source: Embedded Queries, Stored Procedures, or Functions We are moving data from OLTP server into Data Warehouse server, utilizing SSIS to conduct this.

When writing queries from the OLTP Data Source, it it better to utilize?

1) Inline Embedded queries

2) Functions

3) Or Stored Procedures

In researching and testing - Embedded queries are great, however we want code placed in SSDT Database projects to build/compile. So if column name changes/deleted, we would know in Devops portion. If writing in SSIS Datasource 'Test abcd etc', SSIS will still build/compile. If writing in SQL Database project, 'select testabcd from dbo.exampletable', SSDT DB project will fail to build/compile.

I hear Functions are great, since it will display the metadata. However, Multi Statement table value functions are Slow, which team will require sometime.

Stored procedures I am reading have issues with creating metadata. Is this true ? Is there a workaround, heard its possible to create metadata in stored procedures.

Can someone let me know good option between three, or any other options?

4) * Did not want to incorporate views, since team wants to parameterize queries by source Create/Update Dates, if ever intending to reprocess data.

I would recommend using stored procedures.  As far as issues with the metadata, this can occur when temporary tables are used.  Using the WITH RESULT SETS option to execute the stored procedure will allow you to define the metadata of the columns instead and SSIS can bypass this step.  An example of this follows.  Also note that if any column definitions were to change or if new columns are added, this would need to be updated as well.

You may also run into problems with using parameters with an OLE DB source as in your screenshot.  However this can be overcome by using the "SQL command from variable" option for the Data Access field.  A string variable can be created as an expression that executes the stored procedure using a parameter sent to the package (or project).  An example of this is below.  This example uses the DT_STR function to cast the numeric parameter to a string in order to concatenate it.  This is not necessary for string parameters or course, I only included this in case you use numeric parameters.  The WITH RESULT SETS option can also be used in an expression concatenating it with the parameter following the same method.

WITH RESULT SETS

   EXEC ('dbo.YourSP')
    WITH RESULT SETS
      (
        (
            ColumnA INT,
            ColumnB VARCHAR(250),
            ColumnC DATETIME
        )
      );

Variable Expression using Parameter

"EXEC dbo.YourSP " +  (DT_STR, 10, 1252)@ @[$Package::YourParameter]

Functions Followup Response:
If you prefer to use functions and they better suite your needs you can go that route. I tend to prefer stored procedures in general as they offer for flexibility (perform DML/DDL, use temp tables, etc.). If you do use functions, I'd recommend using an inline over a multi-statement table valued version. The inline function is essentially treated like a view and will use the proper statistics to create better execution plans, whereas multi-statement functions won't get these benefits.


Double Entry Accounting Schema Setting up a double entry accounting system for personal use and to help manage a really small business. Trying to put a few features that seem relevant now.

Business Rules

The logic, for those not familiar with accounting, is: money is not created nor destroyed, it is only transferred from account to another. Each transaction has a Debit side, and a Credit side. A few examples:

Salary from your employer:
Credit Salary, Debit Bank Account - the money came from your salary, and went to your bank account.
Pay of rent:
Credit Bank Account, Debit Rent - the money came from your bank account and went to your rent account.

Accounts can be 'stock' accounts, in the sense the balance of the account is cumulative (bank accounts are a good example) or can be flux/flow accounts, in the sense that the balance of the account is non-cumulative (rent is a good example).

The logic behind the design

The idea is to have a major JournalDB table which stores the main entries. The table JournalTx stores each account involved in the transaction. Each entry (from JournalDB) has an ID, and each a transaction (from JournalTx) is linked to a Journal Entry. The base case scenario is that there's 1 entry on JournalDB and two (or more) transactions in JournalTx. Each entry can have a cost_center, a project, and a few other attributes.

There are basically two ways of designing that (as per this question) - as a one-row per transaction style, and a two row per transaction. In the first, I would have one line with the credit account and the debit account, on the second (this one) there are n-lines, one for each account affected.

Accounts

The Accounts table is the Chart of Accounts (on accountant lingo). Is has a hierarchical structure - I used the adjacency list style. Although not very frequent, accounts will have CRUD operations. I added parent_imediate, parent_second as a really ugly solution to make aggregations (calculate the total of the Assets account, for example), but given the challenge (have no idea how to make that after a long research), it seemed like an easy way out - any input or suggestion on that matter is also welcomed.

Main Queries

Get the reports, usually montlhy: basically all acounts with the aggregated transactions that affected each one of then. The best case scenario would be of a pivot table (columns as dates), with each row is an account. I guess an "stacked" version of this would work just fine also.

Accounts are just one dimension - I may want to query by cost_center or by project for example.

Other features

I want to have the ability to budget accounts (hence the budgets table), as well as have 'goals' (I want take vacations that will cost me $ 1.000).
I also want to have tags, and be able to set up recurring bills (which are "expected" transactions)

Basic relations

One entry (journal_db) have many transactions (journal_tx).
One cost_center, project, etc have many entries
One account has many transactions.
One contact has many entries.

My main doubts

I'm just starting to learn about DB/Programming, so bear with me for obvious mistakes.

Is this design solid from a programming/performance/feature point of view?
How to implement reporting? Querying the DB (derived table) or creating a new table (like journal_reports), and creating triggers to update account balances for each entry? (read in this question that is not such a great idea)
Anything that I might be missing?

I think your question is quite broad and hard to answer in its entirety. But here are some things I noticed about your design:
General Design
Some tables in your design violate the first normal form. A good example is contact_adress which has adress1, adress2 and adress3 as columns. Usually a place has only one street address and then one adress column is enough, but in case you really want to have the ability to add multiple street adresses to one place, you should move those adresses to another table (contact_adress, street_adress). The same is true for journal_bills (detail1, detail2) and the accounts table: Instead of parent_imediate, parent_second and parent_third a single parent attribute should be enough. To get the second or the third parent you can use recursive CTEs instead.
I don't know all your business requirements, but you should check if your design allows you to enter nonsensical data: Can an account be both cash and credit? If you have currencies, what are the exchange rates? Can a zip code start with one or more zeros? What about the phone numbers? For some things there are best practices, e.g. how to deal with recurring events.
Naming
Some column names are difficult to understand (ag, acc, pmt), which can lead to maintainability problems if someone else has to work with your database. Use a style guide if you're not sure how to name things. Generally it is a good idea to stick to a consitent naming scheme, e.g. give every table a plural name.
How to implement reporting?
I would stick to simple SQL queries and try to stay away from sophisticated functions like triggers unless you really need to, which is probably never the case for a simple application.


Restrict update on certain columns. Only allow stored procedure to update those columns I have sensitive price columns that I would like to have updated only through a stored procedure. I would like all code or manual attempts to alter values in these price columns to fail if it is not using the stored procedures designed to update it.

I am considering implementing this using triggers and a token table. The idea I am considering is to have a token table. the stored procedures will have to first insert values in the token table. Then update the price columns. The update trigger will check if the token exists in the token table for the updated row. If found, it would continue. if the token is not found, it will throw an exception and make the update transaction fail.

Is there a good/better way to implement this restriction?

SQL Server allows column-level permissions. Just for example:

GRANT UPDATE ON dbo.Person (FirstName, LastName) TO SampleRole;
DENY UPDATE ON dbo.Person (Age, Salary) TO SampleRole;


SQL Server 2008 R2 replication high delivery latency I am seeing an unusually high delivery latency between our distributor and subscribers and I do not understand why.

We have in this configuration 3 sql servers using transactional push replication to replicate data from one master server to two reporting servers.

We have 9 publications.  The distribution agent for most publications are showing under 5ms, but one is show as 2000+ms to both subscribers.

The suspect publication has only 4 small articles (tables) that rarely, if ever, change.  I've checked and each table has an primary key.

I've also checked the @status parameter for each article according to the MS KB: The distribution agent may experience high latency when you configure transactional replication with articles that are configured not to replicate changes as parameterized statements.

I'm tempted to start dropping articles to find out if one particular table is the culprit.

Does anyone have any suggestions as to what I can look at?

Below script will help you find out how many undistributed commands are there. Also, do you see any errors in sql server or windows logs ? Is it just slow or any failures reported ?

SELECT ( CASE
           WHEN mdh.runstatus = '1' THEN 'Start - ' + CAST(
                                         mdh.runstatus AS VARCHAR)
           WHEN mdh.runstatus = '2' THEN 'Succeed - ' + CAST(
                                         mdh.runstatus AS VARCHAR)
           WHEN mdh.runstatus = '3' THEN 'InProgress - ' + CAST(
                                         mdh.runstatus AS VARCHAR)
           WHEN mdh.runstatus = '4' THEN 'Idle - ' + CAST(mdh.runstatus AS
                                                          VARCHAR)
           WHEN mdh.runstatus = '5' THEN 'Retry - ' + CAST(
                                         mdh.runstatus AS VARCHAR)
           WHEN mdh.runstatus = '6' THEN 'Fail - ' + CAST(mdh.runstatus AS
                                                          VARCHAR)
           ELSE CAST(mdh.runstatus AS VARCHAR)
         END )                                   [Run Status],
       mda.subscriber_db                         [Subscriber DB],
       mda.publication                           [PUB Name],
       RIGHT(LEFT(mda.name, Len(mda.name) - ( Len(mda.id) + 1 )),
       Len(LEFT(mda.name, Len(mda.name) - ( Len(mda.id) + 1 ))) - (
       10 + Len(mda.publisher_db) + ( CASE
                                          WHEN mda.publisher_db = 'ALL' THEN 1
                                          ELSE Len(mda.publication) + 2
                                        END ) )) [SUBSCRIBER],
       CONVERT(VARCHAR(25), mdh.[time])          [LastSynchronized],
       und.undelivcmdsindistdb                   [UndistCom],
       mdh.comments                              [Comments],
       'select * from distribution.dbo.msrepl_errors (nolock) where id = ' +
       CAST(mdh.error_id AS VARCHAR(8))          [Query More Info],
       mdh.xact_seqno                            [SEQ_NO],
       ( CASE
           WHEN mda.subscription_type = '0' THEN 'Push'
           WHEN mda.subscription_type = '1' THEN 'Pull'
           WHEN mda.subscription_type = '2' THEN 'Anonymous'
           ELSE CAST(mda.subscription_type AS VARCHAR)
         END )                                   [SUB Type],
       mda.publisher_db + ' - ' + CAST(mda.publisher_database_id AS VARCHAR)
                                                 [Publisher DB],
       mda.name
       [Pub - DB - Publication - SUB - AgentID]
FROM   distribution.dbo.msdistribution_agents mda
       LEFT JOIN distribution.dbo.msdistribution_history mdh
         ON mdh.agent_id = mda.id
       JOIN (SELECT s.agent_id,
                    maxagentvalue.[time],
                    SUM(CASE
                          WHEN xact_seqno > maxagentvalue.maxseq THEN 1
                          ELSE 0
                        END) AS undelivcmdsindistdb
             FROM   distribution.dbo.msrepl_commands t (nolock)
                    JOIN distribution.dbo.mssubscriptions AS s (nolock)
                      ON ( t.article_id = s.article_id
                           AND t.publisher_database_id = s.publisher_database_id
                         )
                    JOIN (SELECT hist.agent_id,
                                 MAX(hist.[time]) AS [time],
                                 h.maxseq
                          FROM   distribution.dbo.msdistribution_history hist (
                                 nolock)
                                 JOIN (SELECT agent_id,
                                              Isnull(MAX(xact_seqno), 0x0) AS
                                              maxseq
                                       FROM
                                 distribution.dbo.msdistribution_history (
                                 nolock)
                                       GROUP  BY agent_id) AS h
                                   ON ( hist.agent_id = h.agent_id
                                        AND h.maxseq = hist.xact_seqno )
                          GROUP  BY hist.agent_id,
                                    h.maxseq) AS maxagentvalue
                      ON maxagentvalue.agent_id = s.agent_id
             GROUP  BY s.agent_id,
                       maxagentvalue.[time]) und
         ON mda.id = und.agent_id
            AND und.[time] = mdh.[time]


How to remove orphaned Log Reader Agents from Replication Monitor? How do I remove the two Log Reader Agents with errors from the Replication Monitor?

As you can see, the job does not exist (Error message produced by right click > Properties on errored Log Agent Reader) - it has previously been deleted, but these two Log Reader Agents refuse to go!

Its never recommended to delete rows from system defined table. Please follow below steps to remove any orphan logreader agent.

Create a  dummy transactional replication ‘Dummy1’.
Add database(select publisher_db from [distribution].[dbo].[MSlogreader_agents]) as publisher and another database(it would be good if you remember the old subscriber) as subscriber.
Add any dummy article to it.
Drop the subscriber of ‘Dummy1’.

This will renmove the orphan log reader agent of transactional replication. Similarly you can work for snapshot agent or merge agent.


How can I detect the maximum value in several rows and columns at the same time in an SQL table? I have a table which records values over time, similar to the following:

The next value after the zero value indicates the beginning of the highest value to one case for a given time.
Example: To Time6 the maximum value is 5 because only case4 Start (after zero value) in that moment the others continuing decreasing at that time.

I need to know at a given moment what is the maximum value case for all the times shown and gradually place it as shown in the following table:

I have worked with some of the aggregate and analytic functions, but can't seem to find the correct solution especially because query is to a table of 40 million records.
I hope an SQL expert guru can help me out, or at least point me in the right direction or some efficient algorithm. Is there a way to query this (preferably without a cursor?)

Thanks in advance.

If I understood what you are trying to do correctly then this should work (fiddle).

The CASE expression ensures that only values with 0 in the preceding column are considered for the aggregation. Prior to the aggregation the integers (presumed >= 0) are padded out with leading zeroes to make them exactly 10 characters long and concatenated to the Cases string.

The MAX is then found to get the lexicographically greatest concatenated string for that time (as here) and then this is split into its constituent parts.

In the event that there is more than one eligible row with the same maximum value for a specific time this will somwehat arbitrarily pick the one with the alphabetically latest Cases string

WITH Grouped As
(
SELECT [ID],
      MAX(CASE WHEN [Time2] = 0 THEN FORMAT([Time3],'D10') + [Cases] END) AS TC3,
      MAX(CASE WHEN [Time3] = 0 THEN FORMAT([Time4],'D10') + [Cases] END) AS TC4,
      MAX(CASE WHEN [Time4] = 0 THEN FORMAT([Time5],'D10') + [Cases] END) AS TC5,
      MAX(CASE WHEN [Time5] = 0 THEN FORMAT([Time6],'D10') + [Cases] END) AS TC6,
      MAX(CASE WHEN [Time6] = 0 THEN FORMAT([Time7],'D10') + [Cases] END) AS TC7,
      MAX(CASE WHEN [Time7] = 0 THEN FORMAT([Time8],'D10') + [Cases] END) AS TC8,
      MAX(CASE WHEN [Time8] = 0 THEN FORMAT([Time9],'D10') + [Cases] END) AS TC9
FROM YourTable
GROUP BY [ID]
)
SELECT [ID],
       Time3 = 0+LEFT(TC3,10),
       Cases3 = SUBSTRING(TC3,11,8000),
       Time4 = 0+LEFT(TC4,10),
       Cases4 = SUBSTRING(TC4,11,8000),
       Time5 = 0+LEFT(TC5,10),
       Cases5 = SUBSTRING(TC5,11,8000),
       Time6 = 0+LEFT(TC6,10),
       Cases6 = SUBSTRING(TC6,11,8000),
       Time7 = 0+LEFT(TC7,10),
       Cases7 = SUBSTRING(TC7,11,8000),
       Time8 = 0+LEFT(TC8,10),
       Cases8 = SUBSTRING(TC8,11,8000),
       Time9 = 0+LEFT(TC9,10),
       Cases9 = SUBSTRING(TC9,11,8000)
FROM Grouped


Optimizing nested SQLite query I have a SQLite table named hashbands that looks like this:
hashband | file_id | window_id
------------------------------
potato   | 0       | 0
potato   | 1       | 0

The table has 100M+ rows (lol). I need to find all hashbands that have two or more distinct file_id values, and then I need to fetch all rows that contain those hashbands and order the results by hashband. Right now I'm using this:
  WITH file_id_counts AS (
    SELECT hashband, COUNT(DISTINCT(file_id)) as count
    FROM hashbands
    GROUP BY hashband
    HAVING COUNT > 1
  ) SELECT hashband, file_id, window_id
    FROM hashbands
    WHERE hashband IN (SELECT hashband from file_id_counts)
    ORDER BY hashband

Does anyone see ways to expedite this query? Any pointers would be helpful!

Likely the majority of the performance issue you're seeing is just the normal constraints of the data limitations of SQLite. Your query is of  sound mind, and I don't believe there's much you can do to optimize it other than re-write the WHERE predicate to something more efficiently relational with an INNER JOIN like this:
WITH file_id_counts AS (
    SELECT hashband, COUNT(DISTINCT(file_id)) as count
    FROM hashbands
    GROUP BY hashband
    HAVING COUNT > 1
  ) SELECT hashband, file_id, window_id
    FROM hashbands
    INNER JOIN file_id_counts
        ON hashbands.hashband = file_id_counts.hashband
    ORDER BY hashband

This is logically equivalent and the reasoning it could be faster is because the IN clause is syntactical sugar for a bunch of OR clauses which can be less efficient than a direct single equality operator like the INNER JOIN is doing above.
Additionally, ensuring your hashbands table has an index on at least (hashband) or possibly on (hashband, file_id) should help (if one doesn't exist already).
Finally, if possible, removing your ORDER BY clause, and instead doing the sorting in your consuming application would probably help a small amount as well. While this mostly just moves the onus of the sort to a different part of the call stack, generally sorting in the database can add a little extra complexity that sometimes can be resolved and slightly more efficient to do in the consuming application. Plus, sorting is really presentation logic in my opinion (at least when not used for a functional purpose of the query).
Where does this SQLite database live?...a mobile application, or on its own server?


How to normalize a database which uses tables as references to other tables? I'm trying to create the database for my application, but I cannot manage to normalize my data on a MySQL database.

I have a Types of map entity
The Types must have one or more Models associated to it, in a particular order
The Models have a Grid, is_prediction flag and an Origin associated to it
There can be more than one Model using the same Grid, Origin and is_prediction condition, differing only on the name of the model
Not all Origins provide all the Models
The Types can only have Models associated to it that have the same condition of [Grid, Origin, is_prediction]

I tried to create a table types_hierarchy, using grid_id, origin_id and is_prediction as foreign key, but it seems wrong, according to the answer on my other question here.

How can I create a normalized database for my needs?

This is what I tried to do:

As per your other post, if you require such delete requirements, it is surely a poor design.

Secondly, there is no "so called" The Best Design.

Third, do not get struck with heavy normalization though essential. Design in such a way that you arrive at a maximum normalization and put it to use. While Beta testing...you shall refine to an extent.

And finally, even if we review / amend your design details, that might not be a best match for your exact requirement. instead, with your given details, if you would have added any particular issue which you foresee, we shall advise on the same.


SQL Server - Log Shipping Into an AlwaysOn Availability Group I have a requirement to build an AlwaysOn Availability Group, which I have done.  Now the user wants me to set up log shipping from another server into the AlwaysOn Availability Group.  I’m not sure how to approach this or if it is even possible.

The AlwaysOn Availability Group is on SQL Server 2012 SP2, running on a Windows 2012 R2 cluster.  The source of the log shipping is SQL Server 2008 R2 SP2, running on Windows 2008 R2.

What is happening is that we are converting from the SS 2008 database to SS 2012 on new hardware.  The customer wants the SS 2012 to be AlwaysOn for HA.  They also want to be able to set it all up in advance and migrate the data from the SS 2008 database in as little time as possible, which is why they asked for log shipping.  What I suggested was to build the SS 2012 without AlwaysOn, do log shipping from the SS 2008, then set up AlwaysOn at cutover time, but there is still an outage as it builds the secondary and they don't like that, either.
Does anyone know of a quick way to copy the data from the SS 2008 database to the SS 2012 database, with AlwaysOn already running?  I'm getting a lot of pressure to make this happen with very little downtime.

The database on the end of log shipping has to be in NORECOVERY to accept the log backups being shipped over. A database involved in AlwaysOn Availability Group has to be removed from the group in order to be restored or set to any restoring state. So setting up log shipping to that database would require it to no longer being replicated.

I would go back to the stockholders and determine their full needs for HA as it seems they left stuff out of their initial request.


sudo service mongod start returns error 100 I'm encountering the following error upon executing sudo service mongod start in terminal.

    mongod.service - MongoDB Database Server
   Loaded: loaded (/lib/systemd/system/mongod.service; disabled; vendor preset: enabled)
   Active: failed (Result: exit-code) since Thu 2018-10-18 12:43:22 ACDT; 7s ago
     Docs: https://docs.mongodb.org/manual
  Process: 3155 ExecStart=/usr/bin/mongod --config /etc/mongod.conf (code=exited, status=100)
 Main PID: 3155 (code=exited, status=100)
      CPU: 22ms

Oct 18 12:43:22 david- systemd[1]: Started MongoDB Database Server.
Oct 18 12:43:22 david- mongod[3155]: 2018-10-18T12:43:22.736+1030 I CONTROL  [main] Automatically disabling TLS 1.0, to force-enable TLS 1.0 specify --sslDisabledProtocols 'none'
Oct 18 12:43:22 david- systemd[1]: mongod.service: Main process exited, code=exited, status=100/n/a
Oct 18 12:43:22 david- systemd[1]: mongod.service: Unit entered failed state.
Oct 18 12:43:22 david- systemd[1]: mongod.service: Failed with result 'exit-code'.

I am unfamiliar with this error, could anyone please suggest what the fix might be?

Fixed using this answer from another post:

  Open /etc/mongod.conf and look for dbPath: /xyz/xyz

  Use the following command to provide the rights to mongodb

  sudo chown -R mongodb:mongodb /xyz/xyz


Is there a way to write intelligent queries against a merge table in MySQL that represents a bunch of tables partitioned by date? I have a set of MySQL tables that are partitioned by date, with a merge table representing all of them. For the query I am writing, the results are joined by date, so rows in table A_2012-12-05 will only join to rows in B_2012-12-05, and rows in A_2012-12-06 will only join to rows in B_2012-12-06, etc. Is there a way to write an intelligent query so that MySQL won't go looking for rows in B_2012-12-06 to join with rows in A_2012-12-05?

Edit by RolandoMySQLDBA 11:17 EDT

Please insert the output of these into this section:

They all look pretty much like this:

CREATE TABLE `mrg_31_session` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `phpsessionid` varchar(32) NOT NULL,
  `start_datetime` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_phpsessionid` (`phpsessionid`),
  KEY `ix_startdatetime` (`start_datetime`),
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=1 UNION=(`session_20121205`,`session_20121204`,`session_20121203`,`session_20121202`,`session_20121201`,`session_20121130`,`session_20121129`,`session_20121128`,`session_20121127`,`session_20121126`,`session_20121125`,`session_20121124`,`session_20121123`,`session_20121122`,`session_20121121`,`session_20121120`,`session_20121119`,`session_20121118`,`session_20121117`,`session_20121116`,`session_20121115`,`session_20121114`,`session_20121113`,`session_20121112`,`session_20121111`,`session_20121110`,`session_20121109`,`session_20121108`,`session_20121107`,`session_20121106`,`session_20121105`)

When you say merge table, I assume you mean MERGE (Mrg_MyISAM) storage engine.

Without seeing any table definitions, I can easily say this...

The smartest way would be to have each MyISAM table defined with a very good primary key/unique key where the lead column would be the date. At the very least, create an index that uniquely identifies data with only one partition (one underlying MyISAM table).

Each MyISAM table under the Merge definition would have such an index.

Any other index where the date is not the first column would result in bunches of table scans (NOT PRETTY !!!)

I have two posts I made back in January 2012 on MERGE table usage:

Jan 04, 2012 : get column from too many tables in mysql
Jan 15, 2012 : Separate tables or partition one huge table?

While the Mrg_MyISAM storage engine is still around, most would favor creating a Partitioned Table instead. Notwithstanding, the same rule applies:  create an index that uniquely identifies data with only one partition.


Index Optimization for Datetime comparing day of week and hour I have a table of sensor observations with obs_ts timestamp, sensor_id text, sensor_val int, and to fill in gaps with data we have models by day of week and hour of day: model_id int, hour_of_day int, model_val int.

To gap fill missing values, we would join these two tables through a crossover table that is: sensor_id text, day_of_week int, model_id int

If our observations table is massive, what would be an optimal way of indexing it for joining on isodow and hour. Does indexing a timestamp also index functions like EXTRACT(isodow FROM obs_ts) or should I make those functional indexes explicit, e.g. CREATE INDEX ON observations (EXTRACT isodow FROM obs_tx). For joining on hour, would it be better to convert the hour_of_day to a timerange?

Per Verace's suggestion I created a test table with 10M records. The TLDR: Indexes on datetime functions need to be explicit, any conversion of that datetime column for joining/filtering will not be served by an index.

SELECT obs_ts AS weekday_indexed, obs_ts AS hour_indexed , *
INTO index_test
FROM observations
LIMIT 10000000;

CREATE INDEX ON index_test(EXTRACT('isodow' from weekday_indexed));
CREATE INDEX ON index_test(EXTRACT('hour' FROM hour_indexed));
CREATE INDEX ON index_test(obs_ts);
ANALYSE index_test;

Table size is 1.7 GB. All of the indexes are 214MB. From my tests the index on obs_ts is never used (probably because these comparisons involve changing the data-type of the timestamp column...). The function specific indexes are used, so there is definitely a performance-index size tradeoff.

Day of Week

SELECT COUNT(1)
FROM index_test
WHERE extract('isodow' from obs_ts )=5
--3 secs 276 msec.

SELECT COUNT(1)
FROM index_test
WHERE extract('isodow' from weekday_indexed)=5
--1 secs 586 msec.

Hour of Day

SELECT COUNT(1)
FROM index_test
WHERE extract('hour' from obs_ts )=5
-- Total query runtime: 2 secs 420 msec.

EXPLAIN ANALYZE SELECT COUNT(1)
FROM index_test
WHERE obs_ts::TIME >= '05:00' AND obs_ts::TIME < '06:00'
-- Total query runtime: 2 secs 391 msec.

SELECT COUNT(1)
FROM index_test
WHERE tx::TIME @> timerange('05:00', '06:00')
-- Total query runtime: 1 secs 928 msec.

SELECT COUNT(1)
FROM index_test
WHERE extract('hour' from hour_indexed)=5
-- 779 msec.


What is the smallest and fastest possible query in MySQL? What would be the fastest possible query to run in MySQL, that will allow monitoring of the basic service functioning?

A few examples I can think of:

SELECT NOW();

or

SHOW GLOBAL STATUS LIKE 'Uptime';

etc..

The purpose is to execute the SQL every few minutes, so the performance is key.

@Kondybas is correct; Fastest query is SELECT 1.

@a-horse-with-no-name is also correct "If your server can't cope with a statement like that once a minute, something is seriously wrong."

Percona Monitoring and Management monitors MySQL servers, in some cases, once per second, without "performance issues".


TDE restored DB encryption state I've been looking into using TDE in our SQL Server 2014 environment and have a few questions. I think I have a fairly solid grasp on the numerous "how-to's" that are out there on initially implementing TDE.  One thing I'm not finding is what state the DB is left in if you restore it to another instance.

For example, I encrypt a DB on one instance, then restore it to another after creating a master key and TDE certificate with the following commands on instance B:

/* Create key in master on second server */
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'

 /* Re-create server certificate in master database
by using the original server certificate backup file.
The password must be the same as was used when the
certificate backup was created. */

    CREATE CERTIFICATE TDE_Dev_Cert
        WITH SUBJECT = 'TDE Certificate'
        FROM FILE = 'C:\certfile'
        WITH PRIVATE KEY (FILE = 'C:\privatekeyfile'
        DECRYPTION BY PASSWORD = 'password';

After a database that has been encrypted with TDE on instance A is restored to instance B, is the database fully decrypted at that point and no longer using TDE, or is the certificate simply allowing TDE to continue to function normally?  The Encryption/Decryption flag on the certificate is what is confusing me.  Thanks in advance.

Restoring the database to another server will not result in the data being unencrypted. However, you do need to:

...move the certificate or asymmetric key that is used to open the DEK.
The certificate or asymmetric key must be installed in the master database of the destination server, so that SQL Server can access the database files.
You must retain copies of both the certificate file and the private
key file in order to recover the certificate. The password for the
private key does not have to be the same as the database master key
password.

The following link provides you with the details:
https://msdn.microsoft.com/en-us/library/ff773063(v=sql.120).aspx
Once you have everything set up on the restored database, you can (of course) turn off TDE and let the server eventually de-encrypt the database, if that is your intention.
There is a Very Important Note on removing encryption from a database that is to be restored from SQL Server 2008 or 2008 R2 to another server at:
http://blogs.msdn.com/b/batuhanyildiz/archive/2012/10/16/how-to-enable-remove-transparent-data-encryption-tde.aspx
(This may not matter to you, depending on the source of the restored database.)


Reassigning surrogate keys? I have a few tables with surrogate keys (SERIAL PRIMARY KEYs unique to each tables).  These tables are used by other tables, with references to these keys.  My usage of these is quite simple :

select * from table_a;
 oid | data
-----+------
  1  |  foo
  2  |  bar

select * from table_c;
 a_id | b_id |   misc
------+------+----------
  1   |   1  | whatever
  2   |   1  | blah

There are no SQL constraints or whatever.

I'm about to make some large modifications to my DB (e.g. addition and merging of tables), so I'm wondering if there is an easy or automatic way to reassign OIDs in related tables ? [Edit : I guess there is no reason such an automatic way should exist, since those keys are assigned manually and not by the DB itself.]

I don't like the idea of having "holes" in my surrogate keys so I'm thinking about "leveling" them (probably using temporary tables with corresponding new/old keys), but is it worth the trouble ?

I'm using PostgreSQL 9.3.5.

There is no definite answer to whether "it is worth the trouble". That's really a matter of taste and style and the total picture we don't have. If in doubt, just don't bother, that's typically the best course of action.

For small, simple tables where you rarely delete rows, it might be convenient for human users to remove gaps from primary keys. However, you should never rely on the absence of gaps if it is at all possible to update IDs or delete rows.

For bigger tables or with ongoing write activity, I wouldn't bother to begin with.

That said, here is a simple procedure, as long as you don't have massive concurrent access to the tables (in which case I also wouldn't bother):

If all your foreign keys are defined ON UPDATE CASCADE, the changes propagate to depending columns.

Do not use "oid" as identifier. This is traditionally used for OIDs and you don't want to cause confusion. Using a sane name for the PK column instead.

To avoid unique violations in intermediary states, you must make way for new IDs. Assuming all your IDs are positive integer numbers:

BEGIN;
LOCK tbl;

-- flip IDs to negative
UPDATE table_a SET table_a_id = table_a_id * -1;

-- assign new gap-less numbers
UPDATE table_a a
SET    table_a_id = u.rn
FROM  (
   SELECT table_a_id, row_number() OVER (ORDER BY table_a_id DESC) AS rn
   FROM   table_a
   ) u
WHERE a.table_a_id = u.table_a_id;

COMMIT;

The (often better) alternative is to just forget about those gaps and use a VIEW with row_number() where you really need gap-less numbers.

Related later answer with another alternative:

Compacting a sequence in PostgreSQL


How to display rows containing special characters in a certain column I have a table with the column f_Name. The column f_name sometimes contains special characters and numbers as in the examples below:

f_name
-----------
Pan23's
Ram'kumar
lawer"s()

I want to retrieve all the values of f_name which have a special character or number.

Try This:

SELECT F_name  FROM tablename WHERE F_name LIKE '%[^a-Z0-9]%'

What this does is search for

%.....% : a string
[.....] : that contains any of the mentioned characters
^       : well actually none of the mentioned characters
a-Z     : from lower-case a through to upper-case Z
0-9     : or any digit

Which results in a search for any character other than a-z, A-Z or 0-9.
Strings that match this would be:

test'user
something?else
I#can#fly

Reference: LIKE (Transact-SQL) (Microsoft | SQL Docs)

After re-reading your question, I would use just:

SELECT F_name  FROM tablename WHERE F_name LIKE '%[^a-Z]%'

...because you want to retrieve rows with numbers too.


plpy.execute('...') statements not in logs How can I get the SQL statements executed by plyp into the postgres log files?

CREATE FUNCTION history_monitor() RETURNS trigger
    LANGUAGE plpythonu
    AS $_$
    ...
    plpy.execute('...')
$_$;

I have a function like above which gets executed if data gets inserted into a table.

I can see all statements executed via psycopg2.

How can I see the statements of the python code running in postgres executed by plpy?

Postgres Version: 9.3

In situations like this, the auto explain module, with log_nested_statements and log_min_duration set to 0 should show the statements executed inside of functions in PostgreSQL.


PostgreSQL (9.3) restore won't finish I've been running a restore job on 38m row table for over 24 hours that doesn't seem to want to come to an end. When I check pg_stat_activity it tells me that the restore job is still running, and the query column shows valid insert queries slotting data into the database.

However, when I run a count(*) on the table, I've been getting the same number just shy of 38m for the last 15 hours.

I don't think the issue is a hardware one, as previously a restore of this table took just 3 hours (it had around 25m rows at that stage).

Has anyone else experienced anything similar?

Also, is there any way to determine the number of rows in a .backup file? Perhaps, then I can know if it has captured all of the data.

Or is the only way to be sure to ignore the backup and run a complete insert all of the data again?

However, when I run a count(*) on the table, I've been getting the same number just shy of 38m for the last 15 hours

The restore will be running in a single transaction - or, at least, that particular table will be being loaded by a COPY command that runs in one transaction. If that's the case you won't see the new rows until the transaction commits.

You haven't shown the contents you mention from pg_stat_activity so it's hard to do more than guess here.

  Also, is there any way to determine the number of rows in a .backup file? Perhaps, then I can know if it has captured all of the data.

Use pg_restore without a target database. It can extract a pg_dump custom-format file, dumping the whole file or individual tables as SQL, as CSV, etc. You can examine that dump contents that way.

  I don't think the issue is a hardware one, as previously a restore of this table took just 3 hours (it had around 25m rows at that stage).

This could be a locking issue - if something else is still connected to the database it might be preventing a table truncation, preventing inserts, etc.

Check pg_locks; see Lock monitoring.


how to obtain a list of restore points? i am implementing restore points using pg_create_restore_point, and will then have a set of scripts to enable my users to restore to one of these restore points. Clearly i have work to do with taking backups and archiving WALs etc, but this is all in hand.

my question is, is there a database table which lists all the available restore points?

cheers
dave

It doesn't look like it. pg_create_restore_point just calls XLogRestorePoint(...) after a bunch of sanity checking, to write the restore point record to the transaction logs. Restore point information is only referenced in recoveryStopsAfter(...), which is part of recovery.

I'm not really sure how PostgreSQL would expose restore points in the catalogs anyway. How would it know a restore point was obsolete and could be removed?

I suggest tracking this yourself using a regular table, where your system deletes from it when it purges old WAL. Your table should contain the restore point name and the LSN (xlog location) at which the point was created. That way you can delete old restore points when you purge the corresponding backups.

So, something like:

INSERT INTO my_restore_points (rpname, rplsn)
VALUES
('some_restore_point', pg_create_restore_point('some_restore_point'))

The other way to do it would be to use pg_xlogdump to scan xlogs for restore points, but that'll be slow and clumsy. Plus I'm not sure 9.2's pg_xlogdump is good enough.


Changing column value Normally used to MySQL so need help on syntax check.

with
SELECT * FROM Bit WHERE CustomerID='3512242' I have a field called Filer. I want to change it from a P to a D. Do I use UPDATE to change the value?

UPDATE dbo.Bit
  SET Filer = 'D'
  WHERE CustomerID = '3512242'
  AND Filer = 'P';

Documentation for UPDATE with many examples.


Given a table containing class and students, need to find the diff in students I have class_students table where given 2 class ids I need to find the diff in students.
This is sql fiddle that I used,
http://sqlfiddle.com/#!9/dba48e/11
Any help here.

Use, for example

SELECT student_id
FROM class_students
GROUP BY student_id
HAVING SUM(class_id = 1) > 0
   AND SUM(class_id != 1) = 0

More informative is

SELECT student_id, GROUP_CONCAT(DISTINCT class_id) classes_in
FROM class_students
GROUP BY student_id
HAVING COUNT(DISTINCT class_id) < ( SELECT COUNT(DISTINCT class_id)
                                    FROM class_students )

PS. Recommendation - define (class_id, student_id) (or in backward fields order) as unique/primary index - it will forbid duplicated records.


Multiple relationships issue Here is my SQLFiddle: http://sqlfiddle.com/#!2/affc4/9

A brief description of the issue:

I have the following data:

topics
brands
celebrities

All 3 of those data types can be associated to an article.

The associations exist in article_assets.

As you can see from the fiddle, I easily retrieved all of the articles that are of asset_type topic and topic name of Relationships.

The issue I'm having is:

I cannot assign an article to having an association of "hot" as a topic AND also the celebrity "Denzel Washington".

I know the flaw is in my database structure. Here's my stream of thoughts on my database structure:

The article_assets table contains an asset type and then a foreign key to that data's table primary id (asset_id)
This creates a problem since I can only reference the specific asset type and the corresponding primary key id.

I need to figure out a solution to allowing multiple references to data and I don't really know how to modify my database structure from here.

Thanks for the help.

If you were designing from scratch, the structure you have is not ideal... however, it can easily be made to work as it is, with one small change to the structure and a more thorough understanding of what SQL can accomplish through joins.

You need to relax the unique constraint on `article_asset`.

UNIQUE KEY `asset_article` (`asset_id`,`article_id`)

This restricts you to not reusing the numeric asset_id of two different types of assets with the same article, which isn't correct, since asset_id is a value that can come from three different information domains (the primary keys of the three tables).  The correct constraint would be this:

UNIQUE KEY `asset_article` (`asset_type`,`asset_id`,`article_id`)

With this, you are only restricted from duplicating the asset_id with the same asset_type for the same article, so this is the correct constraint for that unique index.  That's the only actual change you have to make to your schema.

Then, modify your query structure to effectively treat asset_article as if it were multiple different tables, "taa" (topic article assets) and "caa" (celebrity article assets), each containing a distinct subset of the rows in asset_article.

SELECT a.*
  FROM articles a
  JOIN article_assets taa ON taa.article_id = a.id AND taa.asset_type = 'topic'
  JOIN topics t ON t.id = taa.asset_id
  JOIN article_assets caa ON caa.article_id = a.id AND caa.asset_type = 'celebrity'
  JOIN celebrities c ON c.id = caa.asset_id
 WHERE t.name = 'Family'
   AND c.name = 'Ashton Kutcher';

A logically-equivalent way of expressing this query (above) looks like this (below), which might make it clearer how we are joining both the "taa" and "caa" subsets of article_asset to the articles on the article_id ... or it may make everything much less clear. :)

SELECT a.*
  FROM articles a
  JOIN (article_assets taa JOIN topics t
                           ON t.id = taa.asset_id
                           AND taa.asset_type = 'topic') ON taa.article_id = a.id
  JOIN (article_assets caa JOIN celebrities c
                           ON c.id = caa.asset_id
                           AND caa.asset_type = 'celebrity') ON caa.article_id = a.id
 WHERE t.name = 'Family'
   AND c.name = 'Ashton Kutcher';

A similar structure could be used to find articles about two different celebrities, two different topics, or 1 celeb + 2 topics + 1 brand or however many you felt like stringing together.

With your sample data, my example queries return nothing, because there is no overlap among the different types of assets associated with each story, but after changing the UNIQUE constraint, you can modify the data so that there are overlaps and the query will find the correct results.

http://sqlfiddle.com/#!2/99e4d/4

On the other hand, if you do decide to go with a different design, I would suggest that the more correct approach involves fewer tables -- not more tables.  If you step back and look at the three kinds of "things," you may see that they are all, in fact, one single kind of thing, or at least subtypes of a single kind of thing.  There is no reason why celebrities, topics, and brands should be stored separately -- they are all "subjects" of the articles.  When "things" in a database are the same "kind of thing," they should all be in one table.

For example, if you had a database of contact information, you wouldn't have an "office phone numbers" table and a "home phone numbers" table, and then a table matching people to phone numbers telling you which table to look in for the phone number... right?  That makes no sense at all.  This is the same scenario, just slightly less obvious because you didn't model it based on the common thread among these apparently different (but, arguably, actually the same) kinds of things.

I can't give you a textbook phrase to describe what I'm attempting to say, but if you ever need to look at an attribute in a row in order to determine what information domain another attribute in the row represents, you are definitely "doing it wrong."

One important reason for this assertion is that you cannot enforce referential integrity via foreign key constraints when the "foreign key" could reference the primary key of any one of several tables.

Consider this structure:

CREATE TABLE subject (
  id INT NOT NULL AUTO_INCREMENT,
  subject_type ENUM('brand','celebrity','topic') NOT NULL,
  name VARCHAR(255) not null,
  PRIMARY KEY(id)
);

CREATE TABLE article_has_subject (
  article_id INT NOT NULL,
  subect_id INT NOT NULL,
  PRIMARY KEY(article_id, subject_id),
  KEY(subject_id, article_id) /* to optimize joins in either direction */
);

You could replace that ENUM with a subject_type_id and a corresponding subject_type table, and then, if you wish one day that you wish you had created an "places" table so you could find all of the stories about 'Family' and 'Rhode Island' there's no need to change your schema... you just add another subject_type record.

In your current structure, you're also storing a redundant piece of information in every row of article_asset -- the type of the asset -- because you need that to find the table where you should look.  Redundant data in a database is rarely a good thing.


Restoring mysql data from *.IBD file Unfortunately I have recently lost my VPS and the provider restore it after 1 week, then the only things that I had was the raw files on my server which were accessible in recovery mode.
so by moving those files from the server to my local machine and the other new server which I have bought, I need to recover those IBD files to the MySQL.
What I have to do with these *.IBD files in order to have my data on a live mysql service again?

In this case, I wanted to restore my data on another database rather than the live one.
First of all you should take all of your old data from the following location on your old server and move them to your new VPS.

old destination would be:
/var/lib/mysql/your_db_name

so I created another database called new_db
 then start to creating the tables by the CREATE TABLE statement that I had from my live database, maybe you can find them in your codes.
by doing these, you will have all your tables but empty ones.
let’s assume that you placed the old data files in the:
/location/to/old_files/

and
you created a new empty database called new_db.
this is time to restoring your old data, please follow next steps for each one of your tables:

discarding new data file from its structure by executing
following command on the mysql  where has access to your new
database new_db:
ALTER TABLE my_table DISCARD TABLESPACE;

coping the old data files to the same table structure but in
the new database location:

CP /location/to/old_files/my_table.ibd /var/lib/mysql/new_db/my_table.ibd

change the ownership of the ids file which recently copied in the
new database should be changed to mysql:mysql:

sudo chown mysql:mysql /var/lib/mysql/new_db/my_table.ibd

finally, you can execute this command where you executed the previous one:

ALTER TABLE my_table IMPORT TABLESPACE;

it works for me.


Strangely high writes per second I'm experiencing some extremely strange behavior on one of our OLTP Servers. Just to provide a bit of background, we have an 'OLTP' which has many large and wide tables. We have 560 tables within it which have more than 100 columns within them, and unfortunately as it is an outsourced application there is little I can do with the database design.

So, the graphs below show page reads and writes per second. We have a large spike with reads at around 4 Am which is a load to Qlikview which we are aware of. However, page reads on the whole tend to be lowish, I think they are still higher than the 90 page per second threshold, but less of an issue in comparison to writes. Writes on the other hand, as the graph displays is much higher with regular instances where values are higher than 500 pages per second.

Since 15/10/2018, for the vast majority of data capture intervals, the below has applied

where Lazywrites per second is greater than 1.
Page life expectancy also is normally very high. We do have instances where the value drops during the morning which we know about (4AM), however with the odd exception, we are almost always above the suggested threshold 7500s (Memory/4)*30.
Buffer Cache Hit Ratio also is very healthy at or around 99%.
Finally, we have had very few if any memory related waits, Resource_Semaphore is at 0 seconds and CMEM_THREAD has accumulated 60seconds since 27/10/2018.
Values for Memory grants pending is 0 and Memory grants Outstanding is 1 with data collected since 27/10/2018.

These factors, in my opinion rule out memory pressure being an issue but I can't seem to explain the strange spikes with regards to writes per second. In my opinion, it's related to our database design. We do have extremely wide tables which can be inserted/updated/deleted to frequently. We also have many wide indexes with inefficient data types.

From the data and graphs I've provided, are there any signs of what they high writes is attributed to or are there any metrics I can use to explain the spikes?

Version:

  Microsoft SQL Server 2014 (SP2-GDR) (KB4019093) - 12.0.5207.0 (X64)
  Jul  3 2017 02:25:44   Copyright (c) Microsoft Corporation  Standard
  Edition (64-bit) on Windows NT 6.3  (Build 9600: ) (Hypervisor)

Data Source

  The graphs above are taken from solarwinds, the data I assume is collected from sys.dm_os_performance_counters
  All other counter data which I have references is derived from sys.dm_os_performance_counters, captured at periodic intervals with the wait stats taken from sys.dm_os_wait_stats.

Thanks

Start with the open source sp_BlitzCache (disclaimer: I'm the maintainer of the project.) It's a script that analyzes your cached execution plans to find the most resource-intensive ones, and then tells you about design issues in those queries. You don't have to set anything up in advance - just install sp_BlitzCache and then run it:

sp_BlitzCache @SortOrder = 'writes'

Scroll across the result sets until you get to the "Writes" columns:

That'll show you which queries are doing the most writes. You can scroll back across to the left to see the queries, their execution plans, and their anti-pattern warnings, and then work with your developers to improve them. (Or they might be totally normal: for example, based on your metrics, this might just be a case of someone logging data to tables.)


Any correct procedure for couchbase backup and restore? I have just started using couchbase server and trying to configure backup and restore method. We require to take backup for each couchbase node or cluster backup also available ?

Some recommendations from the professional services team at Couchbase:

Use cbbackupmgr to take differential backups (according to your recover point objectives).
Merge backups to the number of restore points that you want.
Compact the backup set after each backup operation

Some references for you:

cbbackupmgr tutorial
cbbackupmgr strategies


Why does OBJECTPROPERTY return NULL? Say you want to query a database to discover the types of trigger it contains. One way to do this is to use the OBJECTPROPERTY function on all trigger objects in the database.

Sometimes the OBJECTPROPERTY function produces a confusing result. Its output seems to depend on the database context.

The following example query returns a row for each of the sysmail triggers in msdb:

SELECT
  object_id,
  name,
  OBJECTPROPERTY(object_id, 'ExecIsInsertTrigger') AS IsInsertTrigger,
  OBJECTPROPERTY(object_id, 'ExecIsUpdateTrigger') AS IsUpdateTrigger,
  OBJECTPROPERTY(object_id, 'ExecIsDeleteTrigger') AS IsDeleteTrigger
FROM msdb.sys.objects
WHERE
  [type] = 'TR' AND
  name LIKE 'trig_sysmail_%';
GO

The intent is to find out what DML action will fire each trigger. For example, the IsInsertTrigger column contains a 1 if the trigger is defined as AFTER INSERT, and 0 otherwise.

When I execute the query in the context of msdb, the result set contains a 0 or a 1 in each of the computed columns. It looks like this:

object_id   name                         IsInsertTrigger IsUpdateTrigger IsDeleteTrigger
----------- ---------------------------- --------------- --------------- ---------------
713105631   trig_sysmail_profile         0               1               0
745105745   trig_sysmail_account         0               1               0
761105802   trig_sysmail_profileaccount  0               1               0
777105859   trig_sysmail_profile_delete  0               0               1
793105916   trig_sysmail_servertype      0               1               0
809105973   trig_sysmail_server          0               1               0
825106030   trig_sysmail_configuration   0               1               0
841106087   trig_sysmail_mailitems       0               1               0
857106144   trig_sysmail_attachments     0               1               0
873106201   trig_sysmail_log             0               1               0

When I execute the query in the context of master, the result set contains NULL in each of the computed columns. It looks like this:

object_id   name                         IsInsertTrigger IsUpdateTrigger IsDeleteTrigger
----------- ---------------------------- --------------- --------------- ---------------
713105631   trig_sysmail_profile         NULL            NULL            NULL
745105745   trig_sysmail_account         NULL            NULL            NULL
761105802   trig_sysmail_profileaccount  NULL            NULL            NULL
777105859   trig_sysmail_profile_delete  NULL            NULL            NULL
793105916   trig_sysmail_servertype      NULL            NULL            NULL
809105973   trig_sysmail_server          NULL            NULL            NULL
825106030   trig_sysmail_configuration   NULL            NULL            NULL
841106087   trig_sysmail_mailitems       NULL            NULL            NULL
857106144   trig_sysmail_attachments     NULL            NULL            NULL
873106201   trig_sysmail_log             NULL            NULL            NULL

MSDN notes that the OBJECTPROPERTY function returns NULL when:

the property name is not valid.
the object id is not valid.
id is an unsupported object type for the specified property.
the caller does not have permission to view the object's metadata.

I can rule out reasons 1 and 3 because the query returns the correct result in the context of msdb.

At first I thought it might be a cross-database permissions issue (reason 4), but I am sysadmin on the server.

That leaves reason 2, which leaves me with these questions:

Is the object id invalid in a cross-database query?

Which database's OBJECTPROPERTY function is being called?

OBJECTPROPERTY is local to the database the query is run it. So the object_id passed in is resolved against master.sys.objects: but the object_id comes from msdb

So here you have case 2.

On my server, I have 37 matching object_id values between msdb and SomeDBOnMyServer. But the names are different.

USE SomeDBOnMyServer
GO
SELECT
  object_id,
  name,
  OBJECT_NAME(object_id)
FROM msdb.sys.objects
WHERE OBJECT_NAME(object_id) <> name

Obviously, I have a lot of rows where OBJECT_NAME(object_id) is NULL that are filtered here


SQL Server Reporting Services 2017 Cant connect and doesnt show in Configuration Manager I have a few question about SSRS 2017 that I cant find answers to.

Can you no longer add it to the default instance?  It seems to force it to an instance called SSRS

After doing the configuration steps I am still not able to connect to SSRS through mangement studio, it says it cant find a sql server reporting instance.  If I go to browse it does see it under defaultinstance/SSRS but I cant connect.

I cannot see SSRS listed under the configuration manager.

I can connect through the Reporting services configuration manager and I can load the reports page.

Any help is greatly appreciated.

Have you done all the required steps of the installation?

Could you compare your steps with this link post?
Installing SQL Server Reporting Services 2017 (MSSQLTips.com)
Do you notice any differences?

Also check this resource:
What’s New in Reporting Services (SSRS) 2017 (SQLShack.com)

  ...because if you try to connect to a SQL 2017 Reporting Services instance or Power BI Report Server instance with anything less than Management Studio 17 (grab it, its free!) you’ll receive this nasty error:

  The error message on the web site resembles your own error message

  Don’t worry, your instance is there & (likely) online. Once you have SSMS 17 or higher you’ll be able to connect ...


Changing subscription expire days When I go to publication properties and General Tab in that. I see an option

Subscription expire and may be dropped if not synchronized in the following number of days. Replication meta data is kept for this amount of time.

Here some day is set, i.e. 7.

Now I am trying to change this figure to something else, i.e. 20. So If I make this change will the existing subscription reinitialize?

No the subscription will not reinitialize when you make this change.  This setting simply tells the distributor how far behind the subscription can get before the subscription will be automatically expired.


DB2 Load Failure due to timestampformat I'm trying to load a file into DB2 database table using DB2 Load. I'm able to load the file, using the following statement from the unix prompt.

db2 +p -x load from BOM_Sales_20130326.txt of DEL MODIFIED BY COLDEL\| timestampformat=\"YYYY-MM-DD hh:mm:ss\" insert into GEO_SALES.SDM_STL_VFRSK_SALES NONRECOVERABLE

But, when I try to call DB2 Load from a script after parameterising many of the options, it does not seem to work.

For example, I have re-written the statement as follows:

db2 +p -x "load client from $FILE_DIR/$filenm of DEL MODIFIED BY COLDEL\$FILE_DELMTR timestampformat=\"YYYY-MM-DD HH:MM:SS\" insert into $TGT_SCHEMA_NM.$FILE_STG_TBL_TGT NONRECOVERABLE"

All the variable are assigned with correct values prior to calling the statemant. But it seemed to fail due to wrong timestampformat. The error is as follows:

SQL3191N  The field in row "F2-1", column "1" which begins with
"2013-03-26|STL|5678|D|3212345"
does not match the user specified DATEFORMAT,
TIMEFORMAT, or TIMESTAMPFORMAT. The row will be rejected.

In fact, my need is to give the value "timestampformat" also as a parameter, as follows:
db2 +p -x "load client from $FILE_DIR/$filenm of DEL MODIFIED BY COLDEL\$FILE_DELMTR timestampformat=$DTTIME_FMT insert into $TGT_SCHEMA_NM.$FILE_STG_TBL_TGT NONRECOVERABLE"

Since it was not working, just for the testing purpose, the variable $DTTIME_FMT was replaced with \"YYYY-MM-DD HH:MM:SS\". As said earlier, that too does not seem to work.

Request your help to fix this and also like to know whether I can parameterise the all these load options as I've tried.

Thanks,
Tom

Create your statement as follows and check your data format in your table and ensure that you match the time as well (for example, |2011-01-23 14:44:36|2011-01-23 15:44:44|20140512|
is the my time format in my DDL)

load from myexport.del of del modified by coldel| delprioritychar dateformat=\"YYYY-MM-DD\" timestampformat=\"YYYY-MM-DD HH:MM:SS TT\"  insert into mytable

Good luck


How can I achieve a unique constraint with two fields? I have a table with e.g. Name and IsDeleted fields. I want to add a row constraint so that only one Name value can have IsDeleted as 'false'. There can be many duplicate Name values, but they must all have IsDeleted asd true.

How would I write this check constraint ?

Assuming the following table definition

 CREATE TABLE T
 (
 Name VARCHAR(50) NOT NULL,
 IsDeleted BIT NOT NULL
 )

Then you can achieve this with a unique index filtered to only include those Names that you wish to apply the constraint on.

CREATE UNIQUE INDEX ix ON T(Name) WHERE IsDeleted = 'false'


Do large longtext necessarily slow down a database I have a MySQL database that is over a million rows, and has a longtext type data column. Most of these are fairly short, but a few dozen of these rows have more than a MB in the longtext column. No WHERE or LIKE statements are made on the longtext column. Only select statements a few rows at most at a time. It would be nice to keep this data in the database, but the database is slow. I have two tables with a million records, one is 100MB, but this one is 4GB, mostly in the longtext column. I'm not sure if that's a performance concern or not.

We're wondering if removing these rows would result in a performance increase for SELECT, CREATE, UPDATE commands, or if it should not have an effect.

On MyISAM tables, fragmentation can easily occur where rows have variable length and deletions are frequent. This can affect index reads and full table scan performance by increasing the number of pages that need to be read to find the data, and preventing the use of sequential read-ahead technology.

See this Serverfault question for good info on fragmentation, how to detect it and react to it: https://serverfault.com/questions/202000/how-find-and-fix-fragmented-mysql-tables

This is less applicable on InnoDB, where large objects are stored separately to the rest of the rows, and tables are index-oriented.


Can I use SQLite to share data among different applications? Can I use SQLite as a database to which 2 or more applications connect?

I.e. can I use it to share data among different applications?

Yes.

From the FAQ:

  Can multiple applications or multiple instances of the same application access a single database file at the same time?

  Multiple processes can have the same database open at the same time.
  Multiple processes can be doing a SELECT at the same time. But only
  one process can be making changes to the database at any moment in
  time, however.

  SQLite uses reader/writer locks to control access to the database.
  (Under Win95/98/ME which lacks support for reader/writer locks, a
  probabilistic simulation is used instead.) But use caution: this
  locking mechanism might not work correctly if the database file is
  kept on an NFS filesystem. This is because fcntl() file locking is
  broken on many NFS implementations. You should avoid putting SQLite
  database files on NFS if multiple processes might try to access the
  file at the same time. On Windows, Microsoft's documentation says that
  locking may not work under FAT filesystems if you are not running the
  Share.exe daemon. People who have a lot of experience with Windows
  tell me that file locking of network files is very buggy and is not
  dependable. If what they say is true, sharing an SQLite database
  between two or more Windows machines might cause unexpected problems.

  We are aware of no other embedded SQL database engine that supports as
  much concurrency as SQLite. SQLite allows multiple processes to have
  the database file open at once, and for multiple processes to read the
  database at once. When any process wants to write, it must lock the
  entire database file for the duration of its update. But that normally
  only takes a few milliseconds. Other processes just wait on the writer
  to finish then continue about their business. Other embedded SQL
  database engines typically only allow a single process to connect to
  the database at once.

  However, client/server database engines (such as PostgreSQL, MySQL, or
  Oracle) usually support a higher level of concurrency and allow
  multiple processes to be writing to the same database at the same
  time. This is possible in a client/server database because there is
  always a single well-controlled server process available to coordinate
  access. If your application has a need for a lot of concurrency, then
  you should consider using a client/server database. But experience
  suggests that most applications need much less concurrency than their
  designers imagine.

  When SQLite tries to access a file that is locked by another process,
  the default behavior is to return SQLITE_BUSY. You can adjust this
  behavior from C code using the sqlite3_busy_handler() or
  sqlite3_busy_timeout() API functions.


Why does = NULL not work in Oracle? I recently realized that we need to use a special syntax IS NULL to compare a literal to NULL.

Why does = NULL not work here?

Take a look at PSOUG's notes on NULL.  As Fabricio Araujo hinted, NULL is not really a value like the number 4 or string 'bacon strips'.  In fact, NULL is untyped in the SQL language, which is why you cannot validly use it in an equality comparison.  You need the special IS [NOT] NULL syntax to check if a value is NULL or not.


Have foreign key constraint and index key for the same column(s) I am creating a composite index key for 2 columns and then adding foreign keys to its appropriate table. Do I need to create a multiple index key reference and then create separate foreign keys? My example of how I have achieved it is below:

CREATE TABLE IF NOT EXISTS customers_appointments (
appointment_id int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
merchant_id int(11) NOT NULL,
supplier_id int(11) NOT NULL,
appointment_date datetime NOT NULL,
appointment_description mediumtext NOT NULL,
appointment_confirmed tinyint(1) NOT NULL DEFAULT '0',
appointment_confirmed_date datetime NOT NULL,
date_created datetime NOT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

ALTER TABLE customers_appointments
 ADD KEY appointments_index (merchant_id,supplier_id),
 ADD KEY fk_merchant_appointment (merchant_id),
 ADD KEY fk_supplier_appointment (supplier_id);

ALTER TABLE customers_appointments
  ADD CONSTRAINT fk_merchant_appointment
    FOREIGN KEY (merchant_id) REFERENCES merchants (merchant_id)
    ON DELETE CASCADE,
  ADD CONSTRAINT fk_supplier_appointment
    FOREIGN KEY (supplier_id) REFERENCES suppliers (supplier_id)
    ON DELETE CASCADE;

This is working for me but I just need to know from an expert that in terms of performance and how it is designed is correct?

Constraints and Indexes serve two different purposes in the database.

Constraints prevent data that violates the constraints from being stored in the database.  This protects future users of the data, but it limits flexibility and adds overhead.  For foreign key constraints such as the ones you have presented, the overhead is usually small, compared with the benefit of avoiding orphan references.

Indexes provide a map between key values and retrieval pointers.  SQL users never deal directly with retrieval pointers in SQL.  (Well, there are some dialects of SQL that let you do that, but it's generally a bad idea anyway.)  When the optimizer is evaluating strategies for retrieval, and estimating the cost, the presence or absence of an index may make the optimizer choose one strategy over another.  Sometimes the speed-up is dramatic.  Indexes cost a little space to store, and a little time to update when the data changes.  Some indexes are worth the cost, others are not.

In your case, I would play around with leaving out the index you've called fk_merchant_appointment.  It may be the case that MySQL will use the apointments_index instead, and get most of the benefits with less cost.  I can't say for sure.


How is this interpreted by PostgreSQL? Just to give a quick DDL to make my question easier to understand:

------------------ creating tables ------------------
create table if not exists users(
    user_id bigserial primary key,
    first_name text,
    last_name text,
    user_name text
);
create table if not exists managers(
    manager_id bigserial primary key,
    user_id bigint references users(user_id) not null,
    permissions text[]
);
create table if not exists transactions(
    transaction_id bigserial primary key,
    user_id bigint references users(user_id) not null,
    amount numeric,
    is_approved boolean,
    manager_id bigint references managers(manager_id) null
);
------------------ inserting data ------------------
insert into users(first_name, last_name, user_name)
values ('first_name','last_name','ohyea'),
  ('manager','manager','manager');

insert into managers(user_id)
values (2);
insert into transactions(user_id, amount, is_approved, manager_id)
values (1,10,true,null),
  (1,1000,true,1);

Now I am basically going to get manager's name using this way:

select
  t.transaction_id,
  t.user_id,
  u.user_name,
  t.amount,
  t.is_approved,
  mu.user_name as manager_name
from transactions t
    join users u on u.user_id = t.user_id
    left join managers m
        join users mu on mu.user_id = m.user_id
    on m.manager_id = t.manager_id;

The part that I am interested in is this:

    left join managers m
        join users mu on mu.user_id = m.user_id
    on m.manager_id = t.manager_id

What is this to PostgreSQL? A suquery? Like how does it interpret this and what's the difference between doing something like this, and this:

left join (
    select m.manager_id, u.user_name
    from managers m
        join users u on u.user_id = m.user_id
) mu on mu.manager_id = t.manager_id

EDIT:

After a lot of testing, turns out both approaches are absolutely, 100%, 1:1 identical. Same execution plan, same execution times (with small millisecond variations), the only benefit you gain by using the first approach is just cleaner code and easier to follow in bigger functions with a lot of subquery joins.

The documentation tells you:

  A joined table is a table derived from two other (real or derived) tables according to the rules of the particular join type. Inner, outer, and cross-joins are available. The general syntax of a joined table is

T1 join_type T2 [ join_condition ]

  Joins of all types can be chained together, or nested: either or both T1 and T2 can be joined tables. Parentheses can be used around JOIN clauses to control the join order. In the absence of parentheses, JOIN clauses nest left-to-right.

The only way to parse the expression

a JOIN b JOIN c ON cond2 ON cond1

that is in line with the above syntax is

a JOIN (b JOIN c ON cond2) ON cond1

so if your code doesn't throw a syntax error, that's how it must be interpreted.


Run sql script result as job in SQL I would like to know if there is any way to execute a task in SQL that executes the result of a query

these is my script:

SELECT serie,noDoctoFiscal,total,shipperid,fechaemision,
  'update xDoctoFiscalDigital set PDFpath =''C:\Webfact Files Pdfs\Facturas\'
  + RTRIM(noDoctoFiscal)
  + '_' + RTRIM(ShipperId) +'.pdf'''
  + ' '
  + 'where shipperid = ''' +rtrim([ShipperId])+ ''' 'as 'Path'
from xDoctoFiscalDigital
WHERE fechaemision > ='2018-12-01' and serie ='TEC'
and PDFpath is null
order by fechaemision desc

and this is the result of my first script:

update xDoctoFiscalDigital set PDFpath ='C:\Webfact Files Pdfs\Facturas\TEC73535_0EPV085629.pdf' where shipperid = '0EPV085629'

I need to make a job that execute the script result.
Any help would be great, thanks

This might work for your scenario:

Create a table "COMMANDS" to bulk the data from the select statement , and add a field to let you know if that row has been proccesed.

Once you have data on "COMMANDS" , you can create a procedure that exec all the statements
that still un-proccesed.

You can make it with a loop or a cursor or CTE ...

Good luck.


PostgreSQL - Enforcing unique constraint on date column parts I'm trying to enforce a unique month and year combination on a table with a data column. e.g.

create table foo
(
    mydate date
);

I want to enforce that only one row per month and year is valid i.e.

insert into foo values ('2018-01-01'); -- valid
insert into foo values ('2018-01-15'); -- Wouldn't be valid as one row already exists for January 2018
insert into foo values ('2018-02-15'); -- valid

The day portion is irrelevant. The application should only ever insert the first day of the month but as long as there's only one row per month & year it doesn't matter.

In Oracle or SQL Server I would be able to use deterministic functions in a function based unique index so I could use the result of date_trunc('month',mydate) and it would enforce what I want, but that doesn't seem to be possible in PostgreSQL.

I also don't seem to be able to create virtual/computed/calculated fields so can't enforce it that way either.

How should I be enforcing this constraint?

Am I doing something really silly?

Using EXTRACT(year/month ...) works, too:

create unique index year_month_uq
  on foo
  ( extract(year from mydate),
    extract(month from mydate)
  ) ;


MySql not using Buffer Pool I'm checking into a buffer pool issue on a MySql database. According to the query to get database size shown here, the total database size is about 11GB. Unfortunately I know I have much less RAM than that; only about 4.5GB total for this VM. So I check InnoDB buffer pool usage, expecting to see it almost at capacity. Instead I see the opposite:

show engine innodb status \G;

...
Buffer pool size    163839
Free buffers        132864
...

It seems the buffer pool is hardly touched. And, yes, these are all InnoDB tables:

SELECT Engine, COUNT(*) "Count"
FROM Information_Schema.Tables
WHERE table_schema = 'My_Database'
GROUP BY Engine;

Engine   Count
-----    -----
InnoDB    169

What could be going on here?

Fool that I am... the server was restarted this morning. I realized this as I was finishing the question. The buffer pool won't fill up until the database actually needs to use that much data. Indeed, if I re-run the buffer pool query I can see the "Free buffers" number slowly decrease.

I could run some queries to force tables into memory, but given I know I don't have enough memory to go around (hope to address this soon) I should just wait and let my users tell the DB what data is more important.


optimize SELECT query with CASE statements and JOINs I'm creating a social network, and I need a query to fetch the posts for a user's feed. I have the following (simplified) tables:
CREATE TABLE users (
  user_id bigint NOT NULL (primary key),
  username character varying(32)
);

CREATE TABLE posts (
  post_id bigint NOT NULL (primary key),
  repost_id bigint,
  text text,
  media jsonb,
  timestamp timestamp with time zone DEFAULT NOW() NOT NULL,
  user_id bigint
);

CREATE TABLE followers (
  follower_id bigint NOT NULL (primary key),
  user_id bigint NOT NULL,
  following_user_id bigint NOT NULL
);

Users kan follow other users, and their posts will appear on the feed. Users kan repost another post, I want to include these reposts in the feed.
I have the following query (for now). This query works, but is has a lot of CASE statements and joins, and I don't know if this query is the best way to go. I'm a postgresql beginner and I want to know if somebody has some tips to perfect this query.
SELECT
  a.post_id::int,
  a.repost_id::int,
  CASE WHEN a.repost_id IS NULL THEN a.text ELSE b.text END,
  CASE WHEN a.repost_id IS NULL THEN a.media ELSE b.media END,
  CASE WHEN a.repost_id IS NULL THEN a.timestamp ELSE b.timestamp END,
  c.username,
  d.username as original_username
FROM
  posts a
LEFT JOIN
  posts b
  ON a.repost_id = b.post_id
JOIN
  users c
  ON a.user_id = c.user_id
LEFT JOIN
  users d
  ON b.user_id = d.user_id
WHERE
  a.user_id IN (SELECT following_user_id FROM followers WHERE user_id = $1)
  AND
  post_id < $2
ORDER BY
  post_id DESC
LIMIT
  10

Explanation:
I select all posts from the table posts (a) where the user_id is contained in the results of the query to select all the following_user_id's of a user from the table followers (subquery in WHERE). I also do a LEFT JOIN on the table posts (b) to select the information from the reposted post. In the SELECT with the CASE statements, I check if the repost_id is NULL. If it is null, I need the post information from (a), otherwise, I need the post information from (b). The same thing with the usernames. username from (c) is the username from the post itself, the username from (d) is the username of the reposted post (if this is a reposted post), or NULL if it's an original post itself.
A second option is to copy the row op the original post when someone reposts a post, but I want to know if my initial option is OK to?

Moved from a comment because getting lengthy:
I think if your query is executing in only 4ms currently you're fine. The relationship between data growth and execution time is not linear, so if your data even grew 10x what it currently is and you re-ran your query, it'll probably still run in under 10ms, roughly speaking. I don't see much issues in your query itself as long as your indexing everything properly.
The only recommendation I could make is to avoid sub-queries when not necessary, especially as part of predicates, such as in your WHERE clause. This can lead to slight inefficiencies, and there's a better relational alternative by just doing an INNER JOIN to the followers table from your subquery on the user_id = following_user_id field. You may never run into a performance issue as you currently wrote it, but just a heads up a better alternative exists.
Example:
SELECT
  a.post_id::int,
  a.repost_id::int,
  CASE WHEN a.repost_id IS NULL THEN a.text ELSE b.text END,
  CASE WHEN a.repost_id IS NULL THEN a.media ELSE b.media END,
  CASE WHEN a.repost_id IS NULL THEN a.timestamp ELSE b.timestamp END,
  c.username,
  d.username as original_username
FROM
  posts a
INNER JOIN followers f
    ON a.user_id = f.following_user_id
    AND f.user_id = $1
LEFT JOIN
  posts b
      ON a.repost_id = b.post_id
JOIN
  users c
      ON a.user_id = c.user_id
LEFT JOIN
  users d
      ON b.user_id = d.user_id
WHERE
  post_id < $2
ORDER BY
  post_id DESC
LIMIT
  10


How to add/update data from slave server to master server in MariaDB? I am experimenting on whether I can tweak the functionality of master-slave replication. I can write data in my master server which gets updated in the slave server but it cannot happen the other way. I want to find a way where I can write and update data via both the servers. Is it possible to do it ? If yes, what is the way to implement it ?

You can use master-master replication which consists of two master-slave configurations: One master-slave where node 1 is master and node 2 is slave, and one where it's the other way around.

There are a couple of caveats with this, and it's a bit fragile: You need to set auto_increment_increment to 2 on both nodes and set auto_increment_offset to 1 on the one node and 2 on the other, IIRC. This way, you avoid INSERT collisions (assuming the tables use auto_increment). Here's an article that might be useful: MariaDB master/master GTID based replication with keepalived VIP

There is another solution called multi-master replication, and with MariaDB that can be achieved with MariaDB Galera Cluster. This is a more robust solution, although it does have some limitations - note e.g. that it only really works with the InnoDB storage engine. You would usually want to have an odd number of nodes (typically 3) to have quorum amongst the nodes in the event of a network split, although you can simulate this with 2 normal nodes and one Galera arbitrator.


Is it a best practice to contrast table and dependent view metadata to target which views to refresh? We have non-indexed views created on vendor tables. When they change table structure or column definitions we run sp_refreshview as needed. What is the viability of a script to contrast the table column definitions with the view metadata to verify which views need to be refreshed? Is it a best practice to find only views that change or is it OK to refresh all views? Are there any concerns with running sp_refreshview if it's not needed?

Thanks again.

It would be much, much, much, much, much simpler to just run a loop applying sp_refreshsqlmodule for all views and procedures after you apply a vendor update. Don't make this harder than it needs to be.


Is it wrong to consider SCAN DENSITY as a performance metric in higher versions of SQL Server apart from SQL Server 2000? DBCC SHOWCONTIG - introduced in SQL Server 2000
sys.dm_db_index_physical_stats - introduced in SQL Server 2005

Question: Is it wrong to consider scan density as a performance metric in higher versions of SQL Server apart from SQL Server 2000? I am looking for an answer
whether SCAN density was or was not something we should be concerned about in higher versions of SQL Server (> SQL Server 2000), as this metric was rolled out in DMVs and functions.
Background: I have a requirement to maintain scan density of more than 90% in on of my SQL Server database (SQL Server 2016) as part of vendor's requirement.
However, I can see in the Microsoft site, DBCC SHOWCONTIG as a deprecated feature which will be removed in future version of SQL Server and I don't see the SCAN DENSITY column in function sys.dm_db_index_physical_stats (which is the replacement of DBCC SHOWCONTIG).
Example:
For one of my table with 12954223 rows, below are the stats for clustered index (page count - 830943) with different queries:
With DBCC SHOWCONTIG:
Scan density - 76.11 %[103868:106468]
Logical scan fragmentation - 4.68%

With sys.dm_db_index_physical_stats
avg_page_space_used_in_precent - 92.84 %
agg_fragmentation_in_precent - 4.68%

Scan Density is how many times you jump from one extent to another (dividing the "optimal" to the current state). Thing is that this is a side effect of logical fragmentation (jump back and forth between pages, and you are likely to jump back and forth between extents). When the DBCC was redone into a DMV, Paul cleaned up lots of stuff in there.
I suggest you work with logical frag instead (avg frag in percent), if you care about how contiguous things are in the first place. Perhaps more interesting is how full pages are, since random vs seqential I/O is probably more or less irrelevant with modern hardware (read-ahead set aside).
Paul vocals pretty strongly how we should stop using the old DBCC and use the DMV instead.  Do some searches and you will find lots of good nuggets out there. Here is one thread that can give you some insights, for instance: https://social.microsoft.com/Forums/partner/en-US/8d01a374-1493-43c3-8cea-5bf5de723b4d/dmdbindexphysicalstats-and-dbcc-showcontig-disparity.


Would SQL Server Profile show triggers or other code which run as a side-effect of the query I'm profiling? I've been using profiler to test a massive script I'm running in order to check for unintended side effects, e.g. triggers that I don't know about running because I've updated data in a certain table.

Having executed the script while the profiler is running, I can't see any additional code executing but I don't know whether that's because there is none or because profiler wouldn't show it for some reason!

I restricted the profile to these events:

  SQL:BatchCompleted
  SQL:StmtCompleted
  SP:Completed

Am I missing events which might show me that a trigger has fired or can I be confident that nothing is happening which I'm not expecting?

That should capture any ad hoc SQL or stored procedures any triggers associated with the table will fire, depending on the filters of your trace (I would be more concerned that it captures too much for you to sift through).

But there is a much cleaner and less invasive way to know if a table has a trigger associated with it:

SELECT [Trigger] = tr.name
FROM sys.triggers AS tr
INNER JOIN sys.tables AS t
ON tr.parent_id = t.[object_id]
WHERE t.name = N'TableName';

There are multiple other reasons the profiler trace is unreliable - a trigger could be currently disabled, for example, or the trigger might only run further SQL statements if certain conditions are true.