How do we restore database configured with Always Encrypted between environments and enable web app to encrypt/decrypt data?

See this description of how Always Encrypted works. Basically, SQL Server does not have access to the keys required to decrypt data into plain text. This work is performed by the client driver and in your scenario, the client driver will go looking for the certificate to decrypt the data for the web application but on failing to find the correct certificate, the data will not be decrypted.

This won't cause a failure in itself, as you can see from this example if you don't have column encryption support enabled in the driver you still get results back, but when that data isn't decrypted the client driver simply returns the encrypted binary value.

It is likely, however, that your web application is relying on unencrypted data to do more than simply present a result set, and for this, you will need the certificate. If the client refuses to allow the certificate to be shared in lower environments, then you cannot use restored database copies from production in your non-production environments.


Error 1041 "Out Of Memory" after pointing tmpdir to new disk space

If innodb_tmpdir being set to the new location is causing an instant complaint from the server, there's a very good chance that apparmor is preventing MySQL from accessing the location or that the system is reacting to the 777 permissions, which are often rejected by a lot of server applications for obvious reasons. You will need to check your error.log file to see whether the innodb_tmpdir location is being used.

apparmor fixes are quite easy to fix from Oracle a blog a long time ago:

If you want to relocate the data directory in MySQL (in this example, to the /data/ directory), and AppArmor is not letting you, add the following two lines to the bottom of /etc/apparmor.d/local/usr.sbin.mysqld:
/data/ r,
/data/** rwk,

...and then reload the AppArmor profiles:
# service apparmor reload


Does SQL Server optimize two ">" operations on same column

I tested it on a table in my database that has a date column.
https://www.brentozar.com/pastetheplan/?id=r1V9bJ-av

Looking at the query plan, in SQL Server 2019 Dev edition, what SQL did was, it scanned the current timestamp and the date specified and created a date range. Then it scanned my table to find the rows in that range (none).
At first I thought it was searching for a range between the two dates.

but when looking at the logical reads, I think what it did was search between "CAST(CURRENT_TIMESTAMP AS DATE)" and "NOW" which is a few hours. So I think SQL did exactly what you expect it to do, which is optimize the ">" operation on the same column.
 Scan count 1, logical reads 4, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Furthermore, if I change the query to do an "OR" instead of an "AND", I get 84 logical reads instead of 4 and many more rows returned on the index seek. Which indicates to me that SQL Server 2019 is smart enough to optimize the query with two ">" on the same column.
https://www.brentozar.com/pastetheplan/?id=BkbGf1Wpw
Now that might not apply all the time depending on the complexity of your query or if you have many more conditions in your where clause. Testing it in your environment is the key.


Why do we not store names in a separate table?

No, it is absolutely not proper normalization to move a name out to another table.

Normalization reduces redundancy based on functional dependencies, not based on coincidental repetition of data values.

Let's say there are 100 Joe Smiths in your database.  If you move the name "Joe Smith" out to another table and have 100 records point at this name, what happens when one of the Joe Smiths decides to change his name to "Bob Smith"?  Unless they all change their names in unison, you can't work with the data that way.


InnoDB Buffer pool not showing as much I configured in MariaDB 10.5

It is unsafe to raise values arbitrarily.  It often leads to swapping, which makes performance worse.
max_allowed_packet=1G  -- dangerously high; wastes RAM; set to 1% of RAM
innodb_buffer_pool_size=3G   --Since you have only 4GB, lower to 2G
innodb_buffer_pool_instances=6  -- only 1 per GB, so: 2
innodb_log_files_in_group=1   -- Use the standard of 2
innodb_log_write_ahead_size=32M  -- waste of RAM; leave at default (8K)
innodb_write_io_threads=128  -- too high
innodb_io_capacity=10000   -- do you have a super-duper SSD?  Else too high
join_buffer_size=128M    -- lower to 1% of RAM
read_rnd_buffer_size=128M  -- lower to 1% of RAM
max_connections = 500    -- the default will save RAM

As for "Buffer pool size   193296" -- That is pages.  Multiply by 16K to get about 3G.  So it is not "small".


SQL Server 2014 Express has reached database size limit. What is the quickest solution?

My SQL Server 2014 Express database has reached database size limit.

Correct solution would be to upgrade it to version which does not have limitation on data file like developer, Standard and Enterprise depending on your requirement. If there is space in your database try shrinking it and see if that helps. But this is temporary solution in no matter of time you would again face space issue.

  Is Express software the same as the full version, with just some limitations, or is it a complete different software?

No its is not same as full version there are many differences and all is documented in Features Supported by the Editions of SQL Server 2014.

  Can I just go to Microsoft store online, order a key for full version, and type it in to the current Express to activate full features, or do I need to do full uninstall of Express, reinstall of full version, copy database and such?

You can perform an in place upgrade,you don't need to uninstall the express edition. Please see Supported Version and Editon Upgrade. You need license key mostly its embedded in new installation setup you get.


cannot generate sspi - None of Solution worked!

Having no SPN does not cause this error. Having an invalid SPN does. It's not enough to make sure an SPN exists, you have to make sure it is owned by the SQL Server service account and that it is configured correctly.

Follow the advice I blogged here: http://sqlsoldier.net/wp/sqlserver/advanced-troubleshooting-week-at-sql-university-lesson-1


Create Copy of all rows where foreign key matches specified and then replace foreign key

Modelling algorythm.

Assume we have Main table with the fields ID and Name. Assume we have table Details referenced to Main table via ID_Main field (FK) and having Detail field.

Assume we selected the record with some ID from table Main with some name. We altered this name, and now we want to create new records pack - with new Name and with the copy of Detail value from source record.

First, we must ensure that new name value is not present in Main table (we do not like full duplicates, is it?). So we query

SELECT COUNT(*) FROM Main WHERE Name = 'New Name Value'

and check that its result is zero ('0'). If not, this value is already present, and we tell it to operator.

If the value returned is zero, we can store data. We insert it with the query

INSERT INTO Main (Name) VALUES ('New Name Value')

The record is inserted, and some ID is assumed to it. We may:

1) obtain it by simple query

SELECT id FROM Main WHERE Name = 'New Name Value'

and then use this value in the query which adds data to the table Details referenced to this record

INSERT INTO Details (Main_ID, Detail) VALUES ('obtained ID value', 'New Detail value')

2) do not obtain ID separately, doing this in a INSERT query

INSERT INTO Details (Main_ID, Detail)
SELECT id, 'New Detail value' FROM Main WHERE Name = 'New Name Value'

PS. For SQL Server you can obtain ID of last record using select scope_identity(), select @@identity or select ident_curent(‘tablename’) dependent by what you need.


Sizing the SQL Agent Job Activity Monitor screen

Can't be done.

This is the additional 16 characters required to post an answer.


Restoring XtraBackup from MySQL 5.6 to MySQL 5.7

Xtrabackup is a physical backup tool, and is meant to backup and restore to the same MySQL (or MariaDB) version. It's not designed to work with migrations. It might work, it might not.

So if possible, if you want to restore the backup to a new instance, I think the best approach is to restore it to another MySQL 5.6 instance, and then upgrade that instance to 5.7 afterwards.


1: Data Guard between x86 Linux and SPARC Solaris will not work (unless it is x86 Solaris), see:
Data Guard Support for Heterogeneous Primary and Physical Standbys in Same Data Guard Configuration (Doc ID 413484.1)
2: Same for RMAN, due to the difference in endianness of these hardware platforms. Even if the on-premise system is x86 Solaris, restoring a backup on different platform with same endianness is not supported (restore actually works, but recovery does not). Oracle allows building the standby databases with RMAN active duplication instead of backups in such configuration.
3: That will work, you should be able to manage less than2 TB in 2 days with this.
Q1: Most likely Data Pump, unless your on-premise is x86 Solaris.
Q2: It can reconnect and continue where it was interrupted.


Select records based on earliest date in a linked record

Good question. First you need the full date rather than just a year to pick the earliest in year. So I'm assuming event_date in Venues

SELECT title, name, MIN(event_date)
FROM Stories s
JOIN Story_in_Venue sv ON s.story_id = sv.story_id
JOIN Venues ON v.venue_id = sv.venue_id
WHERE genre="Mystery" and event_date >= "1960-01-01" AND event_date < "1961-01-01"
GROUP BY s.story_id

The event_date and genre are the searched columns so they should be indexes/


Find missing timestamps grouped by key: finding gaps in my data

One approach would be to count the hours for each key and substract that from total hours in given period.

WITH period as (
  SELECT COUNT(*) as total_hours
  FROM GENERATE_SERIES('2018-03-15', '2018-03-16', INTERVAL '1 hour') gs
),
key_counts as (
  SELECT key, COUNT(*) as hours
  FROM (
    SELECT distinct key, date_trunc('hour', timestamp)
    FROM test_data
    --apply period limit here
  ) kq
  GROUP BY KEY
)

SELECT key, total_hours-hours as missing_hours
FROM
  period,
  key_counts

dbfiddle


log_statement_stats: What is the significance of values in enclosed in brackets?

It seems that the values in the brackets refer to the raw usage/stats.  Note that the values outside the brackets are adjusted with reference to the last reset (See ResetUsage() above ShowUsage() in the link I shared).  The values inside the brackets are the values without that adjustment.


Display child records in single column by comma separted with parent records

Setup tables and data:

create table T_OPT_PARENT
(OPT       char(2)
,OPT_DESC  varchar2(10)
,STATUS    char(1));

insert into T_OPT_PARENT
select 'XY','XY_DESC','A' from dual union all
select 'YZ','YZ_DESC','A' from dual union all
select 'ZZ','ZZ_DESC','A' from dual;

create table T_OPT_CHILD
(OPT       char(2)
,CODE      varchar2(10));

insert into T_OPT_CHILD
select 'XY','ONE_CODE'   from dual union all
select 'XY','TWO_CODE'   from dual union all
select 'XY','THREE_CODE' from dual union all
select 'ZZ','ONE_CODE'   from dual union all
select 'ZZ','TWO_CODE'   from dual;

Here's one solution based on a left join and the listagg() function:

select p.OPT,
       p.OPT_DESC,
       listagg(c.CODE, ', ') within group (order by ROWNUM) "CODE",
       p.STATUS

from T_OPT_PARENT p

left
join T_OPT_CHILD c
on   c.OPT = p.OPT

group by p.OPT,
         p.OPT_DESC,
         p.STATUS

order by 1;

 OPT | OPT_DESC | CODE                           | STATUS
 --- | -------- | ------------------------------ | ------
 XY  | XY_DESC  | ONE_CODE, TWO_CODE, THREE_CODE | A
 YZ  | YZ_DESC  | null                           | A
 ZZ  | ZZ_DESC  | ONE_CODE, TWO_CODE             | A

NOTE: I'm able to sort XY's CODE as desired using ROWNUM only because I inserted the records into the T_OPT_CHILD table in the same order as the desired sort order.  The use of ROWNUM may not work for your real world data so you'll likely need to modify the order by ROWNUM clause accordingly.

Here's a dbfiddle


Help my database isn't performing fast enough! 100M Merge with 6M need < 1 hour!

If your table has a table with more than 1000 columns, it cannot be converted to InnoDB. In that case, run this query

SELECT CEILING(SUM(index_length)/POWER(1024,2)) num
FROM information_schema.tables WHERE engine='MyISAM';

This will give you the correct size for key_buffer_size in MB.

Since you are doing an UPSERT, you should set concurrent_insert to 2 to make INSERTs go faster. You may want to consider changing the table's row format to Fixed. I wrote about why to do both in StackOverflow. In essence, if you make the table's row format Fixed, all table rows are the same size. Thus, INSERTs and UPDATEs would operate on the exact same length of data. Management of row access is far more reasonable.

Since MyISAM only caches indexes (in the key buffer), all data must be read from disk. anything you can do to getting better RAID performance (as asked by @TomTom) would help your cause as well.


How to grant all privileges on all tables in a schema to a user in IBM DB2?

If you want access to all data (ie, all tables in all schemas), you would need to grant dataaccess.

db2 grant dataaccess on database to user winuser1

If you only want winuser1 to access just the 100 tables in the schema you are referring to, then unfortunately, there is no easy way, you would need to grant SELECT on each table. That being said, it can be accomplished through scripting.

You could do the following

db2 -tnx "select distinct 'GRANT ALL ON TABLE '||
    '\"'||rtrim(tabschema)||'\".\"'||rtrim(tabname)||'\" TO USER winuser1;'
    from syscat.tables
    where tabschema = 'myschema' "  >> grants.sql

db2 -tvf grants.sql

This makes use of querying the system catalogs to dynamically generate a script to permission things. This is a lot of how we permission for users we don't want to give dataaccess to.

Here is a good page of the authorities for DB2.


PostgreSQL: Allow connection by host name and not IP?

Quote from the manual

  address

  Specifies the client machine address(es) that this record matches. This field can contain either a host name, an IP address range, or ...

(emphasis mine)

So putting a hostname into pg_hba.conf is supported.

During the initial connection request, Postgres only sees the IP address (as part of the TCP protocol) and needs to do a lookup to resolve the IP address to a hostname. This only works if the client and the Postgres server are in the same network and are using the same DNS server(s).

My assumption is, that your VPN solution is preventing that hostname lookup to work properly.

If you always get your IP addresses from the same range, you could configure that range in pg_hba.conf rather than a single address (or hostname)


Is it possible to have something like Union-FS for Oracle databases?

You might be able to construct something like this using database links and views, but performance of those views in the development environment would likely be horrendous. That said, in general it is considered an extremely poor security practice to have production data be visible to non-production application instances, or to share any infrastructure (servers, network, storage) with them. In most computing environments it is specifically prohibited by best practice, if not by law, depending on the type of data being stored.

In most of the places I have worked, according to the rules developers were not even allowed read-access to production data for any reason; no links between dev and production were allowed; and no copies of production data could be placed in dev environments. Any exceptions required senior management and security approval from multiple organizations and generally meant that dev environments wound up secured to the same level as production.


How to deny drop, delete permission for a table?

To CREATE a table, stored procedure or function in SQL Server you need to GRANT the following

GRANT CREATE TABLE, CREATE PROCEDURE, CREATE FUNCTION, CREATE VIEW TO [someuser/somerole]

to a user or role. I added VIEW in since you probably wanted that too.

You also need to

GRANT ALTER, EXECUTE, VIEW DEFINITION ON SCHEMA::schemaname TO [someuser/somerole]

to that user so that they can modify or use what they created.

With ALTER however that user would have the ability to DROP and ALTER anything in the schema which is not what you want based on your question.

ALTER PERMISSION SQL SERVER 2008

  ALTER Confers the ability to change the properties, except ownership,
  of a particular securable. When granted on a scope, ALTER also bestows
  the ability to alter, create, or drop any securable that is contained
  within that scope. For example, ALTER permission on a schema includes
  the ability to create, alter, and drop objects from the schema.

You could restrict that user to their own schema but that would probably create a lot of work for you since any application using your database would have to be aware of that new schema.

Outside of permissions, another way I know of to prevent dropping objects from a database is with a database trigger. Something like this: How to deny drop permission for a table?

CREATE TRIGGER NO_DROP_TABLE
ON DATABASE
FOR DROP_TABLE
AS
PRINT 'Dropping tables are not allowed'
ROLLBACK


Please try the following possible solution:
1. Go to the following directory in c:\ drive:

C:\Users\<your_user_id>\AppData\Roaming\

There you will find folder(s) named SQL Developerandsqldeveloper
Rename those folders (whose name is like SQL developer and sqldeveloper) to something like SQL Developer1 and sqldeveloper1 respectively or as you like.
Restart SQL Developer application.

Note: After performing the above operations  all your Connections data will get erased. So, you might need to first export all your connections into an .xml file.

Hope this solves your problem !!


Separation of Duties - Can a DBA patch or Upgrade a SQL Server 2008 R2 DB without TDE Encryption keys?

TDE only encrypts the data at rest.  If they have the access to connect to the server and query the database then they can get to the data.

But I don't think the person running the patch (CU or Service Pack) needs permissions to the database proper, just permissions on the server.

The only way to separate the job functions as you seem to want would be to use the application to encrypt the data, that way it's actually encrypted in the database.  Then even if someone has select rights to the database they can't read the data without the key.  But that adds a lot of overhead and makes a lot of things harder.


and if you are calculating monthly growth, why aren't you calculating the number of trips in eahc month :
SELECT
    EXTRACT(YEAR FROM DATE (stoptime)) AS YEAR
    , EXTRACT(MONTH FROM DATE (stoptime)) AS MONTH
    , COUNT(bikeid) AS Trips
    , ROUND(100* (COUNT(bikeid)- LAG(COUNT(bikeid),1,0) OVER (PARTITION BY 'Year','Month' ORDER BY 'Year', 'Month' desc))
                 /lag(COUNT(bikeid),1) OVER (PARTITION BY 'Year','Month' ORDER BY 'Year', 'Month' desc)) ||'%' AS growth
FROM `bigquery-public-data.new_york_citibike.citibike_trips`
WHERE end_station_name LIKE '%University%'
GROUP BY Year, Month
ORDER BY Year DESC, Month DESC


Optimize MySQL Configuration - MySQL crash and after restart works

Suggestions for my.cnf/ini [wampmysqld] section

key_buffer=64M       SHOULD BE
key_buffer_size=64M  # from key_buffer  to be recognized
expire_logs_days=10  # from default 0, to provide limited historical logs
innodb_buffer_pool_instances=8  # to minimize mutex contention
innodb_buffer_pool_dump_at_shutdown=ON  # prepare for WARM on restart
innodb_buffer_pool_load_at_startup=ON  # pool will be warmed up
innodb_buffer_pool_dump_pct=75  # from 25 you will likely need the data
innodb_page_cleaners=64  # 4 to auto-limit = innodb_buffer_pool_instances
innodb_print_all_deadlocks=ON  # chk error log-be proactive to prevent
innodb_purge_threads=4  # to keep up with activity
log_warnings=2  # to have record of attempted connections in error log
#max_connections=4000  # lead with # to have default of 151 until stable
#sort_buffer_size=512K  # lead with # for default of 2M
#max_allowed_packet=1000M  # lead with # for default of 1M

if you need MAP greater than 1M, it is likely for data loading infile
in your SESSION,
SET @max_allowed_packet=nnnnnnnnn  bytes max in input file  1G is THE LIMIT

this technique will HELP avoid OOM due to a new connection.


In general, you have a heavily de-normalized table, and a bit of normalizing would do some good.

Since you appear to be new to database design, I understand you may not really be aware of what normalization is, so we'll keep it simple and say that for a normalized structure, you want each piece of data to exist in one place only, so that it is not copied repeatedly in many locations.

Of course, you'll find that the Wikipedia page on database normalization is a good start.

Let's look at your specific case:

1) You have the individual's name directly in the row data.  This is fine if a person is referenced once and only once, but what if the a person is part of the data for other tables?  Do you want to copy their name every time?  What if they get married or for some other reason they change their name?  Now you'll have to track down every instance of their name.  What if two people have the exact same name?  How do you discern between them?

In general having a table with unique person ids as

CREATE TABLE people(
  person_id int,
  first_name text,
  last_name text,
  etc...
);

would be a good idea.  Then simply place the person_id into any other relevant table.

2) So, putting someone's age into a table is a pretty bad idea.  Doing it this way, every day you need to go through the table, see if it is someone's birthday, and update any corresponding entry.

Why bother with that when you can simply store their birthday once, and all relevant information is encapsulated in that one, never changing piece of data? Futher, since it is definitively attached to the person, add their birthdate as a column entry in the previously mentioned people table.

3) You can already see where I'm going with this: this data also belongs in the people table.

4) Now we're on to your original question.  Yes, I believe you should have a separate table which lists the different departments.

CREATE TABLE depts(
  dept_id int,
  name text,
  other_info text,
  etc...
);

Lastly, joining people to departments can simply be done with a table having two integer columns:

CREATE TABLE employees(
  person_id int,
  dept_id int
);

Hope this helps!

P.S.: I've made it sound like normalization is always the right answer. In actuality, it is often the right answer, but not always.  They key is, learn to normalize an why you should, so that you can then make key decisions about why you might choose to intentionally de-normalize your data.


SSAS and Visual Studio Optimization Wizard does not work

The answer isn't obvious.

You need BIDS Helper installed to your visual studio.

There are four buttons in the PARTITIONS tab one of which is the "Edit Aggregations" button.  If it isn't there, you dont have the latest BIDS helper.

An aggregation Manager window will appear, with a treeview of your measures structure.  Drill down to the Aggregations Design folder that appears in that tree, and right click it, or any of its children.  Click the "Add Aggregations from Query Log" menu item, and you will be given a window where you can even edit the SQL that selects your Query log item.

This will allow you to achieve exactly the same thing that the Aggregations tab currently isn't.

For the record, the Aggregations tab issue remains, but is irrelevant as this method does everything we need.


Is it possible to execute queries in random order?

From your application layer, stamp each query with a timestamp and batch ID. Instead of executing the queries, store them in a temporary table with the format

sql_test | timestamp | batchID

When you know all queries from a batch have been received, kick off a stored proc that retrieves all queries from the batch, orders by timestamp asc and executes them

You'll probably want to add more logic about establishing dependencies between batches and how to deal with failed error,etc


How do I stop, tune and restart a MariaDB Galera cluster?

So I will chime in the answer here now that I have done this. So after tuning the mysqld settings I did a plain old service mysql restart and it worked perfectly. As long as you have EDIT please see above, grow in odds with at least 3 running running it does not seem to care at a restart.


It turns out my problem is due to the VIEW containing aggregate functions.

What looks like a cached query plan is really the planner (by design) not pushing a WHERE clause applied outside the VIEW into the VIEW.

Problem explained.


Select into specific array positions with array_agg()?

This is the query I was able to come up (with some brute-force):

SELECT b.id, array_agg(b.stock) FROM (
  SELECT a.*, COALESCE(i_s.stock, 0) as stock FROM (
    SELECT id, generate_series(1, 2) as n FROM items
  ) as a
  LEFT OUTER JOIN item_stock i_s ON a.id = i_s.item_id AND a.n = i_s.shop_id
) as b GROUP by b.id;


Error when using DBCA to create database Oracle XE 18c

Sorry it took me a while to get back to this. I see at least two major issues in the trace.log:
First, it appears your environment is misconfigured:

[main] [ 2021-02-03 15:09:37.030 GMT+03:00 ] [StepContext.:429]
Error initializing Network Configuration: Environmental error
detected: Oracle Home is set to "E:\oracle\client_19_3" in the
environment is different from Oracle Net Configuration Assistant
installed Oracle Home.

and second, it appears that you do not have enough physical memory on your host to run a database:

Status     : VERIFICATION_FAILED
Name       : dexp
Type       : Node
Has Results: Yes
Exp. value : 3,1543GB (3307520.0KB)
Act. value : 1,1372GB (1192476.0KB)
Info type  : StorageSize
Info type  : StorageSize
Errors  :
PRVF-7562 : Sufficient available memory is not available on node "dexp" [Required available memory = 3230.0 MB]


Differences between SQL Server 2005 and SQL Server 2008 deadlock handling

"Sensitivity to deadlocks" is not version-specific.  It is related to concurrency and contention.  Please see this BOL reference on more information regarding deadlocking.

There are certain things that influence deadlocking, such as transaction isolation levels, and lock escalation.  These are configurable settings that impact if and when deadlocking can/will happen in certain situations.

It will take a bit of investigation on your part to find out why you're seeing more deadlocking happening.  But to answer your question, it isn't specific to the version of SQL Server.

As for the deadlock process, when SQL Server runs into a deadlock it will choose a victim so that the unresolvable concurrency situation can complete.


Error querying postgresql table created in pgAdmin

PostgreSQL implicitly converts all the SQL that you enter into lower case, unless you wrap things in double-quotes in which case you have to get the case exactly right.
Wherever possible, you should avoid needing quotes or other wrapper characters, i.e. don't use special characters in identifier names.  It will only come back to bite you.
create table public.test3
( pid integer
, name character varying(20)
, primary key (pid)
)


troubleshooting mysqlimport very large text file

The problem with this starts with the my.cnf

The table is using the MyISAM Storage Engine but you have no MyISAM specific options in my.cnf

You are getting the "repair by keycache" because the MyISAM Key Cache is way too small. The default key_buffer_size in MySQL 5.7 is 8MB. Also note that you could have set up the MyISAM bulk insert buffer, whose default is also 8MB.

If you had added this to the my.cnf

[mysqld]
key_buffer_size = 1G (or 2G)
bulk_insert_buffer_size = 256M (or 512M)

and restarted mysqld, you could have bulk loaded without changing the data files all that much.


Connect by prior - multiple fields and query efficiency

Here are some additional questions that would help me answer this question better: (1) how often is this query executed; (2) does the result set have to be returned as a single row per ID; (3) in the actual environment can one item have more than 3 levels of depth?

I am going to assume that data sample is not representative of your actual environment and that the depth of this hierarchy may go deeper than 3 levels.  In order for a single-row query to be successful in each case there must be a lot of potentially redundant SQL to account for all possible levels of depth.  If your query accounts for an item having up to 5 levels of depth, but only one of your items has that many levels then you are putting unnecessary load on the database and potentially face performance delays with long running queries.  A multi-row query could get much better performance, but that probably isn’t how you need the data returned (like below).

SELECT  H.*
FROM    HIERARCHY_TBL H
START WITH H.EQUIP_ID = '1'
CONNECT BY PRIOR H.PARENT_EQUIP_ID = H.EQUIP_ID

If this is an ad hoc query occurring infrequently then dealing with performance delay may be an acceptable option so you could stick with what you have.  If this query is executed very frequently then performance delay is going to be unacceptable by the users.

As far as suggestions, if this SQL is being executed from within an application then you could look into using dynamic SQL.  You could also check into creating a SQL profile to see if that would improve performance.  You could also create a view that does most of this leg work for you and then hit that view with your query.  The view option won’t reduce execution time, but it may help with clarity.  If the data in these tables doesn’t change very often you could also look into using a materialized view that is refreshed from time to time – this will give you a lot of performance gain, but materialized views have their own challenges and depending on the environment some DBAs may discourage their usage.

I hope this helps.


Clone a database from SQL Server 2019 to 2017

Just succeeded to clone the Database, using on local: In the SSMS, right click on the database to export, then Tasks => Export Data-tier Application it creates a bacpac file.

Copy the backpac to the distant server, and there in SSMS, right click on the "Databases" folder and then Import Data-tier Application, use the copied backpac file.
It copies the schema and the data as well...


Why is Mysqlcheck on 5.1.41 still showing as Innodb table corrupted after a table dump and restore?

The error seems to indicate a problem with the primary key on the table mycompany.fact.

InnoDB: Page number (if stored to page already) 266102,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 994
InnoDB: Page may be an index page where index id is 0 4668
InnoDB: (index "PRIMARY" of table "mycompany"."Fact")

You can open the dump file in any text editor and remove the primary key constraint from the CREATE TABLE schema definition.  Then once you've restored your database, you can always add the primary key back in.  You would also need to deactivate Foreign Key Checks in your backup file.

-- at the beginning of the file
SET foreign_key_checks = 0;

-- at the end of the file
SET foreign_key_checks = 1;

This may get your database restored, but if there is a problem with the data in the column you're using as the primary key, then you may see other errors.

If that didn't work, I would also try

Backing up all the databases
Stopping MySQL
Delete the ibdata and iblog files
Starting MySQL again
Restore all my databases.

If there is a problem with your InnoDB files, this may be a way to solve it.

The problem could also be deeper and running a fsck on the filesystem that stores your InnoDB files may find corruption on the disk.  Rebooting your computer is one way to easily cause an fsck on all partitions.


Possible to have nested inserts in Postgres 8.4?

You should be able to do something like this with a writable CTE:

WITH i AS (
   INSERT INTO host (hostname, hostrole) VALUES ('foobar', 'Virtual') RETURNING id
)
INSERT INTO interface (name, mac, host)
SELECT 'eth0', '00:50:56:9d:34:d4', id
FROM i

(untested, but it should be something like that)

Writable CTE is in PostgreSQL 9.1 and up.


Help with wait type CHECK_TABLES_THREAD_BARRIER

Although I don't have any info on this yet in my waits library (will be fixed soon), you're likely hitting this hang when CHECKDB has hit an error state and it waiting for the various threads to finish running.

This is a rare, known issue that Microsoft is investigating but as yet has not identified or fixed.

Thanks


Why ALL PRIVILEGES ON `db`.* gives access to all databases?

The grants you gave on the redmine database have nothing to do with access to the test database.

The problem is the way mysql was installed. It allows full access to the test database. It is not a bug. I wrote a past post (Question and Answer) on why anybody can access a test database and how to fix it:

MySQL : Why are there "test" entries in mysql.db? (Question)
MySQL : Why are there "test" entries in mysql.db? (Answer)

In essence, the problem is the table mysql.db. It contains entries that allow everyone and his grandmother to do anything in a test database or any database whose first five characters are test_. Removing those entries with DELETE FROM mysql.db WHERE LEFT(db,4)='test'; will immediately solve your particular problem.

I have other posts on how to thoroughly strip anonymous users from mysql.user and why

Cannot drop anonymous user from mysql.user
mysqladmin user account not secure?
MySQL error: Access denied for user 'a'@'localhost' (using password: YES)
Grant table privileges to a user connecting from any host

Please read these carefully because my past answers will show you things like

SELECT USER(),CURRENT_USER();

This will break down the difference between how you logged in and how mysqld allowed you to login.


How to view tuples changed in a PostgreSQL transaction?

You can inspect the internal xmin to identify newly inserted row versions (note, that includes results from UPDATE):
SELECT * FROM foo_table
WHERE  xmin::text = (txid_current() % (2^32)::bigint)::text;

How so?
The function txid_current() returns the current transaction ID. However, while the internal transaction ID type in xmin is 32 bits wide and wraps around every 4 billion transactions,  txid_current() returns a bigint quantity, per documentation:

that is extended with an "epoch" counter so it will not wrap around during the life of an installation.

There is a thread on pgsql-hackers with Tom Lane discussing it.
I derived the safe WHERE condition from it.
Addressing comment: Deleted rows that were deleted in the same transaction are always invisible either way, so not included in the result.
Disclaimer
However, this does not work with subtransactions started with SAVEPOINT (or some other way, plpython can use subtransactions, too). Those spawn separate xids and there is currently no way to get the full list of xids belonging to the top transaction returned by txid_current(). In my search for a solution I found this closely related thread on pgsql-hackers:

Re: rows modified in current transaction

I don't currently see a reliable solution working around that limitation.
Related:

Can I select data inserted in the same uncommited transaction?


Hoursworked/day computation via Bio Logs

The idea I wrote in the comment http://sqlfiddle.com/#!9/785bc/5 :

select
    a.userid,
    date(a.CHECKTIME),
    timediff(max(b.CHECKTIME), min(a.CHECKTIME)) as diff
from CHECKINOUT a
join CHECKINOUT b
    on date(a.CHECKTIME) = date(b.CHECKTIME) and a.userid = b.userid
where
    a.CHECKTYPE = 'I'
    AND b.CHECKTYPE = 'O'
group by date(a.CHECKTIME), userid;

But as I wrote, there is a problem with the days where one of the I/O rows is missing and you need to either fix your data or specify what to do in such case.


The only real questions to mainly be considered when choosing between NoSQL and a Relational Database Management System (RDBMS) are:

"Does the data have a well defined structure or is it very unstructured / variable in structure?"
"Are the questions I need to ask of the data always simple?"
And less important but valid: "Do I need to distribute the data across multi-nodes of servers in a cluster, or having one centralized server is sufficient?"

For #1 it sounds like you have a well structured set of data. In fact, it's a very simple structure and should be very easy to store and maintain in a RDBMS. And if you go with a RDBMS then to support multiple Genres for multiple Movies all you would need is one additional linking table (aka bridge / junction table) called MoviesGenres that would be many-to-many with Movies by storing the MovieId and GenreId, one row per combination. You wouldn't need a "separate table for each genre" to solve this, just the one additional table MoviesGenres. So if MovieABC had Genre1 and Genre4 then there would be two rows in your MoviesGenres table for the records (MovieABC, Genre1) and (MovieABC, Genre4).
For #2, that's going to be up to your use cases. If you ever want to ask more complex questions like "How many movies with 3 or more genres were released after 2020?" or "Which movies have both Genre7 and Genre9, and were released before 2000?", etc, then a RDBMS makes it much easier to query these kinds of things and probably are more efficient at doing so (generally speaking).
For #3, this just depends on the scale of your data and your preference of how to maintain the hardware behind it. If you plan to have many terabytes of data and multi-billions of rows, do you prefer to pay for a multi-node cluster to horizontally scale the data at the tradeoff that it may not always be consistently in sync across every node in the cluster, or would you prefer to centralize all the data to one main server and scale vertically as the data grows. This is kind of a moot point nowadays, since even RDBMS now exist with features to support horizontal scaling too. So unless you foresee storing a very large amount of data upfront, I wouldn't be as concerned with this question as the prior two.
In general, based on what information you have provided, my recommendation would be to use a SQL (RDBMS) solution so you have the flexibility to query it as you need in a performant manner, given the fact you have a well defined and simple structure for your data.


If it is porting that you are refering to then...MySQL uses a different flavour of SQL compared to Microsoft SQL Server which uses T-SQL (TransactSQL)

There are guides out there if you search that will show you there difference in syntax and perhaps even convert blocks of SQL for you.


Modeling Subclass (or Subtype) Constraints in an Entity-Relationship Diagram

I think you can make an Orders entity that is a relationship between Products and Customers. The Orders would have subclasses FoodOrders and ShoeOrders, which respectively would be the relationships between the subclasses of Food - GroceryStores and Shoes - ShoeStores.

                                            ┌───────────┐
                                            │ Employees │
                                            └─────┬─────┘
                                                  │
                                                  │
      ┌──────────┐               ┌────────┐       │       ┌───────────┐
      │ Products ├─── contain ───┤ Orders ├─── deliver ───┤ Customers │
      └────┬─────┘               └────┬───┘               └─────┬─────┘
           │                          │                         │
           │                          │                         │
          isA                        isA                       isA
          │ │                        │ │                       │ │
     ┌────┘ └────┐             ┌─────┘ └─────┐            ┌────┘ └────┐
     │           │             │             │            │           │
 ┌───┴──┐    ┌───┴───┐         │             │    ┌───────┴───────┐   │
 │ Food │    │ Shoes │         │             │    │ GroceryStores │   │
 └───┬──┘    └───┬───┘         │             │    └───────┬───────┘   │
     │           │       ┌─────┴──────┐      │            │     ┌─────┴──────┐
     └───────────│───────┤ FoodOrders ├──────│────────────┘     │ ShoeStores │
                 │       └────────────┘      │                  └─────┬──────┘
                 │                     ┌─────┴──────┐                 │
                 └─────────────────────┤ ShoeOrders ├─────────────────┘
                                       └────────────┘


Maximize redo rate on replica side of an Availability Group

what is the bottleneck within SQL Server 2012 that stops it from maximizing the potential throughput and how can I overcome it?

That was a major work item in SQL 2016.

  The result is a design for SQL Server 2016 that provides high
  availability for the most demanding workloads on the latest hardware
  with minimal impact and scalable for the future. Our design for SQL
  Server 2012 and SQL Server 2014 is still proven and meets the demands
  for many of our customers. However, if you are looking to accelerate
  your hardware, our Always On Availability Group design for SQL Server
  2016 can keep pace.

See SQL Server 2016 – It Just Runs Faster: Always On Availability Groups Turbocharged


Upgrade SQL Server 2012 Developer Edition to SQL Server 2014?

From Microsoft BOL 'Supported Version and Edition Upgrades'

  SQL Server 2014 supports upgrade from the following versions of SQL
  Server:
  • SQL Server 2005 SP4 or later
  • SQL Server 2008 SP3 or later
  • SQL Server 2008 R2 SP2 or later
  • SQL Server 2012 SP1 or later

So, as you have 'later' than 2012 SP1, then answer is yes.

The actual 'Upgrades from Earlier Versions to SQL Server 2014' section of the linked article just looks like it is out of date and requires addition of 2012 SP2.


MySQL - using result of Procedure in Trigger to Insert in new table

I believe I have found the solution, and that is to convert the Procedure to a Function.

DELIMITER $$
    CREATE FUNCTION fn_get_item_type_id( item_name varchar(45) )
    RETURNS INT DETERMINISTIC
        RETURN (SELECT
            CASE
                WHEN LEFT(`item_name`,6)  = 'abcdef' THEN (SELECT t.`id` FROM `item_type` t WHERE t.`item_type_name` = 'type1')
            ELSE 0
            END AS `item_type_id`);
    $$
DELIMITER ;

From there I can simply Select it into a variable in the Trigger.


Also check the LOCKTIMEOUT, DLCHKTIME, and LOCKSIZE parameters. This article from DBI has been helpful to know what to set them to. It slants toward warehousing, but does mention settings for OLTP as well.


Facing error while trying to run dynamic pl/sql

You forgot the comparison operator for the WHERE clause

If you really want to use dynamic SQL (which is not needed for a simple UPDATE statement like that), then you should also pass all values as parameters, not just the student ID.

declare
   sql_stmt     varchar2(100);
   v_student_id number := 1;
   v_name varchar2(20) := 'pantea';
   v_new_stname varchar2(50);
begin
  sql_stmt := 'update student set student_name = :1 '||
              'where student_id = :2 '||
              'returning student_name into :3';

  execute immediate sql_stmt
    using v_name, v_student_id
    returning into v_new_stname;

  dbms_output.put_line('New student name is: ' || v_new_stname);
end;
/

The "string buffer too small" seems to happen when you are continuing a string literal (constant) over multiple lines. So it's better to open and close the string constants on each line and concatenate them using || rather than embedding a new line in the string as you did.


Linked Server stops responding for particular login

Stop restarting SQL Server. Just stop doing that. If you have a particular problem with a particular process that is being killed and rolling back, let it finish. Then, find out what's causing that to happen. Use Extended Events, or a server-side trace, heck, even a third party tool like sp_WhoIsActive. Do something.

SQL Server has a transaction log. All modifications go in there. When these are committed, the changes go into the data file. When they are killed, they have to roll back.

That's just how it works. The rollback has to finish for the database to remain transactionally consistent.

Restarting SQL Server does not make this go faster. In fact, you could easily make things worse, possibly even taking the database offline or corrupting it.

Admittedly, this answer does not address your question, but there's not enough space in the comments to explain why you need to figure out what's causing the killed processes. It could be a DBA, it could be an automated process.

Bottom line: let SQL Server finish what it's doing. If it takes a long time to do something, killing the task will take just as long, if not longer, as that to roll back.


How to change temporary directory for pg_dump?

The temporary files are a side effect of the --format=tar option.

A backup in default plain format goes through without temporary tables in the local drive. Omit the option to produce a backup in plain format without temporary local files.


DB2 SQL: How to select all days in a date range even if no data exists for some days

You say you have a date table.  You don't give a name so I shall refer to it as DateTable and the column in it as TheDate.  You need to select from this table and LEFT OUTER JOIN to it.  That way all dates in the range will have a row in the output with NULL for rows where you don't have "real" data.  You can convert these to zero using COALESCE.  I'll use your first posted query as an example:

Select
    dd.date,
    dd.week,
    sum(COALESCE(p.policy_count, 0)) Policies
from DateTable dt  -- Your date table
left outer join magenta.policies p -- left join includes all dates and any polices that exist
    on p.trans_date = dt.TheDate
left outer join global.dim_date dd
    on dd.date = p.trans_date
-- use DateTable in the WHERE to get the full range of time.
Where dt.TheDate between (select week - 112 days from global.TODAY_DATE)
                  and (select week -   1 day from global.TODAY_DATE)
Group by
    dd.date,
    dd.week

Sorry, don't have a DB2 instance to hand to test it.  Forgive any typos I may have introduced.


Creating a SQL Agent Job using Dynamic SQL

The first problem is that you are enclosing escaped string delimiters inside an already-escaped string:
----------------vv this opens a string
    @command = N''EXECUTE [dbo].[DatabaseBackup]
@Databases = N''USER_DATABASES'',
--------------^^ this tries to close it

This is similar to the following:
EXEC sys.sp_executesql N'PRINT ''foo''bar'';';

Which yields:

Msg 102, Level 15, State 1
Incorrect syntax near 'bar'.
Msg 105, Level 15, State 1
Unclosed quotation mark after the character string ';'.

To get around it, you need to double up the second layer of single quotes again.
EXEC sys.sp_executesql N'PRINT ''foo''''bar'';';

Which prints:

foo'bar

I don't think I have to tell you that putting four, eight, sixteen, and so on gets really ugly, really quickly.
A better solution
In the comment above, Dan was off on the reason for the error, but absolutely right on the question of why dynamic SQL in the first place. It's simply not necessary here - you can just use a local variable for the command, and concatenate or replace the certificate name.
DECLARE @SQLCertificate nvarchar(50) = N'00132';

DECLARE @cmd nvarchar(max) = N'EXECUTE [dbo].[DatabaseBackup]
          @Databases = N''USER_DATABASES'',
          @Directory = N''C:\SQLBackup'',
          @DirectoryStructure = N''{DatabaseName}'',
          @FileName = ''{DatabaseName}_{Description}_{Partial}_{CopyOnly}_{Year}{Month}{Day}_{Hour}{Minute}{Second}_{FileNumber}.{FileExtension}'',
          @BackupType = ''FULL'',
          @Description = N''Full_Backup'',
          @CheckSum = ''Y'',
          @Verify = ''Y'',
          @Compress = ''Y'',
          @CleanupTime = ''0'',
          @CleanupMode = ''AFTER_BACKUP'',
          @Encrypt = ''Y'',
          @EncryptionAlgorithm = ''AES_256'',
          @ServerCertificate = ''$cert$'',
          @LogToTable = ''Y'';';

SET @cmd = REPLACE(@cmd, N'$cert$', @SQLCertificate);

IF NOT EXISTS (SELECT 1 FROM [dbo].[sysjobs] WHERE [Name] = N'Backups_Encrypted_FULL')
BEGIN
    EXEC dbo.sp_add_job
        @job_name = N'Backups_Encrypted_FULL',
        @enabled = 1,
        @notify_level_eventlog = 2,
        @notify_level_email = 0,
        @notify_level_netsend = 0,
        @notify_level_page = 0,
        @delete_level = 0,
        @owner_login_name = N'DBA_Maintenance',
        @description = N'Performs a FULL backup of all User Databases',
        @category_name = N'[Database Backups]';

    EXEC dbo.sp_add_jobstep
        @step_id = 1,
        @job_name = N'Backups_Encrypted_FULL',
        @step_name = N'Execute T-SQL',
        @subsystem = N'TSQL',
        @command = @cmd,
        @database_name = N'DBA.Maintenance';

    EXEC dbo.sp_add_jobschedule
        @job_name = N'Backups_Encrypted_FULL',
        @name = N'Saturday Night',
        @enabled = 1,
        @freq_type = 8,
        @freq_interval = 64,
        @freq_subday_interval = 0,
        @freq_relative_interval = 0,
        @freq_recurrence_factor = 1,
        @active_end_date = 99991231,
        @active_start_time = 220000,
        @active_end_time = 235959;

    EXEC dbo.sp_add_jobserver
        @job_name = N'Backups_Encrypted_FULL',
        @server_name = N'(Local)';
END


Command to add column to all tables in a schema

You can do this with a loop and dynamic SQL:

do
$$
declare
  tname record;
begin
  for tname in select t.table_schema, t.table_name
               from information_schema.tables t
               where table_schema = 'schema1' --<< change schema name here
                 and not exists (select *
                                 from information_schema.columns c
                                 where (c.table_schema, c.table_name) = (t.table_schema, t.table_name)
                                   and c.column_name = 'coltest')
  loop
    -- change column definition in the following string
    execute format('alter table %I.%I add column coltest integer', tname.table_schema, tname.table_name);
  end loop;
end;
$$


Another way of doing this is a FOR loop only executing DROP statements for objects that exist. It's frankly overkill for a single object but it's useful when dealing with schema loads of things. For example:

BEGIN ATOMIC
    FOR D AS
        SELECT 'DROP TRIGGER ' || TRIGNAME AS DDL
        FROM SYSCAT.TRIGGERS
        WHERE TRIGSCHEMA = 'SOMESCHEMA'
        AND TRIGNAME = 'SOMETRIGGER'
    DO
        EXECUTE IMMEDIATE D.DDL;
    END FOR;
END@

I used similar tricks in a DROP_SCHEMA routine ages ago (source link at the bottom of the page) although it's redundant now.

The above trick can also be used for conditional upgrades of "only those objects which exist", and/or "only those objects which don't exist".

For actual schema creation I generally preferred keeping a "clean" creation script (no conditional stuff) and a separate "drop" script to clean things up. If that sounds like making redundant work for yourself, there are certain tricks one can employ to generate the drop script from the creation script (use of tac to reverse the install script, and a little regex magic to convert CREATE statements into DROP; it does mean being vaguely disciplined in how you format your SQL though unless you want the regexes to get really messy).


Where can I find scenarios to work through T-SQL

You could try to answer query-related questions here or on Stack Overflow. The nice thing about this is that you are solving real problems, not made-up ones, and you can also gain knowledge by seeing how other folks solve those same problems. You're also helping folks, potentially.


How long will it take to migrate a 5 GB Database to Amazon RDS

The usual answer: It depends

I'm assuming that you've read this guide on how to import data into an Oracle RDS (I chose Oracle because it's what I know, and you don't say what instance type you're using): http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Oracle.Procedural.Importing.html

There's similar guides for MySQL: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Importing.NonRDSRepl.html

Have a look at the guides and do some tests, they should provide you with some idea of timescales.


User Defined Function (UDF) in master database

I agree with Jonathan - as long as the function isn't supposed to return local data based on the calling database context, put the function in a utility database (this is also where I put things like numbers and calendar tables, splitting functions, etc):

USE UtilityDB;
GO
CREATE FUNCTION dbo.whatever() RETURNS ...
GO

Now, in every database that needs to access the function, just create a synonym:

CREATE SYNONYM dbo.whatever() FOR UtilityDB.dbo.whatever();

This way each database can still reference the function using a simple, 2-part name - and they don't have to know or care where it actually exists. And you only have to maintain a single copy of the function.

(And in fact, you can put the synonym in the model database, so that it is created in new databases automatically.)

The reason I dislike putting user objects in master - unless they really do need to be globally available and contextual to the calling database, like your own customized version of sp_spaceused or sp_helpindex - is that people aren't looking for user objects in master, and they're less discoverable there. They also make it harder to migrate your user databases elsewhere, because you need to remember the user stuff you put in master, too. If you are absolutely dead-set against creating or using a utility database for this, I think msdb is a more practical choice for a central location.


Comparing values from two tables in a nested where clause.

If you are selecting everything from Table2 with the condition on Table1, what you could do is this:

SELECT T2.* FROM TABLE2 T2
INNER JOIN TABLE1 T1 ON T2.RE_UID = T1.RE_UID
WHERE T1.CL_FROM_SVC_DTE BETWEEN T2.EFF_DTE AND T2.END_DTE

I hope it helps.


It's supposed to be the "distance" operator from the additional module pg_trgm.

The manual:

  text <-> text   real  Returns the "distance" between the arguments, that is one minus the similarity() value.

The module has to be installed (once per database) with:

CREATE EXTENSION pg_trgm;

More details:

How is LIKE implemented?

Theoretically, any user with the necessary privileges could create an operator with that operator name using CREATE OPERATOR - but not in the example you link to.


Today there's hardly any discussion (as I see it, of course), with regard to InnoDB backup. Xtrabackup by Percona is a widespread, feature rich & stable solution which offers:

Hot backups (no locks placed on database)
Incremental backups (done by backing up pages as opposed to statements)
I/O throttling (allowing to limit backup I/O as as to alleviate the load from the machine/disks)
Backup is consistent at time of end of backup (as opposed to time when backup started).

Xtrabackup derives from InnoDB Hot Backup, nowadays called MySQL Enterprise Backup. There's additional wrapping scripts which allow for the extra featureset.

Smaller databases may benefit from the common mysqldump. It really depends on your database size and traffic. mysqldump, event with --single-transaction, places a high load on the DB server, and makes for accumulating locks. It is not as hot as you might think (see this post of mine regarding backup temperatures).

I use LVM snapshots quite a lot. The great advantage is that these are DB ignorant, so no DB-related bugs affect this type of backup. The great disadvantage is an overly increased I/O. Check out mylvmbackup.

If you have ZFS or another file system which supports snapshots, that may be a great solution. If you have a storage device which supports snapshots, that's great too. All snapshots are DB ignorant, though you often want to script this a-la mylvmbackup so as to catch the point in time (DB-wise) for which backup is done.


How to set host_name() value in a Sql Server Stored Procedure?

As far as I'm aware, you can't manipulate HOST_NAME() inside of SQL Server. And I think that's a good thing. The fact that you can spoof this in the connection string is something I've long considered a security problem waiting to happen.

Instead, you will need to change the logic in your triggers. For example you could store HOST_NAME() in a variable, then check PROGRAM_NAME() - if it's your PowerBuilder app, overwrite the variable with whatever, and then the trigger logic can check the variable instead of HOST_NAME().

(You can do that in fewer steps, obviously; just illustrating.)


How to find percentage data change after last full backup?

Well, you should be able to fix his procedure with a very simple change. Change this line:

SELECT @dbccPageString = 'DBCC PAGE (' + @dbName + ', '

To this:

SELECT @dbccPageString = 'DBCC PAGE (' + QUOTENAME(@dbName) + ', '

But really, fix your database names. This is not the last thing that will break as a result.


Working on a blog creation website; should I be creating a new table for each blog, or keep everything on the same table?

More than a few thousand tables or databases is a bad idea in MySQL.  This because of MySQL's dependence on the filesystem, and most OS's can get bogged down when a directory has "too many" entries.

Hence, I vote for a single Blogs table.  You will probably need few other tables to round out the app.  Probably they will all be in a single database, but that does not matter much.


As long as the indices are smaller than the original table, or sorted differently, they can offer a performance boost, though in practice if you have low cardinality they may not.

The main cost of indices is increased time required for inserts and updates.  If your database is updated daily but read from frequently, even large indices may be worthwhile.  It depends on your data.  Remember: all generalizations are false.

Can you give a more specific example of where you're considering applying an index?  If you've already created one, does it show up in execution plans?


The following assumes that you feel the gaps in the id sequence is a problem. This usually isn't a problem, unless these gaps become enormous and there is a risk that at some point you will exceed the maximum id value.

I'm further going to assume the table is defined similar to this:

CREATE TABLE clients(
  id int unsigned AUTO_INCREMENT,
  clientid varchar(30) UNIQUE NOT NULL,
  lastcheck datetime,
  version varchar(10),
  PRIMARY KEY (id)
);

If you execute an INSERT ... ON DUPLICATE KEY UPDATE statement against this table, then the id column will increase every time since it attempts to INSERT first, then if that fails it does the UPDATE.

To avoid this you can do a SELECT against the table first to see if the clientid exists, then do an INSERT or an UPDATE depending on the result.

However, if you instead had a table like this:

CREATE TABLE clients (
  clientid varchar(30) NOT NULL,
  lastcheck datetime,
  version varchar(10),
  createtime datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (clientid)
);

... then you could use the same INSERT ... ON DUPLICATE KEY statement as before. There would be no id column to worry about, the clientid would be unique (since it's the primary key) and the sequence could be seen from the new createtime column (which might be useful on its own).

You can alter your current table to get the new one with a statement similar to this:

ALTER TABLE clients
  DROP PRIMARY KEY,
  DROP id,
  ADD PRIMARY KEY (clientid),
  ADD COLUMN createtime datetime NOT NULL DEFAULT current_timestamp();

(Note however that all the createtimes for existing records will then be set to the current timestamp when you run the ALTER.)


Many tables v/s one table with different entities types

You could really use either option: one translation table with a FIELD_TYPE partitioning attribute, or a one translation table for each table requiring localization.

The pros and cons of either approach are not likely to be related to the criteria you ask about: space or query performance.  These aren't necessarily the most important criteria for you to be concerned about either.  Unless you have really substantial scalability requirements then you should focus on code maintainability as a differentiator rather than disk space or query performance.

Another factor to consider is how much time you want to spend explaining your choices to other developers.  If you use a single table with a partitioning attribute, then you are opting for something very much like Entity Attribute Value (EAV) which is widely frowned upon for many mostly good reasons.

On the other hand, having a translation table per translatable object lets you tailor your column sizes to the actual data rather than picking the largest common denominator and also use tools like declarative referential integrity which are generally seen as best practices.


Simple Update Join much slower than it should be (MYSQL)

Maybe:
UPDATE A
    SET A.active = 1
    WHERE A.date >= '2020-01-01'
      AND ( EXISTS ( SELECT 1 FROM B WHERE B.id = A.id ) )

What is the datatype of date?  If it is DATE, you were excluding New Year's Day.
I'm assuming that id is the PRIMARY KEY if each table.  But, given that, I must ask why you have two tables with the same PK -- That is usually poor schema design.  (There are exceptions.)  Furthermore, INDEX(id, date) is probably useless.
Needed:  A:  INDEX(date).  Even so, that might be ignored, depending on the data distribution.  Please provide SHOW CREATE TABLE and EXPLAIN UPDATE ...


Postgresql - roles without linux accounts?

You need to update your pg_hba.conf to allow for a non-peer authentication method.

It’s perfectly normal to create roles which don’t have OS accounts.


Row lock contention issue with large transactions

This doesn't solve my problem because the first one simply throws an
  error

That is part of the solution. You can catch the error/exception then display the appropriate message to the client ("you are already logged in", "connection limit exceeded", etc.).

This is not something we can solve in the database.

I have seen a similar extreme case, where transactions were blocked with the event enq: TX - allocate ITL entry, that is something that can be managed in the database.

But I have yet to see a case where enq: TX - row lock contention is a database problem.


current_date is a keyword. There are no parentheses after it.

fcm.effect_end_date >= current_date


Postgres anonymous block execution of generated dynamic sql to drop databases

You can't pass identifiers as parameters, you need to create a dynamic string. The best way to do that is to use format()

Do $$
Begin
   execute format('drop database %I ', 'bak_20180404_2014_12');
End
$$ ;

You can combine that with a loop:

do
$$
declare
  l_rec record;
begin
  for l_rec in select datname from pg_database where datname like 'bak_%'
  loop
     execute format('drop database %I ', l_rec.datname);
  end loop;
end;
$$
;

Edit

I always forget that you can't drop a database like that. But in psql you can easily run the result of a query as a script:

postgres=> select 'drop database '||datname||';' from pg_database
postgres-> \gexec

Note the missing ; at the end of the select statement. When you terminate the query with \gexec instead of ; the query's result will be executed as a script.

Another option is to simply spool the output to a file, so that you can inspect the drop statements before running them:

postgres=> \t
Tuples only is on.
postgres=> \o drop.sql
postgres=> select 'drop database '||datname||';' from pg_database;
postgres=> \o
postgres=> \i drop.sql


MongoDB - Replication Oplog Window has gone below 1 hours

If replication OpLog window goes under one hour, it means that timestamp between first and last  lines in the OpLog is under one hour. This can happen when there is lots of changes in the DB and your size of OpLog is "too small".
No, this is not fatal. It just means that your secondary nodes cannot "fallback" more than what is your OpLog window. I mean that if your window is f.ex. 55 minutes, you cannot stop your secondary node for over 55 minutes, because it cannot "catch-up" anymore and it needs to do "full sync" and if that full sync takes more than 55 minutes, it cannot be done.

What you can do is change OpLog size to bigger. If you double your OpLog size, you get double size window.


You can connect to the instance using the Dedicated Administrator Connection (DAC).
It only accepts local connection by default.

Sqlcmd can be used:

sqlcmd -S localhost -U sa -P password -d master -A

  The DAC is available and supported through the sqlcmd command-prompt utility using a special administrator switch (-A)

  You can also connect prefixing admin: to the instance name in the format sqlcmd -Sadmin:<_instance_name_>.

SQL Server Management Studio can also be used:

  You can also initiate a DAC from a SQL Server Management Studio Query Editor by connecting to admin:<_instance_name_>.

However trying to establish a connection from Object Explorer using Connect to Server in SSMS will fail with this message:

  Dedicated administrator connections are not supported via SSMS as it establishes multiple connections by design.

From a disconnected Query Editor or connected with a regular user, reconnect using either of:

Query menu / Connection / Change Connection
Popup menu / Connection / Change Connection

Once connected, DISABLE TRIGGER can then be executed:

DISABLE TRIGGER logonTrigger ON ALL SERVER;


Generic script for stored procedure rollback

You can capture the event data with EVENTDATA() function and use a ddl trigger to persist the information into a version table, which can be used as rollback script, if needed. That way you will have a rollback script with timestamp stored in the database, no matter what happens within your version control. My sample implementation code is given below.

-- create version table to store procedure versioned text with timestamp
create table sp_verison(
event_type nvarchar(24),
obj_name nvarchar(256),
obj_type nvarchar(24),
sql_text nvarchar(MAX),
principal nvarchar(50),
log_date datetime);

go

--create trigger to capture proc ddl events
create trigger trg_sp_version
on database
for create_procedure, alter_procedure, drop_procedure
as
insert into sp_verison
(event_type, obj_name, obj_type, sql_text, principal, log_date)
values(
eventdata().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(24)'),
eventdata().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(256)'),
eventdata().value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(24)'),
eventdata().value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'),
eventdata().value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(50)'),
current_timestamp);

go


Periodically very slow execution of simplest queries

Probably... Some other transaction hit that table in some locking way.  And it took 15+ seconds for it to finish.  Meanwhile, all of your 'users' were running this SP, but being held up until the lock was released.

If you can catch it when it happens, do SHOW PROCESSLIST, and look for the queries other than CALL get_games.  One of them is likely to be the villain.

Once you have found it, there may be several actionss to consider...

Not grabbing the table so forcefully; that is, moving it outside its transaction, or otherwise changing the SQL.  (This seems like the likely answer.)
Not having every user always call get_games.
Other

SHOW ENGINE INNODB STATUS is likely to show the villain, also, but you have to run it during or very soon after the problem.

I dislike using autocommit=0 because I might forget to eventually do COMMIT.  That is a hard-to-find bug.  I prefer to explicitly say BEGIN as a reminder that I need to place the COMMIT some time really soon.  I even insist  (for my code) of putting both in the same subroutine.

Simply moving the CALL to before the BEGIN may solve it (unless you are also modifying the table in the transaction).


Details about existing roles and users in azure sql database

If you are talking about Azure SQLDB or Managed instance you can use this script:

SELECT role.name AS RoleName,
       member.name AS MemberName
    FROM sys.database_role_members  rolemember
JOIN sys.database_principals AS role
     ON rolemember.role_principal_id = role.principal_id
JOIN sys.database_principals AS member
     ON rolemember.member_principal_id = member.principal_id
     order by 1

I hope this helps.


connecting to Postgres running inside docker container 'posgres' does not exist

Found the issue:

I had PSQL installed locally (via brew) which was showing the error message.

Uninstalled it. Then used PgAdmin to connect to the container. And it's working fine now.


Why do I need to use a sub query to filter down a grouped select?

You can't reference an alias in the WHERE clause - this is just because of the order in which SQL Server parses the statement.

There have been many discussions about this here and on StackOverflow. A couple of examples that give some background:

https://dba.stackexchange.com/questions/19762/why-is-the-select-clause-listed-first/

Why are queries parsed in such a way that disallows the use of column aliases in most clauses?

An alternative would be:

SELECT dv.Name
      ,MAX(hb.[DateEntered]) as DE
FROM
    [Devices] as dv
    INNER JOIN
    [Heartbeats] as hb ON hb.DeviceID = dv.ID
GROUP BY dv.Name
HAVING MAX(hb.[DateEntered]) < '20130304';


"ORA-24454: client host name is not set" when connecting from Ubuntu instance on AWS EC2

After two hours, I figured it out.

Oracle's documentation for the error didn't seem terribly descriptive at first:

  ORA-24454: client host name is not set

  Cause: The network host name in files like /etc/hosts was not set.

  Action: Set the host name and try again.

Now, the command worked on my machine (Ubuntu laptop), but it didn't work on EC2. I mucked around with the HOSTNAME variable for a bit and tried to figure out if there was a way to override the "client host name" in sqlplus's settings -- no dice. I finally compared my machine's /etc/hosts to the one in EC2, and noticed this line that wasn't in EC2:

127.0.1.1 my-laptop-hostname

I didn't know this before, but apparently it's normal on Debian-based machines to map the system's hostname to this IP address. I didn't even know that 127.0.1.1 was a loopback IP; I just knew about 127.0.0.1.

What's more, it seems sqlplus relies on this line being present. Still no idea why.

Anyway, the fix is simple:

$ sudo /bin/bash -c "echo '127.0.1.1 ${HOSTNAME}' >> /etc/hosts"


Tools to ingest data to database in periods

Solution

Your solution contains two parts

Schedule a Job
Extract, Transform, and Load (ETL) the data

Job Schedulers

OSes have them (cron / task scheduler)
some databases have schedulers (oracle DBMS_SCHEDULER)
and there are enterprise COTS (Common Of The Shelf) software for inter-job scheduling and resource control

ETL

The simplest form would be a combination of shell scripts and stored procedures.

The shell script first copies (Extract) the data ( ftp,scp, REST call via curl ).
Then, it Loads the data into the database.  All RDBMS have tools to load CSV data.
After that, a stored procedures and/or SQL script is called to Transform the data into its final state.

These would most likely use INSERT...SELECT or MERGE statements.

Finally, the shell script archives the data file.

On the high end, COTS Software is available.

Build or Buy?

That's up to you.  You will need to do much more research.


SQL Server Import Job Removed Data After Job Failed

Take a good look at the control flow, both in your SQL Agent job, and the SSIS package it's running. I suspect there's a step in one of them that cleans out the source table after the work is finished, and this step is running regardless of whether the import succeeded or not.

Or there may be an unrelated cleanup job that cleans out that import table.


What is DEALLOCATE pdo_stmt_00000001 in pg_stats_activity "query"?

The state of these connections is probably (and hopefully) idle.

That means that the last thing that happened on these connections was to deallocate a prepared statement.

That is kind of funny, because one of the key advantages of prepared statements is to exist for a longer time and be reused. Maybe the application uses a connection pool and wants to “clean” the connection before returning it to the pool.

Anyway, there is nothing to worry about.


Alter mysql database engine doesn't work from cluster to other

Why you can't change ndbcluster to InnoDB?

Clustering with MySQL is supported only by the NDB storage engine. ndbcluster/ndb storage engine is meant for clustering if you specify a table with ENGINE=ndbcluster; this means table is being shared with all nodes and simply you can't change it to other ENGINE types, either you should take a backup of this table and then DROP table with ndbcluster option and import by changing ndbcluster to innodb

However, it is possible to create tables using other storage engines (such as InnoDB or MyISAM) on a MySQL server being used with a MySQL Cluster, but since these tables do not use NDB, they do not participate in clustering; each such table is strictly local to the individual MySQL server instance on which it is created.

Migrating from InnoDB to ndbcluster?

There are two ways to migrate InnoDB tables with foreign keys to NDB.

Dump the database and edit the script so each table specifies ENGINE=NDB before re-importing the script to a new database
Drop the constraints, alter the tables to use the NDB engine, and recreate the constraints

Dumping the database and editing the script is a straightforward use of mysqldump and a text editor.

Converting InnoDB Tables to MySQL Cluster


Function/procedure to use dblink to fetch remote data and insert to multiple local tables

You could use a data-modifying CTE (introduced with PostgreSQL 9.1) for that to perform well. Consider the following demo:

CREATE OR REPLACE FUNCTION f_fetch3()
  RETURNS text AS
$func$

SELECT public.dblink_connect(
      'hostaddr=?.?.?.? port=5432 dbname=db user=postgres password=???');

-- Truncate tables first?
-- TRUNCATE tbl1, tbl2, tbl3;

WITH i AS (
    SELECT *
    FROM public.dblink(
        'SELECT id
               ,col1
               ,col2
               ,col3
        FROM    remote_tbl'
         ) AS d (id int, col1 text, col2 text, col3 text)
    )
    , x AS (
    INSERT INTO tbl1
    SELECT id, col1
    FROM   i
    )
    , y AS (
    INSERT INTO tbl2
    SELECT id, col2
    FROM   i
    )
INSERT INTO tbl3
SELECT id, col3
FROM   i;

/* -- Analyze tables?
ANALYZE tbl1;
ANALYZE tbl2;
ANALYZE tbl3;
*/

