SQL query fills up RDS free storage space after upgrade to MySQL 5.7 After the upgrade to MySQL 5.7 the execution of a SQL query fills up the free storage space.

The upgrade was performed through the AWS console, selecting the automatic procedure to upgrade the RDS from MySQL 5.6.27 to MySQL 5.7.11. However, the same query that runs smoothly on mysql5.6 exhaust the available file system space on the mysql5.7 instance.

Researching on the problem, there was identified that the /rdsdbdata/db/innodb/ibtmp1 file was extended up to use all the free storage space. The behaviour is shown on the figures.

An additional comparison of the execution plan between mysql5.6 and mysql5.7 shows differences in the inclusion of 256 million records for 5.7, even after the optimizer_switch parameter was aligned between the two database versions.

Some evidence exhibits a relation to with utilisation of user defined variables, but it is not conclusive. The SELECT statement, for example,  includes a @count := @count + 1 attribute.

Question: How to mitigate the fact that MySQL 5.7 changed the execution plan therefore filling up the available storage space of the RDS instance.

obviously this is an old issue, but we just had a massive problem with this and I thought I'd share:

SELECT queries can write a lot of data to MySQL's temporary file if executed with a 'Using temporary; using filesort;' plan as shown by EXPLAIN EXTENDED.
MySQL can change its execution plan at the drop of a hat based on changing statistics: as a totally not-hypothetical example, it can go from a perfectly reasonable plan using an index to retrieve ~100,000 rows that takes ~15 seconds to a massive on-disk mergesort that takes 45 minutes total :facepalm:.
This temporary table space will be reclaimed automatically on server restart (at least, that's how it worked for us on an AWS RDS MySQL 5.7 instance).

The temp table is usually called ibtmp1, and you can get the size like so:

SELECT *  FROM INFORMATION_SCHEMA.FILES WHERE FILE_NAME LIKE '%ibtmp%' \G

I haven't yet found a way to configure RDS to limit the size of this file.


Database file paths for Named Instances in AAG's I have configured a 2 node AAG setup with named instances say N1 and N2. During the AAG creation and database seeding from N1 , I got an error saying that N2 did not have any folders/path similar to N1 and I could not continue. So I created another Directory path for the database files with the same name as that on Node 1. Will this work? Am I missing anything else? Please clarify

I have configured a 2 node AAG setup with named instances say N1 and N2.

I wouldn't use named instances, default instances will cause less issues in the long run. We'll get to one of those issues...

  During the AAG creation and database seeding from N1 , I got an error saying that N2 did not have any folders/path similar to N1 and I could not continue.

Correct. Since you're using named instances, the installation directory names will be different which is why you're receiving the error. In order for the databases to seed they must have identical folder structures.

  So I created another Directory path for the database files with the same name as that on Node 1. Will this work?

If the folders are identical, then yes. You'll also need to put the proper security on the new folders.


Using the descriptions of the attributes given in the figure, convert the ERD shown in Figure P6.2 into a dependency diagram that is in at least 3NF I have no idea where to begin. Can you please help me?

Start with learning third normal form

http://en.wikipedia.org/wiki/Third_normal_form.

The key part of the third normal form is that every non-key attribute should rely on the key.

For example, fIrst name and last name should only be present in one table so as not to repeat this information for every row in which they attend a presentation. Keep separating attributes out until there is not data repeated in more than one place.

If you program, you might know the adage: DRY or Don't Repeat Yourself. This holds true for data as well as for programming as if you need to change one of the attendees names, you don't want to have to find and replace it in more than one place.


SQL Server 2008 Mirroring Pauses without warning I have several databases mirroring with a witness.  All 3 machines are local to our network and have generous nics.  Monitoring the mirroring, it does not seem like there is a lot of network traffic.

Yet some of the databases' mirroring is pausing without warning.  When I restart them, they pause again almost immediately.

What could be the culprit?

EDIT:

Well, now it looks like they are mirroring again...when I went into mirroring manager and tried restarting them, it succeeded now--whereas yesterday it would automatically pause again.

Anyone know why mirrors would just pause? Wouldn't they just failover?

If you query the sys.database_mirroring catalog view you should find out the status of database mirroring from the mirroring_state field.  Try this:

select
    DB_NAME(database_id),
    mirroring_state
from sys.database_mirroring

When it "pauses", what does the mirroring_state show?  Here is a legend:

0 = Suspended

1 = Disconnected from the other partner

2 = Synchronizing

3 = Pending Failover

4 = Synchronized

5 = The partners are not synchronized. Failover is not possible now.

6 = The partners are synchronized. Failover is potentially possible. For information about the requirements for failover see, Synchronous Database Mirroring (High-Safety Mode).

NULL = Database is inaccessible or is not mirrored.


Oracle SQL Developer: 'All Source Lines' in 'Find Database Object' What's in 'Find Database Object' the difference between

Search for %pattern% AND

All Source Lines

In which area's does Search for %pattern% search for, if not in All Source Lines ...?

I'm going to answer your question based on version 18.2.

Searching source code in an Oracle Database via SQL Developer:

We automatically wildcard your search string - so you can search db object names, like tables named 'EMP' and also look for that string in your database code. We also make sure the case of the text isn't an issue.

Now, what do we consider 'source code?'

All Source Lines: this is the ALL_SOURCE view, in other words, the PL/SQL code you have access to
All Views - the SQL contained in the definition of your View and Materialized Views
All Statements - new for 12cR2 databases, this is the SQL statements collected by PL/Scope via the pl/sql compiler for your pl/sql code
ORDS - the code used to define your RESTful Services

I talk about the search feature here and provide a video demo.


SQL script to count rows for all views in a database with SELECT-only access I need to know the row count for every view in a third-party database that I have been given SELECT-only access to. The following script works for tables but not views:

SELECT      SCHEMA_NAME(A.schema_id) + '.' +
        --A.Name, SUM(B.rows) AS 'RowCount'  Use AVG instead of SUM
          A.Name, AVG(B.rows) AS 'RowCount'
FROM        sys.objects A
INNER JOIN sys.partitions B ON A.object_id = B.object_id
WHERE       A.type = 'U'
GROUP BY    A.schema_id, A.Name
Order by AVG(B.rows) desc
GO

I have looked online but all the suggestions either work only for tables, require me to create tables or procedures (which I can't do with this level of permissions), or require me to count the rows for each view separately, which is infeasible for a database containing a large number of views such as this.

Does anyone have any ideas for a script that would count all of the rows in all of the views in a database without requiring CREATE or EXECUTE permissions?

A few improvements.

QUOTENAME() to thwart bad namers
add schema name, since schema is important
use sys.views instead of sys.objects (and remove type from output)
limit sys.partitions to index_id 0 or 1, to prevent double-counting in the case of an indexed view with additional non-clustered indexes (not sure what you think AVG will accomplish - what if there is an indexed view with a non-clustered filtered index?
use dynamic SQL to avoid manual copy/ manual paste/ manual massage/ manual run

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'UNION ALL
SELECT '
  + 'N''' + QUOTENAME(s.name) + N'.' + QUOTENAME(v.name) + N''',
  FromMetadata = N' + CONVERT(varchar(11),COALESCE(SUM(p.rows),0)) + N',
  TheHarderWay = COUNT(*) FROM ' + QUOTENAME(s.name) + N'.' + QUOTENAME(v.name) +N'
'
FROM sys.views AS v
INNER JOIN sys.schemas AS s
ON v.schema_id = s.schema_id
LEFT OUTER JOIN sys.partitions AS p
ON v.object_id = p.object_id
AND p.index_id IN (0,1)
GROUP BY s.name, v.name
ORDER BY s.name, v.name;

SET @sql = STUFF(@sql, 1, CHARINDEX(N'SELECT', @sql)-1, N'');

PRINT @sql;
--EXEC sys.sp_executesql @sql;

Note that FromMetadata will be 0 unless the view is indexed.


pg_hba.conf entry query I'm trying to formulate a rule from pg_hba.conf file. Thus far I have understood that pg_hba.conf file is used to give access to the specific user for a specific host. But I just want to take a second opinion hence this question.

Given that I defined a set of entry in pg_hba.conf:

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

host    all            all             111.121.10.32/32         md5

Then does Postgres guarantee the only way anyone can connect to Postgres is either using localhost or from 111.121.10.32 and not other IP will be able to access the Postgres server.

Is my understanding correct?

Your understanding is correct. For every connection attempt, each line in pg_hba.conf is checked in turn. The first matching line applies and the connection then either passes with this or fails trying. Much like a firewall. The manual:

  The first record with a matching connection type, client address, requested database, and user name is used to perform authentication. There is no “fall-through” or “backup”: if one record is chosen and the authentication fails, subsequent records are not considered. If no record matches, access is denied.

Bold emphasis mine.


How to make date intervals from a list of date How to make date intervals from a list of date (Group by columns A and C, the date range consists of a continuous date sequence):

I don't have a SQL Server 2005 instance to test against, but this works under SQL Server 2008.  It uses a couple of Common Table Expressions and a Windowing Function

--demo setup
DECLARE @TestData TABLE (
    ColA varchar(10)
    ,ColB datetime
    ,ColC char(1)
    )

insert into @TestData (ColA,ColB,ColC) values ('Cat','2019-01-02','T');
insert into @TestData (ColA,ColB,ColC) values ('Cat','2019-01-03' ,'T');
insert into @TestData (ColA,ColB,ColC) values ('Cat','2019-01-04' ,'G');
insert into @TestData (ColA,ColB,ColC) values ('Cat','2019-01-06' ,'G');
insert into @TestData (ColA,ColB,ColC) values ('Cat','2019-01-07' ,'G');
insert into @TestData (ColA,ColB,ColC) values ('Cat','2019-01-08' ,'G');
insert into @TestData (ColA,ColB,ColC) values ('Cat','2019-02-07' ,'G');
insert into @TestData (ColA,ColB,ColC) values ('Cat','2019-02-08' ,'G');
insert into @TestData (ColA,ColB,ColC) values ('Cat','2019-02-09' ,'T');
insert into @TestData (ColA,ColB,ColC) values ('Dog','2019-03-09' ,'T');
;
--solution
WITH mycte1
AS (
    SELECT *
        ,DATEADD(DAY, - ROW_NUMBER() OVER (
                PARTITION BY ColA,ColC ORDER BY [ColB]
                ), [ColB]) AS grp
    FROM @TestData
    )
,mycte2 as
(
SELECT ColA
    ,min([ColB]) AS [Start]
    ,max([ColB]) AS [End]
    ,ColC
FROM mycte1
GROUP BY ColA,ColC
    ,grp
)
select * from mycte2
ORDER BY ColA, Start

| ColA | Start      | End        | ColC |
|------|------------|------------|------|
| Cat  | 2019-01-02 | 2019-01-03 | T    |
| Cat  | 2019-01-04 | 2019-01-04 | G    |
| Cat  | 2019-01-06 | 2019-01-08 | G    |
| Cat  | 2019-02-07 | 2019-02-08 | G    |
| Cat  | 2019-02-09 | 2019-02-09 | T    |
| Dog  | 2019-03-09 | 2019-03-09 | T    |


Get a column from the latest row by another column Take a look at this sqlfiddle. I have a table that contains history of records for another table(irrelevant, foreign key included). Each history item will contain a status and time fields. I want to write a select statement that would give me a list of unique(by foreign key) histories with the latest status.

From other searches I think what I need is

SELECT MAX(created) as created, originalMsgId
from ReplayHistory
group by originalMsgId

created             Id
------------------------
2018-06-22 19:18:43 11
2018-06-22 09:28:43 12

but it doesn't return the column I need; status.

The query I am using is

SELECT MAX(created) as created, originalMsgId, status
from ReplayHistory
group by originalMsgId, status

which returns me

created              Id  status
----------------------------------
2018-06-22 19:18:43  11  Finished
2018-06-22 19:17:43  11  InProgress
2018-06-22 09:28:43  12  InProgress
2018-06-22 19:16:43  11  NotStarted
2018-06-22 09:27:43  12  NotStarted

I know that this is happening because of group by status, but if I need it in select then I have to keep it in group by.

This is a good use for a window function, row_number()

select
   x.created,
   x.OriginalMsgId,
   x.Status
from (SELECT
         RN = row_number() over (partition by r.originalMsgId order by Created desc),
         r.originalMsgId,
         r.status,
         created
      from ReplayHistory r) x
where x.RN = 1

Another way is using MAX with a derived table.

SELECT
   r.originalMsgId,
   r.status,
   r.created
from ReplayHistory r
inner join
    (select originalMsgId, max(created) dt from ReplayHistory group by originalMsgId) x on
    x.dt = r.created
    and x.originalMsgId = r.originalMsgId

Notes on performance


Modelling a user-defined filter set in SQL In my database I have a table containing data that I need to filter. The exact contents of this table aren't important so I'll just call it data.

Now, my users also need to be able to create filter sets of alerts, which define some sort of problem with the data that they need to detect. For example this might be "select all points where data.value is less than quality 50", or "select a group of consecutive points that all have the same sign", which can be answered with SQL queries. The results of these queries need to always stay up-to-date as new data rows are added to the database.

As far as I can tell, I could model this two ways:

I treat each filter as a row in a filter_definition table that somehow stores the query (as a string), and then also have a filter_result table that is periodically updated by running the filter_definition query (e.g. whenever a new data or filter_definition is created). By having my filters as rows in the database it's very easy for me to create/update/delete filters using my ORM. However this is highly inefficient to update, and also filter_result can become out of date if not updated recently enough.
I treat each filter as a view in the database, each with their own associated SELECT statement. This would be much more efficient, since nothing needs periodic updates, because the view will automatically run the query when accessed. However, allowing my users to create/update/delete a view is much trickier because it's actually deleting an entire database entity each time. In addition, querying all filters (to find all dangerous data points) becomes nearly impossible. I can union all the views for one user, but how do I know which view belongs to each user? Each view is a database entity, not a row, so it can't have a foreign key.

There might be some other solutions I haven't considered as well.

Does anyone have any advice as to how I should solve this issue?

Note: I'm using sqlalchemy as a DBMS-agnostic ORM, so really I want a solution that will work on any DBMS. However Postgres, MySQL and SQLite are my 3 priorities to support.

You shouldn't allow arbitrary queries for these filters. This opens the door to a world of pain, since arbitrary queries can take arbitrarily long and cause arbitrary mischief.

Rather, you should plan for certain types of filters that correspond to pre-defined queries, perhaps with some parameters that can be chosen by the user and are stored in a table.

You can implement these with functions or views.

Whenever a new type of filter is needed, you have to plan a new release.


MySQL speed up aggregation query on table DB: Amazon RDS,
OS: Linux,
2 vCPU,
Memory: 4GB

I have a table with almost 10M rows of data. Below is the table structure:

CREATE TABLE `meterreadings` (
 `Id` bigint(20) NOT NULL AUTO_INCREMENT,
 `meterid` varchar(16) DEFAULT NULL,
 `metervalue` int(11) DEFAULT NULL,
 `date_time` timestamp NULL DEFAULT NULL,
 PRIMARY KEY (`Id`),
 KEY `meterid` (`meterid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

Another table which stores device IDs (around 120 rows of data)

CREATE TABLE `devices` (
`Id` bigint(20) NOT NULL AUTO_INCREMENT,
`meterid` varchar(16) DEFAULT NULL,
`location` varchar(8) DEFAULT NULL,
PRIMARY KEY (`Id`),
UNIQUE KEY `meterid_UNIQUE` (`meterid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

I need to get 15 min aggregated data  for all 'meter ids' within a time frame. I use this query -

SELECT AVG(metervalue) as value
     , DATE_FORMAT(date_time, "%d %b %Y %H:%i") as label
FROM meterreadings
WHERE meterid IN (SELECT meterid from devices)
  AND date_time BETWEEN '2018-06-23' AND '2018-06-24'
GROUP BY DATE(date_time), HOUR(date_time), MINUTE(date_time) DIV 15
ORDER BY date_time ASC;

This takes almost around 30 to 50 seconds to execute.

EXPLAIN on this query returned me this:

'1', 'SIMPLE', 'devices', 'index', 'meterid_UNIQUE', 'meterid_UNIQUE', '19', NULL, '125', 'Using where; Using index; Using temporary; Using filesort'

'1', 'SIMPLE', 'meterreadings', 'ref', 'meterid', 'meterid', '19', 'devices.meterid', '308', 'Using where'

I have 'Id' as the primary key in meterreadings table and there is an index on 'meterid' as well. How can I improve my query speed?

I think the best thing to do is create a generated column and use that as your index. Consider this:

 CREATE TABLE `meterreadings` (
 `Id` bigint(20) NOT NULL AUTO_INCREMENT,
 `meterid` varchar(16) DEFAULT NULL,
 `metervalue` int(11) DEFAULT NULL,
 `date_time` timestamp NULL DEFAULT NULL,
 `fifteen_mins_date` datetime AS
    (concat(DATE_FORMAT(date_time, 'Y-m-d H:'),
            15*(minute(date_time) div 15),
            ':00')) STORED,
 PRIMARY KEY (`Id`),
 KEY `meterid` (`meterid`),
 KEY `fifteen_mins_date` (`fifteen_mins_date`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

Then, your query could look like this:

SELECT AVG(metervalue) as value, DATE_FORMAT(min(date_time), "%d %b %Y %H:%i") as label
FROM meterreadings WHERE meterid IN (SELECT meterid from devices)
   AND fifteen_mins_date BETWEEN '2018-06-23' AND '2018-06-24'
GROUP BY `fifteen_mins_date`
ORDER BY min(date_time) ASC;


Importing CSV file to MySQL via PHPMyAdmin I am currently stuck and unable to upload a complete CSV file into a MySQL database. Unfortunately, the nice folks at Stackoverflow haven't been able to help me solve this issue, so I'm hoping someone here may be able to assist me.

I would like to upload a CSV file into a MySQL database programmatically using PHP and MySQL's LOAD DATA INFILE function. This is the code that is auto-generated from PHPMyAdmin's import page:

"LOAD DATA LOCAL INFILE  '..path/property_re_1.csv' REPLACE INTO TABLE  `markers` FIELDS TERMINATED BY  ',' ENCLOSED BY  '"' ESCAPED BY  '\\' LINES TERMINATED BY  '\r\n'";

The problem is the CSV file only loads until row 44, where there is a text string with quotations.

I have included the actual SQL table and CSV file for debugging purposes since the information used in this case is public information. Feel free to download it:

https://www.dropbox.com/sh/4iq10i51qlqyq8q/UjEQwvXKDA

Ultimately I am looking for a PHP code with the proper SQL function for this task, as I will be running the PHP script routinely to update the database on the server.

Thanks in advance!

What you're looking for is...

ESCAPED BY '"'

In CSV, one way of including literal quotation marks inside a single field is by doubling them up.

In the example file you posted, that's exactly what's happening at line 44.

The rest of your statement looks correct.  Replace the ESCAPED BY part with what I've shown above, and you should be good.

http://dev.mysql.com/doc/refman/5.5/en/load-data.html

  Occurrences of the ENCLOSED BY character preceded by the ESCAPED BY
  character are interpreted as part of the current field value.

...which is exactly what you're needing.


How does TOPN influence a query with group by? I have a query that looks like the following:

Select t1.field1, t2.field2, sum(t1.field3)
From  t1 inner join t2 on t1.id = t2.id
Group by t1.field1, t2.field2

It's simplified, normally there are a few more joins and attributes on it.
My question is now, how much is the impact of adding a TOP N clause to the query instead of stopping the query after N rows are returned to my C# code.

I know, that adding the clause is better for the server, but I'm interested in a rough estimate of how much impact the clause has. My guess is, that because I have an aggregation, the server has to process all rows anyways, therefore the impact is not that high.

Edit:
C# Snippet how we stop the execution

var cmd = new SqlCommand();
cmd.CommandText = sqlStatement;
cmd.Connection = connectionString;
SqlDataReader dr;
dr = cmd.ExecuteReader();
while (dr.HasRows)
{
    while (dr.Read())
    {
        MyDataRow newRow = new MyDataRowDataRow();
        newRow.Parse(dr); //Parse the value from datarow to our format
        result.Add(newRow);
        if (topN.HasValue)
        {
            if (result.Count >= topN.Value)
            {
                break; //stop reading!
            }
        }
    }
    dr.NextResult();
}

My guess is, that because I have an aggregation, the server has to process all rows anyways, therefore the impact is not that high.

Speaking from a SQL Server perspective, it depends. Here is an overview of what it depends on, and why:
Row Goals
Adding a top-level TOP (n) clause (with or without ORDER BY) has the same row goal effect as if a FAST (n) query hint had been specified. A row goal causes the optimizer to estimate plan costs on the basis of returning n rows quickly.
A small row goal therefore favours serial execution using non-blocking (pipelined) operators like (sort-free) merge or nested loops join, and semi-blocking (per group) operators like stream aggregate, over fully blocking operators like sort and eager hash distinct.
Combination operators, like hash join (which is blocking on its hash table build input, and pipelined on its probe input) may be chosen if the build input is expected to be small, and the build input is not expected to rebind much (or at all).
Pipelined execution
Where the logic of the query allows it, and suitable indexes exist, the optimizer may well be able to find a fully pipelined (or perhaps, semi-blocking) execution plan. A semi-blocking plan that blocks per group is potentially just fine, so long as the groups are small.
Even with optimal indexing, in some cases, it may be necessary to express the query using just the right syntax, perhaps also using various query and table hints, to obtain the most pipelined execution plan possible. It can be very tricky to achieve in all but the simplest cases.
A pipelined/semi-blocking plan means the SqlDataReader.Read() can fetch the first and subsequent rows with minimal latency. Essentially, both the C# code and the SQL Server execution engine are streaming rows. This is the case where specifying TOP (n) will make the most difference.
Default strategy
Where TOP (n) is not specified, the query optimizer targets total execution time for the full potential set of rows the query is expected to produce. This change in optimization goal tends to favour parallelism, fully blocking sorts & eager hash aggregation, and partially-blocking hash joins, for example. This would typically produce the full set significantly quicker than running a pipelined/semi-blocking plan (if available) to its final conclusion.
Where one or more blocking operators appears in a plan, the time-to-first-row may be a sizeable fraction of the potential overall execution time. This means the first SqlDataReader.Read() call will block for a long time. Stopping the DataReader after n rows are received by the client will have a relatively minor effect on elapsed time in this case, because the majority of the SQL Server work is already one before the first row becomes available.
Conclusion
How much effect adding TOP (n) has therefore depends at least on:

There being a pipelined execution plan solution (even in principle)
Suitable access methods (indexing)
Query syntax and optimizer features / limitations
The skill level and experience of the query developer

Example
Using the ContosoRetailDW sample dataset, where table FactOnlineSales has 12,627,600 rows. A query based on the template in the question is:
SELECT
    FOS.ProductKey,
    DS.StoreKey,
    SUM(FOS.SalesAmount)
FROM dbo.DimStore AS DS
JOIN dbo.FactOnlineSales AS FOS
    ON DS.StoreKey = FOS.StoreKey
GROUP BY
    FOS.ProductKey,
    DS.StoreKey
ORDER BY
    FOS.ProductKey,
    DS.StoreKey;

Adding a useful index:
CREATE INDEX i
ON dbo.FactOnlineSales
    (ProductKey, StoreKey)
INCLUDE
    (SalesAmount);

The execution plan is:

This runs for 1650ms. Notice the sort and hashing operations.
With TOP (n)
Choosing n = 50 arbitrarily, the query is now:
SELECT TOP (50)
    FOS.ProductKey,
    DS.StoreKey,
    SUM(FOS.SalesAmount)
FROM dbo.DimStore AS DS
JOIN dbo.FactOnlineSales AS FOS
    ON DS.StoreKey = FOS.StoreKey
GROUP BY
    FOS.ProductKey,
    DS.StoreKey
ORDER BY
    FOS.ProductKey,
    DS.StoreKey;

and the execution plan:

This runs for 57ms using pipelined iterators and per-group semi-blocking stream aggregate (instead of blocking eager hash).
Related question: How (and why) does TOP impact an execution plan?


How to display the dates without DUPLICATE and ORDER by MAX DATE I just want to ask that how can I display the null values along with the MAX date.

SELECT
  MEMB.MEMB_N,
  MEMB.LAST_M,
  PrintDate
FROM
  MEMB
  INNER JOIN tblPrint
    ON MEMB.MEMB_N = tblPrint.MEMB_N
ORDER BY tblPrint.`PrintDate` DESC

My desired output will look like this but without duplicates.

This is yet another installment of the MySQL sql_mode = only_full_group_by saga.

<rant>
The problem is that MySQL's default (till 8) will return results which, depending on the order of the records being INSERTed into the table (and/or PRIMARY KEY ordering), will vary and be correct or wrong DEPENDING. This is absolutely the worst sort of difficult to reproduce error that is so typical of MySQL.
<\rant>

However, it is good to see that MySQL's default behaviour is now the correct one (more than 25 years after PostgreSQL, FirebirdSQL, and the major vendors (Oracle, SQL Server &c...)!!).

What you need to do is the following:

-- create the tables - the fiddle (for MySQL version 8 is here)

CREATE TABLE memb
(
  last_m VARCHAR (30),
  CONSTRAINT memb_pk PRIMARY KEY (last_m)
);

-- and

CREATE TABLE tblprint
(
  last_m VARCHAR (30),
  printdate DATETIME NOT NULL,
  CONSTRAINT tp_pk PRIMARY KEY (last_m, printdate),
  CONSTRAINT tp_memb_fk FOREIGN KEY (last_m) REFERENCES memb (last_m)
);

-- see end of this answer for populating these tables with data

I added more records and deliberately inserted them in an order which was not that of the dates either ASCENDING or DESCENDING - see discussion below.

The correct answer is

SELECT
  m.last_m,
  MAX(tp.printdate) AS "p_date"
  -- COALESCE(MAX(tp.printdate), '"NULL"') AS "for_display" -- optional, just for show!
FROM
  tblprint tp
RIGHT JOIN memb m
  ON tp.last_m = m.last_m
GROUP BY last_m
ORDER BY last_m, p_date;

Strictly speaking, the RIGHT JOIN should be a RIGHT OUTER JOIN.

Result:

last_m  p_date
ANDRES, JOSE    2024-07-29 13:33:24
LORISA, MARIA   2021-07-29 13:37:44
SENINA, JUSTINA
TAN, LOLITA

which is the correct result, no matter what the record insertion order is.

Now, there is an alternative fiddle for version 5.6 (available here) which is the closest to 5.5 which you are using. A dbfiddle.com version for 5.5 is available here - it appears to take the first date arbitrarily by PRIMARY KEY order - so it selects the smallest.

As you will be able to see - the defauts of MySQL, the 5.6 version, are much more permissive than 8. You can see that sql_mode = only_full_group_by for version 8. You can set this (HIGHLY RECOMMENDED) for version 5.5. I would also strongly recommend that you upgrade to version 8 - it's been around a while now.

You will also find that if you experiment with the record insertion order, you can get different results in 5.6 - for example, if by coincidence, the data is inserted in reverse date order by last_m into the tblprinter table, you will be able to get the "correct" results from @HiteshRathi 's  query - of course this is very very dangerous!

I recommend that you "play around" with the various queries in the fiddles and see what happens - record insertion order and PRIMARY KEY order are factors to look at. Make sure your queries don't return different results with differing record insertion orders and/or PRIMARY KEY order.

-------------------- Data for the tables ---------------------------

INSERT INTO memb VALUES ('ANDRES, JOSE');
INSERT INTO memb VALUES ('LORISA, MARIA');
INSERT INTO memb VALUES ('SENINA, JUSTINA');
INSERT INTO memb VALUES ('TAN, LOLITA');

INSERT INTO tblprint VALUES ('ANDRES, JOSE', '2019-07-29 13:39:44');
INSERT INTO tblprint VALUES ('ANDRES, JOSE', '2001-09-01 23:33:41');
INSERT INTO tblprint VALUES ('ANDRES, JOSE', '2002-07-29 13:33:42');
INSERT INTO tblprint VALUES ('ANDRES, JOSE', '2003-07-29 13:33:43');
INSERT INTO tblprint VALUES ('ANDRES, JOSE', '2024-07-29 13:33:24');
INSERT INTO tblprint VALUES ('ANDRES, JOSE', '2020-07-29 20:20:20');

INSERT INTO tblprint VALUES ('LORISA, MARIA', '2014-07-29 13:34:44');
INSERT INTO tblprint VALUES ('LORISA, MARIA', '2007-07-29 13:31:44');
INSERT INTO tblprint VALUES ('LORISA, MARIA', '2008-07-29 13:32:44');
INSERT INTO tblprint VALUES ('LORISA, MARIA', '2009-07-29 13:33:44');
INSERT INTO tblprint VALUES ('LORISA, MARIA', '2018-07-29 13:35:44');
INSERT INTO tblprint VALUES ('LORISA, MARIA', '2021-07-29 13:37:44');
INSERT INTO tblprint VALUES ('LORISA, MARIA', '2020-07-29 13:36:44');


query errors when all data not present I have a working query that show success rate of staff.  When I run it in for all staff it works flawlessly.
However, when I insert it as a function within a java based page I get an error:

ERROR: division by zero

Here is the query and what I think the actual problem is:

SELECT st.staff_id,
  round((count(s.code IN ('10401','10402','10403') OR NULL) * 100.0) / count(*), 1) AS successes
  FROM   notes n
  JOIN   services s  ON s.zzud_service = n.zrud_service
  WHERE n.zrud_staff = ? AND n.date_service >= DATE '07/01/2013'
    AND n.date_service <= CURRENT_DATE
    AND  s.code IN ('10401','10402','10403','10405')
  GROUP  BY st.staff_id

The 10401, 10402, 10403 codes reflect successful discharges.
The 10405 reflect unsuccessful discharges.

If a staff has no notes (new staff for example) I get the division by zero error.

So I believe if a staff does not have at least one entry for one of the codes, it will error.  This prevents them from logging in.

I read of an operator called NULLIF() that would prevent this, but I do not have any idea how to insert it, and, if this will actually work.

Can anyone comment?

count(*)

should become

coalesce(count(*), 1)

for the simplest case. The denominator doesn't actually matter because 0/anything is always zero.

Also:

s.code IN ('10401','10402','10403') OR NULL

is nonsense. count(x OR NULL) is exactly the same as count(x). NULL is false in boolean context, x OR NULL is the same as just saying x. So just write:

s.code IN ('10401','10402','10403')

If what you're actually trying to say is "count rows that are either one of the listed values or are null" then you need a coalesce. You can't just use IN (..., NULL) since x IN (..., NULL) is always either true or NULL, since x IN (...) is equivalent to x = ANY (...) and x = NULL is NULL. So you must write:

s.code IN ('10401','10402','10403') OR s.code IS NULL


TSQL to find if server is listening on dynamic or static port Using TSQL, how can I find if the MS SQL server (any version) is configured to use static port or is it using dynamic port?

Script i am trying to use:

DECLARE @TcpPort VARCHAR(5)
        ,@RegKey VARCHAR(100)

IF @@SERVICENAME !='MSSQLSERVER'
    BEGIN
        SET @RegKey = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @@SERVICENAME + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'
    END
    ELSE
    BEGIN
        SET @RegKey = 'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP\IPAll'
    END

EXEC master..xp_regread
    @rootkey = 'HKEY_LOCAL_MACHINE'
    ,@key = @RegKey
    ,@value_name = 'TcpPort'
    ,@value = @TcpPort OUTPUT

EXEC master..xp_regread
    @rootkey = 'HKEY_LOCAL_MACHINE'
    ,@key = @RegKey
    ,@value_name = 'TcpDynamicPorts'
    ,@value = @TcpPort OUTPUT

SELECT @TcpPort AS PortNumber
        ,@@SERVERNAME AS ServerName
        ,@@SERVICENAME AS ServiceName

You can determine which port an instance is using from the error log but not whether its static or dynamic.

EXEC xp_readerrorlog 1, 1, 'Server is listening'

Alternative would be to read from the registry via xp_regread.

DECLARE
    @StaticPort NVARCHAR(512)
    , @DynamicPort NVARCHAR(512)

EXEC xp_regread
    'HKEY_LOCAL_MACHINE'
    , 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'
    , 'TcpPort'
    , @StaticPort OUTPUT

EXEC xp_regread
    'HKEY_LOCAL_MACHINE'
    , 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'
    , 'TcpDynamicPorts'
    , @DynamicPort OUTPUT

SELECT
    @StaticPort AS StaticPort
    , @DynamicPort AS DynamicPort


What's the difference between betwwen 2 DB and 2 Schema My question is : what is the difference between the two next mode in PostgreSQL, for an production application.

using two DB in the same SGBD (the same table-space and HDD)
Or using two schema in the same DB

What about response time & performance ?

By default, PostgreSQL's databases cannot see each other and you cannot query between them.

You can query between, or have foreign keys between PostgreSQL schemas.

If you want strong multi-tenant client segregation, use distinct databases.


Workaround to avoid read-only table locking during execution of MySql OPTIMIZE command We need to optimize a large table (on a weekly basis) in order to reclaim disk space, since records are being deleted regularly from the table.

The table in question is an InnoDB table and the innodb_file_per_table option was (and still is) enabled during table creation.

As the manual notes:

Note that MySQL locks the table during the time OPTIMIZE TABLE is running.

We cannot afford the table being locked, since new records are inserted continuously in its production environment.

Hopefully there exists some workaround for the obvious problem; a reliable way to OPTIMIZE the table while dealing with the locking problem, that have been used in a production environment.

Maybe creating a temporary table and renaming it, but without the possibility of losing new records while the copying is being performed?

Install percona-toolkit. Then you can use pt-online-schema-change.

I usually use it when I have to alter tables. What it does is copy the data from your current table to another table. Triggers are automatically created, that copy incoming data to the copy while copying. This only works flawlessly if you don't have triggers on the table already. After that, the copied table gets renamed to the original table (which gets dropped, unless you specify not to with parameters). Then you'll have your optimized table.

  For InnoDB tables, OPTIMIZE TABLE is mapped to ALTER TABLE, which rebuilds the table to update index statistics and free unused space in the clustered index.

So an

OPTIMIZE TABLE tablename;

is actually

ALTER TABLE tablename ENGINE = InnoDB;

Then use pt-online-schema-change like this (on command line, not in MySQL client):

pt-online-schema-change --dry-run --user=root --host=localhost  --alter "ENGINE=InnoDB" --alter-foreign-keys-method=auto D=databasename,t=tablename,p=userpassword

If this runs well, change --dry-run to --execute.


UPDATED: Relational (or NoSQL) Database Design (Updated this question with more specifics. Decided on a Relational Database, and would love some comments on its design)
This database holds assembly instructions data for shipbuilding, later use in a Augmented Reality Android application. The application asks the server (Node.JS) for the necessary files.
Simply put: Work Packages contain several Parts that must be mounted, each of these parts has the Files necessary needed by the  application.

Work Packages depend on other Work Packages (WD_Dependencies), in the figure above, S1 depends on 1 and 2;
Workers can be assigned a specific Work Package, if not, the return Work Package is a combination of matches between Type and Department.

Update:
(Open in new tab for better view)

The database is "divided" between the blueprint of a project, and the specific execution of that blueprint. This also allows for multiple executions of the same blueprint.
Questions:

Is this a good design? Dividing Blueprint and Projects?
Efficient SQL Query to recursive lookup all the dependencies of a Work Package?
Do you see any problems with the relationships or any redundancy?
Any comments or help are welcomed.

I understand this could be a broad/opinion question, but I have no experience with database of this size, and would appreciate the help.

Which of the two options is the best?

The relational design is clearly superior to the hierarchical design used in the NoSQL example.  A database schema designed using relational principles does not favor one access path over another.  Each table represents a real world entity type and through the use of relational algebra queries of arbitrary complexity can be constructed to answer not only current questions of the data but any future questions you think of that haven't yet been conceived.  A hierarchical schema imposes a single structure upon the data - in this case work packages contain other work packages, the worker, and the parts, and the files.  If you want to ask questions of the data not aligned with this single view it will be much more difficult if not impossible.  Furthermore, the relational model includes a data integrity component to ensure data is consistent with respect to rules declared to it approximating the real world, whereas systems, like a NoSQL system, using a hierarchical or network model do not provide these.

  Should a Work Package reference the above Work Packages, or the contrary is better?

The best way to implement this is to create a new table that represents the links between the parent and dependent work packages while leaving the work package table to represent the work packages themselves regardless of dependency.  This design does not mix work packages dependent on other work packages with work packages that are not dependent on others.  A key advantage of the relational approach is that a single structure with a single set of operators - relational tables and relational algebra - can easily represent a hierarchy or a network.  Network DBMS's require two types - a node and a link - with two distinct sets of operators, and thus twice the complexity.  A hierarchical DBMS can't represent a network correctly at all.  This is a key reason why the NoSQL systems available today can only address a narrow set of use cases.  Fabian Pascal's book Practical Issues in Database Management, Chapter 7, gives an excellent overview of data hierarchies using SQL and I highly recommend it.

  There will be a substantial of reads, however the writes will be considerable less.

Another benefit of the relational approach is that the performance of the system can be managed to a great extent at the physical level without impacting the logical database schema used by applications to work with the data.  With the relational approach you can focus on the correct logical schema first and then in most cases let the DBA implement a correct physical design and infrastructure to best support the read heavy workload.  This is not the case in navigational systems where the programmer must specify the access paths directly in programs.


Enable query store for all databases I want to start using Query Store with suggested setting by Erin Stellato.
USE [master]
GO
ALTER DATABASE DatabaseName SET QUERY_STORE = ON
GO
ALTER DATABASE DatabaseName SET QUERY_STORE (OPERATION_MODE = READ_WRITE
, INTERVAL_LENGTH_MINUTES = 30
, MAX_STORAGE_SIZE_MB = 1000
, QUERY_CAPTURE_MODE = AUTO)
GO

But that is a by database setting, and I want to be able to activate on all the databases of an instance at once.
I found SET RECOVERY Model Using sp_msforeachdb (at serverfault)
I can use the OP's solution
EXECUTE master.sys.sp_MSforeachdb 'USE [?];
IF DB_ID() >= 5
ALTER DATABASE [?] SET QUERY_STORE = ON
ALTER DATABASE [?] SET QUERY_STORE (OPERATION_MODE = READ_WRITE
, INTERVAL_LENGTH_MINUTES = 30
, MAX_STORAGE_SIZE_MB = 1000
, QUERY_CAPTURE_MODE = AUTO)
'

While it works I get the errors

Msg 12438, Level 16, State 1, Line 41
Cannot perform action because Query Store cannot be enabled on system database master.
Msg 5069, Level 16, State 1, Line 41
ALTER DATABASE statement failed.
Msg 12438, Level 16, State 1, Line 41
Cannot perform action because Query Store cannot be enabled on system database tempdb.
Msg 5069, Level 16, State 1, Line 41
ALTER DATABASE statement failed.

I tried several variations of the solution by Jason Cumberland but could not get it working.
set quoted_identifier on

EXECUTE master.sys.sp_MSforeachdb '
IF '?' not in ('tempdb','master','model')
begin
    exec (ALTER DATABASE [?] SET QUERY_STORE = ON
ALTER DATABASE [?] SET QUERY_STORE (OPERATION_MODE = READ_WRITE
, INTERVAL_LENGTH_MINUTES = 30
, MAX_STORAGE_SIZE_MB = 1000
, QUERY_CAPTURE_MODE = AUTO))
end
'

I am getting the error

Msg 102, Level 15, State 1, Line 25
Incorrect syntax near '?'.

These two work
  --Turn off on all databases
  EXECUTE master.sys.sp_MSforeachdb 'USE [?]; ALTER DATABASE [?] SET QUERY_STORE = OFF'

and
--Check all Query Store database settings at once
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; SELECT * FROM sys.database_query_store_options'

Question: How can I turn on Query Store on all the database of an instance without getting an error message?
Note: of the system databases msdb is the only one that that allows query store.  I am not excluding it currently.
I am using SSMS 17.4 against, SQL instance running SQL 2016 (SP1)

In both cases for the attempted queries in the question, the problems are simply syntactical.

In the first case: You have an IF condition, but do not group the statements after it together, so only the statement immediately following the IF is conditional. The second ALTER DATABASE statement always executes. To fix, do this:

EXECUTE master.sys.sp_MSforeachdb N'USE [?];
IF DB_ID() >= 5
BEGIN
  ALTER DATABASE [?] SET QUERY_STORE = ON;
  ALTER DATABASE [?] SET QUERY_STORE
   (OPERATION_MODE = READ_WRITE,
    INTERVAL_LENGTH_MINUTES = 30,
    MAX_STORAGE_SIZE_MB = 1000,
    QUERY_CAPTURE_MODE = AUTO);
END;
';

You probably also no not need the USE statement. It could be removed and then the DB_ID() could be changed into DB_ID(N''?'').

In the second case: You did not escape the embedded single-quotes. You also had an extraneous EXEC, and you did not include the msdb system database. To fix all of that, do the following:

EXECUTE master.sys.sp_MSforeachdb N'
IF (N''?'' NOT IN (N''tempdb'', N''master'', N''model'', N''msdb''))
BEGIN
   ALTER DATABASE [?] SET QUERY_STORE = ON;
   ALTER DATABASE [?] SET QUERY_STORE
    (OPERATION_MODE = READ_WRITE,
     INTERVAL_LENGTH_MINUTES = 30,
     MAX_STORAGE_SIZE_MB = 1000,
     QUERY_CAPTURE_MODE = AUTO);
END;
';

PLEASE NOTE: @Kris is not incorrect in saying that "sp_MSForeachDB is undocumented and unsupported", and the advice to use another mechanism to cycle through DBs is good advice. It might not be worth the trouble if this is a one-time task, but if this code is to be used repeatedly, then yes, you should probably heed that warning.


Concatenate in MS Access - how to omit space and comma if first value is missing? I am concatenating 2 values in a MS Access Report: "Method" and "Experiment" and they are to be separated by a comma (,).

I have figured out how to concatenate succesfully when the second value is missing (Experiment), but I am struggling to omit a blank space and comma if the first value ("Method") is missing.

The code I am using is:
=([Method] & (", "+[Experiment]))

What I would like to obtain is:

Harvard, Mixed sequence
Intensity
Bradford

Thank you Akina for your suggestion.

As I am really a newbie, it took me some time to work out how to use IFF.

At the end here's the working string to remove comma and space at the beginning if the first value is null (empty):

=([Method].value) & iif (IsNull([Method].value) ,"",", ") & + [Experiment]


SQL Server. Do reads on Read-Only Replica in Availability Group fire Auto Update Statistics on Primary? We have MS SQL Server 2014 Enterprise Edition with an Availability Group with Readable Secondary. Our application reads data only from Secondary.

As far as I know, auto update statistics starts when reads (SELECT and maybe something else) performed. But our reads execute only on Secondary. Does it fire Auto Update Stats on Primary?

Thanks in advance.

P.S. And please, excuse my poor English.

As far as I know, auto update statistics starts when reads (SELECT and
  maybe something else) performed. But our reads execute only on
  Secondary. Does it fire Auto Update Stats on Primary?

No it does not fire any auto update statistics on the primary.

However

In Read only databases, regular permanent statistics can be leveraged to satisfy query plans. If sql server needs new statistics for the read only db or these permanent statistics in the database have become stale, then temporary statistics can be created / updated. These statistics reside in TempDB and are managed by SQL Server (you can only drop them).

There are two kinds of temporary statistics, these that get created due to missing statistics and those that get 'updated'.

Reducing the creation of temporary statistics

The creation of temporary statistics can be removed by adding the statistics manually on the primary database, generating the estimated execution plans of the reporting queries (also on the primary instance/db) or running the queries.

Reducing the updates of permanent to temporary statistics

Permanent statistics can be updated and 'transformed' to temporary statistics on a read only database.

The temporary stat updates can be resolved by updating your statistics on the primary db more often.

As a side note, on Standby / Read Only (log shipping) databases you would have the option to run ALTER DATABASE [ReadOnly] SET AUTO_UPDATE_STATISTICS OFF; to disable the temp stat updates.
This is not possible for database part of an Always On Availability Group.

These stat creates / updates can become expensive depending on the amount of data and sample rates.

Parts of this answer have been taken from a previous post of mine


Query optimizer triggering statistics update seems to be slowing down queries We are running SQL Server 2016 and have a very simple stored procedure that does a select using a clustered index seek of a single row in a table with 140 million rows. In that table, we keep around the last 6 months of data, with roughly the same number of rows per day. On a daily basis, we delete the oldest day and insert the most recent day. The select stored procedure has isolation level set to read uncommitted (which I understand has its own issues, but we felt the risk of blocking was greater than the risk/likelihood of reading incomplete data).
Recently, the select query has been timing out at 30 seconds (a timeout value we set - not sure how long the query would have actually taken).  The issue ultimately resolves itself anywhere between 30-120 seconds later.
I explored various possible causes, but my current theory is that the query optimizer detects out of date statistics, triggering a stats update on the table, which it waits to complete, and then creates an execution plan once the stats have been updated. This was confirmed by looking at the date/time the stats were last updated, which coincides exactly with the query slowdown.
Side note: the first time this issue occurred, it coincided with our daily insert of data. The second time it occurred, it didn't seem to coincide with the insert or delete. But both times, the slowdown coincided with a stats update. I'm not sure why the stats became outdated the second time.
I see a couple options here:

set auto stats update async so that the query optimizer doesn't wait for the stats update to complete
disable auto stats update and manually update stats nightly (would also probably specify a query hint in the stored procedure to use the clustered index, but not sure if that's necessary).

So, does my theory for the cause sound reasonable?
Which of my options is best?
Are there other options?

So its not that you get a bad plan, your issue is that you have somebody becoming a victim of the auto-stats, right?
Since you probably know what table(s) we're talking about, I suggest you disable auto-update at the table (or even stats) level. You can do this using sp_autostats. Now you don't affect the rest of the database. And of course handle the stats update on this stats yourself.
This assumes that you get OK plans even when stats are a bit out of whack. If not, then async can be an option, but you won't have control over when the load of this stats update will take place.
I wouldn't go the hint route unless you identify that you indeed get bad plans with old stats. So consider the plan quality a different aspect. They are related of course, but out-of-date stats doesn't necessarily mean bad plans.
If it is helpful, you can capture when auto-update stats happens with an XE trace. Either just count how many you have over a day, say. That wuuld be using the Event Counter XE target. Or capture the actual event (to a file target) if you need more details than "I had this many in total over the time of the trace running".
For instance, I have my "Looking for strange" trace for things like this (just counting how many - then modify the trace as you want to limit the events and/or add a file target).


Updating table based on conditions (overlapping dates) Say I have a table name CustomerBooking which contains Customers, checkInDate and checkOutDate as follows. I would like to achieve the outcome as shown in condition 1 and condition 2 with update statement.

I have tried some solutions online but to no avail that it does not include NULL as infinite date in overlapping (for condition 1) and/or only takes the max date of overlapping date into account instead of taking max date from last row of overlapping dates. (for condition 2).

Do take in mind that condition 1 and condition 2 are two independent separate outcome that should be achieved with update statement based on the first table.

ID  CustomerID CheckInDate CheckOutDate
1   1          2015-04-02  2015-04-05
2   2          2015-03-04  2015-05-02
3   1          2015-04-01  NULL(which represents infinite date)
4   1          2015-03-04  2015-05-03
5   1          2015-01-03  2015-02-03

Condition 1: Merge the overlapping date of same member ID (take earliest start date and max end date of overlapping dates) and update the table.

Note: Row with ID 1, ID 3 and ID 4 has overlapped dates. Therefore we combine these 3 rows to CheckInDate(2015-03-04) and CheckOutDate(NULL) by taking the min date and max date of overlapping dates. Only row with ID 1 is updated with the new dates and remaining ID 3 and ID 4 date is set to null. Rows with ID 2 and ID 5 do not have overlapping dates of same member ID therefore it remains as it is.

Final result is as shown below:

ID CustomerID CheckInDate CheckOutDate
1   1         2015-03-04  NULL
2   2         2015-03-04  2015-05-02
3   1         NULL        NULL
4   1         NULL        NULL
5   1         2015-01-03  2015-02-03

Condition 2: Merge the overlapping date of same member ID (take earliest start date and last end date(largest ID) of overlapping dates) and update the table.

Similar as condition 1, but this time we take the min date of overlapping dates (2015-03-04) and the check out date of the last row (row with ID 4) of overlappings date (which if 2015-05-03).

Final result is as shown below:

ID CustomerID CheckInDate CheckOutDate
1   1         2015-03-04   2015-05-03
2   2         2015-03-04   2015-05-02
3   1         NULL              NULL
4   1         NULL               NULL
5   1         2015-01-03  2015-02-03

Condition 1:

WITH ord as (
    SELECT ID, CustomerID, CheckInDate, CheckOutDate
        , n = ROW_NUMBER() over(partition by [CustomerID] order by [CheckInDate], [CheckOutDate])
    FROM @data d1
), first as (
    SELECT o1.ID, o1.CustomerID, o1.CheckInDate, o1.CheckOutDate, o1.n
        , m = ROW_NUMBER() over(partition by o1.[CustomerID] order by o1.[CheckInDate], o1.[CheckOutDate])
    FROM ord o1
    INNER JOIN ord o2 ON o1.CustomerID = o2.CustomerID AND o2.n+1 = o1.n AND o1.CheckInDate > o2.CheckOutDate
), groups as (
    SELECT o.ID, o.CustomerID, nx = MIN(coalesce(f.n, 1)), n = MAX(o.n)
        , p = ROW_NUMBER() over(partition by o.CustomerID, MIN(coalesce(f.n, 1)) ORDER BY o.ID)
    FROM ord o
    LEFT JOIN first f ON o.CustomerID = f.CustomerID AND o.n < f.n
    GROUP BY o.ID, o.CustomerID
), dates as (
    SELECT g.CustomerID, g.nx, CheckInDate = MIN(o.CheckInDate)
        , CheckOutDate = CASE WHEN SUM(CASE WHEN o.CheckOutDate IS NULL THEN 1 END) IS NULL THEN MAX(o.CheckOutDate) END
    FROM groups g
    INNER JOIN ord o ON g.ID = o.ID
    GROUP BY g.nx, g.CustomerID
    HAVING COUNT(g.nx) > 1
)
SELECT o.ID, o.CustomerID
    , CheckInDate = CASE WHEN g.p = 1 THEN d.CheckInDate END
    , CheckOutDate = CASE WHEN g.p = 1 THEN d.CheckOutDate END
FROM groups g
INNER JOIN ord o ON g.ID = o.ID
INNER JOIN dates d on g.CustomerID = d.CustomerID AND g.nx = d.nx
ORDER BY ID

This query output the rows that must be updated:

ord = I first partition by CustomerID and order by CheckInDate
first = I join consecutive rows from previous CTE where the next CheckInDate does not overlap previous CheckInDate and CheckDate and I partition and order them
groups = I group by the previous partitioned number in order to know to which group a row belongs
dates = I join with the original data in order to get the first and last date for each groups. Groups with only 1 row are removed
main select output the dates for p=1 or NULL otherwise

Output:

ID  CustomerID  CheckInDate CheckOutDate
1   1           2015-03-04  NULL
3   1           NULL        NULL
4   1           NULL        NULL

Condition 2:

WITH ord as (
    SELECT ID, CustomerID, CheckInDate, CheckOutDate
        , n = ROW_NUMBER() over(partition by [CustomerID] order by [CheckInDate], [CheckOutDate])
    FROM @data d1
), first as (
    SELECT o1.ID, o1.CustomerID, o1.CheckInDate, o1.CheckOutDate, o1.n
        , m = ROW_NUMBER() over(partition by o1.[CustomerID] order by o1.[CheckInDate], o1.[CheckOutDate])
    FROM ord o1
    INNER JOIN ord o2 ON o1.CustomerID = o2.CustomerID AND o2.n+1 = o1.n AND o1.CheckInDate > o2.CheckOutDate
), groups as (
    SELECT o.ID, o.CustomerID, nx = MIN(coalesce(f.n, 1)), n = MAX(o.n)
        , p = ROW_NUMBER() over(partition by o.CustomerID, MIN(coalesce(f.n, 1)) ORDER BY o.ID)
        , last = ROW_NUMBER() over(partition by o.CustomerID, MIN(coalesce(f.n, 1)) ORDER BY o.ID DESC)
    FROM ord o
    LEFT JOIN first f ON o.CustomerID = f.CustomerID AND o.n < f.n
    GROUP BY o.ID, o.CustomerID
), dates as (
    SELECT g.CustomerID, g.nx, CheckInDate = MIN(o.CheckInDate)
        , CheckOutDate = MAX(o2.CheckOutDate)
    FROM groups g
    INNER JOIN ord o ON g.ID = o.ID
    INNER JOIN (SELECT ID, CustomerID, nx FROM groups WHERE last = 1) l ON g.CustomerID = l.CustomerID AND g.nx = l.nx
    INNER JOIN ord o2 ON l.ID = o2.ID
    GROUP BY g.nx, g.CustomerID
    HAVING COUNT(g.nx) > 1
)
SELECT o.ID, o.CustomerID
    , CheckInDate = CASE WHEN g.p = 1 THEN d.CheckInDate END
    , CheckOutDate = CASE WHEN g.p = 1 THEN d.CheckOutDate END
FROM groups g
INNER JOIN ord o ON g.ID = o.ID
INNER JOIN dates d on g.CustomerID = d.CustomerID AND g.nx = d.nx
ORDER BY ID

Output:

ID  CustomerID  CheckInDate CheckOutDate
1   1           2015-03-04  2015-05-03
3   1           NULL        NULL
4   1           NULL        NULL

For Updates, replace SELECT by UPDATE:

UPDATE g SET
    CheckInDate = CASE WHEN g.p = 1 THEN d.CheckInDate END
    , CheckOutDate = CASE WHEN g.p = 1 THEN d.CheckOutDate END
FROM ...

Your data:

declare @data table([ID] int, [CustomerID] int, [CheckInDate] date, [CheckOutDate] date);
Insert into @data([ID], [CustomerID], [CheckInDate], [CheckOutDate])
VALUES
    (1, 1, '2015-04-02', '2015-04-05'),
    (2, 2, '2015-03-04', '2015-05-02'),
    (3, 1, '2015-04-01', NULL),
    (4, 1, '2015-03-04', '2015-05-03'),
    (5, 1, '2015-01-03', '2015-02-03')
;

It works as well with this sample:

(1, 1, '2015-04-02', '2015-04-05'),
(2, 2, '2015-03-04', '2015-05-02'),
(3, 1, '2015-04-01', NULL),
(4, 1, '2015-03-04', '2015-05-03'),
(5, 1, '2015-01-03', '2015-02-03'),
(6, 1, '2015-01-02', '2015-02-03'),
(7, 1, '2015-03-04', '2015-03-06'),
(8, 1, '2015-03-04', '2015-05-06'),
(9, 1, '2014-04-02', '2014-04-05'),
(10, 1, '2014-03-04', '2014-05-02')

If it does not work with some of your data, update Input and Output table with more relevant values.


TimeStamp Column Auto Update Upon Insert and Modification I have been assigned with a task that is update Timestamp column of a table upon each insert or modification of table  data. As per my kowledge this can posssible in case of inserts using default getdate() for the column but NOT modification without trigger.
I am looking for help on a mechanism to update timestamp column upon modification of table data without trigger, of course if possible in SQL Server.
Thanks in advance

Here is a list of automated features you can use to accomplish this, without using Triggers:

Temporal Tables - System versioned copies of the user defined tables that track changes.

Change Tracking - Automated tracking of changes for your user defined tables.

Change Data Capture - Keeps track of DML changes to the specified user defined tables.

Audit - Automatically tracks a multitude of actions at the server level.


to what "scale" of applications does nosql support? Recently i came across the Nosql database concept, though i learnt how to and why of it, i did not get a proper answer to the question , to what scale of project does it support?

Will it support larger application which generally has lot of statistical usage, like google analytics (example)?
Are their solid data or application that are already running on such database like couchbase etc?

This question is really far too vague to answer effectively.  There are dozens of "NoSQL" data stores out there which have various use cases.  Here is a 10,000 foot view of what's out there.

In my mind, there are basically 3 main categories of NoSQL data stores commonly used, key/value stores, document databases, and big data (hadoop).  This is a somewhat artificial designation and many of these products can arguably cross into multiple areas.  There are some other categories, such as graph databases, which are more specialized towards a specific problem and I am not going to discuss them here as I have no expertise about them.

Most NoSQL databases are simple key/value stores which are very fast when retrieving named keys.  They are particularly inefficient if you need to scan or aggregate over sets of data.  Examples of k/v stores are memcached, Riak, Redis, CouchBase,  Voldemort, and Amazon DynamoDB.  With the HandlerSocket plugin (built into Percona Server), even MySQL can be used as a very fast k/v store.  Each of these k/v stores have different feature sets designed to solve different problems.  Very few of them are suitable as the authoritative/primary data store for an application because of how difficult and inefficient it is to perform set operations.  These are mostly used as caching layers or storing very specialized data that does not require relational operations.

Another general class of NoSQL databases are document stores.  Examples include MongoDB and Cassandra.  These types of data stores store more structured data than k/v stores and often have a more capable query language.  They have flexible "schemas" that make it possible to keep completely different sets of data from one row to the next.

Finally, you get to the true "Big Data" stores of which Hadoop and it's related query languages, pig, hive (a SQL interface to hadoop), and hbase (a real-time data store on top of hadoop/HDFS) is king.  With the exception of hbase, Hadoop-based data stores tend to be built for offline processing of truly enormous data sets across hundreds of machines.

As a side note, what drives me absolutely batty about "NoSQL" is that it has literally nothing to do with the SQL language.  NoSQL is about reinventing the data storage layer and making it more "scalable" (another vague, misunderstood term) and highly available.  The query language is irrelevant in most cases and some of these data stores have produced just horribly ugly ways of performing even the most simple operations.  SQL could be used as the access language to most these data stores had the developers made that choice - take a look at VoltDB, MySQL Cluster, or Hive for examples of distributed SQL databases that have "NoSQL" features.  When treated like a key/value store, MySQL with InnoDB is actually incredibly fast at primary key lookups (SELECT value FROM table WHERE key = ?) and it would be relatively easy to create a client library that creates a consistent hashing scheme to build a distributed MySQL cluster as one would use Riak, Redis, or memcached.

Bottom line is that you'll have to be more explicit about your needs if you want any more detail than that.  Here are just a small subset of the questions that you will need to answer in order to even limit the field:

Is your access pattern realtime (OLTP) or will it be performed in batch operations (OLAP)?
Do you need to perform aggregate or set-based calculations on the data, or is it simply accessing keys by name?
How much data do you have and how is it structured?
Have you determined that a traditional SQL database will not suit your needs?
What are your CAP priorities?
Do you require ACID features?
What kind of operations do you need to perform on the data?

I hope that this helps you a bit in your research.


Backup a portion of the database? I have a database that is over 50Gb. I'd like to take a backup of this database, but I don't want a full backup. I need to extract some of the data for a development test db. Is this possible?

Using the SQL Server management console, you can choose the database and Export option to export the table definition and data. Then, you can import this data to your test data base.

Hope you have already created the test database manually. While importing data, it will automatically create the tables, if they are not already exists.


Maximum or suggested number of bound query-parameters in InnoDB (MySQL)? We have code that adds thousands of parameters to a query as members of an IN (...) criterion. My aesthetics say to bucket it into multiple queries, but I want an objective reason.

Does InnoDB have a maximum number of parameters per query? Does it hurt performance to have over a certain number?

With that many values you are likely to run into estimation errors during query plan generation. This will lead to it choosing worse (slower-performing) plans than it might otherwise.

With that many values I would suggest INSERTing them into an actual table, with index(es), which is JOINed to in the query. Logically the two are the same. Practically, aesthetically and performance wise it is likely to be better, even with the additional writes.


Query Slowness Despite Index Problem

I need to create a chart of User Retention over time, similar to this:

Ignoring the percenages for a minute, I have a query that shows unique users for a given "cohort" and then the number of users that come back. However, with the volume of data we've acquired over the last few weeks, the query no longer finishes.

Query

;WITH dates AS
(
-- Set up the date range
SELECT convert(date,GETDATE()) as dt, 1 as Id
UNION ALL
SELECT DATEADD(dd,-1,dt),dates.Id - 1
FROM dates
WHERE Id >= -84
)
, cohort as (
-- create the cohorts
SELECT dt AS StartDate,
    convert(date,CASE WHEN DATEADD(DD, 6, dt) > convert(date,GETDATE()) THEN convert(date,GETDATE()) ELSE DATEADD(DD, 6, dt) END) as EndDate,
    CONCAT(FORMAT(dt, 'MMM dd'), ' - ', FORMAT(CASE WHEN DATEADD(DD, 6, dt) > GETDATE() THEN GETDATE() ELSE DATEADD(DD, 6, dt) END, 'MMM dd')) as Cohort,
    row_number() over (order by dt) as CohortNo
FROM dates A
WHERE  DATEPART(dw,dt)=1
)
 , cohortevent as (
-- The complete set of cohorts and their events
select c.*, e.*
from cohort c
left join Event e on e.eventtime between c.StartDate and C.EndDate
)
, Retained as(
-- Recursive CTE that works out how long each user has been retained
select c.StartDate,c.EndDate,c.CoHort,c.CohortNo,c.EventId,c.EventTime,c.Count,c.UserID, case when Userid is not null then 1 else 0 end as ret
from cohortevent c
union all
select c.StartDate,c.EndDate,c.CoHort,c.CohortNo,c.EventId,c.EventTime,c.Count,c.UserID, ret+1
from cohortevent c
join Retained on Retained.userid=c.userid and Retained.CohortNo=c.CohortNo-1 and Retained.eventid<c.eventid
)
, WeeksRetained as (
-- Get the highest number of weeks, which is the actual number per user (could probably be combined with previous CTE)
select StartDate, Enddate, Cohort, userID,
    case when max(ret)=1 then '<1W' else '+'+convert(varchar,max(ret)-1)+'W' end as Weeks
from Retained
group by StartDate, Enddate, Cohort,userid
)
-- Finally pivot this by the number of weeks
select *
from
(
select StartDate, EndDate, Cohort, Weeks, count(distinct userID) as UserCount
from WeeksRetained
group by StartDate, EndDate, Cohort, Weeks
) src
pivot
(
sum(UserCount)
for Weeks in ([<1W], [+1W], [+2W], [+3W], [+4W], [+5W], [+6W], [+7W], [+8W], [+9W], [+10W], [+11W], [+12W])
) piv
OPTION (MAXRECURSION 0);

Environment

All of the tables are CTE's except for "Event" which has two main columns we care about, UserId and EventTime.

What I've tried

I've added indexes on both UserId and EventTime. I noticed the DTUs (this is an Azure SQL instance) were maxing out originally, but I've vertically scaled the database instance so the DB runs at 70% DTU usage and it's still not completing in 30+ minutes. There are currently only 40k rows in Event.

Execution Plan Link

https://www.brentozar.com/pastetheplan/?id=HkB6xClKH

Testing

Based on your estimated execution plan I tried to get some sample data and get an actual execution of your query. Remember that while I am trying to get closer to your issue, YMMV.

CREATE TABLE dbo.[Event](EventId INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
                    EventTime date,
                    userid INT,
                    Count INT);
CREATE INDEX IX_EventTime_UserID
ON dbo.[Event](EventTime,userid);
CREATE INDEX IX_Event_UserId
ON dbo.[Event](userid)
INCLUDE(EventTIme,Count)

INSERT INTO dbo.[Event](EventTime,userid,Count)
SELECT TOP(100000)
DATEADD(Minute,- ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),GETDATE()),
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) % 2000,
ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM MASTER..spt_values spt1
CROSS APPLY MASTER..spt_values spt2

This is the actual plan that came out.

While not all operators & plan choices are the same, some parts look like they could match. One of these are the high amount of spools & filters with high row counts:

The querytimestats here show as 60 seconds

 <QueryTimeStats CpuTime="58806" ElapsedTime="58867" />

  The generation of the dates and cohort tables is fairly quick when
  I've broken those out separately, so I don't think those are the
  culprit.

The performance can differ between separate executions and joining , filtering, ... on the execution of these date tables. It is much easier to calculate estimates on one small part than on everything at once.

Now, If I simply create a temporary table, and insert the results of the cohortevent cte into this temporary table to split up the work:

CREATE TABLE #temp(
StartDate date,EndDate date,CoHort varchar(50)
,CohortNo int,EventId int,EventTime date
,Count int,UserID int);

&

INSERT INTO #temp
SELECT c.StartDate,c.EndDate,c.CoHort,c.CohortNo,c.EventId,c.EventTime,c.Count,c.UserID
FROM cohortevent c;

The result is a query plan with less spools & better estimates due to not evaluating everything at once.
You are kind of giving the optimizer a breather by splitting the query in two parts.

Actual Execution plan
With the query time stats:

 <QueryTimeStats CpuTime="4957" ElapsedTime="4961" />
&
 <QueryTimeStats CpuTime="2347" ElapsedTime="2348" />

~ 6 - 7 seconds

Adding one more temp table on the cohort cte gives me the execution time I would desire.

Actual execution plan
With querytimestats=

<QueryTimeStats CpuTime="2" ElapsedTime="2" />

&

<QueryTimeStats CpuTime="929" ElapsedTime="475" />

&

    <QueryTimeStats CpuTime="2304" ElapsedTime="2305" />

~3 seconds

With the resulting query:

CREATE TABLE #cohortevent(
StartDate date,EndDate date,CoHort varchar(50)
,CohortNo int,EventId int,EventTime date
,Count int,UserID int);

CREATE TABLE #cohort(
StartDate date,EndDate date,CoHort varchar(50)
,CohortNo int);

;WITH dates AS
(
    -- Set up the date range
    SELECT convert(date,GETDATE()) as dt, 1 as Id
    UNION ALL
    SELECT DATEADD(dd,-1,dt),dates.Id - 1
    FROM dates
    WHERE Id >= -84
)
, cohort as (
    -- create the cohorts
    SELECT dt AS StartDate,
        convert(date,CASE WHEN DATEADD(DD, 6, dt) > convert(date,GETDATE()) THEN convert(date,GETDATE()) ELSE DATEADD(DD, 6, dt) END) as EndDate,
        CONCAT(FORMAT(dt, 'MMM dd'), ' - ', FORMAT(CASE WHEN DATEADD(DD, 6, dt) > GETDATE() THEN GETDATE() ELSE DATEADD(DD, 6, dt) END, 'MMM dd')) as Cohort,
        row_number() over (order by dt) as CohortNo
    FROM dates A
    WHERE  DATEPART(dw,dt)=1
)
INSERT INTO #cohort(StartDate,EndDate,CoHort,CohortNo)
SELECT StartDate,EndDate,Cohort,CohortNo
FROM cohort;

;WITH cohortevent as (
    -- The complete set of cohorts and their events
    select c.*, e.*
    from #cohort c
    left join Event e on e.eventtime between c.StartDate and C.EndDate
)
INSERT INTO #cohortevent
(StartDate ,EndDate ,CoHort
,CohortNo ,EventId ,EventTime
,Count ,UserID )
SELECT c.StartDate,c.EndDate,c.CoHort,c.CohortNo,c.EventId,c.EventTime,c.Count,c.UserID
FROM cohortevent c;

;WITH Retained as(
    -- Recursive CTE that works out how long each user has been retained
    select c.StartDate,c.EndDate,c.CoHort,c.CohortNo,c.EventId,c.EventTime,c.Count,c.UserID, case when Userid is not null then 1 else 0 end as ret
    from #cohortevent c
    union all
    select c.StartDate,c.EndDate,c.CoHort,c.CohortNo,c.EventId,c.EventTime,c.Count,c.UserID, ret+1
    from #cohortevent c
    join Retained on Retained.userid=c.userid and Retained.CohortNo=c.CohortNo-1 and Retained.eventid<c.eventid
)
, WeeksRetained as (
    -- Get the highest number of weeks, which is the actual number per user (could probably be combined with previous CTE)
    select StartDate, Enddate, Cohort, userID,
        case when max(ret)=1 then '<1W' else '+'+convert(varchar,max(ret)-1)+'W' end as Weeks
    from Retained
    group by StartDate, Enddate, Cohort,userid
)
-- Finally pivot this by the number of weeks
select *
from
(
  select StartDate, EndDate, Cohort, Weeks, count(distinct userID) as UserCount
  from WeeksRetained
  group by StartDate, EndDate, Cohort, Weeks
) src
pivot
(
  sum(UserCount)
  for Weeks in ([<1W], [+1W], [+2W], [+3W], [+4W], [+5W], [+6W], [+7W], [+8W], [+9W], [+10W], [+11W], [+12W])
) piv
OPTION (MAXRECURSION 0)

DROP TABLE #cohortevent
DROP TABLE #cohort

This will not be the best performing version of your query but this should resolve the issue with your spools going crazy. You should also investigate other workarounds like using a calendar table like @DanGuzman mentioned.


Stored procedures vs. inline SQL I know stored procedures are more efficient through the execution path (than the inline sql in applications). However, when pressed, I'm not super knowlegeable about why.

I'd like to know the technical reasoning for this (in a way that I can explain it to someone later).

Can anyone help me formulate a good answer?

I believe this sentiment was true at one point, but not in current versions of SQL Server. The whole problem was that in the old days ad hoc SQL statements could not be properly optimized because SQL Server could only optimize / compile at the batch level. Now we have statement-level optimization, so a properly parameterized query coming from an application can take advantage of the same execution plan as that query embedded in a stored procedure.

I still prefer stored procedures from the DBA side for the following reasons (and several of them can have a huge impact on performance):

If I have multiple apps that re-use the same queries, a stored procedure encapsulates that logic, rather than littering the same ad hoc query multiple times in different codebases. Applications that re-use the same queries can also be subject to plan cache bloat, unless they are copied verbatim. Even differences in case and white space can lead to multiple versions of the same plan being stored (wasteful).
I can inspect and troubleshoot what a query is doing without having access to the application source code or running expensive traces to see exactly what the application is doing.
I can also control (and know in advance) what queries the application can run, what tables it can access and in what context, etc. If the developers are writing queries ad-hoc in their application, they're either going to have to come tug my shirt sleeve every time they need access to a table that I didn't know about or couldn't predict, or if I'm less responsible/enthused and/or security-conscious, I'm just going to promote that user to dbo so they stop bugging me. Typically this is done when the developers outnumber the DBAs or the DBAs are stubborn. That last point is our bad, and we need to be better about providing the queries that you need.
On a related note, a set of stored procedures is a very easy way to inventory exactly what queries may be running on my system. As soon as an application is allowed to bypass procedures and submit its own ad-hoc queries, in order to find them, I have to run a trace that covers an entire business cycle, or parse through all of the application code (again, that I might not have access to) to find anything that looks like a query. Being able to see the list of stored procedures (and grep a single source, sys.sql_modules, for references to specific objects) makes everyone's lives much easier.
I can go to much greater lengths to prevent SQL injection; even if I take input and execute it with dynamic SQL, I can control a lot of what is allowed to happen. I have no control over what a developer is doing when constructing inline SQL statements.
I can optimize the query (or queries) without having access to application source code, the ability to make changes, the knowledge of the application language to do so effectively, the authority (never mind the hassle) to re-compile and re-deploy the app, etc. This is particularly problematic if the app is distributed.
I can force certain set options within the stored procedure to avoid individual queries from being subject to some of the Slow in the application, fast in SSMS? problems. Meaning that for two different applications calling an ad hoc query, one could have SET ANSI_WARNINGS ON, and the other could have SET ANSI_WARNINGS OFF, and they would each have their own copy of the plan. The plan they get depends on the parameters in use, stats in place, etc. the first time the query is called in each case, which could lead to different plans and hence very different performance.
I can control things like data types and how parameters are used, unlike certain ORMs - some earlier versions of things like EF would parameterize a query based on the length of a parameter, so if I had a parameter N'Smith' and another N'Johnson' I would get two different versions of the plan. They've fixed this. They've fixed this but what else is still broken?
I can do things that ORMs and other "helpful" frameworks and libraries are not yet able to support.

That all said, this question is likely to stir up more religious arguments than technical debate. If we see that happening we'll probably shut it down.


Two ways to define may-have-a (1..0-1) relationship Today I have discovered for myself that may-have-a, one-to-zero-or-one, (1-0..1) relationship can be implemented in two different ways:

Let A be the "main" table (left side of ..) and B be the "linked" table (right side of ..).

The table A has a column which can be either a number OR NULL. The number links to the primary key of B.
The table B has a UNIQUE column which links to a primary key in table A.

Mathematically these two ways to link tables are equivalent (they are both 1-0..1 that is may-have-a), but the structure of the tables is different for these two variants.

I am writing a lightweight ORM for our DB. (I know I should not, but so things work in our company.)

The bad thing is that we have may-have-a relationships in our database implemented sometimes in one way, sometimes in the other.

Should we restructure our DB to retain only one of these two relationships? Which of the two variants to retain?

As a temporary measurement, I propose to create in my ORM two relationships:

may-have-a1
may-have-a2

Is there any way to avoid this trickery?

You've got 2 conflicting design goals here. One is to design a good database. The other is to play nicely with OR mappers. You might be able to shift to a more consistent approach to mapping partial participation constraints, but you will continue to have design conflicts in the future.

One option would be to create a set of views to worm with your ORM, and so abstract the problem away.


MySQL replication master-slave with 2 or more users in master I've just configured MySQL replication master-slave.
I have into master instance 3 user: root (with all privileges and grant), admin(with all privileges) and slaveUser(with replication salve privilege).
I've configured slave server to, and if I modify (with root user) in my master instance some resource this are replicated into slave.
Well, if I modify a resource, in master instance, with admin user this resource isn't modified into slave and I've this error

  Error 'Can't find any matching row in the user table' on query.
  Default database: 'my_db'. Query: 'GRANT ALL PRIVILEGES ON . TO
  'admin'@'%''

I've configured this on the same machine with 2 docker instances.

Any suggestions??

EDIT

This is my users on MASTER

+-----------+-----------+
| user      | host      |
+-----------+-----------+
| admin     | %         |
| root      | %         |
| userSlave | %         |
| mysql.sys | localhost |
+-----------+-----------+

and this is users on SLAVE

+-----------+-----------+
| user      | host      |
+-----------+-----------+
| root      | %         |
| mysql.sys | localhost |
+-----------+-----------+

Which kind of permission does admin need to write replication on slave??

EDIT add SHOW SLAVE STATUS

mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.17.0.3
                  Master_User: userSlave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 317959980
               Relay_Log_File: mysql-relay.000002
                Relay_Log_Pos: 5834
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1133
                   Last_Error: Error 'Can't find any matching row in the user table' on query. Default database: 'mydb'. Query: 'GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%''
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 5621
              Relay_Log_Space: 317961680
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1133
               Last_SQL_Error: Error 'Can't find any matching row in the user table' on query. Default database: 'mydb'. Query: 'GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%''
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 100
                  Master_UUID: 8527301e-9765-11e5-a957-0242ac110003
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State:
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp: 151215 20:26:46
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
1 row in set (0.00 sec)

ERROR:
No query specified

Not ON ., rather ON dbname.* (good idea for applications) or ON *.* (might be appropriate for admin)


Why am I getting old data? Today I discovered something weird on PostgreSQL 9.5. (I have no idea whether this is because of beta or not.) When I want to fetch data, I get old and deleted data from query. I then do VACUUM FULL and then I get proper data (which is empty).

Am I missing something here? What might be the reason that PostgreSQL returns old data?

Note: Autovacuum is ON.

You will find the chapter Read Committed Isolation Level of the Postgres 9.5 manual instructive:

  Read Committed is the default isolation level in PostgreSQL. When a
  transaction uses this isolation level, a SELECT query (without a
  FOR UPDATE/SHARE clause) sees only data committed before the query began;
  it never sees either uncommitted data or changes committed during
  query execution by concurrent transactions.

Bold emphasis mine.

Until your transaction is committed, changes are not visible to other transactions. VACUUM has nothing to do with this, it only cleans up dead tuples after all transactions are finished with them.


Difference in Value between latest two rows? I have a table as follows:

Id  |   Name    |   Value   |   RegisterDate
--------------------------------------------------
1   |   name1   |   11      |   2018-01-01
2   |   name2   |   12      |   2018-01-02
3   |   name3   |   13      |   2018-01-03
4   |   name4   |   14      |   2018-01-04
5   |   name5   |   15      |   2018-02-05
6   |   name6   |   16      |   2018-02-06
7   |   name7   |   17      |   2018-02-07
8   |   name8   |   18      |   2018-02-08

I want a query to get different value between two latest value fields Order By Desc RegisterDate?

In above sample query result is = 18 - 17 = 1

Another way using lag() to access the previous value and TOP to only get the latest.

SELECT TOP(1)
       value - lag(value, 1, 0) OVER (ORDER BY registerdate)
       FROM elbat
       ORDER BY registerdate DESC;


Attach corrupted database In a little C# tool, I use an a mdf file to save my data.

The file became inconsistent, which isn't really a problem as there only a few changes since my last backup.

Anyway, I would still like to take a look at my last transaction to see what went wrong.

But I can't find a free tool to do so.

ApexSQL requires an SQL Server connection.
SysTool SQL Analyzer only allows for the first 50 rows, I have about 250.
::fn_dump again only on the SQL Server.

I have a running SQL Server available but can't attach the mdf file due to its corrupted state.

Is there a (free) way to view the log file? Doesn't need to be pretty, doesn't need special filters or search functions just plain and simple viewing.

Try creating a dummy database as documented in:

Creating, detaching, re-attaching, and fixing a SUSPECT database by Paul Randal

This sometimes can "trick" the server into mounting a corrupt database.


What is MySQL Cluster's Practical Memory Limit? We are designing a cloud SaaS, and are actively considering MySQL Cluster choice for our DB. We will be writing more than 100M rows/day to the DB. We need to understand if there is any practical memory limit.

I understand that MySQL Cluster supports at max 48 data nodes, but is there practically any limit on the RAM each data node can have? RAM will be anyways expensive but we want to eliminate this if there is any hard limit.

The question stems from this link: http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-ndb-innodb-engines.html, where Storage Limit is mentioned as 3TB. It is slightly ambiguous to understand the way it is written.

Thanks in advance.

As I read it, there is a 64GB limit per node.  48 nodes, without redundancy, would allow 3TB total data size, 1.5TB with redundancy.

That sounds like less than a day's worth of data at the rate you are ingesting data.

Do you need all the raw data?  Or can you summarize the data, then throw away (or archive to text files) the raw data?  This would probably let you collect data for 10 times as long.


Restoring tab exports to a mysql database with integrity I have a database with a lot of data for a client that's on a shared web host. Doing a plain mysqldump is causing the job to get killed on the server, so we were looking at backing up the tables individually.

I've found that the --tab option will automatically create separate files for each table, but my question comes to restoring them while maintaining integrity. This site shows various ways to write shell scripts that loops through the list of backup files and loads them one-by-one. However I am suspicious that this may not guarantee integrity of data.

Is there a more automated way I can lock the database and load the individual tab files, to ensure that I have the database restored to the state it was backed up in?

It doesn't really make sense that you're able to do the backup with the --tab option but not without it, and I think that should be investigated.

Concerns about integrity are generally concerns with the backup, and you should be concerned about the backup, because --single-transaction issues a START TRANSACTION WITH CONSISTENT SNAPSHOT before the backup, ensuring that what gets written to the file represents the state of all the tables at one consistent point in time.

Without this, your "user" table, being alphabetically later than your "history" table could (for example) in the backup file contain a new user but not have caught history of that account's creation, since the backup of "history" finished before the backup of "user" was started.

You say you want to restore within a transaction, but that's not possible (by default) and probably not really what you intend at any rate.

It's not possible, by default, because of the way mysqldump works.  Examine a backup file

DROP TABLE IF EXISTS `action`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `action` (
  `id` int(11) NOT NULL ...

Both CREATE TABLE and DROP TABLE cause an implicit commit.  This isn't the only concern, because non-conflicting writes to the tables could still be done by other threads.

There are two other ways to prevent changes on a MySQL server:

FLUSH TABLES WITH READ LOCK;

This attempts to close all tables and acquire a global lock that prevents any thread from writing.  The problem here again in with the standard behavior of mysqldump, and what it writes to the dump file for execution during the restore:

LOCK TABLES `action` WRITE;
/*!40000 ALTER TABLE `action` DISABLE KEYS */;
/*!40000 ALTER TABLE `action` ENABLE KEYS */;
UNLOCK TABLES;

These individual table locks are not compatible with the global lock:

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.16 sec)

mysql> lock tables t1 write;
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock

You can disable this behavior with --skip-add-locks but then you wouldn't be able to write data into the tables if you had the global read lock because it prevents you from writing as well as other threads... so that one seems to be a non-starter.

Alternately, you could force the entire server to read_only mode.

FLUSH TABLES WITH READ LOCK; -- force a wait until all ongoing writes have finished
SET GLOBAL READ_ONLY = 1;    -- set entire server to read-only except for users with `SUPER` privilege
UNLOCK TABLES;               -- release the global read lock
--- restore backup
SET GLOBAL READ_ONLY = 0;

If you're concerned with other threads modifying the tables during the restore, but you don't want the entire server to be read-only for the duration of the operation, I only see  one really solid option, and that would be to restore all of the tables with an alternate name, and then, in a single SQL statement, rename them all to their correct names.

When multiple tables are renamed in a single statement, the entire statement is atomic.

RENAME TABLE t1 TO t1_old, t1_new to t1;

This acquires a global mutex that prevents t1 from being accessed until all of the renames are complete... but it would require manipulating your dump file to give the tables alternate names, thought it might also work if you restored to an alternate database name and renamed the tables in a fully-qualified form.

Another approach would be to drop and add all of the tables and lock all of them for write, before inserting the data, after removing any LOCK and UNLOCK from the dump file(s).  The one additional step you'd need to do, here, would be after acquiring the locks, you'd need to run through every table and delete any data that got inserted by other threads between the time you created the table and acquired the locks, or the restore would fail.  SET FOREIGN_KEY_CHECKS = 0 is a session-level setting that would allow your session to freely remove any stray data without regard to foreign key constraints.

The bottom line is that there is not a simple, straightforward way to keep all hands off of the data you are attempting to restore, and if faced with that requirement, I would be inclined to shut down the application server or otherwise prevent access to the server with a mechanism outside of mysql itself, such as IP-level filtering.


Security: writing to a file from MySQL CLI I was testing writes to a file from MySQL CLI (locally & remotely) and I wonder what settings are preventing writes for remote users, basically I would like to make sure that it's set on all servers in case someone (devs) will allow remote access to MySQL:

Local (Writes allowed):

mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> \P cat >> /etc/passwd
PAGER set to 'cat >> /etc/passwd'
mysql> SELECT CONCAT('test:x:5000:5000::/home/test:/bin/bash');

Remote (writes denied):

mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| root@%         |
+----------------+
1 row in set (0.01 sec)

mysql> \P cat >> /etc/passwd
PAGER set to 'cat >> /etc/passwd'
mysql> SELECT CONCAT('test:x:5000:5000::/home/test:/bin/bash');
sh: /etc/passwd: Permission denied

I think you are confused about what's happening here.  You ran the client on the server machine as root.  You probably weren't running the client as root on the "remote" machine.  Even if your query had succeeded in appending to /etc/password, you would have only modified your local password file, not the one on the server.

The pager is always local to where the mysql client is running.  It has nothing to do with the MySQL server.

If the user on the local machine (where the client is running) has write access to a local file, then they can write to the file via the pager option, just as they could write to the file with anything else.  There's nothing here that has anything to do with the MySQL server or permissions.

mysql> \P cat >> foo.bar
PAGER set to 'cat >> foo.bar'
mysql> select NOW();
1 row in set (0.00 sec)

mysql> quit
Bye

Now, by making the file so it can't be written...

sqlbot@dev:~$ chmod 000 foo.bar
sqlbot@dev:~$ mysql

mysql> \P cat >> foo.bar
PAGER set to 'cat >> foo.bar'
mysql> select NOW();
sh: cannot create foo.bar: Permission denied
1 row in set (0.00 sec)

mysql> quit
Bye

Run the client as root on the remote machine and you'll see that you won't get permission denied, but you also will not have modified anything on the server -- just your local password file.

Also, yeah, don't create a 'root'@'%' account in MySQL.


UPDATE all values of a column with values of a related table I read so many Answers on this Website or in other blocks, but i just can't resolve this issue. I'm trying to perform an Update on a column of a table with values of an other, related table.

I'm working with ORACLE SQL Developer. And i mostly get the ORA-01427: single-row subquery returns more than one row. I'm totaly aware of what this error message is saying.
I only need the value of corresponding joined object to be written in on cell and for me it should work by declaring it with de join and with the WHERE condition. But maybe i'm missing something.

I tried:

UPDATE GH210_STELE
SET GH210_STELE.GH210_GH220_ID =(SELECT GH220_AUSSTATTUNG.GH220_ID
                                 FROM GH210_STELE
                                 INNER JOIN GH220_AUSSTATTUNG
                                     ON GH220_AUSSTATTUNG.GH220_INVENTAR_NR = GH210_STELE.GH210_NUMMER
                                 WHERE GH210_STELE.GH210_NUMMER=GH220_AUSSTATTUNG.GH220_INVENTAR_NR);

Or:

UPDATE GH210_STELE
SET (GH210_GH220_ID, desc) = (SELECT GH220_AUSSTATTUNG.GH220_ID,
                                     GH220_AUSSTATTUNG.desc
                              FROM GH210_STELE
                              INNER JOIN GH220_AUSSTATTUNG
                                  ON GH220_AUSSTATTUNG.GH220_INVENTAR_NR = GH210_STELE.GH210_NUMMER)
WHERE EXISTS (SELECT 1
              FROM GH220_AUSSTATTUNG
              WHERE GH210_STELE.GH210_NUMMER = GH220_AUSSTATTUNG.GH220_INVENTAR_NR );

Or even:

MERGE INTO GH210_STELE
USING (SELECT GH220_INVENTAR_NR, GH220_ID
       FROM GH220_AUSSTATTUNG) GH220_AUSSTATTUNG
    ON (GH220_AUSSTATTUNG.GH220_INVENTAR_NR = GH210_STELE.GH210_NUMMER)
WHEN MATCHED THEN
    UPDATE
    SET GH210_STELE.GH210_GH220_ID = GH220_AUSSTATTUNG.GH220_ID;

UPDATE GH210_STELE a
SET GH210_GH220_ID=(SELECT GH220_ID
                    FROM GH220_AUSSTATTUNG b
                    WHERE b.GH220_INVENTAR_NR = a.GH210_NUMMER);

Any help is much appreciated.

Cheers Fabi

Run the following select in order to find cases where you have more then one record for the key you are using. Once you find the records with duplicate values you can figure out how to deal with them and it could be removing them from the other table or updating the select to exclude them.

SELECT GH220_INVENTAR_NR,
       COUNT(0)
FROM GH220_AUSSTATTUNG
GROUP BY GH220_INVENTAR_NR
HAVING COUNT(0) > 1