How to check Auditing in Azure MSSQL MI? I know that auditing is currently enabled in my azure MSSQL server, but how can I access these log files?
Could it be done through SSMS?

I need to know details about user activity in my databaase.

From https://docs.microsoft.com/en-us/azure/sql-database/sql-database-auditing

  Use Merge Audit Files in SQL Server Management Studio (starting with
  SSMS 17):

From the SSMS menu, select File > Open > Merge Audit Files.
The Add Audit Files dialog box opens. Select one of the Add options to choose whether to merge audit files from a local disk or import them from Azure Storage. You are required to provide your Azure Storage details and account key.
After all files to merge have been added, click OK to complete the merge operation.
The merged file opens in SSMS, where you can view and analyze it, as well as export it to an XEL or CSV file, or to a table.


Encrypt connection when using sqlcmd Trying to connect database with an encrypted connection, tried below sql queries:

sqlcmd -N -E -S tcp:SQLSERVER,1234  -U username -P password -d Database -Q "INSERT INTO table1 VALUES(1,2,2)

  ERROR: Sqlcmd: The -E and the -U/-P options are mutually exclusive.

sqlcmd -N -S tcp:SQLSERVER,1234  -U username -P password -d Database -Q "INSERT INTO table1 VALUES(1,2,2)

  ERROR Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : SSL Provider: The target principal name is incorrect. Sqlcmd: Error:
  Microsoft ODBC Driver 13 for SQL Server : Client unable to establish
  connection

sqlcmd -S tcp:SQLSERVER,1234  -U username -P password -d Database -Q -E -N "INSERT INTO table1 VALUES(1,2,2)

  Sqlcmd: '-Q': Missing argument. Enter '-?' for help.

Am I missing something here?

The first one failed because the -E flag is for Trusted Connections (Windows Auth) and -U/-P flags are for SQL Authentication. These are mutually exclusive and cannot be combined.

The third one failed because -Q must be followed immediately by the query you wish to execute. In your provided code, you've included other flags between -Q and the actual query.

The second one should work syntactically, so you need to investigate the cause of the

  The target principal name is incorrect

error. Take a look at some of the answers to this question and also validate that you can connect via SSMS, or you get the same error in SSMS.


SQL Server 2017 - Parameter Sniffing We are moving from SQL Server 2014 to 2017, I see in the options that we have Parameter Sniffing, I believe we should we keep it OFF, but what about the options that we used in 2014 to avoid that? can be affected in 2017, like creating local variables of the parameters and put option(recompile) to the queries.

All the ways of influencing parameter sniffing before SQL 2017 remain.  There's just a new database scoped configuration option to turn off parameter sniffing entirely.


Disk I/O High Activity What does the green and blue indicate from the below scrteenshots of disk activity on SQL Server database server?

Thanks!

This seems to be related to your earlier question about High Disk IO. Have you looked at other items to see what is going on as suggested in my answer there? Wait Stats, Query Information, Etc? I think you should look at everything. And in this case it still looks like your disk queue length on that drive at that one time isn't bad. You need to really look at your IO Latency as I mentioned in that answer. It looks to me like you are still hunting around for an IO issue, but should look at the full set of information as I mention in the answer there to really help you out.

But the question here: This screen is in the Resource Monitor, and the blue line refers to the active time of the drive. So in this case you had a quick spike of higher activity on the K drive and mostly the drive wasn't very active.


What is the function that determines the maximum value in a series? Say I have a table with columns C and D (among others but these are the relevant ones) with the date type.

I am instructed to update the values in C to be 90 days before its corresponding value in D, or the current date, whichever is later.

I'm given the hint that it "will be useful to search for the Oracle function that determines the maximum value in a series. Hint: it is not max(), which is an aggregate function!"

I have no idea what this function is and would appreciate any help!

The function is called GREATEST()

  GREATEST returns the greatest of a list of one or more expressions. Oracle Database uses the first expr to determine the return type. If the first expr is numeric, then Oracle determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that data type before the comparison, and returns that data type. If the first expr is not numeric, then each expr after the first is implicitly converted to the data type of the first expr before the comparison.


What is locking order of MySQL on query with join statement When I select for update from join multiple tables, matching rows in all joined tables will be locked.  But I was wondering what is the locking order, which rows are locked first? Is it determined by where table names are in the join statement?

For example, I have two tables. user and department.

    | user  | CREATE TABLE `user`