SELECT public.dblink_disconnect();

$func$ LANGUAGE sql VOLATILE SECURITY DEFINER
SET search_path=myschema, pg_temp;

ALTER FUNCTION f_fetch3() OWNER TO postgres;
REVOKE ALL ON FUNCTION f_fetch3() FROM public;

Call:

SELECT f_fetch3();

Tested with PostgreSQL 9.1.4.

If you put your password here, I would create that function in a separate schema and remove all access from schema and function from the general public.

You obviously have dblink installed, but for the general public: the extension has to be installed once per database:

CREATE EXTENSION dblink;


DELETE ... IN hangs after deleting half of a large table (even for deleting 10 rows)

I found out what's going on here:

Because of how I was choosing test_id values in steps 1 and 2, temp_table B would sometimes be empty. (see question description for step/table context)

Then, with an empty temp_table_B, I was running step 4:

DELETE FROM live_db.test_table WHERE test_id
    IN (SELECT * FROM temp_table_B) ORDER BY test_id ASC LIMIT N;

which gave

DELETE FROM live_db.test_table WHERE test_id
    IN ( <empty> ) ORDER BY test_id ASC LIMIT N;

which would hang for quite a while.

Based on this answer: https://dba.stackexchange.com/a/86103/144766 , it seems like that empty table was probably compared against each of my 106 million rows, making the do-nothing DELETE take 25 minutes.

