What happens when we add an index to an existing table with a large amount of data? I've a table which will contain around ~15 Million records. Now I need to add an index to the table.

Adding an index will take some time to update every entry in the table.

I'm quite confused whether adding the index will cause downtime.

If yes, then how can I overcome the downtime?

With plain CREATE INDEX, the table will be locked for writes but not reads.

Use CREATE INDEX CONCURRENTLY to avoid write locks as well.

From the PostgreSQL docs on CREATE INDEX:

  When this option is used, PostgreSQL will build the index without
  taking any locks that prevent concurrent inserts, updates, or deletes
  on the table; whereas a standard index build locks out writes (but not
  reads) on the table until it's done. There are several caveats to be
  aware of when using this option — see Building Indexes Concurrently.

And more specifically (Like @ypercube commented):

  PostgreSQL supports building indexes without locking out writes. This
  method is invoked by specifying the CONCURRENTLY option of CREATE INDEX.
   When this option is used, PostgreSQL must perform two scans of
  the table, and in addition it must wait for all existing transactions
  that could potentially use the index to terminate. Thus this method
  requires more total work than a standard index build and takes
  significantly longer to complete. However, since it allows normal
  operations to continue while the index is built, this method is useful
  for adding new indexes in a production environment.

Bold emphasis mine.


Mysql: set variable in mysqldump I recently added a global variable in mmy.cnf max_statement_time=120 ; to stop long running queries and to throw an exception. Its working fine. Then I am unable to run mysqldump for backing up all my databases , obviously its true that we have large databases so backing up using mysqldump will take hours since here I have added max_statement_time to 120 seconds only. So how I can pass this as a session variable thru mysqldump to set it to hours just for mysqldump and still global value remains 120 seconds itself. Apparently I am after something like below :

mysqldump --max_statement_time=1000000 -u root -pMYPASS --all-databases > file.sql

Do we have any option there ? I went thru the mysqldump and found that it has --variable-name=value , but its not accepting this. I am using Mariadb 10.2 version.

I believe you should be able to specify a session variable by placing it in a backup.cnf file:

max_statement_time=10000

And then using the --defaults-file=backup.cnf parameter to mysqldump.

(You can also place user credentials in that file so that you don't have to show the username and password on the command-line, which is a potential security issue.)


Is it possible to take incremental backup in MongoDB? Is it possible to take incremental backup in MongoDB? if yes then how please guide me?

MongoDB doesn't provide an incremental backup option out of the box, but it's possible to do a file snapshot and replay the oplog.

As per MongoDB BOL Here If the volume where MongoDB stores its data files supports point-in-time snapshots, you can use these snapshots to create backups of a MongoDB system at an exact moment in time. File system snapshots are an operating system volume manager feature, and are not specific to MongoDB. With file system snapshots, the operating system takes a snapshot of the volume to use as a baseline for data backup. The mechanics of snapshots depend on the underlying storage system.

For example, on Linux, the Logical Volume Manager (LVM) can create snapshots.

For your further ref  Here and Here


How to get the row count from a table related to other table? I am new to mysql and database concepts and I will try to explain my question through these two minimal tables, here is the scenario for which I need to write a query,

Table : Fruits
| FruitId | FruitName |
| -------- | -------------- |
| 1        | Mango          |
| 2        | Apple          |
| 3        | Papaya        |

Table : FruitSubClasses
| FruitSubClassID | FruitSubClass   | FruitID|
| ---------- -----| --------------- |-----------|
| 100             | Alphonso        |1|
| 101             | Irwin           |1|
| 200             | Fuji            |2|
| 201             | Golden Delicious|2|

I want to get data of all such Fruits from Table 1 such that there are no rows for these fruits(no subclasses) in Table 2. From what I searched I think I will need to use Joins and Count but I don't know how to use them in this situation.
I can describe it like "For each FruitID in Fruits, Fetch the FruitID and FruitName from Fruits such that this FruitID is not present in FruitSubClasses" but could not make a working query for this.

You would need an OUTER JOIN to solve this one, particularly a LEFT OUTER JOIN (also known as LEFT JOIN) like so:
SELECT F.FruitID, F.FruitName
FROM Fruits F
LEFT JOIN FruitSubClasses FSC
    ON F.FruitID = FSC.FruitID
WHERE FSC.FruitID IS NULL

A LEFT JOIN returns all rows from the table on the left-hand side of the JOIN clause regardless of the match in the predicate of the ON clause. For rows where there are no matches on the right-hand side, NULL values are returned for the columns of the right-hand table. Knowing this we can then filter out anything that matches in the JOIN with the WHERE clause above, and only get the non-matching Fruits (that don't exist in FruitSubClasses).
And if you wanted just the row counts in a single scalar value, you can use the same query above with the COUNT(*) function like so:
SELECT COUNT(*)
FROM Fruits F
LEFT JOIN FruitSubClasses FSC
    ON F.FruitID = FSC.FruitID
WHERE FSC.FruitID IS NULL


Partitioning a really large table, or splitting in multiple tables? We had a MySQL server old enough to not have partitioning enabled, so we decided to take our largest tables and move all the old rows to another table. In case they were needed at some point, they'd be moved to the "recent table", to make its usage faster. Overall, it would mean we'd have:

table_old: holding about 25Gb
table_recent: holding about 1Gb

We would keep this proportion forever, trying to balance the amount of old data vs new data and keeping only data used last week onwards in the recent one.

This proved to be a little bit difficult to develop in a clean way, but it's working well now. By just editing the "recent date" threshold, we can balance how much data the recent table will hold. At some point, old rows would go to the old table, and if some old data was needed, it would be moved around beforehand.

Now, we moved to MySQL 5.7, and partitioning is a thing. It seems cleaner at a developer side, but I don't know if it would be as efficient as what we had now, meaning we could update one row's last_used_time field and pop it to the main table.

Would MySQL still move those fields between partitions, if my partition is based on a varying field like this?
Would partitioning the table make the system run faster, or keeping the data in different tables is better?

I doubt anyone can tell you whether the performance of your home-grown system will be better or worse than the performance of MySQL built-in partitioning.

In general, some thoughts:

Using MySQL's partitioning, you would get the benefits of any improvements simply be upgrading to a newer version; in most cases, your code would probably not need to change.
MySQL's partitioning might allow more flexibility in what you already do, making some things that are impractical today work adequately.
You've got to support your home-grown version yourself, and make it work with other changes to your system.
Your home-grown version (presumably) works exactly how you need it to, and works adequately well with the rest of your application(s).
If you have a special need, you have the option of modifying your home-grown system; while modifying MySQL is possible, it's a bit more involved.
To move your current system to MySQL's partitioning would probably require some code changes in your system, and might require tuning of multiple queries (to avoid hitting more partitions than necessary).

Therefore:

It might be worthwhile to test switching your table to MySQL partitioning, to get an idea of how it performs with your data (switching and seeing a major performance degradation would be a bad thing).
In the long run, you would probably want to try to switch to using MySQL's partitioning (as it's likely to get better with time, and maintaining your solution could grow more complicated with time)...
...unless it turns out that some features of your home-grown solution are absent in MySQL partitioning and are critical to your application(s).
In any case, I'd allow for at least as much time and effort in switching to MySQL partitioning as you spent in developing and perfecting your own solution. It may be much simpler, but you may have inadvertently built other parts of your system around your solution that would have to be reworked to function with MySQL partitioning.

This is all completely generic advice - I don't know anything about MySQL partitioning (or about your solution, for that matter). I have built some system where we created our own solution instead of using existing products, though. I know that sometimes they work much better for your needs - but they do require maintenance, and switching to using a tool less tailored to your specifics can eventually save you that maintenance time, but can cost you in terms of flexibility, and can take a while to understand as well as you understand your solution.

If your solution is low-maintenance and works well for you, with no major performance concerns, I'd be inclined to maintain it for now at least. However, given the time to explore and the fact that there may now be a built-in solution as good or better than what you have, I'd want to check it out and see if it'd work for you.


When should I think about upgrading our RDS MySQL instance based on memory usage? It seems like our DB server is doing garbage collection at a increasingly faster rate, which seem normal since it's growing. What's a good rule of thumb of when to switch to a bigger instance, I'm not a DBA and have no frame of reference. It seems to be doing garbage collection once every 2-3 days now whenever there's only 100mb left.

The server itself has 1.7GB of RAM.

Before you switch server models, you need to know what differences there in configuration

I sat down and carefully looked over all default MySQL settings that would normally be written in a my.cnf. All are values are the same with the exception of two

max_connections
innodb_buffer_pool_size

Amazon set the following values based on server model

MODEL      max_connections innodb_buffer_pool_size
---------  --------------- -----------------------
t1.micro   34                326107136 (  311M)
m1-small   125              1179648000 ( 1125M,  1.097G)
m1-large   623              5882511360 ( 5610M,  5.479G)
m1-xlarge  1263            11922309120 (11370M, 11.103G)
m2-xlarge  1441            13605273600 (12975M, 12.671G)
m2-2xlarge 2900            27367833600 (26100M, 25.488G)
m2-4xlarge 5816            54892953600 (52350M, 51.123G)

For more information see my past posts on MySQL in Amazon RDS

Sep 21, 2012 : MySQL 5.5 Runs Out of Memory, Drops All Connections When Creating Many Databases
Sep 18, 2012 : How do I reduce the database size on AWS RDS MySQL?
Sep 10, 2012 : Does the Amazon RDS backup/snapshot service lock tables?
Aug 02, 2012 : Local database vs Amazon RDS
Jul 25, 2012 : Scaling Percona datacenters: setup and replication


SSRS output to file subscription not working I'm new to Reporting Services.  We have SSRS 2017 set up on a Windows Server 2019 Standard box.

I've got my report all written and tested but given the size of the output, I'm trying the subscription option to write the output to a local file, but so far I've run into nothing but problems.

I'm trying to write to \\localhost\ReportOutput as a share.  I've granted full control on that folder to all the accounts I've tried to use.

First problem - whenever I've tried the * Use The Following Windows user Credentials option, I can type in the creds, but when I click Apply, it blanks out the password.  When I re-edit the subscription I get warning/errors that those fields can't be blank.  The username is not blank (though it gets the warning anyway); the password is blank.

Second problem - no matter what creds I enter, I get "The permissions granted to user '' are insufficient for performing this operation." as an error when the report is run.  '' is not trying to hide my creds - that's really what the error says.

Third problem - I've tried configuring the system wide Subscription Settings File Share Account and configuring the subscription to use that instead.  When I run the report, I get the same "permissions granted" error above.  I've tried using both domain and local users in this setting.

When I look at the log file, I see these error messages consistently before the insufficient privs error:

Reporting Services account may not possess permission to perform authentication checks.
Check the Windows Authz documentation for more information and details on diagnosing issues.

I've done a lot of googling trying to find what to do about it, but most of the results associate it with email delivery failures (which we are not having), and some of the suggestions (like granting a user 100% read privs in the domain controller) are things I don't have the ability to use.

Has anyone else had so much trouble with SSRS outputting results to a file?

Thanks

Turns out that the report tries to run as the Owner of the subscription, and that credential needs to have a higher level of privs in the AD for the save-to-file-share option.

I don't have those privs but I can tell SSRS that the subscription is owned by someone that does.


psqlODBC access encoding problem My local machine: Windows 7, 32-bit. I have installed on it the psqlODBC for 32-bit. MS Access is 32-bit as well. I have tried both Unicode and Ansi connections to...

The server: a 64-bit Ubuntu, and the PostgreSQL database I want to link Access to is SET SERVER_ENCODING TO LATIN1; because I am Spanish and the data this DB will be storing requires the server to be able to handle our characters and, correct me if I'm wrong, UTF-8 does not do that.

The database works, access works, and everything is just fine as long as I don't ask Access to retrieve and link a table stored in PostgreSQL which fields have accented characters (such as 'ó'). This is: I can link tables that have no fields with accented characters, and I cannot link tables that contain fields with accented characters. HOWEVER, there is no problem when the records contain accented characters: I can both enter them from Access and read them from Postgre back into Access.

Anyone knows the reason behind this weird behaviour? I can live without accented field names but it's really bugging me...

Alright, so the actual problem was not related to the accented or special characters. The issue was with this certain field, namely "Cód. Requerimiento". That dot (.) was the problem: removed it and everything is awesome now :)