(  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(255) DEFAULT NULL,
  `gender` tinyint(4) DEFAULT NULL,
  `department_id` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `status` varchar(63) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `department_id` (`department_id`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1

     department | CREATE TABLE `department` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(63) DEFAULT NULL,
  `status` varchar(31) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

I have two queries.

 explain
select  user.name as name, user.age as age, department.name as dept_name
    from  user
    INNER JOIN  department  ON user.department_id = department.id
    where  user.status='on'
      AND  department.status='operating'
    order by  user.age
    limit  1 for  update  \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
         type: ALL
possible_keys: department_id
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: department
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
        Extra: Using where; Using join buffer
2 rows in set (0.00 sec)

 explain
select  user.name as name, user.age as age, department.name as dept_name
    from  user
    INNER JOIN  department  ON user.department_id = department.id
    where  user.status='off'
      AND  department.status='operating'
    order by  user.age
    limit  1 for  update  \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
         type: ALL
possible_keys: department_id
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: department
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
        Extra: Using where; Using join buffer

SELECT user.id as user_id, order.id as order_id, department.id as dept_id
    FROM user
        INNER JOIN order ON user.id = order.user_id
        INNER JOIN department ON department.id = user.department_id
    WHERE user.age > 30 AND department.name in ('sales')
    LIMIT 10 FOR UPDATE

Not clear about the result of explain. It does not show which rows are locked.

Are rows in table user locked first, and then order and department? Or the matching row locked first?

It is quite natural for the Optimizer might pick different query plans for those two queries.  Even if the EXPLAINs look similar today, they could be different tomorrow.

Regardless, you should plan on deadlocks; it is often futile to try to eliminate all of them.  When you get a deadlock, replay the entire transaction; it is very likely to succeed the second time.

Yes, there can be edge cases where two "independent" queries can collide.  This is probably because of edge cases where gap locking collided - or something like that.

There might be cryptic clues in SHOW ENGINE INNODB STATUS;

This index on user might decrease the frequency of deadlocks:

INDEX(status, age)

And it is likely to speed up the queries when you have a lot of rows.


MySQL query optimization I'm quite new to big MySQL queries (I used to extract raw data from tables then manipulate them with PHP) so I'd like to know if my query below is too "heavy" or slow for some reason (it seems to be too big to me) and how I can optimize it. Here is my query:

SELECT
    L.id,
    L.role,
    L.first_name,
    L.last_name,
    L.birth_date,
    L.team_name,
    L.team AS team_id,
    L.photo,
    L.email,
    L.played,
    L.vote_average,
    L.goal_sum AS goal,
    L.score + IFNULL(L.c_mvp, 0)*10 AS score,
    L.y_card_sum AS y_card,
    L.r_card_sum AS r_card,
    IFNULL(L.c_mvp, 0) AS mvp,
    IFNULL(unbeaten, 0) AS unbeaten
FROM (
    SELECT * FROM (
        SELECT
            dc_player. *,
            dc_team.name AS team_name,
            COUNT( dc_match_entry.player_id ) AS played,
            AVG( dc_match_entry.vote ) AS vote_average,
            SUM( dc_match_entry.goal ) AS goal_sum,
            SUM( dc_match_entry.vote ) *10 + SUM( dc_match_entry.goal ) *10 - SUM( dc_match_entry.r_card ) *10 - SUM( dc_match_entry.y_card ) *5 - SUM( dc_match_entry.own_goal ) *10 AS score,
            SUM( dc_match_entry.y_card ) AS y_card_sum,
            SUM( dc_match_entry.r_card ) AS r_card_sum
        FROM
            dc_player,
            dc_match_entry,
            dc_team
        WHERE
            dc_player.id = dc_match_entry.player_id AND
            dc_player.team = dc_team.id
        GROUP BY
            dc_player.id
    ) LOL
    LEFT JOIN (
        SELECT
            COUNT(dc_match.mvp) c_mvp,
            mvp AS player_id
        FROM dc_match
        WHERE
            dc_match.mvp IS NOT NULL
        GROUP BY dc_match.mvp
    ) ROFL
    ON ROFL.player_id = LOL.id
    ORDER BY
        LOL.score DESC
) L
LEFT OUTER JOIN(
    SELECT
        dc_player.id,
        COUNT(dc_match.id) AS unbeaten
    FROM
        dc_match,
        dc_match_entry,
        dc_player
    WHERE
        (
            (dc_player.team=dc_match.host AND
            dc_match.guest_score=0
            ) OR
            (
                dc_player.team=dc_match.guest AND
                dc_match.host_score=0
            )
        ) AND
        dc_player.id = dc_match_entry.player_id AND dc_match.id = dc_match_entry.match_day
    GROUP BY
        dc_player.id
) I
ON
    L.id = I.id
GROUP BY
    id

I can provide table structures too if needed.

It may be miserably slow (until 5.6.7 or MariaDB 5.5) because of the following.  Let me give a simplified version:

SELECT ... FROM ( SELECT ... ) a
JOIN ( SELECT ... ) b ON a.x = b.x

There are (and cannot be) indexes on either of the temp tables (a, b).  If there are, say, 10K rows in each of a and b, then the JOIN will have to do 100M compares (10K*10K).  Slow.

If one of the subqueries boils down (via GROUP BY, LIMIT, or...) to only a few rows, then performance won't be too bad.

As a general rule, avoid subqueries.  Some subqueries are ok -- namely those that shrink the number of rows (GROUP BY, etc).  But the JOIN problem above still stands.

The general 'fix' for a subquery is to turn it into a JOIN.  But that does not apply here because most have GROUP BY.  So... Consider CREATEing TEMPORARY TABLEs with indexes for some or all of the subqueries.

Another performance killer is OR.  Generally it makes it impossible to use an index on the fields in the OR.  Sometimes the fix is to do two SELECTs and UNION ALL/DISTINCT them.

Wade through some of those suggestions, then come back for more advice.  Bring SHOW CREATE TABLE next time.


Limiting user access by subnet to Amazon Aurora PostgreSQL How do I limit user access by IP? For example, assuming I have two non-superusers (user1 and user2), how can allow general access (only limited by security groups) to the instance/cluster by user1 but only allow access to a particular subnet for user2?

In standalone PostgreSQL this is handled within pg_hba.conf, which to my knowledge doesn't exist in RDS and Aurora on AWS.

pg_hba.conf equivalent:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    app_db          user1           0.0.0.0/0               scram-sha-256
host    app_db          user2           10.0.12.0/24            scram-sha-256

here are two ideas that can be tried..

Run a script or other little app in the background that scans through the killing connections that do not match the established rules
it fires off every few seconds ..

Or create a system event trigger that fires off checks the user IP address and any other information that needs to be checked when the connection is made,  if it fails the checks  call

select  pg_terminate_backend(pid) --or
select  pg_cancel_backend(pid)

The information needed is located in

select * from pg_stat_activity


Database design to record occurences in tv show I want to make a database where I record the guests of each episodes of a tv show. I want to be able to do the following stastics:

What guest had the most appearences?

Which two (or three) guests appeared together the most?

How many unique guests did the show have each month/year?

What are the gender distribution between the guests?

What show had the highest number of guests?

What is the average number of guests on each episode of the show?

I also collect the description of each episode, so I want to find the most common topic for the episodes, which topic has which guests etc. The topics is retrieved from keywords in the episode description. An episode has everywhere from 2 to 10 topics.

...and possible more statistics I find that I want along the way.

However, how should I model it? Since each guest can have hundreds of apparences, I don't think an relational database where I map guest to episode is smart ->

| Guest Name | | Episode appeared in |

Since the second column can be filled with values.

Note that each episode can have ten to twenty guests as well.

Is a FACT table a better design, where I have the following:

|Date/Episode number | | Guest ID |

with an own guest info table?

I think given the requirements and that at some point you may want additional information that wasn't listed here, I would go with a fully normalized approach.
3 Tables: Guests, Episodes, Episode_Guests
Then depending on if you want to do this for more than one show, another table for Shows(or series).
As Paparazzi mentioned, the Guests table should contain sex.  The Episode table should contain a date.  Also, if you are going to do this for multiple shows, the Episode table should also have a foreign key back to the Shows table.
The Episode_Guests table should record every instance of a Guest appearing on an Episode so all it would need is a foreign key relationship back to guests and another for Episode.


SQL Server: reset all database tables I have a SQL Server 2008 R2 database with about 60 tables and around 300 columns. I want to reset all the data and auto increments, I found a question somehow similar but the answer there is to recreate database.

Is there any way to keep the database and just reset it?

Recreate is the better option, because:

You end up with a script to create the db
It's dirt simple.
You don't have to take the complexity of your schema in to account.
SQL Server will be more than happy to create the script for you.

The other option is to write something that looks through the schema, identifies tables with foreign key constraints so they can be dropped, drops them, then finds the identities and reseeds them, then puts the FKs back

Or if you are a masochist, you could write (well try to) something to use the dependency order

Both of options 2 and 3 are high maintenance.


SQL Server - creating a clustered index in a large heap - should I add a column? I'm going to add a clustered index to a fairly large table:

over 7 million records
106 columns
no PK
no other indexes.

There is an Id column which could potentially be used for the index key, but it's nvarchar(18) and not unique (although there are no duplicate values there).

I'm however reluctant to use it because of its datatype (performance impact) and have been thinking of adding an int identity(1,1) column and use it as the clustered index key.

Which solution would you rather recommend?
TIA

In general, a surrogate ID (an incrementing number that doesn't have any other meaning) is the best choice unless you have a specific reason why it won't work.

One of the biggest problems with using a non-incrementing value for a primary key is that you'll have page splits on some inserts, which will cause them to be slower.  How much slower depends on a lot of factors, so it's just something you'd have to measure on the system.

Google "benefits of surrogate primary key" and you'll find a ton of information.  It's really a small book to cover the topic, so the full answer isn't really appropriate for this site.  This question also has a few answers that provide good information on this topic.


What's causing this ERROR 2000 (HY000): Unknown MySQL error I have a MySQL server with version 5.6.12-log (it's running on OpenSUSE). I can connect to it from another server (CentOS 6) using:

mysql -h **** -u **** --port **** -p

It prompts me for my password and connects just fine. The client version is "Ver 14.14 Distrib 5.6.12, for Linux (x86_64) using  EditLine wrapper."

However, from another server (running CentOS 7), using the same command, I get the following error after entering my password:

ERROR 2000 (HY000): Unknown MySQL error

This error is useless. There is nothing in the client machine logs that indicate the cause of the failure. However, I do know that the MySQL server is at least receiving the connect request because it logs a warning about the Reverse DNS lookup not matching. It's just a warning—not the cause of this problem—but it proves that firewalls and/or network configurations aren't blocking the connection. There is nothing else in the MySQL server log (or error log) that indicates the cause of the failure. This client version is "Ver 14.14 Distrib 5.6.23, for Linux (x86_64) using  EditLine wrapper."

Help??? I'm lost here. In 10+ years of MySQL admining, I've never seen this error before. I don't even know where to begin.

Well I figured it out, and it isn't pretty. I guess I'll have to file a bug with MySQL.

The server is compiled with OpenSSL.
The working client is compiled with OpenSSL.
The client that can't connect is compiled with yaSSL.

The user I was trying to connect with used the sha256_password authentication plugin. When I dropped and re-created the user with mysql_native_password instead, the problem went away and I can connect from all clients. It appears that the sha256_password plugin doesn't work if the server and client are compiled against different SSL providers. (It might not even work with yaSSL at all.)


Empty Table: Insert into Table WITH (TABLOCK) We have a clustered index table which is empty. Table has around eg: 20 columns, 50 million rows, around (40GB).

Is it best practice to apply Insert With (TABLOCK) for empty tables such as this? The table is a datawarehouse table, and conducted during ETL hours. There are no concurrent users, and we are in simple recovery mode. Just curious, if so we can update our 100+ stored procedures.

Like many things database-related this option solves some problems but can cause others.

By using TABLOCK you will avoid lock escalation. By using TABLOCKX you may even avoid lock conversion. The downside is that other activity against that table must wait for this lock to be released. You say there is no concurrent activity, so that shouldn't be an actual problem in real life. Taking locks is quite cheap and fast, so I don't really think that would be a problem either.

So my answer is - what problem are you trying to solve? Will the work of changing 100+ stored procedures be repaid by the improvement you see?


Index for a complex similarity query Postgres I have a materialized view that has a few columns that I need to run a complex OR query on with similarity matching and the structure is like this

mymatview:

column a,
column b,
column c,
column d,
column e

and on column D (which is a TEXT field) I run a query like this

SELECT * FROM mymatview
WHERE ((LOWER(d) = <<some value>> ) OR (LENGTH(d) > 4 AND d % <<some value>>))
    OR ((LOWER(d) = <<some other value>> ) OR (LENGTH(d) > 4 AND d % <<some other value>>))

Currently the matview has 1 mil+ rows in it and it runs a sequence scan on that table using the filter above with the OR conditionals. Does anyone know what a good index would be on column d that would help with performance on running a query like this?

UPDATE: I am using the package pg_trgm for the '%' similarity operator here. Postgres 9.6 https://www.postgresql.org/docs/9.6/pgtrgm.html

Your only chance is to index all possible conditions, perhaps PostgreSQL will use bitmap operations to cover the ORs. Perhaps it will think that a sequential scan is cheaper and go for that.

CREATE INDEX ON mymatview (lower(d));

CREATE INDEX ON mymatview USING gin (d gin_trgm_ops) WHERE length(d) > 4;


Does anyone ever use database sequences that count down from 2^64-1? Does anyone ever use database sequences that count down from 2^64-1, rather than up from 1, for assigning ids to rows?

The motivation for doing this would be to ensure that any application consuming these ids does not encounter an overflow bug at some bit width < 64, the most obvious being trying to use a 32-bit int to hold the id. If you start at the top, problems should arise sooner during development.

Never seen it done, certainly not for that reason.

Speaking for SQL Server, it would work fine with a Bigint Identity column. But you'd have to ask whether it's worth the confusion it could cause, and the need to remember to define the clustered index descending to prevent fragmentation, etc.


How do Availability Groups work with cheduled jobs and SSIS packages? I have 2 SQL Servers set up with a database in an AlwaysOn Availability Group.

I also have jobs set up which run against the database. During a failover how do I ensure the jobs will continue to run on the secondary server? Do I need to install the jobs and SSIS packages on both machines and manually disable them on the secondary machine... then manually enable them in the case of a failover? Or is there built in functionality to handle this?

Yes, you will need to create the jobs on any other replicas that you would want those specific jobs to run if they were the primary replica.

You will need to create your own logic for if/when each SQL Server Agent job will run.  For instance, do you want to run a job only if the current instance is the primary replica of a particular Availability Group?  You will need to put that into your job.  It can't be blanketed automatically, because that would take away the flexibility of AlwaysOn AG.  Whether you want them disabled on the secondary replica(s) is completely up to you, what those jobs do, and how/when/if you want them to run.

Remember, the secondary replica server isn't just a stand-by server waiting for failover.  It could be a fully functional, accessible server.  Because of this, having every job sitting by idle would be a huge disability.

So, yes, you will need to push our your jobs to other replicas and use some logic as to if the job should continue execution when it kicks off.

For instance, backup jobs can take advantage of the sys.fn_hadr_backup_is_preferred_replica function by determining whether the current replica is the preferred one for a particular database.  This will derive how you have your Availability Group setup for backup preferences.


In which extent can I use the Oracle Data Provider for .NET Managed Driver? Good evening everybody,

I am planing to dive into the oracle database world. Additionally, I want to develop some tools, which make my life easy by checking and modifying information in different tables.

Since I would develop the tools in C#, I found the Oracle Data Provider for .NET Managed Driver, which seems to be really nice.

Before using it, I have some questions:

Is it free to use?
Regarding to the license (http://www.oracle.com/technetwork/licenses/distribution-license-152002.html), I should be able to use the managed driver. Or do I interpret it in a wrong way?

For me it is not clear, if I could also use my tools at customer side? Or could I give the tool to some customer to help him or does it lead to a license problem already?
What about selling the tool? For me it is hard to interpret the answers from the license text.

I hope you can help me.

Best regards
Seth

Oracle Data Provider for .NET FAQ

  Q: I bundle ODP.NET with an application I've built, which I want to distribute or sell. Does Oracle charge licensing fees for this?

  A: In most cases, Oracle does not charge any fees for distributing the
  Oracle client nor ODP.NET with a third-party application. However, it
  is best to consult the licensing agreement from which you acquired
  rights to the Oracle software. If you purchased Oracle licenses
  directly from Oracle, consult your Oracle licensing contract or your
  Oracle sales representative. If you downloaded ODP.NET from OTN,
  consult the licensing agreement that you agreed to prior to
  downloading the software.


Restore of database failed I wanna restore my database and i use sql server 2012 express edition but when i restore my backup file in sql server management studio, i take this error:

  System.Data.SqlClient.SqlError: There is insufficient free space on disk volume 'c:\' to create the database. The database requires 84260749312 additional free bytes, while only 47428677632 bytes are available. (Microsoft.SqlServer.SmoExtended)

My .bak file is 8.27 gb and my C disk has 44.1 gb free space. Why do i take this error ? how can i fix this error ? Thanks in advance.

As Max Vernon states, the database is trying to restore to a location that doesn't have space to contain it.  If the backup file is compressed or mostly empty, you will still need the full uncompressed space to restore to.  Additionally, by default SQL Server will try to restore those files to their original file location, so if that original location does not exist or does not have enough space when you restore, the restore will fail.

In order to see the space and location required for a backup, you can run the following command:

RESTORE FILELISTONLY
FROM DISK='<backup file location>'

The output from this will show all files contained within the backup.  Look at PhysicalName and Size for the restore location and size (in bytes) that SQL Server needs for the restore.  You can change the restore location by specifying the WITH MOVE syntax in your restore statement, but as Max states, there's not much you can do about the space required.


Trigger/Event that fires when a Transaction finishes I have a query that pulls from about 15 different tables.  I am looking to materialize this into a table that stores it in xml/json.  (To improve performance.)

The problem I have is that these tables are updated by several processes.  I am looking for a way to keep this in SQL Server if possible.

Ideally, I would love it if SQL Server had a trigger that would fire right before a transaction commits, so I could look at the tables and records that were affected and know if I need to update the "result" table.

Is there something like that in SQL Server?

NOTE: I have considered using the INSTEAD OF trigger, but I have no way of knowing the order of the tables in the transaction, so if the transaction updates all 15 tables, then I will be updating the "result" table 15 times for the same row.

Man, I'm terribly sorry.

I'm 'answering' stating that there's no simple solution as the one you devised.

You presented us a very interesting challenge.

I've been doing research and exercises in the past four hours to assure there's no way one can collect the aftermath of a committed transaction relying upon SQL Server engine by itself.

If you, please, could show us the grand view of the solution architecture, we would devise a least effort solution.

For instance, in the scenario:

one or two ADO.Net applications
one or two EntityFramework applications
one or two SSIS jobs

each potentially modifying the data on those tables, I would:

create a trigger for each table just to flag it has been tempered
create an intercption on each application calling a SP that would deal with the flagged data

and, for the interception points, I would:

on ADO.Net apps - supersede/override AdoDbConnection and rely on VS to quickly and safely refactor the code
on EF apps - override SaveChanges from DbContext
on SSIS jobs - include a call at the end of each one

Below I will present:

the schema of the table that will register events to be processed
a script that will generate the triggers for targeted tables
the snippet of what should be the processing stored procedures, called by the extension points listed above

Registry of events to be processed

To register records to be post-processed, I would design a single table such as:

IF EXISTS(SELECT * FROM sys.tables WHERE name = 'TBL_2PROC') DROP TABLE TBL_2PROC
go

CREATE TABLE TBL_2PROC(
    session_id     INT,          -- the session ID - it's expected each app transaction (or session) to have
                                 -- its own, exclusive, not shared, connection, even if pooled
    transaction_id BIGINT,       -- to assure grouping -> (session_id, transaction_id)
    event          VARCHAR(255), -- string stating an INSERT, UPDATE or DELETE
    tblName        VARCHAR(255), -- name of the table affected
    keyId          VARCHAR(255)  -- value of the (single) id column, converted to varchar
)
go

Making sure no deprecated trigger be left alive

Before creating the actual triggers, we must be sure no old one be left:

-- Clean-up existing ones ------------------------------------------------------
--------------------------------------------------------------------------------

DECLARE cCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT name
FROM sys.triggers
WHERE name LIKE 'TR_2PROC%'
--
DECLARE @trName VARCHAR(255)
--
DECLARE @sql NVARCHAR(MAX)

OPEN cCursor
FETCH cCursor INTO @trName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 'DROP TRIGGER [' + @trName + ']'

    EXECUTE sp_executesql @sql

    FETCH cCursor INTO @trName
END
go

Parameterizing targets

Before programmatically create the needed triggers, we have to declare which they will be:

-- Parameterize the target tables and theirs key columns -----------------------
--------------------------------------------------------------------------------

CREATE TABLE #to_log(tblName VARCHAR(255), keyName VARCHAR(255))
go

INSERT INTO #to_log VALUES('SomeTable',      'Id')
INSERT INTO #to_log VALUES('SomeOtherTable', 'OtherId')
go

Programmatically creating the needed triggers

Being the targets set, this script create the triggers:

-- Setup triggers --------------------------------------------------------------
--------------------------------------------------------------------------------

DECLARE @tblName VARCHAR(255)
DECLARE @keyName VARCHAR(255)
--
DECLARE @sql NVARCHAR(MAX)
--

DECLARE cCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT tblName, keyName
FROM #to_log

OPEN cCursor
FETCH cCursor INTO @tblName, @keyName

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @triggerName VARCHAR(255)

    SET @triggerName = '[TR_2PROC_' + @tblName + ']'

    -- Drop if exists ----------------------------------------------------------

    SET @sql = N'
        IF EXISTS(SELECT * FROM sys.triggers WHERE name = ''' + @triggerName + ''')
        DROP TRIGGER ' + @triggerName

    EXECUTE sp_executesql @sql

    -- Create ------------------------------------------------------------------

    SET @sql = N'
        CREATE TRIGGER ' + @triggerName + '
        ON [' + @tblName + ']
        AFTER INSERT, UPDATE, DELETE
        AS
        BEGIN
            DECLARE @session_id     INT
            DECLARE @transaction_id BIGINT

            SELECT
                @session_id     = session_id,
                @transaction_id = transaction_id
            FROM sys.dm_tran_session_transactions
            WHERE session_id = @@spid

            INSERT INTO TBL_2PROC
            SELECT
                @session_id, @transaction_id,
                CASE
                    WHEN del.[' + @keyName + '] IS NULL THEN ''INSERT''
                                                        ELSE ''UPDATE''
                END,
                ''' + @tblName + ''',
                CONVERT(VARCHAR(255), ins.[' + @keyName + '])
            FROM
                inserted ins
                    LEFT OUTER JOIN deleted del
                    ON del.[' + @keyName + '] = ins.[' + @keyName + ']

            INSERT INTO TBL_2PROC
            SELECT
                @session_id, @transaction_id,
                ''DELETE'',
                ''' + @tblName + ''',
                CONVERT(VARCHAR(255), del.[' + @keyName + '])
            FROM deleted del
            WHERE
                del.[' + @keyName + '] NOT IN(
                    SELECT ins.[' + @keyName + ']
                    FROM inserted ins
                )
        END'

    EXECUTE sp_executesql @sql

    FETCH cCursor INTO @tblName, @keyName
END
GO

Some clean-up

Followed by some clean-up:

-- Clean-up --------------------------------------------------------------------
--------------------------------------------------------------------------------

DROP TABLE #to_log
go

... and, the processing happens here!

Now, the real deal.

Last but far from least, you are left with the job of writing the processing procedure.

Even if it were possible to intercept a COMMIT by a trigger, the resulting output to be processed would be very similar with this.

IF EXISTS(SELECT * FROM sys.procedures WHERE name = 'SP_2PROC') DROP PROCEDURE SP_2PROC
GO

CREATE PROCEDURE SP_2PROC AS
BEGIN
    -- Important!
    --
    -- Use WITH(NOLOCK) to avoid dead-locks
    --
    -- Do NOT use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED here,
    -- unless you know what you are doing
    -- (this isolation level is often safe in other contexts)

    -- You, now, shall build your process up from here, based on the
    -- query below

    SELECT *
    FROM
        TBL_2PROC WITH(NOLOCK)
    WHERE session_id = @@SPID
END
GO


mysql load data infile takes too long and db file grows too large I am trying to import pretty large file (3.5G with 39mlm rows) with

set foreign_key_checks=0;
set sql_log_bin=0;
set unique_checks=0;
load data infile '/var/lib/mysql-files/2.csv' ignore
into table main_data fields terminated by ','
optionally enclosed by '"'
lines terminated by '\n'
ignore 1 rows (col, col2, col3);

It already passed like 24 hours, and database file in /var/lib/mysql/MyDB grow to 11G, I am not sure why, because the file I import from is less than 4G.
It is mysql 8.0. The only things I added to config is

innodb_buffer_pool_size = 22G
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
innodb_autoinc_lock_mode=2

I think if I batch insert these data it would be faster...

As suggester in 1st comment here is creat table syntax.
I now think that so many indexes can be the issue with size and time :

CREATE TABLE `main_data` (
  `file_name` tinyint(4) NOT NULL,
  `line_number` int(11) NOT NULL,
  `link` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `title` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `doc_type` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `meta_keywords` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `meta_description` text COLLATE utf8mb4_unicode_ci,
  `language` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `done` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`file_name`,`line_number`),
  UNIQUE KEY `link_UNIQUE` (`link`),
  KEY `done_idx` (`done`),
  KEY `lang_idx` (`language`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

I only import 1st three columns (file_name, line_number, link), rest are nulls.

24 hours is a very long time- the most important factor when running LOAD DATA is the buffer pool, and yours (22GB) should be enough to accommodate the imported 3.5G file. Something else must be going on, please share more data like the SHOW CREATE TABLE output for the used table and SHOW PROCESSLIST- maybe there is something blocking the table and you are waiting for the process to start. You can check at any time the state of the load by checking the GLOBAL STATUS for innodb%' as well as theHandler_writes`.

To give you an idea of how much I would expect this to take, a 3.7 GB file should not take much more than 200 seconds to safely load on an SSD desktop-grade machine. See these tests I did not a long time ago on all recent MariaDB and MySQL versions on a non-server grade machine, even on a default (bad) config: https://dbahire.com/testing-again-load-data-on-mysql-5-6-5-7-8-0-non-ga-and-mariadb-10-0-10-1-and-10-2-non-ga/


Reduce transaction log file utilisation I have a SQL Server 2017 database that currently has an 80GB data file and a 90GB log file. I want to get the log file down to a more manageable size, because I am about to run some big scripts which will hit the log file hard. The database is also replicated to another database.
If I run dbcc sqlperf('LOGSPACE') then it tells me that utilisation of the log file sits at 99%, and this is preventing me from shrinking the log file down to a more manageable size. For instance, if I run dbcc shrinkfile(mydb_log, 4096) then the error is Cannot shrink log file 2 (mydb_log) because of minimum log space required.
I have tried backing up the database then backing up the transaction log, in full recovery mode. I have tried switching to simple recovery mode, then backing up the database then shrinking the database, but the log file utilisation still sits at 99%.
Does anyone have any suggestions as to what to try next? Is replication likely to be causing an issue? The databases currently sit on a test system, so I can take more extreme measures than if they were in production.

It looks like replication was not set up properly; I'm guessing that that means that when a transaction gets written to the log and replication fails, the log entry doesn't get marked as ready-to-be-cleaned-up, so the entries just pile up and the log utilisation goes up with it.
I deleted all replication configuration, and the log file utilisation dropped from 99% to 0.1%, so now I can drop the log file back to something more manageable.


How to set listener.ora and tnsnames.ora for oracle instances I set listener.ora and tnsnames.ora, but I do not know if it is well set up.
When I call the lsnrctl status command, I get instances rcadb and proddb unknown.

I'm a beginner in Oracle, I installed the Oracle 12c pre-built.

Now that I've made two databases, I have this problem, I can not find a mistake.

The database orcl12c I got with the installation,
had host = 0.0.0.0 which I changed

Listener.ora:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl12c)
      (SID_NAME = orcl12c)
      (ORACLE_HOME = /u01/app/oracle/product/12.2/db_1)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = prodDB)
      (SID_NAME = prodDB)
      (ORACLE_HOME = /u01/app/oracle/product/12.2/db_1)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = rcatDB)
      (SID_NAME = rcatDB)
      (ORACLE_HOME = /u01/app/oracle/product/12.2/db_1)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.2)(PORT = 1524))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.3)(PORT = 1525))
    )
  )

