Generate Script for Event Notification I need to migrate my Event Notifications from current server to another one. I would like to generate that code, but I couldn't find an way to do that.

Try 1

I have tried reverse engineering from sys.server_event_notifications but it does not return a value that I can marry with sys.event_notification_event_typeid.

Try 2

I tried also a DAC to query system views, getting more columns from that, but no joy.

Try 3

Tried sp_helptext the objects, but it can't be found.

EXPECTED OUTPUT (for one event)

CREATE EVENT NOTIFICATION [CaptureDeadlocks]
ON SERVER
WITH FAN_IN
FOR DEADLOCK_GRAPH
TO SERVICE N'DeadlockService', N'current database';

Any ideas?

I am unaware of a way to script the existing one out so I drop and create a new one each time.
The following script assumes a database name of DBA and a Service of DeadlockEventsService (viewable in SSMS under Service Broker Services)

/****** BEGIN Create the Event Notification object that will capture the

deadlocks ******/
USE [DBA]
GO

EXECUTE AS LOGIN = 'sa';
DECLARE @AuditServiceBrokerGuid [uniqueidentifier]
,@SQL [varchar](max);

--— Retrieving the service broker guid of CaptureDeadlockGraph database

SELECT @AuditServiceBrokerGuid = [service_broker_guid]
FROM [master].[sys].[databases]
WHERE [name] = 'DBA'

--— Building and executing dynamic SQL to create event notification object

SET @SQL = 'IF EXISTS (SELECT * FROM sys.server_event_notifications
WHERE name = ''DeadlockGraphEventNotification'')

DROP EVENT NOTIFICATION DeadlockGraphEventNotification ON SERVER

CREATE EVENT NOTIFICATION DeadlockGraphEventNotification
ON SERVER
WITH FAN_IN
FOR DEADLOCK_GRAPH
TO SERVICE ''DeadlockEventsService'', '''
+ CAST(@AuditServiceBrokerGuid AS [varchar](50)) + ''';'

EXEC (@SQL)

GO

/****** END Create the Event Notification object that will capture the deadlocks ******/


Add auto increment to already existing primary key column PostgreSQL I have a database schema of the following table:
database=# \d person
              Table "public.person"
   Column    |         Type          | Modifiers
-------------+-----------------------+-----------
 person_id   | smallint              | not null
 fname       | character varying(20) |
 lname       | character varying(20) |
 eye_color   | color_enum            |
 birth_date  | date                  |
 street      | character varying(30) |
 city        | character varying(20) |
 state       | character varying(20) |
 country     | character varying(20) |
 postal_code | character varying(20) |

I want to add AUTO_INCREMENT in one ALTER statement the way we can do in MySQL
ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;

I have tried this in Postgres but I am getting this error:
ALTER TABLE person ALTER COLUMN person_id SERIAL;
ERROR:  syntax error at or near "SERIAL"

I have seen we can create a sequence in the following fashion
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;
CREATE SEQUENCE test_id_seq OWNED BY test1.id;
ALTER TABLE test ALTER COLUMN id SET DEFAULT nextval('test_id_seq');
  UPDATE test1 SET id = nextval('test_id_seq');

But this is too much boilerplate code. Is there a one-line statement to add AUTO_INCREMENT to an existing column in Postgres?
Postgres Version: 9.6.16
Update
After doing the boilerplate code, I am trying to INSERT using the following query:
 INSERT INTO person
 (person_id, fname, lname, eye_color, birth_date)
 VALUES (null, 'William','Turner', 'BR', '1972-05-27');

ERROR:  null value in column "person_id" violates not-null constraint
DETAIL:  Failing row contains (null, William, Turner, BR, 1972-05-27, null, null, null, null, null).

Is there a workaround by which I can pass null values to the primary key where the value of that column is from the sequence?
Answer
Was able to insert using the following error:
INSERT INTO person(person_id, fname, lname, eye_color, birth_date)
       VALUES (nextval('person_id_seq'), 'William','Turner', 'BR', '1972-05-27');

If that is too much boilerplate code, then create a function that does the job:
create or replace function make_serial(p_table_schema text, p_table_name text, p_column_name text)
  returns void
as
$$
declare
  l_sql text;
  l_seq_name text;
  l_full_name text;
begin
  l_seq_name := concat(p_table_name, '_', p_column_name, '_seq');
  l_full_name := quote_ident(p_table_schema)||'.'||quote_ident(l_seq_name);

  execute format('create sequence %I.%I', p_table_schema, l_seq_name);
  execute format('alter table %I.%I alter column %I set default nextval(%L)', p_table_schema, p_table_name, p_column_name, l_seq_name);
  execute format('alter table %I.%I alter column %I set not null', p_table_schema, p_table_name, p_column_name);
  execute format('alter sequence %I.%I owned by %I.%I', p_table_schema, l_seq_name, p_table_name, p_column_name);
  execute format('select setval(%L, coalesce(max(%I),0)) from %I', l_full_name, p_column_name, p_table_name);
end;
$$
language plpgsql;

You can remove the set not null statement if you know that all your columns are already defined as NOT NULL.
To synchronize the sequence with the existing values, the above code essentially runs:
select setval('person_id_seq', max(person_id))
from person;

(Updating all rows and possibly changing their primary key values is the wrong approach to sync the sequence).
Then to change a table, all you need to do is:
select make_serial('public', 'person', 'person_id');


Transaction Replication Without Snapshot So I have looked up many other people trying to get Transaction Replication Without Snapshot working and I'm stuck. I've followed what everyone has said so I don't know what I'm missing.

Prod server and Report server.

On the prod server I configured the Distributor from the UI.
Then I setup a transnational publication from the UI.
I made sure not to check either snapshot options.
Created the publication.

Changed 'Allowed initialization from backup file' to true in the Subscription options in the UI.

Now I create a full backup of the database on the prod server and restore it to the report server. I disable all triggers and constraints on the report server.

Now also on the prod server I want to create the subscription for the report server.
I choose to run all agents at the Distributor.

I choose 'Add Subscriber' and point it to the report server.
Agent Schedule is set to Location: Distributor and Schedule: Run continuously

Subscription Properties are set as follows
Initialize: checked
Initialize When: At first synchronization

I generate the script file and edit it as below

use [Demo]
exec sp_addsubscription
@publication = N'Demo_Publication',
@subscriber = N'mo-rptsvr',
@destination_db = N'Demo',
@subscription_type = N'Push',
@sync_type = N'initialize with backup',
@backupdevicetype ='disk',
@backupdevicename = '\\backup device path.bak',
@article = N'all',
@update_mode = N'read only',
@subscriber_type = 0

And next I left unmodified

exec sp_addpushsubscription_agent
@publication = N'Demo_Publication',
@subscriber = N'mo-rptsvr',
@subscriber_db = N'Demo',
@job_login = null,
@job_password = null,
@subscriber_security_mode = 1,
@frequency_type = 64,
@frequency_interval = 0,
@frequency_relative_interval = 0,
@frequency_recurrence_factor = 0,
@frequency_subday = 0,
@frequency_subday_interval = 0,
@active_start_time_of_day = 0,
@active_end_time_of_day = 235959,
@active_start_date = 20140206,
@active_end_date = 99991231,
@enabled_for_syncmgr = N'False',
@dts_package_location = N'Distributor'

Log Reader Agent Status once in awhile says 'x transaction(s) with x command(s) were delivered'

Now looking at the logs this morning I see the following error

'Cannot insert an explicit value into a timestamp columns. Use INSERT with a column list to exclude the timestamp column, or a default into the timestamp column'

Now I have read some posts saying this is because the row timestamp column.
I have 1,000's of tables. If this is the issue what is the fastest way to change correct it?

Also after the Synchronization status errors and starts back up, Why does it always say Initializing? This alone takes over an hour before it errors out again.

Update
I found in article properties that I can change timestamp to binary(8).
Do I need to take another backup and restore it after this change?
How will this effect the production server application along with the reporting application?

What version of SQL Server are you using ?

I just setup a publication and subscription in the method you describe on SQL Server 2012 (11.0.2218) and it appears to be working fine.

I tested inserting, updating, and deleting records from the source - and all transactions made it successfully to the destination.

It is true that the timestamp column is of interest but I didn't have to use the option you mentioned in your update.  From this SQL 2000 TechNet article note that

  "The literal values for a timestamp column are replicated, but the data type for the replicated values is changed to binary (8) on the Subscriber."

Since this was for SQL 2000 I can only imagine things may have changed... as my subscriber table, just like the publication, has a timestamp column not a binary(8).

Therefore the values that are there when you backup and restore will match.  Future inserts will have different timestamp values in the source and destination.

I scripted the entire thing out into one file.  The order of events:

create the publication (with @allow_initialize_from_backup = N'true');
backup the source database (with format, init);
restore the database to the destination;
create the subscription (with @sync_type = N'initialize with backup', @backupdevicetype = 'disk', @backupdevicename = '\myUNCPath\Backup\Backup.bak').

I ran into Msg 21397 during my first try - and an interesting read, explanation, solution are in this blog post.


What are the side effects of changing MySQL `character_set_server`? I am having some strange issues with character sets on my system. After digging through all kinds of settings, I found a discrepancy in my character set settings.

When I run show variables like 'char%'; I get:

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

Are there any side effects to changing the the character_set_server variable?

Could it possibly affect what character set a connection will use when talking with the database?
Could it possibly alter the existing data in any way?

From my understanding, it looks like all it does is set the character set for you when creating a new database if you don't specify a charset. Is that really all it does?

Source: https://www.blueboxcloud.com/insight/blog-article/getting-out-of-mysql-character-set-hell

You've stumbled onto one of the imponderables of MySQL's default configuration. To quote from the documentation on Character Set Configuration:

  ...when character_set_system differs from character_set_server or
  character_set_client, and you input characters manually (as database
  object identifiers, column values, or both), these may be displayed
  incorrectly in output from the client or the output itself may be
  formatted incorrectly. In such cases, starting the mysql client with
  --default-character-set=system_character_set—that is, setting the client character set to match the system character set—should fix the
  problem.

To distill, not changing the value of character_set_server will almost certainly eventually cause you problems if you use Unicode characters outside the base ISO-latin-1 character set.


How can I disable mysql_native_password and mysql_old_password on MariaDB / MySQL? I only want to use unix_socket as the sole authentication method.
Both mysql_native_password and mysql_old_password are installed and enabled by default, but I'd like to disable them.
I'm unable to uninstall them:
MariaDB [(none)]> uninstall plugin mysql_native_password;
ERROR 1619 (HY000): Built-in plugins cannot be deleted

I've followed the instructions here:
https://mariadb.com/kb/en/mysql_plugin/
I've created disabled_plugins.ini in /etc/mysql with contents:
disabled_plugins
mysql_old_password

Then:
MariaDB [(none)]> mysql_plugin disabled_plugins DISABLE;
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 'mysql_plugin disabled_plugins DISABLE' at line 1