One database in AlwaysOn Group not truncating log file I have been stuck with this issue for two days now and am not hoping someone here might know the answer.

I have multiple databases in one availability group, all of the databases follow the same backup plan. (Full Recovery, Full Backups & hourly Log backups).

One database, and only one, is currently refusing to truncate (reuse) it's log file. It has now grown slowly to 33GBs where the actual database itself is less than 512MB (so tiny).

I checked if there are any running transactions (there aren't) if the log backups run (they do) and everytime I manually do a log backup to check why the truncate isn't happening I get; AVAILABILITY_REPLICA as the reason.

Point is that when looking at the availability dashboard everything is green, there are no Log Queues, no Redo Queues, everything looks as if it is good to go.

As these are hosted on a managed environment where I cannot add/remove databases from the AG myself I've created a ticket to ask for this particular database to be removed from it and then added again. However I'm not sure if this;

a) will fix the problem
b) will not just be a temp fix (truncate once, then slowly start growing again)

Does anyone here have any suggestions as to what to look at?

Extra info; SQL Server 2016, Running on Windows Server

I would like to comment but couldn't due to the lack of reputation.

Is your secondary node being enabled read-only? And if so, did you check if there's any blockings on the secondary node?

Another thing that you could check is if there's any maintenance jobs running.

And... could you try to make everything asynchronous mode to see if it helps? Once log reduced, you may set it back to synchronous mode.

  ... I've created a ticket to ask for this particular database to be removed from it and then added again. However I'm not sure if this;
  a) will fix the problem

Last but not least if everything doesn't work, yes, you can remove that particular DB from the AG group. It should fix the issue. Until the log has reduced, you may add it back to the AG group. Removing DB from the AG group shouldn't cause to any impact as applications should be connecting using the Listener.

  b) a temp fix (truncate once, then slowly start growing again)

Yes, it might occur again if we don't figure out the root cause to it. And you probably would have to repeat the whole procedure in removing/adding back to AG group, though it shouldn't cause to any production impact.


Is it possible to choose RAISERROR or THROW depending on SQL Server version? Here's my code right now:

BEGIN TRY
INSERT INTO TABLE (F1,F2,F3)
VALUES ('1','2','3')
END TRY
BEGIN CATCH
;THROW
END CATCH

Works great, unless it's run on a machine with SQL 2008. I'd like to have the CATCH block do a check against the SQL version and run THROW if it's equal or higher to 2012, and RAISERROR if it's 2008. I keep running into syntax errors, and I'm wondering if it's even possible. Even something simple like this is not working for me.

BEGIN CATCH
IF ((SELECT SERVERPROPERTY('productversion')) >= 11) ;THROW
END CATCH

Any advice is appreciated.

No this isn't possible.

This is invalid syntax in earlier versions and will cause a compile error.

It is not possible to hide the THROW in an EXEC inside the catch block either as a parameterless throw must be directly contained inside the catch.

You would need to deploy the code version you want according to the version of SQL Server you are deploying to (and unfortunately there is not good support for this either in the SSDT tooling that I am aware of - no equivalent of including code lines selectively through conditional compilation)


PostgreSQL: joining on jsonb array Using this contrived example structure:

Base table data

{
  "uid": "b12345",
  "nested": ["n12345", "n34567"]
}

Nested table data

[
  { "uid": "n12345", "message": "Hello world" },
  { "uid": "n34567", "message": "Hello world" }
]

I'm trying to join the tables on the nested array such that each uid is replaced with its corresponding record:

{
  "uid": "b12345",
  "nested": [
    { "uid": "n12345", "message": "Hello world" },
    { "uid": "n34567", "message": "Hello world" }
  ]
}

The solution in this post looks very close to what I need, but it seems like the main difference/blocker is that the nested array here is initially flat.

Here is the SQL I've been using to test it, including a query closely modeled on the above post. I'll appreciate any help!

This seems to do what you want:

select jsonb_build_object('uid', b.uid, 'nested', jsonb_agg(to_jsonb(m)))
from base b
  join lateral (
    select n.uid, n.message
    from nested n
      join jsonb_array_elements_text(b.nested) as x(bid) on x.bid = n.uid
  ) m on true
group by b.uid
order by b.uid;

Online example: https://rextester.com/ASA37564


Postgres foreign key with ON DELETE SET NULL for only some of its columns I have added a (new) 2nd foreign key to a table a. Its 1st foreign key is optional, hence the constraint ON DELETE SET NULL on the foreign key. The 2nd foreign key is mandatory, hence the (new) constraints NOT NULL on a1 and a2 and (new) the constraint ON DELETE CASCADE on the foreign key. The 2nd foreign key also consists of a subset of the columns for the 1st foreign key.

CREATE TABLE a
(
  a0 INTEGER NOT NULL PRIMARY KEY,
  -- other columns
  a1 INT NOT NULL,
  a2 INT NOT NULL,
  a3 INT,
  a4 INT,
  FOREIGN KEY (a1, a2)         REFERENCES b ON DELETE CASCADE,
  FOREIGN KEY (a1, a2, a3, a4) REFERENCES c ON DELETE SET NULL
);

The problem that I run into is that I can now no longer DELETE FROM c, since this invalidates the NOT NULL constraints on a1 and a2. What I would like to happen in the case of such deletes is for a1, a2 to remain unmodified and for a3, a4 to be set to NULL. Is there a concise syntax (more concise than with an extra DELETE trigger) for defining a constraint that achieves this?

Currently there is no way for the CASCADE action to set only some of the columns to NULL. So the only options I see would be:

have all the 4 columns defined as NULL and use ON DELETE SET NULL action. Seems like this is not an option in your case.
add another table to hold this relationship (where all the columns are not nullable) and use ON DELETE CASCADE.
a trigger.
create a procedure (function in Postgres) to handle deletions of table c. The function would first modify the columns (c,d) in table a and then delete from table c. Then you have to make sure that all users/applications use this function and do not directly delete from table c.


Is there a way in SQL Server to make a table only able to insert by trigger? The title is pretty self explanatory but if your'e curious as to why I want this; I want this because I have an archive/log table that stores past values of an active table and due to this I don't want the data at risk of being compromised in any way. The only thing that should ever insert on the table is the trigger I created on the active table to log its changes. In the rare case we may need to manually edit the log table I will turn off (if it exists) the "insert lock"

I am using SQL Server 2012 Enterprise with SQL Management Studio

This can be accomplished using a Certificate and module signing (i.e. ADD SIGNATURE). Using Impersonation via EXECUTE AS can get messy, and it leaves the possibility of someone else Impersonating the "allowed" User, or changing the contents of a module that is using the EXECUTE AS. But with module signing: the Certificate-based User cannot be Impersonated (see final test case), another module cannot be signed without knowing the password for the Certificate, and if anyone changes any module that you sign (such as the Trigger), then the signature is automatically removed, alerting you to that change and then you can decide whether to resign it with the current changes or reject the changes ;-).

Also, trapping ApplicationName / ProgramName in a Trigger is not reliable as it is easy to pass in that value in a ConnectionString.

Please note that the Audit table is in a different Schema -- Auditing -- than the main table -- in dbo -- to prevent ownership-chaining, assuming that most Stored Procedures would also be in the dbo Schema.

The Setup

USE [...];
GO

CREATE CERTIFICATE [AuditCert]
    ENCRYPTION BY PASSWORD = 'Password Goes Here.'
    WITH SUBJECT = 'Restrict Insert Test';
GO

CREATE USER [AuditUser]
    FROM CERTIFICATE [AuditCert];
    -- no DEFAULT_SCHEMA for Certificate-based Users
GO

CREATE SCHEMA [Auditing]
    AUTHORIZATION [AuditUser];
GO

-- DROP TABLE [Auditing].[AuditLog];
CREATE TABLE [Auditing].[AuditLog]
(
    AuditLogID INT IDENTITY(1, 1) NOT NULL,
    AuditDate DATETIME2 NOT NULL
        CONSTRAINT [DF_AuditLog_AuditDate] DEFAULT (SYSDATETIME()),
    ImportantStuffID INT,
    Column2 VARCHAR(50),
    CONSTRAINT [PK_AuditLog] PRIMARY KEY CLUSTERED (AuditLogID ASC)
);
GO

CREATE TABLE [dbo].[ImportantStuff]
(
    ImportantStuffID INT IDENTITY(1, 1) NOT NULL,
    Column2 VARCHAR(50),
    CONSTRAINT [PK_ImportantStuff] PRIMARY KEY CLUSTERED (ImportantStuffID ASC)
);
GO

CREATE TRIGGER [dbo].[AuditImportantStuff]
ON [dbo].[ImportantStuff]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;

