Select / update query doesn't finish and await call on query doesn't get resolved resulting in script to get stuck We are using postgres 11.4 and nodejs to run sql scripts. We use node-postgres module and its Pool feature, setting it to 10.

Database is running on a server with 4GB total memory. When the script is not running the total available memory is around 200MB. When the script is running it can go down to 160MB.

We have a script that most of the time is able to complete on smaller operations but gets stuck because of a query / queries that's being awaited and doesn't finish on larger operations.

The queries that don't finish have a state of 'active' when we do a select on pg_stat_activity. Other connections from the same pool (based on client_addr) have a state of 'idle'.

The node script running these queries does not appear to have become unresponsive when this happens, (we will verify this as it has a message client in it).

In the node script there is a query that fetches 1000 records and these records are iterated over with some logic executed on each one.  The loop does a progress update after every 500 records processed and then fetches another 1000 records.  This is done to process large numbers of records. Eg; 2 - 3 million of records.

The progress updates are done as async database calls that consist of one update and one select.  The update query is asyc/awaited but the select is just async fire and forget, and then loop continues.

We are seeing that when we process a large batch of records that some of the two update/select queries for progress get stuck at the same time. A select query and an update query on the same table. The update is for updating one of the progress columns, and the select computes total progress that is then sent to the client.

Sometimes it happens on the maindata table while doing a select of the 1000 records to be processed but that doesn't happen often.

Sometimes when there is a query that gets stuck we also see autovacuum: VACUUM tablename in pg_stat_activity. And usually once that autovacuum disappears, one
 pair of select,update query gets to finish and the script it is running in runs to completion. BUT not all pair of select,update or other query not related to the table finishes.

Also, after a query(s) is stuck, we can still run select/update/insert queries on that same table from another node script without issue.

We have no idea what is causing this or how to identify/fix the issue.
Any help would be much appreciated.

Thank your for your time.

Update:

Screenshot of pg_stat_activity showing stuck select statement and all rows in pg_locks. Couldn't get the select,update combo that get stucks; for now seems to get eventually unstuck.

Select progress query:

select
    ((tn.progress1 + tn.progress2 + tn.progress3) * 100 / (3 * 100)) total_percent
    ,concat(
            'Total Progress: ', ((tn.progress1 + tn.progress2 + tn.progress3) * 100 / (3 * 100)), E'\n',
            'Status: ', E'\n', tn.message, E'\n',
            E'\n\n'
    ) final_message
    ,(case coalesce(tn.state,0) <> [ERRORCODE] when TRUE then 0 else 1 end) error
from table tn
where
    tn.id = [id]
    and tn.guid1 = [guid1]
    and tn.guid2 = [guid2]
group by
    tn.progress1, tn.progress2, tn.progress3, tn.message, tn.state;

We are now looking at docker + logs as having caused the issue. It's not the database.


trigger to create new row every 30 days How can I create trigger for table to insert a row every 30 day?

ID  Date        Days
1   10/7/2016    0
2   09/8/2016    30
3   08/9/2016    60

A trigger is executed as result of a certain event (e.g insert/update/delete on a table). You need something like a job scheduler.

You did not specified the database. By example Oracle has something like:

DBMS_SCHEDULER.CREATE_JOB(....


Compare sum of two select results Using Firebird, I have two tables, Orders and DespatchedParts.

Orders table:

ID  CustomerID  Total_number_parts
--  ----------  ------------------
1   1           5
2   1           4
3   2           10
4   1           5
5   3           20

DespatchedParts table:

ID  OrdersID  PartQty
--  --------  -------
1   1         2
2   1         3
3   2         10
4   3         10
5   3         10

So in the Orders table the sum of Total_number_parts for Cust 1's orders is 5+4+5=14.

Then looking in DespatchedParts, we want the sum of PartQty for Customer 1's, so 2+3=5.

So Customer 1 has ordered 14 parts and 5 have been dispatched. I want to get all the Customer ID's who have received all their parts.

Below is what I have got so far. I don't know where to put the sum(total_number_parts) = sum(qtyparts) part.

Select a.customerID, sum(a.Total_number_parts)
From Orders a
Join (Select sum(PartQty) from OrderParts Group by customerID) B
   on B.customerID = a.customerID
Group By a.CustomerID

You want to select all customers whose orders have been completely fulfilled. So first you need to know how many parts the customer has ordered in total:

select customerid, sum(total_number_parts) ordered
from orders
group by customerid

You also need to know how many parts have been shipped for each customer. You will need to join orders and dispatched parts to get the customerid associated with the dispatched parts.

select o.customerid, sum(dp.partqty) dispatched
from orders o
left join dispatched_parts dp
   on dp.ordersid = o.id
group by o.customerid

I'm using a left join here, so you also get the total quantity if no parts have been dispatched yet. This is not necessary for your question, but becomes relevant if you want to get all customers who haven't received all their parts yet.

We combine these two, and apply the requested condition in the where-clause:

select a.customerid
from (
    select customerid, sum(total_number_parts) ordered
    from orders
    group by customerid
) a
inner join (
    select o.customerid, sum(dp.partqty) dispatched
    from orders o
    left join dispatched_parts dp
       on dp.ordersid = o.id
    group by o.customerid
) b on b.customerid = a.customerid
where ordered = dispatched

Assuming the corrected orderids (1, 1, 3, 5, 5) I suggested in the comments, this yields

customerid
2
3

Alternatively, you could move the second part entirely into the where-clause by making it a correlated sub-query:

select a.customerid
from (
    select customerid, sum(total_number_parts) ordered
    from orders
    group by customerid
) a
where a.ordered = (
    select sum(dp.partqty)
    from orders o
    left join dispatched_parts dp
       on dp.ordersid = o.id
    where o.customerid = a.customerid
)


Fast query for 'between' with overlapping ranges I'm asking something very similar to Find if number is between two numbers in different fields in a MySQL database on Stack Overflow.

In the two options provided there, one is:

SELECT * FROM your_table WHERE 1265631252 BETWEEN `IP FROM` AND `IP TO`;

...which another poster said is slower compared to his current (and my current) method:

select * from your_table where ip_from <= 1265631252 and ip_to >= 1265631252;"

My table is 700+K rows, and it takes a long time to search the thousands of IP addresses I have. It takes 1 to 2 hours to query the thousands of IP addresses I have, and I'm wondering if there's a faster way.

The table contains overlapping ranges (third row overlaps with second and fourth row), e.g.

 IP FROM      IP TO         REGISTRY  ASSIGNED      CTRY  CNTRY  COUNTRY
"1265631232", "1265893375", "arin",   "1152835200", "US", "USA", "United States"
"1265893376", "1265958911", "arin",   "1149120000", "US", "USA", "United States"
"1265948800", "1266089983", "arin",   "1149120000", "US", "USA", "United States"
"1266024448", "1266089983", "arin",   "1162425600", "US", "USA", "United States"

The EXPLAIN results shows:

id  select_type    table      type    possible_keys    key       key_len    ref     rows      Extra
1   SIMPLE         mytable    range   PRIMARY          PRIMARY   8          NULL    197252    Using where

Your current code is O(N), even with the index, where N=700K.

Solution.  That blog explains how to restructure the table of IP addresses to have only one column (start, no end), and provides code for searching/inserting, etc., efficiently.  This technique is O(logN)* which is a vast improvement compared to O(N).

It involves ORDER BY + LIMIT to get the efficiency.  If you have gaps in IP ranges, then extra rows are needed to indicate "unowned".  Routines take care of properly inserting/removing these rows when you insert new entries.

*: In real world scenarios, to compare performance, we can ignore non-leaf nodes because (in a production system) they are almost always fully cached. Then, assuming the leaf node is not cached, the query only needs to do 1 disk hit. The OP's code is comparison would (estimated) need hundreds of disk hits.


Does any DBMS have a collation that is both case-sensitive and accent-insensitive? Note this question is vendor/version agnostic

It seems to me, as a speaker (typist, writer) of English, reasonable to expect words to be properly cased but not necessarily have the correct accents going in the right direction:

  as I mused in a tete-a-tete with Chloe the maitre d'hotel at the Champs-Elysees restaurant, while waiting for the garcon to fetch my sauted jalapeno pate...

You get the idea with that.

So today I thought I wanted a search condition to use a case-sensitive but accent-insensitive collation but couldn't find one. Is there a good reason for this or is  mine merely a rare use case?

Here's an example of some documentation I was looking at (though thinking vendor/version agnostic):

SQL Server Collation Name (SQL Server 2008 R2)

TL;DR

There is no such thing as a "vendor-agnostic" view of Collations, nor even "version-agnostic", since their implementations -- including which aspects can be made insensitive and their naming conventions -- are vendor-specific and change over time.

Here is a summary of what I have found, and the details are in the longer section below the line:

RDBMS        Naming-             Combinations    Case-Sensitive and
             convention          of options?     Accent-Insensitive support?
-------      ------------        -------------   -----
SQL Server   _CS, _AI, etc       Yes             Latin1_General_100_CS_AI

DB2          _E{x}, _S{y}, etc   Yes             CLDR181_EO_S1

PostgreSQL   locale: en_US       N/A             unaccent(), not via Collation

MySQL        _cs, maybe _ai      No              No: _cs implies _as & _ci implies _ai
                                                 Yes? Create your own Collation :-)

Oracle       only _CI & _AI      No              No: _AI always implies _CI

SAP ASE      arbitrary: turdict  N/A             No: "AI" always implies "CI"

Informix     locale.codepage     N/A             No: no "AI" via Collations

As you can see in the chart, two of the seven RDBMSs do natively support "Case-sensitive and Accent-insensitive" operations via Collations, though they have different naming conventions (and several other functional differences).

One RDBMS -- PostgreSQL -- doesn't natively support this combination, but you can still achieve it by stripping off the accents with the unaccent() add-on function.

The last four RDBMSs, two of which have a similar naming convention for the options, neither natively support this combination nor does there appear to be a means of accomplishing this without writing your own function to remove the accents / diacritical marks. MySQL allows for creating your own Collations, but that requires that you then add it to source control and incorporate it into your testing & deployment process so that it can be applied to all servers in all environments (but still a very cool and flexible option). SAP ASE mentions that SAP can supply additional Unicode sort orders, but no mention of what they might be willing to supply.

With regards to:

  Is there a good reason for this or is mine merely a rare use case?

I can say that in doing the research for this answer I came across a lot of instances of people wanting case-insensitive and accent-sensitive for MySQL, but few, if any, asking for your desired combination.

  I wanted a search condition to use a case-sensitive but accent-insensitive collation but couldn't find one.
  ...
  this question is vendor/version agnostic

You were unsuccessful in your search because it doesn't really make sense to look for an RDBMS based on a Collation specification. That's just not how Collations work. And while you are wanting to approach this as vendor-agnostic, reality is that Collations -- at least the part that we interact with -- are very much vendor-specific, and don't always fit into the scheme that you were searching on.

