Want to get executed query users log in SQL-server Yesterday one of my team members has truncated a few tables, but who have truncated those tables is still a mystery.

Can I have user based query log with the timing of execution? Or something where I can enable that functionality in future??

Currently, I am using SQL-Server 2017 on Linux.

Thank you in advance.

Yes, we had a similar issue as well--for payroll data! Try the default trace as shown in this blog (it appears that this is available for Linux as well):

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/80938ab2-28a4-46eb-8b09-84ab9ae22079/how-to-find-the-user-who-performed-a-truncate-statement?forum=transactsql

Linux default trace location:

http://www.dharmendrakeshari.com/main-file-directory-structure-sql-server-linux/


Check table before delete a constraint In SQL Server I used the following code to drop a table with a constraint:

IF OBJECT_ID('EMPLOYEES') IS NOT NULL
  BEGIN
         ALTER TABLE EMPLOYEES DROP CONSTRAINT EMP_DEPT_FK
         DROP TABLE EMPLOYEES;
  END

How can I accomplish the same thing in Mysql?

You can use information_schema to check if the table exists but in MySQL there is a simpler method:

DROP TABLE IF EXISTS employees ;


Changing the use of GETDATE() in the entire database I need to migrate an on-premises SQL Server 2017 database to an Azure SQL database, and I'm facing some challenges since there's quite a bit of limitations to go through.

In particular, since an Azure SQL database works only in UTC time (no time zones) and we need the local time, we have to change the use of GETDATE() everywhere in the database, which has proven to be more work than I anticipated.

I created a user defined function to get the local time that works correctly for my time zone:

CREATE FUNCTION [dbo].[getlocaldate]()
RETURNS datetime
AS
BEGIN
    DECLARE @D datetimeoffset;
    SET @D = CONVERT(datetimeoffset, SYSDATETIMEOFFSET()) AT TIME ZONE 'Pacific SA Standard Time';
    RETURN(CONVERT(datetime,@D));
END

The issue I'm having trouble with is to actually change GETDATE() with this function in every view, stored procedure, computed columns, default values, other constraints, etc.

What would be the best way to implement this change?

We are in the public preview of Managed Instances. It still has the same issue with GETDATE(), so it doesn't help with this problem. Moving to Azure is a requirement. This database is used (and will be used) always in this time zone.

Use the SQL Server tool to export the database objects definition to a SQL file which should include: tables, views, triggers, SPs, functions, and so on
Edit the SQL file (make a backup first) using any text editor that allows you to find the text "GETDATE()" and replace it with "[dbo].[getlocaldate]()"
Run the edited SQL file in Azure SQL to create your database objects...
Execute the migration of data.

Here you have a reference from azure documentation: Generating Scripts for SQL Azure


Hyphen in my computer's name causing T-SQL error I’m a developer, not a DBA (which is showing, I’m afraid). I am trying to run Report Builder 3.0 with SQL Server 2014 Express on my home computer (named John-PC) and cannot run my reports.

I accidentally created a user/login combo of user = John-PC and login = John-PC\John. When I try to delete the entry with:

Drop Login John-PC\John

I get an error:

  Incorrect syntax near '-'.

I think the problem is the hyphen in my computer's name.

Is there a way to get around the syntax error?
Is there another way to change or drop the user (I tried dropping from sys.server_principals but received an error that I couldn’t make ad hoc changes).
Can I give Report Builder a new user/login name somehow?
If none of the above, can I change the name of my computer to John_PC or will that create a host of other problems that I can’t even imagine?

When you have special characters in a name put []'s around it to let SQL know that it's an identifier.  This is also how you manage special characters.

So in your case

Drop Login [John-PC\John]


Reasons to use covering indexes I was asked "when do we need to bring covering indexes to play, and why?" by a DBA.

My explanation was:

  It allows engine to pull all of the relevant data directly from the values in the indices themselves.

My explanation was not satisfactory to the DBA that asked me this question. What other details are relevant?

I know difference between clustered vs. non-clustered indexes

Clustered Index

This index is the table.  The index information is stored in the b-tree pages while all of the other columns (those not in the index) are stored at the leaf level of the index.  All other indexes will have a link back to this one and will always include the indexed columns of the clustered index at their leaf level.  This is because if SQL uses a non-clustered index and needs additional information it will use that value to look back into the clustered index for the additional index.  There can be only one clustered index (obviously) because as I said above it actually is the table.

Non-Clustered Index

The indexed values are also located in the b-tree pages but in this case the only data located at the leaf level is the link back to the clustered index and any columns specified by the INCLUDE clause.  In the case of a UNIQUE index there may also be an additional, virtual, column stored at the leaf level called the uniqueifer.

Covering Index

As @KrisGruttemeyer said this is a functional term not structural (like the other two).  This is just a non-clustered index that contains all of the required columns (either in the index or at the leaf level) to satisfy the query. Don't forget that all indexes include the clustered index columns so if you have a query that requires Cols1-4 and Id (the clustered index) and you only have an index that is indexed on Col1 and includes Col2, Col3 and Col4 it will still be covering because the Id column is going to be included since it's the clustered index. Edit to answer the Why part of the question: Because you don't have to make that extra lookup to the clustered index a covering index can be much faster.  The smallest index that fits the query and covers it will probably be the fastest.  Of course you also have to take into account the overhead of having indexes vs the benefit you get for how many queries but that goes way beyond what can be put here.

Just for completeness Heaps

A heap is simply a table that does not have a clustered index.  The page layout is somewhat different from a clustered index but that is beyond the scope of this question.

And because I always include this when talking about Clustered Indexes
The Primary Key

The primary key is a special case of a unique index.  There can be only one and it may be clustered or not.  It has nothing to do with the clustered index but a lot of people get confused and think the primary key is always clustered.


LoadBalancing JDBC DataSource Connect without URL When using JDBC I like the new DataSource-way to establish the connection. Further I like the isolated set-Methods for the properties like setUser, setPassword, setDriverType, setServerName, setNetworkProtocol, setDatabaseName, setPortNumber and so on.
The question is (esp. for the use with Oracle), if there is a way to set up LoadBalancing with two or more servers without specifying the URL directly with

setURL("jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=dbsrv1)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=dbsrv2)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=dbpool)))");

for example.

Thin client can also use alias defined in tnsnames.ora. In java call set -Doracle.net.tns_admin=...
 http://docs.oracle.com/cd/B19306_01/java.102/b14355/urls.htm


How to predict new disk allocation? I understand that MongoDB pre-allocates disk space, but the disk allocation I'm seeing is growing much faster than I'm anticipating.

For example, I had a database using up 8GB of actual disk space. Today a new 2GB file was pre-allocated, bringing it up to 10GB on disk. I didn't expect this, because as it looked like my data (including wasted space) would comfortably fit.

Doing db.stats() I can see the dataSize for this database is ~2G, indexSize ~500M. The storageSize is ~4GB, so even with a lot of fragmentation it's still nowhere near the disk space now allocated.

What I'm trying to understand is why the 4GB wasn't happily contained in the first 6GB of files. Why would data have been written to the last 2GB of empty space, causing a further 2GB to be allocated?

Using the output from db.stats is it possible to predict with any certainty what data size will result in new disk allocation?

For detail: I'm running MongoDB 2.6 as a replica set, not sharded. My database contains thousands of collections, but each is not very large.

Mongo always allocates next file when perform a first write to last file.
So when collection is created it has 64MB allocated (using small files option)  in first file and mongo allocates 128MB in next chunk.

When collection grows and last file has 2GB, then when document is added to that file, next 2GB file is allocated.

file allocation sizes
e.g. 64MB (small files), 128MB, 256MB, 512MB ... 2GB - this is max size and every new one will be 2GB.)


Should I pause mirroring when I reboot the witness server? So I know that when patching the two main database servers (SQL Server for the record) you do the mirror, failover and then when it's all caught up do the new mirror, but what about the witness box? Is it safe just to patch and then restart it and (in theory) it will just pick up again?

You must do this in order if you want to update the Principal, Mirror and Witness:

Switch from high-performance mode to high safety mode (if you are using this mode)
Remove the Witness
Upgrade the Witness (anytime between 2 and 9, see below)
Upgrade the Mirror(s)
Wait until Mirror(s) is(are) synchronized and manually fail Primary over
Upgrade the Primary
Upgrade the Witness (if not already done between 2 and 9)
Switch back to high-performance mode (if required)
Add the Witness


postgres partition data record assignment It is possible in PostgreSQL 11 to display the partition table for each row in the table? Well, it's clear that the assignment of partitions is always known by the declarative definition but is there any way to get this information programmatically?

This is possible by using the system column tableoid:

select *, tableoid::regclass as partition_name
from the_table;

Online example