INSERT INTO [Auditing].[AuditLog] ([ImportantStuffID], [Column2])
    SELECT  ins.[ImportantStuffID], ins.[Column2]
    FROM        inserted ins;
END;
GO

ADD SIGNATURE TO [dbo].[AuditImportantStuff]
    BY CERTIFICATE [AuditCert]
    WITH PASSWORD = 'Password Goes Here.';
GO

CREATE PROCEDURE [dbo].[AttemptDirectInsert]
(
    @ImportantStuffID INT,
    @Column2 VARCHAR(50)
)
AS
SET NOCOUNT ON;

INSERT INTO [Auditing].[AuditLog] ([ImportantStuffID], [Column2])
    VALUES (@ImportantStuffID, @Column2);
GO

CREATE PROCEDURE [dbo].[ImportantStuff_AddData]
(
    @ValueForColumn2 VARCHAR(50)
)
AS
SET NOCOUNT ON;

INSERT INTO [dbo].[ImportantStuff] ([Column2])
    VALUES (@ValueForColumn2);
GO

CREATE USER [TestUser]
    WITHOUT LOGIN
    WITH DEFAULT_SCHEMA = [dbo];
GO

GRANT EXECUTE ON [dbo].[AttemptDirectInsert] TO [TestUser];
GRANT EXECUTE ON [dbo].[ImportantStuff_AddData] TO [TestUser];
GO

The Test

SELECT SESSION_USER, ORIGINAL_LOGIN();

INSERT INTO [Auditing].[AuditLog] ([ImportantStuffID], [Column2]) VALUES (-1, 'test 1');

EXECUTE AS USER = 'TestUser';

SELECT SESSION_USER, ORIGINAL_LOGIN();

INSERT INTO [Auditing].[AuditLog] ([ImportantStuffID], [Column2]) VALUES (-2, 'test 2');
-- Msg 229, Level 14, State 5, Line 102
-- The INSERT permission was denied on the object 'AuditLog', database '...',
--   schema 'Auditing'.

EXEC [dbo].[AttemptDirectInsert]
    @ImportantStuffID = -3,
    @Column2 = 'test 3';
-- Msg 229, Level 14, State 5, Procedure AttemptDirectInsert, Line 115
-- The INSERT permission was denied on the object 'AuditLog', database '...',
--   schema 'Auditing'.

INSERT INTO [dbo].[ImportantStuff] ([Column2]) VALUES ('test 4');
-- Msg 229, Level 14, State 5, Line 114
-- The INSERT permission was denied on the object 'ImportantStuff', database '...',
--   schema 'dbo'.

EXEC [dbo].[ImportantStuff_AddData]
    @ValueForColumn2 = 'test 5';
-- woo hoo!

SELECT * FROM [Auditing].[AuditLog];
-- Msg 229, Level 14, State 5, Line 122
-- The SELECT permission was denied on the object 'AuditLog', database '...',
--   schema 'Auditing'.

REVERT;

SELECT SESSION_USER, ORIGINAL_LOGIN();

SELECT * FROM [Auditing].[AuditLog];

EXECUTE AS USER = 'AuditUser';
-- Msg 15517, Level 16, State 1, Line 143
-- Cannot execute as the database principal because the principal "AuditUser" does not
--  exist, this type of principal cannot be impersonated, or you do not have permission.

UPDATE

Additional notes:

As @Paul mentioned in his answer, this method (as shown) does not prevent privileged Users from doing direct inserts. However, it is still possible to block DML actions not initiated from code signed with the Certificate via a Trigger on the Audit table **. But that mostly prevents accidental inserts since anyone in the db_owner fixed database role should be able to disable the Trigger, and there is potentially at least 1 work around that could be used by someone in the db_datawriter fixed database role if they are fairly crafty.
The method described and shown above doesn't need to use a Certificate. It would be possible to do the same setup using an Asymmetric Key. The nice thing about Certificates is that they are more portable since they can be backed up to a file or you can, starting in SQL Server 2012, extract the Certificate and its Private Key using the CERTENCODED and CERTPRIVATEKEY functions. This allows the exact same Certificate to be created in other databases and even other instances. This is quite helpful when there is cross-database functionality and you don't want to enable Cross-database Ownership Chaining and/or TRUSTWORTHY.
Since it is not apparently obvious from any of the test cases shown in either of the answers here, I will point out a fundamental difference between the two methods (which also happens to be the reason why I prefer Module Signing):

Using EXECUTE AS changes the current security context. It is essentially saying: I am Login/User A, but for the moment, please use the permissions of Login/User B INSTEAD OF mine.
Using Module Signing will add permissions to the current security context. It is essentially saying: I am Login/User A, but for the moment, please use the permissions of Login/User B IN ADDITION TO mine.

** I do have mostly-complete example code (about 75% done) for a Trigger on the Audit table that would disallow updates from anything but code signed by the Certificate, but ran out of time to complete it. The concept is that a lock is taken on the Certificate during the process, and the lock entry  includes the Certificate ID. You can verify that the Certificate ID is the desired Certificate and ROLLBACK if it isn't or no Certificate is used in the Transaction. The problem was that VIEW SERVER STATE is needed to use sys.dm_tran_locks. However, that is a fairly easy problem to solve as it can be granted via a Certificate-based Login, which can even be the same Certificate. In that case, the Certificate can be backed-up and restored into master for the purpose of creating the Login from it. Then just grant that Login the VIEW SERVER STATE permission, and finally sign the Trigger on the Audit table with that same Certificate (already in that DB as it was used to sign the Trigger on the base table).


SQL Query: Selecting a person who is a member of all existing groups I'm tinkering around with my very first SQL queries and am completely baffled by a problem I encountered.

Let's say I have the following database:

PERSON(PID, Name)
MEMBER(PID, GroupID)

How would I go about creating an SQL query that selects only the tuples of the persons who are a member of all existing groups (i.e. GroupIDs)?

I tripped over everything I could find regarding IN, EXISTS, ALL and the likes without finding an elegant solution.

Thank you very much,

Pety

There are several ways to get to this answer.  I'll tackle it in two parts.  The first is to find out how many groups there are.  This is a simple summary query:

select count(distinct GroupID)
from MEMBER

The second is how many groups one person is in.  This is similar:

select
  PID,
  COUNT(*)
from MEMBER
group by PID

Next you want to tie these together

select
  PID,
  COUNT(*)
from MEMBER
group by PID
having COUNT(*) = (select count(distinct GroupID)
                   from MEMBER)

Depending on which RDBMS you're using (Oracle, DB2, MySQL) there may be other syntaxes or layouts which make this faster, prettier or otherwise more desirable.


Is it possible to issue an INSERT / UPDATE / DELETE and specify for it not to be relayed to attached slaves in mysql? Just as the topic states.

Is it possible to issue an INSERT / UPDATE / DELETE and specify for it not to be relayed to attached slaves in mysql?

Here is a quick and dirty answer:

Simply run SET SQL_LOG_BIN=0; before each SQL command. It prevents subsequent SQL from entering the Master's binary log upon the completion of any SQL statement afterwards. Thus, it cannot be shipped forward to any Slave. However, there will be no record of the SQL being executed in the Binary Logs.

Within the given DB Connection, if you want to start recording again, just run SET SQL_LOG_BIN=1; before each SQL command.

Opening a new connection will having binary logging enabled within it by default.


DR - Moving databases from Active/Active cluster to Standalone server with LUN sync We have an active/active cluster on LUNs (with one named instance on each node and a total of an 100 DBs)

For DR purpose, LUNs will be replicated (with same drive letters) and attached to a standalone server with same configuration of SQL Server but not same Windows Server editions (enterprise vs standard, but i don't think it may be an issue), and the two same named instances pre-installed.

I proposed the Attach / Detach solution, with a script that auto-generate the attach / detach to simplify the tasks, and other scripts to copy Logins, jobs and so on.

Other DBA said a better option will be to rename the MASTER and MSDB databases from the pre-installed named instances on standalone server with _old, and to attach the MASTER / MSDB databases from Cluster node instance to both corresponding named instance in standalone instance.

He said it works and had tested it.

However, I've never heard of such a solution nor can i find any documentation of it.

I would like to hear some other opinions.

Thank you,
Kukah

I'd stay away from the scripting and detach/attach methods. I've done two migrations in the past nine months and was convinced that a better method would be to backup and restore the databases. That way you have a last-known good configuration point-in-time restore.

So here's my thought process.

Restart the source instance in single-user mode
Backup your databases WITH COPY_ONLY, COMPRESSION
Stop the old instance. Take it offline. Pull the network cables, if you must.
Restart the new instance in single-user mode
Restore master
Change the location of tempdb (see below)
Restore model and msdb
Restart the instance.
Restore your user databases.

Please note that you will need to do some cleanup on the instance names since you're moving from what I assume will be a named instance from your Multi-Instance Cluster to what I assume will be the default instance on a dedicated piece of hardware.

I may have misunderstood what you meant by "replicated." Do you mean that you're going to provision LUNs of the same size (you've already mentioned letters) on your SAN that you attach to new hardware? In this case, you're not describing a DR scenario at all. You're simply describing the provisioning of hardware for your new SQL instance. Disaster recovery would be how you plan to recover from the failure of these instances.

Naturally you know that you're moving away from a High Availability solution. I hope you're not relying on your SAN to do both HA and DR.


Does this cause overhead ? Select name from User where user_name LIKE '%alex%' I'm wondering if this statement can cause real overhead if many concurrent users
send the same request to mySQL InnoDB storage engine. Let's say I have a table that
has the user_name, and someone want to find whoever that has the word alex in their
username. If I use LIKE as follow with InnoDB engine, do you think it can cause so
much over head ? Let's say I index the username field.

Select name from User where user_name LIKE '%alex%';

Maybe, but maybe it's not worth worrying about. Depending on how many rows we're talking about, you might not be able to avoid a scan. How many rows in your table?

An index on the username column won't help this query. At all. Think about a phone book: Very easy to find all the people with the last name starting with Sm, right? Not so easy to find all the people with last names that contains ith (or the first name John, for that matter).

An index might help if you constraint your allowed searches to "starts with" instead of "contains." Which might be a better answer in the long run.

Who's searching for usernames that contain alex, anyway? Is this a common use case? If so you might want to look into alternative mechanisms. In SQL Server we have full-text search; see this StackOverflow question for some ideas on how to solve the same problem in MySQL (but again, keep in mind that it might be a "problem" you don't need to solve).


creating stored procedure to populate empty table using pl/sql I have created a table and inserted a values one row at a time. However, I am trying to populate the table using a "stored procedure" or "trigger". The values can be a random string or number. I have been researching on it, but couldn't find any related examples.