And I set tnsnames.ora like this:

ORCL12C =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl12c)
    )
  )

prodDB =
 (DESCRIPTION =
    (ADDRESS = (PROTOCOL=TCP)(HOST = 127.0.0.2)(PORT = 1524))
    (CONNECT_DATA =
       (SERVICE_NAME = prodDB)
    )
 )

rcatDB =
 (DESCRIPTION =
    (ADDRESS = (PROTOCOL=TCP)(HOST = 127.0.0.3)(PORT = 1525))
    (CONNECT_DATA =
       (SERVICE_NAME = rcatDB)
    )
 )

LISTENER_ORCL12C =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

lsnrctl status

Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.2)(PORT=1524)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.3)(PORT=1525)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=8081))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "51c99766d7e2568de0530100007f4fae" has 1 instance(s).
  Instance "orcl12c", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl12c", status READY, has 1 handler(s) for this service...
Service "orcl12c" has 2 instance(s).
  Instance "orcl12c", status UNKNOWN, has 1 handler(s) for this service...
  Instance "orcl12c", status READY, has 1 handler(s) for this service...
Service "orcl12cXDB" has 1 instance(s).
  Instance "orcl12c", status READY, has 1 handler(s) for this service...
Service "prodDB" has 1 instance(s).
  Instance "prodDB", status UNKNOWN, has 1 handler(s) for this service...