This is on WSL1 and 10.3.27-MariaDB-0+deb10u1-log Debian 10
Can someone help?
Thanks

You are suppose to run this command from server command line not from MariaDB console.
mysql_plugin disabled_plugins DISABLE
And then login to MariaDB & check :
 SELECT * FROM mysql.plugin;


What would be the most efficient indexes to make to speed up these 4 queries? I have a few queries that I want to speed up, but the columns they filter on are all different, but related, and I'm having trouble figuring out the best way to make indexes for them.

So the first query is something like this

1: select * from TABLE where A=? AND B=? and C=?

Then, the next three queries are all on the same table, but they each use one of the possible combinations of 2 elements from A, B and C.

2: select * from TABLE where A=? AND B=?

3: select * from TABLE where A=? and C=?

4: select * from TABLE where B=? and C=?

So I figure if I make an index on (A,B,C) that will speed up query 1 and 2, but I don't think it will do anything for query 3 or 4. So then would I need two more indexes on (A, C) and (B, C), or is there a more efficient way to do this?

I'm using Oracle 12c btw.

Without more information the answer is "it depends" as what is optimal will vary depending on the distribution properties of A, B, and C (are any of them unique per row, that is are any of them your primary key or the subject of a unique index? or do they contain many duplicate values and if so how are those values clumped?), their types & sizes, and so on. Also, how wide is the table: does it have many more columns in addition to these three and/or columns containing a lot of data (such as long strings)?

General answers where possible:

  if I make an index on (A,B,C) that will speed up query 1 and 2

Correct.

  but I don't think it will do anything for query 3 or 4.

It could help query 3. The query planner it like to be able to use that index because it starts with A and that is one of the columns filtered by in query 3. It may chose not to depending on the selectivity of A and properties of the data int he other columns, the table more generally, and in more complex queries the other objects.

If the table is quite wide, that is it has noticeably more data per row than that held in these three columns, then this index may also help query 4: in the absence of any other useful indexes for this query the query planner may chose to scan the index on A,B,C to find the matching rows instead of scanning the whole table which could mean reading many more pages.

  indexes on (A, C) and (B, C), or is there a more efficient way to do this?

If your goal is query speed only then those two indexes may be optimal for these queries. An index on just C would probably (again, depending on the data in C and the other columns) be useful for both queries 3 and 4 as the planner can index-seek using the where clause on C then partial scan to find the rows matching the clause on B or A. This, depending on the sizes of your columns, will probably save disk space & working memory and speed up inserts and updates, but is likely to be less optimal for selects then the two wider indexes.