Could some please provide an example or suggest some reference to it? Cheers!!

CREATE TABLE BOOKING
(
  BOOKING_ID VARCHAR2(7) PRIMARY KEY
, BOOKING_DATE DATE
, BRANCH_ID VARCHAR2(7)
, ROOM_PRICE NUMBER(15)
, IS_BOOKED VARCHAR(5))
TABLESPACE TS02
PCTFREE 20  PCTUSED 70
STORAGE (INITIAL 200K NEXT 500K PCTINCREASE 20);

--to insert value to BOOKING table for one row per at a time
INSERT INTO BOOKING
VALUES ('HIM001','02-FEB-2016','KTM',500,'NO');

You can do it with simple SQL statements and DBMS_RANDON as demonstrated below:

CREATE TABLE BOOKING
(
  BOOKING_ID VARCHAR2(7) PRIMARY KEY, BOOKING_DATE DATE
, BRANCH_ID VARCHAR2(7)
, ROOM_PRICE NUMBER(15)
, IS_BOOKED VARCHAR(5));

--You may create a sequence object to generate IDs.
CREATE SEQUENCE booking_id_seq
 START WITH     1
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;

INSERT INTO booking
SELECT booking_id_seq.nextval, TRUNC(SYSDATE + DBMS_RANDOM.value(0,366)),
dbms_random.string('U',trunc(dbms_random.value(1,7))), trunc(dbms_random.value(1000,100000)), dbms_random.string('U',trunc(dbms_random.value(1,5)))
FROM  dual
CONNECT BY level <= 12;-- Here you can define the number of rows that you want to insert.

SQL> INSERT INTO booking
SELECT booking_id_seq.nextval, TRUNC(SYSDATE + DBMS_RANDOM.value(0,366)),
dbms_random.string('U',trunc(dbms_random.value(1,7))), trunc(dbms_random.value(1000,100000)), dbms_random.string('U',trunc(dbms_random.value(1,5)))
FROM  dual
CONNECT BY level <= 12;

12 rows created.

SQL> select * from booking;

BOOKING BOOKING_D BRANCH_ ROOM_PRICE IS_BO
------- --------- ------- ---------- -----
158     06-NOV-16 ULQXM        81615 SH
159     18-DEC-16 RTG          99655 XDY
160     15-JUN-17 UF           45335 MCY
161     01-JUL-17 I            23249 FGS
162     13-MAY-17 KLLL          2177 T
163     07-AUG-17 HFC          10259 UKD
164     12-FEB-17 TVIZQY       98038 PJL
165     10-JUN-17 BIP          94314 ZSRS
166     22-JUL-17 ERQO         45985 QYC
167     27-AUG-16 EJKYB        39139 NJO
168     12-APR-17 AASMB        86838 NY

BOOKING BOOKING_D BRANCH_ ROOM_PRICE IS_BO
------- --------- ------- ---------- -----
169     24-FEB-17 NCQPMS       38296 Z

12 rows selected.

SQL>

Here are some good examples of using DBMS_RANDOM PL/SQL Package.


Why SELECT is so slow I have the following table

CREATE TABLE words (
  id bigint(20) NOT NULL,
  `text` int(11) NOT NULL,
  w1 int(11) NOT NULL,
  w2 int(11) NOT NULL,
  w3 int(11) NOT NULL,
  w4 int(11) NOT NULL,
  w5 int(11) NOT NULL,
  w6 int(11) NOT NULL,
  w7 int(11) NOT NULL,
  w8 int(11) NOT NULL,
  w9 int(11) NOT NULL,
  PRIMARY KEY (id),
  KEY w1 (w1),
  KEY w2 (w2),
  KEY w3 (w3),
  KEY w4 (w4)
) ENGINE=MyISAM DEFAULT CHARSET=ascii;

that contains 2,124,377,011 rows.

Why this simple query

SELECT w1 FROM words WHERE w2=3112

takes 50 sec on Intel Xeon E3-1230 v2 at 3.30 Ghz Server with 7200 RPM HDD?

Is this normal or am I missing something?

UPDATE: It returns 60243 rows, the execution plain is

+----+-------------+-------+------+---------------+------+---------+-------+-------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows  | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+-------+-------+
|  1 | SIMPLE      | words | ref  | w2            | w2   | 4       | const | 31132 |       |
+----+-------------+-------+------+---------------+------+---------+-------+-------+-------+

It is slow because the table is big, and it performs lots of IO when reading sparse data. The index on w2 would be used to tell which rows will be read from the base table, which is huge. And since the rows in that table are not in order- physically that are fragmented, there will be probably one disk access per row (This is to read the value of w1)
To make this query faster, create a composite index on (w2, w1). This way, the data is only read from the index file, which is much smaller in size, and the required data is adjacent.
Note please that if it is not a frequent query, it is an overhead to have this index.


How to retrieve granted privileges without access to the database “mysql”? As a MySQL user without access to the mysql database, is it possible to retrieve my privileges?

It seems that using SHOW GRANTS did work in this case:

SHOW GRANTS FOR CURRENT_USER;

Although the manual states that you still need to have access to the mysql database:

  SHOW GRANTS requires the SELECT privilege for the mysql database.

And accessing mysql.user directly actually didn’t work, probably because it does also contain further sensitive information like the passwords of other users:

mysql> SELECT * FROM mysql.user;
ERROR 1142 (42000): SELECT command denied to user 'user'@'host' for table 'user'


How can I select a field value in PostgreSQL based to the date computation within the table? I have a PostgreSQL table that have these values:

dollar_rate; end_date
------
1. 46.77; 2015-09-16 11:03:03.4082
2. 46.70; 2015-09-17 11:27:00.2446
3. 46.67; 2015-09-17 15:09:15.0354

Now I have this query:

  SELECT dollar_value,end_date-now() from dollar_rate;

The results:

1. 46.77; -04:48:34.945704
2. 46.70; 19:35:21.890696
3. 46.67; 23:17:36.681496

As you can see at the third row, I would like to select this row, that is, the row that contains the highest value with the computation end_date-now(). How can I do this in PostgreSQL?

Sort the result (descending) by the value of end_date - now() and limit to only one row:

SELECT dollar_value, end_date - now()
from dollar_rate
order by end_date - now() desc
limit 1;

The computation end_date - now() is actually unnecessary in this case, you can simplify this to:

SELECT dollar_value, end_date - now()
from dollar_rate
order by end_date desc
limit 1;


Grant permissions on database except specific tables I've just started out with MS-SQL, so be patience with me :)
I have 3 databases and I need to grant read access to user on all 3 databases, the challenge for me is how I can give him the permissions except for specific tables?

Broadly speaking you can do this in two ways.

Grant read privileges on only the tables you want them to have access to. Or,
Grant them access to all tables and then Deny access on the tables you dont want them to have access to.

  The DBA giveth... and the DBA taketh away

There are lots of articles and references available that explain how they work together such as this one.

The keys words to search for are "sql server grant & deny". Permissions can be granted on the whole database, a schema or a specific object. (Table, view, stored proc etc)

This can be applied per user or via a Role. Rather than modify the existing roles, you could create a new role and grant the permissions you want to the role, and the give users that.

The most suitable method depends on how many tables they should/shouldnt have access to and how many users you would have to repeat this for.

If there is 100 tables in the db, and the user should read 90 of them, id grant + deny.

If you had to do the same thing for 5/10/50 users i'd create a role.


Dynamic Partition on tabular model 1200 using visual basic 2015 in visual studio I am using visual studio 2015, sql server 2016 and tabular model 1200. I am trying to create a dynamic partitions on the existing cube by creating SSIS package using script task and visual basic coding. So can any one specify the approach to create a dynamic partition every month and process the created partition every night. I searched through online and I didn't find any example with this environment.

I would study this white paper and the AsPartitionProcessing sample for ideas. Ignore that it targets Azure Analysis Services as it should work against your SSAS instance if you use that servername instead of asazure:// as the servername. It uses the Tabular Object Model (TOM).


What are the three parts of a LSN? I am curious about what the 3 parts of a Log Sequence Number (LSN) are and how they are generated.

A log sequence number is an ever-increasing, three-part number that uniquely defines the position of a log record within the transaction log. Source

I looked around a bit, and found at least one answer that said I should "not be concerned with how these are generated" but I still want to know.
If I run DBCC FILEHEADER ([AdventureWorks2014], 1) I get results that show me some important LSN's in my AdventureWorks

MaxLSN
45000000001600001
FirstLSN
44000000084000074
RedoStartLSN
45000000001600001

They do seem to have 3 distinct parts, what are they, what do they mean, and how are they generated?

According to Paul Randal in his Pluralsight course "SQL Server: Logging, Recovery, and the Transaction Log" the LSN is composed of three parts:

1) VLF sequence number (4 bytes)
2) Log block number (4 bytes)
3) Log record number (2 bytes)


How to better perform a query split into subqueries in PHP In PHP I have made this query:

$getTrackID = $this->db->query("SELECT `id`,`uid` FROM `tracks`, `users` WHERE `time` > CURDATE() - interval 1 year AND `uid`=`idu` AND `public` > 0 ORDER BY RAND() LIMIT 10");
$TrackID = $getTrackID->fetch_assoc();
$id=$TrackID['id'];
$xid=$TrackID['uid'];

The problem is that it takes 1 sec to return the result. I saw this question and it seems better practice split big queries into subqueries.

Is this the case? To better perform should I need to use something like:

$getTrackTime = $this->db->query("SELECT `id`,`uid` FROM `tracks`, `users` WHERE `time` > CURDATE() - interval 1 year AND `uid`=`idu`");
// Declare the rows array
$rows = array();
while($row = $getTrackTime->fetch_assoc()) {
    // Store the result into the array
    $rowsTime[] = $row;
}

$getTrackPublic = $this->db->query(sprintf("SELECT `%s`,`%s` FROM `tracks`, `users` WHERE `public` > 0",$rowsTime['id'],$rowsTime['uid']));
$rows = array();
while($row = $getTrackPublic->fetch_assoc()) {
    $rowsPublic[] = $row;
}
...

Quickly get into the habit of writing your queries with some kind of formatting, proper joins, and with properly qualified column names. It makes it much easier to understand. E.g.:

SELECT t.id
     , t.uid
  FROM tracks t
  JOIN users u
    ON u.idu = t.uid
 WHERE t.time > CURDATE() - INTERVAL 1 YEAR
   AND u.public > 0
 ORDER
    BY RAND()
 LIMIT 10


