Local MySQL database check for changes Me and my friend had a website. Before closing website he dumped MySQL Database from phpmyadmin and stores it locally on his PC. Now he send me sql file. Is there a way to see if he didn't change/add/remove lines,tables and variables and other stuff from *.sql file ? How he can modify the file so I can't notice that file is modified ?

As you've described the situation, no — if you have the original file or even access to the original database, you could compare what he sent you with what you're able to get yourself. However, with only the file he's provided, there's no way to determine whether it's been edited. The .sql file is simply a plain text file and there's no audit trail that can ensure — after the fact — that he hasn't modified anything.


sql server-mirroring 3 sql server we are developing an application  for a company and this company has 3 branches, we design a SQL server for our application we can put this server in one of the branches but as the connection between these branches is over internet and this connection can be lost some times but the SQL server availability is vital so we decided to mirror the server in each branch, I need a simple solution for how should i config these servers to be mirror of each other with the same privilege to do changes in DBs, all we want to do is configuring 3 SQL server exactly with the same data. we read about merge replication but we want a simpler solution because our scenario is not really enterprise.

Could you go with one SQLServer instance at one of the offices and then just configure VPN and join the other two in with a site-to-site VPN tunnel? That would greatly simply your architecture as long as it met your performance requirements (and possibly some SQLServer license costs).


Calculating the value of a column based on consecutive values of rows in another column It is straight forward to compute column value based on values of other columns, e.g. speed/time AS acceleration where speed and time are columns. Is there a way to compute column's value based on consecutive rows values in other columns?

My database consists of points location coordinates (consecutive points), and for each point, I want to determine that point's bearing change from reference (true north). Here's more detail about the question:

TABLE:
+-----+-----+---------+
| lat | lon | bearing |
+-----+-----+---------+
| x0  | y0  |   b1    |
| x1  | y1  |         |
| x2  | y2  |         |
+-----+-----+---------+

Now I want to compute the bearing column(e.g. b1). From my research, bearing between two points is determined this way:

Then bearing B of (x1,y1) from (x0,y0) is calculated as:

B = arctan(b,a)          .......(1)

where

b = sin[y1 - y0] * cosx1 .....(2)

a = cosx0* sinx1 - sinx0 * cosx1 * [y1 - y0]   ......(3)

Thus, to compute the bearing of row1 in the above table (b1), both  quantities a and b in equations 2-3 require some value from row2 in the table i.e. x1,y1, and addition to x0, y0

This is the computation I would like to do on the database, I have the latitude and longitude columns for the consecutive geolocations stored. I created this fiddle so need to alter the table by adding a 5th column for bearing, but I am not sure how I can use values from next row for the current row in the bearing column.

The main thing you need is an ordering of the rows, since rows are unordered per se. Once you have an ordering, you can use the window function lag to access the previous row in that ordering.

Assuming that your rows are ordered by tstamp, that would look like

SELECT lat,
       lon,
       lag(lat) OVER (ORDER BY tstamp) AS prev_lat,
       lag(lon) OVER (ORDER BY tstamp) AS prev_lon
FROM atable;

Applying the formula of your choice is left as an exercise for the reader.


How to split a multivalued column, do a lookup and update another column? This is a part of my previous question.

How can I achieve the following for all the records in my table using a single query?

SET @TeamMembers = TeamMembers from tblProjects where ID = '125'

DECLARE @MemberEmails TABLE(col VARCHAR(255))
DECLARE @MemberNames TABLE(col VARCHAR(255))
DECLARE @Names VARCHAR(255)

INSERT INTO @MemberEmails
SELECT * FROM [dbo].[fnSplitString] (@TeamMembers, ',')

INSERT INTO @MemberNames
SELECT u.NAME FROM @MemberEmails e LEFT OUTER JOIN Users u ON e.col = u.EMAILID

SELECT @Names =
   STUFF((SELECT '; ' + RTRIM(CONVERT(CHAR(50),col))
   FROM   @MemberNames b
   FOR XML PATH('')),1,1,'')

UPDATE tblProjects SET Team = @Names  WHERE ID = '125'

I was able to achieve the update of all records my table, using the below set of commands :

DECLARE @MemberNames TABLE(ID VARCHAR(50), Person VARCHAR(255))
INSERT INTO @MemberNames
SELECT P.ID, u.NAME
FROM dbo.tblProjects AS P
CROSS apply dbo.fnSplitString(P.TeamMembers, ',') AS S
     LEFT OUTER JOIN Users u ON s.splitdata = u.EMAILID

DECLARE @Names TABLE(ID VARCHAR(50), Name VARCHAR(MAX) )
INSERT INTO @Names
SELECT t.ID, STUFF(ISNULL((SELECT '; ' + x.Person
            FROM @MemberNames x
           WHERE x.ID = t.ID
        GROUP BY x.Person
         FOR XML PATH (''), TYPE).VALUE('.','VARCHAR(max)'), ''), 1, 2, '')
    FROM @MemberNames t
    GROUP BY t.ID
UPDATE tblProjects SET Team = Name FROM tblProjects pr INNER JOIN @Names ON ID = pr.ID

I have a table-valued function fnSplitString for splitting a string based on a delimiter.

p.s. I couldn't get the updates using a single query, as I had mentioned in my question.


Understanding why a query is slow I have a new column in SQL Server 2016, on a table of type bit. (I wanted it to be a persisted calculated column but was overruled and asked to make it a bit that is maintained by program code). Its default value is 0.

I used the following SQL excerpt to attempt to set the initial values for existing rows in a dev environment but had to terminate it after 5 minutes.

update dbo.tableName set newColumn =

cast(
    (
        case when
            (
            isNull(colA,'') <> ''
            or isNull(colB,datefromparts(1901,1,1)) > datefromparts(1901,1,1)
            or colC is not null
            or isNull(colD,'') <> ''
            )
        then 1
        else 0
        end
    )
as bit);

The table has about 92,000 rows and about 3,200 should have the value 1 set. (I understand the above would also be setting the remaining 88,800 row values to 0 even though the default constraint has already set them to 0 but a SELECT using the above logic in a WHERE clause executes within 1 second).

My question is why the above should take so long, and how would I identify the root cause? (Should I obtain a query plan and work from there? What would I look for?)

I re-wrote the update as follows and it completed in 3 seconds.

with cteCommonTableExpression (cteIdColumn)
as
(
    select dbo.tableName.idColumn from dbo.tableName
        where
            case when
                (
                isNull(colA,'') <> ''
                or isNull(colB,datefromparts(1901,1,1)) > datefromparts(1901,1,1)
                or colC is not null
                or isNull(colD,'') <> ''
                )
            then 1
            else 0
            end
        = 1
)

update dbo.tableName set newColumn = 1
where dbo.tableName.idColumn in (select cteIdColumn from cteCommonTableExpression);

The following - which I expect should be logically identical to the last section of code - also completes in 3 seconds.

update dbo.tableName set newColumn = 1
where dbo.tableName.idColumn in
(select tn2.idColumn from dbo.tableName tn2 where case when
                (
                isNull(tn2.colA,'') <> ''
                or isNull(tn2.colB,datefromparts(1901,1,1)) > datefromparts(1901,1,1)
                or tn2.colC is not null
                or isNull(tn2.colD,'') <> ''
                )
            then 1
            else 0
            end
        = 1)

Shortly after posting here I obtained the estimated query plan which reported 89% of the cost goes to a clustered index update on the primary key. The new column is not involved in the primary key. The question becomes: why does this update require a clustered index update (especially while the alternative updates apparently do not)?

Without seeing the plan...

Updating all 92k rows

scans the table start to end
updates all rows (whether value is the same or not)
clustered index leaf = the actual data

= you are actually doing clustered index update

When you updating 3,200 rows means you are doing 96.5% less data changes work (3200 is 3.5% of 92000) so of course it will run faster

Also the transaction size (for rollback) in the transaction log will be a lot bigger.
This needs allocated and may need a log file size increase

Basically, update only the rows you need...


What are the disadvantage/consequences of moving updated column to separate table to prevent table lock on the original table? I have a table p with a column points. There are a couple of million of records in the table and i need to regularly calculate and update the points for each row. This process takes a couple of minutes and it locks the table. I need to be able to insert and update records in p in the meanwhile. One solution I was thinking of is to create a second table and add a foreign key to p and the points column there. If this table is locked for a couple of minutes that's fine. Are here disadvantages/consequences that I should keep in mind with this solution?

From what I remember about this database from your earlier questions, I'd suggest you look at these alternatives to your approach:

Consider RCSI or Snapshot isolation. This will prevent your select queries being blocked during the update. Be mindful of the increased usage of tempdb this can generate but IIRC the nature of your database/data should mean this is small.
Updating the points record in batches, rather than one almighty transaction for the entire table. See How to Use Batch Size to Speed Mass Updates, Inserts and Deletes for an old (but still relevant) approach.


Unable to run update_mysql in MySQL 5.5 I have recently taken over managing a MySQL database running on Windows Server 2008 R2. It is running 5.5.37 Community Edition. When it is started, it includes the following lines:

Thread, Type, Details
Column count of mysql.events_waits_current is wrong. Expected 16, found 19. The table is probably corrupted
Column count of mysql.events_waits_history is wrong. Expected 16, found 19. The table is probably corrupted
Column count of mysql.events_waits_history_long is wrong. Expected 16, found 19. The table is probably corrupted
Native table 'performance_schema'.'threads' has the wrong structure
Incorrect definition of table performance_schema.events_waits_summary_by_thread_by_event_name: expected column 'THREAD_ID' at position 0 to have type int(11), found type bigint(20) unsigned.
Column count of mysql.file_summary_by_event_name is wrong. Expected 5, found 23. The table is probably corrupted
Column count of mysql.file_summary_by_instance is wrong. Expected 6, found 25. The table is probably corrupted
Incorrect definition of table performance_schema.mutex_instances: expected column 'LOCKED_BY_THREAD_ID' at position 2 to have type int(11), found type bigint(20) unsigned.
Incorrect definition of table performance_schema.rwlock_instances: expected column 'WRITE_LOCKED_BY_THREAD_ID' at position 2 to have type int(11), found type bigint(20) unsigned.

I tried running mysql_upgrade, but get the following result:

Looking for 'mysql.exe' as: C:\Program Files\MySQL\MySQL Server 5.5\bin\mysql.exe
Looking for 'mysqlcheck.exe' as: C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqlcheck.exe
The system cannot find the file specified.
FATAL ERROR: Upgrade failed

The paths point to the correct locations. I can run mysql.exe and mysqlcheck.exe from those locations. I am running from an Administrator-level command prompt.

I have tried running mysqlcheck.exe with the parameter --all-databases, and everything checks out as OK. But those errors still remain when I restart MySQL.

Any ideas how to get rid of these errors?

As you  mentioned you are tying to run mysql_upgrade and it's looking for 'mysql.exe' and  'mysqlcheck.exe'.