Getting more advanced: your choice of clustering settings could be significant (see http://use-the-index-luke.com/sql/clustering/index-organized-clustered-index), particularly as you are using SELECT *, though Oracle is a little less flexible than SQL server (where any index could be the clustering key) on this matter.

To try it out for yourself, throw some realistic data into a copy of the table, and use EXPLAIN PLAN (see https://docs.oracle.com/cd/B19306_01/server.102/b14211/ex_plan.htm) to see what indexes it chooses to use for your queries and other performance counters to see what wall-clock effect this has.


MongoDB: Index intersection does not eliminate the need for creating compound indexes This is a quote from the MongoDB docs:

https://docs.mongodb.com/v3.0/core/index-intersection/#index-intersection-and-compound-indexes

But it doesn't explain why it doesn't eliminate the need for compound indexes. Why doesn't it?

There are a couple of reasons for this at present (I say at present, because improvements may be introduced in the future that reduce the relevance of these points):

Index intersection is limited to 2 indexes (this was the case in 2.6 but I don't see anything in subsequent release notes to suggest this has been increased)
If you can predict your query patterns and cover them with compound indexes then this will likely perform better
Index intersection is currently essentially a last resort to provide some index based coverage (as opposed to a table scan for example) for query patterns that are not already covered by a more efficient method

This is true for most purposes and most query profiles. A counter example here would be a set of queries, regularly run, which would require a large number of compound indexes. The work being done to keep those indexes up to date, the amount of memory they take up might mean that index intersection is a better strategy. This, however, is an edge case - most database usage can be profiled and indexed efficiently so that index intersection is not required.


select insert query optimization on large volume MYSQL 8.x Im trying to run this SELECT query from table bidPrice that has over 160 million rows into a second table called spreads_v2
INSERT INTO spreads_v2(SELECT bp1.timestamp, bp1.idExchangePair, bp1.idRequest, bp1.idExchangePlatform AS idEp_a, bp2.idExchangePlatform AS idEp_b, bp1.lastPrice as lastPrice_a, bp2.lastPrice AS lastPrice_b,
(SELECT IF (bp1.lastPrice> bp2.lastPrice,bp1.lastPrice,bp2.lastPrice)),
(SELECT (((ABS(bp1.lastPrice - bp2.lastPrice))*100)/(SELECT IF (bp1.lastPrice> bp2.lastPrice,bp1.lastPrice,bp2.lastPrice)))) AS spread_percentage,
ABS(bp1.lastPrice - bp2.lastPrice) AS spread_A_B
 FROM bidPrice AS bp1
JOIN bidPrice AS bp2
ON bp1.idRequest = bp2.idRequest AND bp1.idExchangePair = bp2.idExchangePair AND bp1.idExchangePlatform < bp2.idExchangePlatform)

The query works fine, i tested on a test db with the right output, but the issue comes when i run this into the production db that has large amounts of data, i ran into canceled query after 12 hours of execution.
heres the SHOW CREATE table for both
CREATE TABLE `bidPrice` (
  `idBidPrice` int NOT NULL AUTO_INCREMENT,
  `idRequest` int NOT NULL,
  `idCurrencyPair` int NOT NULL,
  `idExchangePlatform` int DEFAULT NULL,
  `idExchangePair` int DEFAULT NULL,
  `bidPrice` double DEFAULT NULL,
  `timestamp` datetime DEFAULT CURRENT_TIMESTAMP,
  `wholeApiResponse` longtext,
  `askPrice` double NOT NULL,
  `lastPrice` double NOT NULL,
  `lowPrice` double NOT NULL,
  `highPrice` double NOT NULL,
  `volumePrice` double NOT NULL,
  `platformTimestamp` double DEFAULT NULL,
  PRIMARY KEY (`idBidPrice`,`idRequest`,`idCurrencyPair`),
  KEY `idExchangePlatform` (`idExchangePlatform`),
  KEY `idExchangePair` (`idExchangePair`),
  CONSTRAINT `bidPrice_ibfk_1` FOREIGN KEY (`idExchangePlatform`) REFERENCES `exchangePlatform` (`idExchangePlatform`),
  CONSTRAINT `bidPrice_ibfk_2` FOREIGN KEY (`idExchangePair`) REFERENCES `exchangePair` (`idExchangePair`)
) ENGINE=InnoDB AUTO_INCREMENT=176759983 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE `spreads_v2` (
  `timestamp` datetime DEFAULT CURRENT_TIMESTAMP,
  `idExchangePair` int DEFAULT NULL,
  `idRequest` int DEFAULT NULL,
  `idExchangePlatform_A` int DEFAULT NULL,
  `idExchangePlatform_B` int DEFAULT NULL,
  `bidPrice_A` double DEFAULT NULL,
  `bidPrice_B` double DEFAULT NULL,
  `high_value` double DEFAULT NULL,
  `spread_percentage` double DEFAULT NULL,
  `spread_A_B` double DEFAULT NULL,
  UNIQUE KEY `idExchangePair` (`idExchangePair`,`idRequest`,`idExchangePlatform_A`,`idExchangePlatform_B`),
  KEY `idExchangePlatform_A` (`idExchangePlatform_A`),
  KEY `idExchangePlatform_B` (`idExchangePlatform_B`),
  CONSTRAINT `spreads_v2_ibfk_1` FOREIGN KEY (`idExchangePair`) REFERENCES `exchangePair` (`idExchangePair`),
  CONSTRAINT `spreads_v2_ibfk_2` FOREIGN KEY (`idExchangePlatform_A`) REFERENCES `exchangePlatform` (`idExchangePlatform`),
  CONSTRAINT `spreads_v2_ibfk_3` FOREIGN KEY (`idExchangePlatform_B`) REFERENCES `exchangePlatform` (`idExchangePlatform`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

As an aditional comment, i have this database in a AWS RDS t2.micro, im considering on upgrading the db instance for query speed up.

The first thing about your query is the indexing on bidPrice needs to map to your join criteria:
Hence the index on bidPrice should be:
idRequest, idExchangePair, idExchangePlatform

The first two can be in either order as both are referenced with an = and idExchangePlatform is a range < based search so leave this last.
Extend one of the existing indexes that begins with the same element(s).
The subquery SELECT statements in the query are ugly.
SELECT ...,
   GREATEST(bp1.lastPrice, bp2.lastPrice),
   (ABS(bp1.lastPrice - bp2.lastPrice))*100)/GREATEST(bp1.lastPrice, bp2.lastPrice)

will make it simpler (but probably won't help with speed significantly) - ref: GREATEST.
To help with such a large scan, batch this up based on the first element of the index above.
So:
INSERT ... SELECT ... JOIN ... WHERE idRequest < 10000;
INSERT ... SELECT ... JOIN ... WHERE idRequest BETWEEN 10001 AND 20000;
....


Index use and column type I was told today that having a table structure similar to this is bad for performance of indexes because of the mismatch of column types.

Table 1
id - int (7) PK
Others ....

Table 2
id int (11) Index    (has to exist in table 1 logically - though not enforced through FK)
Others ....

I'm using MySQL with InnoDB.

The question I suppose could also be related to Varchar 200 vs 220 for example.

I know that the index size will be affected by the extra space per item needed (and storage space in general), probably in the 5-10% area, but will the time taken for joins to happen increase?

Phrases like bad for performance are essentially meaningless. Measurements are  meaningful; opinions are, umm, not measurements.

But int(7) and int(11) are both 32-bit integers. The number in parens refers to display width; it has nothing to do with storage size or index size.

For varchar() columns, you can create an index that's based on the first 'n' characters. That decision has some performance implications, for example when you're searching for 'wibble' in a column indexed on the first three characters. And there are several subtle points with columns of type "text". Read CREATE TABLE carefully. (Search for "length".)

But I'd be a little surprised if you could measure any difference in joining columns of varchar(200)/varchar(200) and in joining columns of varchar(220)/varchar(200). That is, other than the obvious--storing more than 200 characters in the varchar(220) column.

There's an easy way to tell: write some code in your favorite scripting language, generate a few million rows of sample data, load it, and test it. And post your results here, too.


Percona xtradb Backup for OLTP I have installed MySQL 5.7 community edition installed.
Can we use Percona xtradb backup tool for backup DB for banking site databases for OLTP?

Percona is a very popular and reputed company. Their products also meet some compliance. So there is nothing to say NO. But you mentioned that this is for Bank. Many banks have their own compliance. So, verify that you are authorized to use 3rd party software.

But the native mysqldump is not good for taking the large backup set. So its good to use percona's xtra backup or mydumper.


Will a change from MyISAM to InnoDB require SQL query changes in MySQL? I am planning to change some large MyISAM tables in my MySQL database into InnoDB.

This is because I am having performance problems where large and complex updates (updating a few million rows in one go) are stopping users from concurrently accessing that tables.

I understand that if I convert the table to InnoDB then the writes/updates will cause row locking rather than table locking.

If I do this, Will have have to review all my SQL selects/insert statements, and my stored procedures, and make any changes due to the engine change, or will they all 'just work'?

I don't think you need to change any SQL statements and SP's, They should work as they were before.

You need to keep in mind some points

If you have MySQL version 5.5 or below and you have FULL TEXT index on Table then this is a issue, InnoDB doesn't support FULL TEXT index up to MySQL 5.5, They are supported in MySQL 5.6 and above.
If you have Spatial Indexes on table, They are yet not supported in InnoDB engine

There are a lot of other points that you should keep in mind are, Please must have a look at some great links which suggest what are various things you should consider in migrating table from MyISAM to InnoDB

What are the main differences between InnoDB and MyISAM.?
How do you tune MySQL for a heavy InnoDB workload. ?
Should I move to InnoDB during a planned migration. ?
Online conversion from MyISAM to InnoDB ?
Converting Tables from MyISAM to InnoDB.


Get The Lowest Date if The other Column is Null I have the following

CREATE TABLE employees (employee_id int, from_date date, to_date date);

INSERT INTO employees(Employee_ID,From_Date,To_Date)
VALUES
    (1    ,       '1998-01-02'   ,  NULL),
    (1    ,       '2000-01-01'   ,  NULL),
    (1    ,       '2015-01-01'   ,  '2018-01-01'),
    (2    ,       '2005-01-01'   ,  '2006-01-01'),
    (2    ,       '2007-01-01'   ,  '2007-05-05'),
    (3    ,       '2002-02-02'   ,  '2004-02-02'),
    (3    ,       '2010-01-01'   ,  '2015-01-01'),
    (4    ,       '2001-01-01'   ,  '2005-03-03'),
    (4    ,       '2003-03-03'   ,  '2004-04-04');

I want to select all the records with the lowest From_Date only if the record has a To_Date and the time duration between the From_Date and To_Date of that record is less than three years. The result should look like below:

Employee_ID     From_Date         To_Date
    2          2005-01-01       2006-01-01
    3          2002-02-02       2004-02-02

In other words if any employee is missing the To_Date in their lowest From_Date record and if the record with lowest From_Date has a To_Date but the time_duration is more than 3 years, they shouldn't be considered. As it can be seen in above result table the employee with ID 1 is not considered because the lowest From_Date record has not To_Date and the Employee with ID 4 is not considered because the time duration between From_Date and To_Date of the lowest record is more than three years.

Here is what I came up with

SELECT employee_id, from_date, to_date
FROM employees AS e1
WHERE to_date IS NOT NULL -- may not be useful as per comments.
  AND to_date - from_date <= (365 * 3)
  AND EXISTS (
    SELECT
    FROM employees AS e2
    WHERE e1.employee_id = e2.employee_id
    GROUP BY employee_id
    HAVING e1.from_date = min(e2.from_date)
  );


How can I redirect database requests in my postgres server So I have a list of(lot many) scrapers which push lot many data into one of my database location. Unfortunately I have a requirement that my database location  have to be changed for some performance issues. Rather than changing my so many scripts config can I have some mechanism implemented in my db server so that database requests coming from certain IP(machines) I would redirect  them to other database/server?

As @horse_with_no_name already comment: you can use pgbouncer to do that.

Basically you need to create a pool configuration, and during your maintenance you ajust the pool config to a new server or database.

Take a look at the session [databases] on /etc/pgbouncer.ini file:

[databases]
; no connection info enforces unix socket
foodb =
; bardb config
bardb = host=127.0.0.1 dbname=bardb

So, if you want to redirect the connections for the database bardb for another database, what you need to do is edit the dbname and reload de pgbouncer service. E.G.:

; bardb config
bardb = host=127.0.0.1 dbname=maintenancedb

Please, take a look at the documentation[1] for more details.

You can do the same with the pgpool[2].

Hope it helps.

References:

https://pgbouncer.github.io/config.html
http://www.pgpool.net


MySQL - MySQLDump problems with large table I have a MySQL database that is 10.6GB - that has both MyISAM & InnoDB tables.
One particular table (called 'responses') is 8Gb - this table is InnoDB.

The live database is replicated to a different server & I take backups using MySQLDump from the replication server... the live server runs Windows, but the replication server runs CentOS.

The MySQLDump command is:

mysqldump --verbose --lock-tables=true --max-allowed-packet=1024M --host=192.168.1.182 --user=myusername --password=mypassword --opt --databases databasename > databasename.sql

The command runs until half-way through the 'responses' table - when we get:
Error 2013 - Lost connection to MySQL server during query when dumping table responses at row 12891212

After some Googling, I tried added the following commands to the MySQLDump (independently & at the same time):

--net-buffer-length=32704

and

--skip-extended-insert

The error still occurred at the same place.

For completeness, I've tried dumping the database in NaviCat and MySQL Workbench - both return the same error.

So, I wondered if it was an issue with corruption. I tried to run the following
 (on the replication server):

CHECK TABLE responses;

and

CHECK TABLE responses EXTENDED;

Both returned the following:
Error 2013 - Lost connection to MySQL server during query.

The live database is VERY busy, so I'm a bit worried... I started CHECK TABLE on the live table, but unsurprisingly, it caused the application to stop so I had to stop it.

Any advise would be welcome here... the ultimate goal is to get the backups using MySQLDump working.

Thanks

Update: I tried copying the table (on the replication server) to a new database on another server using NaviCat - it failed with the same message (transferred records: 12,889,214):
Error 2013 - Lost connection to MySQL server during query

Your problem stems from network. In this instance, it is not the buffer used for data transmission over the network. It is the length of time the network tries to keep alive in so doing.
Please note these options:

net_read_timeout (Default 30) : The number of seconds to wait for more data from a connection before aborting the read. When the server is reading from the client, net_read_timeout is the timeout value controlling when to abort. When the server is writing to the client, net_write_timeout is the timeout value controlling when to abort. See also slave_net_timeout.
net_write_timeout (Default 60) : The number of seconds to wait for a block to be written to a connection before aborting the write. See also net_read_timeout.

You have a busy server and, at some moment in time, the data you need to read for the mysqldump to continue times out.
What I have done in the past is run the following on both source and target servers:
mysql> SET GLOBAL net_read_timeout = 31536000;
mysql> SET GLOBAL net_write_timeout = 31536000;

Then, go run the mysqldump.
Setting these values very high forces the mysqldump DB Connection to wait a long time for data to be shipped over. In case you are wondering, The number 31536000 is the number of seconds in a year (365 * 24 * 60 * 60). The mysqldump DB connection will just have to stay around and wait, even for your busy server, until data comes (eventually). Please see my post from Jan 12, 2012 : Client times out, while MySQL query remains running?
GIVE IT A TRY !!!
UPDATE 2018-01-04 09:53 EST
I noticed your comment just now
Please note the error message you got from the mysqldump:

Error 2013 - Lost connection to MySQL server during query when dumping table responses at row 12891212

Please take note of the row number 12891212. If that row number comes back in the error message every single time your mysqldump fails, then maybe you can suspect data corruption. However, if your get a different row number with every mysqldump attempt, this suggests mysqldump connection is able to move along the large table and just timeout and some random point.
Please go set those values and try the mysqldump again. Then, come back back tell us what happened.


Data scrambling for the development data I have a lot of sensitive data in production like SSN, name, email, and phone number.

I have function to scramble this data using a random value hashmap technique. But in the case of SSN (social security number), we have different tables that contain the same SSN. This causes my data to be messed up because I need to keep keep the same scrambled value for every instance of the same SSN.

Currently, if I have a SSN 12345678 in both the person table and the elige table, then my function's logic causes the tables to have different SSN values after scrambling (i.e. 854612397 for person, 963258741 for elige table).

Desired output should be that the scrambled SSN is the same in both tables.

If you produce the new SSNs with a function like newSSN = makeSSN(hash(realSSN+salt)), where hash() is a known good hash function[1], salt is a long randomly generated string, and makeSSN() takes some/all of the hash function's output to make a nine-digit number output[2]. This way you get the same new value where-ever you have the same input SSN without needing to produce a mapping table to keep values consistent. To make sure that a real SSN can't be mapped to one in the resulting test data by using the same function[3] don't store the salt value between invocations.

Of course the "there are going to be other ways to identify what real person maps to which person in your munged test data-set" issue is something you should be aware of. For truly sensitive data it is far easier to be secure if you generate test data procedurally instead of by trying to depersonalise real live data.

[1] SHA2, SHA3, ...

[2] for example you could take the first or lowest 29 bits, or use CRC32 with output to decimal and strip off one character as it can produce 10

[3] though there are going to be other ways to identify what real person maps to which person in your munged test data-set


Another BCP Export [Fail]ure All, in SELECT Data Set Using Stored Procedure with BCP Error @Remus Rusanu provided a great solution to the problem. However, this evolved in to another problem: this BCP query now runs

DECLARE @SQL VARCHAR(8000);
SELECT @SQL = 'bcp "EXEC [2BSHAEOS]..ispsSelectEmptyAsNull ''B1A'';" ' +
               'queryout "F:\aaData\IPACostData\R15TData\2BSHAEOS_B1A_BCPTesting2.txt" ' +
               '-f "F:\aaData\IPACostData\R15TData\tmpFormatCard_B1A.fmt" -T -S' + @@SERVERNAME + '';
EXEC master..xp_cmdshell @SQL;
GO

This was then failing due to the ordering of the fields in the .fmt file conflicting with those in the results from my SP. Now, following a rewrite of my SP to get the order of the fields in the ORDINAL_POSITION equal to the .fmt file, the above BCP export query gives me

SQLState = S0002, NativeError = 208
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name '#TmpTableColumnNames'.
NULL

Edit: Changed SP Where the SP is

IF EXISTS (SELECT name
           FROM sys.procedures
           WHERE name = N'ispsSelectEmptyAsNull')
DROP PROCEDURE ispsSelectEmptyAsNull;
GO
CREATE PROCEDURE ispsSelectEmptyAsNull @TableName NVARCHAR(256)
AS
DECLARE @Columns VARCHAR(MAX);
SELECT @Columns =
    COALESCE(@Columns + N',
    NULLIF([' + CAST(COLUMN_NAME AS VARCHAR) + N'], '''') AS ' + COLUMN_NAME + '',
  N'NULLIF([' + CAST(COLUMN_NAME AS VARCHAR) + N'], '''') AS ' + COLUMN_NAME + '')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
ORDER BY ORDINAL_POSITION;
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = N'
    SELECT ' + @Columns + N'
    FROM ' + @TableName + N';';
EXEC (@SQL);
GO

To say this stuff is making me want to poke myself in the eye with a long sharp stick is an understatment. Any help would be appreciated. Clearly from this MSDN link this error is an invalid column number. But the ORDINAL_POSITIONs of both the .fmt and the results obtained from the SP are the same!

As I wrote in the comment to your previous post:

  the bcp syntax look ok.
  I double checked with BOL and the only limitation for bcp is that
  if you use a procedure all tables have to exist prior to bcp being executed

No temporary tables or bcp will fail!

Update 1:
Here is an example on how to exclude columns in the format file (*.fmt) for the bcp utility:

9.0
3
1       SQLCHAR       0       7      "\t"     1     Col1         ""
2       SQLCHAR       0       0      ""       0     Col2         ""
3       SQLCHAR       0       100    "\r\n"   3     Col3         SQL_Latin1_General_CP1_CI_AS

Note the zero (0) in the length for Col2. You can find this example in the BOL of SQL Server 2008 at:

ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_4deptrbl/html/30e0e7b9-d131-46c7-90a4-6ccf77e3d4f3.htm

Or search for Using a Format File to Skip a Table Column in BOL.


Why is SQL running the same query longer from another connection? Here is the issue overview: Why does my stored procedure run faster when executed localy vs remotely?

Dont jump to any conclusion just yet, let me explain what I mean...

Here is the setup:
A Windows 2008R2 (correction: 2003) application server executes a stored procedure that performs some action, (what its doing is really important at this point in time). This stored procedure is executed on the SQL server over a TCP/IP connection to the database server. The DB server is physicaly located right next to the application server, and they are connected to eachother via 1GB NICs to a 1GB Switch. The DB server is running SQL 2005 SP2 Enterprise Edition, and has 16GB of memory and several vLUNS striped across 48 15k drives in an HP-EVA FC connected SAN. From all indicators thus far, there are no I/O, Mem, or CPU constreints or limits being hit. Trace Falg 1118 is on and TempDB is split across 8 file on their own vLUN. Data, and TLogs also have their own vLUNS too.

So, here is what I am seeing:
Using SQLCMD on the database server, with SQLProfiler running from the same DB server, I can execute the stored procedure and I see that the execution starts immediatly, and compleats with a durration of about 2,100ms with an IO of about 1200.

Using SQLCMD on the application server, with SQLProfiler running from the DB server, I can execute the same exact stored procedure, with the SAME exact parameters, and I see that the execution starts immediatly, and compleats with a durration of about 110,000ms with an IO of about 1200.

The query results in 1 row, with 4 columns [INT, INT, VARCHAR(50), VARCHAR(100)]

ASIDE:(I know the query is a train wreck, this is a regulated system and I cannot change it on a live prodution server, so please dont make any sugestions about doing so. The next version has been rewritten to be better.)

From everything we can see, there is no reason that we should be seeing differances like this, but what is heppening is the .NET application that calls this query from the application server is timing out waiting for the responce.

We have checked locking and blocking, WAIT states, Query plans, IO contention, CPU contention, MEM contention, NETWORK saturation/utilization, performed indexes rebuilds on all indexes, updates all stats, and a hand full of other items, but haven't come up with anything that points to why this is happening.

Please ask more questions if you have any, make recomendations as you come up with them, and depending on the impact (remember this is a production environment) we will try them and respond back.

-Thanks!
-Chris

This is not a networking issue (though that is tempting).

This is almost certainly "noise." What I mean here is that you need to run this operation perhaps 100 times on the db server itself and from the app server, preferably over a span of 2 hours or so (or more).

What I strongly expect you will find is that at certain times it runs fast on either/both systems, and at certain times it is slow no matter where it is initiated.

E.g. the SQL workload is what you are actually measuring, not the network.


Is the table InnoDB? For that the index statistics are gathered by "index dives" and are by definition imprecise.

EXPLAIN is not actually executing the query, but uses the estimates to plan it so it shows those imprecise numbers.

The important thing is that if it keeps numbers in the same order as reality (which, being a double in your case, it is), it works well enough for most cases.

Keeping exact numbers is too cumbersome and not even really possible in transactional environment.


EF Code First uses nvarchar(max) for all strings. Will this hurt query performance? I have some databases created using Entity Framework Code First; the apps are working and in general I'm pretty happy with what Code First lets me do. I am a programmer first, and a DBA second, by necessity. I am reading about DataAttributes to further describe in C# what I want the database to do; and my question is: what penalty will I be eating by having these nvarchar(max) strings in my table (see example below)?

There are several columns in this particular table; in C# they are defined as such:

    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int ID { get; set; }
    public string Name { get; set; }
    public string Message { get; set; }
    public string Source { get; set; }
    public DateTime Generated { get; set; }
    public DateTime Written { get; set; }

I expect to query and/or sort based on Name, Source, Generated, and Written. I expect Name and Source to be in the 0-50 character length, occasionally up to 150. I expect this table to start pretty small (<100k rows), but grow significantly over time (>1m rows). Obviously message could be small or large, and will probably not be queried against.

What I want to know, is there a performance hit for my Name and Source columns being defined as nvarchar(max) when I never expect them to be larger than 150 characters?

Larger nvarchar (max) data items (over 8000 bytes or so) will spill over into text storage and require additional I/O.  Smaller items will be stored in-row.  There are options that control this behaviour - see this MSDN article for more details.

If stored in-row there is no significant I/O performance overhead; there may be additional CPU overhead on processing the data type but this is likely to be minor.

However, leaving nvarchar (max) columns lying around the database where they are not needed is rather poor form.  It does have some performance overhead and often data sizes are quite helpful in understanding a data table - for example, a varchar column 50 or 100 chars wide is likely to be a description or a free-text field where one that's (say) 10-20 chars ling is likely to be a code.  You would be surprised how much meaning that one often has to infer from a database through assumptions like this.

Working in data warehousing, as often as not on poorly supported or documented legacy systems, having a database schema that's easy to understand is quite valuable.  If you think of the database as the application's legacy, try to be nice to the people who are going to inherit it from you.


pg_restore terminates with "out of memory" error Trying to restore a 45mb pg_dump file but it keeps failing with an "out of memory" error.

Content of my postgresql-10-main.log:

2018-01-16 06:14:50.692 WAT [32405] postgres@app_development LOG:

could not receive data from client: Connection reset by peer
2018-01-16 06:14:50.692 WAT [32405] postgres@app_development CONTEXT:  COPY assessment_scores, line 15347
2018-01-16 06:14:50.692 WAT [32405] postgres@app_development STATEMENT:  COPY assessment_scores (id, student_id, assessment_id, score, exempt, late, collected, missing, comment, deleted_at, created_at, updated_at) FROM stdin;

2018-01-16 06:14:50.693 WAT [32405] postgres@app_development LOG:  incomplete message from client
2018-01-16 06:14:50.693 WAT [32405] postgres@app_development CONTEXT:  COPY assessment_scores, line 15347
2018-01-16 06:14:50.693 WAT [32405] postgres@app_development STATEMENT:  COPY assessment_scores (id, student_id, assessment_id, score, exempt, late, collected, missing, comment, deleted_at, created_at, updated_at) FROM stdin;

2018-01-16 06:14:50.693 WAT [32405] postgres@app_development ERROR:  unexpected EOF on client connection with an open transaction
2018-01-16 06:14:50.693 WAT [32405] postgres@app_development CONTEXT:  COPY assessment_scores, line 15347
2018-01-16 06:14:50.693 WAT [32405] postgres@app_development STATEMENT:  COPY assessment_scores (id, student_id, assessment_id, score, exempt, late, collected, missing, comment, deleted_at, created_at, updated_at) FROM stdin;

2018-01-16 06:14:50.693 WAT [32405] postgres@app_development FATAL:  terminating connection because protocol synchronization was lost
2018-01-16 06:14:50.693 WAT [32405] postgres@app_development LOG:  could not send data to client: Broken pipe

I checked with HTOP, my RAM is at about 4GB/8GB and SWAP space is unused when this happens.

DB server is localhost.

Figured out the problem.

I was trying to import a DB dump from Postgres 9.6 to Postgres 10.

Downgraded to Postgres 9.6 and it imported successfully.


Oracle 11g - Decreasing Big Table Size I want to reduce the size of a table.

I thought if I could do this with export / import, but when I check it
with the ESTIMATE_ONLY option, it doesn't seem to work.
I have no chance of using partitioning.
If I do it with CTAS (create table as select), the archive production
will peak and the disaster database will be affected.
How can I reduce the size of this table? If I do shrink, will UNDO be
enough and how much space will be enough to add UNDO tablespace to
overcome this problem? Do you have an estimate of how long it will
take if I shrink?
I have to be very careful as it will be done directly in the
production database.

Version: Oracle 11g Release 2 - Standard Edition
ASM option: yes
Cluster: yes
Partitioning option: no
Compress data pump option: no

Table size information:
SQL> select segment_name,sum(bytes)/1024/1024/1024 GB from dba_segments where owner='OWNER_NAME' and segment_type='TABLE' and segment_name=upper('TABLE_NAME') group by segment_name;

SEGMENT_NAME                                                                              GB
--------------------------------------------------------------------------------- ----------
TABLE_NAME                                                                        392.493164

Export information (It does not work in reducing the table size.
):
nohup expdp "'/ as sysdba'" directory=DP_DIR tables=OWNER_NAME.TABLE_NAME ESTIMATE_ONLY=Y &

Output:
Starting "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" directory=DP_DIR tables=OWNER_NAME.TABLE_NAME ESTIMATE_ONLY=Y
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
.  estimated "OWNER_NAME"."TABLE_NAME"            392.4 GB
Total estimation using BLOCKS method: 392.4 GB

Best regards,

The most direct method shrinking a table is:
ALTER TABLE [table name] ENABLE ROW MOVEMENT;
ALTER TABLE [table name] SHRINK SPACE CASCADE;

More details here: https://oracle-base.com/articles/misc/alter-table-shrink-space-online
There are some limitations to this command:

Moving rows can cause problem with rowid based triggers.
Rowid materialized views must be rebuilt after a shrink operation.
The shrinking process is only available for objects in tablespaces with
automatic segment-space management enabled.
You can't combine the SHRINK SPACE clause with any other ALTER TABLE clauses.
You can't shrink a cluster or a clustered table.
You can't shrink any object with a LONG column.
You can't shrink tables with dependent function-based indexes, domain indexes, or bitmap join indexes.
You can't shrink tables that are the master table of an ON COMMIT
materialized view Mapping tables of index-organized tables are not
affected by a shrink.
Shrinks can't be used for compressed tables, except those using Advanced Row Compression (ROW STORE COMPRESS ADVANCED).
The shrink operation against a table doesn't cascade to
the LOB segments. They need to handled separately.
You can't shrink securefile LOB segments.
Changing the arrangement of rows in a table can have a negative impact on performance in some circumstances. Test thoroughly before making any decisions.
After any structural change, like a move, remember to check for unusuable indexes. If you have any, rebuild them.


How do I insert a row which contains a foreign key? Using PostgreSQL v9.1.  I have the following tables:

CREATE TABLE foo
(
    id BIGSERIAL     NOT NULL UNIQUE PRIMARY KEY,
    type VARCHAR(60) NOT NULL UNIQUE
);

CREATE TABLE bar
(
    id BIGSERIAL NOT NULL UNIQUE PRIMARY KEY,
    description VARCHAR(40) NOT NULL UNIQUE,
    foo_id BIGINT NOT NULL REFERENCES foo ON DELETE RESTRICT
);

Say the first table foo is populated like this:

INSERT INTO foo (type) VALUES
    ( 'red' ),
    ( 'green' ),
    ( 'blue' );

Is there any way to insert rows into bar easily by referencing the foo table?  Or must I do it in two steps, first by looking up the foo type I want, and then inserting a new row into bar?

Here is an example of pseudo-code showing what I was hoping could be done:

INSERT INTO bar (description, foo_id) VALUES
    ( 'testing',     SELECT id from foo WHERE type='blue' ),
    ( 'another row', SELECT id from foo WHERE type='red'  );

Your syntax is almost good, needs some parenthesis around the subqueries and it will work:

INSERT INTO bar (description, foo_id) VALUES
    ( 'testing',     (SELECT id from foo WHERE type='blue') ),
    ( 'another row', (SELECT id from foo WHERE type='red' ) );

Tested at DB-Fiddle

Another way, with shorter syntax if you have a lot of values to insert:

WITH ins (description, type) AS
( VALUES
    ( 'more testing',   'blue') ,
    ( 'yet another row', 'green' )
)
INSERT INTO bar
   (description, foo_id)
SELECT
    ins.description, foo.id
FROM
  foo JOIN ins
    ON ins.type = foo.type ;


Changing Table Ownership in Postgres from Postgres User I have a database with some tables that was created by a certain user. All tables and sequences in that DB were owned by that user. I needed to change the owner of those tables and sequences to be able to run certain operations (Django Migrations).
In doing so, I changed it to postgres by mistake:
REASSIGN OWNED BY delme TO postgres;

Now, when I attempt to correct the oversight, I get the following:
db_name=# REASSIGN OWNED BY postgres TO django_development;
ERROR:  cannot reassign ownership of objects owned by role postgres because they are required by the database system

How can I change ownership of that entire db's contents to django_development user?

The problem is, that the postgres user also owns all system catalogs and you can't reassign ownership for them.
You will need to do this manually for each table in each schema you have.
I would first do it for the schemas:
alter schema public owner to django_development;
... repeat for all schemas

Then do it for all tables in the schemas using something like the following (not tested):
do
$$
declare
  l_rec record;
  l_sql text;
begin
  for l_rec in select schemaname, tablename
               from pg_tables
               where schemaname in ('public', .... ) --<< adjust for your schemas
                 and tableowner = 'postgres'
  loop
    l_sql := format('alter table %I.%I owner to django_development', l_rec.schemaname, l_rec.tablename);
    raise notice 'Running %: ', l_sql;
    execute l_sql;
  end loop;
end;

If you have other objects (views, functions, etc) you will need to something equivalent for them as well.


PostgreSQL data type text vs varchar without length In PostgreSQL you can create a column with data type character varying (without length precision) or text like this:

ALTER TABLE test ADD COLUMN c1 varchar;
ALTER TABLE test ADD COLUMN c2 text;

Is there a difference between these two data types?

The documentation is not clear about it. They say :

  If character varying is used without length specifier, the type
  accepts strings of any size.

  [...]

  In addition, PostgreSQL provides
  the text type, which stores strings of any length.

It seems that these two datatypes are equivalent but it is not explicit... More info about this?

Thank you,
Nico

There is no difference between the two data types.  They use the exact same storage and the same operators.


Duplicate key on delete and update of unique key I have a table with 10^7 records. I do the following:

Delete a record using a unique key to identify it
Update another record's unique key to the one I have just deleted

MySQL 5.7 complains about duplicate keys. Schematically, the sequence of commands looks like the following:

create database foo;
use foo;

create table users(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `login` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `login` (`login`),
  KEY `email_idx` (`email`)
);

insert into users values(1, 'foo', 'foo@gmail.com');
insert into users values(2, 'bar', 'bar@hotmail.com');

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
delete from users where login = 'foo';
update users set login = 'foo' where email = 'bar@hotmail.com';
COMMIT;

The code above does not fail, but the actual code does fail, even when just one transaction runs.

Why could this be happening?

The mentioned issue will not surface for the given code. If you could create a new code with which we shall reproduce the same error, immediate reply is guaranteed.

The choices are

Do not alter the unique key. You shall redesign your logic.
Unique key fields takes little longer to get updated as they need to be updated both in the data file and the index file. So, give some time gap before using the same key.
Try (atleast) committing the delete and then move on to creating another row with the same deleted key row (though not a good suggestion).


What parameters should be modified after upgrading RAM memory in Oracle server I have a linux server (rhel4) with oracle 9i (9.2.0.8) to which we have recently upgraded the RAM memory from 4G to 16G. I would like to know what server parameters I should modify and even more what Oracle parameters should I modify to make use of this new configuration from memory, thank you very much for your answers.

In 9i, multiple parameters are used for sizing components of the SGA. Below is a typical (but not complete) list:
DB_CACHE_SIZE
JAVA_POOL_SIZE
LARGE_POOL_SIZE
SHARED_POOL_SIZE
And SGA_MAX_SIZE, if set.
Also I assume for simplicity that you use automatic PGA management, so for PGA:
PGA_AGGREGATE_TARGET


Check if there are users who are using a common password Table Structure:
userid password adminlevel

userid is BIGINT (UNIQUE)
password is VARCHAR
adminlevel is SMALLINT

How do I write a query to get a list of users with adminlevel greater than 0 whose password is being used on other accounts?

The query result must satisfy the following conditions:

adminlevel > 0
there are accounts other than itself where the same password is being used

SELECT a.userid AS TheAdmin,
       GROUP_CONCAT(b.userid) AS NonAdminsWithSamePwd
    FROM tbl AS a
    JOIN tbl AS b  ON b.password = a.password
    WHERE a.adminlevel > 0
      AND b.adminlevel = 0


Choose security group for restore-db-instance-to-point-in-time When using the AWS CLI restore-db-instance-to-point-in-time command, I can't figure out how to set the security group. The doc on this page says:

  The target database is created from the source database with the same configuration as the original database except that the DB instance is created with the default DB security group.

Is it possible to override this parameter to use the same security group as the original instance?

Not without an additional API call.  You will need to wait for the target database to move to an "available" state and then use modify-db-instance with the target database and security group as parameters.

Here's some pseudocode based off of a similar example I have in Powershell. Selecting information from describe-db-instances will vary by which language you're using the API with, but by default it looks like it comes back as JSON.

restore-db-instance-to-point-in-time --source-db-instance-identifier sourceDatabase --target-db-instance-identifier targetDatabase
while(targetinstance not "available")
{
    targetinstance = describe-db-instances --db-instance-identifier targetDatabase
    sleep for X seconds
}
modify-db-instance --db-instance-identifier targetInstance --db-security-groups mySecurityGroup


Why is selecting all resulting columns of this query faster than selecting the one column I care about? I have a query where using select * not only does far fewer reads, but also uses significantly less CPU time than using select c.Foo.

This is the query:

select top 1000 c.ID
from ATable a
    join BTable b on b.OrderKey = a.OrderKey and b.ClientId = a.ClientId
    join CTable c on c.OrderId = b.OrderId and c.ShipKey = a.ShipKey
where (a.NextAnalysisDate is null or a.NextAnalysisDate < @dateCutOff)
    and b.IsVoided = 0
    and c.ComplianceStatus in (3, 5)
    and c.ShipmentStatus in (1, 5, 6)
order by a.LastAnalyzedDate

This finished with 2,473,658 logical reads, mostly in Table B.  It used 26,562 CPU and had a duration of 7,965.

This is the query plan generated:

On PasteThePlan: https://www.brentozar.com/pastetheplan/?id=BJAp2mQIQ

When I change c.ID to *, the query finished with 107,049 logical reads, fairly evenly spread between all three tables.  It used 4,266 CPU and had a duration of 1,147.

This is the query plan generated:

On PasteThePlan: https://www.brentozar.com/pastetheplan/?id=SyZYn7QUQ

I attempted to use the query hints suggested by Joe Obbish, with these results:
select c.ID without hint: https://www.brentozar.com/pastetheplan/?id=SJfBdOELm
select c.ID with hint: https://www.brentozar.com/pastetheplan/?id=B1W___N87
select * without hint: https://www.brentozar.com/pastetheplan/?id=HJ6qddEIm
select * with hint: https://www.brentozar.com/pastetheplan/?id=rJhhudNIQ

Using the OPTION(LOOP JOIN) hint with select c.ID did drastically reduced the number of reads compared to the version without the hint, but it is still doing about 4x the number of reads the select * query without any hints.  Adding OPTION(RECOMPILE, HASH JOIN) to the select * query made it perform much worse than anything else I have tried.

After updating statistics on the tables and their indexes using WITH FULLSCAN, the select c.ID query is running much faster:
select c.ID before update: https://www.brentozar.com/pastetheplan/?id=SkiYoOEUm
select * before update: https://www.brentozar.com/pastetheplan/?id=ryrvodEUX
select c.ID after update: https://www.brentozar.com/pastetheplan/?id=B1MRoO487
select * after update: https://www.brentozar.com/pastetheplan/?id=Hk7si_V8m

select * still outperforms select c.ID in terms of total duration and total reads (select * has about half the reads) but it does use more CPU.  Overall they are much much closer than before the update, however the plans still differ.

The same behavior is seen on 2016 running in 2014 Compatibility mode and on 2014.  What could explain the disparity between the two plans?  Could it be that the "correct" indexes have not been created?  Could statistics being slightly out of date cause this?

I tried moving the predicates up to the ON part of the join, in multiple ways, but the query plan is the same each time.

After Index Rebuilds

I rebuilt all of the indexes on the three tables involved in the query.  c.ID is still doing the most reads (over twice as many as *), but CPU usage is about half of the * version.  The c.ID version also spilled into tempdb on the sorting of ATable:
c.ID: https://www.brentozar.com/pastetheplan/?id=HyHIeDO87
*: https://www.brentozar.com/pastetheplan/?id=rJ4deDOIQ

I also tried forcing it to operate without parallelism, and that gave me the best performing query: https://www.brentozar.com/pastetheplan/?id=SJn9-vuLX

I notice the execution count of operators AFTER the big index seek that is doing the ordering only executed 1,000 times in the single-threaded version, but did significantly more in the Parallelized version, between 2,622 and 4,315 executions of various operators.

It's true that selecting more columns implies that SQL Server may need to work harder to get the requested results of the query. If the query optimizer was able to come up with the perfect query plan for both queries then it would be reasonable to expect the SELECT * query to run longer than the query that selects all columns from all tables. You have observed the opposite for your pair of queries. You need to be careful when comparing costs, but the slow query has a total estimated cost of 1090.08 optimizer units and the fast query has a total estimated cost of 6823.11 optimizer units. In this case, it could be said that the optimizer does a poor job with estimating total query costs. It did pick a different plan for your SELECT * query and it expected that plan to be more expensive, but that wasn't the case here. That type of mismatch can happen for many reasons and one of the most common causes is cardinality estimate problems. Operator costs are largely determined by cardinality estimates. If a cardinality estimate at a key point in a plan is inaccurate then the total cost of the plan may not reflect reality. This is a gross oversimplification but I hope that it will be helpful for understanding what's going on here.

Let's start by discussing why a SELECT * query might be more expensive than selecting a single column. The SELECT * query may turn some covering indexes into noncovering indexes, which might mean that the optimizer needs to do addition work to get all of the columns it needs or it may need to read from a larger index. SELECT * may also result in larger intermediate result sets which need to be processed during query execution. You can see this in action by looking at the estimated row sizes in both queries. In the fast query your row sizes range from 664 bytes to 3019 bytes. In the slow query your row sizes range from 19 to 36 bytes. Blocking operators such as sorts or hash builds will have higher costs for data with a larger row size because SQL Server knows it's more expensive to sort larger amounts of data or to turn it into a hash table.

Looking at the fast query, the optimizer estimates that it needs to do 2.4 million index seeks on Database1.Schema1.Object5.Index3. That is where most of the plan cost comes from. Yet the actual plan reveals that only 1332 index seeks were done on that operator. If you compare the actual to the estimated rows for the outer parts of those loop joins you'll see large differences. The optimizer thinks that many more index seeks will be needed to find the first 1000 rows needed for the query's results. That's why the query has a relatively high cost plan but finishes so quickly: the operator that was predicted to be the most expensive did less than 0.1% of its expected work.

Looking at the slow query, you get a plan with mostly hash joins (I believe the loop join is there just to deal with the local variable). Cardinality estimates definitely aren't perfect, but the only real estimate problem is right at the end with the sort. I suspect most of the time is spent on the scans of the tables with hundreds of millions of rows.

You may find it helpful to add query hints to both versions of the query to force the query plan associated with the other version. Query hints can be a good tool to figure out why the optimizer made some of its choices. If you add OPTION (RECOMPILE, HASH JOIN) to the SELECT * query I expect you'll see a similar query plan to the hash join query. I also expect that query costs will be much higher for the hash join plan because your row sizes are much bigger. So that could be why the hash join query wasn't chosen for the SELECT * query. If you add OPTION (LOOP JOIN) to the query that selects just one column I expect you'll see a query plan similar to the one for the SELECT * query. In this case, reducing the row size shouldn't have much of an impact on the overall query cost. You might skip the key lookups but that's a small percentage of the estimated cost.

In summary, I expect that the larger row sizes needed to satisfy the SELECT * query push the optimizer towards a loop join plan instead of a hash join plan. The loop join plan is costed higher than it should be due to cardinality estimate issues. Reducing the row sizes by selecting just one column greatly reduces the cost of a hash join plan but probably won't have much of an effect on the cost for a loop join plan, so you end up with the less efficient hash join plan. It's hard to say more than this for an anonymized plan.


Dropping a column generates an error: SELECT failed because the following SET options have incorrect settings: 'ANSI_NULLS, QUOTED_IDENTIFIER' When I run the following statement ALTER TABLE MyTable DROP COLUMN MyIdentityColumn I receive the following error message:

  SELECT failed because the following SET options have incorrect
  settings: 'ANSI_NULLS, QUOTED_IDENTIFIER'. Verify that SET options are
  correct for use with indexed views and/or indexes on computed columns
  and/or filtered indexes and/or query notifications and/or XML data
  type methods and/or spatial index operations.

Firstly I'm confused because I'm not running a select statement. The column is an identity column which might explain the reference to computed columns but there are no keys, constraints, triggers or indexes on this table (the table was created from another table with a select * from SourceTable into MyTable).

Secondly, what do I need to do to drop this column? Even deleting from the SSMS menu fails with the same message.

Insert profanity of choice.

The problem was caused by a database scoped trigger. Disabling / rewriting the trigger resolved the issue.


How to mirror the privileges of an user to another in Oracle Database? I need to grant all the privileges of a user (say 'AAA') to another user (say 'BBB').

Is there such an Oracle command (11.2.0.3.0)?

Get all privileges from AAA

 SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','AAA') FROM DUAL;

 SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','AAA') FROM DUAL;

 SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','AAA') FROM DUAL;

Change the DDL commands with the user 'BBB' and execute.


DB Design storing products sold in bulk, priced by vastly different units I am trying to design a database that stores products that are sold in varying different units. Some are priced by product, some by volume, some by length, some by surface area. Possibly some by weight. It is not a simple matter of storing the price in one specific unit, and then making the conversions (volume does not convert to surface area).

My current thought is having a product table, a price table, and a unit table. A product has one price, and a price has a per-unit integer cost and one unit. The unit table would contain an entry for every possible unit that products can be sold in, with a single column just being a string containing the name of the unit.

Is this the right approach? Is there a better way to handle such a scenario?

There are two ways to do this. The fast, and the "academic".

In pseudocode (change to fit your database)

The fast/high performance way:

CREATE TABLE Products (
  ID INT
  , Name VARCHAR
  , PricePerKg DECIMAL NULL
  , PricePerMeter DECIMAL NULL
  ... etc...
)

While this is not pure 5NF, it is FAST because all lookups of products can be done with a single index seek. You should add a constraint that ensures that at least one of the price/unit is NOT NULL (easy with COALESCE or NVP). It is very easy to write code that will access the above.

The academic way

CREATE TABLE Product (
  ID INT
  , Name VARCHAR
)

CREATE TABLE ProductPrice (
  ID_Product INT NOT NULL FOREIGN KEY
  , ID_Unit INT NOT NULL FOREIGN KEY
  , PricePerUnit DECIMAL NOT NULL
)
PRIMARY KEY (ID_Product, ID_Unit)

CREATE TABLE Unit (
  ID_Unit NOT NULL PRIMARY KEY
  , Name VARCHAR
)

The latter requires up to 2 joins to grab the information. But it is of course more flexible. To make sure the last solution does not create bad quality data, you will want a trigger to enforce that when the last ProductPrice is deleted, the product goes with it (or you set a flag).

If the number of prices per unit is known to be small, there is really no reason to pick the latter solution. There are no awards for being academically correct.


MySQL audit storing application user ID I am trying to set up auditing for changes to data in MySQL tables, for my Perl web application on Linux.  I've had a look at this:
http://go4expert.com/articles/database-transaction-auditing-mysql-t7252
and that looks good because it uses triggers to automatically create the audit records, but it looks as if it will record the MySQL user (e.g. USER() or CURRENT_USER()) in the audit table record, and since my application uses the same MySQL user for all web users, this won't identify the actual person who is making the change.  In my application, the ID of the person making the change is stored in a Perl variable (say $userid, which is an integer).

So, without me having to do extra coding wherever I make changes to tables (presumably by using triggers), how can I get MySQL to audit all changes to my tables, recording the $userid (Perl variable) as the user who made the change?

I've also had a quick several articles, including this one:
http://dev.mysql.com/doc/refman/5.0/en/account-activity-auditing.html
but haven't recognised a solution to my $userid problem yet.

I'm using mysql Ver 14.14 Distrib 5.5.40, for Linux (x86_64) using readline 5.1, and Perl: 5.10.1.

Thanks.

The point of database-level audits, such as can be done with triggers is to capture "who" made the change... but from the database's perspective, there is only one "who" -- the MySQL user the application uses.  (Also, your devops and DBA users, but primarily, it sounds like the application's changes you're interested in.)

I would suggest that the appropriate approach is for the application to log the app-user actions through deliberate insertions into "history" or "event" tables, referencing that Perl $userid, which presumably would also be the primary key in "user" table in the database... you'd be generating these logs from application code.

The role of trigger-based audits, then, is primarily one of sanity-checking the application: the audit shows this user's balance increased... did the application write a matching log entry in the appropriate "history" table?  The history table should then have the app-user id, and everything cross-references, if the app is correctly logging its actions.

(My personal preference is for the application to modify the database only through calls to stored procedures... in this way, the application can't fail to write these app-user-level history logs, because the stored procedures encapsulate every DML action into a single atomic database call... but I digress.)

There's a workhackaround available in MySQL, if you want to try to hybridize these two approaches... even if it's only to satisfy your curiosity and broaden your understanding of the nature of what you're dealing with.

User Defined Variables in MySQL are untyped variables, much like Perl scalars: they are whatever you put in them, strings, datetimes, floats, or blobs, and they're scoped to the individual MySQL connection.  Their value is independent of database transactions, but never visible from one connection to another.

$dbh->do(q[ SET @app_user_id = ? ], undef, $userid);

Until you disconnect from this connection, the variable @app_user_id (which I just made up, it's just a variable name, but the @ designates it as a user defined variable) will contain the value you stored there unless you set it to something else.  Because these variables have session scope, they also available to triggers, procedures, and stored functions (but not views or events)... so instead of using CURRENT_USER() in a trigger, use @app_user_id.

This, of course, requires that your application initialize this variable each time it begins running code that could update the database, and you have to hold on to that DBI handle, if you're working in a framework that can swap them out from under you.

So, not a perfect solution, but possibly the closest thing you'll find to accomplish what you want, using the setup you want to try.

http://dev.mysql.com/doc/refman/5.6/en/user-variables.html


Where clause in select does not work with Inequality of guid I want to query our CRM Db.
I have a backup of CRM Db and the existing one.
There is a custom entity,in both of them I have created.
Some of my custom entity records has deleted from the existing db.
I want to compare which records has deleted.
Number of existing records is 17 and the backup contains 19 records.
The custom entity is named new_services.
The backup database is named test2 and the existing one is MSCRM The table name is new_services and
When I query this equal ones :

select * from test2.dbo.new_services as t1,
 MSCRM.dbo.new_services as t2
where t1.new_service_Id = t2.new_service_Id;

And return correct records,
But when I try this :

select * from test2.dbo.new_services as t1,
 MSCRM.dbo.new_services as t2
where t1.new_service_Id != t2.new_service_Id;

It returns 308 records !
The new_service_id is the guid of the record.
Where is wrong?

Your logic is wrong ;-) Assume two tables:

CREATE TABLE T1 ( x int not null );
INSERT INTO T1 (x) VALUES (1),(2),(3),(4);

CREATE TABLE T2 ( x int not null );
INSERT INTO T2 (x) VALUES (1),(12),(13),(14);

Your first example will return 1 row:

SELECT *
FROM T1
JOIN T2
    ON T1.x = T2.x

1  1

whereas your second example will return 4*4 - 1 = 15 rows. If you think about it, in a set of 4x4=16 rows where the predicate is true for 1 row, how many rows if the predicate false for?

What you probably are looking for is MINUS or EXCEPT (not sure what it is called in sql-server):

SELECT * from test2.dbo.new_services
EXCEPT
SELECT * from MSCRM.dbo.new_services

or possibly the other way around (not sure which table is which)

Edit:
As pointed out in the comments MySQL (not sure of latest version), does not support the basic set operations INTERSECT and EXCEPT. EXCEPT is SET MINUS and can be implemented using NOT EXISTS or NOT IN:

SELECT t1.* FROM T
WHERE NOT EXISTS (
    SELECT 1 FROM T2 WHERE T1.... = T2... AND ...
);

Another option is using an OUTER JOIN.

Worth noting is that the above corresponds to EXCEPT ALL since any duplicates in T1 is preserved. For a true EXCEPT operator, DISTINCT should be used:

SELECT DISTINCT t1.* FROM T
WHERE NOT EXISTS (
    SELECT 1 FROM T2 WHERE T1.... = T2... AND ...
);

I will not delve into the shadowlands of null, and the consequences it has for logical equality between expressions

INTERSECT on the other hand corresponds closely to multiplication. We can mimic that with the IN or EXISTS predicate in the same manner as above.


Cant Uninstall SP3 from SQL Server 2008r2 I installed SP3 for sql server 2008r2 RTM release on windows server 2008r2. After testing i found some unknown issues in my application. Now i want to downgrade this database engine again into RTM release. I try control panel add/remove option but its remove all database instance. Please suggest

I try control panel add/remove option but its remove all database instance. Please suggest

That is not exactly the place from where you can uninstall SP, you have to go to add remove programs and then on top left you would see installed updates

Click on view installed updates and you would see all updates for your computer and programs installed on it. Locate SQL Server 2008 R2 Sp3, I have SQL Server 2014 SP2 and it shows like.

Right click on the Service pack and select uninstall.


Two substantially different but similar cases: Are they the same in terms of table cadinality? Imagine these different cases when designing a database:
Case 1:
One book (only one instance of a title physically) can be borrowed by many people, but only one person at once but many people during a time period.
Case 2:
Multiple instances of the same book physically (same title) can be borrowed by many people at the same time (obviously) in the one and same library only as before.

Would these two cases produce the same result when designing a relational schema?
If someone comes up with a better title for this question I will gratefully accept a change.

It's common to confuse a Thing (a physical book) with the Specification of that Thing (the idea of the book)

Say you have a Book Specification:

Title: Moby Dick
Format: Hard Cover
ISBN: 0840769008

Then you also have a Book Asset:

ISBN: 0840769008 (refers to Specification)
Serial Number: 1234
Condition: poor

People can borrow a Book Asset (I borrowed a particular copy).

People can reserve a Book Specification (I want to borrow that book but I don't care which copy I get)

So in your case, for one book, you'd have a specification for it, and one asset. For multiple, you'd have one specification and multiple assets

You'd design the same schema for both scenarios, by using Specification has many Assets, Asset belongs to Specification


Extend mysql 5.5 tablespace to another drive on windows I have greater than 300gb mysql database which is running out of disk space in D: drive on windows.( Presently the ibd data file is in D: drive which is running out of space). I have a F: drive which is having around 200 GB free space.

1)How can i extend the ibd data file to f: drive so that all new inserts go in f drive ?
Variable innodb_file_per_table is OFF currently.
Innodb_data_filepath : ibdtaxx:10M:autoextend

Please leave your suggestions on what will be the best course of action to follow in this situation.

mysql have "working dir" where all the schemas' dirs are stored. You have to:

stop your mysql
move the dir with desired schema to the new huge drive

mv D:\path\to\workingdir\dbschema F:\dbschema

make the link from the new location to the old working dir:

mklink \D F:\dbschema D:\path\to\workingdir\dbschema

start your mysql

Test that approach on some noncritical schema, indeed.


How to define custom path to MySQL socket file? I have following config

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql-socket/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
innodb_buffer_pool_size=64M
innodb_log_file_size=16M

[mysqld_safe]
log-error=/var/log/shared/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

So, there is a custom path to the socket file /var/lib/mysql-socket/mysql.sock.

MySQL server works but I cannot connect to it.

$ mysql -uroot
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

but needed file is exists.

$ ls /var/lib/mysql-socket/mysql.sock
/var/lib/mysql-socket/mysql.sock

When path to the socket file is /var/lib/mysql/mysql.sock it works fine.

Your error message says it's connecting to the wrong socket:

  ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

You can either specify that in command line:

mysql -S /var/lib/mysql-socket/mysql.sock ...

Or in the my.cnf file under the client section (this can also be in users home directory ~/.my.cnf):

[client]
socket=/var/lib/mysql-socket/mysql.sock


List of fallback values in SQL My question is what would be the best way to handle storing a list of values where the next serves as a fall-back. With a completely variable number of fall-backs (specified by our user).

I'm aware storing a delimited list in one SQL field is considered an anti-pattern (such as a list of parameters), however in this case we have a folder name, and if that folder name does not exist, we will fall back to another specified folder name, so on and so forth until all given folder names have been exhausted or until the first working folder name is found.

The order of the folder names tried is also important and must be preserved. Can't have it checking 99 before 8.

The simplest solution I can think of is to simply have a table folders with two columns: folder and priority. Simply

select folder
from folders
order by priority;

Then just try them in order and use the first folder that exists as a physical directory.

If folder in your current model is an attribute, then make the folders table a child table of that table; ie, if your current model is:

x (
   id,
   col1,
   col2,
   folder
)

then you should turn that into:

x (
    id,
    col1,
    col2
 )

 folders (
     x_id references x,
     folder,
     priority
  )

and the query becomes:

 select folder
 from folders
 where x_id = <id>
 order by priority;


Injecting connection context information using a persistent table I am working on an application that has several legacy modules that rely heavily on stored procedure (no ORM, so all fetches and data persistence is done through stored procedures).

Security for legacy modules relies on SUSER_NAME() to get current user and apply security rules.

I am migrating it to use an ORM (Entity Framework) and SQL connector will use a generic user to connect to the database (SQL Server), so I have to provide current username to many procedures.

In order to avoid changes in .NET code, I thought of "injecting" somehow current user in the context when a new connection is made:

CREATE TABLE dbo.ConnectionContextInfo
(
    ConnectionContextInfoId INT NOT NULL IDENTITY(1, 1) CONSTRAINT PK_ConnectionContextInfo PRIMARY KEY,
    Created DATETIME2 NOT NULL CONSTRAINT DF_ConnectionContextInfo DEFAULT(GETDATE()),
    SPID INT NOT NULL,
    AttributeName VARCHAR(32) NOT NULL,
    AttributeValue VARCHAR(250) NULL,
    CONSTRAINT UQ_ConnectionContextInfo_Info UNIQUE(SPID, AttributeName)
)
GO

When a connection is opened (or reused, as a connection pool is used), the following command is used:

exec sp_executesql N'
    DELETE FROM dbo.ConnectionContextInfo WHERE SPID = @@SPID AND AttributeName = @UsernameAttribute;
    INSERT INTO dbo.ConnectionContextInfo (SPID, AttributeName, AttributeValue) VALUES (@@SPID, @UsernameAttribute, @Username);
',N'@UsernameAttribute nvarchar(8),@Username nvarchar(16)',@UsernameAttribute=N'Username',@Username=N'domain\username'
go

(0 CPU, ~15 reads, <6 ms)

A scalar function allows to easily get current user:

alter FUNCTION dbo.getCurrentUser()
RETURNS VARCHAR(250)
AS
BEGIN
    DECLARE @ret VARCHAR(250) = (SELECT AttributeValue FROM ConnectionContextInfo where SPID = @@SPID AND AttributeName = 'Username')
    -- fallback to session current, if no data is found on current SPID (i.e. call outside of the actual application)
    RETURN ISNULL(@ret, SUSER_NAME())
END
GO

Are there any caveats (robustness, performance etc.) in this approach from a data tier perspective?

Thanks.

In terms of performance, you will incur the overhead of the DELETE and INSERT every time the connection is opened.  Alternatively, you could use the built-in connection CONTEXT_INFO for this purpose.  The example below stores the information in a fixed-length 48-byte structure.

EXEC sp_executesql N'
    DECLARE @ContextInfo binary(48);
    SET @ContextInfo = CAST(CAST(@UsernameAttribute AS nchar(8)) + CAST(@Username AS nchar(16)) AS binary(48));
',N'@UsernameAttribute nvarchar(8),@Username nvarchar(16)',@UsernameAttribute=N'Username',@Username=N'domain\username'
GO

CREATE FUNCTION dbo.getCurrentUser()
RETURNS VARCHAR(250)
AS
BEGIN
DECLARE
      @ContextInfo binary(48) = CONTEXT_INFO()
    , @Username nvarchar(16);
    SET @Username = RTRIM(CAST(SUBSTRING(@ContextInfo, 17, 32) AS nvarchar(16)));

    RETURN ISNULL(@Username, SUSER_NAME());

END
GO

Also, sp_set_session_context and SESSION_CONTEXT() are available in SQL Server 2016 and Azure SQL Database.  That's a much cleaner method if available to you.


SQL Server Database size unchanged after dropping File column I have a database with a table that has about 10 000 records. One of the columns in this table is a VARBINERY(MAX) that is used to store images. A backup of this database is about 1.2GB. It's size as shown in SQL Server Management Studio is about 3.5GB.

Working on the assumption that the images make up the bulk of this size, I dropped the column. This did not alter the database's size at all. The backup and database size is still the same. How is this possible?

When you drop a table, it will free the space in the database, but not back to the O.S. because your database has this space allocated. You need to shrink your database. but it's not a good Idea, because it's good to have free space in the database , so it can grow without the need to ask the o.s. to grow your file and then use it. this can cause fragmentation.


hstore for versioning fields in postgresql I have a postgresql database that will hold about 50 tables, each of them having about 15 fields, it would have at least 300.000 rows on each table.

In order to track the changes done on each field I am thinking on create a table defined by:

CREATE TABLE fieldhistory(tableid int, fieldid int, id bigint,value hstore);
CREATE INDEX fieldhistory_index ON fieldhistory(tableid, fieldid, id);

I would expect the table to grow and grow, and retrieve from fieldhistory data only by tableid, fieldid and id.

When the tables are modified I would add a new record with something like:

SELECT upserthistory(1,1,1,'first update','115435','3');
SELECT upserthistory(1,1,1,'second update','115435','3');

where upserthistory is defined by :

CREATE OR REPLACE FUNCTION upserthistory(key1 INT, key2 INT, key3 BIGINT, data TEXT, theuser TEXT, whend TEXT) RETURNS VOID AS
$$
BEGIN
    LOOP
        -- first try to update the key
        UPDATE fieldhistory SET value = value || ((whend||'.'||theuser) => data) WHERE tableid = key1 AND fieldid = key2 AND id = key3;
        IF found THEN
            RETURN;
        END IF;
        -- not there, so try to insert the key
        -- if someone else inserts the same key concurrently,
        -- we could get a unique-key failure
        BEGIN
            INSERT INTO fieldhistory(tableid,fieldid,id,value) VALUES (key1, key2, key3, hstore(array[whend||'.'||theuser,data]) );
            RETURN;
        EXCEPTION WHEN unique_violation THEN
            -- Do nothing, and loop to try the UPDATE again.
        END;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

Are there better approaches to accomplish this?  the table would grow max to 225 millions of rows, or would it be better to have 50 tables of 4.5 millions of rows at max each? noting that the actual trace of each field will go to the hstore.

I would personally prefer to have one history table for each "main" table.

There is a ready-made solution for this available for PostgreSQL which implements this history using triggers, see Log Table Changes in PostgreSQL with tablelog by Andreas Scherbaum.


Performance impact field-order I'm building a Go-based system, with MariaDB as the storage back-end. As the responses from MariaDB/MySQL are the slowest part of the whole application I was wondering a specific thing;

Does the field order in a query matter? Say I have this simpler query (but might be much bigger):

SELECT u.*, cu.FirstName, cu.Surname, uu.FirstName, uu.Surname
FROM UserContact u
LEFT JOIN User cu ON cu.ID = u.CreatedByID
LEFT JOIN User uu ON uu.ID = u.UpdatedByID
WHERE u.UserID = ?

Would u.* be faster before or after the custom fields? Or would the query optimiser tweak it correctly?

It might be trivial speed differentials, but I really care about speed, and as a SQL query easily has up to 1ms response time in my app's requests that normally are only a few microseconds themselves; every tweak in the usage of the SQL queries might be a big help in the overall performance of the system. I'm already buffering a lot of queried data with (in)validated cache, but sometimes in the back-end still need to run custom logic.

I can't test the performance in a Go benchmark at the moment, as in the latest macOS High Sierra the whole OS kernel panics on a filesystem crash  if the mysqld gets called too excessively.

The order of things in SELECT ... does not matter.

The main performance killers:

Having to fetch from disk.
Fetching more rows than needed.
Fetching more columns than needed.  That is, SELECTing * when you don't need all the columns.
Everything else is relatively insignificant -- expressions, ordering, etc.

In particular...

FROM UserContact u
LEFT JOIN User cu ON cu.ID = u.CreatedByID
LEFT JOIN User uu ON uu.ID = u.UpdatedByID
WHERE u.UserID = ?

Needs these indexes to tackle #2, above (unless they are already the PRIMARY KEYs)

UserContact: INDEX(UserID)
User:  INDEX(ID)

Please provide SHOW CREATE TABLE if you need to discuss further.


Point in time recovery Oracle 12c with TDPO I have to do a Point in Recovery to 2017-09-12:13:30:00. I'm running in to a TDPO/TSM error ANS1302E (RC2). List backup shows that the backup and archivelogs are available for restore. Anyone had that issue before?

rman target /
Startup mount
RUN {
   allocate channel t0 type 'sbt_tape' parms
   'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
   set until time "to_date('2017-09-12:13:30:00', 'yyyy-mm-dd:hh24:mi:ss')";

   restore database;
   recover database;

   release channel t0;

    }
connected to target database (not started)

Oracle instance started
database mounted

Total System Global Area    2399141888 bytes

Fixed Size                     8623496 bytes
Variable Size                671091320 bytes
Database Buffers            1711276032 bytes
Redo Buffers                   8151040 bytes

RMAN>
RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10>
allocated channel: t0
channel t0: SID=135 device type=SBT_TAPE
channel t0: Data Protection for Oracle: version 8.1.0.0

executing command: SET until clause

Starting restore at 12-SEP-17

channel t0: starting datafile backup set restore
channel t0: specifying datafile(s) to restore from backup set
channel t0: restoring datafile 00004 to /opt/oracle/oradata/datafile/DBOLR2T/use
channel t0: reading from backup piece DB_954446866_1042_1
channel t0: ORA-19870: Fehler beim Zuruckschreiben von Backup Piece DB_954446866
ORA-19507: Fehler beim Abrufen der sequentiellen Datei, Handle="DB_954446866_104
ORA-27029: skgfrtrv: sbtrestore hat Fehler zuruckgegeben
ORA-19511: Kein RMAN-, sondern Media Manager- oder herstellerspezifischer Fehler
ANS1302E (RC2)    No objects on server match query

As the error message already states, this is not an Oracle issue. The backup piece simply can not be found on the TSM server where Oracle searches - based on the tdpo configuration.

Maybe it was created with a different tdpo configuration under a different node name or in another filespace. Maybe it is simply not there on the TSM server.

You can query the contents of TSM for this tdpo config with:

tdposync query -tdpo_optfile=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt

Most likely you will not find DB_954446866_1042_1 in the list, and that is the problem. Ask the TSM administrators to search for DB_954446866_1042_1 in their catalog. If they find it in a different filespace, or registered under a different nodename, you can restore it by collaborating with them and using the correct tdpo configuration. If they can not find it, you are out of luck as that backup simply does not exist on TSM.


Is this Normal SSIS Foreach Loop Container behavior I have a Foreach Loop Container that loops through files in a folder. I thought the Loop Container would only look at the files 1 time but apparently it will start back at the top and pick up a file that it had already seen again.

I have a script in my loop container that will process the file if it meets certain criteria. If it does not then it will simply skip the file.

The files that get processed are moved to another folder. The problem I am having is that once it goes through all the files it starts back up at the top of the list of files so it will just indefinitely continue to loop if there was a file that was skipped and not moved out to a different folder.

I know I can make it so that instead of skipping the file my process moves the file to another folder so that the foreach loop container can finish looping and the package can complete but I just thought I would double check first to see if there was a way of just having the loop container loop thru the files once so that if a file was already identified it would not be detected again by the loop container.

Based on clarification from the comments, the work flow was

C:\SourceFolder\File1.csv -> C:\SourceFolder\Archive\File1.csv

The ForEach File Enumerator has a Traverse Subfolder option which allows the enumerator to look beyond the top level of a folder hierarchy.

The specific mechanics of how the enumerator appears to be implemented is that it will identify all the elements within a folder. Process all files matching the FileSpec, *.csv is this case and then visit each directory applying the same pattern.

What you're observing was the original file was processed and correctly moved to the archive location. The archive location was then processed resulting in a double processed file.


For a MySQL query, how do you determine physical and logical I/O? I'm used to having a showplan in Microsoft SQL and have gotten lazy with using tools to tune MySQL queries. How can I determine physical/logical I/O for a query in MySQL? (EXPLAIN shows the index selection but I want more information from the MySQL server, if possible).

I recommend using Percona Server, for the slow query log enhancements.  I answered a similar thread to this on Stack Overflow:

https://stackoverflow.com/questions/3393206/what-is-the-equivalent-query-in-mysql/3443944#3443944

Take a look at this in particular (from http://www.percona.com/docs/wiki/percona-server:features:slow_extended?redirect=1#changes_to_the_log_format):

# User@Host: mailboxer[mailboxer] @  [192.168.10.165]
# Thread_id: 11167745  Schema: board
# QC_Hit: No  Full_scan: No  Full_join: No  Tmp_table: Yes  Disk_tmp_table: No
# Filesort: Yes  Disk_filesort: No  Merge_passes: 0
# Query_time: 0.000659  Lock_time: 0.000070  Rows_sent: 0  Rows_examined: 30  Rows_affected: 0  Rows_read: 30
#   InnoDB_IO_r_ops: 1  InnoDB_IO_r_bytes: 16384  InnoDB_IO_r_wait: 0.028487
#   InnoDB_rec_lock_wait: 0.000000  InnoDB_queue_wait: 0.000000
#   InnoDB_pages_distinct: 5
select count(distinct author_id) from art87.article87 force index (forum_id) where forum_id = 240215 and thread_id = '710575'


Alerting on Database Mirroring Events I am looking for the actions one must take to cause Threshold Alerts to occur for a single mirrored database on an instance of SQL Server 2008 containing multiple mirrored databases.

FYI: This is already posted on ServerFault.com and submitted as a bug on Microsoft Connect.

Joe Sack's answer on ServerFault is as good as it gets:

https://serverfault.com/questions/251551/sql-server-2008-mirroring-threshold-alerts-at-database-level/251989#251989

For some background, Joe runs the Microsoft Certified Master of SQL Server program.  He knows his stuff.  (I can vouch for him personally.)


Comparing binary 0x and 0x00 turns out to be equal on SQL Server It seems that SQL Server considers 0x and 0x00 equal values:

SELECT CASE WHEN 0x = 0x00 THEN 1 ELSE 0 END

This outputs 1.

How can I get true binary bit-for-bit comparison behavior? Also, what are the exact rules under which two (var)binary values are considered equal?

Also note the following behavior:

--prints just one of the values
SELECT DISTINCT [Data]
FROM (VALUES (0x), (0x00), (0x0000)) x([Data])

--prints the obvious length values 1, 2 and 3
SELECT DATALENGTH([Data]) AS [DATALENGTH], LEN([Data]) AS [LEN]
FROM (VALUES (0x), (0x00), (0x0000)) x([Data])

Background of the question is that I'm trying to deduplicate binary data. I need to GROUP BY binary data, not just compare two values. I'm glad I even noticed this problem.

Note, that HASHBYTES does not support LOBs. I'd also like to find a simpler solution.

I couldn't find this comparison behaviour specified anywhere in BOL.

But the Connect Item Invalid equality comparison for varbinary data with right padded zeros states that

  Basically, the standard leaves it up to implementation to treat
  strings that differ only by [trailing] 00 as equal or less. We treat it as equal.

The Connect Item also states that the presence of trailing zeroes is the only case in which SQL Server differs from byte-by-byte comparison behavior.

In order to distinguish between two binary values in SQL Server that differ only by trailing 0x00 characters you can also add DATALENGTH into the comparison as indicated in your question.

The reason for preferring DATALENGTH rather than LEN generally here is because the latter gives an implicit cast to varchar and then you get the problem with trailing spaces.

+-------------+--------------------+
| LEN(0x2020) | DATALENGTH(0x2020) |
+-------------+--------------------+
|           0 |                  2 |
+-------------+--------------------+

Though either would work in your use case.