Trigger to insert data row into new table when old table is updated I am trying to set up a trigger in SQL server that when the price of an existing item on table item is changed, it inserts that entire item (row) into table item_price_history, putting the old price as old_price and the updated price as new_price. I'm pretty stumped as to how to get this to work. I keep getting errors that item_id cannot be bound, or is an invalid column, but it is a column in both tables. Here is what I have so far:

CREATE TRIGGER update_price_trg
ON item
AFTER UPDATE,INSERT
AS
BEGIN
  DECLARE @DATE_OF_CHANGE datetime;
  SET @DATE_OF_CHANGE = GETDATE();
  INSERT INTO item_price_history,item_id,old_price,new_price,date_of_change)
  VALUES (item_id,old.price,new.price,@DATE_OF_CHANGE)
END;

You need to use the correct table names in the trigger -- inserted and deleted are the internal table names used for the new and old data. Also, you need to associate the two tables correctly, and handle multiple rows simultaneously. Your code will need a rewrite to do all this:

CREATE TRIGGER update_price_trg
ON item
AFTER UPDATE,INSERT
AS
BEGIN
    INSERT INTO item_price_history(
        item_id,
        old_price,
        new_price,
        date_of_change
        )
    SELECT
        i.item_id,
        d.price,
        i.price,
        GETDATE()
    FROM inserted i
    LEFT JOIN deleted d
        ON i.item_id = d.item_id
    WHERE i.price != ISNULL(d.price, -1)
END;


MySQL Error : 1422 Explicit and Implicit Commit is not allowed in SP and Trigger I have Created a trigger, where I am storing some specific data in a table and after the trigger gets executed I am deleting that Table.

The query :

CREATE TRIGGER `afterinsert_Golden_Ball` AFTER INSERT ON `team_trophies` FOR EACH ROW

BEGIN

    DECLARE badgeID INT(10);

   CREATE TABLE tempname AS
     (SELECT AVG(d.fair_play) as AvgPlay , b.tournament_id , d.* FROM
     `team_trophies` a INNER JOIN tournament_scheduling b ON
      a.tournament_id = b.tournament_id INNER JOIN matches c ON b.id
      = .match_id INNER JOIN user_match_result d ON c.id = d.match_id WHERE
      a.tournament_id = 1 GROUP BY d.user_id);

  IF EXISTS (SELECT * from tempname WHERE AvgPlay = (SELECT MAX(AvgPlay) FROM tempname))

  THEN

        SELECT badge_id INTO badgeId FROM badge_master WHERE name  = 'Golden Ball';

        Insert into user_badges(badges_id , user_id , match_id , turf_id ,
           tournament_id , created_date)
        SELECT  badgeID , user_id , match_id , NULL , tournament_id ,
           created_date from tempname limit 1;

   END IF;

   DROP TABLE tempname;

END

MySQL Error : 1422 Explicit and Implicit Commit is not allowed in SP and Trigger

Kindly Help.

(Too much for a Comment...)  (Working backwards...)

LIMIT 1, but no ORDER BY??  You don't care which item you get?

SELECT .. INTO badgeId, but then don't really use it?  Perhaps the subsequent SELECT should have WHERE name  = 'Golden Ball'?

Instead of doing IF EXISTS, try to cause the final SELECT to return nothing.  That would have the same effect, and will probably eliminate the 'need' for a temp table.

Does that IF EXISTS do anything other then see if there are any rows in tempname??  If so please explain.

Is d.user_id the PRIMARY KEY of team_trophies?  If not, the GROUP BY may be improper.

No mention of OLD. or NEW.??

I suspect the INSERT .. SELECT could be simply use the original SELECT, plus an additional WHERE clause based 'Golden Ball', but I am too lost to attempt to rewrite it.


How to retrieve elements from another table in a SQL query? Sorry for my english !!!

I get data from a table wp_posts and now I need to get data from the table wp_postmeta that match the ID captured in wp_posts table.

The data that I need to grab in the table wp_postmeta are product_img1, product_img2, product_img3 as shown in image below.

This is a view of table wp_posts.

I need to show ID, post_title, product_img1, product_img2, product_img3 and my SQL query is:

require("configs/conxao.php");
$datas = $pdo->query("SELECT p.ID, p.post_title, pm.product_img1, pm.product_img2,
                              pm.product_img3
                          FROM wp_posts p
                          JOIN wp_postmeta pm
                          ON p.ID = pm.post_id
                          WHERE p.post_type = 'wpcproduct'
                    ");
$datas->execute();

while($donnees = $data->fetch(PDO::FETCH_ASSOC)){

     How to show this data here?

}

  Can you help me to show this data for ID 103?

SELECT
p.ID,
p.post_title,
ProductImage1.product_img1,
ProductImage2.product_img2,
ProductImage3.product_img3
FROM wp_posts p
cross apply (
        select top(1) pi1.product_img1
        from wp_postmeta pi1
        where p.ID = pi1.post_id
        and pi1.meta_key = 'product_img1'
        ) as ProductImage1
cross apply (
        select top(1) pi2.product_img2
        from wp_postmeta pi2
        where p.ID = pi2.post_id
        and pi2.meta_key = 'product_img2'
        ) as ProductImage2
cross apply (
        select top(1) pi3.product_img1
        from wp_postmeta pi3
        where p.ID = pi3.post_id
        and pi3.meta_key = 'product_img3'
        ) as ProductImage3
WHERE p.post_type = 'wpcproduct'
AND p.ID = 103


Eav model sql select only products with all 3 values? I have an EAV (Entity Attribute Value) based table for smartphones like the following:

What if the user wants to see all phones with
- red color
- steel material

The query is like:

WHERE (attribute_id = color AND value = red)
OR
(attribute_id = material AND value = steel)

In this case the search result would also select product 2 because it has red color but I don't want this, because its material is plastic instead of steel.

You can say group results by product id column having count more than 2 rows per product.

But because it's EAV flexibility, what if the material is a new option for a product, and added to a product after a 1 million values/rows later than its early added values for this product?

I mean not all specs are one after another right? Isn't that going to slow down the search?

So I need to get products only but only with red and steel (only one option like 2. Product in the table above cant be accepted) and it doesn't matter if those options have millions rows between them it still should find all values for given values of a product.

Is there a solution?

To select more than one attribute:

FROM attribute AS color
JOIN attribute AS material
  ON color.content_id = material.content_id
   AND color.attribute_id = 'color' AND color.value = 'red'
   AND material.attribute_id = 'material' AND material.value = 'steel'

So much flexibility you can strangle yourself on the long sql queries it generates :-)

For other solutions see eav


EXISTS and sub-query cost i've just wondered if there's a difference in cost(memory,cpu) of an EXISTS or NOT EXISTS in following queries. Does it make a difference if i select NULL, 1 or columns?

1)

SELECT id
FROM Parent p
WHERE NOT EXISTS
(
   SELECT NULL FROM Child c
   WHERE c.parent_Id = p.id
   AND c.x <> c.y
)

2)

SELECT id
FROM Parent p
WHERE NOT EXISTS
(
   SELECT 1 FROM Child c
   WHERE c.parent_Id = p.id
   AND c.x <> c.y
)

3)

SELECT id
FROM Parent p
WHERE NOT EXISTS
(
   SELECT * FROM Child c
   WHERE c.parent_Id = p.id
   AND c.x <> c.y
)

Option 1 is what i use normally, is there are difference at all or is it optimised to the same?

You should find that each example results in a LEFT SEMI ANTI JOIN with equal costs.

  The Left Anti Semi Join operator returns each row from the first (top)
  input when there is no matching row in the second (bottom) input. If
  no join predicate exists in the Argument column, each row is a
  matching row.

Out of habit I tend to use the syntax in your second example, which you could argue signals intent better than the others.


How to iterate through table and modify entrys in an "if" statement with MySQL What I have is a table containing some temperature readings which often jump back and forth.

+------------+----------+-------------+
| timestamp  | sensorID | temperature |
+------------+----------+-------------+
| 1511020195 | 1        | 25          |
| 1511020196 | 1        | 26          |
| 1511020197 | 1        | 25          |
+------------+----------+-------------+

I'd like to iterate through the table and delete some rows if some conditions are met. Here is some pseudocode of what I am trying to do:

int senID = 1;
int timestamps[] = //SELECT `timestamp` FROM `temperature` WHERE `sensorID` = senID
int actentry = sizeof(timestamps);

while(actentry >= 2)
{
    int acttemp = gettemp(actentry);
    int lasttemp = gettemp(actentry-1);
    int secondlasttemp = gettemp(actentry-2);
    if(acttemp == lasttemp)
    {
        //DELETE FROM `temperature` WHERE `timestamp` = timestamps[actentry] AND `sensorID` = senID
    }
    else if((acttemp == secondlasttemp) && ((acttemp == lasttemp+1) || (acttemp == lasttemp -1)))
    {
        //DELETE FROM `temperature` WHERE `timestamp` = timestamps[actentry] AND `sensorID` = senID
        //DELETE FROM `temperature` WHERE `timestamp` = timestamps[actentry-1] AND `sensorID` = senID
    }
    actentry--;
}

int gettemp(int entry)
{
    return //SELECT `temp` FROM `temperature` WHERE `timestamp` = timestamps[entry] AND `sensorID` = senID
}

Is there a way of executing this directly on my MySQL DB?

I asked before but didn't get an answer. Because of that I wrote this pseudocode to give a better explanation of what I am trying to to.

Thank you for your answers in advance!

What version of MySQL are you using? MySQL version 8 has Window Functions that support last_value().

Shy of using MySQL 8, you have two options,

Write a PROCEDURE that uses cursors in the database
Write a function that uses a cursor in a client side language.