Now that I have modified my process to detect the empty table and abort before calling DELETE, I can manually watch for the (data-specific) condition that's causing my list-of-rows-to-delete to occasionally become empty.

EDIT: Even after fixing the empty-in-list problem, my query was greatly sped up by switching from the " IN (SELECT * temp_table) " version to a JOIN version, similar to what is suggested in comments by ypercube above.  This modification eliminates the 'SUBQUERY' from the execution plan and the query becomes a 'SIMPLE' query:

'IN' version:
id select_type         table           type  key     key_len rows Extra
1  PRIMARY             test_runs       index PRIMARY 4       10   Using where
8  DEPENDENT SUBQUERY  temp_table_B    ALL   NULL    NULL    10   Using where

'JOIN' version:
id  select_type  table        type   key     key_len ref                  rows Extra
1   SIMPLE       temp_table_B ALL    NULL    NULL    NULL                 10   Using where
1   SIMPLE       test_runs    eq_ref PRIMARY 4       temp_table_B.test_id 1


pt-kill - how to securely allow access to mysql server when running as service

I hope you are not one of my workmates :-) He had to setup a very similar configuration than yours a few weeks ago- you can check our packages if that helps you: Source deb, puppetization.
I told him to use socket-based authentication, it is the same method we use for non-human accounts that only need local-only permissions (icinga, prometheus). You only need to enable the plugin and tell the account with the same name as the account the service is going to run as to use it, so no password is required. Because there is no password, it cannot be stolen, so a compromise of the host would not escalate to a worse issue. Also less things that are private to handle. Unless there is a MySQL bug, the account is as secure as your OS account. You have to create a non-root account for both MySQL (auth_socket will provent password-based authentication and will lock it to localhost only) and the OS (system user with locked login), or otherwise it would defeat the point of it.
If for some reason you cannot use the above, you can still use password-based authentication, but set it on its own configuration file only readable by the user (non-root) that you start the service as.
The point is always to provide the least amount of permissions you can to a service. Root usage not only give the maximum amount, it also overlaps some OS (logging, limits, etc.) and mysql limits (max_connections) you don't want to, even if it effectively has a lots of permissions to kill other processes.