Service "rcatDB" has 1 instance(s).
  Instance "rcatDB", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

EDIT:

[oracle@localhost ~]$ unset TWO_TASK
[oracle@localhost ~]$ . ./.profile_productionDB
[oracle@localhost ~]$ sqlplus sys/oracle as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jan 15 05:33:45 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select name from v$database;

NAME
---------
PRODDB

SQL>

Status UNKNOWN is not necessarily a problem. It could happen if your start your listener before your database, then your listener does not know the status of your database.

When you do :

[oracle@localhost ~]$ sqlplus sys/oracle as sysdba

You are connecting to your database locally, whithout using your listener. You can stop your listener and try to connect. It should work.

If you want to test your tnsnames/listener, first create a test user :

[oracle@localhost ~]$ sqlplus / as sysdba
create user test_tns identified by "test_tns";
grant create session to test_tns;
exit;

Then test with :

[oracle@localhost ~]$ sqlplus test_tns/test_tns@rcadb


How to determine if an Index is uselss in Percona MySQL? Using Percona Server 5.5

I've a table which stores user activty (lets call it user_activity). It was created quite a few years ago when I had only a little bit of knowledge about database optimisation. Thusly, I created a few indexes to cope with the different select queries I was likely to run into. Unfortunatly, the indexes are massivly out growning the size of the data and now the table is the largest table I have in my database. Because of this, the more intensive update queries are taking extremly long to run and often cause table locks.

So, I want to see what I can do to improve the table's efficiency in every way and thought I would start with analyzing the indexes we have. Unforutnatly, I can't find a way to determine if an index is needed and with a table this large, i'm not in a position to just remove and re-add as that could take hours to do.

What is the best method to check if an index is needed or can be removed or modified?

Here are the table and index stats:

TABLE:

Engine: InnoDB
Row format: Compact
Column count: 12
Table Rows: 764,640,132
AVG row length: 66
Data length: 47.3 GB
Index length: 168.7GB
max data length: 0 bytes
data free: 6mb

INDEXES:

PRIMARY
BTREE (clustered)
Unique: Yes
Cardinality: 764634204

---------------

actions
BTREE
Unique: no
Cardinality: 18
columns: action_type, action_id

---------------

parent
BTREE
Unique: no
Cardinality: 18
columns: section_parent_type, section_parent_id, status, added_date

----------------

Index_parent
btree
unique: no
cardinality: 18
columns: section_parent_type,added_date,user_activity_id

----------------

Index_parent2
btree
unique:no
cardinality: 18
columns: section_parent_type, section_parent_id,parent_id,parent_type,status,added_date

----------------

Index_actions
btree
unique:no
cardinality:18
columns: section_parent_type, parent_type,parent_id,action_type,added_date

COLUMNS

user_activity_id - int(10), notnull, autoinc (primary)
user_id - int(10), notnull
added_date - datetime
action_type - int(10), notnull
action_id - int(10), notnull
section_parent_type - int(10), notnull
section_parent_id - int(10), notnull
status - tinyint(3), notnull
parent_type - int(10), notnull
parent_id - int(10), notnull
notification - tinyint(3), notnull
friend_activity - tinyint(3), notnull

Percona themselves have written a tool for this very purpose - it's called pt-index-usage and is to be found here.

From the DESCRIPTION:

  This tool connects to a MySQL database server, reads through a query
  log, and uses EXPLAIN to ask MySQL how it will use each query. When it
  is finished, it prints out a report on indexes that the queries didn’t
  use.

Percona's servers, tools, blogs and employees are highly regarded parts of the MySQL ecosystem/community.


How to make middle and suffix matching using full text search? I'm trying to find a string on a database using FTS on Postgres:

select name
from users
where to_tsvector('simple', name) @@ to_tsquery('simple', 'ad:*')

But the :* search only the words start with ad.

I found this question Get partial match from Get partial match from GIN indexed TSVECTOR column column.

But it doesn't mention how to solve the problem of middle and suffix matching using FTS?

The "Full" in Full Text Search means match full words, not parts of words

You should use RegExp or LIKE or tri-gram matching instead

You can mimic suffix matching by reversing words in your index and also reversing your queries, but this takes more space:

select
reverse('brown fox')::tsvector @@ (reverse('rown') || ':*')::tsquery --true


Table partitioning and updates / inserts I'm planning to partition a product table on a website based on the status of the product: 0 or 1 (disabled or active). But I didn't quite understood if the partitioning will work after inserts / updates, as some disabled products may become active and vice versa, plus new active products will be inserted.

Will all the products go automatically to the designed partition after inserts / updates?

Yes it works. The right partitions are automatically populated when inserting new rows or updating existing records.


Accept null value on parent column in multipart relationship EDIT: I've come up with one poor solution (below) that requires some denormalization, but does avoid the risks of UDFs in CHECK constraints. I would appreciate your help if you have a better answer.

Each Category, except any 'built-in' Category, is only visible to
its respective Owner.
Each item ("OwnedItem") must be assigned a Category that is visible
to the item's Owner.

The challenge: since Owners can only see their own Categories and 'built-in' Categories, the data is invalid (for example) if one Owner's items are assigned a Category from another Owner, unless that Category is owned by no one.

Because of this, I would like to accept a null value (or a fixed value, e.g. -1) on one column of the parent in a multi-column relationship.

-- Categories have owners, or may be 'globally shared'
CREATE TABLE Category (
    Id int IDENTITY(1,1) NOT NULL,
    OwnerId int NULL,
    Title nvarchar(50) NOT NULL,
    CONSTRAINT PK_Category PRIMARY KEY (Id ASC)
)

SET IDENTITY_INSERT Category ON
GO
INSERT INTO Category
    (Id, OwnerId, Title)
VALUES
    (-1, NULL, 'Hidden'),
    (0, NULL, 'Default')
GO
SET IDENTITY_INSERT Category OFF
GO

-- Items have owners,
-- and must be assigned a category from the same owner,
-- or be assigned a global category
-- i.e. prevent assignment of items to another Owner's private Category
CREATE TABLE OwnedItem (
    Id int IDENTITY(1,1) NOT NULL,
    CategoryId int NOT NULL,
    OwnerId int NOT NULL,
    Description varchar(50) NOT NULL
    CONSTRAINT PK_Ownedtem PRIMARY KEY (Id ASC)
)

ALTER TABLE OwnedItem
ADD CONSTRAINT FK_OwnedItemCategoryId
    FOREIGN KEY (CategoryId)
    REFERENCES Category (Id);

ALTER TABLE OwnedItem
ADD CONSTRAINT FK_CategoryOwnerOwnedItemOwner
    FOREIGN KEY (OwnerId)
    REFERENCES Category (OwnerId)

    -- How can I say:
    -- OR Category.OwnerId IS NULL

    ;

How can I allow OR Category.OwnerId IS NULL immediately above?

INSERT INTO OwnedItem
    (CategoryId, OwnerId, Description)
VALUES
    (-1, 23, 'Person23s hidden item')
-- SHOULD SUCCEED

Set [OwnerId] on OwnerID to allow nulls.
Put a null value record in your Category table:

INSERT INTO Category (OwnerId,Title) SELECT NULL,'blah??'

I also added  to your category table:

CONSTRAINT UC_OwnerID UNIQUE(OwnerID)

this is what I have working for you in SQL Fiddle http://sqlfiddle.com/#!3/ac720/2.


mongodb equivlant query to this sql group by I have a collection where most documents have an ISODate column.

I'm wanting to get a basic count by date for that column.  In MySQL if I had a datetime column on a table, to get what I'd want I'd do

select date(date_col), count(*) from mytbl group by 1;

It looks like mongos group method might be what I want but I don't seem to be able to get the syntax correct.

Note I'm running version 2.3, so anything specific to 2.4 won't be applicable for me.

Can someone help me with the equivalent mongo translation?

First, 2.3 is a development branch that was turned into 2.4, you should not be using it any longer - hopefully you mean 2.2, which is still supported and developed (though it too will soon be end of life as of writing this answer).

The group method you mention runs server side javascript and is not going to be fast, especially in 2.2 which used the old spidermonkey engine and takes an exclusive javascript lock.  Generally I would not recommend using it, especially with a sharded cluster, where it is unsupported.

