Update different columns on different clauses I have a database table in Postgres 9.3 with the following layout:

id   SERIAL,
col1 INT,
col2 INT

Whenever a new row is inserted, I will have to update ALL rows col1 and/or col2 in different cases but want to do it in only one (faster) query to avoid performance problems (especially because I'll have to do a lock in the table to avoid corruption).

Right now it is done with two queries:

UPDATE tablename
SET col1 = col1 + 2
WHERE col1 > $VAR

and

UPDATE tablename
SET col2 = col2 + 2
WHERE col2 >= $VAR

note, $VAR is the same in both queries. It is the col1 value of the new row.

I can't find anything related to this question in the PostgreSQL manual.

EXAMPLE DB

ID | col1 | col2
1  |   1  |  12
2  |   2  |  5
3  |   3  |  4
4  |   6  |  11
5  |   7  |  8
6  |   9  |  10

If I added a new row with id 7 and col1 5 and col2 6, the table would become

ID | col1 | col2
1  |   1  |  14
2  |   2  |  7
3  |   3  |  4
4  |   8  |  13
5  |   9  |  11
6  |   11 |  12
7  |   5  |  6

In this case, all rows changed at least one column. But in a table with a thousand of rows, most would not even change, so I believe using CASE would not be good with performance.

I would probably do something like this:

UPDATE tablename
SET col1 = col1 + CASE WHEN col1 > $VAR THEN 2 ELSE 0 END,
    col2 = col2 + CASE WHEN col2 >= $VAR THEN 2 ELSE 0 END
WHERE col1 > $VAR OR col2 >= $VAR

Make sure that col1 and col2 are indexed if you are only updating a few rows and tablename has many rows.


Could we recover Mongo records from the data files only without namespace files? I am missing a namespace file from the Mongo db path. I only have data files with number extensions (e.g. report.4,report.5). Is there any way to recover the mongodb dump from the data files?

Hmm.. You have MMAPv1 engine in use... If your mongodb version is BEFORE 3.0, you can use mongodump and --dbpath parameter to read data directly from data files.


Query on Standby redo logs - Oracle I have a oracle 11g (11.3) database running with two standby databases (at different locations) in Maximum performance mode. The platform is Linux.

Log transport parameters are:

log_archive_dest_5 string SERVICE= * * * * **ASYNC NOAFFIRM** valid_for= ... **

Correct me if i'm wrong, so ASYNC noaffirm means the log transfer method is ARCH right? Meaning the archives generated in primary gets copied to standby and then applied there. Right?

The database is primary has been generating too much archives beccause of which my DR lags behind when archives are more. Oracle MOS asks me to create standby redo to resolve the same. My question is that how will standby redo help in reducing the lag since the SRL is used so as not to lose data in case of disaster right?

Also, SRL should be used only when the parameter is LGWR right? Since i have mentioned it as ASYNC NOAFFIRM and there is no standby, the archives are only sent to DR and not redo.

IF I do not have Standby redo, then how does redo gets shipped? Does it gets shipped as archive logs from primary to standby's archive directly and gets applied?

Help me clear out my doubts.
Thanks!

NOAFFIRM means the redo transport will be acknowledged before writing the received redo to disk (standby redo logs).

  ASYNC noaffirm means the log transfer method is ARCH right?

No, it does not. Without specifying LGWR or ARCH, LGWR is the default. You can check it on the primary database by:

select archiver from v$archive_dest where dest_id = 5;

There you should see LGWR. However, specifying ARCH or LGWR became deprecated in 11g:

http://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams125.htm#CHDJDFCJ

Deprecated does not mean it will not work, so it is still possible to specify ARCH in log_archive_dest_n and revert to the way it worked in 10.2 (and what you described). But that is not how you configure MAXIMUM PERFORMANCE anymore, because of the changes, even MAXIMUM PERFORMANCE uses LGWR and standby redo logs. See below:

http://docs.oracle.com/cd/E11882_01/server.112/e41134/log_transport.htm#BABCBEGJ

  The synchronous and asynchronous redo transport modes require that a
  redo transport destination have a standby redo log.


Report on data in SQL database that does not meet certain rules I need to generate reports in a web app for rows in my tables that do not meet specific rules. I want to allow the data to be inserted into the tables, but I want to be able to flag it so that a user can return later and correct it. An example of some of the rules I'm checking against are that strings are in certain formats and lengths (like a license plate) and I'm also checking for validity on dates. I expect to have more of these rules come up in the future, so I'm looking for a scalable solution. My application is in .NET, but I've been told it would be better to do this on the DB side rather than in code. Should I be looking into implementing some type of rules engine with stored procedures?

If you're going to maintain some sort of flag indicating which rows have problems (which makes sense if you have a lot of rows, 99.9%+ of which will be fine), then I'd use a trigger to check for the conditions. That way there's one check, only done when a row is actually added or changed.


MySQL replication with 5.7 master and 5.5 slave I'm trying to setup a MySQL replica with the master on 5.7 and the slave on 5.5.

On the master I have disabled the binlog_checksum.

After import a dump of the database, change the master and start the slave, if I make a change on the master, the slave get this error:

Last_Errno: 1594
Last_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.

And if I run mysqlbinlog to the relay log, I get this error:

# mysqlbinlog /var/lib/mysql/mysql-relay-bin.000002

...

ERROR: Error in Log_event::read_log_event(): 'Sanity check failed', data_len: 61, event_type: 34
ERROR: Could not read entry at offset 270: Error in log format or read error.
DELIMITER ;
# End of log file

...

There's any chance to get this replication working?
The upgrade process can be problematic for my client and I'm trying to avoid that.

Thanks

From the source files, sql/log_event.h, event 34:

ANONYMOUS_GTID_LOG_EVENT= 34,

GTID wasn't introduced until 5.6, so there's no possible way a 5.5 server can understand this event (nor can the the version of mysqlbinlog that shipped with 5.5 understand it -- hence the error; you will probably be able to read this relay log if you use the version of mysqlbinlog that ships with 5.7).

It might seem like you could disable gtid_mode on the master, but there are two problems with this:

Even if that seems to work, it's completely unsupported, and only a matter of time until something else breaks.  The 5.7 master can emit events the pre-5.7 slave can't handle.

  when upgrading to MySQL 5.7, the slaves must be MySQL 5.7 before you can upgrade the master to 5.7. — https://dev.mysql.com/doc/refman/5.7/en/replication-upgrade.html

Any events written to the master's binlog since the upgrade are lost, from the perspective of a pre-5.7 slave, since such a server can't replicate them, so changing gtid_mode now is the equivalent of closing the barn door after the horses have escaped.  This slave's data is no longer useful, unless it is upgrade to understand the master's events and can continue replicating where it stopped.

You may still be able to salvage the slave if you upgrade it to 5.7 but I see no other meaningful alternatives.


How to password protect a field in access? I'm looking to password protect a field in my database containing confidential customer information. Is there a reasonable way of doing this in a timely manner?

Thanks

The following is solely based on Internet research so your mileage may vary.

It appears that you can password protect certain tables.  This article walks you through the process.  Essentially what you need to do is create a workgroup.  Once your workgroup is created you login into your database via a link that workgroup creation wizard produces, and you can set the permissions for all of your users.

I also found this answer on StackOverflow that says it can't be done so who knows.

The more robust solution would be what is suggested as an alternative in the StackOverflow answer above.  Just upgrade to SQL Server Express.  That should be a low cost solution that will offer a nicer, more manageable RDBMS in my opinion.  However I recognize that transition isn't always an option.


innodb_file_per_table option enabled but ibdata1 file still grows up I installed mariadb 10.2.10 with innodb_file_per_table option enabled.

Then I create table with Innodb engine and inserted 1 M row into.

Now I look and local (..data/mydbname/table_name.ibd)   file is grown, but  grown system ibdata1 file also. Why? I expected that because of innodb_file_per_table is enabled, only local .ibd should grown, but not ibdata1.

Why size of ibdata1 also grows up? What I misunderstood ?

A number of operations use ibdata1 temporarily.  The actual data for the given table will be entirely in the .ibd file.

It's rather normal for ibdata1 to be at least tens of MB, even with all tables in their .ibd files.

Do you have the following case?

innodb_file_per_table=OFF
Create and populate table1 -- this will go into ibdata1
change to innodb_file_per_table=ON
Create and populate table2 -- this will go into table2.ibd
insert into table1 -- this will still go into ibdata1

The way to move an existing table to ibd is to set ON, then do

ALTER TABLE table1 ENGINE=InnoDB;

If this does not explain your situation, please keep an eye on (1) what operations are being performed on any table, and (2) the size of ibdata1.  If it grows again, the (1) may give us some more clues.

Note especially any operations involving large tables.


Storing Address Data Model I'm revising our customer database from a one-to-one relation to a one-to-many. Specificaly we are introducing a new billing address and a shipping address. When researching I came across this post Storing a Billing Address Best Practice in Orders Table and this post Is there common street addresses database design for all addresses of the world?

Thinking about this I have some questions. I'm seeking answers so to implement the data model as provided by both answers, but currently I have doubts about implementing it this way, even though I understand from a technical point of view this is the way to do it. At the moment my main question is about the relation between the Address and CustomerAddress table.

We will have customers enter there own address. If they make a mistake and not receive anything it's their bad. This means two people can live at the same physical address, but have a slightly different printed address. One customer could for example abbeviate 'boulevard', but another doesn't have to do that. We want to implement it this way so when someone changes his address the address for different people on the same physical address doesn't change as well. So there will be no standardized address table. (If I would want that and still not want the aforementioned scenario I would my self have to organize an administrative process to create standardized addresses. This is not going to happen.) Every address record will be unique to a customer. So I think there is a one-to-one relation between Address and CustomerAddress.

The one-to-many relationship for addresses is provided, as in both data models in the provided links, through multiple records in the CustomerAddress table. The CustomerAddress table provides different purposes for each address through either a bit (data model 1 ) or a type (data model 2). Changes to these can be recorded in the CustomerAddressHistory table.

Due to the one-to-one relation between Address and CustomerAddress I thought about merging Address and CustomerAddress, but that would mean more storage when changing only a 'purpose' bit or type in the CustomerAddressHistory table.

So the main question is what would be the scenario that is the main objection to not implement a one-to-one relation between Address and CustomerAddress?

When working with addresses, one thing you may consider -- at least give it a serious look -- is using the USPS to convert the user-supplied address into the USPS standard address. So the same address, just with different abbreviations or punctuations, will converge to the same address. There are several remote apis the Post Office makes available to your app.

As for the database, yes, the literature shows that the place to put a 1-to-many FK is in the "many" tuple. You have also stated you want to maintain a history of address changes so this format makes even more sense.

However, there are many advantages to having a 1-to-many intersection table. This is similar to a many-to-many intersection table except that one side is made part of the PK to enforce the "1" side of the 1-to-many cardinality.

create table OneToMany(
  OneID    int   not null
    references UserTable( ID ),
  ManyID   int   not null
    references AddressTable( ID ),
  TypeID   char( 1 ),    -- 'H' for Home, 'W' for work, etc.
  constraint PK_OneToMany primary key( OneID, TypeID )
);

Each user has only one entry for each kind of address and even if multiple users share the same address, if one user moves to a different address, the other users are not affected.

There is a small alteration needed to maintain the history of changes. All that is needed is the addition of a datetime field.

create table OneToMany(
  OneID    int   not null
    references UserTable( ID ),
  ManyID   int   not null
    references AddressTable( ID ),
  TypeID   char( 1 ),    -- 'H' for Home, 'W' for work, etc.
  Effective datetime not null default NOW,
  constraint PK_OneToMany primary key( OneID, TypeID, Effective )
);

Now all changes are maintained and it is very easy to look back at any time in the past to see what the address was at that time. Here is an answer I posted that will refer you to more information. Mine is not the accepted answer, and I would highly recommend you take the time to become familiar with both mine and the accepted answer. Choose the one (or design your own with what you learn) that is best for your situation.


pg_basebackup: could not connect to server: fe_sendauth: no password supplied I'm trying to set up streaming replication between two Postgres servers. The master server's pg_hba.conf file is configured this way:

host    replication     ${REP_USER}     0.0.0.0/0               md5
host    ${DB_NAME}      ${DB_USER}      0.0.0.0/0               md5

And the initializer script in the slave container runs the following at startup to connect to it:

cat > ~/.pgpass <<EOS
${MASTER_PORT_5432_TCP_ADDR}:5432:${REP_USER}:${REP_PASS}
EOS
chmod 0600 ~/.pgpass

echo "Cleaning up old cluster directory"
rm -rf ${PGDATA}/*

echo "Starting base backup as replicator"
pg_basebackup -h ${MASTER_PORT_5432_TCP_ADDR} -D ${PGDATA} -U ${REP_USER} -vPw

All the env values are set. The problem is I keep getting this error:

pg_basebackup: could not connect to server: fe_sendauth: no password supplied

I'm not sure why this keeps happening. I have the password file in place with the right permissions. I also have the password in the recovery file, which doesn't get created because pg_basebackup fails all the time.

Any help would be appreciated...

Your issue is that your .pgpass file is missing the database field, and all fields are required (although you can use it * for any match). As you are using pg_basebackup (that uses the replication protocol), you need supply that as "replication":

cat > ~/.pgpass <<EOS
${MASTER_PORT_5432_TCP_ADDR}:5432:replication:${REP_USER}:${REP_PASS}
EOS
...

Using * for the database field would work too, but replication is more strict.


Watch output of MySQL query over time I'd like to run a query every few seconds and show the output in a console window.  From bash, using watch:

watch "mysql -A -pXXXXX -h host -D dbname -u username --vertical -e \"SELECT rowID FROM IDs LIMIT 1\""

With the output:

*************************** 1. row ***************************
rowID: 68434810

I'd like to not disconnect and reconnect every 2 seconds though.  Is there a better way?

There's a cheap/low-tech way of doing this:

while [ 1 ] ; do echo "select rowid from ids limit 1;" ; sleep 2; done \
 | mysql -u ...

Ctrl C to interrupt.


Configuration Persistance Design We have a well designed and normalized DB, but, there is a requisit that could cloud this design.
This image shows the design of part of the DB.

The main functionality of our application is to create some stats of the quantity of notifications each user recieves. So we query the DB by groups of users (e.g. SELECT COUNT(*) FROM Notifications INNER JOIN Users ON User.UserID = Notifications.UserID WHERE User.att = ...).

The requisit that is causing me troubles is that our application allows a user (which is different of any user stored in the table Users, since this is a monitoring application) to customize some configuration parameters, such as miliseconds to wait before looking again in the DB in order to obtain some data for charts, which number of notifications makes a user selectable (e.g. if the user has more than X notifications then it should be displayed in the chart) etc.

This configuration must be persistant, we do not want to make the user set the parameters each time he opens the application.

The problem I see is, how do I make the configuration persistant without clouding the design. Because, if you set, lets say, the X number of notifications needed, you could see that as an attribute of Notifications, but is kind of an Entity attribute. Since, is (conceptually) an attribute of Notifications but what we store there are notifications, which are oblivious of configuration attributes.

Moreover, we have more entities, with exactly the same problem. An entity that stores certain kind of data, and from the application you can configure in which ways this data is going to be treated. And that configuration must be persistant.

Therefore, my question is, what is the appropiate way to represent this configuration persistance? Should it be represented in the DB or in other file? I think that it should be stored in the DB, but maybe I am being stubborn...

I'd say it depends on how many parameters you have, and whether this list is expected to be largely static or if it's likely to change or expand.

I'm still a little fuzzy on exactly what you're doing, so I'm going to answer a slightly different question.  Let's say you're writing a query engine, searching for users that meet different requirements, and you want to store a set of predicates so it can be reused.  In a simple world, your table might look like this:

CREATE TABLE Searches
    (
    ID  INT NOT NULL PRIMARY KEY,
    CreatedBy   INT NOT NULL,  -- Prob also CreatedDate, LastModified, etc.
    FirstName   VARCHAR(100),
    LastName    VARCHAR(100),
    Sex CHAR(1),
    EMailAddress    VARCHAR(100),
    Country INT
    )

Some day you'll need to add a Region field when you start storing more precise geographic data.  Then you'll add PhoneNumber, and then BloodType, and then... eventually, you'll need something more flexible.  You need a table for searches and a table to store key-value pairs for each search:

CREATE TABLE Searches
    (
    ID  INT NOT NULL PRIMARY KEY,
    CreatedBy   INT NOT NULL
    )
CREATE TABLE SearchTerms
    (
    ID  INT NOT NULL PRIMARY KEY,
    SearchID    INT NOT NULL FOREIGN KEY REFERENCES Searches (ID),
    FieldName   VARCHAR(100) NOT NULL,
    Value   VARCHAR(100) NOT NULL,
    UNIQUE (SearchID, FieldName)
    )

The UNIQUE constraint may not be appropriate.  For example, it may be appropriate to have a search with Terms { FieldName = "FirstName", Value = "Bo%" } and { FieldName = "FirstName", Value = "ob%" }.

This more flexible approach loses type checking; searching for "NumArms = 2" now requires storing "2" in a VARCHAR field.  It also implies using dynamic SQL to construct your searches.


Select with dynamic columns I have an InvoiceHeader, InvoiceItem tables

InvoiceHeader content:

InvoiceId   InvoiceNumber   BusinessId  BusinessName    BranchId
        1               1           10       BSN INC    1
        2               2           10       BSN INC    2
        3               3           23    SAMPLE INC    1
        4               4           45   ANOTHER INC    1
        5               5           45   ANOTHER INC    3

InvoiceItem content:

InvoiceId   ItemId  ItemCode    Name            Quantity
        1        1    SNACK1    GOOD SNACKS     150
        1        2    SNACK2    SNACKERS        120
        2        1    SNACK1    GOOD SNACKS     100
        2        2    SNACK2    SNACKERS        150
        3        1    SNACK1    GOOD SNACKS     150
        3        2    SNACK2    SNACKERS        150
        3        3    SNACK3    BIG SNACKS      110
        4        1    SNACK1    GOOD SNACKS     90
        4        2    SNACK2    SNACKERS        80
        4        3    SNACK3    BIG SNACKS      120
        5        1    SNACK1    GOOD SNACKS     50
        5        2    SNACK2    SNACKERS        70
        5        3    SNACK3    BIG SNACKS      90
        5        4    SNACK4    TASTY SNACKS    70

Here is the sqlfiddle: http://sqlfiddle.com/#!18/aa5fa/6

Now the result that I want is like this:

InvoiceNumber   BusinessName    BusinnessBranch SNACK1  SNACK2  SNACK3  SNACK4  TotalItems
    1           BSN INC         Branch1         150     120     0       0       270
    2           BSN INC         Branch2         100     150     0       0       250
    3           SAMPLE INC      Branch1         150     150     110     0       410
    4           ANOTHER INC     Branch1         90      80      120     0       290
    5           ANOTHER INC     Branch3         50      70      90      70      280
                                                                                1500

As you can see the items code from the invoices are added as columns to count the items, in the last column the items total from that invoice, and the last row it shows the total items from all selected invoices.

The number of transposed columns can be more than that, can reach even more than 10.

WITH abc AS (
SELECT
   i.InvoiceId
   ,h.BusinessName
   ,h.BranchId
   ,ItemCode
   ,Quantity
FROM InvoiceItem i
INNER JOIN InvoiceHeader h ON i.InvoiceId = h.InvoiceId
)
SELECT PT.InvoiceId, PT.BusinessName, PT.BranchId,
    ISNULL(PT.SNACK1,0) as Snack1,
    ISNULL(PT.SNACK2,0) as Snack2,
    ISNULL(PT.SNACK3,0) as Snack3,
    ISNULL(PT.SNACK4,0) as Snack4
FROM abc
PIVOT( SUM(Quantity)
       FOR ItemCode in ("SNACK1","SNACK2","SNACK3","SNACK4")
       ) as PT

output:

SQLfiddle

| InvoiceId | BusinessName | BranchId | Snack1 | Snack2 | Snack3 | Snack4 |
|-----------|--------------|----------|--------|--------|--------|--------|
|         1 |      BSN INC |        1 |    150 |    120 |      0 |      0 |
|         2 |      BSN INC |        2 |    100 |    150 |      0 |      0 |
|         3 |   SAMPLE INC |        1 |    150 |    150 |    110 |      0 |
|         4 |  ANOTHER INC |        1 |     90 |     80 |    120 |      0 |
|         5 |  ANOTHER INC |        3 |     50 |     70 |     90 |     70 |


Queries are faster in SSMS 2008R2 as compared to SSMS 2016 I am connecting to a database server(MSSQL 2008R2) using SSMS 2016 and for simple queries even like "USE DBNAME", it's taking 4-5 secs. However time stat shows "CPU time = 0 ms,  elapsed time = 0 ms", not sure why?

On the other hand, If I connect the same server using SSMS 2008R2, it gets executed instantly.

I compared client statistics for both SSMS2008R2 AND 2016 but both shows almost same stats.

Why my SSMS2016 is behaving like this?

Changing the below setting and uninstalling some add ons for SSMS 2016 helped me -

HKEY_CURRENT_USER\Software\Microsoft\SQL Server Management Studio\13.0\UserFeedbackOptIn

Thanks @Kin.


Sqlite Create Table with Check column from other table I have two tables:
position
CREATE TABLE IF NOT EXISTS position(
  id_position INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  min REAL NOT NULL CHECK (min > 0),
  max REAL CHECK (max > 0),
);

min, max links to salary
employee
CREATE TABLE IF NOT EXISTS employee (
  id_employee INTEGER PRIMARY KEY AUTOINCREMENT,
  id_position INTEGER NOT NULL,
  name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  data_of_birth TEXT NOT NULL CHECK (DATE(data_of_birth) IS NOT NULL AND data_of_birth < DATE('now','localtime')),
  salary REAL NOT NULL CHECK (salary >= position.min AND salary <= position.max),
  FOREIGN KEY(id_stanowisko) REFERENCES stanowisko(id_stanowisko)
);

But check in salary column doesn't work. I have an error no such column: position.min.
Any solution?

Looking up a value from another table would require a subquery, but the documentation says:

  The expression of a CHECK constraint may not contain a subquery.

There is no such restriction on a trigger's WHERE clause:

CREATE TRIGGER employee_salary_check
BEFORE INSERT ON employee
WHEN NEW.salary < (SELECT min FROM position WHERE id_position = NEW.id_position)
  OR NEW.salary > (SELECT max FROM position WHERE id_position = NEW.id_position)
BEGIN
  SELECT RAISE(FAIL, "invalid salary");
END;


Creating a table if it doesn't exist (old versions of PostgreSQL) In recent versions of postgreSQL one can use the syntax:

create temp table if not exist my_table as
...

to create a temporary table if it doesn't exist yet.

However, the clause if not exist is a relatively recent feature.

I am working against a database that uses PostgreSQL version 8.2.15. How can I create a temporary table if it doesn't exist yet?

The actual solution very much depends on your exact situation.  If you can use a pl/pgsql function, you can check first for the existence of the table:

IF NOT EXISTS (SELECT 1 FROM pg_class WHERE relname = 'my_table')
THEN
    CREATE TEMPORARY TABLE my_table ...
END IF;

You could also try to match the schema name (which will be something like pg_temp*) by joining the pg_namespace table to pg_class.  Or use the pg_tables view, which can be a bit easier.


Error on mongos server Firstly,I am new to mongodb.  when we executed the below command on mongos server.

/usr/bin/mongos --configdb X.X.X.1:20001,X.X.X.2:20002,X.X.X.3:20003 --port 27017 --chunkSize 1

we are getting the below error:

2014-12-01T14:40:24.947+0300 [Balancer] SyncClusterConnection connecting to [X.X.X.1:20001]
2014-12-01T14:40:24.947+0300 [Balancer] SyncClusterConnection connecting to [X.X.X.2:20002]
2014-12-01T14:40:24.948+0300 [Balancer] SyncClusterConnection connecting to [X.X.X.3:20003]
2014-12-01T14:40:24.957+0300 [Balancer] scoped connection to X.X.X.1:20001,X.X.X.2:20002,X.X.X.3:20003 not being returned to the pool
2014-12-01T14:40:24.957+0300 [Balancer] caught exception while doing balance: error checking clock skew of cluster X.X.X.1:20001,X.X.X.2:20002,X.X.X.3:20003 :: caused by :: 13650 clock skew of the cluster X.X.X.1:20001,X.X.X.2:20002,X.X.X.3:20003 is too far out of bounds to allow distributed locking.

Kindly let us know what it meant by. weather it is  properly connected with config server or not?

Check your mongos, mongod and config servers NTP settings. The clocks on every member of the cluster must return the same datetime. Use the "date" command on linux to verify it.


Multiple instance of Update Query is causing very high Lck_M_U wait thus high CPU My investigation found the following query is causing high Lck_M_U wait. I noticed there are multiple instance of this query running obviously competing for the same resource. I wonder if there is anything I can do with this query (listed below) to minimize the wait?

UPDATE LBMebrs
                SET DelayedPoints = 0, Points = ISNULL(computed.Points, 0)
                FROM LBMebrs Le1
                LEFT JOIN
                (
                    SELECT lm.UserId, SUM(ua.Points) Points
                    FROM LBMebrs lm
                    INNER JOIN LBrds l ON l.Id = lm.LBrdsId
                    INNER JOIN UserActivities ua ON ua.UserId = lm.UserId
                    WHERE l.Id = @LBrdsId AND ua.Pub BETWEEN l.StartDate AND l.EndDate
                    GROUP BY lm.UserId
                ) computed ON computed.UserId = Le1.UserId
                WHERE Le1.LBrdsId = @LBrdsId

Executions Plan:

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

Indexes defined on LBMebrs table:

Indexes defined on UserActivities Table

Indexes defined on LBrs Table

Multiple update queries running on instance (only once instance on whole server)

Waits on Server:

causing very high Lck_M_U wait thus high CPU

The locks should not really be causing high CPU use - a thread that is waiting for locks to clear is doing just that: waiting and not consuming processing resource. So waiting for locks a lot is a symptom of the statements taking a long time to run, not a cause of it.

I suspect that the tables are not well indexed for the query that is working out what to update, particularly the derived table sub-query. If each run of this is scanning a large amount of data instead of being able to seek it more efficiently, then that would explain the high CPU cost (and the waiting - each call is waiting for the last to finish that scanning). As suggested in the comments already, posting a sample query plan would help here, as would a list of indexes defined on the relevant tables.

Looking at the plan posted after writing the above, I still suspect that better index options will help, add to the question the indexes that are currently defined.

It would also help to describe what the update is trying to achieve.

Is it right that it is updating thousands of rows each time? An estimated 55,979 rows in this instance. If most of the time the values will not change you can at least remove a lot of the page write activity and the need to attain update locks by adding AND (DelayedPoints <> 0 OR Points <> ISNULL(computed.Points, 0)) to the final WHERE, or if DelayedPoints may be NULL in some cases AND (DelayedPoints <> 0 OR DelayedPoints IS NULL OR Points <> ISNULL(computed.Points, 0)). The reason this will help is that SQL Server will perform the updates without checking that they are not necessary, so adding your own check will save it a pile of log and data page updates most times the statement is run (obviously don't do this if you have extra logic such in triggers that is relying on being triggered by these no-op updates).


SSRS not sending scheduled email to specific contact I have a specific contact for my SSRS subscriptions that does not receive any mail from SSRS.
her email is an organization email, everyone else including me are getting emails but only that specific user doesn't.

I checked ssrs logs and i see it was sent successfully:

  notification!WindowsService_0!1aa0!12/05/2019-14:15:23::
   i INFO: Notification af94f76f-d236-4ffb-b4e7-8450d206f654 completed.
    Success: True, Status: Mail sent to contact1@organization.com;contact2@organization.com;,
  DeliveryExtension: Report Server Email, Report: Notables Report (Euro),
   Attempt 0

This is what i tried so far:

recreating the subscription
recreating reports
creating a simple report with no attached files
checking all her mail folders and junk mail
restarting SSRS services
IT team checked the mail server shes not receiving the mails they say it doesn't even go through the mailing server.
She does get regular mails
sending mail from the same server via SQL works.
when sending email to a group she belongs too she still doesn't get mail.

it happens with all reports in that server
Any suggestions?

Thanks

This question shouldn't be posted here even if it is related to SSRS.

IMHO and according the comments it seems that the SMTP service used by SSRS is miss-configured.

Take a deep look at SPF record in your DNS to ensure it belongs to your domain. Some mail servers have an strong spam-filter activated to ensure all received mails are good and belonging to the mail sender domain. Among other spam-filters.

You can use some (online) tools to check your mail server configuration.


Only one non-nullable value across 3 columns I have 3 "Nullable" columns.

I am looking for a way to restrain it so that:

One of the three has value, the other two columns must be null
All three cannot be null

Is it possible to do this?

I tried this:

CREATE UNIQUE INDEX alpha ON contacts ("a")
WHERE "b" IS NULL AND "c" IS NULL;

CREATE UNIQUE INDEX beta ON contacts ("b")
WHERE "a" IS NULL AND "c" IS NULL;

CREATE UNIQUE INDEX gamma ON contacts ("c")
WHERE "a" IS NULL AND "b" IS NULL;

That's not what indexes are for, you need a check constraint for that,

For the "at most one can be not null" you can use a little trick: casting a boolean expression to an integer yields 0 or 1. If you add up the values for a is not null condition, the rule "at most one can be not null" translates to "the sum of those must always be 1":

alter table contacts add constraint only_one_not_null
   check (   (a is not null)::int
           + (b is not null)::int
           + (c is not null)::int = 1);

The above can be simplified by using the num_nonnulls() function:

alter table contacts add constraint only_one_not_null
   check (num_nonnulls(a,b,c) = 1);

This also covers the "not all three can be null" requirement.


Locations, single primary plus multi-secondary I'm looking for suggestions/best practices for implementation of a location management DB.

Locations have a primary access point and potentially infinite secondary access points. They must have one and only one primary access point. These access points may be referenced differently by other locations. (ie a secondary access point for one location may be the primary of another).
In the interests of DRY, I would like to have one table of "Locations" and another table of "AccessPoints". I'm struggling with intermediate tables/rules to enforce this behaviour.

Sounds pretty straightforward, so perhaps there is something I don't understand?

CREATE TABLE LOCATIONS
( location_key ... primary key not null
, location attribute_1 ... not null
, ...
, location attribute_n ... not null );

CREATE TABLE ACCESS_POINTS
( access_point_key ... not null
, location_key ... not null
, access_point attribute_1 ... not null
, ...
, access_point attribute_m ... not null
,    constraint ... primary key (access_point_key, location_key)
,    constraint ... foreign key (location_key)
                    references LOCATIONS (location_key)
);

A assume here that several locations can share the same access_point.


SQL Server - update all data in all tables from backup I need to update data (all tables) on our QA environment by replacing it with our latest Prod Database data (Make QA more actual). As I understand, by default if I do it with SSMS it will also replace Security options and Roles which I want to avoid:

Our Prod and QA DBs are on different servers so service accounts
regulating activity on servers are also different
AD groups are different, so if I just restore DB from Prod on QA, I
will give access to QA to some users
Any other unexpected issue

Is it possible somehow to replace data in all tables (Tables on QA and Prod are identical, difference only in amount of data) from DB backup file and do not touch anything else?

As I understand through Google my only variant is to restore Prod DB on QA server with different name, truncate tables on QA and use SQL MS Wizard but I hope that there are other options.

3rd party compare tools is not an option because server owner is not allow to use it. Only possible options is something free from Microsoft. It's need to be done just one time.

Create a table on your QA environment inside a service database or master. Something like below:

CREATE TABLE [dbo].[SQLAccess](
    [DBName] [sysname] NOT NULL,
    [LoginName] [sysname] NOT NULL,
    [UserName] [sysname] NOT NULL
)

Fill the table with users and logins that have to be remapped:

INSERT SQLAccess (DBName, LoginName, UserName)
VALUES
('YourRestoredDBName', 'TestServiceLogin', 'ProdServiceLogin'),
('YourRestoredDBName', 'TestDomain\ADUser', 'ProdDomain\ADUser')
-- and so on

After restoring:

USE [YourRestoredDBName];

DECLARE @SqlStatement NVARCHAR(max)
    , @LoginName    sysname
    , @UserName     sysname

DECLARE MyCursor Cursor
FOR
    SELECT LoginName
        , UserName
    FROM YourServiceDBName.dbo.SQLAccess
    WHERE DBName = 'YourRestoredDBName'

OPEN MyCursor

FETCH NEXT FROM MyCursor INTO @LoginName, @UserName

WHILE @@FETCH_STATUS = 0 BEGIN

    SELECT @SqlStatement = 'ALTER USER [' + @UserName + '] WITH LOGIN = [' + @LoginName + ']'

    EXEC sp_executesql @SqlStatement

    FETCH NEXT FROM MyCursor INTO @LoginName, @UserName
END

CLOSE MyCursor
DEALLOCATE MyCursor

As a result you will get a database where your test logins have access to test data the same way as prod logins had on the prod environment.

Of course, it is just a concept, feel free to expand the solution with adding logging, error handling, adding additional roles or permissions for some users, etr.


SQL Server Express Freezes for a couple minutes I have SQL Server Express on an AWS micro instance. These instances have only 615MB of RAM. For the most part it performs fine. My application can make up to about 15 db connections.  However, every once in a while it just freezes for a couple of minutes.. my own application logs show all kinds of query time-outs, new db connection attempts failing because of an incorrect pwd (even though I am sure the attempt is using the correct pwd). But after a couple minutes everything turns out fine again.

I have checked eventvwr.msc logs but it doesn't reveal anything. I know there are not backups running at the time. And I have inspected SQL Server logs from SQL Server Management Studio but these too don't reveal anything.

Any ideas? Am I pushing the limits of SQL Server? I have read before the Express Editions run fine until certain connection thresholds get met. Could the number of db connections be an issue?

There is couple of important point missed

Please check whether Auto Close option is set to true for database. I have seen lots of similar issue where Auto close option was culprit.

Is OS resifing on Virtual machine or Physical ?. If its is virtual look for any VM snapshot backup happeing this might also cause freezing.

Other thing can be, do you have backup taken for  database using third party tool/windows native backup  if so these tools freeze data files for couple of sec to sync metadata and take consistent backup.And you can see VIRTUAL_DEVICE in errorlog when you specifically look ta backup information in it.

Hope this helps


In replication A->B->C, can A be binlog_format=STATEMENT and B be binlog_format=ROW? I have replication setup between an on-prem MySQL 5.6 server (A) to another on-prem MySQL 5.6 server (B).  I was setting up an AWS DMS instance (C) that I wanted to migrate and then replicate.  It told me that I had, then, to have my binlog_format set to ROW-based replication.

Is this feasible?  Can I expect changes on A to replicate correctly to C?

STATEMENT → ROW is absolutely a valid configuration.

The thing you've probably overlooked is that Server B needs log_slave_updates enabled, or it won't replicate anything from A to C.

But to address the question at hand, consider the following table, which describes the comparison between configured @@binlog_format and what is actually written to the server's binlog.  Assume A is master, B is slave of A,  no changes are made directly to B, and of course log_slave_updates is enabled on B:

A binlog_format | A actually writes | B binlog_format | B actually writes
-------------------------------------------------------------------------
STATEMENT         STATEMENT           STATEMENT         STATEMENT
STATEMENT         STATEMENT           MIXED             MIXED
STATEMENT         STATEMENT           ROW               ROW

MIXED             MIXED               STATEMENT         TOTALLY FAILS EVENTUALLY
MIXED             MIXED               MIXED             MIXED
MIXED             MIXED               ROW               ROW

ROW               ROW                 STATEMENT         TOTALLY FAILS VERY QUICKLY
ROW               ROW                 MIXED             ROW
ROW               ROW                 ROW               ROW

What's going on here?

When B writes to its binary log, it writes an entry to replicate the change it made.  It writes these entries in the format its local optimizer determines should be used.  When STATEMENT or ROW mode is in effect, that's the only choice, so incoming STATEMENT becomes outgoing ROW, in your case.  Server B easily translates because it executes the incoming event and then simply logs which rows it inserted/updated/deleted in its local data, and the values of the columns in those rows.

  Each MySQL Server can set its own and only its own binary logging format (true whether binlog_format is set with global or session scope). This means that changing the logging format on a replication master does not cause a slave to change its logging format to match. [...]

  Changing the binary logging format on the master while replication is ongoing, or without also changing it on the slave can cause replication to fail with errors such as Error executing row event: 'Cannot execute statement: impossible to write to binary log since statement is in row format and BINLOG_FORMAT = STATEMENT.'

  http://dev.mysql.com/doc/refman/5.7/en/binary-log-setting.html

That's what I was referring to, above, with TOTALLY FAILS.  The only invalid configuration is for a server to be in STATEMENT mode while at least one upstream master is in ROW or MIXED.

Why?  Every possible query that can be represented in STATEMENT format can always be subsequently represented in ROW format, because ROW format is absolutely deterministic.  Conversely, no query replicated in ROW format can ever be subsequently replicated in STATEMENT format.

Note that MIXED format simply gives the local optimizer the option on a query by query basis of how to replicate -- STATEMENT or ROW -- but if the incoming replication event is ROW the only outgoing choice is also ROW.

Also, STATEMENT mode is seriously terrible.  Get away from it as soon as you can.  Before then, it's vitally critical to verify that the data on B is identical to the data on A in every way, because the data on B can slip out of sync much easier and go undetected when A is in STATEMENT mode.


How to repair backup of .mdf file database? One of our clients gets the following error when trying to backup a database:

  System.Data.SqlClient.SqlException: SQL Server detected a logical
  consistency-based I/O error:  incorrect checksum (expected:
  0x121db60d; actual: 0x521db60d).  It occurred during a read of page
  (1:1632) in database ID 4 at offset 0x00000000cc0000 in file
  'C:\Program Files (x86)\Microsoft SQL
  Server\MSSQL.1\MSSQL\DATA\MSDBData.mdf'.  Additional messages in the
  SQL Server error log or system event log may provide more detail.
  This is a severe error condition that threatens database integrity and
  must be corrected immediately.  Complete a full database consistency
  check (DBCC CHECKDB).  This error can be caused by many factors; for
  more information, see SQL Server Books Online. Could not insert a
  backup or restore history/detail record in the msdb database.  This
  may indicate a problem with the msdb database.  The backup/restore
  operation was still successful.

I tried to perform checkdb, checkdb repair_rebuild as well as checkdb REPAIR_ALLOW_DATA_LOSS but none of these corrected the issues is the MSDB database.

My question is: can I just grab a clean MSDB database from another instance to recover corrupt one or do I really have to uninstall/reinstall SQL Server?

You're getting this while trying to backup any database?

msdb is used for a bunch of things and you'll want to make sure it's okay. But critically, it sounds like your corruption is in a table which is used for backups, and this is a good thing, because you don't necessarily need that data.

Start by scripting out all your SQL Agent jobs, exporting all your SSIS packages that you've put in msdb, and anything else you can think of that uses msdb.

Now do some investigation to find out what that corrupt page is used for....

...but ultimately, your best bet is likely to be to stop the instance for a moment, pull out the msdb MDF and LDF files (so you have copies), restore from a backup from a different database (and check again for corruption), and then redeploy all your maintenance jobs, agent jobs, SSIS packages, and anything else you can think of.

You might be able to swap your MDF and LDF files onto another machine and extract extra information that you may have missed, but this should at least get you going again.


multiple entities that share a table I have this situation where i have two different entities, Researcher and Lab Manager and i want for a both to have a profile.
When a user logs in he could be one of the two entities, and searching in both tables for a username can be inefficient, so i think both of theme sharing a table called profile would be the way to go.
here is my attempt

but i realized two issues with it

instead of a researcher or manager have a profile, a profile have to have both a researcher and a manager as owners in the same time
when i query for a user how can i know his type without adding an attribute of type

any ideas would be appreciated

Allow Field to be NULL and every user that has no Field is researcher.
If field isn't enough there is also TeamId which can be -1 for magaers.
Or a combination of both
But to be honest a Type field make a lot if sense as Tinyint, and you  are felible for future as there must be student helpers and so on


Unable to connect to Listener after rebuild I dropped an AG with

DROP AVAILABILITY GROUP [MyDb]

it had a listener.

I left the listener open in the object Explorer of SSMS 17.3.  While the AG and Listener were down. I hit refresh on the listener a couple times in SSMS. (because I have OCD and had to make sure it was gone)

I recreated the AG with the same listener and when I hit refresh it would not connect.  I get the message below.  I tried the following and was not able to connect, receiving the same error.

Disconnect the listener in 17.3 and try to create a fresh connection
Open SSMS 16 (13.0.15900.1) and try and connect
Close and reopen both SSMS sessions.
Try adding MultiSubnetFailover=True to the Additional Connection Parameters tab of the connect popup (this a multi-subnet listener)

I was able to connect using the fully qualified domain name 'MyListener.fully.domain.net', but was still unable to connect with just 'MyListener'

A coworker was able to connect to the listener without issue, using both 'MyListener' and 'MyListener.fully.domain.net'

I rebooted my Windows 10 PC, and after reopening SSMS I was able to connect with just the listener name 'MyListener'.

I upgraded SSMS to 17.4 and the same thing happens.

What is happening here? How can clear the issue without rebooting my PC?

  TITLE: Connect to Server

  Cannot connect to MyListener.

  ADDITIONAL INFORMATION:

  A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)

  For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=53&LinkId=20476

  The network path was not found

  BUTTONS:

  OK

Note in all cases I created the AG and added the listener using a variation of the T-SQL from https://www.brentozar.com/archive/2016/06/sql-server-2016-availability-groups-direct-seeding/  The AG is on an instance running SQL Server 2016 RTM, the database is AdventureWorks2014.

It worked for me after setting Network Connection to TCP and trusting server certificate in the connection properties.


Calculating interval in days based on average velocity I have an orders table and need to calculate when there is a change in the average count across time. This could be either an increase or decrease.

For instance, if there is usually 1 order every two days, I need to detect if that suddenly increased to 3 orders every day or decreased to 1 order every 3 days.

Example table structure and data:

CREATE TABLE "public"."orders" (
    "id" SERIAL,
    "created_at" timestamp NOT NULL,
    PRIMARY KEY ("id")
);

INSERT INTO "orders" ("created_at")
VALUES
(now() - interval '1 hours'),
(now() - interval '6 hours'),
(now() - interval '12 hours'),
(now() - interval '2 days'),
(now() - interval '4 days'),
(now() - interval '6 days'),
(now() - interval '9 days');

In the data set above, supposed I wanted to detect that over the course of the last week there is usually only 1 order every 2 days, but over the last 24 hours that pattern has been violated with 3 orders in one day.

Desired result:

Previous 7 day's average orders per day (not including last 24 hours): 0.43
Previous 7 day's average order velocity (not including last 24 hours): 1 order every 2 days
Last 24 hour's orders: 3

Alternate scenario:

INSERT INTO "orders" ("created_at")
VALUES
(now() - interval '12 hours'),
(now() - interval '2 days'),
(now() - interval '2 days'),
(now() - interval '2 days'),
(now() - interval '3 days'),
(now() - interval '3 days'),
(now() - interval '3 days'),
(now() - interval '4 days'),
(now() - interval '4 days'),
(now() - interval '4 days'),
(now() - interval '5 days'),
(now() - interval '5 days'),
(now() - interval '5 days'),
(now() - interval '6 days'),
(now() - interval '6 days'),
(now() - interval '6 days'),
(now() - interval '7 days'),
(now() - interval '7 days'),
(now() - interval '7 days');

Alternatively, suppose I have the above data set. Normally there are approximately 3 orders every day for the past week, but over the last 24 hours there was only 1 order.

Desired result:

Previous 7 day's average orders (not including last 24 hours): 2.57
Previous 7 day's average order velocity (not including last 24 hours): 3 orders every 1 days
Last 24 hour's orders: 1

How would I do this?

PostgreSQL 9.6

First count orders grouped by day of last 7 days, and then calculate averages

--= Count orders grouping by date
WITH cn AS
(
    SELECT created_at::date, COUNT(*) num_orders
    FROM   orders
    WHERE  created_at <  (now() - interval '1 days')
    AND    created_at >= (now() - interval '8 days')
    GROUP BY created_at::date
)
SELECT to_char(AVG(num_orders), '9.99') AS "AvgOrderVelocity",
       to_char(SUM(num_orders) / 7::decimal(18,2), '0.99')  AS "AvgLast7Days",
       (SELECT COUNT(*)
        FROM   orders
        WHERE  created_at > (now() - interval '1 days')) "Last24h"
FROM   cn;

Using first sample data:

Orders grouped by date:

created_at | num_orders
:--------- | ---------:
2017-05-05 |          1
2017-05-07 |          1
2017-05-09 |          1

AvgOrderVelocity | AvgLast7Days | Last24h
:--------------- | :----------- | ------:
 1.00            |  0.43        |       3

dbfiddle here

Using second sample data:

Orders grouped by date:

created_at | num_orders
:--------- | ---------:
2017-05-04 |          3
2017-05-05 |          3
2017-05-06 |          3
2017-05-07 |          3
2017-05-08 |          3
2017-05-09 |          3

AvgOrderVelocity | AvgLast7Days | Last24h
:--------------- | :----------- | ------:
 3.00            |  2.57        |       1

dbfiddle here


EXECUTE AS does not appear to be changing security context We have legacy code that still makes use of xp_cmdshell calls.  When we migrated to SQL Server 2008, we created a stored procedure that used code after the following pattern:

EXECUTE AS 'DOMAIN\ID2'
EXEC master..xp_cmdshell @command
REVERT

When I pass in WHOAMI as the command, what it shows is not the 'DOMAIN\ID2', but rather the ID of the service account that SQL Server is running under (i.e. 'DOMAIN\ID1').  Should it not be returning 'DOMAIN\ID2' as it's supposedly running unders a different security context?  If so, any idea why it would not be changing context?  This process was created by another dev who is now long gone and I'm not really familiar with security and impersonation as I probably should be.

Wouldn't be nice to find a tool that allows you to impersonate to Windows a user without knowing the password? Every intern could impersonate the CFO and withdraw millions from the bank... The possibilities!

No, EXECUTE AS is changing the security context exclusively with regard to access SQL Server objects (tables, procedures etc). By no means will (or even can, for the matter) be able to change the real NT execution context for the process or for processes launched by SQL Server.

Update

I obviously forgot to add the actual useful piece of information: there is a way to make xp_cmdshell use a specific credential, provided you do create such a credential in SQL Server:

create credential [##xp_cmdshell_proxy_account##]
   with identity = 'DOMAIN\ID2',
   secret = '<ID2 password>';

The key difference is that you are very explicitly giving the password here to SQL Server. Now SQL Server will use this credential 'proxy' whenever faced with calling xp_cmdshell from a SQL login context or from a EXECUTE AS context.

You can also associate a credential with a login for 'generic' access to resources outside SQL Server (eg. accessing a network share):

  A credential is a record that contains the authentication information
  that is required to connect to a resource outside SQL Server. Most
  credentials include a Windows user and password.


Troubles with MySQL open_files_limit Ubuntu 16.04.2. MySQL 5.7.17.

During peek hours, my PHP sites suffer fatal error trying to connect to MySQL. Apache says

  Error 24 Out of resources when opening file

Ok makes sense, I get lots of traffic. I read up, and seems my open_files_limit var of 1,024 is too small (it really is seeing 5000 is default).

So after some reading, I ran mysql --verbose --help | less to find which config files I should go to. It gave me:

  Default options are read from the following files in the given order:
  /etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

But, /etc/my.cnf and ~/.my.cnf didn't exist.

/etc/mysql/my.cnf did, and it looks exactly like this:

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

When I appended open_files_limit = 2084 to the above /etc/mysql/my.cnf file. I was unable to sudo service mysql start MySQL. I had to remove the line, then I could start MySQL.

The only other MySQL configuration file I could find was /etc/mysql/mysql.conf.d/mysqld.cnf and I tried adding the open_files_limit line there and restarted, it restarted but the variable didn't chang.

What am I doing wrong? What do I do to increase my open_files_limit?

UPDATE

Seems the place to change the value was in /lib/systemd/system/mysql.service

Had to add LimitNOFILE=infinity save, then systemctl daemon-reload

There's lots of readings on the reasonings for this for some setups, and reasons not to use infinity.

It depends on your db architecture. If you have many tables in separate files in the MyISAM, ARIA, or Innodb with files per table you will want a higher open file limit. If you have everything in the Innodb / xtradb in a single tablespace then upping your file limits will not benefit.

Check current system limits

ulimit -a

Temporary limit increase

ulimit -n 65535

Permanent update /etc/security/limits.conf

The mysql open file limit can't exceed system limits.

* soft nofile 65535
* hard nofile 65535


MySQL: How to find MySQL 5.1 (with built-in InnoDB) or MySQL 5.1 (with InnoDB-plugin)? My DB server has MySQL 5.1.58-community-log and I am planning to upgrade it to MySQL 5.6 with specific purpose of improving my system performance and removing the bottleneck issue.
So, I have gone through mulitple posts about the differences between MySQL 5.1 and MySQL 5.6

Below article indicates that MySQL 5.1 has better performance than 5.6
https://serverfault.com/questions/362388/mysql-5-1-vs-mysql-5-5-5-1-twice-as-fast/362706#362706

http://www.mysqlperformanceblog.com/2011/10/10/mysql-versions-shootout/

There are two versions of MySQL 5.1 as below:

MySQL 5.1 with built-in InnoDB
MySQL 5.1 with InnoDB-plugin

What are the differences between these?
How will I know that My server has built-in InnoDB or InnoDB-plugin?
How to change from built-in InnoDB version to InnoDB-plugin version and vice-versa?
Should I not leave my plan to upgrade MySQL?

If it's a plugin then you'll see variable innodb_version:

mysql> show variables like 'innodb_version';
+----------------+----------------+
| Variable_name  | Value          |
+----------------+----------------+
| innodb_version | 5.5.35-rel33.0 |
+----------------+----------------+

InnoDB as a plugin had more features and was more actively developed. That happened due to historical reasons (at that time MySQL was owned by Sun and InnoDB was owned by Oracle).

I wouldn't recommend using 5.1 as it's EOL. 5.1 isn't any faster than 5.5 or 5.6. Why would it be if the huge team in Oracle works on InnoDB  and 5.5 and 5.6 are great releases. It reminds me common myth "MyISAM is faster than InnoDB".


SQL Server AlwaysOn from multiple origins to a single secondary replica server I started working in a small company which have three separate databases, for three different systems. They are SQL Server 2008, 2012 and 2014, installed in different servers.

We'd like to set up an AlwaysOn AG to replicate into another offsite server (one server only). Is it possible to create 3 availability groups from different servers and SQL versions into a single SQL Server 2014 instance?

I think I'll have troubles setting up the WSFC from multiple origins to a single server.

Is there any other way to fix this issue, since we only have a single offsite server to replicate our systems?

It is possible, provided that:

all servers run the same post 2012 version, preferably 2014 and all patched to the same SP/CU.
all servers are licensed to Enterprise Edition
all servers are in the same domain, or a within domains that trust each other

Perhaps you are interested in Transactional Replication instead? This would support a heterogeneous configuration (2008, 2012, 2014), crossing domain boundaries via mirrored NT accounts, does not require Enterprise licensing everywhere. You need to explicitly set up the 'published articles' (ie. what tables get replicated).


SQL Server: Simple database restore with overwrite What I'm trying to do is backup one of our production databases and restore them as our dev database (overwriting what's been there).

That's how I back up:

backup database [authfx] to disk = N'f:\db_backups\authfx\authfx-latest.bak'
  with
    noformat,
    init,
    name = N'authfx Latest Full Database Backup',
    skip,
    norewind,
    nounload,
    stats = 1;

Now when I try to restore other database using

restore database [dev-authfx] from disk = N'f:\db_backups\authfx\authfx-latest.bak'
  with
    file = 1,
    nounload,
    replace,
    stats = 1;

it barks all over the place:

Msg 1834, Level 16, State 1, Line 10
The file 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\authfx.mdf' cannot be overwritten.  It is being used by database 'authfx'.
Msg 3156, Level 16, State 4, Line 10
File 'authfx' cannot be restored to 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\authfx.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 1834, Level 16, State 1, Line 10
The file 'F:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\authfx_log.ldf' cannot be overwritten.  It is being used by database 'authfx'.
Msg 3156, Level 16, State 4, Line 10
File 'authfx_log' cannot be restored to 'F:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\authfx_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 10
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 10
RESTORE DATABASE is terminating abnormally.

I understand that I need to add with move and specify where original data and log files go for the dev database, but is there any way I can omit this and just say: restore this database from this other backup, do not touch anything else and overwrite what's been here previously.

Unfortunately not. SQL Server will always attempt to restore to exactly what is recorded in the backup file, unless you explicitly specify otherwise (with MOVE in this case).

The REPLACE option only works if you're replacing files owned by the database you're restoring (which makes sense, You may have said you're fine killing DB_A by restoring over it, but SQL doesn't really know how you feel about killing DB_B as well).

If this is something you'll be doing regularly then, then write a small sql script with everything stated, save it and use that every time you want to do the restore. This is how I do our overnight restores for dev/UAT/Training from prod.


MySql - Ranking so it only needs to be done once By reading around, this seems to be a reasonable way of ranking people.

The table:

UserId (int primary key)
Name (varchar(100))
Score1 (int highest scores rank 1)
Score2 (int, if Score1s are equal, the person with the highest Score2 trumps all other equal Score1s)
Rank (int this is what I want to fill)

The Rank will only change about once a day. However, the page will likely be viewed maybe 1000 times a day. So instead of getting the database to calculate the rank for each of the 1000 visits, I thought it would be best to just fill the 'Rank' column once.

How can I include an UPDATE to fill the Rank column, using my chosen way to rank people. (Note if two people are ranked 5, then the next rank is 7.)

SELECT UserId, Name, Score, Rank FROM
    (SELECT UserId, Name, Score1, Score2
        @curRank := IF(@prevScore1 = Score1 && @prevScore2 = Score2, @curRank, @incRank) AS Rank,
        @incRank := @incRank + 1,
        @prevScore1 := Score1,
        @prevScore2 := Score2
       FROM players p,
       (SELECT @curRank :=0, @prevScore1 := NULL, @prevScore2 := NULL, @incRank := 1) r
       ORDER BY Score1 ?????
    )
s

The Questions are:

How do I UPDATE Rank in the table within this SQL statement?
What do I do in the ????? ORDER BY statement to make sure it orders by Score1 first then when these are equal it orders by Score2 second?

You're almost there: Add order by Score1, score2, and enclose it with an update statement:

update players,
    (SELECT UserId, Name, Score1, score2, Rank FROM
        (SELECT userid, Name, Score1, Score2,
        @curRank := IF(@prevScore1 = Score1 && @prevScore2 = Score2, @curRank, @incRank) AS Rank,
        @incRank := @incRank + 1,
        @prevScore1 := Score1,
        @prevScore2 := Score2
        FROM players p,
        (SELECT @curRank :=0, @prevScore1 := NULL, @prevScore2 := NULL, @incRank := 1) r
        ORDER BY Score1, score2     )x) s
    set players.rank=s.rank where players.userid=s.userid;

This is the final result on a sample table:

mysql> select * from players order by score1, score2;;
+--------+--------+--------+--------+------+
| userid | name   | score1 | score2 | rank |
+--------+--------+--------+--------+------+
|      2 | Nathan |     16 |     13 |    1 |
|      3 | Steve  |     22 |     14 |    2 |
|      4 | James  |     22 |     14 |    2 |
|      1 | John   |     95 |     12 |    4 |
|      5 | Alain  |    168 |     16 |    5 |
+--------+--------+--------+--------+------+
5 rows in set (0.00 sec)

I'll upvote the question because it has the big part of the solution.


Better DB design to have a SUM column, or to constantly add up all relationship items per query? I have a database with an invoices table and invoice_items table. For simplicity, let's just say theinvoices table has the following structure:

| id | name | created_at |

The invoice_items has this structure:

| id | invoice_id | tital | created_at |

Pretty simple, a parent invoice table and the line items go into invoice_items with a relationship setup.

What I'm wondering is, how effective it is to has a sum column on the invoices table? Would it be bad to have all invoice_items per invoice added up on the fly to show the invoice total amount? Or would it be better to have a SUM column which is static, and is pulled automatically when invoices are listed - then updated specifically whenever an invoice changes?

Just trying to weigh up the pro's and con's - because whenever an invoice changes, the SUM column needs to be updated whereas if it was just always calculated based on the line items dynamically, would that be a drastic design flaw or be acceptable?

As ever - it depends.

If you have a blisteringly quick DBMS or only a handful of items in each invoice, then totalling them up on the fly would be the cleanest (and, therefore, "best") option.

The "Ivory-Tower Database Purists" would say that you should never store anything that you can derive dynamically (citing Date-of-Birth vs Age).  Realistically, it depends on just how long it takes to do that derivation!

If you have an "ordinary" database, or hundreds or even thousands of items in each invoice and you need that summarised total frequently, then maintaining the summary field at the Invoice level might be the better solution.  [Per-row] Triggers are the way to achieve this, so that no update to any item gets missed.

All that said, if I were in your position, I'd go with on-the-fly totalling ...   at least until the application's performance began to suffer, and then I'd think about "optimising" this aspect of it.


Linked server to DB2 table locks I have a problem with a SQL linked server connected to a DB2 database on an AS400 IBM iSeries.

I configured an ODBC connection from the ODBC Data Source utility. Once configured the ODBC, i created the LinkedServer and start using it for querying the DB2 database using  SELECT * FROM OPENQUERY([LINKED_SERVER], 'SELECT * FROM [TABLE] FOR READ ONLY').

Once the query is executed, the DB2 table is locked and when the query ends the lock is not released. I temporarily solved the issue setting the ODBC connection type as read only and not as default.

Also all the DB2 tables that are used by the linked server are locked at every query even if they are not involved in the query.

Can someone help me figuring out how to not lock all the tables and set the linked server to read/write and not in read only mode.
Like in the image, i executed a query only on SHPAV20F but locks are taken also for TTB03ACF and TTB0301P that are not involved in the query.

ODBC settings  for commit mode is *CHG, PREFETCH for query data is enabled and LAZYCLOSE is disabled.

If by "table lock" you mean a "shared for read lock" (*SHRRD)... then it's likely that the cursor has been pseudo closed

  Pseudo closed cursors are a key part of a performance optimization feature of IBM DB2 for i SQL. When an application closes a cursor, DB2 for i normally closes the cursor and closes the file, deleting the ODP (Open Data Path). If the application runs the same statement multiple times, each new execution requires a full open of the target file. The idea behind pseudo closed cursors is to not fully close the cursor and file but rather to cache the cursor for potential future use. The cursor is left in a soft closed (or pseudo closed) state. When the cursor is pseudo closed, the underlying file and ODP are left open. All record locks are released; however, a shared lock still appears on the file. DB2 for i can then reuse the cursor as needed without the overhead of a full open of the file. DB2 for i can also decide to hard close the pseudo closed cursor when needed.

They should not cause a problem unless you have a process that needs exclusive access, such as one that uses Clear Physical File Member (CLRPFM)

Such process should be avoided now-a-days, given the 24/7 nature of most business.

If you have to have an exclusive process, modify the process to request the locks to be released if needed... ALCOBJ OBJ((MYFILE *FILE *EXCL)) CONFLICT(*RQSRLS)


What does Oracle SQLCODE ORA-00942 signify? I am very sorry if I am asking a very basic question, but I Googled for this and found no useful info. Could you please explain what the Oracle SQLCODE ORA-00942 stands for?

From the Oracle Doc:

  SQL*Loader-00942: partition string not part of table string

  Cause: the specified partition is not part of the specified table

  Action: None


How to Move All tables from one Database into Different Database? I am using Oracle SQL Developer and I have ten tables. Now I want to move all 10 tables from Oracle into a SQL Server 2008 R2 database. Is there any easy solution for this task?

There are a lot of ways to do this one that go beyond what I listed, but here's a start.

1) You could try the SQL Server Migration Assistant for Oracle.  It's free and usually works well for me for smaller or medium sized datasets.

http://www.microsoft.com/en-us/download/details.aspx?id=28766

2) Create a SSIS package.

3) You could create a linked server in SQL and then select * from Oracle into SQL table (messy and probably not a good idea).

http://support.microsoft.com/kb/280106

4) Export to flat files and import into SQL.  Again, a ton of caveats with this one.


Computed column cannot be persisted because the column is non-deterministic I know this is not the first time this type of question has been asked.

But why in the following scenario is the persisted computed column being created "non-deterministic". The answer should always be the same, right?

CREATE TABLE dbo.test (Id INT, EventTime DATETIME NULL, PosixTime INT NOT NULL)
GO

DECLARE @EventTime DATETIME =  '20181001 12:00:00'
DECLARE @GPSTime INT = DATEDIFF(SECOND, '19700101', @EventTime)
INSERT INTO dbo.Test(Id, EventTime, PosixTime)
VALUES (1, @EventTime, @GPSTime)
    , (2, NULL, @GPSTime)
GO

SELECT * FROM dbo.test
GO

ALTER TABLE dbo.test ADD UTCTime AS CONVERT(DATETIME2,ISNULL(EventTime, DATEADD(SECOND, PosixTime, CONVERT(DATE,'19700101'))),112) PERSISTED
GO

  Msg 4936, Level 16, State 1, Line 42 Computed column 'UTCTime' in
  table 'test' cannot be persisted because the column is
  non-deterministic.

I think I'm following the deterministc rules here.

Is it possible to create a persisted computed column here?

You need to use a deterministic style when converting from a string representation.

You were not using a deterministic style with the conversion from string to date.

You were unnecessarily specifying a style when converting from date to datetime2.

There is a confusing mixture of date/time data types in the question.

This works (producing a datetime column):

ALTER TABLE dbo.test
ADD UTCTime AS
    ISNULL
    (
        EventTime,
        DATEADD
        (
            SECOND,
            PosixTime,
            CONVERT(datetime, '19700101', 112)
        )
    )
    PERSISTED;

As Aaron mentioned (we were answering concurrently), you do not need to persist a deterministic column to index it.


What is the best way to store X509 certificate in PostgreSQL database? I'm working on web authenticate system where users will digitally sign random tokens and this will be checked with X509 certificates stored on the server.

Therefore I have to store several X509 certificates (PEM or DER format) in PostgreSQL database. Sounds easy, but I want to have possibility to search certificates with subject, issuer, notBefore, notAfter and similar criteria.

My idea is to have following columns in database: X509data, notAfter, notBefore, subject, issuer etc. Than I will create object (in SQL alchemy) representing X509 certificate with methods like add_new_X509(), find_X509(search criteria)  etc. So whenever I will add new certificate with method add_new_X509() it will automatically reads from certificate all data and fill up rest of the columns and put raw certificate into X509data column.

Unfortunately this solution have two disadvantages:

I will store the same information twice (in X509 certificate itself and in separate columns for easy searching)
Whenever I want to read X509 certificate, my application will have to crosscheck notAfter, notBefore, subject, issuer with information stored in original certificate (this is for security reasons, in case someone would try to modify this fields).

So.. anybody have better idea, or suggestion? Maybe somebody see any other security issue that can arise with this solution?

Duplication isn't ideal, but in this case is probably the best choice. Set the table permissions so that the table owner is not the operational day to day database user your app runs as, and only GRANT your app the ability to write to the certificate data column, not the "cache" columns with expiry etc. Have a SECURITY DEFINER trigger function intercept writes to the certificate field and as a privileged user update the indexed cache columns by using an X.509 library to extract the fields from the certificate after verifying it.

Alternately, you could write a PL/Python, PL/Perl, or even a C SQL function that calls an X.509 certificate parser library to extract fields and return them. So you'd say extract_x509_field(cert, 'subject'). Or perhaps even a row-returning form like SELECT subject, issuerName FROM (SELECT extract_x509_fields(cert) FROM the_table) where extract_x509_fields returns a row of all relevant cert data. With this approach you could create functional indexes like CREATE INDEX cert_issuer ON certificate_table( extract_x509_field(cert,'issuer') ); that could be used to match WHERE expressions. You wouldn't need to have table columns for the extracted data at all. The downside is that this would likely be slower as the cert would get parsed multiple times during index creation, during index re-checks, etc.

Either way, it's vital that your application operate as a PostgreSQL user that is not the database owner, not a superuser, and not the owner of the tables and indexes of concern. It should be GRANTed the minimum rights necessary and no more. If you have quite separate tasks (say, read-only vs write-and-update) consider using different database users for them so that even if the "read" part of your app is tricked into trying to write a field/update a cert/etc, it doesn't have permission to.


Is it possible to retrieve/get the query hash of a query without searching the DMOs? Sometimes I want to compare the performance of two queries by running them multiple times while an extended events session is capturing the metrics.
To avoid too much filtering and/or noise I would like to filter my extended events based on the query hashes.

In order to do that I need the query hash. So far I have had to look them up in the DMOs based on the text. But then I spend as much time finding the query hashes as I do just filtering the extended event session in other ways.

So is there an easier/quicker way to retrieve/get the query hash?

If you have the queries you're testing in front of you, you can get either an estimated or actual plan. When the query plan pops up, highlight the select operator and hit F4. You'll probably need to toggle around between plan operators to get properties to show up for the one you want, but when they do you'll see the QueryHash, QueryPlanHash, and SQLHandle of your query.


Attempting to setup a new named instance but can't get past the Feature Selection Screen I have been going through and looking at all of the options listed via google for the better part of the day. No matter how I try to run this, whether initially from the straight GUI interface, using a command line option with QS or Q set or from command line without the Q/QS so it goes through the gui I always get to the same spot but can go no further - would be pulling my hair out if any was left!

The latest Command line used is:

I have one complete instance installed. It was setup using all the default values. I am simply attempting to add a new instance (SQL, AS, IS, RS) as named instance with name of SQL2014..
This is all getting installed locally on my PC

You want to make sure you select install new instance

And then make sure you are choosing a new installation

Choose your features, and then under Instance Config you can name your instance


How to fold an event stream into a table? I'm trying to experiment a pure-postgres event sourced projection, and I'd like to represent a stream of events as a table:
imagine this flow of events:
┌───────┬───────────────────────┬───────┬──────────────────────────────┐
│ order |        type           │ topic │           payload            │
├───────┼───────────────────────┼───────┼──────────────────────────────┤
│ 1     | user-registered       │ users │ {"name": "john"}             │
│ 2     | user-changed-password │ users │ {"name": "john", "activation_token": "1234"} │
│ 3     | user-registered       │ users │ {"name": "bob"}              │
│ 4     | user-banned           │ users │ {"name": "bob"}              │
└───────┴───────────────────────┴───────┴──────────────────────────────┘

I'd like to create a view of the current state of active users containing:
┌──────┬──────────────────┐
│ name │ activation_token │
├──────┼──────────────────┤
│ john │ 1234             │
└──────┴──────────────────┘

In this example, there is only 1 active user because the second user has been banned.
So my question is: how could I "fold" the events table to end up with the active_users table ?
I tried to use case expressions but it's so hard to merge records:
create view users as
    select case type
        when 'user-registered' then (payload->>'name', null, true)
        when 'user-changed-password' then (null, payload->>'activation_token', true)
        when 'user-banned' then (null, null, false)
    end from es.events where topic = 'users'
    order by "order" asc
;

assuming the are no duplicate events you can do this.

create view "users" as
  select payload->>'name'
  from es.events
  group by 1
  having
      sum ( case type
            when 'user-registered' then 1
            when 'user-banned' then -1
            end ) > 0
union
  select payload->>'activation_token'
  from es.events
  where type='user-changed-password';


Create Slowly Changing Dimension table from Repeated Data I am receiving transaction snapshot data from a file, it contains a history of repeated data. Currently trying to find Slowly Changing Dimensions on table with [ProductId] Business Key. Many attributes exist: ProductTitle, Category, this is a sample table, actually have around 10 more attributes. How do I create a Product Slowly Changing Dimension table query?

Searching for a performance optimized way, if I have 10 columns, not sure if Group By on 10 columns is optimal

With SQL 2016, is there a function to obtain this data? Should I use Lead/Lag Function? FirstValue/Last Value? New analytics syntax? An attempted query is below.

Note: Data comes from a 1970 legacy file system containing historical data.

Data:

create table dbo.Product
(
    ProductId int,
    ProductTitle varchar(55),
    ProductCategory varchar(255),
    Loaddate datetime
)

insert into dbo.Product
values
 (1,'Table','ABCD','3/4/2018')
,(1,'Table','ABCD','3/5/2018')
,(1,'Table','ABCD','3/5/2018')
,(1,'Table','ABCD','3/6/2018')
,(1,'Table','XYZ','3/7/2018')
,(1,'Table','XYZ','3/8/2018')
,(1,'Table','XYZ','3/8/2018')
,(1,'Table','XYZ','3/9/2018')
,(1,'Table-Dinner', 'GHI','3/10/2018')
,(1,'Table-Dinner', 'GHI','3/11/2018')
....more data with ProductId =2,3,4, etc

Current Repeated Data in File:

Expected Output:

Attempted Query

(seems to be inefficient, especially when having 10 attribute columns)

select
    product.Productid
    ,product.ProductTitle
    ,product.ProductCategory
    ,min(product.LoadDate) as BeginDate
    ,case when max(product.LoadDate)  = (select max(subproduct.LoadDate) from dbo.Product subproduct where subproduct.productid = product.productid) then '12/31/9999' else max(product.loadDate) end as EndDate
from dbo.Product product
group by Productid, ProductTitle, ProductCategory

The problem here is the way you're loading data. With a Type 2 SCD (Effective Date) you want to add a new row only when there is a change to the data. The first four rows in your dataset do not change except for the load date.

You need to ETL your data from the source files into your database where you can more easily identify if records have been changed and only add new rows for the changed records. Begin Date would be the date that row is first loaded and End Date would be when one of the values changes marking the original row as no longer current. For the current row, End Date is NULL.

The query then becomes much simpler:

select
    product.Productid
    ,product.ProductTitle
    ,product.ProductCategory
    ,BeginDate
    ,CASE EndDate
        WHEN NULL THEN '12/31/9999'
        ELSE EndDate
     END AS EndDate
from dbo.Product product
group by Productid, ProductTitle, ProductCategory

The end result is your table would only have 3 rows rather than 10. If you're importing directly using TSQL scripts, look at using MERGE to UPDATE\INSERT as appropriate. If you're using an ETL tool like SSIS, they have native functions for handling SCDs. There are lots of ways to solve this, but the key is importing your data in a better way.


Should I use a RDBMS JSON field or a NoSQL database to store large JSON datasets that I don't need to query? I am building a website that allows users to retrieve a bunch of open source chart (eg UN data). I don't need to be able to query the data itself. I will instead extract certain meta data (keywords, date ranges, etc) that will be stored separately in a structured format for querying.

Since I don't need to query the data itself, would a NoSQL database offer any advantage over something like a Postgres JSONB field? I'm primarily thinking about read performance, database size, and cost (I realize cost will depend on a number of factors, so maybe that's not easy to anticipate at this high level).

Neither. What you need is a file system, or cloud blob storage. Choose one with high compression ratios.

Typically data is used intensely for a short while then touched only infrequently thereafter. Choosing a storage service with tiers will allow further cost saving.

As the service becomes popular it is likely several users will have the same dataset. Storage service de-duplication could allow further cost reduction.

Store the meta data in whichever flavour of DBMS you choose for the main application. This will reduce the overall complexity and utilise existing expertise. Relational would be my preference but document storage would work, too.

MongoDB adds empty space to the written data so the on-disk length follows a power-of-two rule. This could waste a lot of space depending on how your source JSON works out. This is a lot like the fill-factor in RDBMS B-Tree indexes. This behaviour can be removed in more recent versions.

Performance-wise I would think a solution based on the file system directly would be marginally faster than one involving a DBMS of either flavour. Both approaches will use the same underlying hardware - spindles, NICs, buses etc. The DBMS, no matter how efficient, is an additional layer in the stack between the disk and the client. If it is not adding additional value, through joins, filters, aggregates, security or whatever, I can't see how having it in the run-time path helps. The meta-data is a different beast to the JSON, of course, and will benefit from all the additional functionality a DBMS provides.

There's a trade-off to be had with compressed data. On one side the storage IO and network communication is faster when the data is compressed. Reading 10GB uncompressed data from disk and moving it over the network will always be slower than reading and moving the same data compressed to 7GB. On the other side is the CPU cost to decompress before usage. My experience from data warehouse environments, with gigabyte-scale reads and ten-plus cores, is that compression lowered elapsed time. In OLTP-like environments (small, random reads with frequent updates) compression is definitely not recommended. We don't have hardware specs, dataset sizes, usage patterns, pricing information or a measure of your speed-versus-cost tolerance so can't calculate this on your behalf. Likely testing at production scale will be the only way to answer this properly.


Sync two postgres databases from two sperate locations with no network I have 2 postgres databases running the same schemes on two different computers, that are not guaranteed to have any network connection while having to handle read/write requests.

Because they operate on two different locations, I'm trying to find a solution to merge them once in a while to keep data updated in both (I can connect them every couple of week, or pass files with a flash drive).

My main concern is that this databases have unique values in some of the tables (Auto Increment Primary Keys), and I'm not sure how to handle this - what if both dbs create the same key, for different data? Are there any tools that can merge automatically and output changes that were done?

(If it matters, using postgres 10, ubuntu env.)

Thanks

I think a good way to do that is to have sequences with an increment of 2 instead of 1. That way your primary keys will be uneven on one site and even on the other one. Then you can merge without worrying.

create sequence seq_uneven increment by 2 start with 1;
create sequence seq_even increment by 2 start with 2;

And then you use seq_uneven on one site and seq_even on the other.


Error creating table (Error in your sql syntax). What am I doing wrong? I'm trying to create a data base, but I get this error:

  ERROR 1064 (42000) at line 37: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '
  CONSTRAINT Users_Manager_ID_PK PRIMARY KEY(Manager_ID, Employee_ID))' at line 6

CREATE TABLE Customer
(
Customer_ID INTEGER(8) PRIMARY KEY,
f_Name VARCHAR(25) NOT NULL,
l_Name VARCHAR(25) NOT NULL);

CREATE TABLE Users
(
Manager_ID INTEGER(8) NOT NULL,
Employee_ID INTEGER(8) NOT NULL,
f_Name VARCHAR(25) NOT NULL,
l_Name VARCHAR(25) NOT NULL),
CONSTRAINT Users_Manager_ID_PK PRIMARY KEY(Manager_ID, Employee_ID));

CREATE TABLE Advertising
(
Sales_Person VARCHAR(25) PRIMARY KEY,
CONSTRAINT Advertising_Manager_ID_FK FOREIGN KEY(Manager_ID) REFERENCES Users(Manager_ID)),
CONSTRAINT Advertising_Employee_ID_FK FOREIGN KEY(Employee_ID) REFERENCES Users(Employee_ID));

CREATE TABLE Customer ( Customer_ID INTEGER(8) PRIMARY KEY, f_Name VARCHAR(25) NOT NULL, l_Name VARCHAR(25) NOT NULL);

CREATE TABLE Users ( Manager_ID INTEGER(8) NOT NULL, Employee_ID INTEGER(8) NOT NULL, f_Name VARCHAR(25) NOT NULL, l_Name VARCHAR(25) NOT NULL,
    CONSTRAINT Users_Manager_ID_PK PRIMARY KEY(Manager_ID, Employee_ID),
    CONSTRAINT idx_unque_users UNIQUE KEY (Employee_ID,Manager_ID));

CREATE TABLE Advertising ( Sales_Person VARCHAR(25) PRIMARY KEY, Manager_ID INTEGER(8) NOT NULL,Employee_ID INTEGER(8) NOT NULL,
    CONSTRAINT Advertising_Manager_ID_FK FOREIGN KEY (Manager_ID) REFERENCES Users(Manager_ID),
    CONSTRAINT Advertising_Employee_ID_FK FOREIGN KEY (Employee_ID) REFERENCES Users(Employee_ID));


Oracle Error: the sga requires more space than was allocated for it (Oracle SQL 12c running on Windows 10)

My installation has previously been working fine. I was trying to set up one of my tables to use the In-memory option available in 12c. I was following this guide: https://blogs.oracle.com/In-Memory/entry/getting_started_with_oracle_database
I wanted to allocate 4Gigs of system RAM to be used as in-memory table storage space for a table I would specify later. To do this I ran these commands using the guide as a reference:

This one to allocate 4G of RAM space:

ALTER SYSTEM SET inmemory_size = 4G scope=spfile;

And this one, which is what caused the problem:

ALTER SYSTEM SET sga_target = 75G scope=spfile;

The guide said "By default, the installer set the SGA_TARGET to 71G, " which is where I came up with 71G + 4G = 75G.

Now of course I get the error when trying to connect to the DB:

  ORA-27101: shared memory realm does not exist

  the sga requires more space than was allocated for it

Since I can't connect to the database because it won't start, how would I fix the invalid setting? Is there some sort of "undo"?

Edit:
No, there is no backup of the spfile or pfile that I can use to generate the spfile. I have manually changed the sga_target to small values that should be fine but still get the error no matter what. I also tried creating a new, additional database. When I did that, the other database started working for some reason. It just looks like the new one though and is missing new tables I had previously created.

Before you start make a copy of the files you change (the spfile)
Check where your spfile is located, on Linux this is $ORACLE_HOME/dbs/spfile${ORACLE_SID].ora. If this does not exist check the content of $ORACLE_HOME/dbs/init${ORACLE_SID].ora. There the spfile parameter may be set which defines the path of the spfile. For Windows look in the %ORACLE_HOME%\database directory instead of.
Open sqlplus and create a pfile from the spfile create pfile='<auxiliarypfile>' from spfile='<spfilepath>'. Do not overwite an existing pfile but write the file in an arbitrary directory.
Edit the pfile <auxiliarypfile> created in step 3 with a text editor like notepad.
Open sqlplus and overwrite the spfile by a new created spfile create spfile='<spfilepath>' from pfile='<auxiliarypfile>'


Extract one MySQL database from a FULL MYSQL Server backup file I have a small shared windows server on which i'have installed MySQL 5.1 engine for hosting my user's mysql databases.

I backup the complete MySQL databases each day by running this script as batch file in SCHEDULED TASKS:

"C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqldump.exe" -uroot -p"**********" --force --flush-logs --all-databases > c:\dbbackup\alldatabases.sql

So alldatabases.sql will be a SQL file which contains the complete data of MySQL server which can be restored on cases that all of the databases are lost or migrating to another server.

I have never extracted one specific mysql database from this "alldatabases.sql" backup file and i don't have any clue on this.

**The must idiot solution is to restore this big backup file in another server and then simply create an individual backup from a specific database.

Could you help me on extracting one specific MySQL database from this big backup file?
Lets say that MySQL database name is  JOOMLAdb

** Also do you have any idea on changing my backup script, so instead of having a big backup file i may have individual backups for each database (Databases will increase/decrease through the time and we don't have a fix list of available databases)
Thank you for your care

Best Regards

Your same question seems to be answered here:

https://stackoverflow.com/questions/2342356/import-single-database-from-all-databases-dump

And as far as an improved backup script, here's what I'm currently using for several years and it rocks:

http://sourceforge.net/projects/automysqlbackup/


How often do you run VACUUM FULL? I'm running PostgreSQL 10 on AWS for a couple years in production and wondering if it's worth the downtime to perform a VACUUM FULL on the database.

Normally you never have to run VACUUM (FULL). It is only necessary if the tables have become unduly bloated, either because of autovacuum being too slow or because of mass updates.

I would examine the tables you suspect to be bloated with pgstattuple to see how big free_percent is.

20% bloat is normal. Consider VACUUM (FULL) only if you have much more bloat.


Constrain/limit certain records in sql table definitions Let's say I have posts on which users can leave a comment. And I want to limit the amount of comments to a certain post to 10, is it possible to add such constraint/restriction to the table?
My guess is not, but just want to make sure there is nothing I am missing.

----------------------------------
| user_id | post_id | comment_id |
----------------------------------
| 1       | 1       | 1          |
| 1       | 1       | 2          |
| 1       | 1       | 3          |
| 1       | 1       | 4          |
| 1       | 1       | 5          |
| 1       | 1       | 6          |
| 1       | 1       | 7          |
| 1       | 1       | 8          |
| 1       | 1       | 9          |
| 1       | 1       | 10         |
----------------------------------

So adding another comment for user 1 on post 1 should not be possible in the above case. But the table is just an example, if I can achieve this with a different database schema, don't hesitate to answer. I simply want to limit the amount of comments for a user on a certain post.

I work with MySql through Doctrine 2. I can of course build in such limitations in my abstraction layer, but I am curious if such constraints are possible in MySql (or maybe MongoDB) directly.

To have maximum 10 rows for a user_id and post_id and given that MySQL has not even implemented CHECK constraints, only PRIMARY KEY, UNIQUE and FOREIGN KEY constraints, it might be easier to enforce this in the application layer.

Of course it's better to have it in the database, as applications may have bugs and the constraint would have to be implemented in every application that uses the database. So, if you want to enforce this constraint in MySQL, the only way possible I think is to add another comment_no column (not to be confused with the comment_id column you may already have and may already be used, e.g. may be unique in the table or unique per post. This comment_no will be unique per post and user and will allow values only from 1 to 10.) The applications that insert data in the table would still have to provide this numbering (1 to 10). But the database would never allow more than 10 rows per user and post, using an additional table and the following FOREIGN KEY and UNIQUE constraints:

-- auxiliary table
CREATE TABLE number_of_comments_allowed
  ( comment_no TINYINT NOT NULL,
    CONSTRAINT number_of_comments_allowed_PK
        PRIMARY KEY (comment_no)
  ) ;

-- values needed in the auxialry table
INSERT INTO number_of_comments_allowed
  ( comment_no)
VALUES
  (1), (2), (3), (4), (5), (6), (7), (8), (9), (10) ;

CREATE TABLE comment
  ( user_id INT NOT NULL,
    post_id INT NOT NULL,
    comment_id INT NOT NULL,
    comment_no TINYINT NOT NULL,
    -- PRIMARY KEY (whatever),
    -- other UNIQUE and FOREIGN KEY constraints
    UNIQUE (post_id, user_id, comment_no),
    FOREIGN KEY (comment_no)
        REFERENCES number_of_comments_allowed (comment_no)
  ) ;

The only other thing that needs taken care of is to restrict access to the auxiliary table once it has been created and populated, so no user/session (except an admin) can insert, delete or update it.

The number (10) of rows allowed per user and post can be easily increased (or decreased) later by adding rows (or removing rows - but this may need to delete some rows from the comment table first) from the auxiliary table.


MySQL: Select rows, grouped by ID, where none of the rows have a value in a certain column Confusing title, I know. Ii couldn't think of a better way to word it.
Basically, I have a table like this:

OwnerID | LotID | CurrentOwner

The OwnerID is the unique key for the table. This value is never repeated.
The LotID IS repeated. This table stores historical owner records for lots.
The CurrentOwner column simply contains a Y or NULL for each row. It is possible for one LotID to have multiple owners.

What I need is basically just for reporting. I need to know which LotIDs DO NOT have a "Y" for CurrentOwner in any of their rows. If I had the following data:

1, 1, Y
2, 1, NULL
3, 1, NULL
4, 2, NULL
5, 2, NULL
6, 3, Y
7, 3, NULL

Then I would need to know that LotID #2 does not have any owners currently.

I was able to solve my problem with the following query:

SELECT SUM(CASE
             WHEN o.CurrentOwner IS NULL THEN 1
             ELSE 0
           END) AS numNull,
       COUNT(*) AS total,
       LotID
         FROM owner o GROUP BY LotID HAVING numNull=total