SSMS constraint script: Purpose of second alter table statement? SSMS scripts Foreign Key constraints as two statements:

ALTER TABLE {table}
WITH CHECK
ADD CONSTRAINT {constraintname} {constraint spec}
GO

ALTER TABLE {table}
CHECK CONSTRAINT {constraintname}
GO

What is the purpose of the second statement?

The second line is redundant but I agree the overuse of CHECK in that syntax is confusing.

According to the ALTER TABLE documentation the existing data is checked anyway if the WITH CHECK ADD option is used and the newly added constraint is enabled.

  WITH CHECK | WITH NOCHECK
  Specifies whether the data in the table is
  or is not validated against a newly added or re-enabled FOREIGN KEY or
  CHECK constraint. If not specified, WITH CHECK is assumed for new
  constraints, and WITH NOCHECK is assumed for re-enabled constraints.

The second line enables the constraint, again from the documentation, but that constraint was already enabled.

  { CHECK | NOCHECK } CONSTRAINT   Specifies that constraint_name is
  enabled or disabled. This option can only be used with FOREIGN KEY and
  CHECK constraints. When NOCHECK is specified, the constraint is
  disabled and future inserts or updates to the column are not validated
  against the constraint conditions. DEFAULT, PRIMARY KEY, and UNIQUE
  constraints cannot be disabled.

However, the constraint is already enabled as can be evidenced from this code:

CREATE TABLE [dbo].[constraintdemo](
    [constraintfield] [int] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[constraintdemo]  WITH CHECK ADD  CONSTRAINT [CK_constraintdemo] CHECK  (([constraintfield]>(0)))
GO

INSERT INTO constraintdemo (constraintfield) VALUES (-1)

Which results in:

  Msg 547, Level 16, State 0, Line 12   The INSERT statement conflicted
  with the CHECK constraint "CK_constraintdemo". The conflict occurred
  in database "PlayGround", table "dbo.constraintdemo", column
  'constraintfield'.   The statement has been terminated.

See this dbfiddle where you can run the above code

If you want evidence that the existing data is effectively validated by the WITH CHECK you can run this code:

CREATE TABLE [dbo].[constraintdemo](
    [constraintfield] [int] NULL
) ON [PRIMARY]

GO

INSERT INTO constraintdemo (constraintfield) values (-1),(-2)
GO

ALTER TABLE [dbo].[constraintdemo]  WITH CHECK ADD  CONSTRAINT [CK_constraintdemo] CHECK  (([constraintfield]>(0)))
GO

Which returns:

  (2 row(s) affected)   Msg 547, Level 16, State 0, Line 21   The ALTER
  TABLE statement conflicted with the CHECK constraint
  "CK_constraintdemo". The conflict occurred in database "PlayGround",
  table "dbo.constraintdemo", column 'constraintfield'.

Again: dbfiddle

In this example it doesn't matter if it's a check constraint or a foreign key constraint as evidenced by this somewhat more complicated example (note that I commented out the second line):

CREATE TABLE [dbo].[keytable](
    [key] [nchar](10) NOT NULL,
 CONSTRAINT [PK_keytable] PRIMARY KEY CLUSTERED
(
    [key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[valuetable](
    [key] [nchar](10) NULL,
    [value] [nchar](10) NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[valuetable]  WITH CHECK ADD  CONSTRAINT [FK_valuetable_keytable] FOREIGN KEY([key])
REFERENCES [dbo].[keytable] ([key])
GO

--ALTER TABLE [dbo].[valuetable] CHECK CONSTRAINT [FK_valuetable_keytable]
GO

INSERT INTO keytable ([key]) VALUES (1),(2);
GO

INSERT INTO valuetable ([key],[value]) VALUES (3,'test');
GO

This still results in an error showing the foreign key is enabled:

  Msg 547 Level 16 State 0 Line 1   The INSERT statement conflicted with
  the FOREIGN KEY constraint "FK_valuetable_keytable". The conflict
  occurred in database "fiddle_7378e515ee284b358b4e2edbc07d1329", table
  "dbo.keytable", column 'key'.   Msg 3621 Level 0 State 0 Line 1
  The statement has been terminated.

Dbfiddle here

And again we can turn things around to prove that it checks existing data on creation using this code:

CREATE TABLE [dbo].[keytable](
    [key] [nchar](10) NOT NULL,
 CONSTRAINT [PK_keytable] PRIMARY KEY CLUSTERED
(
    [key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[valuetable](
    [key] [nchar](10) NULL,
    [value] [nchar](10) NULL
) ON [PRIMARY]

GO
INSERT INTO keytable ([key]) VALUES (1),(2);
GO

INSERT INTO valuetable ([key],[value]) VALUES (3,'test');
GO

ALTER TABLE [dbo].[valuetable]  WITH CHECK ADD  CONSTRAINT [FK_valuetable_keytable] FOREIGN KEY([key])
REFERENCES [dbo].[keytable] ([key])
GO

Which returns:

  Msg 547 Level 16 State 0 Line 1   The ALTER TABLE statement conflicted
  with the FOREIGN KEY constraint "FK_valuetable_keytable". The conflict
  occurred in database "fiddle_59761b4587c14f2b96b8029a10de6229", table
  "dbo.keytable", column 'key'.

dbfiddle here


SqlServer Passing parameters within Nesting procedures I have these 2 procedures:

Create procedure jolly (@job_par varchar(100)='p%') as
select * from emp where job like @job_par

create procedure test1 @proc_name varchar(30)
as exec @proc_name

if i execute like this

exec test1 @proc_name= jolly

i get all the employees with job that begins with 'p', and it's ok.

But how can i call the procedure test1 to make me return all the employees with job='ACCOUNT'??

i tried like this but it gives me errors:

exec test1 @proc_name= jolly @job_par='ACCOUNT'

How can i pass the parameter for the second procedure?

Thank you.

You need to add the @job_par parameter to your calling procedure so that you can then pass that down the line. Using your example:

CREATE PROCEDURE jolly
    (
      @job_par VARCHAR(100) = 'p%'
    )
AS
    SELECT  *
    FROM    emp
    WHERE   job LIKE @job_par;
GO

CREATE PROCEDURE test1
    @proc_name VARCHAR(30) ,
    @job_par VARCHAR(100)
AS
    EXEC @proc_name @job_par;
GO

EXEC test1 @proc_name = 'jolly', @job_par = 'ACCOUNT';


What is the last Mysql Version supported in REDHAT 6? Maybe not such a good question ! But my Linux admin is to busy to answer my doubts. SO:

I have all my Mysql servers running on 5.1.69 version(the free one) that comes with RedHat O.S. My users are forced to upgrade their Mysql Versions because of the Drupal and moodle needs !!
  So i need to know if RedHat goes beyond  5.1.69 version or not. If not i will install a new version with no support or buy Oralcle support (which is not an option i think for my users as they are so cheap kkkkkkkkk)
THX

Here's a list of supported platforms: http://www.mysql.com/support/supportedplatforms/database.html.

The MySQL yum repo is detailed here: http://dev.mysql.com/downloads/repo/ - That has binaries for MySQL 5.5, 5.6 and 5.7.


Mirroring partner timeout - Not working the way advertised? I'm testing a 2 node windows failover cluster with mirroring in high safety mode and automatic failover. My mirror is a standalone server as is the witness. All instances are 2008 R2 RTM Enterprise Edition (64-bit) (this is a test, the witness will be Express in prod)

Because I'd like to prevent failover to the mirror when I failover one cluster node to the other (or in the case we just lose a node), I've upped the partner timeout value. My cluster fails over fairly quickly, somewhere between 25 and 30 seconds. But, even when I set the value of the timeout to 59 seconds, the mirrored DB still fails over to the mirror server, and rather quickly.

Generally, I'm testing by simply moving the SQL resource from one node to the other in the cluster manager but I did try it by rebooting the active node as well. The same is true when I failback. I can shutdown the SQL Service on my mirror server (which is now the principle) and by querying sys.database_mirroring, I see the mirror (former principal) change state and description in very short order.

So, what am I doing wrong or assuming about how the timeout works?

I'd suggest that the clean shutdown of the principal is causing it.
From Automatic Failover

How Automatic Failover Works

If the principal server is still running, it changes the state of the principal database to DISCONNECTED and disconnects all clients from the principal database.

One may assume that the principal says "I'm DISCONNECTED" on clean shutdown
To test this correctly, remove the network cables (or power leads) from the node hosting the principal to simulate/invoke a "real" server failure
I assume that the mirror is actually remote and not on the same cluster...


What is the optimal solution for storing real time time-series in MariaDB / MySQL? Use case: A measurement creates a given number of images. For each image we need to store a small set of quality indicators (floats, doubles) along with an image integer [1 ...N], a timestamp and one or two foreign key values. This should then be plotted in "real time" in a web application (PHP) for users to evaluate.

Each web client polls the database every 5s. Storage + retrieval of each set of quality indicators should ideally take < 2s (approximately). In a worst case scenario there can be ~30 simultaneous web clients polling and around 10 measurements could be writing simultaneously, leading to write bursts of approx. 1000 sets of quality indicators per second.

In a programming language, this sort of data would probably be stored in arrays or lists. As I'm not aware of anything similar in the MariaDB / MySQL world I'm just using a regular InnoDB table with a column for each of the values mentioned above. This already has 90+ million rows and is expected to grow faster in the coming months.

Is InnoDB overall the best storage engine for this, or should I consider others? Is it best practice to archive data after a while, perhaps once all the measurements' images have been processed? Would it help to enable compression, or would that have very negative impacts on performance?

With just MySQL/MariaDB, I would employ:

High speed ingestion:  http://mysql.rjweb.org/doc.php/staging_table
Summary tables (to make fetching the data much faster):  http://mysql.rjweb.org/doc.php/summarytables
I would even consider not storing the raw data; instead summarize the data, then toss it.  If this is practical, it avoids most of the questions you pose.
(If data needs purging):  Rapid deletion of old data:  http://mysql.rjweb.org/doc.php/partitionmaint
I would avoid FOREIGN KEYS because of the extra overhead.  (Instead, I would debug the SQL.)
I would not use UUID keys; performance degenerates terribly in huge tables.  (http://mysql.rjweb.org/doc.php/uuid)
I would avoid extra indexes -- Don't use AUTO_INCREMENT if some other column(s) is unique.
You mention Spatial -- Please elaborate.  2D lookups are tricky; SPATIAL is one approach; here is another: http://mysql.rjweb.org/doc.php/latlng

Your last paragraph throws in the kitchen sink of questions (Toku, MyRocks, archive, compression, history table).  I'm surprised the posting has not be killed for being "too broad".  Please elaborate on what your data and queries are like; otherwise, all we can do is throw a kitchen sink full of solutions.

You say "realtime", yet you need "thousands/sec".  Can you allow 1 minute delay in realtime?  1 sec?  You cannot get 1ms; 1s will be hard to achieve.  How long does a burst last?  What is a per-minute burst?  1K/sec will probably spill into the next couple of seconds.  6K/minute is not much trouble.

How many clients are storing data?  Some solutions work well with a single client; different solutions are needed for multiple clients.

Keep in mind that benchmarks are tuned to show off one thing, and rarely match real life.


How to get list of enabled/disabled features of SQLAnywhere I'm looking to get a list of features, which are installed or enabled/disabled on a given SQLAnywhere instance. dblic is only showing the 'possible' feature based on lice, but not the actually available features of database server ... or is it?

You don't say which version of SQL Anywhere you are using but for v12.0.1 you can execute
SELECT PROPERTY ( 'ServerEdition' ); The result is explained in the docs thus:

  ServerEdition

  Returns a space-separated list of words describing the database server type.
  Values include:

Evaluation
Developer
Web
Educational
Standard
Advanced
Workgroup
OEM
Authenticated

  If you have a separate license for any of the following features, then the appropriate string(s) are added to the license string that is returned:

HighAvailability   See SQL Anywhere high availability option.
InMemory           See SQL Anywhere in-memory mode option.
ECC                See SQL Anywhere security option.
FIPS               See SQL Anywhere security option.

You can get more help at the dedicated SQL Anywhere forum http://sqlanywhere-forum.sap.com/


Error while restoring Postgres DB Dump Using below command to restore the database

Restoration command:

cat my_db_dump.sql.gz | gunzip | psql -d new_database -U my_username

Note: my_db_dump.sql.gz size is 4.5 GB, (extracted size will be around 70 GB)

Getting the below exception (after executing above restoration command)

  gunzip: stdin: unexpected end of file
  ERROR:  unterminated quoted string at or near "'\x3c3f78
  (hexadecimal junks removed)
  LINE 1: SELECT pg_catalog.lowrite(0, '\x3c3f786d6c2076657273696f6e3d...

Based on the first error message, the .gz backup file seems to be truncated.

This could be confirmed with the command:

gunzip -c my_db_dump.sql.gz >/dev/null

Presumably this will lead to the same error message: unexpected end of file which is the root cause, the other error from postgres being the consequence of the abrupt end of the data stream.

If the .gz file is a copy resulting from a transfer, you want to compare its size against the size of the original, and transfer it again if it's incomplete. If that's the only copy or if the original itself is incomplete, completing the restore will not be possible.


How to restore mysqldump of one database in different databaseName with content of data in the same host machine Let's start from the beginning there is a MySQL database with name DB1. As I want to restore the mysqldump backup of DB1 database in DB2. The DB2 database is another database in same Host Server machine.

As I have been restored the DB1 mysqldump backup file to DB2 database through (MySQL Workbench as well as SQLyog Ultimate Tool). The restoration went through successfully restored. But there is no Any tables,Views,Stored Procs,Functions,Triggers and event tables or data present/appears in DB2 database. I have also gone through Restore mysql database with different name but somewhere bed luck.

My Environment is as follows:

Operating system version  :   Microsoft Windows Server 2012 R2 Stanadard
Handware information      :   VMware, Inc. VMware Virtual Platform
Processors                :   Intel(R) Xeon(R) CPU E5-2670 0 @ 2.60 GHz
Intalled memory (RAM)     :   24 GB
Installed MySQL Server version : Server version 5.7.24 community edition
The DEFAULT CHARACTER SET of database : utf8
The Backup of DB1 database is 26 GB.

  Note: As I want to all content of database of DB1 in DB2 database.

My question is that how to get DB1 database all (tables,Views,Stored Procs,Functions,Triggers and event tables) data in DB2 in the same host machine.

How to restore mysqldump of one database in different databaseName with content of data in the same host machine

I would like to say that as I am able to restore the backup of DB1 in DB2 with the same host. When I took the mysqldump backup of DB1 from the SQLyog ultimate tool. During the mysqldump backup window just exclude these statements. Which is as follows:

Exclude USE database statement
Exclude CREATE database statement

After excluding these statements in dump file , I am able to restore the dump backup file at DB2 (Different database name with mysqldump database name) in same host name.

Hope it will helpful to other person.


Why merge replication wants to replicate view before replicating related table? We have a publication and a subscriber running on 2 SQL 2016 servers. Now the error that we are receiving during subscription initialization is:

Error messages:

  The schema script 'vw_BlackList_961.sch' could not be propagated to
  the subscriber. (Source: MSSQL_REPL, Error number:
  MSSQL_REPL-2147201001) Get help: http://help/MSSQL_REPL-2147201001
  Unable to replicate a view or function because the referenced objects
  or columns are not present on the Subscriber. (Source: MSSQL_REPL,
  Error number: MSSQL_REPL20164) Get help: http://help/MSSQL_REPL20164
  Invalid object name 'StaffBlacklistedSites'. (Source: MSSQLServer,
  Error number: 208) Get help: http://help/208

Where is quite obvious that SQL server wants first to push VIEW instead first to push TABLE. How that is even possible? How to remedy this issue?

Thanks,
Dejan

I don't know if this apply to your issue, but according MS-DOCS about this error:

Error number: MSSQL_REPL-2147201001

Maybe it is not about trying to replicate a VIEW before the TABLE, but another reasons.

Quoting from docs: (Bold is mine)

  Explanation

  COM object initialization failed for an XML Subscriber. Some reasons
  why merge replication did not apply schema changes to the Subscriber
  include the following:

  A failure to create a directory to write the temporary snapshot files.
  A failure to enumerate schema articles.
  For SQL Server Compact Subscribers, a failure to reinitialize the
  subscription.
  If the object is message based, a failure to write to the message
  file.

Perhaps for some reason it can't enumerate the schema properly, and then it is trying to replicate the view before the table.

You can have a look at errors log in this way:

  User Action

  Run SQL Server Profiler and examine replmerg.log for
  failures. If you are using Web Synchronization, elevate the severity
  of the websync log, rerun the scenario, and check for errors in the
  websync.log file.


Optimize Query of Count Less Than I would like to optimize querying in SQL Server of a table to know whether it has a count of records less than a certain amount. I don't need to know the complete count, just whether it is less than, say, 2.

Would it be efficient to do something like this?...

if (
    select count(*)
      from (select top (2) *
              from sys.databases) t
   ) > 1
begin
  select 1
end
else
begin
  select 0
end

Yes.

The query in the question would in principal be able to do this more efficiently than the obvious alternative of just doing select count(*) from sys.databases.

For the same reason as EXISTS is generally preferable to COUNT. It can short circuit.

The TOP underneath the aggregate operator can stop requesting rows as soon as the second one is received rather than requesting more and causing additional irrelevant rows to be read.

It isn't absolutely guaranteed to be more efficient however. There are occasionally edge cases where adding a TOP causes a worse overall plan than that for the query which simply brings back the whole result set.


T-SQL brain teaser: find non-overlapping sets I would like to ask for help coming up with a query, which can identify groups on non-overlapping records.  Here is a sample scenario (admittedly contrived).  Let's say I have employees who are assigned to work on various projects.  While an employee can be assigned to multiple projects, he/she can only work on one project at any given time (don't you wish we all had this luxury :).  I need to find out which projects can be scheduled to be worked on in parallel because they do not share any employees.  Here is some code to setup sample tables and data.

--1. Create #Projects table
IF OBJECT_ID('tempdb..#Projects') IS NOT NULL
    DROP TABLE #Projects

CREATE TABLE #Projects (
    ProjectId INT,
    ProjectName VARCHAR(16)
    )

INSERT INTO #Projects ( ProjectId, ProjectName )
VALUES  ( 1, 'Project 1'), ( 2, 'Project 2'), ( 3, 'Project 3'), ( 4, 'Project 4'), ( 5, 'Project 5'),
        ( 6, 'Project 6'), ( 7, 'Project 7')

--2. Create #Employees table
IF OBJECT_ID('tempdb..#Employees') IS NOT NULL
    DROP TABLE #Employees

CREATE TABLE #Employees (
    EmployeeId INT,
    EmployeeName VARCHAR(16)
    )

INSERT INTO #Employees (EmployeeId, EmployeeName)
VALUES (101, 'Employee 101'), (102, 'Employee 102'), (103, 'Employee 103'), (104, 'Employee 104'),
        (105, 'Employee 105'), (106, 'Employee 106'), (107, 'Employee 107')

--3. Create #Employee_Projects table
IF OBJECT_ID('tempdb..#Employee_Projects') IS NOT NULL
    DROP TABLE #Employee_Projects

CREATE TABLE #Employee_Projects (
    ProjectId INT,
    EmployeeId INT
    )

INSERT INTO #Employee_Projects (ProjectId, EmployeeId)
VALUES (1, 101), (1, 105), (1, 107), (2, 102), (2, 103), (2, 107), (3, 104), (3, 105),  (3, 106), (4, 100), (4, 101), (4, 102), (5, 103), (5, 104), (6, 105), (6, 106), (7, 106), (7, 107), (8, 102), (8, 104), (8, 106)

And here is the query, which will show you the employees and projects we have created:

SELECT p.ProjectId, p.ProjectName, e.EmployeeId, e.EmployeeName
FROM #Projects p
JOIN #Employee_Projects ep ON ep.ProjectId = p.ProjectId
JOIN #Employees e ON e.EmployeeId = ep.EmployeeId
ORDER BY ep.ProjectId, e.EmployeeId

Our data looks like this:

ProjectId   ProjectName      EmployeeId  EmployeeName
----------- ---------------- ----------- ----------------
1           Project 1        101         Employee 101
1           Project 1        105         Employee 105
1           Project 1        107         Employee 107
2           Project 2        102         Employee 102
2           Project 2        103         Employee 103
2           Project 2        107         Employee 107
3           Project 3        104         Employee 104
3           Project 3        105         Employee 105
3           Project 3        106         Employee 106
4           Project 4        101         Employee 101
4           Project 4        102         Employee 102
5           Project 5        103         Employee 103
5           Project 5        104         Employee 104
6           Project 6        105         Employee 105
6           Project 6        106         Employee 106
7           Project 7        106         Employee 106
7           Project 7        107         Employee 107
8           Project 8        102         Employee 102
8           Project 8        104         Employee 104
8           Project 8        106         Employee 106

Visually we can tell that, for example, we can schedule projects 1, 2, 3 to run at the same time because they do not share any employees.  Let's call this group of projects "Group 1".  After that we could schedule projects 4, 5, 6.  Let's call it "Group 2".  Finally, in our example we have project 7 left and this would be our "Group 3". My question is, how can I write a T-SQL query to perform such grouping of projects?

Thank you!

This is a sort of bin-packing problem, so you'll most likely need to choose from one of the available approximate solutions, rather than attempting an exhaustive search.

One very straightforward idea is to pack projects into groups choosing the project with the largest number of employees each time. As soon as the current project no longer fits into the current group, we start a new group.

This is relatively easy to express iteratively, though it may not perform well, depending on the characteristics of the real data set. It might be quite challenging to express the same logic in a single T-SQL statement. Anyway, as an example, here is an iterative T-SQL solution using the simple greedy algorithm outlined above:

SET NOCOUNT ON;
CREATE TABLE #Groups
(
    GroupID integer NOT NULL,
    ProjectID integer NOT NULL,

    CONSTRAINT PK_Groups
    PRIMARY KEY (GroupID, ProjectID)
);

-- First group
DECLARE @GroupID integer = 1;

-- Choose the largest project to start with
-- and assign to group 1
INSERT #Groups
SELECT TOP (1)
    @GroupID AS GroupID,
    P.ProjectId
FROM #Projects AS P
GROUP BY
    P.ProjectId
ORDER BY
    COUNT_BIG(*) DESC,
    NEWID(); -- Random order tie-breaker

WHILE 1 = 1
BEGIN
    -- Add the next largest project to the current group
    INSERT #Groups
    SELECT TOP (1)
        @GroupID AS GroupID,
        P.ProjectId
    FROM #Projects AS P
    WHERE NOT EXISTS
    (
        -- Project not already allocated to a group
        SELECT *
        FROM #Groups AS G
        WHERE G.ProjectID = P.ProjectId
    )
    AND NOT EXISTS
    (
        -- Employees for this project
        SELECT EP2.EmployeeId
        FROM #Employee_Projects AS EP2
        WHERE EP2.ProjectId = P.ProjectId

        INTERSECT

        -- Employees already in the current group of projects
        SELECT EP.EmployeeId
        FROM #Groups AS G2
        JOIN #Employee_Projects AS EP
            ON EP.ProjectId = G2.ProjectID
        WHERE G2.GroupID = @GroupID
    )
    GROUP BY P.ProjectId
    ORDER BY
        COUNT_BIG(*) DESC,
        NEWID(); -- Random order tie-breaker

    -- No project found for this group
    IF @@ROWCOUNT = 0
    BEGIN
        -- Finished when no more projects left to process
        IF NOT EXISTS
        (
            SELECT P.ProjectId FROM #Projects AS P
            EXCEPT
            SELECT G.ProjectID FROM #Groups AS G
        )
        BEGIN
            BREAK;
        END;

        -- Next group number
        SET @GroupID += 1;
    END;
END;

SELECT
    G.GroupID,
    G.ProjectID
FROM #Groups AS G;

This code is non-deterministic because any ties encountered (when sorting by the number of employees per project descending) are broken randomly using NEWID. Nevertheless, a typical output is:

╔═════════╦═══════════╗
║ GroupID ║ ProjectID ║
╠═════════╬═══════════╣
║       1 ║         3 ║
║       1 ║         4 ║
║       2 ║         2 ║
║       2 ║         6 ║
║       3 ║         5 ║
║       3 ║         7 ║
║       4 ║         1 ║
╚═════════╩═══════════╝


Postgres Data Dump problem I am trying to dump this data into Postgres:

COPY address_types (game_id, device_type) FROM stdin;
1   Ps3
2   Xbox
3   Other
\.

But I always get this error:

[Err] ERROR:  syntax error at or near "1"
LINE 2: 1 Ps3
        ^

Any ideas what might be wrong?

According to the PostgreSQL documentation on the COPY command the values must be  separated by the delimiter character which is by default a tab (\t). If spaces are used instead I get a similar error on PostgreSQL 9.5. BTW: Which version do you use?

Alternatively you might try to alter the delimiter character:

nd1=> COPY address_types (game_id, device_type) FROM stdin with delimiter '#';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1#Ps3
>> 2#Xbox
>> 3#Other
>> \.
COPY 3


How do I check three conditions on a select with one query? I have a table containing email text for welcoming new users. There is a default email, one that may be client_id specific and one that may be user_id specific. I can do three queries to find user, then if not found. client, then if not found, default. But I'd prefer to have one query that selects the one that follows that priority list of user, client and default.

The other option is to do one query that may return up to three rows and then just select the appropriate row. But that is messy...

SELECT * FROM emails
WHERE primary_key=?
  AND ((user_id=?)
       OR (user_id IS NULL AND client_id=?)
       OR (user_id IS NULL AND client_id IS NULL))

This may return up to three rows. Is there a way to just return one row?

You can use Conditional ORDER BY and then use LIMIT 1 to restrict result-set to one row. This approach depends on a MySQL feature where it implicitly typecasts the result of a conditional expression, i.e., boolean to int (0/1)

SELECT * FROM emails
WHERE primary_key=?
ORDER BY
  user_id = ? DESC,  -- if user_id matches, it will be 1 and come first
  client_id = ? DESC
  -- does not matter if user_id matches,
  -- but if it does not, then client_id matches will come first
LIMIT 1


Design star schema for many-many relationship What are the steps/rules to build a DW star schema design from a production database; specifically, how do you handle many-to-many relationships.

I understand how to take basic data including a many-to-many relationship, and get to a normalized production database:

For example:

If I want to handle sales transactions, given tables Product Entity, Promotion, and Employee, the initial step is to construct a table, SaleTransaction:

SaleTransaction
- TransactionID
- ProductID
- EmployeeID
- SellingDateID
- Quantity
- SaleAmount
- PromotionID

The Promotion entity will be:

Promotion
- PromotionID
- ProductID
- DiscountAmount

However, that would only allow 1 product and 1 promotion per sale transaction. Since we want to allow one or more products, and zero or more promotions:

I remove PromotionID, ProductID, Quantity, and (unless required by some business rule) SaleAmount from SaleTransaction
I create SaleTransactionDetail:

- DetailID
- TransactionID
- ProductID
- Quantity
- SaleAmount

I will create SaleTransactionPromotion:

- TransPromoID
- PromotionID

What would be the equivalent steps to take a production DB design to a DW star schema design?

Many-to-many tables are typically handled with a "bridge table."  This is really just another name for a many-to-many table like you'd use in a transactional database: one column for an FK to the first table, one column for an FK to the second table, and probably a column or two for housekeeping fields.

You may also want a Weight field in the bridge table, with a value equal to one divided by the number of entries for each sale.  This lets calculate SUM(Weight) instead of COUNT(*), when you want to avoid double-counting orders which had multiple promotions.

Your proposed schema is good, as is, but I think it could be improved slightly; two things come to mind.  One is that I would generally collapse SalesTransactions and SalesTransactionDetails into a single table.  This introduces redundancy for things like customer IDs, which ought to make your OLTP instincts flinch (transitioning from normalized databases to data warehousing is a paradigm shift!), but in this case it's worth it.  By putting all of your sales into a single table, you simplify joins and, in many databases, signal that you want to use bitmap indices.  In a warehouse, you often end up having to do table scans, but that's OK because your fact tables are very narrow, and fit in memory.

So we have three tables Sales, Promotions, and SalesPromotions.  You might consider "order" rather than "sale," because the latter implies a finality which may not apply.  If your system includes orders which have been placed but not shipped or not closed, the more general term makes sense.  That's quibbling, but it never hurts to think carefully about what you name things.

CREATE TABLE Orders
    -- IDs/dimensions:
    OrderID
    EmployeeID
    ProductID
    -- Date dimensions: date placed, date contract signed, date shipped, etc.
    -- Metrics: quantity, gross price, net price, extended price
    -- Attributes: is taxable, currency code, business key

CREATE TABLE Promotions
    PromotionID
    -- Date dimensions: date effective, date expires
    -- Metrics: fixed amount, percentage amount
    -- Attributes: name, type

CREATE TABLE OrderPromotions
    OrderID
    PromotionID
    Weight

ExtendedPrice can be a computed column rather than storing a value.  If attributes start to bloat your Orders table, consider a "junk dimension."

You could include ProductID in Promotions, but only if promotions will only ever apply to a single product.  For example, do you have some promotions which are applied to a whole order rather than specific line items?  Might a promotion apply to product code ABC1 today, but ABC2 tomorrow, after a price change?

The second caveat I had in mind is IDs.  I strongly recommend that you use synthetic keys (SKs) in a data warehouse, rather than relying on IDs from the source system.  There are a number of reasons, and this is discussed elsewhere on DBA.StackExchange, but offhand:

A 32- or 64-bit integer for an SK will often be narrower than source system order IDs or customer IDs (e.g., a 144-bit Salesforce ID or other long alphanumeric IDs).  This keeps your tables narrow, your secondary indices narrow, and your scans fast.
You can avoid collisions in the source system.  If Marketing some day decides to use product key ABC123 for something unrelated to what it was last year, you can give them two different synthetic keys.
In particular, if your company acquires another business, you may find that their IDs don't match the data types you've been using.  You'll need to map them to a common set, and handle collisions.  Best to have the infrastructure for that from day one.
You can use magic values for specific meanings which are relevant for reporting, but which aren't handled in the transactional system.  For example, I always use SKs -1 for "N/A" and 0 for "Unknown".  E.g., the Customers.FirstOrderSK field will be populated with -1 for customers who've had no orders, and 0 for customers who've had an order but for which no more specific info is available yet.  Your reports can reflect this without having to put in special logic to handle NULL values.

You can take advantage of synthetic keys in your bridge table by including a "dummy record" for orders without promotions.  Populate it with a PromotionID of -1, to indicate that no promotion applied; now consumers of your data can filter for orders sold at full price.


Group by checks constraints are possible? I have a table with the following columns:

TEAM_ID integer,
NAME varchar(64),
IS_LEADER char(1) not null default 'Y'

I want to apply the constraint that, in the same team, only a leader can be possible.
This constraint applies only on a subset of rows, not to the whole table as, of course, multiple teams can have their leader.

How to write this kind of constraint in oracle?

You can use a unique index for that. Example:

create unique index one_leader_per_team
  on members
    (case when IS_LEADER = 'Y' then TEAM_ID else null end) ;

What it does is store team_id - in the index - only for those rows that have IS_LEADER = 'Y' and nulls in the rest of the rows  The UNIQUE ensures that have only one row per team with 'Y' and the nulls are ignored.

(I think that up to 11 and older version, Oracle doesn't store the nulls at all in the index, so the index is more space efficient - not sure if this has changed in version 12). That means that the Oracle index is equivalent to a filtered/partial index in other DBMS (SQL Server, PostgreSQL), i.e.:

-- this would work in SQL Server, Postgres
create unique index one_leader_per_team
  on members (TEAM_ID)
  where (IS_LEADER = 'Y') ;

Test in dbfiddle.uk:

create table members
  ( MEMBER_ID integer not null primary key,
    TEAM_ID integer not null,
    NAME varchar(64) not null,
    IS_LEADER char(1)  default 'Y' not null check (IS_LEADER in ('Y','N'))
  ) ;

create unique index one_leader_per_team
  on members
    (case when IS_LEADER = 'Y' then TEAM_ID end) ;

insert all
  into members values (1, 1, 'Alex', 'Y')
  into members values (2, 1, 'Bill', 'N')
  into members values (3, 1, 'Cath', 'N')
  into members values (4, 2, 'Diana', 'N')
  into members values (5, 2, 'Elsa', 'Y')
select * from dual ;

5 rows affected

insert all
  into members values (6, 3, 'Fay', 'Y')
  into members values (7, 3, 'George', 'Y')
select * from dual ;

ORA-00001: unique constraint (FIDDLE_TITLKRLCAFRLTVCBXZVI.ONE_LEADER_PER_TEAM) violated

insert into members values (8, 1, 'Harry', 'Y');

ORA-00001: unique constraint (FIDDLE_TITLKRLCAFRLTVCBXZVI.ONE_LEADER_PER_TEAM) violated

select * from members ;

MEMBER_ID | TEAM_ID | NAME  | IS_LEADER
--------: | ------: | :---- | :--------
        1 |       1 | Alex  | Y
        2 |       1 | Bill  | N
        3 |       1 | Cath  | N
        4 |       2 | Diana | N
        5 |       2 | Elsa  | Y

db<>fiddle here


How to select column names by data type How would I get the names of all the columns in a table that are of a specific type such as datetime?

Even better -- How can I do the same thing but with multiple tables joined together and then list the column name along with the table it comes from?

Query sys.columns and sys.types like this:

select object_name(c.object_id) TableName, *
FROM sys.columns c
join sys.types t ON c.system_type_id = t.system_type_id
where t.name = 'datetime'


Get SSMS to show the time a query completed at? I'm using Sql Server Management Studio 2012, and I'm wondering if there is a way to have a query show me at what time it was run and/or completed at. Basically just some kind of timestamp so that I know if I actually ran the query recently or not.

Thanks

Hitting F4 also enables properties view, which shows the start time, finish time, and run time.

Also, if you Include Client Statistics (under Query menu), you can get a history of when you ran the query, along with various statistics such as run time and number of rows.

Update, 2019-10-14: SSMS 18 now includes the completion time in the Messages pane of the results tab.


Check entry based on entry in another column Still fairly new to databases and SQL, so bear with me. I'm trying to check whether or not data can be inserted depending on data in another column,

create table Table1
       (PaymentType varchar(5) not null
       check (PaymentType = 'CC' or PaymentType = 'PO' or PaymentType = 'Check'),
       CCNumber varchar(16)
       check (PaymentType = 'CC' and (LEN(CCNumber) = 15 or LEN(CCNumber) = 16) ),
       PONumber varchar(30)
       check (PaymentType = 'PO'),
       CheckNumber varchar(10)
       check (PaymentType = 'Check') );

This probably isn't the way to go about it, but I want CCNumber to accept data if PaymentType is 'CC', PONumber if PaymentType is 'PO', etc. How would I create those checks or constraints properly?

One way would be a check constraint, but this will not give meaningful error messages

create table Table1
 (PaymentType varchar(5) not null,
 CCNumber varchar(16),
 PONumber varchar(30),
 CheckNumber varchar(10));
 GO
 ALTER Table Table1
 add CONSTRAINT CHK_PAYMENTTYPE CHECK
  (CASE WHEN PaymentType = 'CC' and CCNumber is NOT NULL and PONumber is NULL and CheckNumber is NULL THEN 0
    WHEN PaymentType = 'PO' AND PONumber IS NOT NULL and CCNumber IS NULL and CheckNumber is NULL THEN 0
    WHEN PaymentType = 'Check' AND CheckNumber IS NOT NULL and CCNumber IS NULL and PONumber is NULL THEN 0
    ELSE 1 END = 0);

It would probably be better to do a foreign key relationship or just have a single column for the number with a check constraint that checks that the number is of correct format for each payment type

ps: dont store CreditCard numbers unencrypted in a database...


Dropping the automatically created NT AUTHORITY and NT SERVICE accounts So I recently moved jobs - one piece of code I've spotted in our build scripts for new SQL Server installations is below.

IF EXISTS ( SELECT  *
            FROM    [sys].[syslogins]
            WHERE   [name] = N'NT AUTHORITY\SYSTEM' )
    BEGIN
        DROP LOGIN [NT AUTHORITY\SYSTEM];
    END

IF EXISTS ( SELECT  *
            FROM    [sys].[syslogins]
            WHERE   [name] = N'NT SERVICE\SQLWriter' )
    BEGIN
        DROP LOGIN [NT SERVICE\SQLWriter];
    END

IF EXISTS ( SELECT  *
            FROM    [sys].[syslogins]
            WHERE   [name] = N'NT SERVICE\Winmgmt' )
    BEGIN
        DROP LOGIN [NT SERVICE\Winmgmt];
    END
GO

These accounts are created during the SQL Server installation process by default.

Are dropping the above logins recommended? Are there any side effects this could cause? What are these logins used for?

I read Can I delete NT SERVICE\SQLWriter and NT SERVICE\Winmgmt logins? but it just isn't concrete enough - I can see what they're needed for but very little else. Do they need sysadmin access? etc.

As an example (taken from Configure Windows Service Accounts and Permissions):

  Local System is a very high-privileged built-in account. It has extensive privileges on the local system and acts as the computer on the network. The actual name of the account is NT AUTHORITY\SYSTEM.

How should I read this? Should it be left with these "high" privileges?

Before you downvote, here is formal documentation you can reference for a legitimate reason why these accounts would be scripted as you see: STIG Rule SV-53421r2_rule.  These controls are SQL Server version specific, but there are a number of other controls in the more recent versions as well.  If you work for an organization that falls under these controls and also adheres to some of the more stringent ones, such as revoking default Grants given to the public role things can get complicated pretty quickly.

As I have some experience in an environment that falls under these controls, I can say that you can disable/drop both the NT SERVICE\SQLWriter and NT SERVICE\Winmgmt accounts outright, but you need to make sure your SQL Server Service is running under an appropriate service account with sufficient OS level permissions, such as a sufficiently locked down Domain Service Account, or even better, standalone or group Managed Service Accounts.  Again, OS permissions need to be treated with care as this does venture into house-of-cards territory if you don't perform sufficient testing.

As for the NT AUTHORITY\SYSTEM account, this is NOT an account you want to drop if you are going to be running any Availability Groups.  In fact, STIG SV-93835r1_rule mentions that you should keep it with only CONNECT SQL permissions granted by default.  In the event that you have an Availability group, this account will also need to have the following, additional permissions:

ALTER ANY AVAILABILITY GROUP
VIEW SERVER STATE
EXECUTE ON [sys].[sp_server_diagnostics]
EXECUTE ON [sys].[sp_availability_group_command_internal]

These restrictions can be a royal pain, but there are legitimate reasons you would need to limit their use and permission sets.  If you don't fall under these guidelines, do yourself the favor and comment out those steps.

Hope that helps!


Can a killed application process cause blocking of the server? We use an application made by HPE which blocks completely from time to time. The application comes with Java 6, uses ojdbc6 and connects to Oracle 11.
HPE claims that when one of our 6 application servers dies when it runs a blocking update to a central table this causes the Oracle server to wait infinitely effectively rendering all servers defunct.
I can see the blocking statement (our DBA showed me) and can see that the connection pool of all servers grows until it hits a defined maximum. But I have doubts that Oracle does not detect the died connection.

Can someone confirm HPEs theory or give me a hint to further diagnose this ?

By default, an Oracle database does not detect dead clients, you need to enable it explicitly by setting SQLNET.EXPIRE_TIME in sqlnet.ora.

SQLNET.EXPIRE_TIME

Since the database does not detect dead clients, it maintains its session and locks, so the answer is yes, killed application processes can cause blocking.

This parameter is often misinterpreted. Setting this parameter to 10 does not mean dead connections will disappear at most in 10 minutes. This means the database server sends a probe packet every 10 minutes. The time required to detect a dead client depends on the operating system as well. For example on Linux, an already established TCP connection is valuable, so even if there is no answer from the other side, the server keeps trying to reach it. When the database server sends the probe packet and receives no answer, it will try again, 15 times in total (because of the tcp_retries2 kernel parameter), and the total time needed to detect a dead client can be way more than 10 minutes.


How to grant insert in a table that is in another database on SQL Server 2008 R2? On SQLServer 2008 R2 I have 2 schemas: DatabaseA and DatabaseB.

On DatabaseB I have a table named TableB. Whenever tableB is updated, a trigger will insert a value in the tableA (that is on DatabaseA).

For that I need to grant insert / update on tableA to UserB (that is on DatabaseB).

dbo is the owner of tables on DatabaseA and UserB is a service user of tables on DatabaseB (I mean, UserB is not the owner from tables on DatabaseB. It has limited access to them - For some tables he can insert / update permissions and for other tables it has just select permission).

After reading these articles

GRANT Object Permissions (Transact-SQL)

Setting user permissions for different SQL Server schemas

I tried the following:

USE [DatabaseA]
GRANT INSERT, UPDATE ON DatabaseA.dbo.TableA TO [DatabaseB].[UserB]
GO

I got the error:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '.'.

Then I tried

USE [DatabaseA]
GRANT INSERT, UPDATE ON DatabaseA.dbo.TableA TO [UserB]
GO

And got message below (what makes sense taking in account UserB doesn't exist on DatabaseA):
*Msg 15151, Level 16, State 1, Line 2

Cannot find the user 'UserB', because it does not exist or you do not have permission.*

Then I tried

USE [DatabaseA]
GRANT INSERT, UPDATE ON DatabaseA.dbo.TableA TO [DatabaseB\UserB]
GO

Which gave me
Msg 15151, Level 16, State 1, Line 2
Cannot find the user 'DatabaseB\UserB', because it does not exist or you do not have permission.

UserB does exist on DatabaseB and I'm issuing the command as sa user so I don't get what I'm doing wrong.

Any ideas?

Edited: Fixed the naming. It's database. Not schema as pointed out by Kris Gruttemeyer

Indeed what I was looking for (but were unaware of its name) is called cross database ownership chaining.

By reading the article Understanding Cross Database Ownership Chaining in SQL Server  I was able to solve my problem.

As a reference, below is what I did:

Use [DatabaseA]
GO

ALTER DATABASE [DatabaseA]
SET DB_CHAINING ON
GO

Use [DatabaseB]
GO
ALTER DATABASE [DatabaseB]
SET DB_CHAINING ON
GO

EXEC sp_grantdbaccess 'UserB';
GO

GRANT SELECT, UPDATE on [DatabaseB].[dbo].[TableA] TO [UserB]
GO

Thank you all.


mySQL trigger - prevent insert won't work for multiple inserts This is not another "how to prevent insert with triggers" thread. I have done my research and I found a couple of ways but unfortunately, they won't work in my case.

I have a table in my database with a numeric column called "value". I want to cancel a new insert if its value is near the value of a previous insert. So here are my attempts:

first try:

IF EXISTS(SELECT * FROM test2 WHERE ABS(new.val - val) <= 1) THEN
  SET new.val = null;
END IF

Results: It works well for one insert (throws an error and cancels the insert). However, for multiple inserts at once, all records are inserted (and the inserts that should be rejected have value 0).

second try:

IF EXISTS(SELECT * FROM test2 WHERE ABS(new.val - val) <= 1) THEN
  SIGNAL SQLSTATE '45000';
END IF

Results: Again, works well for one insert. But for multiple inserts at once, it will reject all inserts even if there is only one that should be rejected.

Any suggestions?

I hope I was clear. Thank you in advance!

This is expected behaviour: if you raise a SIGNAL, the entire statement is rolled back; if you set new.val to NULL, you insert NULL (I guess your client is messing with you by showing zeroes for NULL values).

You could work around this problem by not inserting ineligible records in the first place, something like this:

insert into test2 (val) select val from (
  -- values to insert
  select 1 as val union all
  select 3
) i
where not exists (
  select 1 from test2 t where abs(t.val - i.val) <= 1
)


How can I recover a dropped table which is not in the recycle bin? I dropped a table yesterday and I want to restore it. I know that it's done with flashback but the table is not present in the recycle bin (yes it is set as ON).

So how can I restore a table that is not in the recycle bin. I am logged in as sysdba and also there are no rows neither in the dba_recyclebin nor in the user_recyclebin.

You can clone your production database to a point in time before the table was dropped, then export the table you need and import into your prod database.
You can also try a table space point in time recovery. I tested it out a few years back, but I haven't needed to do it since.

Performing RMAN Tablespace Point-in-Time Recovery (TSPITR)


Amazon RDS - Can't connect to MySQL database server? So I've been working on this issue for a while now, and I'll explain what I'm trying to do. I'm trying to connect to a MySQL database server, hosted by Amazon Web Services in the Amazon RDS service. The database is up and running, and I've created a security group, as well as an EC2 security group. Within the Ec2 security group, I've opened up port 3306 (the MySQL port), and added it as a rule. I've then applied this rule. I've then connected the security group to the EC2 security group, and I connected the database to the security group. Thus the database should have port 3306 open (and for all I know it does!).

So here's the issue. I'm trying to connect to the MySQL server based on the IP of the database I'm given by Amazon Web Services. However, when I attempt to connect via the Terminal command shell, the connection times out, saying that the MySQL connection failed.

Here's what I type in: "mysql -h *****.rds.amazonaws.com" where the asterisks are part of the IP address.

The error is as follows: "ERROR 2003 (HY000): Can't connect to MySQL server on '*****.rds.amazonaws.com' (60)"

I've also tried specifying a username and password to the command, but the same error occurs.

Any thoughts as to why the connection is failing?

Thanks, any help appreciated!

Jake

1) Make sure that RDS instance has access to ec2 instance from where you are trying to connect

2) From ec2 instance connect to Amazon RDS instance using telnet
$telnet

If telnet fails means connection to MySQL port is not possible.


Can you remove the redirect from transactional replication from an HADR publisher? We have a pair of SQL Server 2012 availability groups that we have set up transactional replication to a 2012 distributor and subscriber.

We are moving databases for the (synchronized) secondary nodes to new faster less congested hard drives. Since these secondaries are synchronized, the transactions never commit and replication doesn't happen.

According to the BOL sp_redirect_publisher should be used if the replica on the primary is taken offline. However it doesn't appear to have a scenario quite like what I'm looking for.

Has anyone had to move database files of an AG database to new drive media while that db was being published? If so what steps did you take?

Apparently I was overthinking the problem. Running the following:

Exec dbo.sp_redirect_publisher @original_publisher   =  'SQLServer03',
                               @publisher_db         =  'LMDB25',
                               @redirected_publisher =  NULL;
Go

where @original_publisher is the currently and original primary node,
and @publisher_db is the database name.

To replace the redirects so that replication will work normally again simply re-run the command changing the NULL parameter value to the server node name of the previously offline server node:

Exec dbo.sp_redirect_publisher @original_publisher   =  'SQLServer03',
                               @publisher_db         =  'LMDB25',
                               @redirected_publisher =  'SQLServer04';
Go


How to make pg_basebackup use temporary replication slots This article
https://blog.hagander.net/another-couple-of-steps-on-my-backup-crusade-235/
states
that temporary replication slots can be "used to prevent the wal on the server from being removed while a downstream replica or backup still needs it"

Apparently, temporary replication slots can now be used by pg_basebackup since 10.
My question is how do we enable this feature or does it happen now automatically with pg_basebackup?

Also, is this feature related to the -X stream option
"Stream the write-ahead log while the backup is created. This will open a second connection to the server and start streaming the write-ahead log in parallel while running the backup. Therefore, it will use up two connections configured by the max_wal_senders parameter. As long as the client can keep up with write-ahead log received, using this mode requires no extra write-ahead logs to be saved on the master." which is enabled by default?
https://www.postgresql.org/docs/current/static/app-pgbasebackup.html

The slot to use for -X stream is specified with the --slot option. Its documentation says:

  If this option is not specified and the server supports temporary replication slots (version 10 and later), then a temporary replication slot is automatically used for WAL streaming.


Difference between SQL batch, statement and RPC? What is the difference between SQL batch, T-SQL statement and Remote Procedure Call?
How can I tell if part of the T-SQL code is a batch or statement?

Well, I suppose you're talking mostly about the Profiler classes, but the explanation stands anyway.

An SQL batch is a set of one or more statements grouped together and separated by a GO statement. EG: more SELECT and INSERT statements form a batch if they have a GO at the end.

A RPC call is a call that comes from a client application to the database. EG: a windows service, a web application, a windows app, whatever needs a connection to the database actually makes a RPC call.

Now, in Profiler you'll see everything that touches the database server.
A batch from Management Studio, an RPC call (which is either a batch or a stored procedure call) from an external application, a procedure execution from Management Studio.

Each of them is formed of TSQL statements, so this Profiler class is useful in case you want to expand the execution further, to see what's actually executed. What inserts, selects..etc.

The easiest way to look at them in Profiler is to enable only End RPC call, or End batch call and you'll see there all the statistics needed (duration, IO, CPU). Then, move further by enabling TSQL Statements class and dig deeper.


Is possible to backup database online using rsync? Some people tell me that the backup database on-the-fly using rsync is possible. I think it is possible but not safe because some log, buffer... is not flushed. Database is being recovered from the backup would not consistent.

Actually I am not sure why the backup using rsync is not safe. Could anyone explain me the underlying of mysql for the reason ?

When it comes to InnoDB, you are right to be concerned.

Here is a Pictorial Representation of InnoDB (Made by Percona CTO Vadim Tkachenko)

The whole left side of the diagram represents InnoDB's moving parts in memory. The critical piece here is the InnoDB Buffer Pool

The InnoDB Buffer Pool holds three things

Data Pages : If a page is dirty, it is eventually written to its .ibd file
Index Pages : If a page is dirty, it is eventually written to its .ibd file
Secondary Index Changes : Dirty index pages related to non-unique indexes are written to ibdata1

Any page that has been updated is also written beforehand into the Double Write Buffer inside ibdata1.

If you rsync everything in datadir, you are chasing too many moving files

ibdata1 itself has moving parts

Double Write Buffer
Inset buffer
Rollback Segments
Undo Logs

Redo Logs (ib_logfile0 and ib_logfile1)
Every .ibd file being updated

If you had to rsync your datadir to back it up and you attempt to setup it and start mysql, the best case scenario would be crash recovery, an initiated repair of data and index pages using the Double Write Buffer and the Insert Buffer and some playback from the Redo Logs. This will roll forward whatever data can be saved via crash recovery.

It would be fair to say that rsync could possibly miss dirty pages that have not been written the Double Write Buffer and the Insert Buffer if the rsync got passed it already.

I have written posts about doing rsyncs for backup

Aug 19, 2012 : How do I replicate a MySql server's data without using mysqldump?
Jun 17, 2011 : What is the difference between XtraBackup and rsync?
May 23, 2011 : How can I move a database from one server to another?

If you want to try to rsync, you could use the script from How do I replicate a MySql server's data without using mysqldump? but you need to properly flush all moving parts

About 10 minutes before the backup, set buffer pool to have dirty pages flush aggregsively

SET GLOBAL innodb_max_dirty_pages_pct = 0;

Then, just before starting the rsync, run these commands

FLUSH LOGS;
FLUSH TABLES WITH READ LOCK;
SELECT CONNECTION_ID() ID_With_Lock;
SELECT SLEEP(86400);

You could run the rsync at this point, but any new transaction will be held up in the Buffer Pool and will not commit to disk until you login to MySQL in a separate session and kill the process ID (ID_With_Lock).

EPILOGUE

It is possible to have a good rsync backup if handled with great care. Percona XtraDB Cluster has 3 modes of adding new node to a Galera Cluster (xtrabackup, mysqldump, rsync)

If you are not that confident doing rsync backups, you are better off just doing mysqldump --single-transacton and retrieving binlog events to copy data and roll forward to what ever point-in-time you want. You could also resort to Percona XtraBackup and let it do the heavy lifting.


MongoDB: All commands spit out "not authorized on admin to execute command" Mongo version: 3.2.11

I spun up a mongoDB droplet with DigitalOcean so that I could have a database connected to my little game. However, complications after complications have arisen.
I am connecting to the droplet through PuTTY and was previously able to invoke "mongo" then type "show dbs" to see all the current databases.

I had been altering the /etc/mongod.conf file to allow remote connecting, and since then - any command that I invoke spits back an error message:

Just through a simple show dbs - I get this.

> show dbs
2016-12-28T00:12:26.655+0000 E QUERY    [thread1] Error: listDatabases failed:{
        "ok" : 0,
        "errmsg" : "not authorized on admin to execute command { listDatabases: 1.0 }",
        "code" : 13
} :
_getErrorWithCode@src/mongo/shell/utils.js:25:13
Mongo.prototype.getDBs@src/mongo/shell/mongo.js:62:1
shellHelper.show@src/mongo/shell/utils.js:761:19
shellHelper@src/mongo/shell/utils.js:651:15
@(shellhelp2):1:1

I'm so confused as to why this is and I've been scouring the internet to find anything that could possibly resolve this.

PS: I had altered the /etc/mongod.conf file by uncommenting security and adding authorization: 'enabled' to it, as well as commenting out bindIp: 127.0.0.1 under net.

Let's try this:

Connect to your DO droplet.

In your /etc/mongod.conf, comment out 'security' and restore bindIp to value 127.0.0.1

Restart your mongod instance.

Connect to mongo (at this point without security).

Create you admin user in mongo with something like this:

use admin
db.createUser(
  {
    user: "myUserAdmin",
    pwd: "abc123",
    roles: [ { role: "userAdminAnyDatabase", db: "admin" } ]
  }
)

Make sure the user was successfully created.

Go back to your /etc/mongod.conf. Set your bindIp value as: 127.0.0.1,YOUR_DO_DROPLET_IP (no spaces between).

Uncomment 'security', keep 'authentication' commented out, add below it 'authorization: enabled'

Restart your server's mongod instance.

Connect to mongod remotely by using:

mongo YOUR_DO_DROPLET_IP -u myUserAdmin -p --authenticationDatabase admin

References:
MongoDB Authentication
Enabling MongoDB Authentication


First and last records in a string of linked rows I have been handed an (ancient) database in SQLServer format containing rental records. These records can be modified by the customer while the rental is still in flight, if they want the drill for another 24 hours for instance. To indicate this, a new row is written to the database covering that additional period, and a pointer is left in the original record to say "this one modified me". The result is something like this:

pk   custId    prodId   overrideByPk   startDate   endDate
----------------------------------------------------------
1    1         1        0              1-1-2000    2-1-2000
2    1         1        3              1-2-2000    2-2-2000
3    1         1        4              3-2-2000    4-2-2000
4    1         1        0              4-2-2000    5-2-2000

These records represent two rental terms. The first ran for a single day in January, and the second ran from the 1st to the 5th of February. My goal is to produce output like this...

pk   custId    prodId   startDate   endDate
--------------------------------------------
1    1         1        1-1-2000    2-1-2000
2    1         1        1-2-2000    5-2-2000

It seems, based on the data I've looked at so far, that a record with a overrideByPk=0 is the last record in a string, even in a string of one row. Note that my example has separate date periods, but that's only for clarity. The customer may indeed return the item and the pick it back up the same day, so you can't look for discontinuities in the dates.

I can do this in C#  code by getting all the rows with zero, and then looking for those overrideByPk's repeatedly, but there are thousands of these and I am concerned about performance and code complexity. I seem to recall solving this in SQL in the past, but can no longer remember the trick (if it existed).

If you are using SQL 2012 as your tag indicates, then you can do this with a recursive CTE, although it's a little different since you find the child first and then the parents.  But Take a look at the sample below.

The Setup

DECLARE @Rentals TABLE
    (
    PK INT NOT NULL
    , CustID INT NOT NULL
    , ProdID INT NOT NULL
    , OverrideByPK INT NOT NULL
    , StartDate DATE NOT NULL
    , EndDate DATE NOT NULL
    );

INSERT INTO @Rentals
        ( PK
        , CustID
        , ProdID
        , OverrideByPK
        , StartDate
        , EndDate
        )
VALUES  ( 1,1,1,0,'1/1/2000','2/1/2000')
    , ( 2,1,1,3,'1/2/2000','2/2/2000')
    , ( 3,1,1,4,'3/2/2000','4/2/2000')
    , ( 4,1,1,0,'4/2/2000','5/2/2000');

The Query

WITH CTE_Rentals AS
    (
    SELECT R.PK AS PK
        , R.OverrideByPK AS NextPK
        , R.PK AS OldestPK
    FROM @Rentals AS R
    WHERE R.OverrideByPK = 0
    UNION ALL
    SELECT R.PK AS LastPK
        , R.overrideByPK
        , CR.OldestPK
    FROM @Rentals AS R
        INNER JOIN CTE_Rentals AS CR ON R.OverrideByPK = CR.PK
    WHERE R.OverrideByPK <> 0
    )
, CTE_Single AS
    (
    SELECT C.OldestPK AS LastPK
        , MIN(C.PK) AS FirstPK
    FROM CTE_Rentals AS C
    GROUP BY C.OldestPK
    )
SELECT S.FirstPK
    , FirstRental.CustID
    , FirstRental.ProdID
    , FirstRental.StartDate
    , LastRental.EndDate
FROM CTE_Single AS S
    INNER JOIN @Rentals AS FirstRental ON FirstRental.PK = S.FirstPK
    INNER JOIN @Rentals AS LastRental ON LastRental.PK = S.LastPK;


Is there a way I can speed up queries against this particular view? I have three tables in a PostgreSQL database that I'm querying via a view and some joins.

CREATE TABLE network_info (
  network         CIDR          NOT NULL,
  some_info       TEXT          NULL,
  PRIMARY KEY (network)
);

CREATE TABLE ipaddr_info (
  ipaddr          INET          NOT NULL,
  some_info       INT           NULL,
  PRIMARY KEY (ipaddr, some_info)
);

CREATE TABLE ipaddrs (
  addr            INET          NOT NULL,
  PRIMARY KEY (addr)
);

CREATE VIEW ipaddr_summary AS
SELECT DISTINCT
  i.addr                  AS ip_address,
  a.some_info             AS network_info,
  COUNT(b.ipaddr)         AS ip_info_count
FROM ipaddrs AS i
LEFT JOIN network_info AS a
  ON (i.addr << a.network)
LEFT JOIN ipaddr_info AS b
  ON (i.addr = b.ipaddr)
GROUP BY i.addr, a.some_info
;

All of the tables have ~150K rows right now, and it takes a really long time (~3 hours) to run SELECT * from ipaddr_summary; on an Intel Pentium 4 2.8GHz dual core with 2G of memory running PostgreSQL 9.3.

Is there a way I can restructure or optimize this particular schema or view to make the query faster, or is it a hardware issue? I'm going to spin up a beefy VM in the cloud and test, but wanted to see if there's a way to optimize w/out just throwing more hardware at it.

There might be hardware issues, too - how should we know? But there are certainly issues with the query.

First of all, remove DISTINCT from your VIEW definition. It's doing nothing at all (but complicating and slowing things down). Related answer on SO with explanation:

PostgreSQL - Slow query joining on a VIEW

Arriving at this (cleaned up) query:

SELECT i.addr      AS ip_address
     , a.some_info AS network_info
     , COUNT(b.ipaddr) AS ip_info_count
FROM   ipaddrs           i
LEFT   JOIN ipaddr_info  b  ON i.addr = b.ipaddr
LEFT   JOIN network_info a  ON i.addr << a.network
GROUP  BY 1,2;

Next, the query looks suspiciously like it's going very wrong. Two uncorrelated joins can multiply rows:

Two SQL LEFT JOINS produce incorrect result

With 150k rows in each table, there is potential for a huge (unintended) Cartesian product. My educated guess, you really want this:

SELECT addr        AS ip_address
     , a.some_info AS network_info
     , b.ip_info_count
FROM   ipaddrs i
LEFT   JOIN (
   SELECT  ipaddr AS addr, count(*) AS ip_info_count
   FROM    ipaddr_info
   GROUP   BY 1
  ) b USING (addr)
LEFT   JOIN network_info a ON i.addr << a.network

I suspect that GROUP BY is also not needed in the outer SELECT now.
Besides fixing the count, this might also be faster by several orders of magnitude, avoiding the proxy cross-join.

You could first join to ipaddrs (especially if you have predicates filtering rows from it) and then aggregate, or first aggregate in the subquery like displayed. Usefulness of this variant largely depends on data distribution. It's great for few ipaddr with big counts. Details:

Slow queries related to subqueries using aggregation

Finally, you need index support. Equality between ipaddr and addr is covered by the default btree indexes of the PRIMARY KEY. The query on the whole table is probably using a sequential scan anyway.

For the "is contained by" operator << operator you'll need a GIN or GiST index. The best option would be the new inet_ops GiST index operator class in Postgres 9.4 (supports data types inet and cidr):

CREATE INDEX ON network_info USING gist (network inet_ops);

Not sure if the index can be used in a plain INNER (or OUTER) join. Can't test right now. Maybe you need correlated subqueries or a LATERAL join to utilize the index:

SELECT addr AS ip_address
     , a.network_info
     , b.ip_info_count
FROM   ipaddrs i
LEFT   JOIN (
   SELECT  ipaddr AS addr, count(*) AS ip_info_count
   FROM    ipaddr_info
   GROUP   BY 1
  ) b USING (addr)
LEFT   JOIN LATERAL (
   SELECT some_info AS network_info
   FROM   network_info
   WHERE  network >> i.addr
   ) a ON TRUE;

Advice for indexing in older versions:

PostgreSQL field data type for IPv4 addresses


List all MongoDB databases from Linux bash terminal Can you list all databases in MongoDB from a linux bash terminal? I cannot find the correct command. I am trying to list all so as that I can write a bash script to backup all DB's individually. Is there a better way?

"show dbs" calculate the databases sizes and that may take some time.

You can pass this command to the mongo shell, then clean the response:

echo "db.getMongo().getDBNames()"|mongo --quiet |tr -d \[\] | tr , "\n"|cut -c3-| tr -d \"


Why does my database using the simple recovery model have such a large transaction log? Not a duplicate with:

Transaction Log is big! But simple recovery mode is chosen!
Why is my transaction file growing when db is in simple mode and there are no waiting transactions

Reason: My log is not big, but empty. It's big, but in use.

The Problem

I have a small database (100 MB) which gets hit with a lot of small transactional workloads throughout the day. I've watched the transaction log continue to grow despite seemingly understanding how to cap it. Clearly I'm missing something.

The database is in simple recovery model (I can't guarantee it wasn't in Full at one point when it was first created, but it has never been put under load in Full).
DBCC SQLPERF(Logspace) says:

| Log Size (MB) | Log Space Used (%) | Status
--------------------------------------------
| 927.2422      | 94.72562           | 0

DBCC LOGINFO says:

There are 39 rows (VLFs), all of which have a Status of 2.

DBCC opentran says:

  Oldest active transaction:
    SPID: 51
    Name: user_transaction
    Start time   : Mar 11 2019  7:27 PM

note: Given my server time, this transaction is about five minutes old.

I can't understand why every VLF is in use when my application only creates - at most - about a half dozen short-lived transactions, albeit very frequently.

I've tried killing the sole application process which connects to this database, but it didn't free up the transaction logs.

Workaround

I did a full backup just to scratch that itch and it freed up the entire log space... so... I don't know. I'll have to see if it grows excessively again.

Your first stop for problems like this should always be this query:

SELECT log_reuse_wait, log_reuse_wait_desc
FROM sys.databases
WHERE [name] = 'YourDatabaseName';

From the sys.databases documentation:

  Reuse of transaction log space is currently waiting on one of the following as of the last checkpoint.

There's a list of possible values on that page, and a fair amount of additional detail for the different values here: The Transaction Log (SQL Server) - Factors that can delay log truncation

It's possible that, if your server is not very busy, that it had been a while since an automatic CHECKPOINT ran.  This is the process that writes data pages to disk after they have been modified in memory.  Operations in the transaction log can't be overwritten until they have been successfully flushed to the data file, so you end up with a log_reuse_wait of 1 = Checkpoint.

Since you're using the simple recovery model, this seems like one of the more likely culprits (since you ruled out long-running transactions).  Running a full database backup causes a CHECKPOINT to run, which is likely what caused the log file to finally clear for you after running the backup.  From Database Checkpoints (SQL Server), specifically about the "Internal" checkpoint type:

  Issued by various server operations such as backup and database-snapshot creation to guarantee that disk images match the current state of the log.


Warnings while running AutoMysqlBackup I am using Automysqlbackup.sh file to take MySql backups on Ubuntu.I have only 1 database.
But when i run this script then it generates several Warnings (plus a error mail) like below

Errors reported during AutoMySQLBackup execution.. Backup failed
Error log below..
Warning: Using a password on the command line interface can be insecure.
Warning: Using a password on the command line interface can be insecure.
Warning: Using a password on the command line interface can be insecure.
Warning: Using a password on the command line interface can be insecure.

i am using the latest Version of AutoMysqlBackup file from link below
http://sourceforge.net/projects/automysqlbackup/

I also tried to solve the error by creating a .properties file for writing the password and reading it in the automysqlbackup script. But no help!

Can Anyone Help me?

Solution to your problem is below :-

On line 115 there is a line that says

removeIO

Add this after removeIO:

# Remove annoying warning message since MySQL 5.6
if [[ -s "$log_errfile" ]]; then
sedtmpfile="/tmp/$(basename $0).$$.tmp"
grep -v "Warning: Using a password on the command line interface can be insecure." "$log_errfile" > $sedtmpfile
mv $sedtmpfile $log_errfile
fi

And you will receive no more daily emails regarding that it is unsafe to use passwords on the command line.

Referred Link:

http://www.redeo.nl/2013/11/automysqlbackup-warning-using-password-command-line-interface-can-insecure/

But I don't know if this is the cleanest solution.

To make this solution work for MySQL 5.7+, the grep statement needs to be modified to take account of the altered warning message:

grep -v ".*: \[Warning\] Using a password on the command line interface can be insecure\." "$log_errfile" > $sedtmpfile


How to fetch the transaction activities/logs for a specific table? I can see the transaction logs with fn_dblog, but I need to do this for specific table. How can I do that?

You could try the following:

Retrieve the id for your user table:

SELECT id, name FROM sys.sysobjects WHERE NAME = '<Your_Table_Name>'

e.g. 658101385

Retrieve the Transaction Log information with the undocumented function:

SELECT * FROM ::fn_dblog(NULL,NULL) WHERE [Lock Information] LIKE '%658101385%'

And then carry on from there.

Reference: Identifying Object Name for CREATE and ALTER Using fn_dblog()


Update column values to integers I have a column in my table that I need to change the data type from VARCHAR to DOUBLE. Now I cannot convert it to DOUBLE data type easily because the values on that column have numbers and strings.

> 0 41  14:16:57
  ALTER TABLE `ControlAutomation`.`constantcomponentstabular`
     DROP COLUMN `String`,
     CHANGE COLUMN `ConstantValue` `ConstantValue` DOUBLE NULL DEFAULT NULL

> Error Code: 1265. Data truncated for column 'ConstantValue' at row 217966 1.891 sec

I know what is causing this issue, its because there are strings on the column that I want to change to DOUBLE.

Here is the solution that I have in mind using Python

Query the table.
Check whether the value can be converted to float.
If not, UPDATE value of that column to 0.

This process takes a long time, My question is how to best change those values to any number so I will be able to ALTER the table.

I am using MySQL 5.6.32 and InnoDB as storage engine.

Let me suggest to add a new column and then update this new column before to drop the old one.

Backup, backup, backup

create table mytable (ConstantValue varchar(100));

insert into mytable values
('1252.36'),('abc'),('55969'),('def'),('66955663'),('A8879'),('-41.25'),('+636');

Add a new column with a default value of 0.

alter table mytable
    add column NewConstantValue double default(0);

Set alphanumeric values to 0. I've borrowed this regex expression from MySql forums

update mytable
set    ConstantValue = '0'
where  ConstantValue not regexp '^(-|\\+)?([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$';

Update new column with current values.

update mytable
set    NewConstantValue = cast(ConstantValue as double);

Check new values before to drop old column.

select * from mytable;

ConstantValue | NewConstantValue
:------------ | ---------------:
1252.36       |          1252.36
0             |                0
55969         |            55969
0             |                0
66955663      |         66955663
0             |                0
-41.25        |           -41.25
+636          |              636

Drop ConstantValue column.

alter table mytable
    drop column ConstantValue;

Rename new column as ConstantValue.

alter table mytable
    change column NewConstantValue ConstantValue double default(0);

Check final result.

select * from mytable;

| ConstantValue |
| ------------: |
|       1252.36 |
|             0 |
|         55969 |
|             0 |
|      66955663 |
|             0 |
|        -41.25 |
|           636 |

dbfiddle here


InnoDB log sequence number is in the future 130711 23:03:40 InnoDB: Error: page 23 log sequence number 3336825872455
InnoDB: is in the future! Current system log sequence number 235089.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files.

I've never seen or had this error before. The client does not have backups so they need a recovery done.

I've done MySAIM recovery before but never InnoDB. Is there any tips on how to do it correctly?

From what I've read up on I need to increase a single counter ("log sequence number") which is stored somewhere in the headers of ib_logfile0 and ib_logfile1 or something like that correct?

The only correct solution is to reinstall and restore from a backup.  You say you don't have a backup, but the point here is that the idea behind forcing InnoDB recovery is that you can potentially get the server stable enough that you can make a backup.  Then use it to restore on a fresh setup.

Obviously, make a copy of all the files before doing anything.

Unless there's a memo I didn't get, you don't want to try increasing the log sequence number to pacify InnoDB, because even if you manage to do it, you've still got a server with data corruption in unknown places -- fix the LSN and all you've really accomplished is made InnoDB unaware of the corruption.  Forcing the LSN is pretty much a fool's errand.

Have you tried anything from...

http://dev.mysql.com/doc/refman/5.0/en/forcing-innodb-recovery.html

The different levels, 1 through 6, cumulatively disable parts of InnoDB with the idea that if you disable enough functionality you might be able to get a successful mysqldump or at least SELECT ... INTO OUTFILE from the tables.

The idea is to use the lowest number that allows you to successfully dump your data, but if this error is occurring at startup like I think it may be, you might just want to go ahead and start with 6.  If the server starts up, then try selecting from tables.  It may crash again and if it does, then write that table off for the moment and move to the next table.

Once you've retrieved the data from the tables that aren't corrupt, you may be able to go back to the ones that don't work, and try to retrieve the data in small chucks with SELECT ... LIMIT ... OFFSET.

Take a look at my answer to this similar question for some additional ideas.

You may also want to look at Percona's InnoDB data recovery tool as a potential resource, though I've never (fingers crossed) been caught without a backup or a running replication slave, so I've never needed it.


How to construct a set-based query for subset problem? I have a DB of the following structure (picture shows simplified version)

How should I construct a query to get only those recipees, that can be cooked, i.e. for each ingredient in a receipe requiredQuaintity > availableQuantity?

I tried this:

SELECT r.Name
FROM
   Receipe r
   JOIN RecipeIngredients ri ON ri.RecipeID = r.Id
   JOIN Ingredients i ON i.ID = ri.IngredientsId
WHERE
   ri.RequiredQuantity - i.AvailableQuantity > 0

but am not sure this is correct as I think this will only return available ingredients. How should I modify above query to produce only receipees where each of ingredient is available?
Thank you for help

---- Edit ----

Maybe something like this:

SELECT r.Name
FROM
(
    SELECT   r.Name AS Name
             r.Id AS Id
           , CASE (
                WHEN (ri.RequiredQuantity - i.AvailableQuantity >= 0)
                THEN 1
             ) AS Available
    FROM
       Receipe r
       JOIN RecipeIngredients ri ON ri.RecipeID = r.Id
       JOIN Ingredients i ON i.ID = ri.IngredientsId
    WHERE
           ri.RequiredQuantity - i.AvailableQuantity >= 0
    GROUP BY
       r.Id
) AS results
WHERE
    // count of ingredients with value 1 for each recipe == count of all required ingredients for this recipe

Assuming referential integrity and all columns to be NOT NULL, this should be simplest and fastest:

SELECT *
FROM   Receipe r
WHERE  NOT EXISTS (
   SELECT 1
   FROM   Ingredients i
   JOIN   RecipeIngredients ON ri.IngredientsId = i.ID
   WHERE  ri.RecipeID = r.Id
   AND    ri.RequiredQuantity > i.AvailableQuantity
   );

Basically, use a NOT EXISTS anti-semi-join to rule out recipes with any shortcomings. As soon as the first is found, Postgres can drop the recipe at hand from the result and move on.

Aside: My standing advice is not to use CaMeL-case identifiers in Postgres.


Is it safe to change the name of a database? I have a SQL Server database which has data for several customers/devices.  We are changing the prefix of our website (asp.net) and our application dynamically loads the proper connection string based on the first part of the URL.  Is it safe for me to change the name of my database accordingly, or should I simply put a conditional statement in the connection string logic to make the new URL point to the old database? Thanks!

If you have queries that reference the full database name (which you likely will have if you have cross database queries), they will break. If you have Imports or reports that reference the full database name those will break. If you have linked servers (SQL server), those will break.  If you have other applications that hit the same database backend, those will likely break.

If you have none of those situations, you may be OK. However, you will still want to do this only if you have a full test suite to be able to verify that nothing broke. Keeping the database name and changing how the application connects is a far safer choice if you don't have a good test suite.


SQL Server Database grew too much too fast I have an issue with one database. The issue is as follows:
The database is being running on SQL Server 2000 Standard for the last 6 years in Full Recovery mode.

In the beginning of this summer the database was about 5GB.

Since then, the only thing we did beyond normal usage is some extensive deletions.

This week, there was a problem with the PC and I was forced to do a backup and restore it on an SQL Server 2008R2 Express and set the database in 2008 mode.

The backup file was about 1GB. When I restored it, the MDF was 9GB of size! I checked the old MDF and it was of the same size.

I checked the size of the tables and they cannot reach the 9GB reported!

I did a shrink but the size did not change.

Any clues or where to check?

Is there a chance that the Full Recovery, can affect the size of the MDF files?

I am thinking of setinng the recovery model to Simple, back-it-up and restore it. Is it going to make a difference? Can I do it on a live database?

Thanx in advance!

UPDATE:The initial size of the database is 1306 MB

UPDATE2 sp_spaceused: Database Size=8646.88 MB  Unallocated Space= 0.00 MB

reserved=1336984 KB  data=1020376 KB  index_size=210408 KB unused=106200 KB

The database is being running on SQL Server 2000 Standard for the last 6 years in Full Recovery mode.

This is fin as long as you are doing regular full and log backups to keep the log file size under control. There are plenty of articles and questions on here regarding log management.

  In the beginning of this summer the database was about 5GB. Since then, the only thing we did beyond normal usage is some extensive deletions.

Large deletions are fine but understand that the space that data used was not released back to the operating system, unless you did a shrink of the data file.

  This week, there was a problem with the PC and I was forced to do a backup and restore it on an SQL Server 2008R2 Express and set the database in 2008 mode.

I hope you tested your code! T-SQL syntax changed a good bit from SQL 2000 to SQL 2008 R2. I have supported many databases that broke due to some of these changes. Also note that Express has a limitation on how large a database can grow, 10GB with SQL 2008 R2

  The backup file was about 1GB. When I restored it, the MDF was 9GB of size! I checked the old MDF and it was of the same size. I checked the size of the tables and they cannot reach the 9GB reported!

When you do a backup of the database, the backup is only going to grab the data itself along with some configuration information of the database. One of those configuration items is the initial size of the data and log file. I would expect if you checked your database properties you might find the initial size of your data file (MDF) is set to 9GB in size.

  I did a shrink but the size did not change.

A database shrink is likely what you performed. Although ill-advised, if you need to regain disk space or just bring the data file (MDF) of your database under control you will need to perform a data file shrink. Again there are plenty of articles on MSDN and blog post that provide the way of doing this.

  Is there a chance that the Full Recovery, can affect the size of the MDF files?

Nope.

  I am thinking of settinng the recovery model to Simple, back-it-up and restore it. Is it going to make a difference?

With the size of your data file, nope.


Example where schema are better than multiple databases In several database systems (e.g., PostgreSQL and MS SQL Server) there is a concept of schema. There are many questions out there asking what schema are and how they are used; here is a good one. Usually the answer boils down to "namespace" and "permissions". However, what remains unclear to me is why a schema has any advantage over just adding a new database. A different database allows one to have both separate namespaces and permission assignments.

Can someone provide an example in PostgreSQL where multiple schema in a single database provide an advantage over multiple databases without schema? From a purely organizational perspective they appear equivalent.

Postgres documentation indeed provides the answer, although it is spread across its many sections. It begins with this general statement:

  It is not possible to access more than one database per connection. [...] Databases are physically separated and access control is managed at the connection level. If one PostgreSQL server instance is to house projects or users that should be separate and for the most part unaware of each other, it is therefore recommended to put them into separate databases. If the projects or users are interrelated and should be able to use each other's resources, they should be put in the same database but possibly into separate schemas.

There are some things that you can control only at the database level, but not at the schema level. Among them:

Object privileges. The database owner role privileges override anything granted at the object (schema, table, etc.) level, unless explicitly revoked.
Database connection privileges. You can control at the database level what users can connect to it from what IP addresses.
Configuration parameters. This allows you to tune each database for its specific workload.
Maintenance. While you can backup and restore individual schemas using pg_dump and pg_restore, operations like point-in-time recovery, log shipping, and streaming replication work at the database (actually, cluster) level.

The minor downside of having multiple databases on the same server is the storage and memory overhead of maintaining and caching system catalogs for each database.

If you often query tables in different namespaces you might also prefer multiple schemas over databases. It is possible to query tables across different databases, but this requires setting up foreign data wrappers, which brings about additional maintenance and possible performance implications.

In short, if your applications require strict security isolation (e.g. the administrator in one namespace must not have access to other namespaces), or they have very different workload patterns, and you don't need to query tables in different databases, you should choose to implement multiple databases.

If, on the other hand, the workload pattern is the same for all namespaces, and you don't require strict separation of duties, and you often query tables across namespaces, you should probably use schemas in a single database for logical isolation of data. This is often the preferred scenario for multi-tenant applications.


How to edit data to 2 tables using MySQL Workbench How to edit data in 2 tables using MySQL Workbench (or can I)?
I use the below query to get the data but unable to edit it from the table (or Form Editor).

SELECT
    *
FROM
    TABLE1
        INNER JOIN
    TABLE2 ON TABLE1.ID = TABLE2.ID
WHERE
    TABLE1.ID = 1

=====

If you are editing data through the editor(mysqlworkbench) it needs a table on where it should execute the update command and the where column. That's why you can edit the record only when you have executed SELECT * FROM table because result of this query has primary key and table So Updating the data through workbench will be allowed

If you are using join to get the record then the editor displays the record of both the tables. And workbench(editor) won't get the table and primary key to update.


Postgres10 picks very slow query plan, when faster plans seem available we are using Postgres 10.

We are seeing a situation where, for the same query, with one value it picks a super ineficient query plan, but with another value it picks a good query plan.

SELECT
   "sfe_procedure"."*"
FROM "sfe_procedure" INNER JOIN "sfe_claim"
   ON ("sfe_procedure"."claim_id" = "sfe_claim"."id")
WHERE (
   "sfe_procedure"."patient_id" = 100
   AND "sfe_claim"."from_date" >= '2011-01-01'::date
   AND "sfe_claim"."from_date" <= '2020-02-13'::date
   AND "sfe_procedure"."code" IN (
       SELECT U0."code"
       FROM "medical_codes_hediscode" U0
       WHERE U0."value_set_name" = 'Colonoscopy'
   )
)
ORDER BY "sfe_claim"."from_date" DESC LIMIT 1;

It picks this query plan:

 Limit  (cost=1001.30..5784.38 rows=1 width=46)
   ->  Nested Loop Semi Join  (cost=1001.30..2459500.48 rows=514 width=46)
         Join Filter: ((sfe_procedure.code)::text = (u0.code)::text)
         ->  Gather Merge  (cost=1000.89..2455885.11 rows=4369 width=46)
               Workers Planned: 2
               ->  Nested Loop  (cost=0.86..2454380.80 rows=1820 width=46)
                     ->  Parallel Index Scan Backward using sfe_claim_from_da_3d3cc6_idx on sfe_claim  (cost=0.43..771392.17 rows=1427744 width=8)
                           Index Cond: ((from_date >= '2011-01-01'::date) AND (from_date <= '2020-02-13'::date))
                     ->  Index Scan using sfe_procedure_claim_id_f0f8a72b on sfe_procedure  (cost=0.43..1.17 rows=1 width=42)
                           Index Cond: (claim_id = sfe_claim.id)
                           Filter: (patient_id = 100)
         ->  Materialize  (cost=0.42..11.08 rows=55 width=7)
               ->  Index Scan using medical_cod_value_s_e3a75d_idx on medical_codes_hediscode u0  (cost=0.42..10.81 rows=55 width=7)
                     Index Cond: ((value_set_name)::text = 'Colonoscopy'::text)
(14 rows)

Notice especially this row:

Parallel Index Scan Backward using sfe_claim_from_da_3d3cc6_idx
  on sfe_claim  (cost=0.43..771392.17 rows=1427744 width=8)

Which seems to suggest that its best bet is to load 1.4 mil rows, and take 771 seconds? Am I reading this wrong?

On a different value for patient_id, the query plan is sane and the query is blazing fast:

Limit  (cost=4173.67..4173.67 rows=1 width=48)
  ->  Sort  (cost=4173.67..4174.12 rows=180 width=48)
        Sort Key: sfe_claim.from_date DESC
        ->  Nested Loop  (cost=18.73..4172.77 rows=180 width=48)
              ->  Hash Semi Join  (cost=18.29..2722.51 rows=180 width=44)
                    Hash Cond: ((sfe_procedure.code)::text = (u0.code)::text)
                    ->  Index Scan using sfe_procedure_patient_id_4bcdc9ad on sfe_procedure  (cost=0.43..2677.30 rows=3829 width=44)
                          Index Cond: (patient_id = 200)
                    ->  Hash  (cost=16.82..16.82 rows=83 width=7)
                          ->  Index Scan using medical_cod_value_s_e3a75d_idx on medical_codes_hediscode u0  (cost=0.42..16.82 rows=83 width=7)
                                Index Cond: ((value_set_name)::text = 'Colonoscopy'::text)"
              ->  Index Scan using sfe_claim_pkey on sfe_claim  (cost=0.43..8.06 rows=1 width=8)
                    Index Cond: (id = sfe_procedure.claim_id)
                    Filter: ((from_date >= '2011-01-01'::date) AND (from_date <= '2020-02-13'::date))"

The DB has had ANALYZE run on it recently, so it doesn't seem like the DB is confused about the amount of work that it's signing up for... but it still seems to pick that.

Questions

Is there a way to understand why other query plans were not picked?
Am I missing something obvious - a multiple-index doesn't seem to work - that could help Postgres see a better plan faster?
Do I need to rewrite the query?

Thank you for any help or pointers!

Which seems to suggest that its best bet is to load 1.4 mil rows, and take 771 seconds? Am I reading this wrong?

You are reading it wrong in two ways.  It doesn't think it will read 1.4 mil rows.  It thinks it will start a index scan which if completed will read 1.4 mil rows, but that due to the LIMIT 1 it will get to stop very early on. Apparently it is wrong about how early it can stop, but that is what it thinks.

Also, cost estimates are in arbitrary units, not in milliseconds, so it doesn't think it will take 771 seconds.  And the cost at that level is again assuming the index scan runs to completion.  The cost of the top LIMIT node, 5784.38 arbitrary units, has been scaled back for the expected benefit of stopping early, while the costs of the child nodes have not been.

Presumably patient 100 has not had any claims in a long time (like maybe they died or moved away a couple decade ago), while PostgreSQL thinks their claims will be evenly interspersed through time.

It is hard to say more than that, without seeing the output of EXPLAIN (ANALYZE) rather than just EXPLAIN.

  Am I missing something obvious - a multiple-index doesn't seem to work - that could help Postgres see a better plan faster?

What multiple-index did you try?  I would think one on sfe_procedure (patient_id, code) would have a reasonable chance to work.

But you can just hit the planner with a big hammer by rewriting it to:

ORDER BY "sfe_claim"."from_date" + interval '0 days' DESC LIMIT 1

This will preclude use of the falsely appealing index "sfe_claim_from_da_3d3cc6_idx"


Oracle Database 11g and TFS (2010) integration I am currently in a team of 4 database developers, working with Oracle Database 11g Enterprise Edition. We use quite a bit of SQL/PLSQL and create many scripts. My colleagues send all their work to a specific person before they can be deployed in a production environment.

Instead of bombarding a colleague like this, we'd like to use a version control tool (Team Foundation Server 2010 in our case), pretty much as a code repository, where our work can be saved on a regular basis.

Since noone -- including me -- has any experience with TFS, I'd like to ask:

How can we best deploy TFS (or perhaps any other version control tool) in a development and test environment, as a way to manage our work? Any downsides/advantages, any specific points to take into consideration, any tips/pointers?

This is more of an organisational exercise, rather than a technical one. So my current focus is not the installation, etc. but to find the best way to utilize version control in a small team.

Thanks!

EDIT: Perhaps you can also answer the question by recommending several TFS-specific features that might be useful to (Oracle) DBAs and developers.

If TFS is in the picture then I assume Visual Studio is there also and Oracle creates development tools for Visual Studio (VS) that will allow you to VS with TFS. With this, you could then create Oracle Database Project in VS and maintain SQL scripts.