Instead you should use the aggregation framework, which was added in 2.2, is improved in 2.4 and will be even better in 2.6.  The framework includes a $group operator, and and many others that you can use in a "pipeline" to achieve the results you want.  If that seems odd, then I recommend the pipeline explanation docs here - for anyone familiar with the Linux/Unix shell, the concept should be very familiar.

To do a group on your field you would do something like this (this would be more specific and easier to be specific if you had actually included a sample document and desired output):

db.mytbl.aggregate(
  [
    { $group : { _id : {date:"$date_col"} , count : { $sum : 1 } } }
  ]
)

You can find some more general examples here:

http://docs.mongodb.org/manual/applications/aggregation/


Does having auto_increment ID degrade performance compared to no auto_increment? I have a big table with ID column set as bigint auto_increment. At some time in history we decided to generate IDs on client side as some pseudo-random number.

My question is simple: given that the table column is still set as auto_increment, does this affect performance negatively even though we ALWAYS supply ID in INSERT queries? Is this significant/should I bother to change the schema? The table is EXTREMELY huge and busy, I am afraid I cannot do this without downtime.

After statically looking at the MySQL (5.7.28) code it makes no difference. Auto_increment locks within innodb only happen if fetching a new auto_increment value (via here), or altering the table to have a different auto_increment value.

Its current dormant status is not causing any impact to you.


Oracle ODBC connection failure (using oracle's stock ODBC drivers) I want to use an Oracle-ODBC connection in Visual Studio 2017/ SSIS as it's much faster than OLE DB during tests.

Problem:

I follow Oracle's steps to the letter.

  Install instant client (v18, also tried v12).

  Download/ extract ODBC download in same library. Run odbc_install.exe.

  See the Driver in 'ODBC Data Sources/ Admin' in Windows 10.

  Add new User Data Source. TNS Service names pull up fine. Test
  Connection (User/ Pass) -- it works!! The Connection works!!

I tried this with 64 bit in Oracle, their instant client v18.3 or 12.2 both. All works in Window's "Oracle Source Administrator" via test connections.

I tried this with 32 bit downloads as well. All is good.

Now, open Visual Studio. First tried 64 bit (my Windows OS is 64 bit, but Visual Studio Data Tools is only 32 bit). Had a hunch it wouldn't work.

Error message "system architecture and client is not the same" or such. Gotcha.

Tried the 32 bit Oracle ODBC driver (User Source). I keep getting the same message (tried 18_3 and 12_2 versions).

Now .... SQLORA32.dll is in the very file path it named. It's right there! Why can't it be found? The test connection in ODBC Source Admin works! What is going on here?

And I'm unsure if I have to "register" something via the command line, I had to do that once before, maybe it was an unrelated issue.

To boot, when I tried a 3rd party "Devart Oracle ODBC connector" -- it's a simple 5-second install wizard that works flawlessly instantly. Problem is it's a 30-day trial and costs $150 at least. How can I can get an Oracle-created ODBC connector (Oracle being world-renowned for janky-azz products) to actually work?

Devart, and probably Attunity Oracle ODBC: 5 second installs

Oracle's own: Harder to install than breaking into Fort Knox/ learning Mandarin Chinese. Please advise.

I have not used Visual Studion in the last 7 years and I have never ever used SSIS, but this is your 2nd frustrated post about this in 2 weeks, so here we go:

Oracle ODBC driver installation BEGIN

Download 32-bit 18.3 Oracle instant client (instantclient-basic-nt-18.3.0.0.0dbru.zip) + ODBC (instantclient-odbc-nt-18.3.0.0.0dbru.zip), extract it (C:\instantclient_18_3_32).

Actual ODBC driver installation, takes about 1 second:

C:\instantclient_18_3_32>odbc_install.exe
Oracle ODBC Driver is installed successfully.

C:\instantclient_18_3_32>

Set up tnsnames.ora in C:\instantclient_18_3_32\network\admin, set TNS_ADMIN to this directory in system variables, create DSN with C:\Windows\SysWOW64\odbcad32.exe, test it.

Oracle ODBC driver installation END

Create a new BI/Integration Services project, add a new ODBC connection, select DSN from list, enter user + password, test:

There we are. Close VS, add C:\instantclient_18_3_32 to PATH in System Variables.

Repeat the test, and:

Guess it's time to start learning Mandarin Chinese.


Most effective way to determine the minimum set of permissions for user I have a database that an application connects to using db_owner permissions.

How do I effectively determine the minimum set of requirements actually needed by this user (application) to run without causing service interruption? (ie. without trial and error)

This should be available from the developer who wrote the software. However, on occasions this information is not available - for instance if the software is written by a third party vendor. Most vendors tend to say it needs sa or db_owner which is generally down to poor coding on their part if it turns out to be the case.

If this information is not available from the developer of the software then your best solution would be to run a profiler trace against that user account over a set period of time - this should give you the majority of the objects that it needs access to.

Best practice would be to set up a test environment and put the trace on that - you don't really want to put a trace on a production database unless it is absolutely necessary - and expect significant testing before you can change the production accounts permissions.

I hope this helps you.


pg_trgm index not used with commutator PostgreSQL 9.6, on a table with ~100000 rows of text up to 3200 characters long.

I have the trigram indexes:

CREATE INDEX trgm_idx_gin ON test_trgm USING gin (t gin_trgm_ops);
CREATE INDEX trgm_idx_gist ON test_trgm USING gist (t gist_trgm_ops);

SELECT t, 'word' <<-> t AS dist
  FROM test_trgm
  ORDER BY dist LIMIT 10;

Index is used as predicted by the documentation, execution: 331 ms.

Limit  (cost=0.41..23.91 rows=10 width=886)
  ->  Index Scan using trgm_idx_gist on test_trgm  (cost=0.41..421142.11 rows=179185 width=886)
        Order By: (t <->> 'word'::text)

But the commutator <->> of the <<-> operator for word_similarity() does not use the index:

SELECT t, 'word' <->> t AS dist
  FROM test_trgm
  ORDER BY dist LIMIT 10;

Execution: 38 s, more than 100x more.

Limit  (cost=70833.94..70833.96 rows=10 width=886)
  ->  Sort  (cost=70833.94..71281.90 rows=179185 width=886)
        Sort Key: (('word'::text <->> t))
        ->  Seq Scan on test_trgm  (cost=0.00..66961.81 rows=179185 width=886)

How could I speed up the commutator?

You cannot.  These are different operations, and only one of them is supported by the index.  If you were to swap the order of the arguments as well as the operator itself, then it would be the same operation and the index would still support it.

Although it might be possible that you could modify the source code of pg_trgm to support this other operation.  I doubt it would be very efficient, though.


Does DBA need (a read) access to virtualization infrastructure? And why? I am in charge of monitoring, troubleshooting, tuning and optimizing a few dozens production SQL Servers 2008 R2/2012 in my company that are being run inside VMWare virtual machines.

I've requested a read-only access to VMWare infrastructure without much success. I feel that I should provide irrefutable and clear for non-technical people (company's management) proofs and illustrations why I need such access.

What are they?

UPDATE:
Recently, I downloaded and "tried" Confio Ignite trial for monitoring "my" SQL Servers running inside (VMWare) virtual machines.

Why is such access necessary?
Which of this info is important for DBA that cannot be got from Window's PerfMon VM counters?

Related question:

Can you explain me VM Performance Counters in Windows?

Short Answer: Yes.

There are many reasons but the few that stick to mind:

1.) Trust but verify - SQL cares a lot about its environment, the hardware or virtualized system it is on. When I help a company with SQL on VM issues it is normally a misconfigured VM. In many cases the idea of SQL on VM is about to be thrown away.

2.) DBAs should look at memory reservations, alerts and performance conditions, how overcrowded a physical host is ( or isn't) and understand how it works.

3.) DBAs are learning more and more about virtualization. Through excellent blog posts and SQL events, the VMware knowledge in the SQL dba community is rising. An extra set of eyes may help ensure things are well tuned and future proofed.

4.) Prove yourself... If you've done everything right and built a great environment, show it off. Read access to center isn't going to destroy your system and you can show those pesky DBAa everything is up to snuff. And if it isn't? Don't you want to know? ;-)


PostgreSQL: Auto update updated_at with custom column Before, I would join multiple tables into one and return result, but in many tables I had same column names. That's why I decided to to prefix column names with table name. But that broke the trigger I had to auto-update my updated_at column.

This is the function I had:

CREATE OR REPLACE FUNCTION update_updated_at()
  RETURNS TRIGGER AS $$
  BEGIN
      NEW.updated_at = now();
      RETURN NEW;
  END;
  $$ language 'plpgsql';

And I would add a trigger like:

CREATE TRIGGER update_users_updated_at
  BEFORE UPDATE ON users
  FOR EACH ROW EXECUTE PROCEDURE update_updated_at();

But now my column is named users_updated_at and this doesn't work. My question is is there any way that I can pass the column name to the trigger and update the passed column, or is there is any other way that I am not aware of?

Use the spi module's moddatetime

spi module, moddatetime

moddatetime — Functions for Tracking Last Modification Time

  moddatetime() is a trigger that stores the current time into a timestamp field. This can be useful for tracking the last modification time of a particular row within a table.

  To use, create a BEFORE UPDATE trigger using this function. Specify a single trigger argument: the name of the column to be modified. The column must be of type timestamp or timestamp with time zone.

  There is an example in moddatetime.example.

Example / Synopsis

From the above referenced file,

DROP TABLE mdt;

CREATE TABLE mdt (
    id      int4,
    idesc       text,
    moddate timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TRIGGER mdt_moddatetime
    BEFORE UPDATE ON mdt
    FOR EACH ROW
    EXECUTE PROCEDURE moddatetime (moddate);

INSERT INTO mdt VALUES (1, 'first');
INSERT INTO mdt VALUES (2, 'second');
INSERT INTO mdt VALUES (3, 'third');

SELECT * FROM mdt;

UPDATE mdt SET id = 4
    WHERE id = 1;
UPDATE mdt SET id = 5
    WHERE id = 2;
UPDATE mdt SET id = 6
    WHERE id = 3;

SELECT * FROM mdt;

Your Application

So this is what you would need to.

CREATE EXTENSION spi;

ALTER TABLE users ALTER timestamp_at SET DEFAULT now();

DROP TRIGGER IF EXISTS
  update_users_updated_at ON users;

CREATE TRIGGER mdt_users
  BEFORE UPDATE ON users
  FOR EACH ROW
  EXECUTE PROCEDURE moddatetime (timestamp_at);


MySQL Community Edition: Can I replicate my entire server with another server in one go? I need to migrate all the databases from one server to another. There are about 100 databases, and all databases are used daily and updated/inserted too and read through their respective websites.

If I copy over the databases in one go, by the time the website for that database is migrated to the new server, the data will be out of sync.

Is there a way to setup a temporary replicate of all the databases in the server to replicate to the new server (one way master/slave replication, master being the original server and slave being the source server). I can then setup the website on the new server and run it in parallel to the old site until all sites are migrated then switch off the old server.

I'm running MySQL 5 community edition.

Thanks

Jason

I answered a question on how to use rsync to copy a live DB to another location.

You would repeat rsync process multiple times until two rsync's run in the same time.

Then, you would shutdown mysql on the master, perform a final rsync to the new server, and start mysql on the master.

You then properly prep the new server, make sure /var/lib/mysql has everything, make sure you erase all binary logs on the new server, and make sure you have the same my.cnf on both machines (you may want to change the server_id)

Here is an excerpt from that link:

Step 01) install the same version of mysql on ServerB that ServerA has