Try this To make it easier to invoke MySQL programs, you can add the path name of the MySQL bin directory to your Windows system PATH environment variable:

Here is the link to set Mysql environment variable in Windows system path.

Hope this will help you.


How to select rows that have multiple relations in common I apologize if this is a duplicate, I tried to search for an answer, but possibly do not have the right keywords to help my search.

Fiddle: http://sqlfiddle.com/#!9/ea5723/6

I have the 3 tables:

places

+----+---------+
| id | name    |
+----+---------+
| 1  | Place A |
| 2  | Place B |
| 3  | Place C |
| 4  | Place D |
| 5  | Place E |
+----+---------+

tags

+----+-------+
| id | slug  |
+----+-------+
| 1  | tag-z |
| 2  | tag-y |
| 3  | tag-x |
| 4  | tag-w |
| 5  | tag-v |
+----+-------+

place_taxonomy

+----------+--------+
| place_id | tag_id |
+----------+--------+
| 1        | 1      |
| 1        | 3      |
| 1        | 5      |
| 2        | 3      |
| 3        | 1      |
| 3        | 2      |
| 3        | 3      |
| 3        | 4      |
| 3        | 5      |
| 4        | 2      |
| 4        | 4      |
+----------+--------+

I have been able to query what places have a single tag with

SELECT
  places.id AS p_id,
  places.name
FROM
  places
  LEFT JOIN place_taxonomy
    ON places.id=place_taxonomy.place_id
  LEFT JOIN tags
    ON place_taxonomy.tag_id=tags.id
WHERE
  tags.id=1
GROUP BY
  p_id;

And I can select which places have either of multiple tags with:

SELECT
  places.id AS p_id,
  places.name
FROM
  places
  LEFT JOIN place_taxonomy
    ON places.id=place_taxonomy.place_id
  LEFT JOIN tags
    ON place_taxonomy.tag_id=tags.id
WHERE
  tags.id=1 OR
  tags.id=2
GROUP BY
  p_id;

But, the trouble I am having is how to select places that have a combination of tags such as tag-z and tag-y

SELECT
  places.id AS p_id,
  places.name
FROM
  places
  LEFT JOIN place_taxonomy
    ON places.id=place_taxonomy.place_id
  LEFT JOIN tags
    ON place_taxonomy.tag_id=tags.id
WHERE
  tags.id=1 AND
  tags.id=2
GROUP BY
  p_id;
#Returns No Results!

But, the trouble I am having is how to select places that have a
  combination of tags such as tag-z and tag-y

You are close with your query. You can use GROUP BY and SUM to find those relations.

Please note mine query is valid with sql-mode enabled only_full_group_by because places.id is a PRIMARY key.

Query

SELECT
   places.id
 , places.name
FROM
 places

INNER JOIN
 place_taxonomy
ON
 places.id = place_taxonomy.place_id

INNER JOIN
 tags
ON
 place_taxonomy.tag_id = tags.id

GROUP BY
 places.id

HAVING
    SUM(tags.slug = 'tag-z')
  AND
    SUM(tags.slug = 'tag-y')

Result

    id  name
------  ---------
     3  Place C

demo http://sqlfiddle.com/#!9/ea5723/15

Or use a delivered table to find the places with the tags 'tag-z' and 'tag-y'.

Query

SELECT
    places_taged.id
  , places_taged.name
FROM (

  SELECT
     places.id
   , places.name
  FROM
   places

  INNER JOIN
   place_taxonomy
  ON
   places.id = place_taxonomy.place_id

  INNER JOIN
   tags
  ON
   place_taxonomy.tag_id = tags.id

  WHERE
   tags.slug IN (
      'tag-z'
    , 'tag-y'
   )
)
 AS places_taged
GROUP BY
    places_taged.id
  , places_taged.name
HAVING COUNT(*) >= 2

Result

| id |    name |
|----|---------|
|  3 | Place C |

demo http://sqlfiddle.com/#!9/ea5723/27


How to extract the structure out of DB2 (Back up Structure)? Is there a way in db2 to take a backup of the database structure (Metadata) without actual data?

My purpose is to pull structure (tables, views, indexes, triggers, procedures and functions) from an existing DB into a new DB.

The tables can be blank.

Since YperSillyCube(TM) insists, I will add his answer. db2look is the tool that comes with DB2 to extract DDL from the database. You can extract things like tables, constraints, indexes, triggers, stored procedures, etc. As well as security statements, federation settings, buffer pool and tablespaces, and some database settings.

I highly recommend using it. I used to use it all the time when I worked with DB2. Very handy. Especially when combined with tools like Beyond Compare to compare DDLs between environments, etc.

The IBM Knowledge Center link is good documentation to read up on. Ember Crooks has posted two great articles on db2look here and here.

Personally, I tended to use the following options the most with the command.

db2look -d <databasename> -a -l -e -x -o <file to extract to> -td"@"

This allows me to grab several objects besides just the default, the tablespaces and bufferpools, some security settings and then make sure I set the @ sign as the statement terminator in case I have triggers, functions, or stored procedures.

You can then import those into a new database using the following db2 -td@ -vf <filename> and then either redirect the results out or pipe to tee as I like to do on Unix. Do note that the character you use as the statement terminator in the db2look command needs to match the statement terminator supplied when importing the file.


insert Query throwing error I am ran into a weird problem this particular query is not working

insert into `timesheet`
    (`type`,`app_id`,`app_name`,`app_title`,
     `app_path`,`timestamp_start`,`timestamp_end`,`duration`,
     `user_id`,`matter_id`,`updated_by_user_id`,`timestamp_create`,
     `source`,`user_rate`,`cost`,`description`,
     `location_name`,`conflict`,`suggestion`)
Values
    ('PLUGIN', '5232', 'oMgqTrYiZVQp.exe', 'qZXhgWtnwvQq',
     'k', '2016-05-06T18:21:29.000Z', '2016-05-18 19:29:50.000', '1021101',
     '4', '1', '-1', '2019-06-15T01:13:34.000Z',
     NULL, '15000', '4254.5875', NULL,
     NULL, '0', NULL)

WHat I was doing is running man this query with different values, rest are working fine, but for this particular query, it throws the error.

Error Code: 1136. Column count doesn't match value count at row 1

Can you pelase let me know what I am doing wrong or any hint would be appreciated so that i can debug it

Your error is telling you that the amount of columns you said you'd fill does not match the amount of columns you actually are giving data for. Make sure the amount of columns specified after timesheet matches the amount of columns filled after Values.

Additionally you may want to check if the source, description, location_name and suggestion columns allow NULLS. You can also test if it works if you replace the NULL values with dummy values (like an empty string or a 0).


SSRS Encryption Key backup location I would like to know if there is any metadata table/registry entry where we can find the backup location of SSRS Encryption Key.

I am working on a power shell report to display the Encryption Key backup date. But the challenge is to find out the location so that the script can be generic.

I could see no answer to this since couple of months. I have done my research and have put together in the below link:
http://sqlgeekspro.com/ssrs-encryption-key-backup-information/


How to translate high level entity relationship into schema According to the following articles written by Dr. Chan, we can describe high level entity relationship. (see page-10,11 of doc-A and page9 of doc-B)

•doc-A) English Sentence Structure and Entity-Relationship Diagram.

http://www.maltercorp.com/DrPeterChen/english.pdf

•doc-B) English, Chinese and ER diagrams.

http://www.csc.lsu.edu/~chen/pdf/ER_C.pdf

These articles said ERD is translated from English sentence like below.

-- The product is shipped to the customer.

-- This action “Shipping” is performed by the clerk.

Then think about another example.

-- The customer is identified their bank card.

-- This action “identifying” is Authenticated by the bank.

-- The bank issues the "bank card" to their "customer" with similar interpretation.

Now my questions are:

How can I express this situation with an ERD?

Relationship “issue” has possibility to connect with two entities "Bank card" or
"customer".

But I don't want to use ternary relationship as a restriction.
How can I translate those entities mixed with different dimensions to a database schema?
Basically, is it possible to connect to the entity at another level ?
Are there any good tools for describing high level entity relationship and generating DDL?

The key to understand this case is at the ninth page from Doc-A:

  "product" and "customer" are entity types, and "shipped to" is a
  relationship... the verb "ship" is then converted to a gerund... in
  other words, the relationship... has been converted into the entity
  type "shipping"

Hence, the final third form will have "shipped to" converted to an entity by itself, "shipping".