Should I convert the frontend generated UUIDv6 to binary(16) for use in SQL Server as the clustered primary key?

I would consider another option: cluster on a surrogate key (like Id int IDENTITY(1,1) NOT NULL), and make the app-generated UUID a nonclustered primary key.
This avoids the problems you called out with options 1 and 2, because you don't have to worry about the sorting / fragmentation issues in your base table, or potentially esoteric issues with clustering on binary.
It will also save you some space (int is smaller than uniqueidentifier and binary(16)), as the clustering key is included in every nonclustered index (as well as other tables that reference this table via foreign key).


Found out the option
MASTER_BIND is for use on replication slaves having multiple network interfaces, and determines which of the slave's network interfaces is chosen for connecting to the master
Thanks


I can't add extra node after reset root password mariadb 10.1.20

I solved by giving permissions (read/write) to grastate.dat


When Mysql chose to kill a Process(automatically)

MySQL can kill a connection if it exceeds wait_timeout.  (Caution, there are various wait timeout settings.)
InnoDB can kill a transaction in the case of a "deadlock".  One of the competing transactions is killed, the other(s) are allowed to continue.
InnoDB can kill a query if it is waiting too long for a lock.  See innodb_lock_wait_timeout, which defaults to 50 seconds.
A user can "kill" a mysql process via the commandline tool 'mysql' and it's kill command.
I don't think there is anything special about Stored Procedures, Functions, Events, Triggers -- they are essentially collections of queries that are, in turn, subject to the comments above.
UDFs contain code written by users and specially installed.  These can do anything nasty that you like.
There are essentially no "priorities" that you can control.  It is better to let MySQL decide what to do when.
Do you mean something else by "process"?