String comparison and sorting is highly complex, and there are different ways of performing these rules. One method is to have mappings that take into account one or more rules. Hence the four combinations of Sensitive and Insensitive for Case and Accents would equate to four separate mappings. For example, you saw this on that MSDN page for SQL Server Collation Name. If you scroll down, you will see that the left column of the chart is the Sort Order ID. Each Collation has a different ID: SQL_Latin1_General_Cp1_CI_AS = 52 while SQL_Latin1_General_Cp1_CS_AS = 51, even though the only difference is in the case-sensitivity.

Or, it can be rule-based, such as what Unicode offers through the Unicode Collation Algorithm (UCA). In this approach, every character is given, by default, one or more weights. Then, each culture / locale has the option to override any of those weights, or remove rules, or add rules. The algorithm takes into account any locale-specific rules, and then potentially manipulates those weights based on any options chosen (sensitivity, which case comes first when doing case-sensitive sorts, etc). This is one reason why doing Unicode sorting is a bit slower than non-Unicode sorting.

To get a sense of how many options there really are (i.e. the actual complexity), check out this demo from the ICU (International Components for Unicode) project:

ICU Collation Demo

There are 8 separate options to specify, and some of them get represented in multiple elements of the Collation name specification that you are thinking of (e.g. CS, CI, AS, AI, etc). Given how many variations there are, using the mapping file approach where each combination has its own ID would result in many thousands of files. Many of those files would need to get updated whenever there are changes in those particular languages, or when bugs are found. This is probably why there are only 75 of those type of Collations in SQL Server 2012 (i.e. those with names starting with SQL_). Hence no combination for _CS_AI.

And the reason why you couldn't find that combination for the UCA-based Collations? Well, there are 3810 Collations in SQL Server 2012 that do not begin with SQL_, so 3885 Collations total. That list seems to be too long to be enumerated fully on a web page. But this doesn't fully explain why you couldn't find this combination for other vendors.

Beyond what has already been mentioned (i.e. too many combinations to implement, and too many implementations to list), you still need to contend with vendor-specific implementations. Meaning: not all vendors allow for tailoring all of those options, and there is no standard naming convention for Collations in the first place. Also, not all vendors view the sorting options as being a part of the Collation: PostgreSQL Collations are default ordering for the chosen locale, and you need to use ILIKE to get a case-insensitive comparison. See below for vendor-specific info.

SQL Server (Microsoft)

The distinction between what you are seeing on those two MSDN documentation pages and the query provided by @MartinSmith in a comment on the question (slightly revised below):

SELECT *
FROM   sys.fn_helpcollations()
WHERE  [name] LIKE '%[_]CS[_]AI%';

is that those two MSDN pages are referring specifically to the very deprecated SQL Server Collations, whereas the collations that show up as a result of that query (888 of them as of SQL Server 2012, SP3) are Windows Collations.

Starting in SQL Server 2000, the older SQL Server Collations (created prior to SQL Server being able to tap into the Windows Collations) are deprecated and are not being updated with new rules or functionality. For example, starting in SQL Server 2012, a set of Collations was added that support the proper handling of the built-in functions for Supplementary Characters (i.e. the remaining UTF-16 characters beyond the "base" 65,536 characters initially defined in UCS-2). These newer Collations end in _SC (as in Supplementary Characters).

It is best to not use the SQL Server Collations -- those with names starting with SQL_. Hence you do have access to plenty of Collations that support the combination of options that you are looking for (i.e. Case-Sensitive and Accent-Insensitive). Whenever available, it is also best to use the one end in _SC as long as it has all of the other options you are wanting.

While SQL Server does use the _CS_AI naming convention, there is no list of all 3810 (as of SQL Server 2012) Windows Collations. There is just the Windows Collation Name page that lists all of the locales and versions, and how the naming convention works, but that's it.

SQL Server also supports toggling both Width and Kana sensitivity.

MySQL (bought by Oracle)

The MySQL version 5.7, documentation states that it does support the _ai, _as, _ci, and _cs suffixes (and _bin for completeness), but also states:

  For nonbinary collation names that do not specify accent sensitivity, it is determined by case sensitivity. That is, if a collation name does not contain _ai or _as, _ci in the name implies _ai and _cs in the name implies _as.

  For example, latin1_general_ci is case insensitive (and accent insensitive, implicitly), latin1_general_cs is case sensitive (and accent sensitive, implicitly)

This certainly implies that it is possible to have a latin1_general_cs_ai Collation. However, the MySQL 5.5.50 server that I have access to does not have any collations with more than one suffix, and the only suffixes I see are: _cs, _ci, and _bin across 198 total Collations. I used the SHOW COLLATION command to list them.

So, while it sounds like MySQL uses a similar naming convention (at least as far as those two options go), I cannot find a Collation matching what you are looking for. However, it might be possible to strip off the accents (and other diacritical marks) and use a _cs collation to get what you want (similar to how you would do it in PostgreSQL -- see below). But I am not sure of this option and do not have time at the moment to research further.

OR, you might could create your very own Collation to do exactly what you want. Unlike the other RDBMSs, MySQL appears to make it rather simple to add your own Collations, in which case you are in full control over the weighting of each character. Please see Adding a Simple Collation to an 8-Bit Character Set and Adding a UCA Collation to a Unicode Character Set for more details.

For more info on how MySQL handles different types of Collations, please see their Collation Implementation Types page.

PostgreSQL

Collations in PostgreSQL seem to be far less flexible. You specify only the culture / locale: en_US, de_DE, etc. Please see their documentation page for Collation Support for details. Hence, by default you get the culture-specific overrides, but the Collations are otherwise everything-sensitive (which, by the way, is not the same as a "binary" collation).

You can use ILIKE (section 9.7.1) to get case insensitivity, but they do not have a similar operator for accent sensitivity. However, I found that they do have an unaccent function that can be used to strip off accents and other diacritical marks. Please note that this function is an Additional Supplied Module and hence is not necessarily present in any particular PostgreSQL server to use. That most recently linked documentation states:

  When building from the source distribution, these components are not built automatically, unless you build the "world" target
  ...
  If you are using a pre-packaged version of PostgreSQL, these modules are typically made available as a separate subpackage, such as postgresql-contrib.

Please see that documentation for instructions on how to get that function if you don't have it and want it.

More information can also be found in the following Stack Overflow answer:

Does PostgreSQL support “accent insensitive” collations?

DB2 (IBM)

Similar to Microsoft SQL Server, DB2 has two types of Collations:

"SYSTEM" Collations, which are specified using the following format: SYSTEM_{codepage}_[optional-territory]. These are not very flexible, and do not appear to support tailoring sensitivity to case, accents, or anything. You can find the list of supported Collations here: Supported territory codes and code pages
Unicode Collation Algorithm (UCA)-based Collations. These do support quite a bit of tailoring. Please see their Unicode Collation Algorithm based collations page for details about how to configure the behavior, the naming convention, and the list of valid locales. Please note that in Table 1, the example in the third row ("Case Level") starts with:

  Setting the Case Level attribute to on and the Strength attribute to primary level will ignore accent but not case.

That is exactly what you were looking for. But, the syntax for that is:
CLDR181_EO_S1. And this is why your searching did not find anything related to DB2.

Oracle

Oracle 10g added support for doing accent insensitive comparisons and sorting. However:

they only have the options to denote "insensitive" operations: _CI and _AI
you can only specify one of those options at a time
the case-insensitive option -- _CI -- is still accent sensitive
the accent-insensitive option -- _AI -- "is always case-insensitive as well." (quoted from their documentation which is linked below)

Please see their Linguistic Sorting and String Searching documentation page for more details and examples.

SAP ASE (formerly Sybase ASE, a.k.a. Sybase)

ASE supports one or more of the following combinations of sensitivities per each locale / character set:

case-sensitive, accent-sensitive
case-insensitive, accent-sensitive
case-insensitive, accent-sensitive, order with preference
case-insensitive, accent-insensitive

You can see the relationship between locale, character set, and available sort orders on their Selecting the Default Sort Order page. And you can see the full list of Collations on their Collation Names and IDs page.

Their Collation naming convention is arbitrary in that they are all 4 - 8 characters and try to capture the locale name or code page and and some sense of the sorting. For example:

altnoacc == "CP 850 Alternative – no accent"
rusdict == "Russian dictionary ordering"
dynix == "Chinese phonetic ordering"

There is a note on their Selecting the Default Unicode Sort Order page stating:

  You can add sort orders using external files in the $/collate/Unicode directory. The names and collation IDs are stored in syscharsets. The names of external Unicode sort orders do not have to be in syscharsets before you can set the default Unicode sort order.
  ...
  External Unicode sort orders are provided by SAP. Do not attempt to create external Unicode sort orders.

It is unclear whether or not SAP would supply an external sort order to allow for Case-Sensitive and Accent-Insensitive. Maybe someday I will email them and ask if one could be requested.

In order to get the desired combination of sensitivities, you should be able to create a scalar user-defined function to strip off accents and other diacritical marks.

Informix (bought by IBM)

Informix appears to mostly just support the default sorting and comparison behavior of a Collation. Hence Collations are just the locale and character set. Case-sensitivity is handled at the database-level, and by default they are case-sensitive. You can set a database (not a table, or a column, or a query, or even a predicate) to be case-insensitive by specifying NLSCASE INSENSITIVE in the CREATE DATABASE statement.

While the database Collation -- locale and character set -- can be overridden per client connection, there does not seem to be a way to override the case-sensitivity setting. AND, the NLSCASE option has "NLS" in the name for a reason: it only affects NCHAR and NVARCHAR data; CHAR and VARCHAR are always case-sensitive.

Accent-sensitivity is not addressed, nor is there a built-in function to strip of the accents / diacritical marks.

The Informix Collation naming convention is:

<lang>_<country>.<code set>

where:

<lang> = a 2-letter or 3-letter language code
<country> = a 2-letter country or region code
<code set> = the code page specified in one of the 3 following equivalent ways:

name: 8859-1
decimal value of IBM CCSID number: 819
hexadecimal value of IBM CCSID number: 0333

Hence, the following three locale specifications all refer to the exact same locale:

fr_fr.8859-1
fr_fr.819
fr_fr.0333

For more information, please see:

User Guide for IBM Informix International Language Supplement
The IBM Informix registry file (Windows)


Migrating log files - should I copy file/directory permissions? I'm planning to move the log files for the databases ReportServer, ReportServerTempDB, tempDB, and msdb to a new drive.
But it seems that permissions aren´t copied with the directories and files; should I bother to move them over? (Or is that overkill?)

If you don't move the permissions too the service account under which SQL Server is running may not be able to access them.

You can use Robocopy to do this quite easily. I tend to use the following.

ROBOCOPY <source> <target> /MIR /SEC /SECFIX

More detaisl on this command can be found here

More Robocopy options can be found here


Adding a column to the view which shows the date in format YYYYMM In the following view, I want to add a column (not alter the view) named say "ABC" which shows the date in format YYYYMM (no date, only year and month). How can I do that? I am using db2.