This is necessary because "Shipped to" is a MANY-TO-MANY relationship (although to myself Chen's text is not clear on this).

But, your example does not relate - the relationship "Identified by" is an ONE-TO-MANY (a card belongs to ONE customer / a customer have ONE OR MORE cards issued).

Hence, the model would have the relationship between "Bank" and "Bank card" identified by TWO verbs.

Or, making the model stronger, to register several authentication events, one may turn "Authenticated by" into a entity by itself, "Authenticating" (to register event date, data shared, etc.).


Index with few seeks and many updates Say I create a nonclustered index on a table. After a week I look at the sats, e.g. number of seeks, scans, updates etc. E.g. say I have 10 seeks and 10000 updates. How can I measure the cost of the 10000 updates? That is how can I measure the cost of the 10000 updates on the nonclustered index vs 10 extra scans on the clustered index?
I want to learn when the cost of having the index is more than the benefit. How do I measure this?

What I would do is
SET STATISTICS IO ON

and then I would test the SELECT statement that produces the seek and the UPDATE statement.
Finally, I would compare the number of pages read by the SELECT with the number of pages written by the UPDATE.
But in general this ratio between seek and updates seems to suggest that your nonclustered index is not valuable. But it really depends on your query needs.


Amazon RDS Postgresql adding new extensions I want to download jsonbx to RDS instance. RDS PostgreSQL Features Supported shows only built-in features. Does this mean there is no way we can install an extension to RDS which is not in the Feature Matrix? Is there a work around for this?

You can't add your own extensions to RDS, at least not ones that require superuser rights (like anything with C code). This is one of the downsides you accept in exchange for convenient management.

If the extension only includes simple plpgsql and sql functions you can add the functions manually. That is not possible with anything requiring superuser privileges, including any extension with C code.

jsonbx adds functions and operators. The source contains C code, so there is no way you can load this on RDS. Ever. At all. RDS doesn't allow you to run anything that might give you superuser access or access to the underlying VM that PostgreSQL runs on, nor does it provide a way to upload extensions even if it would permit you to load them.

You will need to switch to running a standalone PostgreSQL server (say, on EC2), persuade Amazon support to make the extension available as part of their supported set of extensions, or make do without.


Delete records from one table using join with another table? Delete records from one table using join with another table.
Ok, the mission is to delete all employee records if in the title of their department can be
found word "sales". It can happen in the real life. Imagine that newlly elected CEO decides
to close all sales operations.

this my Query:

DELETE *
FROM Employees
WHERE DeptNo IN
  (SELECT DeptNo
  FROM Departments
  WHERE LCase(DeptName) LIKE '*sales*')

but i got error. SQL Syntax error.Please help me.

Remove the star! The syntax (see the DELETE page at MSDN), can be either:

DELETE
FROM Employees
...

or

DELETE Employees
FROM Employees
...

or

DELETE e
FROM Employees AS e
...

You probably don't need the stars in the '*sales*' string either. If you mean to find the rows with 'sales' string anywhere in that column, the wild card is % (see the LIKE page):

DELETE e
FROM dbo.Employees AS e
WHERE e.DeptNo IN
  (SELECT d.DeptNo
  FROM dbo.Departments AS d
  WHERE d.DeptName LIKE '%sales%') ;

I removed the LCase() as well. That is an Access function, not an SQL-Server one (thnx @Aaron). Add back (the equivalent LOWER()) if your database (or table) uses a case sensitive collation (the default is case insensitive so usually there is no need for such functions.)


How to group-by to get MultiPoint/GeometryCollection in MySQL? I have a table x like this:

Columns:

SubjectID int(11)
Timestamp bigint(20)
fix_geom point

When i try to group my fix_geom column to a MultiPoint (or GeometryCollection) by SubjectID, like:

SELECT SubjectID, ST_AsText(MultiPoint(fix_geom))
FROM x
WHERE SubjectID = 100
GROUP BY SubjectID;

i get a table with a multipoint geometry, but it contains just one single Point instead of the many i'd liked to see there...:

Can anybody tell me what i'm doing wrong?

Cheers and many thanks in advance,
Olaf

As Evan Carroll said in a comment: Unlike PostGIS, spatial aggregates are not available for MySQL.

The workaround left in a comment by danblack works:

  It looks like you want MultiPoint to be an aggregate function, which is isn't. You'll probably need something like ST_GeomFromText(CONCAT('MultiPoint(', GROUP_CONCAT(...), ')')), where ... gets an X Y string from fix_geom.


Execution plan caching of ITVF called with inline vs dynamic SQL I would like someone to explain to me how each of the two queries below would generate their execution plan in SQL Server where my function [dbo].[GetAdditionalProjectDatesForCalendar] is an ITVF (inline table-valued function):

Dynamic SQL

sp_executesql N'SELECT * FROM [dbo].[GetAdditionalProjectDatesForCalendar]
(@Parameter1,@Parameter2)', @Parameter1 = 'Value1', @Parameter2 = 'Value2'

Inline SQL

SELECT * FROM [dbo].[GetAdditionalProjectDatesForCalendar]
('Value1','Value2')

Background

The Dynamic SQL is what's generated from a C# application that I'm working on and while it performs just as well as the Inline SQL most of the time it performs poorly for certain values of @Parameter1 and @Parameter2.

I've read up on how this is caused by Parameter Sniffing and I've tried using option(recompile) with the Dynamic SQL but that hasn't improved the performance entirely.

I feel like I need to find a way to force C# to create the Inline SQL so that it will use the exact execution plan for the given parameters instead of creating one on the fly with option(recompile).  I also think I can do this by writing my dynamic sql without using parameterization but I know that would introduce a SQL Injection risk.

The sp_executesql plan with OPTION (RECOMPILE) should be fully optimized for the specific parameter values on each call, with parameter embedding also enabled.

Taking the AdventureWorks example from Itzik Ben-Gan's comment on the article referenced in the question:

CREATE FUNCTION dbo.F
(
    @NameLike nvarchar(50),
    @Sort tinyint
) RETURNS TABLE
AS
RETURN
    SELECT TOP (5)
        ProductID,
        Name
    FROM Production.Product
    WHERE
        @NameLike IS NULL
        OR Name LIKE @NameLike
    ORDER BY
        CASE WHEN @Sort = 1 THEN ProductID ELSE NULL END ASC,
        CASE WHEN @Sort = 2 THEN ProductID ELSE NULL END DESC,
        CASE WHEN @Sort = 3 THEN Name ELSE NULL END ASC,
        CASE WHEN @Sort = 4 THEN Name ELSE NULL END DESC;

The execution plan for:

SELECT *
FROM dbo.F(N'K%', 3);

...shows the specific parameter values being inlined (so the @NameLike IS NULL is optimized away, enabling a seek) but parameter embedding does not take place so there is a Top N Sort for the ORDER BY clause:

If we add the recompile query hint, a plan is generated for the specific values with parameter embedding enabled (so no sort):

SELECT *
FROM dbo.F(N'K%', 3)
OPTION (RECOMPILE);

If we wrap the query in sp_executesql we get neither inlining nor embedding, since the values are explicitly parameterized:

EXECUTE sys.sp_executesql
    N'SELECT * FROM dbo.F(@NameLike, @Sort)',
    N'@NameLike nvarchar(50), @Sort tinyint',
    @NameLike = N'K%', @Sort = 3;

Adding OPTION (RECOMPILE) enables both optimizations again:

EXECUTE sys.sp_executesql
    N'SELECT * FROM dbo.F(@NameLike, @Sort) OPTION (RECOMPILE)',
    N'@NameLike nvarchar(50), @Sort tinyint',
    @NameLike = N'K%', @Sort = 3;


how do i get rows for each month which contain the store with the highest number of purchases? how do i get rows for each month which contain the store with the highest number of purchases?

my code is:

SELECT DATE_FORMAT(buyDate, '%M') as month, count(id) as numPurchases, store
from purchase, customerpurchases
where purchase.id = customerpurchases.purchaseid
group by month, store;

SELECT t1.*
FROM purchase t1
JOIN ( SELECT t2.`month`, MAX(t2.numPurchases) numPurchases
       FROM purchase t2
       GROUP BY t2.`month` ) t3
    ON t1.`month` = t3.`month`
   AND t1.numPurchases = t3.numPurchases


How to safely eject a tablespace oracle and move it to another machine I've a tablespace created on a external hardisk with one big datafile. If there are no connections to it, is it safe to eject the disk and plug it into a new machine? And how to map the tablespace to the oracle in the new machine?

If there are no connections to it, is it safe to eject the disk and plug it into a new machine?

No.  Absolutely not.

Database != File

Unless you shutdown the entire database, there is no guarantee that changes have been written from the Buffer Cache into the data files that make up that Tablespace.

Making a Tablespace transportable does forces that write activity to happen (and then prevents any more) but without the downtime.  But then, as you say, you have to muck about "mapping" that Tablespace into the new database.  Don't make work for yourself.

Your best option, though (i.e. easiest, most reliable, best supported but, perhaps, not fastest to execute), is to dump (datapump) the data out of one database and into the new one.  Depending on your network connectivity, you might be able to do this over a database link, or might have to resort to moving the dump file between machines.


How to monitor InnoDB Disk I/O Required for MySQL? I am doing an estimate on how much i/o[disk read write] on Mysql 5.6 [innodb storage engine], for getting idea how to calculate total number of disk read write i have searched a lot on SO on DA and googled also  and encounter a post give on this link Which is quite helpful.

It says I have to use SHOW STATUS

mysql>SHOW STATUS;

And monitor Key_read and Key_write for physical index read write , Created_tmp_disk_tables and Created_tmp_files  for creating temp file  for creating temp table and file on disk.

As i am using innodb , buffer pool size also affected on total number of disk read operation [which is totally depend on availability of hot pages ] so here i am confused about

  1>>.What are the Innodb "Server Status Variables” i should consider while
    counting disk  read write?

  2>>.Are there any other "Server Status Variables” i should consider while
    counting disk  read write?

  3>>. Are there other factor i am missing here?

  4>>. Dose hard disk type and speed will affect on total i/o disk read write?

thanks in advance!!!

Innodb_buffer_pool_read_requests and Innodb_buffer_pool_reads are the values you need to monitor for buffer reads and disk read for the Buffer Pool.
What you are looking for is a cache miss rate of 1% or less based on this
Innodb_buffer_pool_reads X 100 / Innodb_buffer_pool_read_requests

I have written posts like this before

Feb 04, 2014 : Tuning key_reads in MySQL
Jun 08, 2012 : How to improve MySQL Server Performance..??

You should also be tracking how much data is being poured into the InnoDB transaction logs

Innodb_os_log_written : The number of bytes written to the InnoDB redo log files.
Innodb_log_write_requests : The number of write requests for the InnoDB redo log.
Innodb_log_writes : The number of physical writes to the InnoDB redo log file.

EXAMPLE
you could take the delta of Innodb_os_log_written (bytes written in a one second window) and divide it by the delta of Innodb_log_writes and track how many bytes are being flushed to the logs per second. If the result seems too high for you, you could tweek innodb_flush_method, innodb_flush_log_at_trx_commit, innodb_log_file_size and things of this nature.


How do i increase the total MyISAM indexes? I have just made a check for my server with mysqltuner.

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB +Federated +InnoDB -ISAM -NDBCluster
[--] Data in InnoDB tables: 2G (Tables: 54)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 54

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 11h 57m 48s (46K q [1.089 qps], 21K conn, TX: 12M, RX: 4M)
[--] Reads / Writes: 98% / 2%
[--] Total buffers: 7.2G global + 2.7M per thread (100 max threads)
[OK] Maximum possible memory usage: 7.5G (63% of installed RAM)
[OK] Slow queries: 0% (8/46K)
[OK] Highest usage of available connections: 7% (7/100)
***[OK] Key buffer size / total MyISAM indexes: 2.0G/96.0K***
[OK] Query cache efficiency: 48.4% (22K cached / 46K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 1% (4 temp sorts / 247 sorts)
[OK] Temporary tables created on disk: 17% (212 on disk / 1K total)
[OK] Thread cache hit rate: 99% (7 created / 21K connections)
[OK] Table cache hit rate: 35% (102 open / 289 opened)
[OK] Open file limit used: 4% (47/1K)
[OK] Table locks acquired immediately: 100% (2K immediate / 2K locks)
[OK] InnoDB data size / buffer pool: 2.7G/5.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries

Please advise me a way to increase total MyISAM indexes (96K) to an effect number.
Thanks.

Your key_buffer_size is way too big.

Since your MyISAM indexes is that small, set it to 128K.

You need to open my.cnf and add this under the [mysqld] group header

[mysqld]
key_buffer_size = 128K;

You don't need to restart mysql. Just run this

SET GLOBAL key_buffer_size = 1024 * 128;

Give it a Try !!!

Based on the mysqltuner output, you can drop the innodb_buffer_pool_size to 3G.

For more clarification on sizing MyISAM and InnoDB caches, see my old post What are the main differences between InnoDB and MyISAM? on how to compute the ideal sizes for both MyISAM and InnoDB.


MySQL full text search result problem I have this query that does not return any results:

SELECT
    MATCH (e.firstname, e.lastname, e.tc_identity) AGAINST ('Baha') AS Score
    , e.*
FROM
    employees AS e
WHERE
    MATCH (e.firstname, e.lastname, e.tc_identity) AGAINST ('Baha' IN BOOLEAN MODE)
ORDER BY `Score` DESC;

firstname, lastname and tc_identity are of type varchar.

I have data in firstname like 'Bahadir', 'Bahadır', etc...

When I try to match against 'Baha', I get no results.

When I try to match against 'Bahadir' or 'Bahadır', I get no results.

What's the problem?

Your query is looking for the token = 'Baha'. You need a wildcard search for 'Baha*'.

Please try the following

SELECT
MATCH (`e`.`firstname`, `e`.`lastname`, `e`.`tc_identity`) AGAINST ('Baha*') AS `Score`,
`e`.*

FROM
`employees` AS `e`

WHERE
MATCH (`e`.`firstname`, `e`.`lastname`, `e`.`tc_identity`) AGAINST ('Baha*' IN BOOLEAN MODE)

ORDER BY `Score` DESC


Oracle: Can a tablespace stay in backup mode forever? Recently I discovered in a legacy Oracle 8.1.7.3.0 database that the tablespace holding de AUD$ table has been in backup mode for six months. Dunno who issued the begin backup command on it six moths ago.

The scheduled backups are cold backups that will not issue a end backup command on any tablespace. The database can be shutdown and started up with no problemo.
The datafiles belonging to the AUD$ tables have a modification date of just right now.
When you query the AUD$ table you can see that it's being updated almost every minute. This table is inserted data continously.
I understand that REDO information is generated when a datafile is in active backup mode but that tablespace has been un backup mode for six months for Pete's sake.

My questions:

Can this tablespace cotinue in active backup mode forever with no consequences?
Is there a risk of losing data?
Is this a disaster waiting to happen?
What exactly is happening here?

When we put the tablespace in backup mode Oracle copies whole changed data blocks into the redo stream in order to make the backup safe from fractured block. Copying whole data block instead of only the change may degrade the performance. Oracle strongly recommends to put the tablespace in backup mode only when required.

What Happens When A Tablespace/Database Is Kept In Begin Backup Mode (Doc ID 469950.1) (MOS Account needed)

The following is the excerpt from the Oracle Documentation.

  Caution:

  If you fail to take the tablespace out of backup mode, then Oracle Database continues to write copies of data blocks in this tablespace to the online redo logs, causing performance problems. Also, you receive an ORA-01149 error if you try to shut down the database with the tablespaces still in backup mode.

Making User-Managed Backups of Online Tablespaces and Data Files


How to support custom fields for an entity and list them as columns? I am currently working on a system which supports "custom fields" for users.

I will illustrate this with a simplified example of this database schema:

users
--------
user_id
name
age

custom_fields
----------------
custom_field_id
custom_field_name

user_has_custom_field_value
------------------------
user_id (FK to users table)
custom_field_id (FK to custom_fields table)
custom_field_value (column that contains value of custom field)

This schema supports creation of custom fields to store information as per what the customer wishes.

However, I want to list all users along with their custom fields in the query output (or by using some PHP logic)

|  user_id    |    name     |    age    |    fav_sport    |    payment_mode   |    bank
----------------------------------------------------------------------------------
     3              John         28          Football           SWIFT             Chase
     6              Jane         24          Swimming           Cash               BOA

fav_sport, payment_mode and bank are custom fields created which are represented as 3 rows in the custom_fields table.

So, the custom_fields table looks like this :

custom_field_id     |      custom_field_name
-----------------------------------------------
       1                     fav_sport
       2                     payment_mode
       3                     bank

and the user_has_custom_field value looks like this :

user_id    |        custom_field_id     |       custom_field_value
----------------------------------------------------------------------
   1                       1                          Football
   1                       2                           SWIFT
   1                       3                           Chase
   2                       1                          Swimming
   2                       2                           Cash
   2                       3                           BOA

What is the best way to achieve this with minimum queries and without taxing the database ?

The only solution I can come up with is by using a query inside a for loop in PHP to fetch the custom fields (which will definitely be heavy). What are more efficient solutions to do this ?

Suggestions welcome for MySQL based, PHP based, Stored Procedures etc.

You have two options: either you loop results from PHP, or you store all the custom fields and values in one single JSON datatype field (stored as a json object, requires MySQL 5.7) in the users table.


How to recover the database with old data? I am using PostgreSQL. I have 4 database servers.  Each server has same database and same schema. Mistakenly I restored the database util05db backup onto the util01db database.

How can I get the util01db before restored?

if you have a base backup AND full WAL archives you can use point in time recovery.  Otherwise you are stuck.  good luck.  I hope you have something you can work with.

Otherwise you are screwed.  There is no way to roll back restoring a backup in this way.  If you didn't drop relations in the process you MIGHT be able to sort things out using min_xid columns on the tables.  In that case you will have a lot of work.....


Database Design for Paper Submission System I am learner of Database design, I want to design a database for a journal paper submission system in which one paper can be submitted by one or more author. I have initially two tables Author and Paper

Table Author has attributes like,

AuthorID
Name
Contact
Email
URL

Table Paper has attributes like

PaperID
Title
Abstract
Keywords

I need a third table submission where i can preserve association between paper and authors/author. Can any one help me in this regard to find possible association.

If all you want to do is keep track of which authors contributed to which papers, then all you need is a simple intersecting entity (see more) like this:

Here the columns of the SUBMISSION table are just the two primary key columns from PAPER and AUTHOR.  These two columns will jointly form the primary key of the intersecting table and will also each individually be foreign keys to their respective tables.

NOTE: Don't add any other columns to the SUBMISSION table, unless those columns can reasonably be expected to vary row by row for the same paper and for the same author.  For example, it would be a bad idea to add a date to the submission table unless a single paper is submitted at various times by multiple authors.  If you need to track information about the event of an author or group of authors submitting a paper, then you need to normalize out the extra information to avoid creating update anomalies.  Consider the following:

In the above ERD, notice how the extra information that is not author-dependent remains at the SUBMISSION level, while the new intersecting entity CONTRIBUTION is added to record the connections between authors and a particular instance of the submission of a paper.

This four table model is more robust because it allows for papers to be resubmitted multiple times with changes in authorship each time.  If this kind of real world nuance is important to your system then you would be better off with the four table approach than with a three table approach.

Here is the four table model with more column details at OP's request:


one trigger for multiple tables i have a trigger in PostgreSql 9.1 that i want to fire on inserts of several tables. is it possible to have it affect all these tables instead of creating the trigger for all these tables?
i have 58 tables that i want to use the same insert trigger which is calling a trigger function, so i have been using

create trigger tuweke
after insert on product
for each row execute procedure tuwekeAdjustextract();
create trigger tuweke
after insert on caneweightment
for each row execute procedure tuwekeAdjustextract();
...

doing this for all those tables and then we have multiple schemas,so the workload is alot,can this be done in one query? then affect all tables in that schema or the whole database?

There is no option like that.  A trigger belongs to a table and that is that.  However, if all your triggers use the same procedure, you can easily generate a nice text output with all the CREATE TRIGGER statements.  The only thing to do is to collect the desired table names and then prepend and appent the necessary parts to those.

You can get all table names from a given schema with a query like this:

SELECT p.tablename
FROM pg_tables p
WHERE p.schemaname = 'public'
;

If now you select

'CREATE TRIGGER tuweke
AFTER INSERT ON ' || p.tablename || '
FOR EACH ROW EXECUTE PROCEDURE tuwekeAdjustextract();
'

instead of p.tablename then you are ready.  Copy the output and run it.


Can't find missing packages Oracle Linux 8 super newbie here. Trying to learn DBA stuff.
I've been trying to install oracle database 18c on Oracle Linux 8 but there are two missing packages that are just not being found. I need these to complete the installation properly:
 nothing provides compat-libcap1 needed by oracle-database-preinstall-18c-1.0-1.el7.x86_64
- nothing provides compat-libstdc++-33 needed by oracle-database-preinstall-18c-1.0-1.el7.x86_64

These two commands do not work.
rpm -ivh compat-libstdc++-33-3.2.3-72.el7.x86_64.rpm
rpm -ivh compat-libcap1-1.10-7.el7.x86_64.rpm
I've looked a lot online.
Thanks in advance.

You want to install the "oracle-database-preinstall-18c" package, using the "yum" utility as root. That will load all of your dependencies for you:
[root@orcl1 ~]# yum search oracle-database-preinstall-18c
Loaded plugins: langpacks, ulninfo
================================================================== N/S matched: oracle-database-preinstall-18c ===================================================================
oracle-database-preinstall-18c.x86_64 : Sets the system for Oracle Database single instance and Real Application Cluster install for Oracle Linux 7

[root@orcl1 ~]# yum install oracle-database-preinstall-18c
Resolving Dependencies
--> Running transaction check
---> Package oracle-database-preinstall-18c.x86_64 0:1.0-1.el7 will be installed
--> Processing Dependency: compat-libstdc++-33 for package: oracle-database-preinstall-18c-1.0-1.el7.x86_64
--> Running transaction check
---> Package compat-libstdc++-33.x86_64 0:3.2.3-72.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

==================================================================================================================================================================================
 Package                                                   Arch                              Version                                  Repository                             Size
==================================================================================================================================================================================
Installing:
 oracle-database-preinstall-18c                            x86_64                            1.0-1.el7                                ol7_latest                             18 k
Installing for dependencies:
 compat-libstdc++-33                                       x86_64                            3.2.3-72.el7                             ol7_latest                            190 k

Transaction Summary
==================================================================================================================================================================================
Install  1 Package (+1 Dependent package)

Total download size: 208 k
Installed size: 866 k
Is this ok [y/d/N]:

It should be noted that Oracle 18c isn't supported on Oracle Linux 8; ideally you should be using Oracle Linux 7. See these articles for install steps and guidelines:

Database Installation Guide for Linux
Oracle Database 18c Installation On Oracle Linux 8 (OL8)


What may be the consequences of deleting the system keyspace? I have a Cassandra cluster composed of 2 nodes. One node could not start Cassandra yesterday. I deleted the system keyspace directory and I started the node again. The node is running after system keyspace directory deletion.

What are consequences of system keyspace missing?

All data are present in my cluster and I find no errors at the moment.

The system keyspace is where a Cassandra node maintains information about itself and other nodes.  So things like network addresses, responsible token ranges, status from gossip, hints, schema, and other local information.  The system keyspace has its own, special replication strategy known as "LocalStrategy:"

cqlsh:system> desc KEYSPACE;

CREATE KEYSPACE system WITH replication = {
  'class': 'LocalStrategy'
};

This should tell you that data in the system keyspace isn't replicated to other nodes, and is largely specific to itself.

Sometimes you can fix gossip-related issues by blowing-away the system keyspace (or even just all entries in system.peers).  Removing it via the filesystem (as you did) has the same effect.  The reason this works, is because it forces the node to rewrite all of that data, most of which it already has (system.local) or it can learn from gossip.

While I wouldn't make a habit out of it, deleting the system keyspace via the filesystem is ok...as long as you have another node in the cluster for it to communicate with.


Trying to disable xp_Cmdshell and rpc_out and and receiving mixed results I'm trying to disable xp_Cmdshell and rpc_out and when I run the commands on query analyzer it shows it's disabled.

But after this I need to run a security scan report which provides me the following report that it's has not been disabled. Can anyone help me?

  5 Microsoft SQL Server Database Link Crawling Command Execution

  QID:
  19824

  Category:
  Database

  CVE ID:
  -

  Vendor Reference
  -

  Bugtraq ID:
  -

  Service Modified:
  02/20/2013

  User Modified:
  -

  Edited:
  No

  PCI Vuln:
  Yes

  THREAT:
  Microsoft SQL Server is exposed to a remote command execution vulnerability.

  Affected Versions:
  Microsoft SQL Server 2005, 2008, 2008 R2, 2012 are affected.

  IMPACT:
  Successful exploitation could allow attackers to obtain sensitive information and execute arbitrary code.

  SOLUTION:
  There are no solutions available at this time.
  Workaround:
  Disable RPC_Out and xp_cmdshell for this issue.

  COMPLIANCE:
  Not Applicable

  EXPLOITABILITY:
  There is no exploitability information for this vulnerability.

  ASSOCIATED MALWARE:
  There is no malware information for this vulnerability.

  RESULTS:
  C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Binn\sqlservr.exe Version is 2009.100.4000.0

You can see the actual value with the following tiny script:

SELECT *
FROM sys.configurations
where name='xp_cmdshell'

You can also do it this way to see all advanced options:

EXEC sp_configure 'Show Advanced Options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure;


How to switch Quiescing to Normal Mode in Oracle? I have two instance database Oracle, they are ORCL11 and ORCL10,.... I've configured them, it looks like correct. Then, I connect to replication user admin ORCL10

conn repadmin/repadmin@orcl10

And run SQL command in replication admin user ORCL10,...

BEGIN DBMS_REPCAT.RESUME_MASTER_ACTIVITY (gname=>'scott_repg');end;/

Then, i connect to replication admin user ORCL11 to see gname status.

conn repadmin/repadmin@orcl11;
select gname,status from dba_repgroup;

It display gname = SCOTT_REPG and status = NORMAL,.... but if connect to replication admin ORCL10

conn repadmin/repadmin@orcl10;
select gname,status from dba_repgroup;

It display that gname = SCOTT_REPG and STATUS = QUIESCING, I think it does not have any impact, so I test my replication on ORCL10, for example :

conn system/ok@orcl10;
insert into DEPT values (101,'Financial');
select*from DEPT;

It works on table DEPT ORCL10 (1 row created), but when i switch to ORCL11 then run simple sql query :

conn system/ok@orcl11;
select*from DEPT;

it doesn't show anything, i've searched it on Google but there is nothing tutorial how to fix that, I think the problem is about how to switch QUIESCING to NORMAL mode at ORCL10. I've try this way to fix it :

ALTER SYSTEM QUIESCE RESTRICTED;
ALTER SYSTEM UNQUIESCE;

But it still doesn't solve the problem,..... Would you help me to fix this problem ??? thanks !!!

You are mixing up quiescing a replication master group and quiescing a database — these are different concepts:

ALTER SYSTEM QUIESCE

Use the QUIESCE RESTRICTED and UNQUIESCE clauses to put the database in and take it out of the quiesced state. This state enables database administrators to perform administrative operations that cannot be safely performed in the presence of concurrent transactions, queries, or PL/SQL operations.

Master Group Quiesce

At times, you must stop all replication activity for a master group so that you can perform certain administrative tasks on the master group. For example, you must stop all replication activity for a master group to add a new master group object. Stopping all replication activity for a master group is called quiescing the group. When a master group is quiesced, users cannot issue DML statements on any of the objects in the master group. Also, all deferred transactions must be propagated before you can quiesce a master group. Users can continue to query the tables in a quiesced master group.

After quiescing a replication master group, RESUME_MASTER_ACTIVITY resumes normal replication activity.


How did my databases shrink? I have a nightly job that monitors database and log file sizes.  Over time the databases grow.  Last night some databases actually shrank in size.  The logs show no SHRINKFILE OR SHRINKDATABASE events.  AUTOSHRINK is OFF.  Is there any other way these databases could have shrank?

You can use the Power of Default Trace to find out the culprit.

provided the default trace files are not rolled over

-- find out the default trace location

SELECT *
FROM fn_trace_getinfo(default);
GO

   --- check the events

SELECT
    TextData,
    HostName,
    ApplicationName,
    LoginName,
    StartTime
FROM
[fn_trace_gettable]('Location of Tracefile.trc', DEFAULT)
WHERE TextData LIKE '%SHRINKFILE%';


Importing a large tsv file to mongodb I'm trying to import a large one giga tsv file names bouffe.csv. It is in ̀ mongo.../bin but seems there is a syntax error :

> ./mongoimport -d db_name -c collection_name --type tsv --file bouffe.csv -f --numInsertionWorkers 8 --headerline

2017-12-05T22:11:01.064+0100 E QUERY    [thread1] SyntaxError: expected expression, got '.' @(shell):1:0

I am not able to handle it, I am quite new to this database system.

Here is an overview :

@Marine1,You mongoimport syntax should be like as mention below example.

mongoimport --db test --collection stuff --type csv C:\data\Employee.csv --headerline

In the above example test is database name, stuff is collection name and Employee.csv is the file which i want to import in MongoDB database through mongo shell. And the Employee.csv file location is C:\data\Employee.csv.
In My case it has worked properly.

In your case the syntax should be like that

mongoimport --db databasename --collection collectionname --type csv  locationofbouffe.csv  --headerline

The syntax should be in the above mention format, if you want to import the header of the file then mention --headerline.

Note:- Make sure you are running the above mention script in inside the BIN of MongoDB Server.

Very Important Note : mongoimport would be to import a non-BSON file (such as JSON or CSV) into mongodb.


MySQL 5.6 DateTime Incorrect datetime value: '2013-08-25T17:00:00+00:00' with Error Code 1292 I'm using MySQL 5.6 and I have a program that runs the following SQL statement against my database:

UPDATE `m_table` SET `s_time` = '2013-08-25T17:00:00+00:00' WHERE id = '123'

Unforutnately, I get the following error:
Incorrect datetime value: '2013-08-25T17:00:00+00:00' for column 's_time' at row 1

The datatype for s_time is DateTime.

I have already attempted to set the allow_invalid_dates property using the workbench.

Can anyone understand and please explain this error to me?  I know that if I manually change the statement to UPDATE m_table SET s_time = '2013-08-25 17:00:00' WHERE id = '123', the statement works.

Unfortunately, I cannot modify the program that supplies the SQL statement (which I'm told is valid by the creator of the program) and I also cannot understand what the +00:00 symbolises.

Thanks

'2013-08-25T17:00:00+00:00'

This is a valid iso-8601 datetime value, but it is not a valid MySQL datetime literal.  On that point, the developer is incorrect.

The documentation explains what ALLOW_INVALID_DATES does:

  Check only that the month is in the range from 1 to 12 and the day is in the range from 1 to 31.

In other words, 2013-02-31 would be a permissible date if allow_invalid_dates is set.  This option does not do anything when the date or datetime isn't even in a valid format for MySQL.

The +00:00 is the timezone offset from UTC.  In this case, the time expressed is in UTC, so the offset is zero hours, zero minutes.

Your workaround would be to remove the STRICT_TRANS_TABLES from the sql_mode that is a default in the config file created during the MySQL 5.6 installation process... you need to carefully consider the implications of changing this, but it does allow the data to go in.

mysql> select @@sql_mode;
+--------------------------------------------+
| @@sql_mode                                 |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into datetimetest(dt) values ('2013-08-26T12:00:00+00:00');
ERROR 1292 (22007): Incorrect datetime value: '2013-08-26T12:00:00+00:00' for column 'dt' at row 1

-- remove STRICT_TRANS_TABLES -- note that executing this only removes it for your
-- current session -- it does not make a server-wide config change

mysql> set @@sql_mode='no_engine_substitution';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@sql_mode;
+------------------------+
| @@sql_mode             |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.00 sec)

-- now MySQL will accept the invalid value, with a warning

mysql> insert into datetimetest(dt) values ('2013-08-26T12:00:00+00:00');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1265 | Data truncated for column 'dt' at row 1 |
+---------+------+-----------------------------------------+
1 row in set (0.00 sec)

-- the value did get inserted, but the time zone information was lost:

mysql> select * from datetimetest;
+----+---------------------+
| id | dt                  |
+----+---------------------+
|  1 | 2013-08-26 12:00:00 |
+----+---------------------+
1 row in set (0.00 sec)


SSRS Query Timeout settings: rsreportserver.config verses RDL Query.Timeout I'm trying to understand the interaction between the SSRS DatabaseQueryTimeout (set at the server level, defaults to 120 - does this only apply to connections to the ReportServer internal database?)

RsReportServer.config Configuration File

and the RDL Query.Timeout setting (set in individual RDL, defaults to 30)

Setting Time-out Values for Report and Shared Dataset Processing (SSRS)

If I have an RDL with no setting defined, will it timeout at 30s or 120s?

The answer is in your first link:

  DatabaseQueryTimeout  -  Specifies the number of seconds after which a connection to the report server database times out.

This setting only applies to the connections from the RS services to the RS databases, not for individual reports to their data sources. This makes sense given:

  This value is passed to the System.Data.SQLClient.SQLCommand.CommandTimeout property

What if your data source is not SQL, what if it is Oracle or MongoDB or some other data source? This setting wouldn't exist for that connection object.

The answer to your question is, with no setting defined your timeout would be 30s.


Oracle paging query performance I'm new to Oracle and I've noticed that pagination queries take the format.

select *
from ( select /*+ FIRST_ROWS(n) */
         a.*, ROWNUM rnum
       from
         ( your_query_goes_here with filter and order by ) a
       where
         ROWNUM <= :MAX_ROW_TO_FETCH )
where
  rnum  >= :MIN_ROW_TO_FETCH;

http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html

Why is there an extra outer select to split the min and max rownum where clause? Would this be just as performant and correct?

select /*+ FIRST_ROWS(n) */
  *
from
  ( your_query_goes_here with filter and order by )
where
  ROWNUM between :MIN_ROW_TO_FETCH and :MAX_ROW_TO_FETCH

Shouldn't ROWNUM be already be correct since the inner query filters and orders the values?

No, the second query won't work.  It will always return 0 rows if :MIN_ROW_TO_FETCH is greater than 1.

Conceptually, if you have a query like

select * /*+ FIRST_ROWS(n) */
  from ( your_query_goes_here with filter and order by )
  where ROWNUM between :MIN_ROW_TO_FETCH and :MAX_ROW_TO_FETCH

where :MIN_ROW_TO_FETCH is 11 and :MAX_ROW_TO_FETCH is 20, Oracle would fetch the first row, give it a rownum of 1, then discard it because it didn't satisfy the predicate.  It would then fetch the second row from the inner query, give it a rownum of 1 (since no rows have been successfully returned yet), then discard it because it didn't satisfy the predicate.  That would repeat until every row had been fetched, assigned a rownum of 1, and discarded.  So the query would return 0 rows.

The extra layer of nesting ensures that the outer query with rnum  >= :MIN_ROW_TO_FETCH sees rows with rnum values between 1 and :MAX_ROW_TO_FETCH and that the full query returns the expected set of rows.

If you're using 12.1 or later, you can use the OFFSET ... FETCH syntax as well

SELECT *
  FROM table_name
 ORDER BY some_column
 OFFSET n ROWS
  FETCH NEXT m ROWS ONLY;


Is there any fastest way to shrink LOB data in SQL Server We have a database of total size of 3886 GB. Out of which 778 GB is free. So total 3108 GB worth of data is present.

Out of which 2458 GB is "LOB Data" and 400 GB is worth "Regular".

Since the database is hosted on AWS, so in order to save cost, we are trying to shrink data file in order to free up space. Son in order to accomodate data, we already have two drives of 2 TB each and both are almost FULL and my manager do not want to create another drive and put new files over there.

But when we tried to shrink 2 GB at a time, it was taking lot of time (saw dbcc lobcompact running in sp_who2) and we are sometimes seeing blocking as well. After googling, I came across below blog post from Paul S. Randal and he also states LOB data makes shrinking go slow.

https://www.sqlskills.com/blogs/paul/why-lob-data-makes-shrink-run-slooooowly-t-sql-tuesday-006/

My concern is:

Is there any way to make shrink go faster in this case or there is no way at all?
What other people are doing in such scenarios when it comes to cloud and they want to save some cost by reducing storage size?
Is my understanding correct that shrinking files can some way save costs to organization or am I wasting my time and worsening things up? If yes, then how can I convince him?

Shrinking a database is not fast. In fact, it tends to be incredibly slow, and there's nothing you can do to speed it up, other than to have less data to shrink.

How does shrinking work?

When you shrink a data file, SQL Server takes the 8k pages from the end of the file, and moves them to a (random) spot at the beginning of the file. It moves the pages one page at a time. In a way, the page-by-page movement of data is similar to the way a REORGANIZE works, except in reverse. Because the pages are moved individually, to a (random) new spot, it will "disorganize" pages by making previously-contiguous pages non-contiguous, creating fragmentation.

The page-by-page movement is inherently slow, which is why it is faster to REBUILD highly-fragmented indexes than to REORGANIZE them.

REBUILD instead

Normally, I'd suggest that you just REBUILD into a new filegroup, then remove or shrink the old (empty) filegroup. The REBUILD into the new filegroup will be faster than shrinking, because REBUILD if faster then REORGANIZE (and "backwards REORGANIZE" aka shrink). Then, with the old filegroup empty, it will be quick to remove or shrink when it is empty or almost empty, because there are no/few pages to remove.

And because shrinking creates fragmentation, you are going to need to do a REORGANIZE after you shrink anyway.

LOBing a curveball

In your scenario, LOB data represents 2458GB out of 3108GB total data (80%). When you do a REBUILD it will move the B-Tree pages, but it will not move the LOB data. So if you rebuild into a new filegroup, your old filegroup will still be 60+% full.

For the table(s) that have a significant amount of LOB data, you would need to take a different approach. You could create a new table in the new filegroup, migrate data to that new table, then swap table names and drop the old table. You could think of this process as a "manual rebuild"--except doing it this way would allow you to copy LOB data to the new filegroup. I describe this method in a blog post on changing data types, but the exact same method would work in this case--except you would be changing filegroups, rather than changing data types.

Shrinking (nearly) empty filegroups is fast

Shrinking is slow because it has to move allocated pages in order to create contiguous free space at the end of the data file. If the filegroup is empty, the end of the file can be truncated without moving pages and will be very fast.

If the filegroup you are shrinking is PRIMARY then you won't be able to get it completely empty--PRIMARY contains all the system tables & metadata. By moving all your user tables out of PRIMARY, the filegroup will be almost empty, and there will be a very small number of pages to move. Having just a few pages to move will also be pretty fast, particularly compared to moving 2TB of pages.


Full backup on database creation When new databases are created the Differential and transaction log backup maintenance plans start failing and sending alerts as full backup is not taken. What could be the best solution to handle it.

I tried to do full backup on database created trigger but that is failing as backup cannot be taken in the same transaction.

To achieve this task kindly follow below steps:-

create server level trigger on create_database.
Create sql job and add code to dynamic get the name of database and initiate backup.
Add Code in the trigger to invoke job to create database backup.

e.g

CREATE TRIGGER ddl_trig_database
ON ALL SERVER
FOR CREATE_DATABASE
AS
   EXEC msdb.dbo.sp_start_job N'backup_job' ;
GO


How to convert UPDATE operation into INSERT using triggers Given I have a table User with column status (active/inactive)

these users may be attached with some other entities, so I do not want them to get modified unless explicitly specified.
and for attaching to newer entities I only need the latest updated ones.

So when an UPDATE is performed, I want to make the existing row inactive,
and INSERT the updated values as a new row.

I am using PostgreSQL

This is not very complicated to do. If a BEFORE trigger returns a NULL value, this prevents the UPDATE from taking place.

So in the trigger function you can check the conditions if an UPDATE is allowed. If not, Run an UPDATE statement that marks the row as inactive and do an INSERT.

Something along the lines (not tested!):

create or replace function prevent_update()
  returns trigger
as
$$
begin
   -- adjust this check to whatever you want to prevent
   -- but make sure to only enter this part if the status is not inactive.
   -- you might want to check pg_trigger_depth() as well to make sure you never create an endless loop
   if old is distinct from new and new.status <> 'inactive' then

     -- The row was changed, but status column is the same, so we want to mark
     -- the row as inactive

     -- Because the new status is 'inactive' the trigger that is now fired for this UPDATE statement
     -- will not do anything because we only do this for status other than 'inactive'
     UPDATE the_table
       SET status = 'inactive'
     WHERE id = new.id; --<< the primary key column

     INSERT INTO the_table (id, some_column, status)
     VALUES (default, new.some_column, 'active');

     -- do NOT proceed with original UPDATE
     return null;
   end if;

   -- all OK, proceed with the update
   return new;
end;
$$
language plpgsql;

And the trigger definition:

create trigger prevent_update_trigger
   before update on the_table
   for each row execute procedure prevent_update();

I would be very careful to deploy something like this. This kind of "magic" in the background might be the cause of a lot of confusion and trouble, so use with care!


PostgreSQL GiST compress skeleton when no compression required I'm trying to implement my GiST index, where storage type is the same as column type (bytea). They are even going to have same length because these are bitarrays and unions are just disjunctions of all arrays in set (in other words, bitwise OR of all arrays).

Because of that, I don't need to convert or compress data. In that case, is this a correct implementation of compress function?

Datum
sub_fp_compress(PG_FUNCTION_ARGS)
{
    GISTENTRY  *entry = (GISTENTRY *)PG_GETARG_POINTER(0);
    // Column and Storage data types are the same (bytea)
    // so no compression is required.
    GISTENTRY  *retval = entry;

    PG_RETURN_POINTER(retval);
}

The reason why I'm asking is that when creating index I get some unexpected values and first occurrence of this is in compress function. To be more precise, all my bitarrays stored in DB are 256 bytes long (checked) and the GiST nodes I create have same length. But all of sudden I get in penalty function as second newentry param a bitarray that is 9 bytes long. I have traced this to compress function where it first appears so it's first place I need to check.

I think this would actually work, but I would follow the same format used in the docs for consistency on the decompress version that doesn't require compression,

PG_FUNCTION_INFO_V1(my_decompress);

Datum
my_decompress(PG_FUNCTION_ARGS)
{
    PG_RETURN_POINTER(PG_GETARG_POINTER(0));
}

My assumption here is that because you don't actually need to initialize a new GIST entry to return for Pg to write it disk, and because you're just using binary stuff, you can use one function for both

PG_FUNCTION_INFO_V1(id);

Datum
id(PG_FUNCTION_ARGS)
{
    PG_RETURN_POINTER(PG_GETARG_POINTER(0));
}

But you may have to DETOAST too which the docs eerily don't mention.


some tips on setting up sql server database server I am currently setting up a new SQL Server database server with databases. It is supplied by a third party company.

I need to request access for them around ports etc.

I would like to be able connect to the SQL Server instance on the above database from SQL Server Management Studio on my PC. What do I need to get setup on the server so I can get such access?

The other question I have is that I have a number of databases on other servers, I will like to "copy" these databases to the new server. I need to copy both structure and data. What is the best way to do such?

The standard SQL port is 1433. Though, the provider could be using another port. So, you'll need to ask them what port they're exposing.

Once you know the port, you can connect to the database instance using the following connection string (substituting placeholders with appropriate values):

"Data Source=111.222.333.444,1433;
Network Library=DBMSSOCN;
Initial Catalog=[databasename];
User ID=[username];Password=[password];"

Substitute 111.222.333.444,1433 with the appropriate ip address and port.

  The other question I have is that I have a number of databases on
  other servers, I will like to "copy" these databases to the new
  server. I need to copy both structure and data. What is the best way
  to do such?

You can either backup and restore the database or you can generate scripts to create the schema and insert data. To generate the schema and data scripts go to:

object explorer => [right-click] database => select tasks => "generate scripts"

Then, under the "advanced options" make sure schema and data is selected for "Types of data to script":


Postgres - Trying to backup Functions only I'm trying to follow Craig Ringer's answer to this question:
https://stackoverflow.com/questions/13758003/how-to-take-backup-of-functions-only-in-postgres

My command is:

psql -U username -AT db_name -c "
    SELECT pg_get_functiondef(f.oid)
    FROM pg_catalog.pg_proc f
    INNER JOIN pg_catalog.pg_namespace n ON (f.pronamespace = n.oid)
    WHERE n.nspname = 'public'
" > /dump/file/functions.sql

But I'm getting the following error:

Error: "st_extent" is an aggregate function

Why doesn't it like aggregate functions? And what can I do to get around this problem?

There are at least two problems with this:

Don't include functions installed by an extension. Just install the extension to "restore" that function to any other database.

Aggregate functions are completely different and have to be treated separately. The CREATE AGGREGATE syntax differs from CREATE FUNCTION.

Don't you want to include the COMMENT for each function? (If one exists.)

I suggest these two queries:
Functions
-- SET LOCAL search_path = '';  -- force schema-qualification everywhere
SELECT pg_get_functiondef(p.oid) AS function
     , 'COMMENT ON FUNCTION ' || p.oid::regprocedure
       || ' IS ' || quote_literal(obj_description(p.oid, 'pg_proc')) AS comment
FROM   pg_proc p
JOIN   pg_namespace n ON n.oid = p.pronamespace
WHERE  n.nspname !~ '^(pg_|information_schema)'    -- exclude system schemas
-- AND    n.nspname =  ...                         -- or from selected schema only?
AND    NOT EXISTS (                                -- exclude functions from extensions
   SELECT FROM pg_depend
   WHERE  objid = p.oid
   AND    deptype = 'e'
   )
--    AND    NOT p.proisagg                        -- exclude aggregate functions! for pg 10 or older
AND    NOT p.prokind = 'f'                         -- only normal functions! pg 11+
ORDER  BY n.nspname, p.proname;

Various types of dependencies are stored in the system catalog pg_depend. deptype = 'e' indicates it's part of an extension.
About proisagg vs prokind:

PostgreSQL 11 error: column p.proisagg does not exist

Aggregates (= aggregate functions)
-- SET LOCAL search_path = '';  -- force schema-qualification everywhere
SELECT format('CREATE AGGREGATE %s (SFUNC = %s, STYPE = %s%s%s%s%s)'
            , a.aggfnoid::regprocedure
            , a.aggtransfn
            , a.aggtranstype::regtype
            , ', SORTOP = '    || NULLIF(a.aggsortop, 0)::regoper
            , ', INITCOND = '  || a.agginitval
            , ', FINALFUNC = ' || NULLIF(a.aggfinalfn, 0)
            , CASE WHEN a.aggfinalextra THEN ', FINALFUNC_EXTRA' END
            --  add more to cover special cases like moving-aggregate etc.
              ) AS aggregate
     , 'COMMENT ON AGGREGATE ' || aggfnoid::regprocedure
       || ' IS ' || quote_literal(obj_description(aggfnoid, 'pg_proc')) AS comment
FROM   pg_aggregate a
JOIN   pg_proc      p ON p.oid = a.aggfnoid
JOIN   pg_namespace n ON n.oid = p.pronamespace
WHERE  n.nspname !~ '^(pg_|information_schema)'    -- exclude system schemas
-- AND    n.nspname = 'public'                     -- or from selected schema only?
AND    NOT EXISTS (
   SELECT FROM pg_depend
   WHERE  objid = a.aggfnoid
   AND    deptype = 'e'
   )
ORDER  BY n.nspname, p.proname;

This only covers plain aggregate functions. Further reading:

How to get definition/source code of an aggregate in PostgreSQL?

To make it complete, you might want want add ownership (ALTER FUNCTION ... OWNER TO ...;) and privileges (GRANT / REVOKE).


Postgresql Replica is in sync but repmgr says node unattached I've set up a replica of a Postgresql instance using repmgr, and the replica is totally in sync with the master instance and have been in sync since some days, but repmgr tells me replica isn't connected.
This is what the cluster looks like:

postgres@www:~$ repmgr cluster show
 ID | Name             | Role    | Status    | Upstream    | Location | Connection string
----+------------------+---------+-----------+-------------+---------+------------------------------------------------------------------
 1  | orig_master      | primary | * running |             | default  | host=MASTER user=USER dbname=repmgr connect_timeout=2
 2  | orig_slave       | standby |   running | orig_master | default  | host=SLAVE user=USER dbname=repmgr connect_timeout=2

This is what we get on the replica instance:

postgres@db:~$ repmgr node status
Node "orig_slave":
    PostgreSQL version: 9.6.11
    Total data size: 5603 MB
    Conninfo: host=SLAVE user=USER dbname=repmgr connect_timeout=2
    Role: standby
    WAL archiving: disabled (on standbys "archive_mode" must be set to "always" to be effective)
    Archive command: rsync -a %p barman@BARMAN:/mnt/volume/prod/incoming/%f
    WALs pending archiving: 0 pending files
    Replication connections: 0 (of maximal 6)
    Replication slots: disabled
    Upstream node: orig_master (ID: 1)
    Replication lag: 0 seconds
    Last received LSN: 63/BB0E3C10
    Last replayed LSN: 63/BB0E3C10

Which clearly shows that we have no relpication lag and instances are in sync, and WAL numbers are correct too.
This is what I get on the master instance:

postgres@www:~$ repmgr node status
Node "orig_master":
    PostgreSQL version: 9.6.11
    Total data size: 5603 MB
    Conninfo: host=MASTER user=USER dbname=repmgr connect_timeout=2
    Role: primary
    WAL archiving: enabled
    Archive command: rsync -a %p barman@BARMAN:/mnt/volume/prod/incoming/%f
    WALs pending archiving: 0 pending files
    Replication connections: 1 (of maximal 6)
    Replication slots: disabled
    Replication lag: n/a

WARNING: following issue(s) were detected:
  - 1 of 1 downstream nodes not attached:
    - orig_slave (ID: 2)

HINT: execute "repmgr node check" for more details

Which tells me that downstream node isn't connected, which is clearly connected!
Now, my question is: Is there actually a problem in replication process? If yes, then What is it and how can I solve it? If not, how can I make repmgr sure that there is no problem?

P.S.: Postgresql 9.6.11, repmgr 4.2

Maybe you can try running the following command from the standby node:

/usr/pgsql-10/bin/repmgr -f /var/lib/pgsql/repmgr/repmgr.conf standby follow

The paths of the repmgr file and the repmgr.conf configuration file may vary according to the installation.


Do table locks get released after a query finishes when WAITFOR is used after that query within the same batch and transaction? If I have a query that reads from a table, if that query is proceeded with the WAITFOR keyword (within the same batch and transaction) will the table lock of the preceding query be held until the WAITFOR completes and the transaction finishes? (I mean in the context of a Read Committed isolation level server.)

Example query:

BEGIN TRANSACTION

SELECT *
FROM ReallyLargeTable

WAITFOR DELAY '00:30';

COMMIT TRANSACTION

Does the WAITFOR DELAY cause the preceding query to hold its table lock against ReallyLargeTable for an additional 30 seconds until the delay is over and the transaction is committed?

It depends on the transaction isolation level.

In read committed it will release locks.
In "Repeatable read" and "Serializable" it will hold locks.

You can find more info in the following article: SET TRANSACTION ISOLATION LEVEL


Cardinality is so far off from reality I've got a table where the cardinality for plans just seems waaaaaay too high - despite updating stats and even trying dynamic sampling for Oracle 11gR2.

select count(*)
from table1
where col1 = 123
and col2 = '1';

The estimated cardinality for one value of col1 is roughly 29k, when the actual rows it returns is 637.  For another value, its plan cardinality 460k when the actual rows is 67k.  In general, this is causing it to choose very bad plans..

I've tried updating stats:

exec DBMS_STATS.gather_table_stats (ownname => 'ME', tabname =>'table1');

or

exec DBMS_STATS.gather_table_stats (ownname => 'ME', tabname =>'table1',estimate_percent =>100);

And I've tried various values of dynamic_samping (1-10), but nothing changes it signifcantly:

select /*+ dynamic_sampling(t1 10) */ count(*)
from table1 t1
where col1 = 123
and col2 = '1';

dyanmic_sampling will alter, but its still off by nearly a factor of 50-100 times.

How can I get better estimates?

The first thing I'd do is check the stats for the columns individually with these queries:

select count(*) from table1 where col1 = 123;
select count(*) from table1 where col2 = '1';

If the estimated cardinalities here agree much better with the actual row counts, it means there is some correlation between the columns that the CBO cannot guess based on the histograms generated by a vanilla gather_table_stats. Dynamic sampling should improve things at the cost of increased parse time, but is not a silver bullet.

Alternatively, with 11g, "Oracle Database can also gather statistics on a group of columns within a table"

create a column group
gather stats on a column group


Oracle: Create a hidden user for RMAN backups only It's been a long time since I worked with Oracle and I can't remember whether the following is possible:

I'm trying to create a hidden DBA user [ alternatively do this process as root user ] to perform complete and differential backups on Oracle 11g with RMAN. I cannot use the "SYS" user, because I don't have the password and I don't want to change it.

The sole purpose for the hidden user is to have complete db backups in case of a problem, since we suspect the software company that should be providing us with servicing our application and db of damaging the database, either out of sheer incompetence or on purpose. We don't care which it is, our company needs to be able to fix such issues ASAP so that we can work while the software provider sorts out their problems in their own time. But the only type of backup they do is a db dump and it's insufficient.

In order to do RMAN backups you need the SYSDBA or SYSOPER roles. In order to grant those you need to be able log on as a user with those roles: ie the SYS role.

There are variations where you use OS authentication or if your OS user is a member of the ORA_DBA group.

Easiest is if you can do this:

CONNECT / AS SYSDBA

from the server then you can run an RMAN script like

RMAN> BACKUP AS BACKUPSET DATABASE PLUS ARCHIVELOG;

This very basic script should be configured with details particular to your setup such as the location to backup the files to, crosscheck existing backups, etc.


last_value ignore null in left join with additional dimension? This is just a silly example that I started to think about, but can't seem to come up with an elegant solution for. Assuming a Calendar table (I'll use Db2 syntax):
create table calendar
( dt date not null primary key
);

insert into calendar (dt)
with t (dt) as ( values cast('2020-01-01' as date)
                 union all
                 select dt + 1 day from t where dt < '2020-01-11')
select dt from t
;

and a table that holds the balance:
create table balance
( dt date not null
, amount int not null
,     primary key (dt)
);

insert into balance (dt, amount)
values ('2020-01-03',100) ,('2020-01-05', -50);

If we want to copy the last known balance, we can use LAST_VALUE and 'IGNORE NULLS' like:
select c.dt, last_value(b.amount, 'IGNORE NULLS') over (order by c.dt)
from calendar c
left join balance b
    on c.dt = b.dt;

However, if we add a dimension, say cid (customer_id), it is no longer obvious what last_value means. Normally we would partition by cid, but cid is lost due to the left join:
create table balance1
( cid int not null
, dt date not null
, amount int not null
, primary key (cid, dt)
);

insert into balance1 (cid, dt, amount)
values (1, '2020-01-03',100) ,(1, '2020-01-05', -50)
     , (2, '2020-01-04',75), (2, '2020-01-08',55), (2, '2020-01-10', -35);

select c.dt, last_value(b.amount, 'IGNORE NULLS') over (partition by ?
                                                        order by c.dt)
from calendar c
left join balance b
    on c.dt = b.dt;

The best I could come up with was to use a cartesian product between calendar and distinct customers from balances:
select cid, dt, last_value(amount, 'IGNORE NULLS') over (partition by cid order by dt)
from (
  select cid, dt, amount from balance1
  union
  select distinct b.cid, c.dt, null
  from balance1 b
  cross join calendar c
  where not exists (
    select 1 from balance1
    where dt = c.dt and cid = b.cid
  )
) t
order by dt, cid
;

Not that pretty, and I'm looking for a more elegant solution. I used Db2 syntax above and in this Fiddle but it's the principle I'm after so any vendor syntax will do.

Why not this?
SELECT def.cid, c.dt, LAST_VALUE(b.amount, 'IGNORE NULLS') OVER(PARTITION BY def.cid ORDER BY c.dt)
  FROM calendar c
    INNER JOIN (SELECT DISTINCT cid FROM balance1) AS def(cid) ON 1=1
    LEFT JOIN balance1 b ON (c.dt, def.cid) = (b.dt, b.cid)
  ORDER BY c.dt, def.cid
;

Comparison in dbfiddle


Dealing with subtypes and supertypes in Database design I'm currently doing a database project for a class and I am having some difficulty dealing the subtypes and supertypes.
This is the list of entities and their attributes:

Piece (PIECE_ID, Description)
   In_house (PIECE_ID)
      Bulk (PIECE_ID, Location)
      To order (PIECE_ID, Lead time)
   Purchased (PIECE_ID, Cost)
Order (OID, Order date)
Supplier (SID, Name, Postcode, City )
Supplier piece (SPID, Price, description)

To clarify, Piece is the supertype with two subtypes, In_house and Purchased. In_house then has two subtypes, Bulk and To Order.

My first instinct was to keep a table Piece(PIECE_ID, description), another one for Purchased(PIECE_ID, cost), and then delete the In_house one and keep only the subtypes as Bulk(PIECE_ID, Location) and To_order(PIECE_ID, Lead Time).

First question: is this better as opposed to keeping the In_house table and put the Bulk and To_order info all in that table, generating some NULL values?

Also, how does the system know that there can't be any repeated PIECE_ID in the Purchased,  Bulk and To_order tables? Do I need to create a separate ID for these individual tables or can they just the foreign key for the Piece table?

Finally, the Purchased table has a 1:N obligatory on both relationship with Supplier part table. Therefore, I have to put the primary key of the Purchased table in the Supplier piece table. If the primary key on Purchased is just PIECE_ID, how can it associate that those PIECE_ID are from the Purchased table and not from the other tables that have PIECE_ID as a primary key?

Please let me know in comments if clarification is needed.

Not sure I understand all of your questions, but one fairly common model for super/sub-type is to add a classifier attribute to your supertype:

CREATE TABLE super
( xxx_id ... not null primary key
, classifier int not null
,   unique (classifier, xxx_id)
,   check (classifier in (1,2,...))
)

CREATE TABLE sub1
( xxx_id ... not null primary key
, classifier int default 1 not null
,    check (classifier = 1)
,     foreign key (classifier,xxx_id)
          references super (classifier,xxx_id)
)

CREATE TABLE sub2
( xxx_id ... not null primary key
, classifier int default 2 not null
,    check (classifier = 2)
,     foreign key (classifier,xxx_id)
          references super (classifier,xxx_id)
)

Now the classifier in super guarantees that for example 1 can't be located in sub2 (that it exist in any subtype is handled by a transaction).

However, this model is not fully practical if the classifier changes, so this is more of a food for thought answer (but it is too long to be a comment).


Calculate time difference in minutes I am Amazon Redshift. I tried to calculate the time difference between two time stamp columns in minutes, but the timestampdiff function does not work on it. The error is telling me that minute column does not exist in the table I query. At the same time some answers suggest that timestampdiff does not work for PostgreSQL, but function like date_part does not work on my end as well. Could anyone share experience or any document for the grammar and function on Amazon Redshift?
  select
      email,
      createddate,
      lastloggedin,
      timestampdiff(minute, createddate, lastloggedin)
       from udb.user
       where createddate >= '2019-09-01' and createddate <= '2019-09-30'

Error: [Amazon](500310) Invalid operation: column "minute" does not exist in user;
[SQL State=42703, DB Errorcode=500310]
1 statement failed.

Checking the docs for Amazon Redshift shows this:
DATEDIFF ( datepart, {date|time|timetz|timestamp}, {date|time|time|timestamp} )

datepart: The specific part of the date or time value (year, month, or day, hour, minute, second, millisecond, or microsecond) that the function operates on. For more information, see Date parts for date or timestamp functions.

Specifically, DATEDIFF determines the number of date part boundaries that are crossed between two expressions. For example, suppose that you're calculating the difference in years between two dates, 12-31-2008 and 01-01-2009. In this case, the function returns 1 year despite the fact that these dates are only one day apart. If you are finding the difference in hours between two timestamps, 01-01-2009 8:30:00 and 01-01-2009 10:00:00, the result is 2 hours. If you are finding the difference in hours between two timestamps, 8:30:00 and 10:00:00, the result is 2 hours.

date|time|timetz|timestamp:  A DATE, TIME, TIMETZ, or TIMESTAMP column or expressions that implicitly convert to a DATE, TIME, TIMETZ, or TIMESTAMP. The expressions must both contain the specified date or time part. If the second date or time is later than the first date or time, the result is positive. If the second date or time is earlier than the first date or time, the result is negative.

So, your query should be:
select
    email,
    createddate,
    lastloggedin,
    datediff(minute, createddate, lastloggedin)
from udb.user
where createddate >= '2019-09-01' and createddate <= '2019-09-30'


Should I split a single table with an AUTO_INCREMENT PRIMARY KEY into two tables that can be joined on that PRIMARY KEY? I have a table that has 46 fields with an AUTO_INCREMENT PRIMARY KEY.  It's hard to look at a table that has this many columns, so I frequently use a SELECT statement to look at 34 of the fields that I mentally categorize as 'group 1' and 12 fields that I categorize as 'group 2'.

Would I face a performance hit on reads/writes if I split this table into two tables, one with 34 fields and another with 12 fields.

Both would have the same AUTO_INCREMENT PRIMARY KEY so that they could be joined on the PRIMARY key if I need to look at at 46 fields at once.

It's hard to look at a table that has this many columns, so I
  frequently use a SELECT statement to look at 34 of the fields that I
  mentally categorize as 'group 1' and 12 fields that I categorize as
  'group 2'.

Create a view.


What does a TRUNCATE TABLE write to the Log file in SQL Server? I know it does not log everything like a DELETE would but I am not sure what it would write to the log file.

There is a huge difference between the records logged in a transaction log file for a DELETE and for TRUNCATE TABLE statement

A DELETE statement records what exactly has been deleted, i.e. the value of the deleted row e.g. 'JohnSmith', so you can read the transaction log content (you can use fn_dblog), see what was deleted and re-insert the deleted record if you want.

With the TRUNCATE TABLE statement, you cannot do this, as the statement is 'minimally logged'. The exact deleted values are not logged in the transaction log, only the IDs of the pages that held the truncated records are logged. These pages are marked for overwriting in the database data file and the truncated data will be gone for good when the new transactions are written to these pages.

Therefore, reading only the transaction log cannot provide the values that are lost due to the TRUNCATE TABLE statement. The only chance you have to recover the truncated records is to read the page ID in the transaction log and find it in the MDF file, in case it hasn't been overwritten


How to insert a record returned by a function into a table I have a plpgsql function that takes one input parameter and returns multiple columns of data as a record.

CREATE FUNCTION my_func(
    IN id bigint,
    OUT foo bigint,
    OUT bar double precision
    )
  RETURNS RECORD AS
$BODY$
BEGIN
    -- some calculations
    foo = id + 1;
    bar = 42.0;
END;
$BODY$
  LANGUAGE plpgsql;

I want to execute my_func for each row of a table (table_input) with the id column as input parameter and insert the returned row into another table (table_output).
The table table_output has the same columns as the columns return by my_func.

How can I do that?

The query

SELECT my_func(id) INTO table_output FROM table_input;

gives me:

ERROR:  column "my_func" has pseudo-type record

The following query does what I want, but my_func() gets called twice per row:

INSERT INTO table_output
SELECT
 (my_func(table_input.id)).foo,
 (my_func(table_input.id)).bar
FROM table_input;

Any suggestions?

Assuming current Postgres version 9.5, since it has not been defined.
Best solved with a LATERAL join (Postgres 9.3+).

The minimal working form:

INSERT INTO table_output
SELECT f.*
FROM   table_input t, my_func(t.id) f;

The same, more explicit:

INSERT INTO table_output (foo, bar)
SELECT f.foo, f.bar
FROM   table_input t
CROSS  JOIN LATERAL my_func(t.id) AS f;

Or, since exactly one result row is guaranteed in this case, a plain function call in a subquery does the same, probably cheaper:

INSERT INTO table_output
SELECT f.*
FROM  (SELECT my_func(id) AS f FROM table_input) t;

It's wise to declare target columns in a persisted INSERT, even if the row types of function and table currently match. This is more robust against later changes.

CROSS JOIN LATERAL is just the verbose equivalent of a simple comma before a set-returning function in the FROM list. (Binding more closely, but that's not relevant in this query.)

Detailed explanation for LATERAL and how to avoid repeated function evaluation in this recent, related answer on SO - with links to more:

Split function-returned record into multiple columns


select rows where timestamp is a multiple of n seconds I am logging live data to a database where it is then selected by a php script and posted to a graph on a webpage.

A new datapoint is added every second and I have been graphing the most recent 300 points for a 5 minute history on the graph using the following:

SELECT * FROM table_name ORDER BY timestamp DESC LIMIT 300;

What I would like to do is add functionality to zoom out the data, losing resolution but showing a longer history but selecting every 5th row for example. However if I just select the most recent 300 5th entries the graph 'snakes' across the page as the values I am asking for are different each time as shown:

query 1 entries: [8:00] [7:55] [7:50] [7:45] [7:40] ...
query 2 entries: [8:01] [7:56] [7:51] [7:46] [7:41] ...

where what I want to do is

query 1 entries: [8:00] [7:55] [7:50] [7:45] [7:40] ...
query 1 entries: [8:05] [8:00] [7:55] [7:50] [7:45] ...

I have been looking for a way to do something like

SELECT * FROM table_name WHERE timestamp IS MULTIPLE OF 5 SECONDS

but have had no luck so here I am.

something like

select * from table where MOD(UNIX_TIMESTAMP(timestamp),300)) = 0;

should do the job. But I am not sure if this is correct. The idea is to try to modulo divide the minute part evenly. Be aware, that this may cause in a full table scan


Where does SSMS Last database backup date come from? The backup date for several of my databases do not appear in msdb.dbo.back upset yet the correct date appears in SSMS when I select a db name, right click, properties, there in the general tab is the correct date. Where does SQL Server get this date?

Where does SQL Server get this date?

SSMS behind the scenes uses msdb.dbo.backupset to populate the data in the Properties page Last Database Backup. Note that the SSMS properties page does not give info about filegroup backup. It only gives info about full, diff or log backup.

You can use this script to retrieve SQL Server database backup history and no backups details