Step 02) On ServerA, run SET GLOBAL innodb_max_dirty_pages_pct = 0; from mysql and about 10 minutes (This purges dirty pages from the InnoDB Buffer Pool. It also helps perform a mysql shutdown faster) If your database is all MyISAM, you can skip this step.

Step 03) rsync /var/lib/mysql of ServerA to /var/lib/mysql on ServerB

Step 04) Repeat Step 03 until an rsync takes less than 1 minute

Step 05) service mysql stop on ServerA

Step 06) Perform one more rsync

Step 07) scp ServerA:/etc/my.cnf to ServerB:/etc/.

Step 08) service mysql start on ServerB

Step 09) service mysql start on ServerA (optional)

Give it a Try !!!


Update every selected row I have a table in postgresql, let's say users:
CREATE TABLE users(
  user_id bigint NOT NULL,
  username character varying(24) NOT NULL,
  password character varying(64) NOT NULL,
  timestamp_created timestamp with time zone DEFAULT NOW() NOT NULL,
  new boolean DEFAULT FALSE NOT NULL
);

With the following query, I select all users
SELECT * FROM users;

What I want to achieve is the following: when a row is selected, the value of 'new' should be updated from false to true.
There is no BEFORE/AFTER SELECT in postgresql trigger functions, is there something similar?
Edit: I'm using NodeJS. Currently, I store all the user ID's of the returned rows in an array and do this query:
UPDATE users SET new = true WHERE user_id = ANY($1);

$1 is placeholder for the user ID's array.
Is there a more convenient way to do this?

A somewhat complicated way could be
WITH new_users AS (
   UPDATE users SET new = TRUE
   WHERE NOT new RETURNING /* all columns except "new" */
)
 SELECT /* all columns except "new" */
 FROM new_users
UNION
 SELECT /* all columns except "new" */
 FROM users;

The UNION will eliminate duplicates.


How would I reset all of the identity values for a table, and reseed the auto-number in Sql Server 2008 I have a customer that has a table in the database with an autonumber id field. The customer uses that id as a user-friendly identifier to a unique record stored in the database. After some time, my application will remove old records in the database, such that there is usually a sliding window of about 30k records at any given time.

This functionality is fine, and has been working for a few years, but now the id's in the database are up in the hundred millions, and it is harder for the customer to now communicate failure id's to the outside world.

What they want to do, and what I am hoping to find out how to do, is to take all of the records in the current table, and reduce their id to start at 1, and then reseed the identity to start counting where the new id would be.

In math:
new id = current id - min(ID)

And then finally:
new seed = max(id)

Thanks

edit

To just help clarify one thing, it's that records are getting regularly deleted from the database so the start:

1-30k Valid Records

After 5 months:

1-2mil - no records
2mil - 2mil + 30k - Valid records

What the user essentially wants to do is to subtract 2mil from all of the id's (still unique), and pretend that the previous records never existed at all.

The easiest way would be to start a new table. Inject the 30,000 rows you want to keep, then drop the old table and rename the new table.

BEGIN TRANSACTION;

CREATE TABLE dbo.copy_of_foo
(
  ID INT IDENTITY(1,1) PRIMARY KEY,
  col2 ..., ...
);

INSERT dbo.copy_of_foo(col2, ...)
  SELECT TOP 30000 col2, ...
  FROM dbo.foo
  ORDER BY ...;

DROP TABLE dbo.foo;

EXEC sp_rename N'dbo.copy_of_foo', N'foo', N'OBJECT';

COMMIT TRANSACTION;

You may need to nest the order by and add OPTION (MAXDOP 1) if you want to ensure that you get the 30K most recent rows and the IDENTITY values are assigned in order from oldest to newest, e.g. hopefully you have some other column, such as a date time column, that can help identify the 30000 rows you want to keep:

INSERT dbo.copy_of_foo(col2, ...)
  SELECT col2, ...
  FROM
  (
    SELECT TOP 30000 col2, ...
      FROM dbo.foo
      ORDER BY date_time_column DESC
  ) AS x
  ORDER BY date_time_column
  OPTION (MAXDOP 1);

Keep in mind though that if someone reported an exception yesterday with row #42,000,564, good luck finding it today. As I suggested in the comment, maybe you shouldn't be exposing these obviously-otherwise-absolutely-meaningless surrogate identifiers to users.


pgAdmin 4 version 3 fails to open a second time I just installed pgAdmin 4 version 3.0.0 for macOS Sierra, replacing the pgAdmin that came bundled with Postgres 10.3 running locally on the same Mac.

I opened the pgAdmin 4.app app icon, which caused Safari browser to come to the front with a tab showing the usual pgAdmin user-interface. Good.

I closed that tab in Safari. Now when I open the pgAdmin 4.app icon (either double-clicking or choosing File > Open), nothing happens.

Is pgAdmin supposed to launch in a browser window rather than in the app's own window?

➡ How to re-open pgAdmin again?

“Elephant” icon menu

When pgAdmin is running, an elephant icon menu appears. Look at the upper right corner of the screen. The elephant refers to Slonik, the Postgres mascot.

Choose New pgAdmin Window menu item.

FYI, pgAdmin 4 is built as a web app, not a desktop app, even when run locally on your own computer. Note that the internal web server provided by pgAdmin 4 is running continuously, even after you close all windows. Use the Shutdown server menu item, as seen above, if you want to stop that web server.

p.s. I agree, it is very confusing that clicking the icon does not do the same thing (when pgAdmin is already running).


Workload analysis before migration Does anybody have a recommendation on which performance counters to collect and/or templates to use on the profiler before migrating SQL databases? The purpose is to evaluate the workload and make decision on which cluster to put the databases under. Thank you for all your help!

If it's SQL Server 2005 or later, I'd initially look at file stats, which shows you how much disc IO each database uses, together with memory stats:

Memory Breakdown:

SELECT
   (CASE WHEN ([is_modified] = 1) THEN 'Dirty' ELSE 'Clean' END) AS 'Page State',
   (CASE WHEN ([database_id] = 32767) THEN 'Resource Database' ELSE DB_NAME (database_id) END) AS 'Database Name',
   COUNT (*) AS 'Page Count'
FROM sys.dm_os_buffer_descriptors
   GROUP BY [database_id], [is_modified]
   ORDER BY [database_id], [is_modified];
GO

Microsoft provide an Assessment and Planning Toolkit which you can point to your SQL Servers, and it will poll every 5 minutes to gather useful information such as IOPS.

For a consolidation/migration project, Bob Duffy's (MCM) SQL Bits presentation video is full of tips.


The meaning of the Roles in SQL Server 2008 R2 Just because I’m confused around this issue I’d like to ask the following questions, and please I would like to receive clear and easy understandable answers. Not deep analyze.

1.What is the Roles for a programmer?
2.What is the role of the Schemas owned by this… which related with them?
3.What is the meaning and the use for Securables in Roles
4.Why we add Users to a Role?
5.How we may handle the Roles in conjunction with to the Users, inside of a project?

Thank you very much in advance.

ADDITIONAL INFORMATION

The reason uploading this question based on an issue which I’m facing developing a web.site. And the Issue is.
I have a number (100) of Web.Pages and a number (20.000) of Users (application users), each User has a special code, thus numerous Users belong to a Group (regarding to there code) and others in another Group.
Now, when a User of a certain Group tries to open a page I need a rule to look if he has the right to open this page.
For that reason I was thinking to use the Roles… But I steel don’t know if that is a good idea rather to create my own rule between Web.Pages and Application Users…

Roles are predefined sets of permissions on the database or on the server, that can be granted to the user as a package. Best practice is creating your own set of roles for the database, with the exact permissions that they need.


Setting Circular Replication in mysql I have 3 MySQL servers say A,B,C.

What I want to do is that I want to make all of them master as well as slave.If there is update on any of the MySQL Servers it should be replicated to all of the Servers.

I have studied about the circular replication and found it can be implemented with it.

Can anybody please give me all the steps to accomplish the Replication as stated between three servers.

One More Point i want to ignore some of the tables from a database also.

Also What are caveats of this type of replication?

Rolando told you how to do it and his answer is excellent.  The truth is, circular replication is an awful, terrible, terrible idea.  Don't do it. Ever. "Absolutes are never true," but in this case, it's pretty close.

Instead of having a single point of failure, now you have three.

What if one of the servers goes down?  Now replication is broken everywhere.

How do you replace it easily and align the binary log positions?  You can't without doing some very complicated and error prone binary log analysis.

So, now you have to restore all three servers from a backup.  Wait, was that backup taken at the same moment in time?  Nope, because there's no way to make a transactional backup across multiple servers.  So, you're left with broken replication and attempting to fix it with pt-table-checksum and pt-table-sync...or rebuilding the entire cluster from scratch.

Just because you can do something, doesn't mean you should.  Don't do this, really.  I'm not kidding.


DB2 MQT not rewriting role playing columns If in a MQT, if I have 2 referential constraints to the same table (role playing) , usage of one of the constraints doesnt allow the query to be optimized

Example

FACT_SALES

DIM_BILL_TO_ID --> DIM_ADDRESS

DIM_SHIP_TO_ID --> DIM_ADDRESS

SALES_AMOUNT

DIM_ADDRESS

DIM_ADDRESS_ID

COUNTRY_NAME

I create a MQT with the sql query definition as

MQT 1

SELECT COUNTRY_NAME,
       SUM(SALES_AMOUNT)
FROM
      FACT_SALES INNER JOIN DIM_ADDRESS ON FACT_SALES.DIM_BILL_TO_ID = DIM_ADDRESS.DIM_ADDRESS_ID
GROUP BY
      COUNTRY_NAME

When a query is fired, automatically query rewrite happens and MQT is used

Lets say my definition was

MQT 2

SELECT a1.COUNTRY_NAME AS BILL_TO_NAME,
       a2.COUNTRY_NAM AS SHIP_TO_NAME,
       SUM(SALES_AMOUNT)