My own preference, if I was to do this task in MySQL would be to write it outside of the server if that was reasonable (the data could be moved off-server), if not (we're talking about millions of rows) I would write a PROCEDURE


Is it possible to modify an existing trigger definition in MySQL? I am wondering if it is possible to update a trigger definition in MySQL. For example, I have a trigger T and I want to add some new functionality to it.

My assumption is that I need to drop and recreate it.

What are the best practices in the database administration for such scenarios?

It sounds like you're asking whether it's possible to alter a trigger in an atomic operation, where, if the new definition fails, you don't lose the old one... similar to CREATE OR REPLACE VIEW, which replaces the view definition if the new definition is valid, but leaves the old one in place, if you can't replace it.

Unfortunately, there's no ALTER TRIGGER or CREATE OR REPLACE TRIGGER in MySQL.

I would suggest that the best practice is to lock the table where the trigger lives, so no rows are impacted with the trigger absent.  Dropping and adding triggers while a table is locked is allowed.

mysql> LOCK TABLES t1 WRITE; -- the next prompt appears once you've obtained the lock
mysql> DROP TRIGGER t1_bi;
mysql> DELIMITER $$
mysql> CREATE TRIGGER ti_bi BEFORE INSERT ON t1 FOR EACH ROW
       BEGIN
       ...
       END $$
mysql> DELIMITER ;
mysql> UNLOCK TABLES;

Update: MariaDB, in version 10.1.4, added support CREATE OR REPLACE TRIGGER to their drop-in replacement for MySQL.

https://mariadb.com/kb/en/mariadb/create-trigger/

Oracle's MySQL as of 5.7 still relies on the solution above.


InnoDB MySQL account usage history I would like to view the account usage history. I know it can be viewed
via my.cnf through the general query log. But it will slow down the usage of the
DB significantly.

Is there a way to mitigate this or is there another way?

It depends on what you want to log.

One solution (taken from here) that does not use the general query log is the following:

CREATE DATABASE admin;

CREATE TABLE admin.connections (id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, connect_time DATETIME NOT NULL, user_host VARCHAR(50) NOT NULL, connection_id INT UNSIGNED NOT NULL);

SET GLOBAL init_connect = "INSERT INTO admin.connections (connect_time, user, connection_id) VALUES (NOW(), CURRENT_USER(), CONNECTION_ID());";

This solution uses the init_connect MySQL variable which stores SQL queries executed each time a user logs in. Now each time a user logs in, it will be logged into the table admin.connections. Be sure to check the permissions of all of your users to ensure that they can insert a record into the table.

Another solution, available only for MySQL Enterprise and that I include here for completeness' sake, is to install the MySQL Enterprise Audit plugin.


Restore a large number of transaction logs I'm using SQL Server 2008 R2, and I need to restore 36 .trn logs to a database. I have restored the first 4 using the restore transaction log functionality in SSMS (right-click database > tasks > restore > transaction log).

Is there a way to restore them all in order using this tool or with a script? The .trn files are in a folder on a remote server. I do not not need to restore all the files in the folder, only ones between specific dates.

I'm looking for a way to automate this so I don't have to manually restore each individual log.

I access the files using an address like the one below.

\\remote server IP\SQL_Backup\LMSQL02\TLog\file.trn

I would suggest to use Restore Gene : Automating SQL Server Database Restores - written by Paul Brewer

  The Restore Gene stored procedure generates the required restore scripts, including the DBCC CHECKDB command, if required. It is a useful tool, by itself; in disaster recovery situations, it can construct a restore script, in seconds. It optionally includes override options for name of the target database and WITH MOVE overrides for data, log file locations, which might help recover lost data by restoring a temporary version of a database to the same instance.

It has both TSQL and PowerShell versions for automation.


Unable to search data fast in postgres table I am using Postgres v 9.5.

I have a table with a column of type JSON. Trying to search data in that JSON column by using the following query:

select * from mytable where  data->'application_form_attributes'->>'5705' = 'ABCD';

To speedup the query I have tried to create an index :

CREATE INDEX myindex1 ON mytable USING btree (base_service_id,(data :: jsonb->'application_form_attributes'));

But I am getting the error:-

ERROR:  index row size 2744 exceeds maximum 2712 for index "myindex1"
HINT:  Values larger than 1/3 of a buffer page cannot be indexed.
Consider a function index of an MD5 hash of the value, or use full text indexing.
SQL state: 54000

mytable structure is:

  department_id integer,
  org_name character varying(250),
  base_service_id integer,
  service_id integer,
  service_name character varying(250),
  application_id integer NOT NULL,
  appl_ref_no character varying(50),
  submission_date timestamp without time zone,
  submission_mode_id integer,
  submission_mode character varying(150),
  submission_location character varying(250),
  data json,
  execution_data json,
  pending_task_json json,
  max_current_process_id integer,
  appl_status character(1),
  due_date timestamp without time zone,
  payment_mode_name character varying(200),
  version_no integer,
  appl_amount numeric(18,2),
  coverage_location_id integer,
  location_id integer,
  location_type_id integer,
  payment_mode_id integer,
  applied_by character varying,
  kiosk_registration_no character varying(50),
  last_task_at character varying(100),
  current_task_pending character varying(500),

  CONSTRAINT pk_table1_json PRIMARY KEY (application_id)

Need solution so that the search could be made fast.

Update :

My index that created successfully:

CREATE INDEX concurrently appl_processingjson_data_idx2 ON application_processing_json (CAST(md5(initiated_data :: text) AS uuid));

EXPLAIN output:

select * from application_processing_json where base_service_id = 721 and initiated_data->'application_form_attributes'->>'5734' = '9898989822';

"Seq Scan on application_processing_json (cost=0.00..109162.32 rows=2342 width=1998)"
" Filter: ((base_service_id = 721) AND (((initiated_data -> 'application_form_attributes'::text) ->> '5734'::text) = '9898989822'::text))"

Now problem is that this index is not being used in searching. Rather a sequential scan is being used as described in EXPLAIN.

You get that error because data->'application_form_attributes' is too large to fit in an index entry.

You'd have to index the whole left side of the expression:

CREATE INDEX ON mytable (data->'application_form_attributes'->>'5705');

Of course, if '5705' is not a constant, that won't get you far.

With your index, you could change the query to:

... WHERE data->'application_form_attributes' ? '5705'

but since you cannot create the index, that won't help you.

I don't think that there is an easy way out. Perhaps it would be best to "normalize" the schema, pull those values you want to search for out of the JSON and model it as several tables with foreign key relationship. Then your query will be simpler and perform better, and indexing won't be a problem.


How to change the default timezone in Amazon Redshift? Setting a timestamp column to SYSDATE by default, stores it as UTC. Is it possible to change the timezone so SYSDATE stores dates and times to a different timezone?

So far, I've checked the SET command but I'm not sure if it is possible to use it to change the timezone.

You can change the timezone with a SET command.
For example:

SET TIMEZONE = 'America/Los_Angeles';

For more info please find the relevant AWS documentation here.

The challenge is that setting the environment seems to hold for maybe the leader node but doesn't get transferred along to the compute node that may end up writing your table.
See the comments here for more info on the issue:

Until the folks at Amazon fix that issue, you can alter the user timezone with

ALTER USER dbuser SET timezone to 'America/Los_Angeles';

or convert the timezone each time you make the timestamp like so:

load_dttm TIMESTAMP DEFAULT convert_timezone('America/Los_Angeles', sysdate) NOT NULL


Using MySQL workbench, how to prevent new foreing key columns when modeling I'm reverse-engineering a database and specifying relationships.  MySQL workbench wants add foreign-key columns as I do this.

While I see the benefit of this, I would like to turn off this default behavior since the columns already exist (just using a different name than what Workbench is expecting).

I'm using Mysql 5.5.49 and Workbench 6.3.6 on Ubuntu if that makes any difference.

When you create a relationship you can use one of the tools reachable with the buttons in the vertical toolbar (or the hotkeys 1-6). The last one () is what you are looking for. It let's you pick the columns to be used for a relationship instead of creating new columns for it.


Large MySQL table that need to index some fields? I would like to know some hints about how to index some columns of a MySQL table that has about 28 million rows.

Maybe I could recreate the table indexing it properly, then import these rows?

Could someone help me with this please.

If you just wish to create a single index, CREATE INDEX would be the way to go.

However, if you wish to setup your table with multiple indices, setting up a new empty table and then copying from your old to your new table is IMO a more practical solution, since you can get everything ready and then simply run the insert when it's convenient.


Efficiently search for a prefix range in SQL Server Let's say I want all records with a prefix between two user-supplied alphanumeric values of the same length. Thus, if the user enters A010 and A025, I want to return A0101, A0200 and A0259.

What I've tried:

Obviously, I cannot use WHERE myText BETWEEN @from and @to, because that won't return A0259.
Technically, WHERE LEFT(myText, @len) BETWEEN @from AND @to, would be exactly what I want, but that kills SARGability.
I could use WHERE myText BETWEEN @from and @to + 'zzzzzzzzzzz', but that is an ugly hack and potentially error-prone. (Is z really the highest character? Did I use enough "padding" characters?)
I could "explode" the range and search for every possible prefix, e.g. WHERE (myText LIKE 'A01%' OR myText LIKE 'A020%' OR myText LIKE 'A021%' ...), but that's a lot of work.

Is there some smart solution that I've missed? I'll probably use option 3 to solve the problem (since I know about the length and the range of allowed characters), but I'm curious about the general case.

If you use the >= and < operators you can return the required range by "increasing the second argument" by 1. Technically, replacing the last character ('5') with the next character ('6') in the character set.

Below is a query that returns all the rows in your range of A010 and A025. To get this I pass 'A026' as the second argument. The expression < 'A026' includes the value 'A0259456546'.

DECLARE @Test TABLE
(
    Val NVARCHAR(100)
) ;

INSERT @Test
VALUES ('A0004534543'),
       ('A01034950834'),
       ('A020043553'),
       ('A0259456546'),
       ('A0264565464') ;

SELECT  *

FROM    @Test

WHERE   Val >= 'A010'
        AND Val < 'A026' ;


Why using innodb_file_per_table? There are many articles exaggerating (IMHO of course) the need for innodb_file_per_table. I understand that with innodb_file_per_table, there should be a better control over the individual tables; like backup each table separately. However, the claim for better performance is questionable.

In my test, there is no difference in performance of innodb_file_per_table and ibdata1 for a database of 60GB. Of course, it was a simple test with normal queries, and the situation can be different for complicated queries in real life (this is the reason that I asked this question). 64-bit linux with ext4 can effectively handle large files.

With innodb_file_per_table, more disk I/O operations are needed; and this is significant in complicated JOINs and FOREIGN KEY constraints.

Tablespace is shared on single ibdata; how dedicated tablespaces for separate tables can save disk space? Of course, it is easier to free table space for each table with ALTER, but it is still an expensive process (with table lock).

QUESTION: Does innodb_file_per_table has an effect on a better performance of mysql? If yes, why?

I don't think it's a matter of performance but of management.

With separate file per table, you can store different databases in different storage devices for example.

You can deal with the case of very large databases in file systems that can't handle big files (at least postpone the problem until one table reaches the file size limit).

You don't have uncontrolled tablespace growth. If you have some big tables that you drop, the ibdata file stays small.

One aspect that may have some effect on performance is the fragmentation of table data and indexes, which will be limited per table. But that needs testing to be confirmed.


MySQL: Assign rankings for a large table of results In our games we have a quartely league system. Every 3 months, the current season ends and all scorings of the current quarter are archived into a table called archive_league. Each entry represents a user and his score for a given league quarter. It comes with the following fields:

id - the id of an entry
uid - the id of a user
rounds - the number of games the user played in the given quarter
score - the score of the user for the given quarter
rank - the position of the player, descending by score, in the given quarter
date - the date of the quarter this entry represents

My goal is to go through all entries for a given date and assign the field "rank" for results. For example the player with the highest score should receive rank = 1, 2nd highest score rank = 2 and so on. If 2 players share the same score, they should receive the same rank (olympic scoring).

Example:

player x with score 528 receives rank 7
player y with score 528 receives rank 7
player z with score 529 receives rank 9

I am currently achieving this goal with this query:

UPDATE archive_league
        LEFT JOIN
    (SELECT
        t.id,
            (SELECT
                    COUNT(id) + 1
                FROM
                    archive_league x
                WHERE
                    x.score > t.score
                        AND x.date = :quarter) AS new_rank
    FROM
        archive_league t) AS temp USING (id)
SET
    rank = new_rank
WHERE
    date = :quarter;

The problem is, however, with some hundreds of thousands of entries, this query runs for a couple of days, even though I have created indexes for the WHERE conditions. How can I optimize this query to run faster?

Materialize it

It may be more performant to make a real (materialized) temp table instead of the inline subquery.

-- Cleanup any old temporary table structure
DROP TEMPORARY TABLE IF EXISTS temp_ranks;

-- Initialize a new temporary table. This will copy your same data types.
-- Structure only, no data.
CREATE TEMPORARY TABLE temp_ranks
  AS SELECT id, rank AS new_rank
     FROM archive_league WHERE 0=1;

-- Apply a PK index to the temp table for performance.
ALTER TABLE temp_ranks ADD PRIMARY KEY (id);

-- Compute ranks, store in temp table.
INSERT INTO temp_ranks
SELECT t.id,
      (SELECT COUNT(*) + 1
          FROM archive_league
          WHERE score > t.score
            AND date = :quarter) AS new_rank
    FROM archive_league t;

-- Apply to original table the materialized (temp) ranks.
UPDATE archive_league
LEFT JOIN temp_ranks USING (id)
SET rank = new_rank
WHERE date = :quarter;

If we were using Oracle, I wouldn't expect this technique to make a difference (as Oracle's optimizer is pretty smart).  However, MySQL's optimizer has some weak spots, one of which is that SELECT ... JOIN is pretty optimized, will choose best algorithm at run time (merge, hash, nested loop), yet UPDATE ... JOIN and DELETE ... JOIN lack the same optimization.  Such an optimization is not impossible, but nobody has written the C code to make it so.  If you're brilliant with programming databases in C, you are welcome to write an optimization and submit as a patch to MySQL (or MariaDB).