Comparing two different ODBC system-DNS entries. Which is faster? PS script

I haven't done a lot of PS so not sure what the error is really saying but... it looks like you are trying to fill a local table with the output from the select *? If that is true, I am wondering if PS created a temp table with a number/int column type for rownum but the rownum value in row 840 or 841 is something other than a number/int. Or something similar for another column.

It is expecting one data type when setting up the the temp table but is finding another.

I have seen this happen more than a few times over the years of doing data etl.

Is rownum ALWAYS in number?

What does all the data row 840 and 841 look like?

What happens if instead of select * you itemized one field? i.e. SELECT [first_ name] FROM TABLE1 WHERE ROWNUM <= 900;

What if you changed the rownum? between 900 and 1800?

BTW: cool script!


In Full recovery mode, when does the buffer/transaction log changes get applied to the database data pages?

According to the SQL Server Transaction Log Architecture and Management Guide, data pages are modified in cache first (logical write). This occurs the same way regardless of the database recovery model, with only the level of logging detail being different. A transaction log record containing the modification is created and written to the log buffer cache. These log buffers are always physically written to disk before the modified pages (write-ahead logging).

Data pages may be written to disk by lazy writing, eager writing, and the checkpoint process. The log buffer is flushed to disk before the corresponding data pages are written to the data files by these processes.