FROM
      FACT_SALES INNER JOIN DIM_ADDRESS a1 ON FACT_SALES.DIM_BILL_TO_ID = a1.DIM_ADDRESS_ID
      INNER JOIN DIM_ADDRESS a2 ON FACT_SALES.DIM_SHIP_TO_ID = a2.DIM_ADDRESS_ID

GROUP BY
      a1.COUNTRY_NAME,a2.COUNTRY_NAME

and the query that was fired was

SELECT COUNTRY_NAME,
       SUM(SALES_AMOUNT)
FROM
      FACT_SALES INNER JOIN DIM_ADDRESS ON FACT_SALES.DIM_SHIP_TO_ID = DIM_ADDRESS.DIM_ADDRESS_ID
GROUP BY
      COUNTRY_NAME

The query rewrite doesn't happen... but instead if I used BILL_TO_ID in the query, then query rewrite happens...

Is there any logic where the DB2 optimizer cant understand role playing columns?

You may wish to try creating indexes over the foreign keys.


MySQL row-based-replication HA_ERR_KEY_NOT_FOUND... which record is it? I have a mysql slave (5.1) using row-based replication. The slave has stopped with the error HA_ERR_KEY_NOT_FOUND. OK fine, it's trying to update a record that doesn't exist for some reason. Using the master's binlog position, I use mysqlbinlog -vv to read the binlog... but the transaction in question updates at least 50 records! How can I tell specifically which record/PK is missing? MySQL has to know, but doesn't appear to tell me anywhere?

I think you should re-initialize your replication.

If the replication worked good, the rows should not be missing. That the rows are missing means there is some inconsistency between your master und your slave. I would not act on the assumption that this is the only error and if I'd fix it by hand everything is back fine again. Instead I would set up the replication from the start again and make sure it stays synced this time, to be sure to get rid of all inconsistencies.

Nevertheless there are some ways to identify which rows are missing. As a starting point you could take a look at this blogpost. This basically includes "decode the relay log" and "search for the current relay log position". One can decode the log for example with this statement:

mysqlbinlog mysql-relay-bin.000001 –base64-output=decode-rows -v > decoded.log


Why does MariaDB 10.1.16 vanilla installation comes with an almost empty config file? Why does MariaDB 10.1.16 vanilla installation comes with an almost empty config file (/etc/my.cnf) ? Whereas it is not the case for MySQL. The config file for MySQL is usually not empty.

Not sure if it answers your question, but in /etc/my.cnf:

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

One of the files in this directory is:

mariadb-server.cnf

This file and possibly other files as well does not exists in MySQL.


SQL Server Trace - Filter on System Processes Is there a way to filter a (2008 R2) profiler trace on a specific system process? I don’t mean the usual server process (SPID), but the system processes that append “s” onto the end of a SPID (eg .spid29s) as seen when looking at  the SQL Server Logs.

The system processes are seen a lot when a server is first started.

So I actually went and tried this myself.

The "s" suffix in the log is only for display purposes to show that it was a system SPID. It's not actually part of the SPID.

In Profiler, there's an IsSystem column that you can filter on, but otherwise, the system SPIDs show up as per normal (i.e., "spid25s" is simply SPID 25), and you can filter as normal using the SPID column.

I'm not sure what problem you're having with that, as I was able to capture activity on the system SPIDs during my test.

That said, since traces don't persist on instance restart, it might be difficult, or impossible to capture startup activity immediately as the server comes up. You could start a trace by executing a startup stored procedure, or create an Extended Events session that does persist on startup.

While I didn't go to either of those extents, I tried resuming a trace using Profiler while I clicked Yes on the Service Control dialog to start the database engine service. I didn't see any activity at all.

Just because a process (system or otherwise) has activity (disk I/O, CPU, etc.) associated with it doesn't necessarily mean the process is running a T-SQL statement. It's possible there just isn't anything to see and analyze through this channel.

If you're having issues with slow startup, it may be more fruitful to look at the SQL Server error logs to see if there are any long-running operations that stick out.


Rename database object during restore Is there a way to rename database objects such as tables, stored-procedures, etc, during a restore of a .BAK file to a new database?

You will be able to do this after the restoration process, when the database is in NORECOVERY mode.  Concurrently?  No, there isn't a way.


Is Locked Pages In Memory Still Recommend in SQL Server 2017? I have done some research about lock pages in memory, the question that still bothers me is (I know it really depends on your specifications but) is it still recommended to lock pages in SQL Server 2017 like it was in SQL Server 2005, 2008 etc.?

In general, I still recommend to enable the "Lock Pages in Memory" policy on the SQL Server service account. This instructs SQL Server to ignore Windows' requests to trim the working set and page it to disk.

As Erik points out in the comments, this is really needed when you need to preserve SQL Servers' working set, which is necessary when the same Windows machine hosts other services or applications that may end up competing with SQL Server for RAM.

Another thing to keep in mind is Virtualization: nowadays almost all SQL Servers are installed on virtual machines, with several possible memory configurations and often sharing the same hypervisor host with many other VMs. Overcommitting memory is commonplace and the hypervisor tries to balance RAM allocations to VMs using memory balooning. Even if your SQL Server is not sharing the same Windows guest with other services, you might want to enable LPIM to let it play nicely with memory balooning and avoid unwanted memory trimming on production workloads.

Here is a whitepaper from Microsoft describing how to configure Hyper-V and SQL Server.

That said, if this is a test instance, you probably don't care much about the performance implications of memory trimming.


How to update a table from a another table I have one table A has column (id, field_1,field_2),
and another table B has column (id,field_2)

Now I want to merge table B to A, that means i want to update field_2 in table A to value of table B. So how to implement that ? BTW I am using oracle

Thanks

An alternative approach to the co-related subquery (suggested by Kerri) would be to use the MERGE statement, which might be more efficient than the subselect (which can only be verified by looking at the execution plan of both statements).

MERGE INTO table_b
USING
(
  SELECT id,
         field_2
  FROM table_a
) ta ON (ta.id = table_b.id)
WHEN MATCHED THEN UPDATE
    SET table_b.field_2 = ta.field_2


How can I enforce data integrity in this model with a M:2 relationship? This model describes a part of my database (somewhat simplified):

A "Tumor model" describes an experiment a researcher does on animals. The "Animal line" is a breed of lab animals, e.g. a Black 6 mouse. "Experimental animal" is the specific animal from a given line which is used for something in an experiment. Each animal can have tumor entities, and the researcher can do interventions on it (e.g. give the animal chemotherapy). So far, pretty straightforward.

The part where I ran into problems was when I had to add a transplantation. A transplantation is always performed on exactly two animals within the same tumor model, one being the donor and the other the acceptor. There can be multiple transplantations within a tumor model. As it is a M:2 and not a M:N relationship, I took a shortcut and did not make a junction table out of the "performed on" relationship, opting instead for two foreign keys from the Experimental animal table to be referenced in the Transplantation table.

My problem is that now, I am able to create a transplantation between two animals which are not part of the same model. (By the way, using a junction table instead of two IDs in Transplantation would not have removed the problem). I tried many ways to model the whole thing and I am quite sure that there is no way to enforce this on the ERD model level. I know that we can enforce this well within the business logic, but our department has a "our user has a one-time wishes to do something, we will quickly throw together a script/Access frontend/whatever to do it" policy, where the business logic gets completely bypassed.

What are my options for ensuring that I never get a Transplantation row with two IDs pointing to experimental animals from two different models? Keep in mind that as an application developer, I do not have experience with databases beyond creating my own schema and doing CRUD operations in SQL. I am not sure if I can somehow put together a constraint which can do what I want, or maybe set a trigger which prevents updates and inserts with the wrong combination of IDs, or if there is something else entirely I didn't think of. Ideas about which way leads to the cleanest solution and a rough description of what exactly I would need to implement are welcome.

You can add a "model" attribute to Transplantation and use it (in combination with the Donor and Receiver IDs) to create the 2 foreign keys.

If the "model" attribute is not part of Experimental Animal but of Used In, make the foreign keys target that relation.

In SQL terms that would be something like:

CREATE TABLE Transplantation
( DonorID
, ReceiverID
, ModelID
-- other attributes

-- primary key and other constraints

, CONSTRAINT Donor_FK
    FOREIGN KEY (ModelID, DonorID)
    REFERENCES UsedIn                        -- UsedIn or ExperimentalAnimal
       (ModelID, ExperimentalAnimalID)       -- depending where the model
, CONSTRAINT Receiver_FK                     -- attribute resides
    FOREIGN KEY (ModelID, ReceiverID)
    REFERENCES UsedIn                        -- same as above
      (ModelID, ExperimentalAnimalID)
, CONSTRAINT CommonSense_CK
    CHECK (DonorID <> ReceiverID)
) ;

The above FKs to be created, a UNIQUE constraint on UsedIn(ModelID, ExperimentalAnimalID) is required.


Cross Database with Application Role I have a requirement to access (read) a table/view in database B from database A. The application that needs to execute the query however uses an Application Role. From what I've read and tried, doing Module Signing is not an option.

What would be the next best approach?
Is that setting TRUSTWORTHY on for database A and use EXECUTE AS?

Just found the answer:

Create SQL login with minimal privileges
Execute function from source database under that user (WITH EXECUTE AS)
Module sign object
GRANT AUTHENTICATE to user created from certificate (this is the one I missed)


Backups fail to copy over the network We have a backup process set to copy files over to a different network. Occasionally we lose internet connection and then one of the backups files will fail to copy over. This becomes a bigger issue for larger files as they take much longer to copy over and if it needs to be copied again we need to wait a few hours. Our proposed solution is to split the backups across multiple files so that when it fails we only lose a smaller file.
Is there any other/better solutions to handle this type of issue?

if you backup to multiple files the backup will normally run faster - so I think this is a win win suggestion. If you do the file copy with something like robocopy then there is retry built in.


Does MariaDB or MySQL implement the VALUES (expression) table value constructor? I am just wondering if MariaDB or MySQL implements the <table value constructor> in the SQL Spec. In SQL Server and PostgreSQL this is done with standardized VALUES (expression)?

SELECT *
FROM ( VALUES (1) ) AS t(x);
 x
---
 1
(1 row)

(syntax from postgresql).

MariaDB 10.3+

Yes, speaking of MariaDB 10.3.3 and later it seems to support table value constructors.

SELECT 1, t.7
FROM (VALUES (7),(42)) AS t;
 1 |  7
--------
 1 |  7
 1 |  42

You'll notice that there is no support for column_alias in the FROM clause, instead requiring you to use the table_alias and the name of the first row's value for that column. But column_alias are allowed in a CTE's WITH clause,