create view VIEW_IRS1 as Select
MBR.PRSN_MBRSH_ID
, Atr.Ssn_Tx
, Names.First_Na
, Names.Middle_Na
, Names.Last_Na
, Reff.Ref_Cd_Desc_Tx Suffix_Na
,'FALSE' AS "Request Title II Monthly Income Verification"

From Hix.T_Enrt En
Inner Join Hix.T_Prsn_Enrt Pe On En.Enrt_Id = Pe.Enrt_Id And En.Enrt_Status_Cd In
(428,439, 430 ) And Pe.Active_In = 'Y' And (Pe.Retro_In Is Null Or Pe.Retro_In = 'N')

Left Outer Join Hix.T_Aptc Ap On En.Enrt_Id = Ap.Enrt_Id

Left Outer Join Hix.T_Prsn_Addl_Attr Atr On Pe.Prsn_Mbrsh_Id = Atr.Prsn_Mbrsh_Id

Left Outer Join
(
    Select nAc.Prsn_mbrsh_Id,  First_Na , Middle_Na , Last_Na, Suffix_Cd From
    Hix.T_Prsn_Name Na, Hix.t_prsn_mbrsh_prsn_name_ac nAc
    where na.prsn_name_id = nAc.prsn_Name_Id
) Names On Pe.prsn_mbrsh_id = Names.prsn_mbrsh_id

Left Outer Join Hix.T_Ref_Data Reff On Names.Suffix_Cd = Reff.Ref_Data_Id

Left Outer Join Hix.T_Ref_Data Re On Atr.GENDER_CD = Reff.Ref_Data_Id

Left Outer Join Hix.T_PRSN_MBRSH MBR on MBR.PRSN_MBRSH_ID=Pe.PRSN_ENRT_ID;

(Assuming DB2 for Linux/Unix/Windows here, since you don't specify, but this will probably also work on the other platforms.)

You don't have to do funky date math, there is a built-in function VARCHAR_FORMAT that will do it for you:

SELECT VARCHAR_FORMAT(CURRENT DATE, 'YYYYMM')
FROM SYSIBM.SYSDUMMY1


Querying from system.schema_keyspaces generates code=2200 [Invalid query] I'm following Cassandra docs to learn CQL but when I type the query
SELECT * from system.schema_keyspaces; the system generates
InvalidRequest: code=2200 [Invalid query] message= unconfigured table system.schema_keyspaces. Although I'm able to execute another quires like create, update..etc.

The issue you are having here, is that you are using docs to explore Cassandra that are for version 2.0 and 2.1.  My guess is that you are probably using Cassandra 3.0.  In that case, you will need to query the system_schema keyspace for the "keyspaces" table:

cassandra@cqlsh:system_schema> SELECT * FROM system_schema.keyspaces;

 keyspace_name      | durable_writes | replication
--------------------+----------------+----------------------------------------------------------------------------------------
               test |           True | {'PowerWindows': '1', 'class': 'org.apache.cassandra.locator.NetworkTopologyStrategy'}
        system_auth |           True |    {'class': 'org.apache.cassandra.locator.SimpleStrategy', 'replication_factor': '1'}
      system_schema |           True |                                {'class': 'org.apache.cassandra.locator.LocalStrategy'}
 system_distributed |           True |    {'class': 'org.apache.cassandra.locator.SimpleStrategy', 'replication_factor': '3'}
             system |           True |                                {'class': 'org.apache.cassandra.locator.LocalStrategy'}
      system_traces |           True |    {'class': 'org.apache.cassandra.locator.SimpleStrategy', 'replication_factor': '2'}

(6 rows)

Cassandra underwent a major storage engine rewrite for 3.0+, so much of the schema interactions are different as of that version.  In the future, be sure to double-check valid documentation versions with your local Cassandra version.  From cqlsh, you can query that:

cassandra@cqlsh:system_schema> SELECT release_version from system.local;

 release_version
-----------------
             3.4

(1 rows)


Database design for surveys and experiments I have only a bit of experience in working with databases, but rather theoretically during my studies and not so much practically. I was wondering if someone could help me figuring out how to design a good relational database for this data setup.

The setup is this. I have a set of predefined surveys that are used to evaluate subjects on certain aspects during some experiments. Each of these surveys is composed of a set of items. The items are combined to a set of factors, e.g. items 1, 4 and 8 build factor A, so the survey can be evaluated. The surveys are something like general templates that can be used by experimenters.

In an experiment each subject fills out the survey resulting in a value for each item of the survey. I don’t need to store the results for each subject but rather the mean across subjects for each item. Items are combined to factors in any combination, which is predefined by the survey, and for each factor a value can be calculated.

I want a database that contains all surveys and stores the information for each experiment where a survey is used, that means, the experiment should link to a particular survey and the survey should be linked to the items that compose it with their values, the factors with their values and which items compose which factors. Take into account that each survey can be used in multiple experiments.

The thing is, and this is where it gets complex, for some surveys I won't have the items but only the factors so I should be able to store the information on factors without relying on the items being there. Another thing is that usually the experimenters use the predefined items and factors of a survey. However, sometimes it happens that they use some additional items or factors, or they compose the factors differently. I want to be able to distinguish which items and factors were originally from the survey and which are added extra (or were changed).

So in summary I want to store this information:

Experiment
Survey used on the experiment
Original items of the survey and their respective values (if
available)
Original factors from the survey, their respective values and the
items that compose them
Extra items from the experiment that are not linked to the survey (if
available)
Extra (or changed) factors from the experiment that are not linked to
the survey

Take into account that one factor could be composed with original items and extra items. Any combination could happen. An extra factor could be defined with original and extra items too.

Any ideas what could be a good efficient way to define the structure of this database? I tried around already, but I always have circular references somewhere. And the result seems to be very complex.

You may wish to start with http://tdan.com/data-model-quality-where-good-data-begins/5286 Particulary i found following technique very useful when building relationships:

  Relationship Names
  Your author studied under Richard Barker and is a strong believer in the Oracle/SSADM[5] standard structure for
  relationship names.  It provides discipline, and it permits the
  relationships to be read as easy-to-understand but precise English
  sentences.  When models are being presented to the public, this is
  extremely valuable.   Each of the two roles in a relationship should
  be named with this structure:

Each
<entity type 1>
must be           (Side of relationship line closest to first entity type is solid)
(or)
may be             (Side of relationship line closest to first entity type is dashed)
<relationship>
one and only one  (No crow’s foot next to second entity type)
(or)
one or more       (Crow’s foot next to second entity type)
<entity type 2)

  For example, in Figure 10, the first relationship consists of the
  following two roles:
   •    Each ORDER must be  from one and only one PARTY.
   •    Each PARTY may be a buyer in one or more ORDERS.
  The second  relationship consists of the following two roles:
  • Each ORDER must be to one and only one PARTY.
  • Each PARTY may be a seller in one or  more ORDERS.
  (Note that these sentences are much more graceful than
  something like “A PARTY buys in zero, one, or more ORDERS”.  Non-data
  modelers don’t talk like that.)


How to create sql query results in columns from rows? From the following tables:

store(store_id, ...) {1 ..., 2 ...} two stores
film(film_id, ...)
inventory(inventory_id, film_id, store_id)
rental(rental_id, rental_date, inventory_id, return_date, ...)
category(category_id, name)
film_category(film_id, category_id)
film(film_id, rental_duration, rental_rate, ...)
customer(customer_id, store_id, ...)

Can I have a query result on a table just like the following?

CategoryName | "Store 1: available items" | "Store 1: unavaliable items" | "Store 2: avaliable items" | "Store 2: unavaliable items"

Notice that the store fields in the result table are in separate columns not in the same columns !!

I am not looking for the result query, I am looking for how to make them side by side store 1, store 2.

I don't have the energy to deal with all the joins through all your tables, but the general idea would be to join to the store table twice and check the results.

 SELECT f.film_id,
   CASE when store1.store_id is not null then 'Item exists in store 1' else 'Item missing from store 1' END as 'Store 1 Status'
 FROM film f
 LEFT OUTER JOIN store store1 on f.film_id = store1.film_id AND store1.store_id = 1
 LEFT OUTER JOIN store store2 on f.film_id = store2.film_id AND store2.store_id = 2

This doesn't scale well if you had 100 stores, but for two it wouldn't be evil.

Hope that helps.


Calculating Galera gcache for PXC I was trying ti figure out how to calculate the right gcache for my server and came across the following post discussing just how to do that:

https://www.percona.com/blog/2014/09/08/calculate-correct-size-percona-xtradb-clusters-gcache/

In the post, the talk about doing the following to get the values needed for the calculation:

show global status like 'wsrep_received_bytes';
show global status like 'wsrep_replicated_bytes';
select sleep(60);
show global status like 'wsrep_received_bytes';
show global status like 'wsrep_replicated_bytes';

My question is, will running the select sleep(60) in production block all reads on MySQL (by keeping the mysqld thread busy)? How can i calculate the best gcache value in production?

The answer to your first question is No

When you execute Sleep function, it only block current session and not related to another sessions. So all other sessions work properly

But about your second question.

Note: gcache used to store only writesets in circular buffer style.

Usually a service have a Peak time that users have huge interactive to it.

So at the peak time of the system execute those queries that you said in your question.

After that use this formula to find writesets data bytes per minute

(second wsrep_received_bytes – first wsrep_received_bytes) + (second wsrep_replicated_bytes – first wsrep_replicated_bytes)

Well, now you can decide How much time you want to keep changes to server.

For example if writesets data is 10 MB per minutes and you plan to keep changes data to 24 hours you need to set gcache size to 14400 MB.

Note: In my opinion for more certainty add 20% more to writesets data bytes per minute. In this case for example consider 12 MB instead of 10 MB.


Dynamic Server Name in SQL Statements (from clause) My scenario is I have a thick client side app using a SQL DB. The use of the app is divided into 10 "sites" mostly by Geography but some by Usage. I've written an SP to archive selected data out of the DB and put it elsewhere as the DBs are growing to nearly 500GB in size at this point. Further whilst most servers host one application DB there is one server (and instance) that hosts 3 DBs.

My dilemma is I want to write code that is the same everytime. Ideally I would write something like

declare @dbname varchar(20)
set @dbname = 'Archive' + DB_NAME(DB_ID())

INSERT INTO @dbname.dbo.ArchiveTable (ID, DATA)
SELECT (ID, DATA) from TheData
WHERE Archive = 1

That way I can have the same SP installed on each server, any bug/feature fixes doesn't need me to manually type up 10 different SPs to install.

In the real world I'm also limited by SQL2000 however I'd be interested to hear in ideas using SQL2000 and/or SQL2008R2. The servers are a mixture of both.

As I understand it Synonyms wouldn't help as on the instance with 3 DBs I'd still end up with each DB having it's own copy of the SP with it's own hard coded definition of which named synonym to use.

I also don't feel dynamic SQL statements would be a good fit. There's more to it than the example snippet above and I use table variables to marshal all the work to be done- so that would be out of scope for all the other statements I need to work with.

Dynamic SQL will work. I have written similar things in SQL Server 2000 this way.

EXEC() can take a string variable as input. Construct this string as you show in the question, using the current DB context.