Where do I use T-sql for SQL Server? I have used SQL Server a fair amount but always with SSMS.  I would like to be able to use all the capabilities and understand the environment better but I do not know where I use T-sql.  I have tried a few tutorials but they seem to gloss over this simple but important part; where do I input these commands?  Is it command line?  Is it some command line type GUI that should have installed with the SQL Server installation?  In this post (https://www.tutorialgateway.org/sql-attach-database/), there is a good example of just jumping into the commands but not specifying where these commands are input.  Thank you for any help you can give me.

where do I input these commands? Is it command line?

You can use these commands in a variety of tools that connect to SQL Server these include :

SQL Server Management Studio.
SQLCMD (Command line tool)
SQL Operations Studio, (Now SQL Azure Data Studio)
Power Query tool in Excel.
Powershell
SQL Developer
Toad for SQL Server

Pretty much any tool that allows you to connect and issue SQL commands.


Correct way to uninstall SQL Server 2008 ( or above ) Since University I've been facing this problem...

How do I uninstall SQL Server?

What I've done is delete everything in "control panel" related to SQL Server.

But, is there a right way to uninstall everything?

That's pretty much all there is to it. You may want to backup or transfer your database files/backups before the un-installation process, but getting rid of the application is that simple.

As per Mirosoft Support KB 955499,

  Open the Programs and Features item in Control Panel.To do this, click Start, type appwiz.cplin the Start Search box, and then click
  appwiz.cpl in the Programs list.
  Select the SQL Server component to
  uninstall, and then click Change/Remove. This will start the SQL
  Server Installation Wizard.
  Setup Support
  Rules will run to verify your computer configuration. To continue,
  click Ok.
  On the Select Instance page, use the drop-down box to
  specify an instance of SQL Server to remove, or specify the option
  to remove only the SQL Server shared features and management tools.
  To continue, click Next.
  On the Select Features page, specify the
  features to remove from the specified instance of SQL Server. To
  continue, click Next.
  Removal Rules will run to verify that the
  operation can complete successfully.
  On the Ready to Uninstall page,
  review the list of components and features that will be uninstalled.
  The Setup Progress page will display Setup status.
  On the Completing Setup page, click Close to exit the Installation Wizard.
  Repeat
  steps 2-9 until all SQL Server 2008 components have been removed.


SQL Server 2012 Index Reorganize Logfile Growth My SQL 2012 SP3 Server is configured to run an Index Reorg job, via a maintenance plan, each weekend(not my call, it's policy, I promise!).
I have a 30GB database(not including logfile) where the transaction logfile grows to alllllmost 30GB after index reorg's.
There are several other databases in this instance that are of similar size but the logfiles are tiny, ranging from 17MB to 2.5GB.
I have read through numerous forums and articles trying to determine why this particular logfile would grow so large. Alas, I have failed, which is why I'm now asking for enlightenment.

Here are some of the configurations:

Note, most of the configurations across the databases are the same(based on sys.databases).

Recovery Model = Full (on all databases)
is_FullText_Enabled = 1 (many, not all, of the databases have this enabled), there does seem to be some correlation here, the logfiles appear to be larger where is_FullText_Enabled = 1.
No replication/transaction log shipping of any kind in this instance.
Log backups run every 3 hours, which is part of the growth problem but not allowed to change schedule/frequency.

Here are some things I've tried:

Shrunk the log to 10MB to get the VLFs down, then grew it out to 24G.
Manually ran the index reorg a handful of times to see if fragmentation would go down and eventually stop growing the logfile but that did not help.
I even rebuilt the indexes(online via maint plan) and attempted a subsequent index reorg but the log grew back out again.
Shrank the logfile between reorg/rebuild attempts. Yes, shrink = bad, I know, bad DBA, bad!

So yeah. Index maintenance is logged, I get that, but why is there a large difference in logfile growths between databases? What am I missing? What could cause this logfile to grow so much larger than the rest?

I'm a newbie, make me more smarterer please and thank you. :)

In full recovery model, internal log truncation depends on you taking a log backup. If you wait three hours in between them, you'll have three hours of transactions sitting in there.

Index maintenance, as you've noted, is fully logged. If you truly perform some maintenance on every index in your database (or even most of them -- it's hard to say without knowing at what threshold you reorganize indexes), it's entirely possible for your log file to need to accommodate every one of those in between log backups.

Let's say in the 30 GB database you reorganize indexes at 5% fragmentation (a common tactic), and most of them have hit that level. You'll need to perform some action on all of those. In the other databases, if you have the same threshold, they may have far fewer indexes to touch. This could explain the discrepancy.

I understand that not much is under your control here, but if I had my DBA druthers, I'd want to

Take more frequent log backups
Perform less aggressive maintenance
Use Ola Hallengren's free scripts

Using Ola's scripts is a no-brainer. You can set very specific levels to reorg or rebuild, which indexes to skip or include, and at what size you want to perform some activity. I get that this isn't your question, but should you choose to share this with management, I'd be a bad person if I didn't include it.

The other plus side? All the actions that Ola's scripts run get logged to a table, so you can look at what actually happened. That's the most important thing to have around for forensics if you wanna find out why something happened.

Hope this helps!


Background Jobs in PostgreSQL I want to run a query all X seconds on my PostgreSQL database (9.5 on Windows Systems).

I read that you can use Background Jobs to achieve this.

The pgAgent Jobs (https://www.pgadmin.org/docs/1.4/pgagent-jobs.html) are a good explanation on how to do this.

However on the installation page (https://www.pgadmin.org/docs/1.4/pgagent-install.html) they mention the security concerns about storing the password on the server / connection string.

Is there another way to run a query all X seconds on the database?
Maybe there is an extension that works like oracle jobs?

There are lots of ways to run a scheduled job. One of them is using pgAgent.
For windows you can store your password in %APPDATA%\postgresql\pgpass.conf file. Here you can find details. You can also add trust to specific ip and user from server side by changing pg_hba.conf file


Database Backup Drive Occupancy We have 2 Databases whose backup files are close to 900 GB each.Backup Drive is 2 TB.Now the drive is fully occupied.The Database Backup files are compressed ones.

Database size in sp_helpdb shows as 171250 MB and 182350 MB.

Each week we have full backups and each day we have diff backups and .trn logs every 15 minutes. Last full backup size is getting close to 960 GB.

My question is apart from increasing backup drive size.what else should think of to free up some space.The drive contains all wanted files.

Thanks

You have options

Move away the backup files to a network share or a central backup server. Based on your company's retention policy, the backup can be archived to tape or just deleted (Caution !). This way if you lost the backup drive, you still have backups.
For VLDBs, you have to look into a good workable (meeting your company's RTOsand RPOs) backup (and restore) strategy.

E.g. Take weekly full backup, daily diff and transaction log backups say 15mins... This way, you wont end up taking FULL backups. You can be creative to check - how much database has changed since the last full backup before taking a diff backup.

The take away is to do proper storage planning. Enable Instant file initialization and yes do test your restores ... since a backup is no good unless you can restore it !


Replication: Procedure or function sp_MSupd_PROC has too many arguments specified Source is one service pack behind destination (2012).  I know service packs can be issues here, but it looks like this is only for versions 2000 and 2005.
When using sp_helptext sp_MSupd_PROC the objects uses a CASE WHEN with the binary input (last parameter) and updates or adds columns with the other parameters where the primary key column is equal to the primary key passed in.  For non-SQL types reading this - is there an Entity Framework approach to this, as this entire replication process looks slow and completely outdated and I highly doubt is using efficient .NET code?
Using sp_help on the objects on both the source and destination show that they're both the same.  Also, I did look at this briefly, but the no specific transaction number comes with the error - so I can't do his second step.
I did a quick verification on the data in the table, confirming that it matches with the parameters shown from the sp_helptext.

I haven't found anything else I can do to troubleshoot here and updating service packs right now isn't an option.  Note that this replication was fine yesterday and the day before yesterday, so what would cause it to be fine for multiple days, but now not be fine.  I realize that if users change or update the source or destination object, that would cause problems, but I don't see that being the case.

Thanks.

It sounds like the replication procedures might need to be recreated. Use the sp_scriptpublicationcustomprocs procedure on the publisher to generate the replication procs and recreate them on the subscriber.


Teradata performance statistics I need some statistics regarding the performance of Teradata, something like with a given hardware, given nodes and given AMPs - a table which has thousands of rows can be read in 't' secs.
I need this information to analyze the feasibility of teradata.

There are a multitude of factors that go into determining which Teradata platform and the configuration of the platform that will suite your needs. Teradata has spent untold amounts of money on intellectual property and decades of experience working with potential customers to help them properly size a configuration that not only meets the immediate needs of a customer but provides them capacity for which the environment can adequately grow and evolve.

I would strongly suggest you reach out to Teradata directly and engage them in a pre-sales capacity if your company is considering their technology to meet the needs of your data warehouse environment.

For a sandbox environment, you could may be able to get away with using the one terabyte version of Teradata Express on an adequately sized server or consider using Amazon EC2 to stand up a instance of Teradata to complete a proof of concept. It should be noted that either of these options should not be used to gauge the performance of a production environment for service level agreements but whether or not the technology will accomplish what you are trying to do.


AlwaysOn AG with and without listener I inherited a few SQL Servers (2014) with AlwaysOn AGs on AWS. I am totally new to this and we are talking live production environments. 2 different setups. Both have 2 servers each with one AG, so pretty simple. However, one set has a listener created and the other doesn't.

The one that doesn't have a listener is working fine. The one with the listener has issues at failover.

The GIS application accessing this server cannot connect to it after a failover. Once we failed back over to the original primary, it worked fine again. It doesn't use the listener to connect, instead it uses the cluster name.

I tried to have them use the listener but they said they couldn't even connect to that. I assume the listener hasn't been set up correctly.

Can I just remove the listener since it is not used anyways?

But everything I read says that you need to use a listener, yet the servers without a listener are working fine. I'm so confused.

Can someone point me in the right direction, please?

However, one set has a listener created and the other doesn't.

Well that doesn't help you application stay available! You can absolutely have an availability group without a listener, but the useful of such setups are low.

  (From Comments) The listener is the correct approach, but it's real function is to be able to direct intent read only connections to the proper secondary.

That's not quite correct. The listener always points to the primary replica and the main purpose of it is to facilitate transparent failover so that the connection strings do not need to change. One additional use is in read only routing as you've said but the primary use is actually for transparent failover.

  (From Comments) ... but the Cluster Name provides the same seamless redirection to the primary node as the listener does.

The CNO has no idea about what an availability group is, which should be primary, etc. The CNO absolutely DOES NOT always point to the primary replica. You can connect using it only when the core cluster resources (which holds the CNO) happens to be owned by the same node as the AG primary but if it isn't - and the CNO doesn't follow the AG around - then it'll fail. Here's an example:

  The one that doesn't have a listener is working fine. The one with the listener has issues at failover.

The one without the listener might be working fine... does it still work fine on a failover? Are they using some other DNS alias to "act" like the listener may.

The one with the listener, what does their connection string look like? If it isn't using the listener name in the connection string then of course it isn't going to work... they need to point it to the right place, that's why the listener exists!

The connection to the listener may also fail if there are multiple subnets, older client libraries are used, or certain keywords aren't in the connection string. If there are multiple subnets, the client driver should be something that supports the MultiSubnetFailover keyword and this should be set to TRUE.

  It doesn't use the listener to connect, instead it uses the cluster name.

Well, that's 50% of the problem.

  The GIS application accessing this server cannot connect to it after a failover.

If they update their connection string to use the listener and set MultiSubnetFailover = True then I'm betting it'll work... assuming the client library used to connect supports it.

  I tried to have them use the listener but they said they couldn't even connect to that. I assume the listener hasn't been set up correctly.

If SQL Server created the listener for you, I highly doubt it is setup incorrectly... I won't rule out edge cases though.

  Can I just remove the listener since it is not used anyways?

I would do the opposite. Have the GIS team change their connection string to the listener and set multisubnetfailover. It should, then, work... again with previously said assumptions.

  But everything I read says that you need to use a listener, yet the servers without a listener are working fine. I'm so confused.

Yes, you do want to use the listener. The fact that it is working is either because there is some document that says "always have this on node3" or you've been getting lucky that it stays working. They might have used the replica name directly in the connection string also... that would allow it to connect. Depending on the settings for the secondary role it may or may not still connect and work properly... just depends on said settings.


SQL Joins and Relationships I am confused by joins.  I want to know how to choose the join based on the relationship between tables, or do joins have no dependency on relationship?

if relationship is "one-to-one" then "Inner join" is the best.
if relationship is "one-to-many" then ?
if relationship is "many-to-one" then ?
if relationship is "many-to-many" then left join be the best.

Instead of thinking about "type of join based relationship multiplicity" you should start thinking about the type of join based on what you want to happen if there isn't a corresponding relationship.  This is a much more useful place to start.

See this post by Jeff Atwood.  As you can see in the sample results next to his queries, inner joins reduce the number of results and outer joins allow more results in comparison.  Both situations can be useful.

Generally speaking inner joins should be preferred to outer joins due to performance reasons, but the driving force for which type of join needs to be what you want to happen if there isn't a match.  Also in my experience there is a general preference for left outer joins over right outer joins.

As with anything in life there are caveats and gotchas, but this is a much better place to start.

Another useful visualization: SQL joins are easy


Database design for storing historical nmap data I am designing a database to store nmap data. I want to store two basic types of data:

Information about the nmap scans themselves (e.g. start and end times, nmap version used, command line arguments used)
Information about the hosts on the network (e.g. host names, IP and MAC addresses, open ports, services running)

The primary purpose of my application is to generate a "map" of the network; essentially, a listing of which hosts are up, which hosts are down, which ports are open on which hosts, etc. For this part of the application, all I care about is the current state; in other words, the results of the latest nmap scan. However, my application also needs to do some historical reporting, like generate a graph of host OS over time.

I read this StackOverflow answer about storing historical data and it seems logical: only track the current state in your main tables and use audit tables for historical reporting. So I would have, for example, a hosts table with the following columns:

host_id
name
ip_address
mac_address
status

and a ports table with the following columns:

port_id
host_id
number
status

Every time I run nmap, I would truncate the hosts and ports tables and insert the new results.1 The corresponding audit tables would track all of the changes over time so I could do historical reporting. Easy enough.

The problem(s)

What I can't figure out is the best way to store the information about the nmap scans themselves. If I followed the above structure, I might have a scans table with these columns:

scan_id
start
end
version
arguments

and a corresponding audit table. But the scans table would only ever contain one row (the most recent scan). This seems utterly pointless. There would also be no need for a scan_id column.

Okay, drop the audit table just for scans. Every time I run nmap, create a new row in the scans table and increment the scan_id. Problem solved.

However, now I want to generate a graph of number of hosts on the network over time. This would require me to join the scans table with the hosts_audit table. But now I need to add scan_id as a column to the hosts and ports tables in order to do the join. If I do this, however, every row in the hosts and ports tables would have the same scan_id, since those tables are truncated every time I run nmap. Hmmmm.

This is my first time designing a database from the ground up, and something doesn't smell right. I want the hosts table to reflect the present state of the network, but truncating the table every time I run nmap seems...unorthodox. I want to be able to connect data from the scans table with data from the hosts table, but can't figure out a way that makes sense. What exactly is wrong with the design I've outlined above and how can I improve it?

I don't think simply updating rows would work; for example, if a particular host is decommissioned, it would no longer appear in the nmap results but without truncating, would stay in the hosts table indefinitely. However, it feels a little odd to be truncating all my tables every few minutes...

I'm not sure you need audit tables. I think a simple schema like this would be sufficient:

scans
-----
  scan_id (PK)
  start (this must be a datetime)
  end (this must be a datetime)
  version
  arguments

hosts
-----
  host_id (PK)
  scan_id (FK to scans.scan_id)
  name
  ip_address
  mac_address
  status

ports
-----
  port_id (PK)
  host_id (FK to hosts.host_id)
  number
  status

Each scan has a start and end so you know when it took place. From this you can easily determine which is the most recent scan. Since hosts has a foreign key to scans, you can always tell which hosts were found with a specific scan. Similar for ports.

In this example, host_id is simply a numeric identifier generated by the database. If you want to store something like myhost.com, add a hostname column to hosts. Same thing for ports.


How to number SQL results sequentially? Given following query -

SELECT name, age FROM students WHERE age > 22

The result is -

name    age
----   ---
Kshitiz 23
Steve   22
Paul    24

How can I number the results sequentially? Like so -

serial  name    age
------  ----    ---
   1    Kshitiz 23
   2    Steve   22
   3    Paul    24

Please note that using surrogate key is not a solution. Because the surrogate id for record Kshitiz might be 9. I want whatever results are returned from whatever location to be numbered sequentially.

SELECT @a := @a + 1 serial, name, age
FROM students, (SELECT @a :=0) AS dummy
WHERE age > 22;

This works for MySQL. I was modifying from this example.


Equal sized timeseries for each id What I'm trying to implement is to generate equal-sized time series for each customer. One some days he may have some activity hence some retention value indicator, on other dates he may not hence 0.

CustRetention table is the following:

cust_id, date, intensity
...
1,2018-02-02,4
1,2018-02-06,5
...
2,2018-05-05,7
3,2018-02-06,4

What is the sql to generate equal time series (say for 240 days) for each cust_id?
I tried the following, but it didn't work

 drop table if exists retention_weekly_intensity_balanced;

 create table retention_weekly_intensity_balanced as
 select
 coalesce(t.cust_id, 'unidentified') as cust,
 d.date_actual,
 coalesce(t.size_of_increased_intensity) as size_of_increased_intensity
 from  retention_weekly_intensity t
 left join d_date d
ON
 d.date_actual = t.plaindate
WHERE d.date_actual>='2018-01-01'
AND d.date_actual <= '2018-09-01'
 ;

d_date is a simple calendar table

generate_series() returns a SETOF dates between the first and second argument, at intervals specified by the third argument. Seems to be what you're looking for.

testdb=# create table customers(customer_id bigint);
CREATE TABLE
testdb=# insert into customers select 1;
INSERT 0 1
testdb=# insert into customers select 2;
INSERT 0 1
testdb=# select customer_id, d from customers, generate_series('2018-11-01'::timestamp, '2018-12-01', '1 week') AS d order by 1, 2;
 customer_id |          d
-------------+---------------------
           1 | 2018-11-01 00:00:00
           1 | 2018-11-08 00:00:00
           1 | 2018-11-15 00:00:00
           1 | 2018-11-22 00:00:00
           1 | 2018-11-29 00:00:00
           2 | 2018-11-01 00:00:00
           2 | 2018-11-08 00:00:00
           2 | 2018-11-15 00:00:00
           2 | 2018-11-22 00:00:00
           2 | 2018-11-29 00:00:00
(10 rows)


Best approach to find missing logins and missing jobs between principal and mirror server I am doing a manual comparison of the logins and jobs between Principal and Secondary server, when doing database failover from P to S. I copy both the logins and jobs from one server to excel and compare them to those on the other.

Could someone please suggest a way to automate at least part of this check?

Note - I am doing this before doing a database failover as a pre-check, just to ensure that the secondary isn't missing anything as compared to the principal, so as to avoid a break after mirroring database failover.

Using Power Shell worked for me. I have this as part of bigger script, I am only providing sections of it. Hopefully you can put it together.

FUNCTION report-status-primary
    {
        PARAM (
                $Check,
                $Variable,
                $Primary,
                $Secondary
              )

        WRITE-HOST "Checking $Check on $Primary..." -ForegroundColor Yellow
        IF ($Variable)

            {

                WRITE-HOST "$Check on $primary not on $secondary" -ForegroundColor Red
                $Variable
            }
        ELSE
            {
                WRITE-HOST "Match" -ForegroundColor Green
            }
        WRITE-HOST "------------------------------------------------------------" -ForegroundColor Yellow
    }

  FUNCTION report-status-secondary
    {
        PARAM (
                $Check,
                $Variable,
                $Primary,
                $Secondary
              )

        WRITE-HOST "Checking $Check on $Secondary..." -ForegroundColor Yellow
        IF ($Variable)

            {

                WRITE-HOST "$Check on $Secondary not on $primary" -ForegroundColor Red
                $Variable
            }
        ELSE
            {
                WRITE-HOST "Match" -ForegroundColor Green
            }
        WRITE-HOST "------------------------------------------------------------" -ForegroundColor Yellow
    }

Variable:

$SourceSQLServer = 'Primary Server'
$TargetSQLServer = 'Secondary Server'

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null
$SourceServer = New-Object 'Microsoft.SqlServer.Management.SMO.Server' ($SourceSQLServer)
$TargetServer  = New-Object 'Microsoft.SqlServer.Management.SMO.Server' ($TargetSQLServer)

# -- Jobs
$JobsSNT = $SourceServer.JobServer.Jobs.name | where {($TargetServer.JobServer.Jobs.name -notcontains $_) -and $_ -notlike "*]__*" }
$JobsTNS = $TargetServer.JobServer.Jobs.name | where {($SourceServer.JobServer.Jobs.name -notcontains $_) -and $_ -notlike "*]__*" }

# -- Logins
$LoginsSNT = $SourceServer.Logins.name | where {$TargetServer.Logins.name -notcontains $_}
$LoginsTNS = $TargetServer.Logins.name | where {$SourceServer.Logins.name -notcontains $_}

Reporting:

report-status-primary -check 'Jobs' -variable $JobsSNT -primary $SourceServer -secondary $TargetServer
report-status-secondary -check 'Jobs' -variable $JobsTNS -primary $SourceServer -secondary $TargetServer

report-status-primary -check 'Logins' -variable $LoginsSNT -primary $SourceServer -secondary $TargetServer
report-status-secondary -check 'Logins' -variable $LoginsTNS -primary $SourceServer -secondary $TargetServer

Here is a sample output I get running this script.


Get data from different tables indexed by same ID Database: postgresql 12. I have a table people:

person_id | name
----------+-----
1         |John
2         |Paul
3         |Jim
...

Each person can have zero or more car insurance contracts, so I have another table car_contracts as follows:

contract_id | person_id | contract_number | date_from | insured_value | car_tag | .... (more fields)
------------+-----------+-----------------+-----------+---------------+---------+
1           | 1         | C99             |2020-01-01 | 5000          | ABC     | ....
2           | 1         | C02             |2020-03-01 | 4000          | XYZ     | ....
3           | 3         | C96             |2020-02-02 | 12000         | KKK     | ....
4           | 1         | C01             |2019-01-01 | 5500          | XXX     | ....
5           | 2         | C02             |2019-06-01 | 1000          | 666     | ....
6           | 3         | C03             |2020-02-20 | 11000         | KPX     | ....
...

Each person can also have zero or more power plants (or whatever; the point is, another table with different fields but still tied to people by means of person_id), so the table plants is like

plant_id | person_id | plant_name | power | address     | .... (more fields)
---------+-----------+------------+-------+-------------+
1        | 2         | plant01    |100    | 230 rue st. | ....
2        | 3         | jimsplant1 |50     | ...         | ....
3        | 3         | jimsplant2 |65.5   |             | ....
4        | 2         | plant02    |12.5   |             | ....
5        | 1         | house01    |20     |             | ....
6        | 1         | house02    |20     |             | ....
...

I can either get a list of people with their contracts or people with their plants by joining the people table to the car_contracts and plants table respectively.
Is it possible to somehow get all the information at once (people with their contracts and their plants) with a single query?

The main issue seems to me that the number of contracts and the number of plants have no relation and can be very different, so I think joining people with contracts with plants makes little sense.

Well, you can aggregate the rows into arrays:

select p.person_id, p.name,
       array_agg(cc) as contracts,
       array_agg(pl) as plants
from people p
  left join car_contracts cc on p.person_id = cc.person_id
  left join plants pl on p.person_id = pl.person_id
group by p.person_id;

(This assumes that person_id is declared as the primary key)

Aggregating the rows into json values might be easier to work with in the front end:

select p.person_id, p.name,
       json_agg(to_jsonb(cc)) as contracts,
       jsong_agg(to_jsonb(pl)) as plants
from people p
  left join car_contracts cc on p.person_id = cc.person_id
  left join plants pl on p.person_id = pl.person_id
group by p.person_id;

To avoid duplicates, do the aggregation in a derived table:

select p.person_id, p.name,
       cc.contracts,
       pl.plants
from people p
  left join (
    select person_id, jsonb_agg(to_jsonb(c)) as contracts
    from car_contracts c
    group by person_id
  ) cc on p.person_id = cc.person_id
  left join (
    select person_id, jsonb_agg(to_jsonb(p)
    from plants
    group by person_id
  ) pl on p.person_id = pl.person_id;


upgrade mysql 5.1 to 5.5 on Ubuntu 11.10 I currently have mysql server 5.1  installed via apt-get on my production Ubuntu 11.10 server

root@Ubuntu-1110-oneiric-64-minimal$ dpkg --get-selections | grep sql-server
mysql-server                    install
mysql-server-5.1                install
mysql-server- core-5.1          install

I would like to upgrade this to 5.6, but the mysql docs seem to suggest upgrading to 5.5 first, and from there to 5.6.

I've seen various lengthy guides describing how to upgrade from 5.1 to 5.5, but they all seem to describe how to upgrade by installing the tarball rather than using the package manager. Is there a simpler to upgrade using the package manager if the current version was installed using apt-get.

Obviously I want my existing configuration and databases to be retained after the upgrade and I will be sure to backup my databases using mysqldump before performing the upgrade.

I'm not sure about your package management issues but wanted to point out that the docs Say that you should completely reload your data using mysqldump when upgrading from a previous minor version.  So once you have your complete mysqldump w/ --all-databases --events --routines, be sure to import that into the new binaries.


Is there a way to automate checking stored procedures, functions, etc. written for sql server 2008 will work in 2016? The shop I work in has thousands of stored procedures which support applications on MS SQL Server 2008 R2 instances. We need to upgrade these to MS SQL 2016 over the course of the next year. Is there a tool that exists which will cycle through stored procs, functions, etc. which will identify anything that may have been deprecated prior to running an actual update?

One of my personal fav. checklists is from Thomas LaRock SQL MVP.

The first 3 are highly relevant to your question. Also note the Perfmon metric for 'Deprecated Features' that you can use to see if any are running.

  1- The SQL Server 2016 Upgrade Advisor (UA) is just that: an advisor.
  Much like a consultant, it doesn’t fix everything that is wrong, it
  merely advises you on what actions you should take when upgrading to
  SQL 2014. The actions the UA recommends will come in two forms: those
  actions to be done prior to a migration, and those actions to be
  completed post-migration. The UA is really good at finding what I call
  the “stub-your-big-toe” things that need fixing prior to a migration.
  But it is not foolproof, it will not identify every last detail. You
  will need to play the role of an actual DBA when migrating to a new
  version. Many of the items below will help you to do just that.

  REVIEWING THE “BREAKING CHANGES” SECTION IN THE BOOKS ONLINE Did you know that Microsoft publishes a list of breaking changes for each
  version of SQL Server? Well, you do now. You should review them to the
  point that they are familiar to you. You don’t have to memorize them
  all, just be familiar with them so that if something odd happens you
  can think to yourself “…hey, is this odd behavior listed in the
  breaking changes section of the Books Online (BOL)”? I would like to
  believe that the UA will alert you to many of these breaking changes
  but the truth is the UA is not as dynamic as the BOL. That means the
  BOL may have an entry or two that doesn’t make it into the UA
  checklist, and that is why you should review this section.
  REVIEWING THE “BEHAVIORAL CHANGES” SECTION IN THE BOOKS ONLINE Similar to the breaking changes, the behavioral changes are changes
  that could still affect you in an adverse way. They are definitely
  worth reviewing, and they are also things that the UA is likely to
  never report back to you about because they aren’t things that will
  break, but merely things that could break. Also worth noting is that
  the BOL appears to have two entries for behavioral changes, one for
  SQL Server features, and one specific for the database engine.  I’d
  advise you keep your eye on both going forward.


For Postgres, how would I define a custom function to generate primary keys? In postgres, primary keys are typically generated using a SEQUENCE. I am hoping to generate a unique random 12 character string for use as a primary key.
How would I do this in Postgres?

Turning the comments into an Answer so we can close this Question.

12-chars not random enough

A 12-character random string may be a poor choice for a surrogate key. Your generated values might have a significant chance of colliding, especially depending on your particular implementation for generating such values.

No need to invent such an identifier, as the industry has defined one already: UUID.

UUID

A better route would be using a standard Universally Unique Identifier (UUID). A UUID is a 128-bit value. Though usually displayed as a string of thirty-two hexadecimal characters plus four hyphens (36 chars total), Postgres knows how to compactly store a UUID natively as 128-bit. Postgres also supports indexing a UUID column, so the UUID type serves well as a primary key.

The original version of UUIDs are unique in space and time by combining the generating computer’s MAC address, the current moment, plus a small random number. You may choose alternative versions if security or privacy concerns require that you not encode the place (MAC address) or the time (current moment).

One advantage of UUIDs is that they may be generated either on the server-side and/or the client-side (within your app).

An advantage of using standard UUIDs instead of roll-your-own values is that all mainstream operating systems and development platforms already have high-quality implementations for generating quality UUIDs. No need to implement and document your roll-your-own algorithm.

Postgres has built-in support for storing UUID values. To generate UUID values, you need a plug-in, an “extension”.  The uuid-ossp extension is usually bundled with a distribution of Postgres, but you may need to activate it.

For more info, see the Question, Generating a UUID in Postgres for Insert statement?.


Encrypted Query locking server from time to time We have a system that runs without problems for days, but from time to time we see that all the queries do not run at all, and at the same time the SQL server is consuming too much CPU

So when we execute the following query to get all the running queries on the server:

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

We see the following running query:

TEXT    session_id  status      command     cpu_time    total_elapsed_time
NULL    26          background  INSERT      494039      1362613

Executing sp_who2:

SPID    Status      Login   HostName    BlkBy   DBName  Command CPUTime DiskIO  LastBatch       ProgramName SPID    REQUESTID
26      BACKGROUND  sa        .           .     msdb    SELECT  504257  4152076 04/26 12:16:13              26      0

So, how can I find what proccess, job, task, or whatever is executing this query and what exactly it is doing?

Tks in advance.

Looking at what you posted, it's most likely an Agent job trying to insert data into a table in msdb clashing with an Agent job trying to select data.

This can take a long time, especially if you've never cleaned up Agent job history. These tables can get quite large and contentious on old or busy servers.

Have a look here (full disclosure, it's my company's blog): https://www.brentozar.com/blitz/msdb-history-not-purged/


Why SQL Server Service Account connects to the DAC? We started getting Alerts:

Could not connect because the maximum number of '1' dedicated administrator connections already exists. Before a new connection can be made, the existing dedicated administrator connection must be dropped, either by logging off or ending the process. [CLIENT: 127.0.0.1]

With a help of SQL job and holding table, we managed to find out that it's the SQL Server Service Account that tries to connect, approx every 4 hours e.g. 12:00 16:00 and so on, with a program name: Net SqlClient Data Provider.
I've added the host_process_id column as per jadarnel27 suggestions, identified the PID, but there were no processes with the same PID in the task manager.
Oddly enough, there are no entries in the log: "Dedicated admin connection support was established", just the following ones:

16:00:05 Login succeeded for user 'sql service user'. Connection made using Windows authentication. [CLIENT: ip address of Windows Failover Cluster Virtual Adapter]
16:00:05 Could not connect because the maximum number of '1' dedicated administrator connections already exists. Before a new connection can be made, the existing dedicated administrator connection must be dropped, either by logging off or ending the process. [CLIENT: 127.0.0.1]
16:00:11 Dedicated administrator connection has been disconnected. This is an informational message only. No user action is required.

No clues in Windows Event Viewer logs or Windows Cluster logs.
This is only happening on an active node of AlwaysOn Availability  Group.
No jobs/port scanning configured to run at those times, nor any other tasks.
I have seen  How do we find who used Dedicated Admin Connection but we know what account has used DAC, just don't understand why.
How can we troubleshoot the issue further?
EDIT: I've found the cause of the problem, it was a powershell script that synchronises server objects for Availability Groups, it was set to run on port 1433 and I have no idea why it tried to connect to DAC, however simple reboot has solved the issue! Thanks ever so much to everyone who replied!

I recon that this is port/virus scanner that your security team might be using to find out vulnerabilities e.g. weak passwords. I had the same issue and eventually I figured out that it was the port scanner.

Look into windows event log as well.


Apply job aborting with <1 granule size unable to allocate, ASMM on all

My team is trying to resolve an oracle issue we're seeing sporadically that is really causing us trouble.

We have an update process that imports .dmp files via a package that utilizes an Apply job to do so. (not our choice to implement it this way...)

The problem is, we're getting this apply job aborted with an error of 4031 unable to allocate <100 bytes (various values, usually from 60 to 90) from memory, with the shared pool marked as the culprit. ASMM is on. The server is provisioned ~500 MB of memory to run on (these are old Server 2003 environments so memory is in high demand). I am concerned about this error - it seems to me that the error should always report multiples of 4k, as that is our granule size, but perhaps I am mis-understanding the process of freeing up memory. I am also concerned that the server is not freeing up packages as needed.

I believe that the fix is to use dbms_shared_pool.keep("PACKAGE NAME") to pin the package used to run the apply job, so that it never has to allocate the memory again, but we need to test this fix before we deploy it, for various organizational reasons. We also plan to allocate more memory to the oracle database. Setting the minimum size is not a solution, as we've seen this issue when the size of the pool is above the minimum, with ASMM on. We've also tried setting the sga maximum size to a higher value, and pinning the package in question, but again, with no way to reproduce the issue, it is a waiting game to see if it ever happens again.

Our main problem is in reliably reproducing the problem - even when disabling ASMM and setting the shared pool as small as possible, we're not able to reliably reproduce the issue. We've gotten the shared pool small enough that the web interface can't even pull image tags out of the database, or query the login information from the appropriate table, yet the apply job still runs reliably.

We do not have an in-house DBA so naturally we're probably missing some obvious steps.

This issue is present sporadically on 2-3 servers in a 8 server environment. These individual servers all see similar use by different groups. I am unclear as to why only some of the servers encounter this problem, but have not been able to identify any smoking gun cause.

I am hoping for input in either reliably avoiding this issue entirely, or in reliably forcing the shared pool to be unable to allocate bytes to this job, so that we can confirm these fixes actually work.

Please bear in mind that I have read most of the 4031 posts on SO at this point - this problem has been under investigation for weeks. It is likely NOT appropriate to mark this question as duplicate and direct me to another question - I've read it already. It did not answer my question.This is a cross post from plain old Stackoverflow

I have examined the trace files for one of the servers but it was less than enlightening - I am unsure of what to look for.

We've tried flushing the shared pool, but again, with no way to reproduce the issue it turns into an "wait and see" fix, which isn't the answer that our funder is really interested in.

This answer is based on the discussion had in the comments of the original question.

The primary cause of any ORA-4031 is that the shared pool is exhausted and is unable to allocate new connections to the database. This will cause the database to crash. I am not going to comment if the package that is running the import is just the last straw or if it is the entire problem, architecturally, I am just offering a workaround.

Due to the small nature of the server memory, space is extremely restricted. I would recommend assigning specific amounts of memory to the shared_pool_size parameter in the spfile and bumping up memory usage incrementally each time the issue occurs. This, of course, implies an increase to SGA_max_size and SGA_target_size. Initial sizing and increment size will be left for the author to discuss, but I'd imagine 50m jumps would be small enough to keep the server happy and also have reasonable chances of success to workaround the ORA-4031.

If memory of the server becomes exhausted before issues begin to resolve, then I would begin digging around the import package load to see if there are inefficiencies with how that procedure is using the shared_pool.


Split a row into two for attendance seet I'm redesigning an attendance system used in my company. Originally, an attendance sheet was rendered on a Grid that was getting week-by-week data Pivoted. I've opted for a Scheduler control by DevExpress. A sign-in and a sign-out are tow be represented as two 'appointments' now.

The Scheduler requires a very specific data source for appointments. Here is my attendance table:

The DutyIn_Manual is what the employee reports and the DutyIn is the actual time captured. DutyIn_Manual is to be shown on the Scheduler. Similarly, DutyOut_Manual goes into view.

Now, I need to split one row into two: One for Duty In and one for Duty Out.

Any help greatly appreciated.

Update
The solution by @Mikael Eriksson worked perfectly.

Here's my updated query:

DECLARE @T TABLE
(
    Id INT IDENTITY PRIMARY KEY,
    AttendanceId INT,
    StaffNumber VARCHAR(50),
    DutyDate DATE,
    DutyIn TIME,
    DutyOut TIME,
    DutyInManual TIME,
    DutyOutManual TIME
);

INSERT INTO @T
SELECT [iSAID],
    [cStaffNo],
    [dtDutyDate],
    [dtDutyIN],
    [dtDutyOUT],
    [dtDutyIN_Manual],
    [dtDutyOUT_Manual]
FROM [Attendance].[dbo].[StaffAttendance];

SELECT T.Id,
    T.AttendanceId,
    T.StaffNumber,
    T.DutyDate,
    S.DutyAuto,
    S.DutyManual,
    S.DutyType
FROM @T AS T
    CROSS APPLY
    (
        VALUES
            (T  .DutyIn, T.DutyInManual, 'In'),
            (T  .DutyOut, T.DutyOutManual, 'Out')
    ) AS S (DutyAuto, DutyManual, DutyType);

You can use the Table Value Constructor in a cross apply to generate two rows for each row in the source table.

declare  @T table
(
  ID int identity primary key,
  DutyIn datetime,
  DutyOut datetime,
  DutyInManual datetime,
  DutyOutManual datetime
);

insert into @T values
('2016-10-01T10:10:00', '2016-10-01T10:50:00', '2016-10-01T10:00:00', '2016-10-01T11:00:00');

select T.ID,
       S.DutyAuto,
       S.DutyManual
from @T as T
  cross apply (values(T.DutyIn, T.DutyInManual),
                     (T.DutyOut, T.DutyOutManual)) as S(DutyAuto, DutyManual);


Log flushes per second appears low I've newly taken over administration of a system with about 25 databases. Using Redgate Monitor to chart some metrics, I see that each day, one database shows values for log flushes / second that look like a "normal" chart - a zig-zag line throughout the day.

Redgate explains the metric: "A log flush occurs when a transaction is committed and data is written from the log cache to the physical transaction log file."

All well and good - work is happening and data is getting flushed to disk as it commits.

My question is - why do all the other databases fail to register anything much above the x-axis at all?

Redgate also explains: "Log flushes per second should generally correlate with the number of transactions per second."

When I view transactions per second, plenty of databases are showing zig-zag chart lines - so what's going on that only one of them is showing activity for log flushes? Does this mean all the other databases are failing to commit?

All user databases are using full recovery model.

(I also note between 2am and 5am there are huge spikes in log flushes for almost all databases - still to investigate the cause of these).

I don't know anything about the Redgate tool you're using, but it sounds like "transaction per second" would include read and write transactions together.  So the likely explanation is that the one database with high log flushes per second is the only one with a significant write workload (thus generating log flushes).  The other databases may have lots of read transactions, but these don't generate any log activity.

Regarding your note at the end, the log activity in the middle of the night is probably due to index maintenance, which is a common thing to do overnight that generates lots of log records.

Without details about your databases, these are mostly just educated guesses based on the information provided in your question.


How to speedup pg_dump big database I've got big database (about 900GB), and when I migrate it to new server dump speed is not more than 2MB/s (very slow). Link between servers is 10Gb/s.

The database contain 20 tables with different sizes (the bigest one has about 600GB).
Because servers have different version of postgresql (source 9.3 and destination 9.4) I can't copy all cluster to new server.
I try using:

pg_dump -h source | pg_restore -h 127.0.0.1

but no result. I also try dump to file and then restore with '-j' parameter but also no result (after 20 min only one thread work because others finish their job). Problem isn't disk speed because both methods give me similar speed.

I haven't any experience with migrate such big data so maybe this methods are bad.
Have You any advices why this migration is to slow, and how speed it up?

You don't actually have to dump to upgrade a PostgreSQL instance. For example, you can bring up a new cluster with access to an older cluster using the pg_upgrade command. If you're using XFS you can even use --clone. I would read the rest of the docs for pg_upgrade.

If you're just looking to move the instance you can try pg_basebackup - though I'm not sure that works across major versions.


Copying data up in single SQL statement I need to fix an SQL statement in MySQL that should calculate a field using values from two of the columns and I prefer to do this using set-based programming, ie not procedural. What needs to happen is that in a separate column called delta the value of level is copied depending on whether in_event is 0 or 1. If it is 1, then the value of level is copied, else the last known value of delta is copied.

This is similar to how data is copied down in spreadsheets for underlying cells that have no value, except that in this case data is copied up instead of down.

These are the statements I am using:

  create table events (
  date date default null,
  sortorder int(11) not null default '0',
  level float default null,
  in_event int(1) not null default '0'
);

insert into events values
('2018-09-04', 2, 1547.8, 0),
('2018-09-03', 3, 1571, 1),
('2018-08-31', 4, 1541.6, 0),
('2018-08-30', 5, 1575.4, 0),
('2018-08-29', 6, 1596.6, 0),
('2018-08-28', 7, 1626.4, 0),
('2018-08-24', 8, 1575, 0),
('2018-08-23', 9, 1536, 0),
('2018-08-22', 10, 1579, 0),
('2018-08-21', 11, 1559, 1),
('2018-08-20', 12, 1554, 0),
('2018-08-17', 13, 1534, 0),
('2018-08-16', 14, 1543, 0),
('2018-08-15', 15, 1542.2, 0),
('2018-08-14', 16, 1646.8, 0),
('2018-08-13', 17, 1680.4, 0),
('2018-08-10', 18, 1667.6, 0),
('2018-08-09', 19, 1711.6, 0),
('2018-08-08', 20, 1726.6, 0),
('2018-08-07', 21, 1736.2, 1);

I have gotten as far as the following, but it copies the data down instead of up:

alter table events add column delta decimal(10, 2);
update events set delta = level where in_event = 1;
select date, sortorder, level, in_event, case when delta is not NULL && @d := delta then delta else @d end as delta
from events
order by date desc;

This is the result that I am looking for:

insert into events values
('2018-09-04', 2, 1547.8, 0, 1571),  -- value of 1571 is copied up, because it is the last known value and in_event is not 1
('2018-09-03', 3, 1571, 1, 1571),    -- value of 1571 is attained, because in_event is 1.
('2018-08-31', 4, 1541.6, 0, 1559),
('2018-08-30', 5, 1575.4, 0, 1559),
('2018-08-29', 6, 1596.6, 0, 1559),
('2018-08-28', 7, 1626.4, 0, 1559),
('2018-08-24', 8, 1575, 0, 1559),
('2018-08-23', 9, 1536, 0, 1559),
('2018-08-22', 10, 1579, 0, 1559),
('2018-08-21', 11, 1559, 1, 1559),
('2018-08-20', 12, 1554, 0, 1736.2),
('2018-08-17', 13, 1534, 0, 1736.2),
('2018-08-16', 14, 1543, 0, 1736.2),
('2018-08-15', 15, 1542.2, 0, 1736.2),
('2018-08-14', 16, 1646.8, 0, 1736.2),
('2018-08-13', 17, 1680.4, 0, 1736.2),
('2018-08-10', 18, 1667.6, 0, 1736.2),
('2018-08-09', 19, 1711.6, 0, 1736.2),
('2018-08-08', 20, 1726.6, 0, 1736.2),
('2018-08-07', 21, 1736.2, 1, 1736.2);

Look for

SELECT *
FROM ( SELECT events.*,
              CASE WHEN in_event
                   THEN @tmp := level
                   ELSE @tmp
              END delta
       FROM events,
            (select @tmp := 0) var -- or another value for "unknown"
       ORDER BY date ASC
     ) tmp
ORDER BY date DESC;


Extract Database Name From String In a database we have the full file path for a database field type is nvarchar(255) and structure/data would look like this:

M:\Blue\Red\Green\Folder\Pink\Purple\Database1.accdb
M:\Blue\Red\Orange\Folder1\Folder2\Folder3\Size\Small\Large\DbTest1232323Test.accdb

My question is, how can I extract only the database name – pull only this out:

Database1.accdb
DbTest1232323Test.accdb

Since the name of the database can vary, I can't use just a RIGHT() function.

Is there a way I can somehow use a wildcard in conjunction with RIGHT() to say, "Pull everything to the right of the last \"?

This ought to do it.  You'll want the last column (FileName_Only).

SELECT f.physical_name,
    REVERSE(f.physical_name) AS physical_name_Reversed,
    CHARINDEX('\', REVERSE(f.physical_name)) AS First_Backslash_When_Reversed,
    LEFT(REVERSE(f.physical_name), CHARINDEX('\', REVERSE(f.physical_name)) - 1) AS FileName_Only_Reversed,
    REVERSE(LEFT(REVERSE(f.physical_name), CHARINDEX('\', REVERSE(f.physical_name)) - 1)) AS FileName_Only
FROM sys.database_files f

Here's another option. The code is a bit leaner:

SELECT
    f.physical_name,
    REVERSE(f.physical_name) AS physical_name_Reversed,
    CHARINDEX('\', REVERSE(f.physical_name)) AS First_Backslash_When_Reversed,
    RIGHT(f.physical_name, CHARINDEX('\', REVERSE(f.physical_name)) - 1) AS FileName_Only
FROM sys.database_files f


Case statement query How can this be re-written? somehow not able to make it right. In case statement THEN and ELSE are the same
would like to change it to a single statement.
SELECT    t.status_id status_id, (
CASE WHEN t .status_id NOT IN (SELECT     status_id  FROM  firm_return   WHERE      enabled = 'D' and firm_id=t.firm_id)

THEN (cl.category_code + '-' + s.status_name)   ELSE cl.category_code + '-' + s.status_name END)  AS status_type
   FROM       bla bla

Your example query appears to be incomplete (e.g. your aliases t and cl don't correspond to anything in the example). Ignoring that for a second and using your example query, this is probably what you're after:
SELECT t.status_id AS status_id, cl.category_code + '-' + s.status_name AS status_type
FROM SomeTable AS t
LEFT JOIN firm_return AS s
    ON t.firm_id = s.firm_id
    AND s.enabled = 'D'
... -- The rest of your query logic here (e.g. the table for the cl alias)

If you care about NULLS and want to handle them with a default value then you can use the ISNULL() function around the logic for your status_type column like so: ISNULL(cl.category_code + '-' + s.status_name, 'SomeDefaultValueGoesHere') AS status_type


Foreign Key Constraint over two tables I am trying to put the following entity-model into practice:

We have a list of Payments, each Payment belongs to a certain store and is of a certain payment type (e.g. 10€ maintenance cost in Store1, 20€ rent in Store2)
A Payment Type is a categroy like (rent, maintenance, legal fees, licenses) and each category is assigned a certaing account_type (e.g. budget_legal, budget_monthly, budget_extraordinary ) where multiple Payment types can have the same account_type
We have accounts: An account belongs to a certain store for a certain account type. So Store1 can have account-types budget_legal, budget_monthly, while Store2 has account types budget_legal, budget_monthly and budget_extra

Relationship Model:

Image Created with http://draw.io/

Now I want a constraint that each payment must belong to an existing account. I want to guarantee the following:

When I change the account_type of a Payment_type, all payments of this type will then "belong" to the respective accounts.
If I delete an account I want to delete all payments in this account ( like delete cascade in a foreign key )
I can only insert/update Payments so that each payment refers to a valid store_id/account_type, which exists in the accounts table.

Possible solutions which I tried but don't really work:

I tried to add the account_type to payments, but then I have doubled the field - and when I change a Payment type I will have to change the referenced payments via trigger. - But in the best case I don't want triggers to enforce data integrity - and I don't want duplicate data storage.

I tried to create a foreign key constraint over multiple tables / a view which joins payment with payment_types. Unfortunately this seems to be impossible in Oracle DBMS

I tried to create a virtual column on Payments, which calculates the value from payment_types, but virtual columns can only reference columns in the same table.

Some notes first:

Considering that Accounts has a unique constraint on (account_type, account_store), a Payment seem to be associated with only one Account. That suggests that the FK from Payments should reference Accounts (and not Stores). This change will also solve issue #2.
Adding account_type to Payments (and modifying accordingly the foreign key to Payment_types) seems like the most sensible approach and it will solve issue #3.
The two changes together will solve issue #1.
Oracle does not have ON UPDATE CASCADE option, so this issue (updating the account_type of a Payment_type to update the related Payments) needs to be addressed by either an update trigger or by a stored procedure (that will also check that related Accounts also exist (or are added) for all Stores that have Payments of the (updated) account_type.

The suggested design, assuming you also have an Account_types table (I renamed a few columns but that is up to you and your preferences / conventions):

Account_types
---------------
account_type_id  PK

Payment_types
---------------
account_type_id  PK, FK -> Account_types
payment_type_id  PK

Stores
--------
store_id         PK

Accounts
---------------
account_type_id  PK, FK1 -> Account_types
account_id       PK
store_id             FK2 -> Stores

Payments
---------------
payment_id       PK
amount
account_type_id      FK1, FK2
payment_type_id      FK1 -> Payment_types
account_id           FK2 -> Accounts

Note how both foreign keys from Payments use composite keys.


What is the Best Way to create MySQL Master-Slave Replication Setup and Troubleshoot it? I am very new to Database Administration.

I face a lot of problems while setting up mysql master-slave replication.

I also face regular mysql replication troubleshooting issues.

Can anybody helps to understand how should i deal with all these?

I provided links to tutorials.  Just keep mind that on Ubuntu, the my.cnf file is in /etc/mysql/my.cnf and not in /etc/my.cnf like in the howtoforge tutorial.  In my setup, I didn't use FLUSH TABLES WITH READ LOCK; on the master.  If your master server has a lot of write activity, you may need to lock your tables by running that command before backing up.  If you use FLUSH TABLES WITH READ LOCK;, then after your backup, you will want to run UNLOCK TABLES.   If you run into any problems, let me know.

Here is the tutorial that I found on howto forge, made for Redhat/CentOS :
http://www.howtoforge.com/mysql_database_replication

Another tutorial that looked ok for Ubuntu
http://www.srcnix.com/2010/10/14/simple-mysql-replication-with-ubuntu-master-to-slave/

Here is the configuration I used :

On the MASTER Server

Configure the master server:

vi /etc/mysql/my.cnf

[mysqld]

# bind-address = 127.0.0.1 (comment this out)
server_id           = 1
log_bin             = /var/log/mysql/mysql-bin.log
log_bin_index       = /var/log/mysql/mysql-bin.log.index
max_binlog_size     = 100M
expire_logs_days    = 1

Restart MySQL:

/etc/init.d/mysql restart

Connect to mysql's console: mysql -u root -ppassword

Create and grant permissions to replication user.

GRANT REPLICATION SLAVE ON *.* TO 'replication'@'ipaddressofslave' IDENTIFIED BY 'replicationuserpassword';

Make sure to copy this information somewhere or leave it visible

SHOW MASTER STATUS \G;
mysql> show master status \G;
            File: mysql-bin.000001
        Position: 100
    Binlog_Do_DB:
Binlog_Ignore_DB:

mysql> quit

Dump the database to a file:

mysqldump -u root -p databasename > /tmp/databasename-backup.sql

Copy the database dump to the slave server using scp or use ftp if you like:

scp /tmp/databasename-backup.sql root@ipaddressofslave:/tmp/

On the SLAVE Server

Edit the mysql configuration:

vi /etc/mysql/my.cnf
[mysqld]

# slave server configuration
server_id           = 2

# this is optional, but I find it useful to specify where the relay logs go to control.
# Don't forget to create the /var/log/mysql directory and give mysql rights to it.
# chown mysql:mysql -R /var/log/mysql
# disk space
relay_log           = /var/log/mysql/mysql-relay-bin
relay_log_index     = /var/log/mysql/mysql-relay-bin.index
relay_log_space_limit = 2000M

Restart MySQL: /etc/init.d/mysql restart

Restore the backup:

mysql -u root -ppassword nameofthedatabase < /tmp/databasename-backup.sql

Connect to MySQL:

mysql -u root -ppassword

stop slave;

# master log file and master_log_pos taken from show master status above
CHANGE MASTER TO master_host='ipaddressmaster', master_port=3306, master_user='replication', master_password='replicationuserpassword', master_log_file='mysql-bin.000001', master_log_pos=100;

start slave;

Run SHOW SLAVE STATUS\G:

mysql> show slave status\G;
             Slave_IO_State: Waiting for master to send event
                Master_Host: ipaddressmaster
                Master_User: replication
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.0000001
        Read_Master_Log_Pos: 100
             Relay_Log_File: mysql-relay-bin.000001
              Relay_Log_Pos: 1
      Relay_Master_Log_File: mysql-bin.000001
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 17324288
            Relay_Log_Space: 17324425
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
          Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 0
1 row in set (0.02 sec)

Afterwards, keep in mind that replication can fail for various reasons.  On the slave, you can monitor the status by running the command SHOW SLAVE STATUS \G; Or setting up a cron job to monitor the status and send emails if it fails.  Get familar with the output from this command.  If replication is running correctly, you should see "Slave_IO_State: Waiting for master to send event".

Once you get this setup correctly, I can provide you with a script to monitor that replication.

Here is a script to monitor the error log in MySQL.  If you add the line

  [mysqld]

  log-error       = /var/log/mysql/mysql.err

restart mysql : /etc/init.d/mysql restart

Then you can use the following script to monitor the log file.  If the log changes in any way, you will receive an email notifying you that an error occured on the slave server.  If you want the error log checked on a regular basis, you will need to add this script to your crontab.

Here is a sample script : /somepath/monitor_mysql_log.sh

#! /bin/sh
MAIL_TO="addressemail@something.com"

# This is the log that will be monitored.
# If any changes occur to this, then take appropriate action.
MONITORED_LOG=/var/log/mysql/mysql.err

# We will need this log to see whether any changes occured to /tmp/goreb.log
TEMP_LOG=/tmp/.mysql.err.1

# This is a 1-time command i.e. create the log file if it does nto exist.
[ ! -f $TEMP_LOG ] && touch -r $MONITORED_LOG $TEMP_LOG

[ $MONITORED_LOG -nt $TEMP_LOG ] && echo "an error occurred in mysql" | mail -s "Error on MySQL" $MAILTO

# Update $TEMP_LOG with the new modified date of $MONITORED_LOG
touch -r $MONITORED_LOG $TEMP_LOG

To add to crontab.

Make the script executable:

chmod +x /somepath/monitor_mysql_log.sh

Update crontab:

crontab -e

* * * * * /somepath/monitor_mysql_log.sh

And the script will be run every minute.

The script I provided is a script that I just quickly put together.  Also, in order for your server to be able to send emails, you'd have to install something like postfix or sendmail.


Is it possible to archive table rows of an RDS read replica? I would just like to know if I can purge 60 days old rows from a replica. Would the replica just copy the rows again from the master after deleting the rows? Because what I would really like to do is to leave the master as is and then do all the reads for the reports on the slave. So what I would really be doing is to copy the rows older than 60 days to an archive table and then delete it from the source table. Is it ok to have an additional table(archive) on the read replica that doesn't exist on the source?

A read replica is basically a read_only slave in normal MySQL speak. RDS does allow you to change the read_only parameter, so you can purge the records you want and RDS won't recopy the data from the master.

However be careful if you go this route. If you run DML statements on the master for rows that don't exist on the slave, you likely will break the replica. If replication breaks, you're only choice is to drop the replica and create a new one from the master. You'd then have to re-purge the rows.

I am curious why you want to remove data from the replicas for report queries. I would expect reports would want more data, not less, than the master.


command line tool on linux to convert oracle sql into db2 sql IBM has a GUI based tool to convert Oracle SQL into DB2 SQL.

Is there a command line tool (preferably on linux) that does the same thing? The reason being, we want to do this work as part of a nightly build when no humans would be around.

DCW does not correct all of the possible incompatibilities in the Oracle SQL and PL/SQL, except in trivial cases. Even if there were a command-line tool (though there is not), there would always be some manual tweaking so you would not be able to fully automate the process.


Multiple primary keys I am new to database design and have been looking at some example data models here.

One thing I don't understand is if you have a table like client_addresses from the link above:

**************************
 client_addresses
**************************
 PF   client_id
 PF   address_id
 PK   date_address_from
      date_address_to
**************************

Im not quite sure I understand how the keys have been setup. It looks like there are three primary keys. Two are primary and foreign, another is just primary.

Is it the case that there are three primary keys? If so, why is this necessary?

Im using SQL InnoDB. How would I setup a table like this?

The notation that consists of sticking the letters "P" for Primary, "K" for Key and "F" for foreign key, in front of attribute names, is crippled, flawed and confusing.

It is crippled because it suggests that "foreign keys" are keys in a sense similar to "primary keys" and/or "candidate keys".  They are not.

It is also crippled because the notation tends to suggest/instill the belief in people's minds that there is somehow an important distinction between "primary" keys and "non-primary" keys.  There is not.

It is flawed because it neglects the notion that any attribute can be part of any number of keys.  The notation typically allows for an attribute to participate in at most "the" "primary" key and only one other "non-primary" key.

It is confusing because it is both crippled and flawed.  Here are the facts about keys :

Foreign keys are not "keys".  The term is a blast from the past, and a gross misnomer.  They denote an inclusion dependency from the table that the "foreign key" is on, to some other table.  There can be any number of "foreign keys" on a table, to any number of other tables, and it is even a fact that the very same set of attributes can be a foreign key twice, or thrice, or any number of times (perhaps to just as many distinct other tables).  Heck, the very same set of attributes can even be a "foreign key" twice or more to the very same table (with a different mapping of from/to attributes).  Anyhow, any attribute can participate in any number of "foreign key"s.

Primary keys are just keys.  There is nothing special about them, in comparison with "non-primary" keys.  All of them denote nothing more than just a rule that for the set of attributes they consist of, the combinations of values that appear for these attributes, in the table, must be unique.  The fact that most SQL engines force you to declare a primary key, is, once again, a sad and sorry blast from the past.  The idea to single out one key and declare that one "more important than others" is merely psychological.  It might suggest that this is the preferred key to use for the table at hand.  Don't make the mistake of thinking that this constitutes an obligation for other database designers to reference only this key in what tables they design.

Brief : there can be any number of "foreign keys", and any attribute can participate in any of them.  There can be any number of keys, and any attribute can participate in any of them.

A notation or a tool that respects these facts, will at the very least allow to identify each key properly (e.g. using labels such as "K1", "K2", ..., "F1", "F2", ...), and allow for each attribute to be accompanied by more than one of these labels (e.g. "K1,K2,F2").

Furthermore, with respect to "foreign keys" specifically, such a tool will also allow to document the exact and complete nature of the "foreign key" : that is, it will also allow to identify the target table of the foreign key and it will also allow to specify/identify what the mapping is like between the attributes of the "source" and the "target" table.


How to build a database that contain only the delta from yesterday I need to know what has been changed on my database since last night.
Is it possible to extract this data from the LDF file and to build a new Database that contains the delta?

For example, let say I have a table for users and now, a new user was added and one of the users update his home address. I need to be able to build a new database that users table will contain two records
1. The new user (and to add a new column to know if it’s new or update field)
2.  The user that update his record (it will be nice to know which record has been update)?

BTW,
I have to SQL servers that I can use (2008 and 2012)

Thanks In Advance

ApexSQL Log can provide the list of transactions made in a specified time period. It has a time range filter where you can specify the last 24 hours, or any other period you want to read. Just make sure you provide enough transaction log backups to cover the specified time range.

ApexSQL Log can also create a redo script for all these transactions, so you can execute it against the new database where delta data will be stored.

What's equally important, if you want this to be a reoccurring job, you can schedule it using ApexSQL Log CLI and SQL Server Jobs.

Here's an example: Automating daily transaction log reading

Disclaimer: I work for ApexSQL as a Support engineer


SERVERPROPERTY('HadrManagerStatus') equating to 1 even if Always On is not configured We have a SQL Server Enterprise 2017. I would like to get insights as to why our SERVERPROPERTY('HadrManagerStatus')returns 1 even if Always On is not configured in this instance.

I have a local instance in my work machine that is a stand alone. When I run SELECT SERVERPROPERTY('HadrManagerStatus') it returns 1 even if I have not set up Always On on this machine. Never have attempted to check the "Enable Always ON" box on the Service in the SQL server Configuration Manager.

I have setup this instance to be a publisher and a distributor for Replication. I have restored databases on this instance that the database were part of the availability databases.

I guess my question is, how does this SERVERPROPERTY('HadrManagerStatus') evaluates? What conditions does it check?

As mentioned in a comment by Kin, if you want to check on whether the "Enable Always On" checkbox is checked in SQL Server Configuration Manager, you should be using this property instead:

SELECT SERVERPROPERTY('IsHadrEnabled');

From the docs on IsHadrEnabled:

  Always On availability groups is enabled on this server instance.

Regarding the other setting (HadrManagerStatus), here's what docs has to say:

  Indicates whether the Always On availability groups manager has started.

I don't really know what the "Availability Groups manager" is, but it sounds like probably an internal component to SQL Server.

Tony helpfully pointed out that this value was non-zero on non-AG instances prior to 2017.  I just verified this on a SQL Server 2016 instance where AGs are not enabled and set up, and I get "2" ("Not started and failed").

I'm speculating here, but the fact that the "AG manager" component is started by default on SQL Server 2017 probably has something to do with the clusterless, "read-scale" AG feature introduced in that version.  So on 2017 this component is able to start up and run, whereas previous versions noticed the lack of WSFC and failed to start.

Anyway, that's just a guess.  Maybe someone from Microsoft will come by and confirm one way or the other.  The real answer is what Kin pointed out - if you want to check for AG's being enabled, use IsHadrEnabled.


SQL Server refuses to connect from time to time I get this error like 5 times a day. It does not happen always and we cannot really reproduce it.
So is there any common knowledge as to why SQL Server sometimes refuses to connect and sometimes allows it?
Error #1070 is our own error code.

SQLState 42000
Error Code 4060

...and ...

SQLState 28000
Error Code 18456

...are pretty much standard error codes that do not help to solve this mystery.

I use PHP and AdoDB as a framework.
Here is a Link that adresses this issue, but i do not see that this will solve the "from time to time" aspect.
https://support.microsoft.com/de-de/help/2121736/error-message-when-you-try-to-authenticate-an-odbc-connection-to-your

There are several possible issues, check one by one:

Do you have permission to access the database using the credentials that you are supplying? Maybe user don't have the necessary permissions to access the server
User credentials are ok, but user doesn't have the right to connect to the given database, you should grant him all rights needed
And last but not least, maybe there isn't a database which correspond to the information you specified, check that the database you are trying to connect exists

If everything fails, check server error logs and client event log too to get some more information.


_Batch_ priorities in PostgreSQL I would want to tag many queries and mostly materialised view refreshing tasks as low priority in my RDBMS, in the fashion I run the web browser on the local machine as low priority (because JS, CSS, animated GIF do not deserve CPU time slices), the same way I do for C/C++ software builds, using a nice on Darwin and Linux supervisors.

Can I do that?

Can I do that in vanilla PostgreSQL?

Could I even couple initial job prioritisation with increasing to normal priority after a given timeout? Highways as real world analogy: in a normal situation there are slow traffic lanes and fast traffic lanes; but in a dense traffic situation all lanes get relatively slow, then unless for a very specific exceptional emergency services job that appropriates the lane.

Is this an oversight for the need of another family of DBMS (o-o, columnar, dwh...)?

This is talked about exhaustively on the wiki. You'll have to ask a more poignant question

https://wiki.postgresql.org/wiki/Priorities


Migrating everything to do with a schema from one Oracle DB server to another We have an old DB server, running Oracle 11g on Solaris.

We also have a new DB server, also running Oracle 11g, but on RHEL.

We want to move a schema to the new db - but we need everything it refers to moved too.  The data /tables can be moved easily enough, but then there's sequences, stored procedures, db types, views and maybe other things I'm not aware of yet.

The original schema contains different objects assigned to specific tablespaces and we need the same assignments in the destination server too.

What's the best way to accomplish this?

It looks like expdp/impdp can export and import other object types, but I can't find a list of the types to include, or how to say "everything".

Also DBMS_METADATA.GET_DDL looks useful, but again I can only find examples for specific object types and I want "everything in the schema", ideally.

Paul

Datapump is one answer.

Look at http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm#i1007524 - specifically "Transportable Table mode".  Moves the metadata, but not the data.  You can choose which schemas to include with the SCHEMAS switch, along with the INCLUDE/EXCLUDE parameters.


Does Mongo have disappearing/phantom writes I have heard that Mongo has something like phantom writes.  For example, if I have a web request with the following db commands in a single unit of work:

a) update comments in article
b) update article attributes
c) update users comment_field

Questions:

Is it possible that only a and c execute, and b fails or never gets to the database?
Is it possible that only a and b execute, and c fails or never gets to the database?

Where can I read some more in regards to this, or is it just a myth?

Just to answer my question, in case people come across it:

1) Yes, it is possible for A, and C to be committed and B to fail.  Atomicity only exists on documents in MongoDB.
2) Yes, this is possible as well.

See more for reading here:

http://docs.mongodb.org/manual/tutorial/isolate-sequence-of-operations/


Are my views slowing down an update? I have a SQL Server database with an update sentence ran very often. I've seen on Activity Monitor that this update sentence trigger 27ms and I want to improve it.

I have checked execution plan and I have seen that the update sentence updates view's indices:

the update query is this:

UPDATE Code
    SET CommissioningFlag = @newCommFlagValue
                , Source = @source
                , UserName = @username
                , LastChange = CAST(SYSDATETIMEOFFSET() as nvarchar(50))
                , SentToNextLevel = 0
                , HelperCodeId = @helperCodeId
WHERE CodeId = @codeId

And the view highlighted script is:

CREATE VIEW dbo.PrintedCodesStatistics
WITH SCHEMABINDING
AS
    SELECT AggregationLevelId, COUNT_BIG(*) as CodesPrinted
    FROM dbo.Code
    WHERE   (CommissioningFlag != 255)
    GROUP BY AggregationLevelId

GO
CREATE UNIQUE CLUSTERED INDEX [IX_Printed_Statistics]
    ON [dbo].[PrintedCodesStatistics]([AggregationLevelId] ASC);

And finally, Code table script:

CREATE TABLE [dbo].[Code] (
    [CodeId]            INT            IDENTITY (1, 1) NOT NULL,
    [Serial]            NVARCHAR (20)  NOT NULL,
    [AggregationLevelId]  TINYINT      NOT NULL,
    [CommissioningFlag] TINYINT        NOT NULL,
    [... ]
    CONSTRAINT [PK_CODE] PRIMARY KEY CLUSTERED ([CodeId] ASC),
    CONSTRAINT [UC_CODE_SERIAL] UNIQUE NONCLUSTERED ([Serial] ASC)

Do the views slow-down the update query?

Indexed views contribute to the write overhead as much as regular indexes on the tables do, if not more.

Every time you write to the base tables, the index on the view has to be updated immediately.

As with regular indexes, there is a tradeoff: if your index slows down reasonably and gives a significant performance boost on reads, then keep it. If not, get rid of it.


How can I optimize this mySQL table that will need to hold 5+ million rows? The table has about 12 rows.

Indexes:

idint(11), Unique, auto-increment, primary

ATTACHMENTIDvarchar(14), Unique

MLSNUMBERbigint(20)

POSITIONint(10)

FILETYPEvarchar(32)

I'm doing an extended insert with an ON DUPLICATE KEY UPDATE clause.
Each insert has anywhere from 300-10,000 records.
The more records in the table, the longer the query takes.

With 0 rows, the query took about 5-10 seconds.
After about 300,000 rows, the query was taking 50 seconds.

What would be the best way to optimize this table? Thanks in advance!

The reason it takes progressively longer when your table has a lot of rows is due to having to write the indexes. Try turning offing indexes for the table before inserting, then re-enable afterwards:

ALTER TABLE my_table DISABLE KEYS;

-- Your insert statement

ALTER TABLE my_table ENABLE KEYS;

Disabling keys only affects the updating of non-unique keys, so ON DUPLICATE UPDATE will still work as intended:

  ALTER TABLE ... DISABLE KEYS tells MySQL to stop updating nonunique indexes. ALTER TABLE ... ENABLE KEYS then should be used to re-create missing indexes. [src]


Row level security with a single DB user and connection pooling I'm using node-postgres to connect to a PostgreSQL 9.6 database with connection pooling enabled. All connections user the same database user. One reason I can't use multiple DB users is that as far as I read, row level security and views don't work well together in that case as the owner of the view is used for RLS.

I'm now looking at using row level security in Postgres, and I'd like to make sure I'm doing this correctly.

I'm using SET LOCAL to set the current application user id which is then used by the row level security USING clause. The only way I can think of achieving this with connection pooling is to wrap every query in a transaction with node-postgres and execute the SET LOCAL command like the following in every transaction.

SET LOCAL postgres.my_user = 20;

The following code is a simplified example of how I want to define the row level security, the real version has a few more conditions:

CREATE POLICY table_a_read_policy ON table_a FOR SELECT
USING (
    EXISTS (
    SELECT *
    FROM permissions
    WHERE
        user_id = current_setting('postgres.my_user')::int AND
        permission_type = 'read'
    )
);

My understanding is that any SQL injection in this case would also give the attacker the ability to just set any user id and circumvent row level security this way. But I don't see any way to avoid this as the db user I connect with must have the ability to see all data for the entire application.

There are a few points I'm not entirely sure about:

Is using SET LOCAL as a way to store the current application user identity for row level security safe? Especially in the context of connection pooling?
Is there a better way than wrapping every query in a transaction in node-postgres and executing the SET LOCAL command every single time?
Is there a way to structure this (with the restriction that the database connection is made using a single database user) in a way where an SQL injection would not automatically give the ability to circumvent row level security?

I have a similar setup on a new project.

The issue here is that you have a connection pool that uses a single login. The application that holds that connection pool will need to set the user correctly to get the required row level permissions. If your application is incorrectly configured / coded, then the permissions can be subverted.

It seems to me that row level permissions are only really useful when you have different users/roles with different logins reading from tables directly. In that case there is no where user = xxxx style clause that can be subverted. In this situation they make different connections.


Error restoring backup in sql server. Old server version When trying to restore a backup file (.bak) to SQL Server installed on my machine, I get the following error (steps that I follow to restore are: Right Click Database ---> Tasks ---> Restore)

Error message:

  The database was backed up on a server running version 10.50.2500.
  That version is incompatible with this server, which is running
  version 10.00.2573. Either restore the database on a server that
  supports the backup, or use a backup that is compatible with this
  server.

I have SQL Server 2008 R2 on my machine and under Help --> About, I see the following:

Microsoft SQL Server Management Studio    10.50.1600.1
Microsoft Data Access Components (MDAC)    3.85.1132

Question: I already have Service pack 2, what should I down and from where to be able to restore my back up.

While you may have SQL Server 2008 R2 on your machine (or at least the management studio), that doesn't stop you having or connecting to a SQL 2008 Server - which it seems you are.

 select @@version

will tell you the server version and its number.

10.50.2500 is SQL 2008 R2 SP1

10.0.2573 is SQL 2008 SP1

NB - SQL 2008 SP2 (Service Pack 2) is different to SQL 2008 R2.

To restore this backup, you need to connect to a 2008 R2 or 2012 server.


Map-Reduce on `system.profile` collection I have a simple collection posts with a few documents in it. I have set the profiling level to 2 allowing for profiling information to be collected into another collection system.profile. My goal is to use a mapReduce operation to collect information from the command entry grouped via various actors using this simple query:
db.system.profile.mapReduce(
    function()
    {
        emit( this.appName, this.command  );
    },
    function( key, values )
    {
        return JSON.stringify(values);
    },
    {
        query: {},
        out  : "command_maps"
    }
)

However, trying this in the REPL yields the following error
2021-05-10T22:09:33.494+0800 E QUERY    [js] Error: map reduce failed:{
    "ok" : 0,
    "errmsg" : "rename failed: { ok: 0.0, errmsg: \"error with source namespace: cannot write to 'test.tmp.mr.system.profile_22'\", code: 20, codeName: \"IllegalOperation\" }",
    "code" : 10076,
    "codeName" : "Location10076"
} :
_getErrorWithCode@src/mongo/shell/utils.js:25:13
DBCollection.prototype.mapReduce@src/mongo/shell/collection.js:1145:1
@(shell):1:1

I suspect mongo is trying to temporarily write into the system.profile collection and fails due to it being readonly in a sense. Is there any way to execute this successfully ?
Unfortunately the documents are too large to add here but basically its sort of like this:
DOCUMENT
{
   "appName": "actor1",
   "command": {"find": {},"ns" : "test.post""}
},
{  "appName": "actor2",
   "command": { "find": {},"ns" : "test.comments"}
}
{
   "appName": "actor1",
   "command": { "insert": {},}
}

OUTPUT
{  appName: "actor1": command_maps: '[{command: "find"}, {command:"query"} ...]' }
{    appName: "actor2": command_maps: '[{command: "find"}...]' }

Version: MongoDB shell version v4.4.5
PS     : mapReduce is working properly on regular collections

So it seems special readWrite privileges are needed to execute mapReduce on system.profile collection. So I got the query to work eventually after checking through:
https://docs.mongodb.com/manual/tutorial/manage-users-and-roles/