WITH t(a) AS ( VALUES (1),(2) )
SELECT t.a, t.a AS b
FROM t;
 a |  b
--------
 1 |  1
 2 |  2

Additionally you may able to skirt this by giving the first row a definitive alias.

SELECT 1 AS x ,2 AS y
UNION VALUES (3,4),(5,6);
 x | y
-------
 1 | 2
 3 | 4
 5 | 6

also see examples in https://jira.mariadb.org/browse/MDEV-12172

PostgreSQL permits column aliases in FROM clauses, as show above with t(x), this syntax is also not supported in MariaDB

MariaDB <10.3; MySQL 5.x & 8.x (Workaround)

Prior to MariaDB 10.3, and all versions of MySQL do not support the VALUES expression.

SELECT * FROM ( VALUES (1) ) AS t(x);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'VALUES (1) ) AS t(x)' at line 1

However, it does support sub-selects with literals.

SELECT * FROM (SELECT (1) AS x) AS t;
-- more simply
-- SELECT * FROM (SELECT 1 AS x) AS t;
+---+
| x |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

PostgreSQL also supports this syntax.

SELECT * FROM ( SELECT 1 AS x ) AS t;
 x
---
 1
(1 row)

However, things get a lot more verbose with this syntax if you're doing multiple rows. Below from MariaDB, also works in PostgreSQL

SELECT * FROM ( SELECT 1 AS x UNION ALL SELECT 2 ) AS t;
+---+
| x |
+---+
| 1 |
| 2 |
+---+

Whereas with VALUES, it's simply VALUES (1),(2)


How do you create a job in SQL server 2008 R2 that will clear transaction logs? I have created a maintenance plan in SQL Server 2008 to delete transaction logs backups older than 3 days. Every day I look in the folder designated and the logs are still there. The job states it has run but nothing is removed. Job schedule is recurring with a daily frequency and the status is enabled.

If I copy the Transact-SQL and run it manually, it appears to clear space on the drive even though I don't see the logs removed up to the last 3 days. The transaction logs are saved to a folder on the target drive not the root itself.

Database recovery model is full and this is required due to the nature of the application that run against the SQL Server box. I have tried removing the maintenance plan and recreating it but I'm getting the same results (nothing). I also checked to see if the SQL Server agent isn't tied up on any job.

What am I doing wrong?

You can use Ola Hallengren's  SQL Server Backup Solution.

Especially the CleanupTime will help you to delete the old backups (FULL / Diff or Transaction log backups).

  Specify the time, in hours, after which the backup files are deleted.


How to list all views in SQL in PostgreSQL? How do I list all views for a database using an SQL command in PostgreSQL?

I would like something similar to output of the psql \dv command, but preferably just a list of view names.  e.g.,

SELECT ...;
my_view_1
my_view_2
my_view_3

I'm running PostgreSQL v9.1.4 on Ubuntu Linux.

From the documentation:

 select table_name from INFORMATION_SCHEMA.views;

If you don't want the system views is your result, try this:

 select table_name from INFORMATION_SCHEMA.views WHERE table_schema = ANY (current_schemas(false))


How to return a null from an empty XML tag in SQL server using Xpath only? I have an application that stores various user defined data points about a record in an XML column. I have no control over how these are stored or updated.

When I query a column it can return 1 of 3 values:

The value
Null
An empty string

$64 question I would like to return those empty strings as nulls using xpath.

I've found a lot of answers about returning the null as an empty sting but nothing this way round.

They end up that way when someone has delete the value, and instead of deleting the tag it blanks it to a <value /> tag. The nulls occur when the value has never been set. They have not included xsi:nil.

I've done it with a case statement:

    select
    so.SalesOrderId,
    Case
        when sopc.value('(Value)[1]','smalldatetime') IS null then Null
        when sopc.value('(Value)[1]','smalldatetime') ='' then Null
        Else sopc.value('(Value)[1]','smalldatetime')
    End as sopc
    From
    SalesOrders so
    Outer apply so.CustomColumns.nodes('/CustomColumnsCollection/CustomColumn[Name="ProjCompleted"]') sopc(sopc)

but this feels inefficient and this is not the only column so it makes the code long and harder to maintain.

Edit to include sample data

SalesOrderId    CustomColumns
SO 1            "<CustomColumnsCollection>
                   <CustomColumn>
                     <Name>ProjCompleted</Name>
                     <DataType>1</DataType>
                     <Value />
                   </CustomColumn>
                 </CustomColumnsCollection>"
SO 2           "<CustomColumnsCollection>
                  <CustomColumn>
                     <Name>ProjCompleted</Name>
                     <DataType>1</DataType>
                     <Value>'2017-11-21'</Value>
                  </CustomColumn>
                </CustomColumnsCollection>"
SO 3           "<CustomColumnsCollection>
                </CustomColumnsCollection>"

**Output**
Current      Desired
''           null
2017-11-21   2017-11-21
null         null

Thanks in advance.

Specify the text() node within the Value element. That node is missing when you have an empty tag.

Try this:

declare @X xml;
set @X = '<Value/>';

select @X.value('(Value/text())[1]', 'smalldatetime'),
       @X.value('(Value/text())[1]', 'varchar(max)'),
       @X.value('(Value/text())[1]', 'int'),
       @X.value('(Value)[1]', 'smalldatetime'),
       @X.value('(Value)[1]', 'varchar(max)'),
       @X.value('(Value)[1]', 'int');

Result:

------------------- ---------- ----------- ----------------------- ---------- -----------
NULL                NULL       NULL        1900-01-01 00:00:00                0

It is a good thing in general to always specify the text() node. The query plan is simpler and more efficient.


Ignoring Null (optional) Variables in Stored Procedure I am looking for what is hopefully something easily achievable without a ton of coding.

I have a stored procedure that does some work and has about 20 optional parameters:

CREATE PROCEDURE dowork
                 @param1_1=NULL,
                 @param1_2=NULL
                 .....@Param1_30=NULL

I am implementing another procedure, that will call that one, and ideally, I want to offer all the parameters, but only use those that have been set:

CREATE PROCEDURE CallWork
                 @param1=NULL,
                 @param2=NULL
                 .....@Param30=NULL
AS BEGIN
--Do some stuff
EXEC dowork
     @param1_1=@param1,
     @param1_2=@param2
     .....@Param1_30=@Param30

Will the above example work as is, or do I need to create a variable of some sort to hold all the 'set' parameters for example?

The parameters are static. I have used number 1 to 30 as an example, but they are all static. Examples: @StartTime, @EndTime, @Type, @Location etc.

Currently dowork, processes a batches based on the input from the parameters. For certain workloads, I need it to process each batch, one at a time, instead of all at the same time.

By creating an abstracting procedure, I am planning to create a table variable to hold a row for each batch, and then pass them through one by one, allowing more control over each batch that we require in certain circumstances. This will then leave the original procedure to function as it did when required for mass batch jobs.

If you set the value of a given parameter to it's default value (when it has one), you'd get the same functional equivalent as omitting it.  Therefore, I'd suggest doing something like the following:

EXEC dowork
    @param1 = ISNULL(@param1, SELECT default_value FROM sys.parameters p WHERE object_Id = OBJECT_ID('dowork') AND name = '@param1' AND has_default_value = 1),
    @param2 = ISNULL(@param2, SELECT default_value FROM sys.parameters p WHERE object_Id = OBJECT_ID('dowork') AND name = '@param2' AND has_default_value = 1),
    ...@param30 = ISNULL(@param30, SELECT default_value FROM sys.parameters p WHERE object_Id = OBJECT_ID('dowork') AND name = '@param30' AND has_default_value = 1)

The only flaw being values that do not have a default value, which will result in passing a NULL vs causing the error that would occur when omitted.  If that is a concern in your particular case, I would do some checking before hand and throw an error.

The only other alternative I could suggest would be dynamic SQL.


In Postgres, set log_rotation_size but my logs are still not rotating I’m using Postgres 9.5 on Ubuntu 14.04.  I wanted to rotate my logs whenever they reach a certain size.  So I edited  /etc/postgresql/9.5/main/postgresql.conf and set this

log_rotation_size = 50MB                # Automatic rotation of logfiles will
                                        # happen after that much log output.
                                        # 0 disables.

Then I restarted my server

sudo /etc/init.d/postgresql restart

However after running some operations, I ran out of disk space, and discovered my log had not rotated at all …

myuser@mymachine:~$ ls -al /var/log/postgresql/postgresql-9.5-main.log
-rw-r----- 1 postgres postgres 3165773943 Dec 29 18:34 /var/log/postgresql/postgresql-9.5-main.log

What else do I need to do to get the log rotation to kick in?

In Debian/Ubuntu, per policy it's the logrotate package that is in charge of handling log rotation and purge for all services, PostgreSQL included.

From  https://www.debian.org/doc/debian-policy/ch-files.html :

  Log files must be rotated occasionally so that they don't grow
  indefinitely. The best way to do this is to install a log rotation
  configuration file in the directory /etc/logrotate.d, normally named
  /etc/logrotate.d/package, and use the facilities provided by
  logrotate.

So there is  a /etc/logrotate.d/postgresql-common configuration file that can be tuned to rotate log files when they grow over a size given by the size keyword , among a lot of other options.

On the other hand, should you want to use the PostgreSQL feature to achieve this, as opposed to the pre-configured method of the OS, you need to consider all logging parameters in postgresql.conf, not just log_rotation_size. First a Debian/Ubuntu packaged PostgreSQL will turn off logging_collector to not interfere with logrotate, but most of log_* options depend on it to be effective, as mentioned in the doc, for instance:

https://www.postgresql.org/docs/current/static/runtime-config-logging.html

  log_rotation_size (integer)

  When logging_collector is enabled, this parameter determines the maximum size of an individual log file. After this many kilobytes have
  been emitted into a log file, a new log file will be created. Set to
  zero to disable size-based creation of new log files. This parameter
  can only be set in the postgresql.conf file or on the server command
  line.


Special Oracle Order I have a table (emp) with two columns in ORACLE:

COMPANY   OFFICE    EMP
------------------------
9999      00001     emp1
9999      00001     emp2
9999      00002     emp3
9999      00002     emp4
0001      99999     emp5
0001      00001     emp6
0001      00002     emp7
0002      00001     emp8
0002      99999     emp9
0003      99999     emp10
0003      00001     emp11
0003      00002     emp12

I want to get value in EMP column with following statement:

SELECT
   EMP
FROM
   emp
ORDER BY COMPANY /*(But 9999 is first)*/, OFFICE /*(But 99999 is first).*/

I don't know how to do.

I guess you want the results in ascending order but with 9999 always first?

Select EMP
From Emp
Order By Case When Company = 9999 Then -1 else Company End,
         Case When Office = 9999 Then -1 else Office End;