Transaction log buffers are physically written to disk when 1) they become full, 2) during COMMIT when the log buffer contains data for a durable transaction, 3) a checkpoint, or 4) a modified page is written to disk by one of the aforementioned processes.


How to prevent SQL Server Job History deadlocks

I wrote this (a much simplified version of the code in @Taiob's blog post he linked to), and then call it from a job instead of the Microsoft-provided sp_purge_jobhistory:

--  Create Procedure PurgeJobHistory.sql

USE master;
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO

IF NOT EXISTS (SELECT 1 FROM sys.procedures p JOIN sys.schemas s ON p.schema_id = s.schema_id WHERE s.name = N'dbo' AND p.name = N'PurgeJobHistory') BEGIN
   EXEC('CREATE PROCEDURE [dbo].[PurgeJobHistory] AS BEGIN SET NOCOUNT ON; END');
END;
GO

ALTER PROCEDURE [dbo].[PurgeJobHistory]
    @numberOfDaysToKeepSucceeded    SMALLINT    =   7
    ,@numberOfDaysToKeepOther       SMALLINT    =   30
AS
/*
Purpose:        Purges old rows from msdb.dbo.sysjobhistory, providing for different retention for success vs. other statuses
Author:         Mark Freeman (@m60freeman)
Last Edited:    2017-03-29
Compatibility:  SQL Server 2008 and newer
Adapted from:   http://sqlworldwide.com/manage-sql-agent-job-history/
License:        This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 3.0 Unported License.
                    http://creativecommons.org/licenses/by-nc-sa/3.0/
*/
SET NOCOUNT ON;
SET DEADLOCK_PRIORITY LOW;

DECLARE @LastDateToRetainSucceeded  INT
        ,@LastDateToRetainOther     INT
        ,@RowsDeleted               INT
        ,@RowCount                  INT;

--Get the actual date of the most recent rows that we wish to maintain (converted match sysjobhistory.run_date)
SELECT  @LastDateToRetainSucceeded  = CONVERT(INT, CONVERT(VARCHAR(200), (GETDATE() - @numberOfDaysToKeepSucceeded), 112))
        ,@LastDateToRetainOther     = CONVERT(INT, CONVERT(VARCHAR(200), (GETDATE() - @numberOfDaysToKeepOther), 112))
        ,@RowCount                  = COUNT(1)
  FROM  msdb.dbo.sysjobhistory sjh WITH (NOLOCK);

RAISERROR('Starting rows in history: %i', 0, 1, @RowCount) WITH NOWAIT;

--DELETE sysjobhistory rows 1,000 at a time in a loop until we have nothing left to delete
SET @RowsDeleted = -1;

WHILE @RowsDeleted != 0 BEGIN
    --Handle rows with a Succeeded status
    BEGIN TRAN;
        DELETE  TOP (1000)
          FROM  msdb.dbo.sysjobhistory WITH (READPAST)      --Ignore locked rows
         WHERE  run_status  =   1                           --Succeeded
           AND  run_date    <   @LastDateToRetainSucceeded;

        SELECT  @RowsDeleted = @@ROWCOUNT;
        RAISERROR('Succeeded rows deleted: %i', 0, 1, @RowsDeleted) WITH NOWAIT;
    COMMIT;

    WAITFOR DELAY '00:00:01';
END;

SET @RowsDeleted = 1;

WHILE @RowsDeleted != 0 BEGIN
    --Handle rows with all other status values
    BEGIN TRAN;
        DELETE  TOP (1000)
          FROM  msdb.dbo.sysjobhistory WITH (READPAST)      --Ignore locked rows
         WHERE  run_status  IN  (0, 2, 3)                   --Failed, Retry, and Canceled are all treated as failures.
           AND  run_date    <   @LastDateToRetainOther;

        SELECT  @RowsDeleted = @@ROWCOUNT;
        RAISERROR('Other rows deleted:     %i', 0, 1, @RowsDeleted) WITH NOWAIT;
    COMMIT;

    WAITFOR DELAY '00:00:01';
END;

SELECT  @RowCount   = COUNT(1)
  FROM  msdb.dbo.sysjobhistory sjh WITH (NOLOCK);

RAISERROR('Ending rows in history: %i', 0, 1, @RowCount) WITH NOWAIT;
GO

It cut us down from having over 1 million rows in msdb.dbo.sysjobhistory to only about 1/4 as many, and the deletes are no longer involved in deadlocks.

Thanks to @Taiob and @ScottHodgin for their input!


How can I manage an exception generated by Postgis?

You can wrap the PostGIS function ST_Contains() in a plpgsql (or other PL) function of your own and trap the exception there. Like:

CREATE OR REPLACE FUNCTION f_ST_Contains(geometry, geometry)
  RETURNS boolean AS
$func$
BEGIN
   RETURN ST_Contains($1,$2);

EXCEPTION WHEN OTHERS THEN
   RETURN FALSE;  -- Return FALSE instead of raising an exception
END
$func$  LANGUAGE plpgsql IMMUTABLE;

This returns FALSE instead of raising an error. You might want return NULL instead. Same effect on your query.

And use that instead of ST_Contains() in your query:

...
ON f_ST_Contains(A.geom, B.geom)

Schema-qualify the function name in the function definition (and in the call) to remove dependency on the search path:

...
RETURN public.ST_Contains($1,$2);  -- actual schema of extension here
...

Not sure about implications on performance (especially index usage); didn't test.


Why is installing ORACLE ODBC client a complex task?

You've pointed to instructions for installing the Oracle Instant Client.  That is a very slimmed down version of the Oracle Client that is intended to be bundled with third party applications and installed as part of their installer packages.  It's not something that normal people are expected to manually install (other than the third-party application developer that is working on building the installer that includes the Instant Client).

If you download the full Oracle client, you'll get a nice, friendly GUI where you hit Next a bunch of times.  The issue is that it's much bigger (because it contains a lot more options) and it's more difficult to transparently embed in a third-party's installer.


No, there isn't any. Any sort of 'last updated at' tracking would run into a severe performance problem as all updates, from all transactions, would attempt to update the one record tracking the 'last updated at'. This would effectively mean only one transaction can update the table at any moment, and all other transactions have to wait for the first one to commit. Complete Serialization. The number of admins/devs willing to put up with such performance penalty just for the benefit of knowing when the last update occurred is probably small.

So you are stranded to handle it via custom code. That means triggers since the alternative (detecting from log records) is a prerogative reserved only for transactional replication (or it's CDC alter-ego). Be aware that if you try to track it via a 'last updated at' column then you'll be facing exactly the serialization problem mentioned above. If update concurrency is important then you'd have to use a queue mechanism (trigger uses an INSERT and then a process aggregates the inserted values to formulate the 'last updated at'). Do not try to cheat with some 'clever' solution like sneaking at the current identity or looking up sys.dm_db_index_usage_stats. And also an 'updated_at' per-record column, like Rails timestamps have, is not working because it does not detect deletes...

Is there any 'lightweight' alternative? Actually there is one, but it is difficult to say whether it will work for you and is difficult to get it right: Query Notifications. Query Notification does exactly that, it will set up a notification if any data has changes and you need to refresh your query. Although most devs are familiar only with its .Net incarnation as SqlDependency, Query Notification can be used as a long lived, persisted mechanism to detect data change. Compared with true change tracking it is going to be really lightweight, and its semantics are closer to your needs (something, anything, changed, so you need to rerun the query).

But in the end, in your place, I would really reconsider my assumptions and go back to the drawing board. Perhaps you can use log shipping or replication to set up a reporting database, on a different server. What I read between the lines is that you're in need of a proper ETL pipe-line and an analytics data warehouse...


Problem connecting to postgresql from external windows machine

You need to edit pg_hba.conf file in your Postgres installation, and add an entry to allow access via other machine or allow network range.

host    all             all             10.10.10.0/24           trust

As you had success accessing Postgres on the same machine, that could be the reason.
Access via localhost is default.
Let us know if worked.


Is it worth making a separate DATE table or not?

Seems to me that is a classic example of over-thinking your design.

Keep the dates in the same table so they can be easily indexed with other columns if necessary, and not take up 3 times as many bytes, and not make life a living hell for future developers.


Trouble with Alerts and Notifications on SQL Server 2000

For SQL Server 2000, I quite honestly wouldn't bother trying to get operators to work. SQL Mail is a royal PITA and requires Outlook or a similar mail client to be installed on the server. I would rather just setup each job to have a step called "mail on failure", which uses a token to identify the job, and then calls a stored procedure. You would only reach that step in the workflow if you set a step to go to that step on failure. Otherwise the step before it simply quits the job with success.

A couple of alternatives:

(1) I used xp_smtp_sendmail with great success when I was still straddled with SQL Server 2000. The challenge is it used to be available on sqldev.net, but Gert seems to have abandoned that site. So you may have to search around to find a copy of the DLL. It was written by a Microsoft employee but the standard "download from a trusted source only" disclaimer applies.

(2) Use CDO. The same ick factor as SQL Mail but no dependency on Outlook / MAPI. See this KB article for some info, but here is an example that sets up a stored procedure to send job mail failures and then how to include a job token in a job step to call that stored procedure.

First, a generic procedure to send the e-mail (you may want to re-use this for more than just job failures):

CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
   @From       VARCHAR(320)   = 'default_admin@yourcompany.com',
   @To         VARCHAR(320)   = 'default_admin@yourcompany.com',
   @Subject    NVARCHAR(4000) = N'',
   @Body       NVARCHAR(4000) = N'',
   @SMTPServer VARCHAR(2000)  = '127.0.0.1'
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @obj INT;

  EXEC sp_OACreate 'CDO.Message', @obj OUTPUT;
  EXEC sp_OASetProperty           @obj,
    'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value',
    '2';
  EXEC sp_OASetProperty           @obj,
    'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value',
    @SMTPServer;
  EXEC sp_OAMethod                @obj,
    'Configuration.Fields.Update',
    NULL;
  EXEC sp_OASetProperty           @obj, 'To',       @To;
  EXEC sp_OASetProperty           @obj, 'From',     @From;
  EXEC sp_OASetProperty           @obj, 'Subject',  @Subject;
  EXEC sp_OASetProperty           @obj, 'TextBody', @Body;
  EXEC sp_OAMethod                @obj, 'Send',     NULL;
  EXEC sp_OADestroy               @obj;
END
GO

Next, a procedure to handle the e-mail specifically for job failures:

CREATE PROCEDURE dbo.JobAlertSender
   @To      VARCHAR(320) = 'default_admin@yourcompany.com',
   @job_id  VARCHAR(36)
AS
BEGIN
   SET NOCOUNT ON;

   DECLARE @Subject NVARCHAR(4000);
   SELECT @Subject = N'Job failed: ' + name
       FROM msdb.dbo.sysjobs
       WHERE job_id = @job_id;

   DECLARE @Body NVARCHAR(4000);
   SET @Body = CONVERT(CHAR(10), GETDATE(), 120)
       + ' ' + CONVERT(CHAR(5),  GETDATE(), 108);

   EXEC dbo.sp_send_cdosysmail
        @From    = 'default_admin@yourcompany.com',
        @To      = @To,
        @Subject = @Subject,
        @Body    = @Body;
END
GO

Now you can add a step to your job that calls this procedure. You would only hit this step if a previous step failed. The step would have code like this (note that you may want to override the default "To" person depending on the job):

EXEC master.dbo.JobAlertSender
   -- @To = 'override@yourcompany.com',
   @job_id = '(JOBID)';

Here is how the job step would look:

And here is how you would set up previous steps, either using "go to the next step" or picking the failure step explicitly from the drop-down:

If you find xp_smtp_sendmail, you'd just call a slightly different procedure from dbo.JobAlertSender.

If you don't have a valid SMTP server, you'll need to install an SMTP server locally. For help with that you'll want to hit up SuperUser or ServerFault. If you need something to test locally in the meantime, nothing could be simpler than smtp4dev. You won't get the e-mails in your inbox, but you can verify the contents, headers, etc until you do have a proper SMTP server in place.

Google also told me I wrote this article in 2002 which may contain some useful information.


It just depends on what one you want,

pgpool-II-96 is provided by pgpool.net
postgresql-pgpool-II is provided by your distro.

There is no right answer. Likely, I would go with distro-provided version. It's usually better integrated and I value that more than being more up to date.

As for,

  FATAL:  unable to read data from DB node 0

That's another question, but it sounds like a connectivity issue.


FULLTEXT CONTAINS search returns no results in SQL SERVER 2014 with multiple search terms

Turns out these are not logically the same; the second requires the results to be in the same column, while the first is across all columns


What is the maximum number of local variables that can participate in SET operation?

Msg 8631, Level 17, State 1, Line xxx
Internal error: Server stack limit has been reached.
Please look for potentially deep nesting in your query, and try to simplify it.

This error occurs with long SET or SELECT variable assignment concatenation lists due to the way SQL Server parses and binds this type of statement - as a nested list of two-input concatenations.
For example, SET @V = @W + @X + @Y + @Z is bound into a tree of the form:
ScaOp_Arithmetic x_aopAdd
    ScaOp_Arithmetic x_aopAdd
        ScaOp_Arithmetic x_aopAdd
            ScaOp_Identifier @W
            ScaOp_Identifier @X
        ScaOp_Identifier @Y
    ScaOp_Identifier @Z

Each concatenation element after the first two results in an extra level of nesting in this representation.
The amount of stack space available to SQL Server determines the ultimate limit to this nesting. When the limit is exceeded, an exception is raised internally, which eventually results in the error message shown above. An example process call stack when the error is thrown is shown below:

Repro
DECLARE @SQL varchar(max);

SET @SQL = '
    DECLARE @S integer, @A integer = 1;
    SET @S = @A'; -- Change to SELECT if you like

SET @SQL += REPLICATE(CONVERT(varchar(max), ' + @A'), 3410) +';'; -- Change the number 3410

-- SET @S = @A + @A + @A...
EXECUTE (@SQL);

This is a fundamental limit due to the way multiple concatenations are handled internally. It affects SET and SELECT variable assignment statements equally.
The workaround is to limit the number of concatenations performed in a single statement. This will also typically be more efficient, since compiling deep query trees is resource-intensive.


What privileges are need for a normal Oracle user to access V$ and DBA_ views?

If you want to allow that user to read all system views, you can use

grant select_catalog_role to the_user;

Predefined Roles in an Oracle Database Installation