Instead of table variables, however, use temporary tables (#SomeName). These are visible in nested scopes, once defined. So create the #Table, then construct the SQL string referencing the temp table, then EXEC that string.


Msg 2627 Violation of PRIMARY KEY constraint trying to make a database for teams in a tv show here. but when I try and insert data into tblShowteam the following error made its appearance.

Msg 2627, Level 14, State 1, Line 2
Violation of PRIMARY KEY constraint 'PK__tblShowt__F693078C03317E3D'. Cannot insert duplicate key in object 'dbo.tblShowteam'.

tables

-- tabbellen aanmaken
create table tblShow(
setId       int,
Datum       date,
teams       int
primary key (setId));

create table tblShowteam(
SetId           int,
datum           date,
teams           int,
primary key (teams));

create table tblTeam(
TeamId      int,
Coach       varchar(35),
CoachId     int,
teams       int
primary key (CoachId));

-- participant table
create table tblDeelnemer(
DeelnemerId     int identity(1, 1),
DeelnemerV      varchar(35),
deelnemerT_V    varchar(10),
DeelnemerA      varchar(35),
CoachId         int,
datum_optreden  date
primary key (DeelnemerId));

--table for the public viewers
create table tblKijker(
Kijkerv         varchar(35),
KijkerT_V       varchar(10),
KijkerA         varchar(35),
Stoelnummer     int identity(1,3),
ShowId          int Not null,
Email           varchar(35)
primary key (Email));

my inserts would look like this:

insert into tblShowteam values (1, '2014-06-28', 1)
insert into tblShowteam values (2, '2014-06-05', 1)
insert into tblShowteam values (3, '2014-06-12', 1)
insert into tblShowteam values (4, '2014-06-19', 1)
insert into tblShowteam values (5, '2014-06-26', 1)
all other inserts (in diffrent tables) work like normal.

what am i doing wrong here?

tblShowteam has primary key on teams column  (

  create table tblShowteam(
  SetId           int,
  datum           date,
  teams           int,
  primary key (teams));

You are inserting rows with the same primary key (1) multiple time :

insert into tblShowteam values (1, '2014-06-28', 1);
--the same as  "insert into tblShowteam (SetId,datum,teams) VALUES (...,1);
insert into tblShowteam values (2, '2014-06-05', 1) ; --it's gonna fail , record with
-- teams = 1 already exists

I guess setId was supposed to be primary key, not teams.


Can I make a full export of a corrupted database? I have a datafile with a corrupted block. Unfortunately I don't have backup to repair with RMAN (BAD strategy, first the strategy delete the old backup and after make a new backup) Because this new corruption I can't make a new backup with RMAN..

What happen if I make a full export of the database? the export will fail as well? Can I filter for don't make the export of the data in that block?

By default the full scan on that table with a corrupt block will fail, but you can make the scans skip the corrupt blocks of an object:

DBMS_REPAIR.SKIP_CORRUPT_BLOCKS

  This procedure enables or disables the skipping of corrupt blocks
  during index and table scans of the specified object.

DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (
   schema_name  IN VARCHAR2,
   object_name  IN VARCHAR2,
   object_type  IN BINARY_INTEGER DEFAULT TABLE_OBJECT,
   flags        IN BINARY_INTEGER DEFAULT SKIP_FLAG);

In RMAN you can create a backup with corrupt blocks by specifying the allowed maximum amount of corrupt blocks:

SET

  MAXCORRUPT FOR DATAFILE datafileSpec TO integer

  Sets a limit on the number of previously undetected block corruptions
  that the database permits in a specified data file or group of data
  files. The default limit is zero, meaning that RMAN tolerates no
  corrupt blocks.

  The SET MAXCORRUPT command specifies the total number of physical and
  logical corruptions permitted in a data file during a backup job. If
  the sum of physical and logical corruptions detected for a data file
  is no more than its MAXCORRUPT setting, then the BACKUP command
  completes. If more than MAXCORRUPT corrupt blocks exist, then RMAN
  terminates without creating output files.

But you should just fix the object with the corrupt block instead of ignoring it.


How can I Loop a column value if Row exists using INSERT ON KEY DUPLICATE UPDATE? In this Table Scheme

[id  -  name  -  active]
[1   -  alex  -  0     ]

I have a UNIQUE KEY on name, active, What I'm trying to do is to INSERT a new Row

[2   -  max  -   1     ]

using INSERT INTO table (name, active) VALUES (max, 1) ON KEY DUPLICATE active = 0

That I want to loop at active column value,

If the row:[2-max-1] exists with active=1 to change active value to 0

And if exists with active=0 to change it to 1,

And if not exist to INSERT the row with active=1

I keep thinking but can't figure out a way to make this loop using only INSERT . . . ON KEY DUPLICATE UPDATE

The simple expression which returns what you want is (NOT active).

So, your insert will become (you missed the UPDATE keyword):

INSERT INTO table
    (name, active)
VALUES
    ('max', 1)
ON DUPLICATE KEY UPDATE
    active = NOT active ;

If you want to have only one row per name, then you need to change the UNIQUE constraint to (name) only. With that change, the above insert will do exactly what you want:

Insert the row except if a row with that name already exists, in which case toggle the existing active value (essentially ignoring the 1 / 0 in the insert VALUES).


Setting up a use voting table I had a table to store user votes for other users. So user1 can vote for user2 (vote up or vote down). One user should be able to vote for many users, but not repeatedly to the same user; user1 can only vote once for user2. I do not think my table is properly structured to reflect this. Currently each time a user gets a vote up/down an ew row is created for this with either a 1 in the vote_up/vote_down column. What changes should I make to properly structure this table?

vote_id              int
voter_id         int
voted_for_id         int
vote_down        int  //stores the up votes
vote_up              int  //stores the down votes

To clarify users 1-19 can all vote for user 20 once each and user 20 can vote for 1-19 once each. At the end of the day I want to get a tally of how many votes each user has for both up votes and down votes each.

If you want to restrict your votes to "one user can vote for another user once", you simply use the two user id:s as primary key. As for the vote, just use one column and make it an enum:

CREATE TABLE IF NOT EXISTS `user_votes` (
  `voter` int(11) NOT NULL,
  `voted_for` int(11) NOT NULL,
  `vote` enum('up','down') NOT NULL,
  PRIMARY KEY (`voter`,`voted_for`)
)


BEFORE INSERT trigger in MySQL I am new to MySQL and I'm having problems creating a BEFORE INSERT trigger.

I get an unexpected END error.

I have a table named verlof_aanvragen with a column datum (and 6 more columns).

What I want to accomplish is only rows with a date between now()+8 and now()+365 or  CURDATE() + INTERVAL 8 DAY AND CURDATE() + INTERVAL 365 DAY)
can be inserted. An insert with a date outside that interval should fail.

I am using MySQL version 5.5.24.

This is the code:

CREATE TRIGGER chk_dates
BEFORE INSERT ON verlof_aanvragen
FOR EACH ROW
BEGIN
  IF (NEW.datum < CURDATE() + INTERVAL 8 DAY OR NEW.datum > CURDATE() + INTERVAL 365 DAY)
THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Any Message'

END IF
END

I have dealt with this question before. MySQL has a very flimsy architecture when it comes to triggers.

I have written posts before on how to break triggers midstream. The solution is rather unsavory and would leave a pure SQL developer with a bad taste in the month.

With that said, here are my posts that I hope would help:

Apr 25, 2011 : Trigger in MySQL to prevent insertion
Dec 23, 2011 : check constraint does not work?

Since you are a newbie, writing trigger code requires changing the delimiter to something other than a semicolon and then changing back.

DELIMITER $$

CREATE TRIGGER chk_dates
BEFORE INSERT ON verlof_aanvragen
FOR EACH ROW
BEGIN
  IF (NEW.datum < CURDATE() + INTERVAL 8 DAY OR NEW.datum > CURDATE() + INTERVAL 365 DAY)
THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Any Message';
END IF;
END $$

DELIMITER ;

Please look back at my posts to see how I do this in my code.

UPDATE 2013-06-12 11:34 EDT

Here is your last comment

  I am using above trigger with mysql server v 5.5.24 and it works fine.
  This is my local test site. Now I want to use it at my webhost, they
  have mysql server v 5.1.56 ... and it won; t work ! Am I missing
  something?

The problem is the MySQL version. If you look back in my two trigger posts, I used black market techniques to break the trigger midstream. This is necessary for older versions of MySQL. Notice what I stated on Apr 25, 2011 in Trigger in MySQL to prevent insertion :

  The reason the book suggests preempting the Trigger in this manner
  stems from the fact that the MySQL Stored Procedure Language did not
  have SIGNAL implemented into the language (of course, SIGNAL is ANSI
  standard).

  The authors of the book created work arounds by calling on SQL
  statements that are syntactically correct but fail at runtime. Pages
  144-145 (Chapter 6: Error Handling) of the book gives these examples
  on preempting a Stored Procedure directly (Example 6-18) or by SIGNAL
  emulation (Examples 6-19 and 6-20).

I directly quoted Pages 254-256 of the Book

Please note the following:

MySQL 5.5.8 was released as GA Dec 03, 2010
The book was written March 2006

Therefore, signal processing DOES NOT WORK IN MySQL 5.1.

You will have to do the following:

Rewrite the trigger in MySQL 5.1 to break midstream
The trigger you wrote in the MySQL 5.5 database should stay the way it is now

That's why the trigger does not work in MySQL 5.1.

UPDATE 2013-06-12 11:44 EDT

Here is what you can do to the trigger in the MySQL 5.1.56 database

DELIMITER $$

CREATE TRIGGER chk_dates
BEFORE INSERT ON verlof_aanvragen
FOR EACH ROW
BEGIN
  DECLARE dummy INT;
  IF (NEW.datum < CURDATE() + INTERVAL 8 DAY OR NEW.datum > CURDATE() + INTERVAL 365 DAY)
THEN
  SELECT 'Any Message' INTO dummy FROM mysql.user WHERE used = 'anything';
END IF;
END $$

DELIMITER ;

Since mysql.user has no column called used, the query does not execute, breaking the trigger.


SQL Server 2008 R2 DDL Trigger to create DML Triggers on all new user tables As the title says I would like to create a DDL trigger which in turn creates DML triggers for most new tables.

The trigger I have so far will create triggers on ALL tables, which is not what I'm after:

CREATE TRIGGER [CreateReplTriggers] ON DATABASE
    FOR CREATE_TABLE
AS
BEGIN
    SET NOCOUNT ON
    SET ANSI_PADDING ON
    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON

    DECLARE @TABLE_NAME SYSNAME

    SELECT @TABLE_NAME = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME')

    EXEC sp_CreateReplTrigger @TABLE_NAME
END

sp_CreateReplTrigger is a stored proc which creates DML trigger for table with passed name.

Unfortunately this fires off when some System Tables are created as well, e.g. dbo.MSreplication_subscriptions.

There doesn't seem to be anything useful in EVENT_DATA() which would help me differentiate user tables from system tables.

There's also many System Tables and their names don't have much in common: http://msdn.microsoft.com/en-us/library/aa260604(v=sql.80).aspx ... I can't exclude table that have 'MS' or 'sys' in name, because that would be hacky and bad, and would definitely cause issues 5 years later when someone forgets that user tables aren't supposed to have 'MS' or 'sys' in name.

Edit:

The final solution was a combination of the answer below + adding a whitelist array or table. So the stored proc was only called if the table name was in the whitelist. is_ms_shipped was not used.

From SQL 2005 onwards I would use sys.tables which is scoped to user tables and includes the is_ms_shipped column, something like:

CREATE TRIGGER [CreateReplTriggers] ON DATABASE
    FOR CREATE_TABLE
AS
BEGIN
    SET NOCOUNT ON
    SET ANSI_PADDING ON
    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON

    DECLARE @table_name SYSNAME
    DECLARE @xml XML

    SET @xml = EVENTDATA()

    SELECT
        @table_name = QUOTENAME( t.c.value('(SchemaName/text())[1]', 'SYSNAME') ) + '.' +
                      QUOTENAME( t.c.value('(ObjectName/text())[1]', 'SYSNAME') )
    FROM @xml.nodes( 'EVENT_INSTANCE') t(c)

    IF EXISTS ( SELECT * FROM sys.tables WHERE object_id = OBJECT_ID(@TABLE_NAME) AND is_ms_shipped = 0 )
    BEGIN
        RAISERROR( 'Creating replication trigger for table %s.', 10, 1, @TABLE_NAME )
        EXEC sp_CreateReplTrigger @TABLE_NAME
    END
    ELSE
        -- Raise low-level error which won't cause trigger to fail, but will print message to indicate which table has been ignored
        RAISERROR( 'Table %s ignored for replication trigger proc.', 10, 1, @TABLE_NAME )
END

In a quick test on some of my servers, this excluded MS* and sys* tables including MSreplication_subscriptions etc


MySQL database cluster vs. replication setup for large crawler indexes What is the most efficient way to set up a MySQL cluster or replication in an application that has a crawler/indexing engine with thousands of read/writes and then a separate front-end for public access.

It is kind of like creating a mini Google search engine. The database that keeps the latest crawl data has to be separate in a way not to impact the performance of the publicly-accessed database. However, eventually the two databases need to be reconciled so that the publicly-accessed database has all the latest data.

So my idea would be the crawler DB would be the master, while the publicly accessed database would be the slave. And then edit/insert actions on the publicly assessed database would really need to be done on the master DB (same as crawler).

Is this the best way to do such a thing? Is there a better setup?

More information:

Currently there is a lot of reading/updating/inserting at a very fast pace while the crawler is running.

For example, the crawler could send 10 businesses with location and items for each business per every 2-3 seconds, at which, the receiving server needs to do the following. Search DB if business already exists (out of few hundred thousand records), if not create it. Then search if that location (out of half a million records) for that business exists, if not, create it. Then loop through each item of the business and check if it exists (out of 40+ million records), update, if not, create it. Old ones are backed up and then deleted.

Crawler:

Put this process on a separate server (or multiple servers).
If practical, use multi-row INSERTs to batch-insert the rows.
See this for more tips on high-speed ingestion:  http://mysql.rjweb.org/doc.php/staging_table
Write to the Master (or, if allowed by the cluster, any of the Masters).

For the web side:

Have multiple servers with Apache / nginx / whatever receiving the requests.
Send the requests to the Slave(s) (or nodes).
Use round-robin (if there is more than one server for reading).

Other things:

300% CPU is high.  Let's see the worst offender, plus SHOW CREATE TABLE.
Your dataset will probably be very large, so shrink datatypes where practical, and normalize where appropriate.  (The above link has more on normalizing.)
So far, a single Master seems to be adequate, hence any of the replication or clustering options is still viable.
All writes are performed on all servers.  This puts a cap on how much you can scale.  The only way to go beyond that is via "sharding".
Read scaling can be achieved by adding more Slaves, virtually without limit.  This applies to all flavors of replication/clustering.
Indexing -- Use FULLTEXT for searching for "words"; use regular INDEXes where appropriate.
Since neither FULLTEXT nor INDEX handles "model numbers" and "synonyms", you may need to sanitize the data as it is inserted.


Schema Design Inquiry In a timesheets data model, suppose I have an employee table, and a job table. Then, I also have an employeeInJob table to store an employee's rate for a given job e.g.

employeeInJob (
    employeeId,
    jobId,
    rate,
    effectiveFrom )

A given employee may have multiple entries in the employeeInJob table for a given job since his/her rate may vary for that position over time.

Now I have a timesheetEntry table:

timesheetEntry (
    employeeId,
    startTime,
    endTime,
    ???JobId )

My question is whether the timesheetEntry table should reference the job table or the employeeInJob table. I think having it point to the core job provides a more flexible design? Is this the case?

I think it's better that timesheetEntry table should reference to job table as

JobId will be PK of Job table and so associating timesheetEntry with job table will provide better referential integrity of data


Create DB snapshot without specifying the FILENAME attribute I create DB snapshot as one of the tasks in SSIS package. The package works with 20 different DBs. Thus, each DB will have a snapshot with hard-coded FILENAME attribute.
CREATE DATABASE Sales_snap ON
(
  NAME = SalesData,
  FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\data\SalesData_snap.ss'
),
(
  NAME = SalesData1,
  FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\data\SalesData1_snap.ss'
),
(
  NAME = SalesData2,
  FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\mssql\data\SalesData2_snap.ss'
)
AS SNAPSHOT OF Sales

Is there a way to use some default path for FILENAME instead of copy-pasting the same string 20 x 3 times? Alternatively, is there any way to specify base path only once and then reuse it in other SSIS tasks?

Dynamic SQL is probably your best bet.
See 3rd example here:
https://www.sqlservercentral.com/scripts/create-database-snapshot-dynamically


Get minute part from time data type With the time data type (I'm using Microsoft SQL Server 2008), is there any way to take only the minute part out of it? I tried to pass the time to datepart and datediff function but both refused to work.

Example: I want to get 15 from 04:15

select datepart(minute, '04:15')

Utilize the DATEPART() function, with minute as the datepart param.

declare @current_time time

select @current_time = getdate()

select datepart(minute, @current_time) as current_minute

BOL Reference on DATEPART


Mariadb times out on startup after system reboot This is continued from:

https://stackoverflow.com/q/55071021/8644833

Mariadb shuts down after running for a minute or two. If I remove Mariadb and reinstall (keeping my databases), everything works fine. But if I reboot the system Mariadb exhibits the same issue as before. Which is that it will run for 90 seconds or so and then crash. There are no errors generated from this in /var/log/MySQL, and everything else has the same output as the original post.

All my databases checkout as ok.

I realize I may not be able to provide enough information to get an answer as to how to solve this issue. If that's the case could anyone instruct me on the best way to troubleshoot or reinstall without loosing my data. I have a backup of my WordPress database, but I don't know how to tell if it is part of what's causing the problem.

These messages:

[ERROR] mysqld: Table './mysql/db' is marked as crashed and should be repaired
[ERROR] mysql.db: 1 client is using or hasn't closed the table properly

are a symptom of a crash and some MyISAM table(s) was open at the time of the crash.

The standard cure is to run

myisamchk --quick

More details: https://dev.mysql.com/doc/refman/5.7/en/myisam-table-close.html and https://dev.mysql.com/doc/refman/5.7/en/myisam-crash-recovery.html  This is reasonably fast and avoids a reinstall.

A better cure is to switch to InnoDB, since it takes care of repairing itself.


SQL Server Service Broker Compatability across versions? I'm planning a project to upgrade internally-developed app from SQL Server 2005 to SQL Server 2012. I hope to use Service Broker as a bridge while in transition. Are there any issues to consider in a forward-compatibility scenario like this other than newer features like multicasting not being present? I can't find anything in BOL specifically about compatibility.

The answer is yes, you can do cross SQL Server versions service broker as long as all instances are 2005 or newer, and only one of the instances can be SQL Server Express Edition.

Here are some helpful links:
http://blogs.msdn.com/b/stuartpa/archive/2005/07/25/443229.aspx
http://msdn.microsoft.com/en-us/library/bb839483(v=sql.105).aspx


How do I find out how far along my PostgreSQL query is? I have a pretty decent idea of how many rows my SELECT...INTO query will actually process (e.g. I know how many will materialize).

I understand Postgres won't tell me percentage completeness, is there a way (buried deep in logs, system tables, or otherwise) that I can find out how many rows have been pumped into the destination table or have been read by the SELECT query?

As Daniel Vérité mentioned there doesn't seem to be a generic solution. When loading data into a table from a file the following technique can be used to get the progress of the load.

COPY command console progress bar

Create an empty table.

CREATE TABLE mytest (n int);

Create a data file with 10 million lines for loading into the table.

$ seq 10000000 > /tmp/data.txt

Load data from file into the table and display a progress bar.

$ pv /tmp/data.txt | psql -c "COPY mytest FROM STDIN;"

Demo

How this works

By using the copy commands STDIN option we can feed in the data for the copy operation from another process. The pv command will output a file and track it's progress displaying a progress bar, ETA, total time elapsed and the rate of data transfer.

COPY command graphical progress bar

Using the same general technique we could display a progress bar in a graphical application or a web-based application. Using python for example the psycopg2 module lets you call the copy command with a file object of your choosing. You could then track how much of your file object has been read and display a progress bar.


Registry error while trying to install Oracle 11g on Win 2008 R2 I am new to Oracle and trying to install Oracle database 11g Enterprise Edition on Windows 2008 Server R2 but getting this error all the time...

It gives me error when updating registry key HKEY_LOCAL_MACHINE/software/oracle/OracleMTSRecoveryServiceProtid_0..

It says The specified home name was not found in the registry

Could anyone please help me out to resolve this..

From this link:

An old ORACLE_HOME had not been deinstalled/removed correctly and the following registry information still existed:

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\OracleMTSRecoveryService]

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\OracleMTSRecoveryService\Protid_0]
"Name"="HTTP"
"Host"="hostname"
"Port"="49155"

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\OracleMTSRecoveryService\Setup]
"NumberOfInstalls"=dword:00000001

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\OracleMTSRecoveryService\Setup\All Versions]

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\OracleMTSRecoveryService\Setup\All Versions\1]
"Version"="11.2.0.1.0"
"Home"="SOFTWARE\\ORACLE\\KEY_OraDb11g_home2"

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\OracleMTSRecoveryService\Setup\Current Version]
"Version"="11.2.0.1.0"
"Home"="SOFTWARE\\ORACLE\\KEY_OraDb11g_home2"