Reference from the manual https://dev.mysql.com/doc/refman/5.7/en/subquery-optimization.html:

  A limitation on UPDATE and DELETE statements that use a subquery to modify a single table is that the optimizer does not use semi-join or materialization subquery optimizations. As a workaround, try rewriting them as multiple-table UPDATE and DELETE statements that use a join rather than a subquery.


Is it possible to retrieve a history of the remote connections to the MongoDB including the IP addresses and the executed commands? For some reasons I disabled the user access control and somehow all collections have been wiped out. So I want an activity log to find out how this happened

I need a list like this:

|  Date/Time           |  IP           |  Command           |
|----------------------+---------------+--------------------|
|  2018/1/1  18:20:50  |  127.0.0.1    |  show collections  |
|  yyyy/m/d  hh:mm:ss  |  x.x.x.x      |  executed command  |

OS: Ubuntu 18.04
MongoDB: 3.6.3

If you haven't configured any extra logging, profiling, or auditing in advance, the default MongoDB log level will only include limited information such as connections, slow commands, and database drops rather than details of every command that was run. Logging every command will add significant write overhead for an active deployment.

However, assuming you have retained sufficient logs (and haven't suppressed connection information with the quiet logging mode), you should be able to identify when your server was restarted without authentication as well as the remote IPs that connected. You'll have to grep or parse available information out of the MongoDB log file(s).

Some example log search strings that might help construct a timeline of events:

[initandlisten] options -- shows the configuration options (such as authentication) used when your mongod server is started (or restarted).
connection accepted from --  includes the IP address for remote clients as well as a connection ID (for example: [conn123]) that you can use to find related log lines.
received client metadata -- provides (optional) client metadata (such as driver name and version) for a new connection.
dropDatabase -- indicates a database drop command was issued.

For a quick summary of IPs that connected you could also use mloginfo --connections.

To mitigate future issues, please review the Security Checklist in the MongoDB documentation.


Can i pair multivalued attribute with an entity? If i have the following scenario.

  Every doctor can diagnose a disease for different patients. A diagnose
  holds a date and disease(multi valued attribute). So the patient has
  testing that need to be done with investigation, the investigation
  holds type, description and title and testing holds date. For each
  investigation we know the disease that need to be investigated, hence
  one disease can have multiple investigations.

This is the sentence where i have difficulties

  For each investigation we know the disease that need to be investigated, hence one disease can have multiple investigations.

I have the following diagram so far. My question is can i connect multivalued disease with Investigation entity? I am new to EER diagrams

Yes, you can.

You could create a table for Diseases:

TABLE disease
-------------
disease.id (PK)
disease.name

Then you can model Diagnosis (note that's a junction table) this way:

TABLE diagnosis
---------------
diagnosis.id (PK)
diagnosis.date
diagnosis.doctor_id (FK)
diagnosis.patient_id (FK)
diagnosis.disease_id (FK)

So you can add a Disease to an Investigation:

TABLE investigation
-------------------
investigation.id (PK)
investigation.type
investigation.title
investigation.description
investigation.disease_id (FK)

And finally, here's the table for Testing:

TABLE testing
-------------
testing.id (PK)
testing.date
testing.investigation_id (FK)
testing.patient_id (FK)

The Testing can as well be included as a field in Investigation instead of having its own table, unless the exercise requires it.

Then there are the tables for Doctor and Patient which you already modeled.

This is the description of the tables, from it you can easily create the ER diagram, remembering that a FK on a table connects to the PK on another table.


Galera Mariadb Multi-Master Replication I am trying to setup a cluster of 3 servers in 3 different locations; Dallas-US, London-UK, Mumbai-India. On each location I have setup a webserver and db server. On db server I have configured Galera Mariadb Multi-Master cluster to replicate db among all three servers. My each webservers are connected with local IP to their regional db server. I am expecting that my Dallas webserver will fetch db records from Dallas db server only; London webserver from London db server only and Mumbai webserver from Mumbai db server only. So I am having this setup
Dallas: 1 Webserver, 1 db server connected to webserver with local IP
London: 1 Webserver, 1 db server connected to webserver with local IP
Mumbai: 1 Webserver, 1 db server connected to webserver with local IP
Everything is working well but I have found that mysql query takes much time above 100s while fetching record. I have tried Mariadb with single instance and its fetching data within 5s.
What am I doing wrong? Is it possible to setup something like this where every db server will geo-replicate its data but every webserver will fetch data only from its local db server?

The performance is going to be atrocious with your setup, especially in writes because every write has to be acknowledged by at least one remote node. You will also get an astronomical number of deadlocks.
If you need geo-replicated synchronous redundancy, you would be batter off with with DRBD for block level replication. Performance will still be terrible on commit latency but not as terrible as with Galera.
You need to rethink your requirements and your approach to fulfilling them.


Grant sysadmin permissions to 'NT AUTHORITY\SYSTEM' For SQL Server 2012 and above, as What's New in SQL Server Installation states:

  BUILTIN\administrators and Local System (NT AUTHORITY\SYSTEM) are not automatically provisioned in the sysadmin fixed server role.

In my case this has become a problem: I have a WiX installer which uses a Custom Action C++ code to setup database data (create the database, views, procedures, data etc.). Custom Action runs as user NT AUTHORITY\SYSTEM, but this account does not have permission to execute CREATE DATABASE scripts.

Is it correct to give sysadmin role to NT AUTHORITY\SYSTEM while installing software? Or maybe there is a better solution?
If it is correct, is it possible to find the proper user name for NT AUTHORITY\SYSTEM? I need this name in order to change permissions*. In different locales this name is different, for example, NT AUTHORITY\SYSTEM or NT AUTHORITY\СИСТЕМА.

Adding NT AUTHORITY\SYSTEM to the sysadmin role:

IF NOT EXISTS
(
    SELECT name
    FROM master.sys.server_principals
    WHERE IS_SRVROLEMEMBER ('sysadmin', name) = 1
    AND name LIKE 'NT AUTHORITY\SYSTEM'
)
EXEC master..sp_addsrvrolemember
    @loginame = N'NT AUTHORITY\SYSTEM', @rolename = N'sysadmin'

Searching sys.server_principals for name LIKE 'NT AUTHORITY\%' will not work because sometimes there will be more than one NT AUTHORITY\... user. I encountered a case like that on one of my virtual machines.

You can get the name of well-known security identifiers (SIDs) using a PowerShell script:

$objSID = New-Object System.Security.Principal.SecurityIdentifier ("S-1-5-18")
$objUser = $objSID.Translate( [System.Security.Principal.NTAccount])
$objUser.Value

Personally I never had to do this from C/C++ code. See the MSDN article about the WINAPI or System.Security namespace for managed code.

There is a compact example in the Stack Overflow Q & A WinAPI LookupAccountSid from an .evt file by Remy Lebeau:

static const DWORD MAX_BUFF_SIZE = 256;

wstring userNameFromSid(SID userSid, wstring computerName)
{
    wchar_t buffName[MAX_BUFF_SIZE];
    DWORD buffNameSize = MAX_BUFF_SIZE;
    wchar_t buffDomain[MAX_BUFF_SIZE];
    DWORD buffDomainSize = MAX_BUFF_SIZE;
    SID_NAME_USE SidType;

    if (LookupAccountSid(!computerName.empty() ? computerName.c_str() : NULL, &userSid, buffName, &buffNameSize, buffDomain, &buffDomainSize, &SidType))
    {
        return buffName;
    }

    /*Here some code to print error in a Message box*/
    return L"";
}

Is there any problem placing NT AUTHORITY\SYSTEM in the SQL Server sysadmin role when installing software from a reliable source? There should be no problem, I hope, but who knows...


Oracle Streams Setup Error I am running Oracle 11g on a Linux box.  I am trying to run the following command to set my streams user grants and other authorizations:

BEGIN
  DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
    grantee          => 'nwisxfer',
    grant_privileges => false,
    file_name        => 'grant_strms_privs.sql',
    directory_name   => '/usr/oracle');
END;
  8  /
BEGIN
*
ERROR at line 1:
ORA-23481: unexpected name string "/usr/oracle"
ORA-06512: at "SYS.DBMS_STREAMS_AUTH", line 1621
ORA-06512: at line 2

A Google search suggested that I run these scripts:

/usr/oracle/app/product/11.2.0/dbhome_1/rdbms/admin/catrep.sql
/usr/oracle/app/product/11.2.0/dbhome_1/rdbms/admin/utlrpt.sql

This did not eliminate the error.

Any advice on how to proceed?

You need to create an Oracle DIRECTORY first.

CREATE DIRECTORY MYSTREAMSDIR as '/usr/oracle';

GRANT READ, WRITE ON DIRECTORY MYSTREAMSDIR TO YOURUSER;

BEGIN
  DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
    grantee          => 'nwisxfer',
    grant_privileges => false,
    file_name        => 'grant_strms_privs.sql',
    directory_name   => 'MYSTREAMSDIR');
END;

The grant isn't needed if you create the directory logged in as the streams admin user (usually strmadmin).


PostgreSQL Master server act as a slave for 2nd DB on new server I have a strange situation I'm are trying to resolve. I've googled around but I haven't found a clear answer so I thought I'd pose it to the good people at DBA on StackEx to get a second opinion.

We have a Master server(server1) that has a Hot Standby attached(server2). This runs great (using SR).

We've ended up acquiring a new PostgreSQL server(server3) that has it's own DB and software that regularly adds INSERTs into a table. We need to get that table's INSERTs over to our Master(server1). I am trying to figure out how best to do this using PostgreSQL only. All servers are in the same warehouse now and are on the same LAN(connection reliability/latency is excellent).

My questions are:

Is there a way to have my Master(server1) act as a slave to server3 while still being the Master for the HS(server2)?
If that is possible, will the INSERTs that are replicated to the Master(server1) from server3 likely bring with them a serious performance hit? I would be using asynchronous replication.
I'm the only one here with any DB experience so I have no one to bounce ideas off of... Am I missing anything obvious? Is there a better way to get the table receiving the INSERTs on server3 over to server1? I can't decommission server3 for various reasons.

As a note: I have looked at/considered the following other options:

SQLWorkBench - I'd like to avoid adding JRE to the servers if possible.
Batch SELECT/COPY to an output file on server3 that is dropped onto a file share owned by Master(server1) that it then picks up and works on.
Because of the steady stream of INSERTs pg_dump is not a solution.

After a lot more digging I now know a couple of things:

A PostgreSQL server can't be a master of one DB and a slave for a second DB. In order to have a master DB and a slave DB on the same physical hardware with PostgreSQL I need to run two separate PostgreSQL services/clusters.
Updates are actually more expensive than Inserts because Updates have to seek out the row first. Also always try to append your Insert(or use a clustered index if necessary).

Thanks


Does MySQL exectution plan depend on available indexes? Does MySQL choose an execution plan for a given query taking into account what indexes are available, or does it first choose an execution plan and then uses indexes if they are available?

Motivation: I want to decide which indexes would be useful. I have a number of typical queries. So I can imagine two possible strategies:

If the plans do not depend on available indexes: see the execution plans of those queries and add indexes useful for these plans, or
If the plans depend on available indexes: add all possible indexes (a lot!), see the plans for the queries, and remove unused indexes.

My DB is not very large, so I can afford playing with indexes. I currently use InnoDB, but I can switch to MyISAM or another if needed.

MySQL's optimizer looks only at what indexes are available.

There is an exception in 5.6:  If you have a subqueries such as
FROM ( SELECT ... ) JOIN ( SELECT ... ) ..., there are no indexes on the temporary tables that are created.  This used to lead to terrible performance.  Now, the optimizer will try out various indexes, and create the best one for the tmp table.  I suspect it only looks for single-column indexes, not "compound" indexes

InnoDB is getting the most attention these days.  MyISAM does not have any tricks up its sleeve.

Try out my Cookbook for creating the best index, given a SELECT.  It may save you some false starts.


Use multiple columns for location or supertype/subtype? Given an Equipment entity, it has a location, generically called Whereabouts.  This could be a reference to:

a User entity (the employee who will use and be responsible for the eqipment for the next week).
a Vendor entity (if the equpment is getting serviced).
a StorageLocation entity (if the equpment is put away).

The piece of equpment can only be in one place at a time, even if that place is not a physical location.

Is it better:

to have Equipment with three columns (UserId, VendorId, StorageLocationId) such that one of these columns must be not null while the other two must be null (enforced by a constraint)?
or to have Equipment with a WhereaboutsId that references some type supertype table that then links to User, Vendor, and StorageLocation as subtype tables, even though they share no other common attributes?
or something else?

My approach to design is to think about the what-if's:

How would the design need to change to add a 4th type?
How would it need to change to store a common attribute?
How would it need to change to store an attribute for only one of the types?

I think after going through the process of answering the questions above for the proposed designs, it will be obvious that using a supertype/subtype structure is definitely the way to go (I dare say this is a best-practice for the situation).

There are things to be concerned about, of course -- for example, ensuring that for a given supertype, a matching record must exist in exactly 1 subtype table. With a well-written stored procedure to do "transfers" between types (which is something I assume you'll need), this should be pretty straightforward to implement.


How does the actual restoration works in SQL Server internally? Suppose my Database DB1 lost some data, but I have a Full, Differential and then followed by Transaction log backups.

Till full\Differential backup I can understand it is like the snapshot of the database, but after this, how T-log Backup works, if we have point in time recovery.

Means like all the committed transaction from the T-log Backup are taken in a sequence and then after it is performed again on the Database (DB1).

Is this right?

If yes, Is this same is followed during log-shipping, i.e, when logs are shipped from one end to other.

Thanks in Advance.

I think this is the core methodology of database backup and restoration and the whole purpose of taking backup is to be able to restore it to point in time in case of any untoward incident like disk failure, server failure or database crash etc.

Lets understand them one by one.

Full Backup

A full backup, as the name implies, backs up everything. It is the foundation of any kind of backup. This is a complete copy, which stores all the objects of the database: Tables, procedures, functions, views, indexes etc. Having a full backup, you will be able to easily restore a database in exactly the same form as it was at the time of the backup.

A full backup creates a complete backup of the database as well as part of the transaction log, so the database can be recovered. This allows for the simplest form of database restoration, since all of the contents are contained in one single backup.

A full backup must be done at least once before any of the other types of backups can be run—this is the foundation for every other kind of backup.

Differential Backup

A differential database backup is the superset of the last full backup and contains all changes that have been made since the last full backup. So, if there are very few transactions that have happened recently, a differential backup might be small in size, but if you have made a large number of transactions, the differential backup could be very large in size.

As a differential backup doesn’t back up everything, the backup usually runs quicker than a full backup. A differential database backup captures the state of the changed extents at the time that backup was created. If you create a series of differential backups, a frequently-updated database is likely to contain different data in each differential. As the differential backups increase in size, restoring a differential backup can significantly increase the time that is required to restore a database. Therefore, it is recommended to take a new full backup, at set intervals, to establish a new differential base for the data.

Differential backups save storage space and the time it takes for a backup. However, as data changes over time, the differential backup size also increases. The longer the age of a differential backup and larger the size and at some point in time it may reach the size of the full backup. A large differential backup loses the advantages of a faster and smaller backup as it requires the full backup to be restored before restoring the recent differential backup. Typically, we would restore the most recent full backup followed by the most recent differential backup that is based on that full backup.

Transaction Log Backup

The log backup, as its name implies, backs up the transaction logs. This backup type is possible only with full or bulk-logged recovery models. A transaction log file stores a series of the logs that provide the history of every modification of data, in a database. A transaction log backup contains all log records that have not been included in the last transaction log backup.

It allows the database to be recovered to a specific point in time. This means that the transaction log backups are incremental and differential backups are cumulative in nature. If you want to restore the database to a specific point in time, you need restore a full, recent differential, and all the corresponding transaction log records which are necessary to build the database up to that specific point, or to a point very close to the desired point in time, just before the occurrence of the accident that resulted in the data loss. This series of modifications is contained and maintained using LSN (Log Sequence Number) in the log chain. A log backup chain is an unbroken series of logs that contain all the transaction log records necessary to recover a database to a point in time. A log chain always starts with a full database backup and continues until for reason it breaks the chain (for example, changing the recovery model of database to simple, or taking an extra full backup), thus by preventing log backups from being taken on the database until another full (or differential) backup is initiated for that database.

For having more clarity, you can refer below image:

Below image will show how to use SSMS for point in time restoration once you are done restoring Full, Differential and prior log backups:

Log shipping works on same concept however Full and differential backup restoration is part of initial set up whereas log backup and restoration works on the LSN part. Each log backup at primary servers maintains last LSN, same is compared at Secondary server and applied accordingly.

As far as internals of database restoration is concerned, you may find more details of it at these links:

Basically they go through three phases in case of any failure or non-clean shutdown:

Analysis Phase analyzes the transaction log to determine what is the last checkpoint, and creates the Dirty Page Table (DPT) and the Active Transaction Table (ATT). The DPT contains records of pages that were dirty at the time the database was shut down. The ATT contains records of transactions that were active at the time the database was not cleanly shut down.
Redo Phase rolls forwards every modification recorded in the log that may not have been written to the data files at the time the database was shut down. The minimum log sequence number (minLSN) required for a successful database-wide recovery is found in the DPT, and marks the start of the redo operations needed on all dirty pages. At this phase, the SQL Server Database Engine writes to disk all dirty pages belonging to committed transactions.
Undo Phase rolls back incomplete transactions found in the ATT to make sure the integrity of the database is preserved. After rollback, the database goes online, and no more transaction log backups can be applied to the database.

When we are restoring database from Full and Differential backup and Log backup, without using recovery command i.e. with NORECOVERY 2nd phase will be performed which is redo upto the last committed transactions. When you issue command for recovery i.e. in the last log backup restoration or point in time restoration, undo will be performed and no more log backups can be applied afterwards.

First phase i.e. analysis phase is when database comes up from the failure or unclean shutdown and won't be applicable in case of backup restoration.

Hope above helps.