Please do the following,

Verify that:
the above registry keys exist in the Windows registry, and
they point to an old %ORACLE_HOME% which no longer exists on the server
If the registry keys do not exist or if they point to a valid %ORACLE_HOME% on the server, there is another cause for the error which needs to be investigated further. Do not perform step 2.
After verifying both of the above points, remove the above registry keys and perform the 11.2 installation again. The error should no longer occur.


Connecting to a SQL Server database from a Flash program I currently have the ability to utilize Microsoft SQL Server 2012. I am developing a project with Adobe Flash Builder 4.7.

If I link my database with Adobe Flash Builder is there any additional steps I must take in order to make the database live, or as long as my computer is running will this database be accessible from any device that is utilizing it?

In other words is this a LAN only system or does it automatically make itself available for the programs I link to it?

There are MANY things you will need to consider if you are planning on making your application publicly accessible, some of the common ones are:

If your DB is on your LAN, you need to configure NAT and port forwarding correctly to make it visible on the internet
Permissions need to be set correctly so that your app can access the DB, but random people cannot
Not a very good idea to have an app with its main DB residing on a residential machine
DNS,etc setup since you need to tell the app how to find your computer on the internet


MySQL: JOIN and Conditional Statement Can comeone help me to decipher the right logic of code needed for my query? I just can't find solutions about this or maybe my search queries are just vague. Lol.

I made a query and thought before it was fine but I just realized, just now, the inaccuracy of these. Here are what I've tried:

From a search page, for example we have this variable:

$variable = !empty($_POST["variable"]) ? $_POST['variable'] : "";

QUERY 1

SELECT
    ...
    ...
FROM table1
LEFT JOIN table2 ON table1.column_id = table2.column_id
WHERE table2.column LIKE '%$variable%'

RESULT 1

If $variable has no value, only rows with respective data from table2 are displaying.
If $variable has a value, rows with respective data from table2 and matching value are displaying.

QUERY 2

SELECT
    ...
    ...
FROM table1
LEFT JOIN table2 ON table1.column_id = table2.column_id
     AND table2.column LIKE '%$variable%'

RESULT 2

If $variable has no value, all rows are displaying.
If $variable has a value, all rows are still displaying.

DESIRED RESULT

If $variable has no value, all rows will be displayed.
If $variable has a value, rows with respective data from table2
and matching value will be displayed.

I already tried using IF, CASE and LEFT OUTER JOIN but still no luck. Am I forgetting something with here? Or I'm doing it wrong (which is the truth)?

Okay, I've just gave another shot with IF clause and figured out the simplest query that I can think of. It's now giving the desired result, here's my query:

SELECT
    ...
    ...
FROM table1
LEFT JOIN table2 ON table1.column_id = table2.column_id
WHERE IF('$variable' != '', table2.column = '$variable', 1 = 1)

Successfully, I placed that IF() clause in the right place. If you guys have thoughts about this code or have a very better one than this, kindly share it :)

  [ADDITIONAL]

  This is an answer from @ypercubeᵀᴹ and I love it!

SELECT
    ...
    ...
FROM table1
LEFT JOIN table2 ON table1.column_id = table2.column_id
WHERE table2.column = '$variable' OR '$variable' = ''


TSQL Copy an existing database I am learning how to administrate MS Sql server 2012, come cross a question and its suggested solution for copying an existing database for different name.

Administer a windows azure sql database named Orders, you need to create a copy of this table and renamed as Order_Reporting

CREATE DATABASE Order_Reporting COPY OF Orders

I was wondering whether such useful query could also be used in standard ms sql server some how?

I was wondering whether such useful query could also be used in standard ms sql server some how?

No, CREATE DATABASE .. COPY OF .. is ONLY applicable to  Azure SQL Database

As a side note,

  Copying a database using the CREATE DATABASE statement is an asynchronous operation. Therefore, a connection to the SQL Database server is not needed for the full duration of the copy process.  You can monitor the copy process with the sys.dm_database_copies and sys.databases views. The sys.dm_operations_status view can be used as well as it returns the status of database operations including database copy.

In order to create a copy of current database in SQL Server, you have to backup the database and then restore it with a different name

e.g.

backup database OriginalDB
to disk = 'D:\backup\OriginalDB_full.bak'
with init, stats =10;

restore database new_db_name
from disk = 'D:\backup\OriginalDB_full.bak'
with stats =10, recovery,
move 'logical_Data_file' to 'D:\data\new_db_name.mdf',
move 'logical_log_file' to 'L:\log\new_db_name_log.ldf'


Will SQL charset wildcard query work on Oracle database? I am trying to run the following query

SELECT * FROM GEEKYBRAINS.admin_emp where ename like '[sm]%';

It results in an empty set.

The admin_emp table has the following data though.

I am running this on Oracle database. Will the charset wildcard search work with Oracle database ?

Use regexp_like:

select ename
from admin_emp
where regexp_like(ename,'(^[sm])');

Example SQL Fiddle here.


MongoDB - Set master after re-initation Using version 2.6 (I know...)

My goal is to test a failover.
There are 3 nodes in a replica set.

- node1:Master
- node2:Secondary
- node3:Secondary

I shut node1 and node2 down. Where node3 is a secondary, in READ-ONLY mode.

Because the application requires changes to be written to a mongo node, the config is changed of node3 to leave the Replica set and act as a single node. Now node3 is accepting writes. This is fine so far.

When re-enabling the replica set, node3 isn't set as master but as secondary, therefore it loses the writes and tries to catch up with node1 or node2, which is a master. This is the part where I lose my data.

The question is:

How can I ensure no data is lost on node3 and enable the replica set agin

Because the application requires changes to be written to a mongo node, the config is changed of node3 to leave the Replica set and act as a single node.

A normal replica set failover situation involves one or more members being unavailable, subject to the fault tolerance of your replica set configuration. As long as a majority of configured voting members in the replica set are healthy, they should be able to automatically elect a new primary without manual intervention.

If a majority of members are unavailable and you need to manually reconfigure the replica set to recover write availability, the correct process to follow is a forced reconfiguration with the surviving member(s). This process will ensure that the replica set version information is updated so when former members rejoin they will detect the new configuration and resume syncing if possible.

In this disaster scenario, you are effectively reconfiguring your replica set to have a single member (node3), and later re-adding other members to rebuild the replica set.

  Now node3 is accepting writes. This is fine so far.

This is actually not fine, as far as replication goes. If you restart a mongod that is part of a replica set without the replSet parameter and start writing in standalone mode, the standalone data will diverge from the other members of the replica set. Any standalone writes are not noted in the replication oplog, and there is no way to reconcile local changes with other writes that may have happened in the replica set.

  When re-enabling the replica set, node3 isn't set as master but as secondary, therefore it loses the writes and tries to catch up with node1 or node2, which is a master. This is the part where I lose my data.

If you later rejoin that mongod to the same replica set, the assumption will be that no writes have happened outside of the oplog. The member may also have an older view of the replica set configuration if there have been changes since the member was offline. Replication will try to sync to a common point in the oplog of a current replica set  member (assuming there is one), but you will have introduced data inconsistency via direct updates in standalone mode. Local writes will not be rolled back (or replicated) because the oplog has no record of the changes. This can lead to data loss and other conflicts, because node3 breaks the requirement that all members have the same data (aside from oplog entries not applied yet due to replication lag).

If you follow the forced reconfiguration approach noted earlier, you would end up re-adding former members to your new replica set configuration and would not lose any data.


How to change the default initial and next for new partitions in oracle This is kind of a follow up on this question :
How to change initial extent of an existing partition
I have a table that was created with an initial extent of 8M for new partitions and this takes too much space. Using the answer above I am able to resize already created partitions to something that is OK, but newly created partitions still get an initial extent of 8M. Is it possible to change this without re-creating the table ?
Edit : my table looks like this :
    CREATE TABLE MY_EVENTS (
      EVENT_ID NUMBER            NOT NULL,
      EVENT_DATE     DATE              NOT NULL,
      DESCRIPTION    VARCHAR2(2000 BYTE),
      CONSTRAINT TAUDIT_TRAIL_STA_PK PRIMARY KEY (EVENT_ID) USING INDEX TABLESPACE USR_D1 STORAGE (INITIAL 64 K
                                                                                NEXT 1 M
                                                                                MAXEXTENTS UNLIMITED)
    )
    TABLESPACE USR_D1
    PARTITION BY RANGE (EVENT_DATE)
    INTERVAL (NUMTODSINTERVAL(1, 'DAY'))
    (
    PARTITION P_FIRST VALUES LESS THAN (DATE'2013-06-01'),
    PARTITION VALUES LESS THAN (DATE'2013-10-23')
    );

Edit 2
This is what I have been trying :
ALTER TABLE MY_EVENTS
     MODIFY DEFAULT ATTRIBUTES FOR PARTITION P_FIRST INITIAL 65536;

but I keep getting this error :
ORA-14121: MODIFY DEFAULT ATTRIBUTES may not be combined with other operations

Also, I am using Oracle 12.1
Thanks !

Actually the "FOR PARTITION" part of my query was the issue.
This worked :
ALTER TABLE MY_EVENTS MODIFY DEFAULT ATTRIBUTES
STORAGE (INITIAL 65536 NEXT 65536);


Which picture does the ORA-01830 error message refer to? ORA-01830: date format picture ends before converting entire input string

About the error message.

I am confused. Which picture is the error message talking about? All I provided was a date.

The "format picture" refers to the format mask.  If you are doing an explicit conversion using the TO_DATE function, the format picture is the second argument

to_date( input_string, format_picture )

i.e.

to_date( '2011-01-01 01:23:45', 'YYYY-MM-DD' )

In this case, the format picture is the "YYYY-MM-DD".  This expression raises the ORA-01830 error because the input string contains more data than the format picture is capable of handling.

If, on the other hand, you are doing an implicit conversion, the format picture refer's to the session's NLS_DATE_FORMAT which Oracle uses in its own implicit TO_DATE call.


Question about database structure for network visualuzation Hello folks. I have a matrix I created in Excel that shows 17 nodes, each node's connection to every other node (if any), and that connections strength. Note the error in the image, cells that represent a node's connection to itself should be null not 0.

Where:

0 no connection
1 min connection
2 median connection
3 primary connection

I want to translate this matrix into a database I can use for network visualization but I need help with the structure.

I'd like to avoid having a record for each connection as that's 278 records for my limited dataset alone (final dataset will have 200+ nodes). Would like to have a record for each node that contains every connection and it's connection strength.

Ideal database would look like (columns)
Id
Name
Connections
Strengths (where i2,3,4 = connections i2,3,4)

I've done this type of thing before with data trees and XML but looking for a more robust option I can query for online interactive graphics and games.

In terms of translating the Excel file to the database itself I was planning on writing a string parser to convert CSV to SQL but interested to hear if there are other ideas out there.

Thanks!

The common way of representing network (or graphs) in a tabular / relational structure is to divide it into two tables:

1) A node table where each node has a unique numeric identifier, together with any attributes. Something like this:

CREATE TABLE net_nodes (
  node_id         NUMBER PRIMARY KEY,
  node_type       ....
  ... other attributes
);

2) A links table to represent the connections. Each link has a unique numeric identifier and the IDs of the origin and destination nodes. And of course any attributes, such as direction/orientation (does not apply in your case), throughput, etc. Something like this:

CREATE TABLE net_links (
  link_id         NUMBER PRIMARY KEY,
  start_node_id   NUMBER NOT NULL REFERENCES net_nodes,
  end_node_id     NUMBER NOT NULL REFERENCES net_nodes,
  link_type       ....
  active          ....
  ... other attributes
);

That data model then lets you perform simple graph traversals using recursive SQL. That should work fine given you have a tiny network (you mention 200 nodes). For more serious graphs (10s or 100s of nodes or more) specialized graph databases / engines become necessary.


Index utilization issues in SQL Server queries These days I have to optimize queries written by someone long back and I don't have business info behind them.So trying to optimize them a safe way,without hampering business logic.
While tuning queries, I have come across some doubts which I want to be clear.

The table source_price_curve is as follows:

CREATE TABLE [dbo].[source_price_curve](
    [source_curve_def_id] [int] NOT NULL,
    [as_of_date] [datetime] NOT NULL,
    [Assessment_curve_type_value_id] [int] NOT NULL,
    [curve_source_value_id] [int] NOT NULL,
    [maturity_date] [datetime] NOT NULL,
    [curve_value] [float] NOT NULL,
    [create_user] [varchar](50) NULL,
    [create_ts] [datetime] NULL,
    [update_user] [varchar](50) NULL,
    [update_ts] [datetime] NULL,
    [bid_value] [float] NULL,
    [ask_value] [float] NULL,
    [is_dst] [int] NOT NULL,
 CONSTRAINT [IX_unique_source_curve_def_id_index] UNIQUE NONCLUSTERED
(
    [as_of_date] ASC,
    [source_curve_def_id] ASC,
    [maturity_date] ASC,
    [is_dst] ASC,
    [curve_source_value_id] ASC,
    [Assessment_curve_type_value_id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[source_price_curve]  WITH NOCHECK ADD  CONSTRAINT [FK_source_curve_def_id] FOREIGN KEY([source_curve_def_id])
REFERENCES [dbo].[source_price_curve_def] ([source_curve_def_id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[source_price_curve] CHECK CONSTRAINT [FK_source_curve_def_id]
GO

It has around 130 million records.The following indexes are defined within this table.

CREATE CLUSTERED INDEX [source_curve_def_id_index] ON [dbo].[source_price_curve]
(
    [as_of_date] ASC,
    [source_curve_def_id] ASC,
    [maturity_date] ASC,
    [is_dst] ASC,
    [curve_source_value_id] ASC
)
CREATE NONCLUSTERED INDEX [source_price_curve_123] ON [dbo].[source_price_curve]
(
    [source_curve_def_id] ASC,
    [as_of_date] ASC
)
INCLUDE (   [maturity_date])
CREATE NONCLUSTERED INDEX [IX_PT_source_price_curve_as_of_date_curve_source_value_id]
ON [dbo].[source_price_curve]
(
    [as_of_date] ASC,
    [curve_source_value_id] ASC
)
INCLUDE (   [curve_value],
    [maturity_date],
    [source_curve_def_id])
CREATE NONCLUSTERED INDEX [IX_PT_source_price_curve_as_of_date_curve_source_value_id_Assessment_curve_type_value_id]
ON [dbo].[source_price_curve]
(
    [as_of_date] ASC,
    [curve_source_value_id] ASC,
    [Assessment_curve_type_value_id] ASC
)
INCLUDE (   [curve_value],
    [maturity_date],
    [source_curve_def_id])
CREATE NONCLUSTERED INDEX [IX_PT_source_price_curve_maturity_date] ON [dbo].[source_price_curve]
(
    [maturity_date] ASC
)
CREATE NONCLUSTERED INDEX [IX_PT_source_price_curve_source_curve_def_id_curve_source_value_id]
 ON [dbo].[source_price_curve]
(
    [source_curve_def_id] ASC,
    [curve_source_value_id] ASC
)
INCLUDE (   [as_of_date],
    [Assessment_curve_type_value_id],
    [curve_value],
    [is_dst],
    [maturity_date])
CREATE NONCLUSTERED INDEX [IX_PT_source_price_curve_source_curve_def_id111]
ON [dbo].[source_price_curve]
(
    [source_curve_def_id] ASC,
    [Assessment_curve_type_value_id] ASC,
    [curve_source_value_id] ASC,
    [maturity_date] ASC,
    [as_of_date] ASC
)

I run a query as follows:

SELECT @tenor_from =CONVERT(DATETIME,MIN(spc.maturity_date),103)
FROM source_price_curve spc
WHERE spc.as_of_date >= @as_of_date_from

The execution plan of this query is provided in the below link:
https://www.brentozar.com/pastetheplan/?id=BJ2_3boSZ

I wonder why it is utilizing index IX_PT_source_price_curve_maturity_date(with index on maturity date column only) when filter condition is on as_of_date(while selecting data from source_price_curve).
Why isn't it utilizing the clustered index which has as_of_date as the first column in index order ?

Thanks in advance.

Your query is looking for the minimum maturity_date from rows where the as_of_date is greater than a given value.

The optimizer has to choose between two main execution strategies:

Scan an index ordered by maturity_date ASC until it finds the first row where as_of_date >= @as_of_date_from.

As soon as the query processor finds this first row, the scan of the index stops. This is a point often missed when reading execution plans: an index scan does not necessarily always scan the whole index.

If the first qualifying row can be found quickly, a partial scan of the ordered index is a great strategy for your query. On the other hand, this is a poor strategy if the first row matching the as_of_date predicate occurs toward the end of the index (in maturity_date order).

The optimizer therefore makes an assessment of how many rows from the index it will have to scan before it finds one that matches the as_of_date predicate. This assessment is based on an assumption of uniform distribution (often incorrect in practice).

The plan you provided shows that the Index Scan had to read 1,101,479 rows (in maturity date order) before it found the first one matching the as_of_date predicate.
Seek an index (with as_of_date as the leading key) to find all rows where as_of_date >= @as_of_date_from, then compute the minimum maturity_date found in those rows.

This is a good strategy if the as_of_date predicate matches relatively few rows. It is not so good if many rows are expected to qualify. The expected cost of this strategy depends on the value of @as_of_date_from, and statistical information about the as_of_date column.

Your query uses a local variable, so the optimizer cannot 'see' the value contained in it, and makes a guess at how selective the unknown value will be.

For an inequality predicate, the default guess is that 30% of the rows will qualify. For a table of 130 million rows, the optimizer will therefore guess that 30% * 130,000,000 = 39,000,000 rows will match. This is a lot of rows to compute the minimum maturity_date on, the the optimizer prefers the other strategy.

You can allow the optimizer to 'see' the value inside the variable at execution time by adding an OPTION (RECOMPILE) hint to the query. This enables the Parameter Embedding Optimization, at the cost of having to recompile the query plan every time the query is executed.

If the @as_of_date_from variable value varies a lot between executions, using the OPTION (RECOMPILE) hint may be worthwhile for you. The optimizer may still choose the partial ordered scan strategy, depending on its assessment of the relative costs of the two strategies.

In any case, you should change the data type of the @as_of_date_from to datetime to match the as_of_date column to avoid an implicit conversion.

The data type of @tenor_from should probably be datetime not varchar(20). Or change the query to CONVERT(varchar(20),MIN(spc.maturity_date),103). The current CONVERT makes no sense.


Email open rate calculations in subqueries with postgres So I'm relatively new to PostgreSQL, but here we go:

I have a pgsql database that is receiving HTTP Post Email Events from my Sendgrid email server, and am trying to make a dashboard that displays all pertinent information. The query returns everything how I wanted, except for a crazy impossible open rate. I'm not sure how I can best find the unique open rate by email send.

Here is the query I have so far, which returns valid results for everything but open rate:

 SELECT
  MIN(timestamp) as "Date",
  "EmailEvents".send_name as "Send Name",
  cohort as "Cohort",
  product as "Product",
  COUNT(DISTINCT email) AS "Send Size",
  opencount.openRate AS "Open Rate"
 FROM
  "EmailEvents",
  (SELECT
   send_name,
   (100*(SUM(CASE
    WHEN email IS NOT NULL
    AND event = 'open' THEN 1
    ELSE 0
    END) / SUM(DISTINCT CASE
    WHEN email IS NOT NULL THEN 1
    ELSE 0
   END))) AS openRate
  FROM
   "EmailEvents"
  GROUP BY
   send_name) opencount
 WHERE
  "EmailEvents".send_name = opencount.send_name
  GROUP BY
  "EmailEvents".send_name,
  product,
  cohort,
  opencount.openRate
 ORDER BY
  MIN(timestamp) DESC

Any help would be appreciated. Thanks!!

Answering my own question...but after playing around with it for awhile this seems to provide the correct answer:

SELECT
  MIN(timestamp) as "Date",
  "EmailEvents".send_name as "Send Name",
  cohort as "Cohort",
  product as "Product",
  COUNT(DISTINCT email) AS "Send Size",
  (100*openCount.distinctCount / COUNT(DISTINCT email))::numeric AS "Open Rate"
FROM
  "EmailEvents",
  (SELECT
   DISTINCT send_name,
   COUNT(distinct email) as distinctCount
   FROM
   "EmailEvents"
WHERE
   event='open'
GROUP BY
   send_name) openCount
WHERE
   "EmailEvents".send_name = openCount.send_name
   AND EXTRACT(month
   from
   timestamp) = EXTRACT(month
   from
   now())
GROUP BY
  "EmailEvents".send_name,
  product,
  cohort,
  openCount.distinctCount
ORDER BY
  MIN(timestamp) DESC

Hope this helps someone else looking for a similar solution!


Duplicates when checking for two different column values based on a key that is not unique in table I want to check to see if two orders were placed for one person within an hour of one another.
Example data
PERSONID | proc_code | TIME
123 | 1 | 4/25/2016 00:01:00
123 | 2 | 4/25/2016 00:01:00
123 | 2 | 4/25/2016 00:02:00
111 | 1 | 4/25/2016 00:01:00
111 | 1 | 4/25/2016 00:03:00
111 | 2 | 4/25/2016 00:01:00

SELECT
persons.person_id,
order_proc.proc_code,
(CASE WHEN order_proc.proc_code='1'
then case when order_proc2.proc_code='2' and order_proc.time=order_proc2.time
then 'Y'
else 'N'
end
end) as 'Y/N?'
from order_proc
inner join PERSONS on order_proc.PERSON_ID=PERSONS.PERSON_ID
inner join order_proc as order_proc2 on order_proc2.PERSON_ID=PERSONS.PERSON_ID
where order_proc.ordertime=order_proc2.ordertime

I then get duplicate rows if a person has had multiple "2" orders.
What I would like to see returned is for every proc_code=1 check to see if a 2 has been ordered at the same time as 1 was ordered (though ideally it will be within an hour of one another but I can figure that out later).
The furthest I've made it is with the above code but it isn't showing me all 1 orders when 2 wasn't ordered. The where statement above limits me from doing this because it is only matching where times are equal so I don't see any values returned as 'N'
What I'd like to see based on the above sample data:
PERSON_ID Proc_code Y/N?
123|1|Y
111|1|Y
111|1|N

Is there a way I can do this with a while loop and without duplicates? I'm guessing my duplicates are coming from the second join where I alias order_proc as order_proc2.
I am dealing with millions of rows and joining to several other tables as well to pull different types of information for the Persons and orders. I need to check a huge table (order_proc) whose foreign key is a order_procedure_id. There are several persons each with multiple orders.

Based on what you have shared, this should work:

Table and data

CREATE TABLE #Orders (Personid int, proc_code int, time datetime)

INSERT INTO #Orders VALUES (123, 1,  '4/25/2016 00:01:00')
INSERT INTO #Orders VALUES (123, 2,  '4/25/2016 00:01:00')
INSERT INTO #Orders VALUES (123, 2,  '4/25/2016 00:02:00')
INSERT INTO #Orders VALUES (111, 1,  '4/25/2016 00:01:00')
INSERT INTO #Orders VALUES (111, 1,  '4/25/2016 00:03:00')
INSERT INTO #Orders VALUES (111, 2,  '4/25/2016 00:01:00')

Query

select a.PersonId, a.proc_code,
    CASE WHEN b.proc_code = 2 THEN 'Y' ELSE 'N' END AS 'Y/N'
FROM #Orders a
LEFT JOIN #Orders b
    ON a.time = b.time
    AND a.Personid = b.Personid
    AND a.proc_code != b.proc_code
WHERE a.proc_code = 1

DROP TABLE #Orders


Record not getting filtered using like clause Using below query to filter records:

select * from SearchRecord where searchkeyword1  like  '[%'

Tried this also

select * from SearchRecord where searchkeyword1  like  '%[%'

Records in table contain these values (as per below i need last 2 records, why this simple like won't filter records)

"Affiliate" means
"Agent's Rate of Exchange" means
["Aggregate Total Commitments" means
["Agreed Security Principles" means

Your search pattern contains a wildcard character [,
you should enclose it in brackets or use ESCAPE character like this:

declare @SearchRecord table (searchkeyword1 varchar(100));
insert into @SearchRecord values
('"Affiliate" means'),
('"Agent''''s Rate of Exchange" means'),
('["Aggregate Total Commitments" means'),
('["Agreed Security Principles" means');

select *
from @SearchRecord
where searchkeyword1  like  '![%' ESCAPE '!';

select *
from @SearchRecord
where searchkeyword1  like  '[[]%';

All this is described here:
LIKE (Transact-SQL)


How do I backup/restore database encryption key in order to restore on new server I am currently in the process of restoring a TDE encrypted database to another instance. I have backed up master key and certificate, and have restored them to the new server fine, but I am still incurring the following message -

Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint '0x85187A0021B33712B314113A5F75DBE2EFD118F2'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

TDE was set up on the source with a database encryption key ....

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE TDE_CERT
GO

and can be found under sys.dm_database_encryption_keys

and the thumbprint is of this. I have not restored this to the new server. How do I go about this?

Not understanding how I restore/create this on the new server, or am I going the wrong way about it? Relatively new to dba-ing, so please be gentle.

If you are receiving the thumbprint error the certificate was not created properly from the Source Server's Cert/Key backups.

I found this question which was already answered with a solution for you:

Restore encrypted database to another server


Why is SAN Replication is Causing Random Database Corruption? Having a strange issue with a disaster recovery technology we're trying to implement.  The environment in both datacenters is the same having VMWare and Dell Equallogic SANs, which are the same versions.

When we replicate from one datacenter to another, random databases are getting corrupted somehow and end up in SUSPECT mode.  Each time we try this method, different databases will get corrupted.  Is this a behavior in SQL that is causing this?  Is this the software used in the SAN to replicate causing these errors?

I've been able to change the state of the database to EMERGENCY mode and perform DBCC CHECKDB, but it is a different problem and database each time.  Some of the errors I've found are index issues and data mismatch problems.  I'm still in the process of checking other databases to see if I can find a pattern.  If find other things, I'll be sure to post if it'll help.

I've heard of people implementing this procedure successfully and it is the last task in the project to figure out before we can close the project charter.

I was really hoping that we could just use the built-in features of SQL server like mirroring or AO-AGs.

The versions of SQL are 2008 R2 and 2012.  We're in the process of installing some brand new SQL 2014 servers.  Also, they're all Standard, not Enterprise.

Any input or things I could try would be of great help, thanks in advance!!

Edit#1 8/6/15 12:50 PM CST -
The following are some of the error messages I've found in the Windows Event Viewer, which is more or less what DBCC CHECKDB produced.

  EventID 605 - Attempt to fetch logical page (1:22620) in database 26 failed.  It belongs to allocation unit 72057594239385600 not to 72057594249412608
  EventID 824 - SQL Server detected a logical consistency-based I/O error: incorrect paged (expected 1:1230; actual 0:0).  It occurred during a read of a page (1:1230) in database ID 58 at offset 0x0000000099c000 in file 'D:\Mydatabase.mdf'.  Additional messages in the SQL Server error log or system event log may provide more detail.  This is a severe error condition that threatened database integrity and must be correct immediately.  Complete a full database consistency check (DBCC CHECKDB).
  EventID 7886 - A read operation on a large object failed while sending data to the client.  A common cause for this is if the application is running in READ UNCOMMITTED isolation level.  This connection will be terminated.
  EventID 608 - No catalog entry found for partition ID 72057594383564800 in database 23.  The metadata is inconsistent.  Run DBCC CHECKDB to check for a metadata corruption.

Edit#2 8/6/15 2:24 PM CST - Received info that restoring .bak files of the databases in SUSPECT mode fixes the matter.

in relation to your comment, I suspect a Ops related issue here instead of a SQL Server engine issue.  These SAN devices usually work on the block layer and some manage transaction log/data file sync better than others, as well as other areas.

You can show the ops team that no, SQL Server doesn't randomly corrupt data like this.  You can restore backups to another server, setup mirroring, and all this happens with no corruption.  The minute we do san level replication it happens.  If SQL Server caused corruption like this it wouldn't be around.  SQL Server has almost millions of lines of code dealing with corruption, fixing corruption, and reducing the potential of corruption.  You don't get this issue in any other environment and it only comes up with SAN replication, correct?

Firmware is often a major cause of these types of issues.  Get your Dell support rep on the line, they will have a lot more info and troubleshooting.  Don't settle for a lazy rep, your enterprise's data and time is on the line.  They have a lot of tools that check to see what is causing this in the background and other tools such as DPAC which might help.  This is not a SQL Server engine issue, we will need the full support of Ops.

Edit: If your firmware is out of date or mismatched, get a policy from the Ops team that manages the SAN which states that they will keep the firmware across the stack of the machines they manage up to date.  If this SLA doesn't exist, you should make a note of it to your managers because you're exposed to a lot of other issues besides this one.

I'm assuming you're using SAN block level replication.

It could often also be mismatch in settings.  Maybe different block sizes, etc. but the san os should be able to detect those issues usually.


How can I represent a one-to-many relationship with three tables? I just started using MySQL Workbench drawing an entity-relationship diagram that I previously designed in paper.

I want to represent a conceptual one-to-many relationship by means of three tables. For example, I have entity A and entity B with 1-to-N (for every A I have many Bs).

How can I do that with three tables, one of them being a "middle" table?

Like in the diagram that follows

or like in the diagram below

?

When representing one-to-many (1:n) relationships no "intermediate" table is neccessary. You can simply model that with two tables, one for the 1 cardinality and the other for the n cardinality. The outcome is that one row in the referenced table can be referenced by many rows in the referencing table.

You need an "intermediate" table for representations of many-to-many (n:m) relationships only, because you cannot directly model the situation as described above in both directions.


MySQL data too long error One of the column in my table was initially created as a varchar(1000). Now there is a need to increase the size of that column to hold around 5000 characters. I used the alter statement to increase the size of that column.

DESC table_name as well as SHOW CREATE TABLE table_name clearly indicates the new size of that column to be 5000 characters. However, interestingly when I try to insert any data exceeding 1000 characters ERROR 1406 (22001) : Data too long for column error shows up.

Out of desperation I changed the datatype to text, and still it's limited to 1000 characters. Any suggestion?

I created an other dummy table with a column size of varchar(5000) and that works just fine.

Engine used is InnoDB and Default Charset is UTF8.

I had what appeared to be exactly the same problem.  I had a column that was VARCHAR(30), and I used ALTER TABLE myTable MODIFY COLUMN myColumn VARCHAR(60) NOT NULL, to double its size.  But I kept getting "Error Code: 1406 Data too long for column 'myColumn' at row 1" whenever I tried to insert a value that was > 30 characters.  It seemed, somehow, that mySQL had some "memory" of the previous column size, and was still enforcing it, even though INFORMATION_SCHEMA.COLUMNS showed the column's character_maximum_length as the new value, 60.  I was convinced it was a mySQL bug.

I eventually discovered the actual problem was that this particular table had a trigger on it, whose purpose was to maintain an audit trail of changes, by populating a similarly-defined myTableHistory table.  I had forgotten to do an ALTER TABLE myTableHistory to increase the size of the same column in that table.


Slow query on large table with 50M plus data (especially with larger offset) Scenario

I have a table with 50M plus data.

I need to fetch 4 columns and show on datatable order by 'name_voter' column.

First, I started with basic query such as

 select id,name_voter,home_street_address_1, home_address_city
from base_voter
where deleted_at is null
order by home_street_address_1
offset @pagesize * (@pagenumber - 1) rows
fetch next @pagesize rows only option(recompile)

With the increase in data, it started to become slow. So, I decided to create an index as

create index IX_base_voter_name_voter_asc_deleted_at on base_voter(name_voter asc,deleted_at) include(home_street_address_1, home_address_city) where deleted_at is null

It makes quite faster but not enough, so one of my friends suggest to create additional table as:

create table base_voter_name_voter
(
sr_no bigint not null,
base_voter_id int,
constraint pk_name_voter_sr_no primary key (sr_no)
)
go

and need to run following query on every insertion/deletion in the base_voter table

create procedure procUpdateSortDetails
as
begin
    truncate table base_voter_name_voter
    insert into base_voter_name_voter(sr_no,base_voter_id)
    select row_number() over(order by name_voter asc),id from base_voter with(nolock)
    where deleted_at is null
end
go

After this the execution time is acceptable, but still, I am not convinced to run that query on every update.

My question:

1) Is there any other way to make this faster without updating this additional table on every insertion manually need to be automatically updated something as in index.

2) Isn't index itself should be enough to handle tasks like this? Am I doing it incorrectly or only this much index is capable

3) Is any other way to deal with such problems, to make the query faster even for the last page?

4) If there is also some other way of representing data so that this query might not be needed. Please, mention those.

My data table is something like

name_voter (asc)     |     home_street_address_1    |    home_address_city

Please, suggest some way to tackle this problem.

Try this index:

create index IX_base_voter_home_street_address_1_asc
on base_voter(home_street_address_1 asc)
include(id, name_voter, home_address_city, deleted_at)
where deleted_at is null