Excluding (or subtracting) multiple dateranges from a daterange in PostgreSQL

Disjoint ranges.

What essentially you're asking to do is exclude a subset of a range. That creates a "discontinuous ranges" or "disjoint ranges" which can not be contained . It's better to see this with an int,

([1,10] - [3,7]) = ([1,2] + [8,10])

These two forms are the same, they're just different ways of writing it. This is simply not supported by the daterange type from the docs,

  The union and difference operators will fail if the resulting range would need to contain two disjoint sub-ranges, as such a range cannot be represented.

That's not to say you can't accomplish the same thing just explode the ranges and subtract out the values you don't want.

SELECT d::date
FROM generate_series(
  '2018-11-01'::timestamp without time zone,
  '2018-11-30'::timestamp without time zone,
  '1 day'
) AS gs(d)
WHERE NOT EXISTS (
  SELECT
  FROM test
  WHERE test.period @> d::date
    AND test.available IS FALSE
);

Note always do date-math including date-range generate with timestamp without time zone or you may encounter DST bugs


High Log Flush Wait Time and filling Transaction logs

Usual supect, in this case: The IO subsystem is slow. Are you using SSDs?

DMVs are your friends.

Identity & Isolate Issues

Log Flush Wait Time

This is an indication of Underlying IO bottleneck
What is the write latency?
Find out using sys.dm_io_virtual_file_stats &
sys.dm_io_pending_io_requests

Size log files appropriately

Your application seems to be a very high transactional load type.
If log files are frequenting to a certain size limit, it means it needs such size.
I would size my log files to be 750 MB if the log file is frequently reaching that size at its peak.

Transactional log backups

Maybe you just need a more frequent log backup.
Test & decide.

As far as memory, a 30000 PLE looks good to me on a 32 GB RAM server. Assuming this is SQL's memory, not the server's.

Resources:

Paul Randal's Transaction Log Monitoring
Paul Randal's Page Life Expectancy


Left joining table with existing data negative match

I think you need a simple anti-semijoin. Either with LEFT JOIN - IS NULL:

SELECT x.*
FROM table_X AS x
    LEFT JOIN TYPE_GOODS_ASSOC AS g
    ON g.GOOD_ID = x.GOOD_ID
WHERE g.GOOD_ID IS NULL ;

or with NOT EXISTS:

SELECT x.*
FROM table_X AS x
WHERE NOT EXISTS
      ( SELECT 1
        FROM TYPE_GOODS_ASSOC AS g
        WHERE g.GOOD_ID = x.GOOD_ID
      ) ;


How to massively change string capitalization in a table?

Mysql has a mechanism called cursors that allows to read all the rows from the table one by one. Detailed description can be found here:
https://dev.mysql.com/doc/refman/8.0/en/cursors.html
You have to write a stored routine with a cursor loop inside and to place some case transforming logic in it. Useful functions are LOWER(), UPPER(), SUBSTRING() and other text functions described here:
https://dev.mysql.com/doc/refman/8.0/en/string-functions.html


Limiting user access by subnet to Amazon Aurora PostgreSQL

here are two ideas that can be tried..

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

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

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

The information needed is located in

select * from pg_stat_activity


Is it a good idea to include a value column (e.g. from measurements) in an index?

You have a time series (measurements) organized by id (clustered index). I am yet to see a single case where using id as clustered key for time series makes sense. All queries will ask for date ranges. Organize by time:

CREATE TABLE measurements
(
    id bigint IDENTITY,
    parameter_id int NOT NULL,
    measuretime datetime NOT NULL,
    value float NOT NULL,
    PRIMARY KEY NONCLUSTERED (id ASC)
);

CREATE CLUSTERED INDEX cdx_measurements ON measurements(measuretime);

Yes, lookup by id will take two reads instead of one. Who cares, id is looked up in singletons. Is a trade off.


UPDATE performance where no data changes

If I have an UPDATE statement that does not actually change any data (because the data is already in the updated state), is there any performance benefit in putting a check in the where clause to prevent the update?

There certainly could be as there is a slight performance difference due to UPDATE 1:

not actually updating any rows (hence nothing to write to disk, not even minimal log activity), and
taking out less restrictive locks than what are required for doing the actual update (hence better for concurrency) (Please see Update section towards the end)

However, how much of a difference there is would need to be measured by you on your system with your schema, and data, and system load. There are several factors that play into how much impact a non-updating UPDATE has:

the amount of contention on the table being updated
the number of rows being updated
if there are UPDATE Triggers on the table being updated (as noted by Mark in a comment on the Question). If you execute UPDATE TableName SET Field1 = Field1, then an Update Trigger will fire and indicate that the field was updated (if you check using either the UPDATE() or COLUMNS_UPDATED functions), and that the field in both INSERTED and DELETED tables are the same value.

Also, the following summary section is found in Paul White's article, The Impact of Non-Updating Updates (as noted by @spaghettidba in a comment on his answer):

  SQL Server contains a number of optimisations to avoid unnecessary logging or page flushing when processing an UPDATE operation that will not result in any change to the persistent database.

  Non-updating updates to a clustered table generally avoid extra logging and page flushing, unless a column that forms (part of) the cluster key is affected by the update operation.
  If any part of the cluster key is ‘updated’ to the same value, the operation is logged as if data had changed, and the affected pages are marked as dirty in the buffer pool.  This is a consequence of the conversion of the UPDATE to a delete-then-insert operation.
  Heap tables behave the same as clustered tables, except they do not have a cluster key to cause any extra logging or page flushing.  This remains the case even where a non-clustered primary key exists on the heap.  Non-updating updates to a heap therefore generally avoid the extra logging and flushing (but see below).
  Both heaps and clustered tables will suffer the extra logging and flushing for any row where a LOB column containing more than 8000 bytes of data is updated to the same value using any syntax other than ‘SET column_name = column_name’.
  Simply enabling either type of row versioning isolation level on a database always causes the extra logging and flushing.  This occurs regardless of the isolation level in effect for the update transaction.

Please keep in mind (especially if you don't follow the link to see Paul's full article), the following two items:

Non-updating updates still have some log activity, showing that a transaction is beginning and ending. It is just that no data modification happens (which is still a good savings).
As I stated above, you need to test on your system. Use the same research queries that Paul is using and see if you get the same results. I am seeing slightly different results on my system than what is shown in the article. Still no dirty pages to be written, but a little more log activity.

  ... I need the row count to include the unchanged row so I know whether to do an insert if the ID does not exist. ... is it possible to get the row count that I need somehow?

Simplistically, if you are just dealing with a single row, you can do the following:

UPDATE MyTable
SET    Value = 2
WHERE  ID = 2
AND Value <> 2;

IF (@@ROWCOUNT = 0)
BEGIN
  IF (NOT EXISTS(
                 SELECT *
                 FROM   MyTable
                 WHERE  ID = 2 -- or Value = 2 depending on the scenario
                )
     )
  BEGIN
     INSERT INTO MyTable (ID, Value) -- or leave out ID if it is an IDENTITY
     VALUES (2, 2);
  END;
END;

For multiple rows, you can get the information needed to make that decision by using the OUTPUT clause. By capturing exactly what rows were updated, then you can narrow down the items to look up to know the difference between not updating rows that don't exist as opposed to not updating rows that exist but don't need the update.

I show the basic implementation in the following answer:

How to avoid using Merge query when upserting multiple data using xml parameter?

The method shown in that answer doesn't filter out rows that exist yet do not need to be updated. That portion could be added, but you would first need to show exactly where you are getting your dataset that you are merging into MyTable. Are they coming from a temporary table? A table-valued parameter (TVP)?

UPDATE 1:

I was finally able to do some testing and here is what I found regarding transaction log and locking. First, the schema for the table:

CREATE TABLE [dbo].[Test]
(
  [ID] [int] NOT NULL CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED,
  [StringField] [varchar](500) NULL
);

Next, the test updating the field to the value that it already has:

UPDATE rt
SET    rt.StringField = '04CF508B-B78E-4264-B9EE-E87DC4AD237A'
FROM   dbo.Test rt
WHERE  rt.ID = 4082117

Results:

-- Transaction Log (2 entries):
Operation
----------------------------
LOP_BEGIN_XACT
LOP_COMMIT_XACT

-- SQL Profiler (3 Lock:Acquired events):
Mode            Type
--------------------------------------
8 - IX          5 - OBJECT
8 - IX          6 - PAGE
5 - X           7 - KEY

Finally, the test that filters out the update due to the value not changing:

UPDATE rt
SET    rt.StringField = '04CF508B-B78E-4264-B9EE-E87DC4AD237A'
FROM   dbo.Test rt
WHERE  rt.ID = 4082117
AND    rt.StringField <> '04CF508B-B78E-4264-B9EE-E87DC4AD237A';

Results:

-- Transaction Log (0 entries):
Operation
----------------------------

-- SQL Profiler (3 Lock:Acquired events):
Mode            Type
--------------------------------------
8 - IX          5 - OBJECT
7 - IU          6 - PAGE
4 - U           7 - KEY

As you can see, nothing is written to the Transaction Log when filtering out the row, as opposed to the two entries marking the beginning and ending of the Transaction. And while it is true that those two entries are almost nothing, they are still something.

Also, the locking of the PAGE and KEY resources is less restrictive when filtering out the rows that haven't changed. If no other processes are interacting with this table then it is probably a non-issue (but how likely is that, really?). Keep in mind that that testing shown in any of the linked blogs (and even my testing) implicitly assumes that there is no contention on the table since it is never part of the tests. Saying that non-updating updates are so light-weight that it doesn't pay to do the filtering needs to be taken with a grain of salt since the testing has been done, more or less, in a vacuum. But in Production, this table is most likely not isolated. Of course, it could very well be that the little bit of logging and more restrictive locks don't translate into less efficiency. So the most reliable source of information to answer this question? SQL Server. Specifically: your SQL Server. It will show you which method is better for your system :-).

UPDATE 2:

If the operations in which the new value is the same as the current value (i.e. no update) out number the operations in which the new value is different and the update is necessary, then the following pattern might prove to be even better, especially if there is a lot of contention on the table. The idea is to do a simple SELECT first to get the current value. If you don't get a value then you have your answer regarding the INSERT. If you do have a value, you can do a simple IF and issue the UPDATE only if it is needed.

DECLARE @CurrentValue VARCHAR(500) = NULL,
        @NewValue VARCHAR(500) = '04CF508B-B78E-4264-B9EE-E87DC4AD237A',
        @ID INT = 4082117;

SELECT @CurrentValue = rt.StringField
FROM   dbo.Test rt
WHERE  rt.ID = @ID;

IF (@CurrentValue IS NULL) -- if NULL is valid, use @@ROWCOUNT = 0
BEGIN
  -- row does not exist
  INSERT INTO dbo.Test (ID, StringField)
  VALUES (@ID, @NewValue);
END;
ELSE
BEGIN
  -- row exists, so check value to see if it is different
  IF (@CurrentValue <> @NewValue)
  BEGIN
    -- value is different, so do the update
    UPDATE rt
    SET    rt.StringField = @NewValue
    FROM   dbo.Test rt
    WHERE  rt.ID = @ID;
  END;
END;

Results:

-- Transaction Log (0 entries):
Operation
----------------------------

-- SQL Profiler (2 Lock:Acquired events):
Mode            Type
--------------------------------------
6 - IS          5 - OBJECT
6 - IS          6 - PAGE

So there are only 2 locks acquired instead of 3, and both of these locks are Intent Shared, not Intent eXclusive or Intent Update (Lock Compatibility). Keeping in mind that each lock acquired will also get released, each lock is really 2 operations, so this new method is a total of 4 operations instead of the 6 operations in the originally proposed method. Considering this operation is running once every 15 ms (approximately, as stated by the O.P.), that is about 66 times per second. So the original proposal amounts to 396 lock/unlock operations per second, while this new method amounts to only 264 lock/unlock operations per second of even lighter-weight locks. This is not a guarantee of awesome performance, but certainly worth testing :-).


SQL Server Snapshot Replication - Is there an option to clean up the snapshot folder after X amount of time?

set the retention period on the publication
also see the link below ( this is the replication escalation engineers blog site - so a great resource ) for a slight gotcha.
https://repltalk.com/2010/07/14/snapshot-files-deleted-when-immediate_sync-ntrue-and-retention-0/


If the database does not know about those files, you should catalog them in RMAN:

catalog start with '/path/to/files';

After that you can use your above commands for identifying or deleting these files.

Deleting old backups in a safe way is achieved by running DELETE OBSOLETE, with the required retention set in configuration or specified in the command (e.g. DELETE OBSOLETE RECOVERY WINDOW OF 14 DAYS;).


How to track changes to a record that spans multipe tables

In Oracle 12c specifically there is a new feature called "temporal flashback queries" that serves exactly this purpose. (Similar features are available in other databases: DB2, SQL Server, Vertica to name a few.)

To enable temporal queries you:

Ensure that each of the tables representing your document has a pair of dates indicating the start and end date (time) when a particular record was valid (active), and
Keep historical records by not updating them in place but rather inserting new versions of the records, updating only the end dates of historical records.

You will then be able to issue queries against your tables that look much like SELECT whatever FROM mytable AS OF some_date, which would return the records that were active at the specified date.

You can read the detailed description here.


Unable to check whether the location specified is on CFS

I was getting this on a VM with NAT NIC. Installer was pulling in DNS suffix from NIC and messing up DNS name of my server on install. Disabling NAT NIC allowed to me to install and then I re-enabled after install and it was fine. Not related to permissions at all.


How to create a flexible table schema for storing messages from different chats?

All modern chatting interfaces, without exception, implement a hierarchical and a not-chronological schema for chat. That means you have to use (the soon to be released) MySQL 8, as prior versions do not support recursive CTEs which are required to do this. Any workaround can't permit infinite depth, which is usually required or at the very least nice to have.

Rather than drawing up a schema here, you can see what one looks like in PostgreSQL here, where I answered a similar question. Essentially, each message has id|parent_id the parent_id points to the id on the same table. This creates a hierarchy. We call this implementation of hierarchy "self-referential", or "single-table hierarchy."

Moreover, you may wish to implement an array of tagged users or the like for better indexing.

Ultimately, if you're going to go down this route and you have no prior work, you should be using PostgreSQL -- which supports Recursive CTE's now, and sql-array's for easy tagging.

I'm not saying your schema has to look like those. You may have additional demands, but I wouldn't want to start off with an inferior feature set and the wrong tool.

For clarity this critique is specific to your chat_messages table. The chat sessioning would be done by just creating a seperate table for it, and linking all messages to an entry in that table. For instance,

CREATE TABLE chat_session (
  chat_session_id  int  PRIMARY KEY
  ....
);

Other tables..

CREATE TABLE chat_message (
  ts                             timestamp with time zone,
  chat_message_id                int PRIMARY KEY,
  chat_message_parent_id         int REFERENCES chat_message,
  chat_message_author_user_id    int REFERENCES user,
  chat_message_author_client_id  int REFERENCES client
);

Or the like.


This database has two foreign keys going to a single field, is it in 3NF?

Normalization up to 3NF and BCNF is about FDs (functional dependencies) within a table. (We can also say that a database is in a certain normal form to mean that all of its tables are.) But FKs (foreign keys) are between tables, so they do not affect what normal forms a table is in.

(Some diagramming styles have lines that are FKs. Others have lines that are relationships represented by "association" tables. Some have both kinds of lines. Association tables have to be normalized too. You need to know what the relationship is in terms of the application.)

A FD (constraint) on a table says that when a subrow value for a (determining) column set only ever appears with the same subrow value for another (determined) column set or column. A column set that determines every attribute is a superkey. A CK (candidate key) is a superkey that contains no smaller superkey.

  A table is in 3NF if and only if, for each of its FDs X → A, at least one of the following conditions holds:
  - X contains A
  - X is a superkey
  - each attribute in A-X is contained in some CK

There is nothing wrong with having two FKs to the same table or superkey in a table:

  Just declare per what is true of your relationships/tables:

  a PK or UNIQUE NOT NULL declaration says that every subrow value in a column set is unique. Ie that the column set is a superkey. (A PK is
  just a distinguished UNIQUE NOT NULL.)
  a FK declaration says that a column list subrow value in referencing columns must also be in referenced superkey columns.

We can only guess at your FDs from the words you chose for table and column names. You need to tell us what they are before we can tell you what normal forms your tables satisfy. But your FKs and what they reference will have nothing to do with that.

PS Some of your tables seem to have one composite (multi-column) PK where the columns should really be two CKs, one simple (one column) and one composite.


How to link Similar-but-distinct Models of Medical Imaging Data?

Since you said that there are always 2 types of data I would do it like this

This might use the wrong terms since I don't know what exactly is available, but I think you will get the idea.

image (the data that is common to both scanes/images)
---------------------------
    image_id
    date

coordinator (the one that is inserted by hand)
---------------------
    image_id (PrimaryKey and ForeignKey)
    comment
    description
    order
    [...]

metadata (that you get programmatically)
----------------------
   image_id (PrimaryKey and ForeignKey)
   {whatever data that is}

This way, you have both images connected but have the freedom to model the details differently.

Additionally, you could move the metadata into a seperate table if the data is similar. This might be useful for later comparisson/collection/...

metadata (that you get programmatically)
----------------------
    image_id (PrimaryKey and ForeignKey)
    metadata_id

metadata_description
-------------------------
    metadata_id
    description
    name
    {whatever the metadata is}


SQL Server 2008 server cannot connect to itself with alias name or "localhost"

Not sure if this will help or not, but i had the same issue with a clustered environment.

localhost will only refer to the same machine it was run on, if the sql server is located on a different machine, localhost will fail, as the server is not on that machine.

I found this issue when i failed over to the second box in the cluster, and from the first machine i ran the localhost and it failed, i jumped over to the second machine and ran the localhost command and it worked....

i failed the second machine back over to the original machine, and issued the localhost command on the newly failed over to machine, and it would work again, but the localhost would fail on the old machine.

long story short, localhost only applies to the machine it is being run from, and the cluster sub-system does not really recognize the localhost command...  best to use the virtual IP address of the cluster/sql server for all access.


S1 = old server, S2 = new server

Full Backup S1
Restore to S2 and with NOrecovery
Block ALL data changing connections to S1 (e.g. change web connection password)
Diff backup on S1
Restore diff to S2 with recovery - S2 will now be ready
Enable connections to S2 ( don't forget to reset any changed passwords back to the originals)


Cannot process cube using SQL agent job in SSMS

I got this to work now.

In Management Studios I extended Databases->theDatabase->Data source and from there I had the option ImpersonateInfo with the value ImpersonateAccount.

I clicked at ImpersonateAccount and then on .... From there I entered the credentials of the newly created account, and voilá.


That is not possible, the database does not support such grants.
You can grant the required privileges on each object individually in the schema.

For example:

begin
  for o in (select * from dba_tables where owner = 'XYZ')
  loop
    execute immediate 'grant read on "' || o.owner || '"."' || o.table_name || '" to ABC';
  end loop;
end;
/

It is better to create a role, grant that role to the other user, and maintain the grants to the role when you create new objects.

grant_system_privileges::=

grant_object_privileges::=

on_object_clause ::=


Why does this cursor produce results in the incorrect order?

The PRINT statement inside the WHILE loop does execute in the order you expect, but the output is buffered before sys.sp_executesql returns.  Implementation details mean the buffered output is reversed.

Using RAISERROR (@cmd, 0, 1) WITH NOWAIT; instead of PRINT forces the buffer to flush after each call, giving you the results in the order you expect.  IIRC the NOWAIT trick only works per-row for the first 500 rows.  In any case, all this is undocumented stuff that could change at any time, so please don't rely on it - I only mention it to explain what you see.

The reversal does not occur if you replace the sp_executesql call with EXEC (@cmd) AT ' + @Server + ' though that does require a USE database command prefixed to @cmd and also the linked server needs to be enabled for RPC.  This is not a recommendation either, just showing the output reversal is a quirk of sp_executesql.


Teradata: How to design table to be normalized with many foreign key columns?

Disclaimer: I have never built a Teradata system, so I can't claim this from first-hand experience, but I will explain the reasoning.

I think that Teradata will be able to produce this view efficiently.  From what you say, it appears to do little more than join some very small dimension tables against a fact table.  The join operations will be relatively efficient.  Unless I misunderstand your requirements these columns are allowing your application to select various rollups of data from a multi-grain fact table.

Even though Teradata is a shared-nothing system, I can't see any requirement for the view to push large semi-joins across nodes or anything like that.

Beyond that, all I can suggest is that you suck it and see.  If you don't have anywhere to experiment you could download the express version of Teradata off their web site and see if you can prototype this structure to see what the query plan actually is.


Restoring a database backup to a local machine in MySQL

You should login to mysql like this

C:\> mysql -uroot -p <hit enter>
Enter password:

Next, select the database you want to load the data into

mysql> CREATE DATABASE IF NOT EXISTS mynewdb;
mysql> USE mynewdb

Then, run the script

mysql> source D:\backup\backup.sql

Give it a Try !!!


Pointing DB to new Oracle home in primary and standby oracle database

With Grid Infrastructure, you need to modify the configuration with srvctl, for example after upgrading to a new release:

srvctl stop database -d db_unique_name
export ORACLE_HOME=<..new home...>
export PATH=$ORACLE_HOME/bin:$PATH
srvctl upgrade database -d db_unique_name -o oracle_home

Alternatively you can just re-register the database (add back with srvctl from the new home):

srvctl remove database -d db_unique_name
export ORACLE_HOME=<..new home...>
export PATH=$ORACLE_HOME/bin:$PATH
srvctl add database -d dbname -o oracle_home ...

Without Grid Infrastructure, you can start your databases manually, or with a custom script. You need to modify these scripts and update ORACLE_HOME in them. Also, you need to update /etc/oratab if you are on a UNIX/Linux platform. This is also needed if you use the scripts provided by Oracle (dbstart and dbshut).

If you do not have any scripts for starting and stopping the databases, all you need to do is:

shutdown immediate
export ORACLE_HOME=<..new home...>
export PATH=$ORACLE_HOME/bin:$PATH
sqlplus / as sysdba
startup

Finally, do not forget to copy other configuration files from the old home (pfile/spfile, listener.ora, tnsnames.ora, sqlnet.ora, etc.).


Combine a generated series with each row from a select

You need to do a left outer join on the generate_series with an ON clause that matches the date of each report.

Without a schema or sample data it's a bit fiddly to cook up an example modification of your actual query.

Here's a simplified example to show you how it works:

CREATE TABLE sparse_dates(
    bakedgood text primary key,
    firstbaked date not null
);

INSERT INTO sparse_dates (bakedgood, firstbaked) VALUES
('tart', '2012-02-01'),
('baguette', '2012-02-01'),
('cookie', '2012-03-01'),
('macaron', '2012-08-01');

To get the number of baked goods first baked on a given month:

SELECT monthtimestamp, count(bakedgood)
FROM generate_series(
    (SELECT min(firstbaked) FROM sparse_dates),
    (SELECT max(firstbaked) FROM sparse_dates),
    INTERVAL '1' MONTH)
    AS monthtimestamp
LEFT OUTER JOIN sparse_dates ON (monthtimestamp = firstbaked)
GROUP BY monthtimestamp;

with output like:

     monthtimestamp     | count
------------------------+-------
 2012-02-01 00:00:00+08 |     2
 2012-03-01 00:00:00+08 |     1
 2012-04-01 00:00:00+08 |     0
 2012-05-01 00:00:00+08 |     0
 2012-06-01 00:00:00+08 |     0
 2012-07-01 00:00:00+08 |     0
 2012-08-01 00:00:00+08 |     1
(7 rows)


The value truncates because you have not specified a length for the convert to varbinary. Without a specific length you get the default which is 30.

select convert(varchar(max), convert(varbinary, '1234567890123456789012345678901234567890'));
select convert(varchar(max), convert(varbinary(max), '1234567890123456789012345678901234567890'));

Result:

123456789012345678901234567890
1234567890123456789012345678901234567890


It depends on the replication configuration. The two Replication Formats are Statement-based Replication and Row-Based Replication.

The default is Statement-Based logging.

If the Statement-Based Replication is used, the master writes SQL statements to the binary log and the SQL statements are executed on the slave.

In the case of Row-Based Replcation, the master writes events to the binary log that indicate how individual tables rows are changed. These events are then copied to the slave.

Another format is Mixed-Based Replication. MySQL replication can also be configured to use a mix of both Statement and Row-Based logging. Statement-based log is used by default, but depending on certain statements, the log is automatically changed to row-based.

Refer to binlog_format, to configure the logging format and for binary log setting more information.


MySQL FullText search on string shorter than 3 chars returns no rows

Setting ft_min_word_len only affects MyISAM.

You need to set innodb_ft_min_token_size to 1 since the default is 3.

Once you set innodb_ft_min_token_size to 1, go back and do

ALTER TABLE addresses DROP INDEX address_index
CREATE FULLTEXT INDEX address_index ON addresses(street);

Give it a Try !!!


How does performance of SQL Server CE and SQL Server Express compare?

For your case and reasons to upgrade

merge replication is available in SQL Server CE 3.5

Stored procedures, views, triggers is not available in SQL Server CE 3.5

for a basic comparison between the 2 editions in your case working on a net book

1.
SQL Server CE 3.5 can Runs as in-process with application
SQL Server Express runs as a service

2.
SQL CE supports maximum size of 4GB
SQL Express can go up to 10GB

3.
SQL CE Doesn't Support FILESTREAM
SQL Express Supports FILESTREAM

Procedural T-SQL - Select Case, If, features is not supported in SQL CE
Procedural T-SQL - Select Case, If, features is supported in SQL Express

5.
Distributed transactions is not supported in SQL CE but supported in SQL express

Native XML, XQuery/XPath is not supported in SQL CE but supported in SQL express

7.
Stored procedures, views, triggers is not supported in SQL CE but supported in SQL express

Role-based security is not supported in SQL CE but supported in SQL express

9.
concurrent connections supported in SQL CE is limited to 100 but unlimited in sql express

this is a useful blog focused on sql CE

for the comparison between the 2 in your specific environment you can use these tools

SQL LOAD TEST
SQL STRESS TEST TOOL

and check this post on SO about sql statment performance
https://stackoverflow.com/questions/1555471/what-tools-are-available-to-test-sql-statement-performance


This question is already being answered https://stackoverflow.com/questions/5376427/cant-connect-to-local-mysql-server-through-socket-var-mysql-mysql-sock-38:

  If your file my.cnf (usually in the /etc/mysql/ folder) is correctly configured with

socket=/var/lib/mysql/mysql.sock

  you can check if mysql is running with the following command:

mysqladmin -u root -p status

  try changing your permission to mysql folder. If you are working locally, you can try:

sudo chmod -R 755 /var/lib/mysql/

  that solved it for me


If you want a proper database then of the two you should go for innodb - it supports ACID transactions, referential integrity, and smaller granularity locking.

myISAM is faster in a number of read-only use cases but:

The lack of support for transaction safety can lead to corruption in multi-user situations where several updates happen at the same time unless you are very careful in your application design
The lack of foreign key support removes a valuable data integrity protection from your armoury
The fact the writes result in a full table lock can cause massive performance issues for use cases that involve much by way of insert and update operations.

innodb is currently the default selected if you do not specify a storage engine, unless you are running an old or badly configured instance of mysql (myisam used to be the default).

Update: as mentioned by akuzminsky in the comments, it may no longer be the case that myISAM is a better performer for any real world workload. A quick search for "innodb myisam benchmarks" seems to suggest this is the case.


SQL Server : replication between two different locations

Ok it was problem that Windows block cross domain connections.

Error in error log:
Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.

I had to make new local Windows account and used it for replicaton snapshot agent.


mysql ibdata1 file corrupted or increased unusually

Please keep in mind that ibdata1 is the system tablespace. It is home of the InnoDB Infrastructure. Here are the following classes of information stored in ibdata1.

Table Data Pages (if innodb_file_per_table disabled)
Table Index Pages (if innodb_file_per_table disabled)
Data Dictionary (Tablespace IDs, Logical-to-Physical Mapping to Tables)
Double Write Buffer (Handles Data Redundancy to Support Crash Recovery)
Insert Buffer (Handles Updates to Secondary Indexes)
Rollback Segments (Manages Reverse Engineering of Uncommitted Transactions)
Undo Space (Containers of Information for Rollback Operations)
Click to See Pictorial Representation of the InnoDB Infrastructure

As you can see, ibdata1 has a lot of moving parts. The fastest growing section of ibdata1 would be the undo space, especially when there are a lot of read transactions and a few write transactions.

Please read me past posts on doing InnoDB Cleanup:

Oct 29, 2010 : Howto: Clean a mysql InnoDB storage engine?
Nov 26, 2011 : ERROR 1114 (HY000) at line 6308 in file & The table user_analysis is full
Mar 25, 2012 : Why does InnoDB store all databases in one file?
Jun 19, 2012 : whether a big (but not used) ibdata1 slow anything down
Jan 07, 2013 : Database space doesn't match ibdata1 size


Some steps to reduce these vulnerabilities can be found here:

  Disable the login that is being attacked. In most cases, the login would be a generic sa or admin account. This will disable access to the account. However, the server may still be vulnerable to outside attacks.
  Change name of the account that is being attacked. For instance, change the sa account to sa_databasename in order to make it harder to guess a high level account name.
  Change the port from the default 1433 to a random port in order to make it less predictable to find the port that the server is listening to.
  Change the firewall settings to only allow know IP addresses to get through the security system.
  Implement a strong password policy and make sure that the passwords expire on a frequent basis.


Is there any limit for IN results in SQL Server?

It depends. Seriously. That’s why the docs can’t be specific about where you will notice degradation in your environment.

The solution is to just stop doing this (and worrying about it) and use a table-valued parameter.

If you have the values in C# in such a way that you can build a comma-separated list  as a string to concatenate together in a query, you have the values in C# in such a way that you could stuff them in a DataTable or other structure (maybe that's where they're coming from in the first place), and pass that structure as a parameter to the stored procedure.


REPLICATE() return type for VARBINARY input

I suspect that REPLICATE is expecting a string not a binary type, and an implicit conversion is taking place from VARBINARY to VARCHAR. If you update your example like so you get the expected type:

declare @vb nvarchar(10) = '#'
declare @res sql_variant = REPLICATE(@vb, 2)
SELECT sql_variant_property(@res, 'BaseType')

Even though it accepts a binary input, the spec does specifically state that parameter is a "string expression" which is why I suspect type coercion is happening before the function is actually run. By "expected type" I am referring to it not being fixed as VARCHAR irrespective of input type which might be implied when looking in your result on its own. So I think the documentation is correct, but perhaps not perfectly clear: the function returns the same [N][VAR]CHAR(<len>) type that is receives, and in your test it is receiving a VARCHAR() due to implicit conversion from VARBINARY to VARCHAR.


Can SQL Server 2016 R Services be used to connect to Hadoop data?

The fastest way to import data into SQL Data Warehouse is to use PolyBase...

https://azure.microsoft.com/en-gb/documentation/articles/sql-data-warehouse-overview-load/

I do not think the same recommendation exists for SQL Server 2016, but it still makes sense.  The two options are slightly different in that Polybase allows you to access Hadoop or Azure Blob storage directly and has been optimised for scale, eg PolyBase scale-out groups.   You write ordinary T-SQL to access these external tables which is one of the main advantages - no Map Reduce jobs for you!  Using R in SQL Server 2016, you would have to use stored procedure sp_execute_external_script to call these libraries, create the connection, and import the data into a dataframe.  You would then have to expose the dataframe back to SQL Server as an output resultset, then write it to a table, something like ...

--NB This is pseduo-code / will not execute!

INSERT INTO dbo.someTable ( ...
EXEC dbo.sp_execute_external_script
    @language = N'R',
    @script = N'
library(RxHDFSConnect)
OutputDataSet <- some code that allows RxHDFSConnect to get some data ...'
WITH RESULT SETS ( ( col1 INT NOT NULL, col2 FLOAT NOT NULL ... etc ) );

Just in terms of number of steps it feels more complicated.  I haven't done any scale testing on this but instinct tells me this is not meant for massive volumes.  Interestingly I could only find this page on importing data with RevoScaelR in googlecache.

What kind of use cases are you looking out, just out of interest?


MySQL not using index on large WHERE IN clause

I think there is an issue with large IN clauses and PARITIONing -- Beyond some not-so-large-number, the Optimizer say "punt, I'll just send this query to every partition".  (This may be part of what you are seeing.)

But more than that.  Picking partitions is costly, not cheap.  If you end up with all the partitions, the query will be slower than if you did not have partitioning.  (I recommend removing partitioning.  I have found no use case where BY LIST helps performance.)

Most importantly...  "Tuples" are not optimized.  That is, the Optimizer does a terrible job with WHERE (a,b) IN ((1,2), (3,4)), regardless of the indexes.  Don't use it if you want performance.  (Or you could plead for the optimization feature at bugs.mysql.com).)  (This is the real answer to the question.)

So, what to do?  One approach is to create a temp table with all those tuples.  Then JOIN from that tmp table to the real table and have a composite index (as you have with unique_index) to make the lookups fast.

Some other notes.

The number in INT(55) means nothing.  INT is always 4 bytes, with a range up to a few billion.  world int(3) should probably be TINYINT UNSIGNED (0..255) or SMALLINT UNSIGNED (0..65535).
"Prefix" indexing is rarely beneficial:  KEY player_index (player(8)); remove the (8).
KEY date_index (date) is redundant since there is another index (unique_index) starting with the same column.
Recommend moving to InnoDB before MyISAM is deprecated.


Does MySQL support defining a table inside a session without appending it to the database?

You are right it is a like a local table to the connection.
According to the link you have under the subheading Temporary Tables, it says:

A TEMPORARY table is visible only within the current session, and is dropped automatically when the session is closed

Simply put, once the DB Connection terminates (normally or abnormally), the table disappears.
Also, please be aware that only the DB Connection that created the temporary table can access it. If 10 different DB Connections perform CREATE TEMPORARY TABLE, each DB Connection would have its own table. No other DB Connection can access it.


The two nested selects in your query are called derived tables. A derived table is not meant to be correlated with other datasets participating in the query, hence outer references to them in the nested query are not allowed.

One way to resolve the issue is to rewrite your query so as to move the offending select to the context where correlation is allowed. In your case you can move the offending subquery to the SELECT clause:

select    t.number,
          DATE(p.date),
          ti.total 'total',
          SUM(p.amount) 'paid',
          ti.total - (select sum(inner_p.amount)
                      from     payments inner_p
                      where    inner_p.transaction_id = p.transaction_id
                      and      inner_p.date <= p.date
                     ) 'balance'
from      payments p
left join transactions t
on        p.transaction_id = t.id
left join (
          select   inner_ti.transaction_id,
                   sum((inner_ti.price - inner_ti.discount) * inner_ti.quantity)  'total'
          from     transaction_items inner_ti
          group by inner_ti.transaction_id
          ) ti
on        t.id = ti.transaction_id
group by  t.number, DATE(p.date), ti.total, 'balance'
order by  DATE(p.date) ASC;

rextester here

For the sake of completeness, the SQL standard actually has syntax that allows correlation for derived tables. It is called lateral join. From the syntactical point of view, it looks almost exactly like a normal join, you just need to add the LATERAL keyword after JOIN:

…
left join lateral (
    select inner_p.transaction_id, sum(inner_p.amount) 'total'
    from payments inner_p
    where inner_p.date <= p.date -- this outer reference would be valid
    group by inner_p.transaction_id
) paid on t.id = paid.transaction_id
…

The added keyword makes all the difference, as only with that keyword a nested query is permitted to reference other datasets in the same FROM clause (to the left of the most recent JOIN keyword).

Lateral joins are currently supported by PostgreSQL and Oracle. A similar concept with a slightly different (and less flexible) syntax is also supported by SQL Server. As you may have guessed, MySQL does not currently support anything of the sort.


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

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

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

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


PostgreSQL behaviour in presence of two different type of indexes on the same column

An index is going to be used if it's part of the fastest plan the query planner can come up with. Many factors are involved in the cost estimation. Conditio sine qua non: the index must be applicable. Indexes are bound to operators, not data types or functions or anything else. And operators are defined by the operator and data type of left and right operand. The manual about btree indexes:

  B-trees can handle equality and range queries on data that can be
  sorted into some ordering. In particular, the PostgreSQL query planner
  will consider using a B-tree index whenever an indexed column is
  involved in a comparison using one of these operators:

<
<=
=
>=
>

There is more, read the manual.

Related case with more explanation:

Can PostgreSQL index array columns?

A btree index on tsvector is not useless. It can be used to sort rows (implicitly using >= or <= operators):

SELECT * FROM tbl ORDER BY my_tsvector;

Or it can be used for equality predicates:

DELETE FROM tbl WHERE my_tsvector = 'foo bar'::tsvector;

But it cannot be used for a predicate my_tsvector @@ '...'::tsquery. You need a GIN or GiST index for that. Both use the the operator class tsvector_ops by default (no need to declare it explicitly):

https://www.postgresql.org/docs/current/static/gin-builtin-opclasses.html
https://www.postgresql.org/docs/current/static/gist-builtin-opclasses.html

Then the index is applicable. But Postgres may still decide to use a different query plan, based on table statistics and cost settings ...

You can have many indexes involving the same index columns. There are various kinds, types and various operator classes, which can be combined in many ways. The query planner will only consider applicable indexes and estimate the cost based on many factors ...

And you can even create the same index any number of times under different names. Then Postgres will take an arbitrary pick. It's your responsibility not to create pointless, duplicated objects.


Linux Error: 29: Illegal seek in LSNRCTL for Linux Version 11.2

I was having the exact same problem, searched a lot and tried about everything suggested (like setting /etc/hosts correctly).

In my case (OEL6, 11gR2 XE) it was solved with the following:

export LD_BIND_NOW=1


What to do when your Always On cluster loses quorum?

AGs are based on Windows Clustering. The WSFC procedures for quorum loss apply.

WSFC Disaster Recovery through Forced Quorum
Force a WSFC Cluster to Start Without a Quorum

Once the WSFC is running, you can then force AG, if needed. Perform a Forced Manual Failover of an Availability Group:

  After forcing quorum on the WSFC cluster (forced quorum), you need to force failover each availability group (with possible data loss). Forcing failover is required because the real state of the WSFC cluster values might have been lost. However, you can avoid data loss, if are able to force failover on the server instance that was hosting the replica that was the primary replica before you forced quorum or to a secondary replica that was synchronized before you forced quorum. For more information, see Potential Ways to Avoid Data Loss After Quorum is Forced.


Migrate existing data when altering the column type from string to json

Rather than making up any keys, why not convert that to a json array?
alter table the_table
  alter column the_column type jsonb
  using to_jsonb(string_to_array(the_column, ':'));

That would turn str1:str2 into ["str1", "str2"]
If you really need to make up some (numbered keys), you will have to write a helper function:
create function make_json_keys(p_input text)
returns jsonb
as
$$
  select jsonb_object_agg('key'||idx, val)
  from unnest(string_to_array(p_input, ':')) with ordinality as t(val, idx);
$$
language sql
immutable;

That can be used in the USING expression:
alter table the_table
  alter column the_column type jsonb
  using make_json_keys(the_column);


The idea of having multiple instances on one server can have various reasons.

1. SQL Server License Costs

You can have multiple instances all running on one (big?) server and not have to pay large amounts of licensing fees.

  Disclaimer
  This depends on your licensing model, your license agreement and probably requires additional clearance by your certified reseller / licensor. Do not take this statement for granted. Your costs/licenses may vary. No guarantee given.

2. Upgrades / Service Packs / Cummulative Updtes / Hotfixes

Having multiple instances on one (big?) server, allows you to patch the individual instances to different levels. You can have once instance running Service Pack 1 and another instance running Service Pack 2. And on another instance you could even test drive the hotfix you're not sure it will work well.

3. Permissions / SQL Server Roles / ....

Having multiple instances allows you to assign the sysadmin server role to individuals, without giving them the ability to destroy all the databases if they would be located on one instance. You have a separation between databases of one customer and another customer.

4. Windows Server Licenses

You can save on Windows Server licenses, because you only require one server license for multiple SQL Server instances.

  Disclaimer
  This depends on your licensing model, your license agreement and probably requires additional clearance by your certified reseller / licensor. Do not take this statement for granted. Your costs/licenses may vary. No guarantee given.

5. Individual IP Addresses for Each SQL Server Instance

You can setup the individual SQL SErver instances to only respond to one IP address which in turn is linked to a CNAME/ALIAS. e.g. you could have your Windows Server running on 10.0.0.1 and each of your SQL Server instances running on 10.0.0.2 ... 10.0.0.5 which in turn would have their own CNAME:

10.0.0.1   hostname
10.0.0.2   hostanme-alias1
10.0.0.3   hostname-alias2

Each instance is then only reachable via the IP address and Port 1433 which in turn allows for IP to IP firewall configuration.

See my question Configure SQL Server Analysis Services (SSAS) to run on same IP as SQL Server instance for an example of how you could configure multiple instances. Or the question Multi-instance SQL Server Standard Editon MaxDop settings where I show how we setup multiple insances at our shop.


Firebird has two distinct options for monitoring:

The monitoring tables, which provide a snapshot of activity on the server. For example MON$STATEMENTS provides the currently running statements. Note that content of monitoring tables are stable within a transaction, so to refresh it, you will have to commit and start a new transaction.
The trace facility, which allows you to get information on activity in a database. It can be either be configured by an administrator to run server side, or users can do limited tracing of their own sessions remotely (users with administrator rights have more access). For details see Trace and Audit Services in the Firebird 2.5 release notes, and the article Audit and Trace Services in Firebird 2.5 by Thomas Steinmaurer.

Tooling included with Firebird is rather basic and low level, and you may want to look at third party tools that simplify access to this trace facility.

In addition, there are tools that act like a proxy for Firebird, allowing you to trace activity that way, for example FBScanner.

See also my answer on How to capture Firebird SQL queries? on Stack Overflow (as you already found).


Calculate products count based on category tree table

Use a recursive CTE:

WITH RECURSIVE cats AS (
   SELECT 1::bigint AS id
 UNION
   SELECT c.id
   FROM category AS c
      JOIN cats ON c.parentid = cats.id
)
SELECT count(*)
FROM product
   JOIN cats ON cats.id = product.categoryid;

This query uses category id number 1 as an example. Replace it with the appropriate category id or use a parameter instead.


How can I design a table with greater than in where statement and ordering to avoid filesort?

Can't be done.
To achieve the goal, you would need a 2-dimensional index.  Such does not exist.
WHERE
    category_id = 30
    AND created_at > 1592862179

That much is handled nicely with INDEX(category_id, created_at).  But adding anything onto the end of the index definition will not benefit you
The only way to get the index to be also useful for the ORDER BY is to have ORDER BY created_at (either ASC or DESC).  At that point, it can stop after 10 rows (because of LIMIT 10).
More on indexing:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql


Creating dummy from JSON values and using it in the WHERE clause

I've conceived an unelegant but working approach using a subquery:

SELECT COUNT(*) as has_defaulted

FROM (
SELECT answer::json->>'value' AS resp
_FROM CLAUSE HERE_
WHERE CONDITION 1
  AND "code" = 'hasDefaulted' ) as subquery
  GROUP BY subquery.resp
  ORDER BY subquery.resp ASC
  LIMIT 1)


How do you query supertypes to get subtype data?

Unless you use dynamic SQL to build the query (which adds no value anyway) then SQL is a "fixed column" language. That is, you'd have to send all columns you need. This example is bad in that you have all columns but you get the idea.

SELECT
   V.*,
   C.*,
   B.*
FROM
   Vehicle V
   LEFT JOIN Car C  ON V.VEH_ID = C.CAR_ID
   LEFT JOIN Boat B ON V.VEH_ID = B.BOAT_ID

Another option is to hide the VEHICLE-CAR join behind a view "CARS" and VEHICLE-BOAT join behind another view "BOATS". However, you can't UNION them as the have different columns and types

Basically, you don't query supertypes/subtypes like this because each subtype gives a different resultset.


SQL Server: Interplay of personal login and group membership

SQL Server will deny access to a login that has been disabled. This is by design. If it is your intention to allow the person access through the group login, you should simply remove the login that is linked to the Windows account.

SQL Server limits principals' rights such that any rights that have been explicitly denied override rights that have been explicitly or implicitly granted.  For instance, if a Windows Account has a login, and is also a member of an Active Directory group that also has a login, the set of rights granted at connection time actually reflect the rights for both the Account login and the Group login.  If the group login permits access to a specific resource, but the account login denies access to that resource, then access to that resource is denied.

You may be interested in seeing the SQL Server Database Engine Permissions Poster.


Changing MySQL Master in Replication: with or without MASTER_LOG?

Assuming that no one can write (right now, before the change) in servers B and C you could try this:

First, lock writes on Server A, so there's nothing to replicate in the Slaves.

Server A (You'll lock all tables, NOTE: You should not exit this CLI, or it'll release the lock):

mysql> FLUSH TABLES WITH READ LOCK;

Wait to Server B and Server C are up-to-date with the replication, check that both server are in the same Master Log File and Master Log Position!

Server B:

mysql> SHOW MASTER STATUS;

From that one you can get your new MASTER_LOG_FILE and MASTER_LOG_POS.

Server C:

mysql> STOP SLAVE;

So you can restart your slave from its new master. Then you can UNLOCK your tables in Server A (In the CLI that you had before):

mysql> UNLOCK TABLES;

Now the only thing left is to do your CHANGE MASTER on Server C with the new MASTER_LOG_FILE and MASTER_LOG_POS that you got from Server B and everything should work fine.

IMPORTANT: You should check that Server B is running with log_slave_updates = TRUE and that you have log-bins too, since you'll need them for it to be a new Master.


MySQL Workbench secretly running SHOW INDEX after every query

Scanning the source code of MySQL Workbench you can see a few places where SHOW INDEX is sent out. The application needs to run quite a number of queries for its tasks, e.g. the index view or the table editor. Some of them are only active when you open a view from the management section. Others, like the SQL editor need a SHOW INDEX to find info about PK columns which is needed to decide if a resultset returned for a query can be edited.


How to pass the input parameters to dynamic Pivot stored procedure

I'm passing input parameter in_branchId into pivot Mysql Query

DROP PROCEDURE IF EXISTS `leavemanagement`.`SP_ViewAttendance` $$
    CREATE DEFINER=`root`@`localhost` PROCEDURE `SP_ViewAttendance`(

              IN in_attendance_start_date DATETIME,
        IN in_attendance_end_date DATETIME,
        In in_branchId varchar(100)
    )
    BEGIN
          SET SESSION group_concat_max_len = 1000000000;

          SET @sql = NULL ;
      SELECT
        GROUP_CONCAT(
          DISTINCT CONCAT(
            'max(CASE WHEN attendance.date = ''',
            DATE_FORMAT(DATE, '%Y-%m-%d'),
            ''' THEN coalesce(p.present, '''') END) AS `',
            DATE_FORMAT(DATE, '%Y-%m-%d'),
            '`'
          )
        ) INTO @sql
      FROM
        calendar ,attendance
      WHERE DATE >= in_attendance_start_date
        AND DATE <= in_attendance_end_date
          AND attendance.branch_id = in_branchId;

      SET @sql = CONCAT(
        ' SELECT  ', @sql,'
            from
                (
                   select c.date,  a.user_id ,a.user_name,a.attendance_id,a.attendance_date
                     from calendar c
                     cross join attendance a
                ) attendance
                left join attendance  p
                  on attendance.user_id= p.user_id
                  and attendance.date = p.attendance_date

               where

                      attendance.date>= date
                  and attendance.date <= date
                  AND p.branch_id = ',in_branchId,' GROUP BY attendance.user_id') ;

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;


Your understanding is correct.  It does get a little confusing.

Kim Tripp (one of the programmers of SQL Server and a integral part of SQLSkills) goes through exactly what you stated in the MCM videos on Snapshot Isolation.  Fast fwd to 41:45 in the video to get to the part where she answers your question.

If you use ALLOW_SNAPSHOT_ISOLATION make sure you use SET TRANSACTION ISOLATION LEVEL SNAPSHOT in your code, otherwise you will not get any of the benefits.

If you set SET READ_COMMITTED_SNAPSHOT ON, then there is no need to modify any code.  MS SQL Server automatically applies snapshot isolation for that table.

I haven't tested to see what happens if you ask for a different isolation level in your code, I suspect it will overwrite this option but test it first.

A quick look at performance overhead using Snapshot Isolation.

Good article on how snapshot isolation can change the expected behavior of your app.  It shows examples of how a update statement and a select statement might return totally different and unexpected results.


You can only GRANT to a PostgreSQL role.  If you have created your own role system at the application level, storing the data about them in the database, PostgreSQL has no idea that you did that and certainly isn't going to grant PostgreSQL privileges to these roles.

You either need to convert the users listed in "myeg_users" into PostgreSQL users/roles, or you need your app server to connect as PostgreSQL user "role1" and then to voluntarily do only those things the app-side role it is implementing is allowed to do.


does max_connect_errors not check Aborted_connects?

max_connect_errors is on a per-host basis, so I doubt that it checks a status variable such as Aborted_connects.

This variable probably does not apply if you are testing using 'localhost' (which does not use TCP), for what happens if you lock localhost out? No one could connect (assuming a single root user and not being able to log in root remotely)!

So I felt like digging into the source on this one. In the file 'sql/hostname.cc' is a function ip_to_hostname. This function has the following comments (emphasis mine):

  Resolve IP-address to host name.

  This function does the following things:
      - resolves IP-address;
      - employs Forward Confirmed Reverse DNS technique to validate IP-address;
      - returns host name if IP-address is validated;
      - set value to out-variable connect_errors -- this variable represents the
        number of connection errors from the specified IP-address.

  NOTE: connect_errors are counted (are supported) only for the clients
    where IP-address can be resolved and FCrDNS check is passed.

There is also this comment with regards to localhost (or loopback ip like 127.0.0.1):

*connect_errors= 0; /* Do not count connect errors from localhost. */

Furthermore, the only time connect_errors is incremented is in the function 'inc_host_errors' of the same sql/hostname.cc file. This is only called from the sql/sql_acl.cc file. It looks like these are some of the situations:

1) There is an error with the packets (packet lengths don't match). This is also probably why the documentation says:

  If you get the Host 'host_name' is blocked error message for a given host, you should first verify that there is nothing wrong with TCP/IP connections from that host. If you are having network problems, it does you no good to increase the value of the max_connect_errors variable.

2) Invalid native password (function is native_password_authenticate)

3) Invalid old-style password (function is old_password_authenticate)

I might have missed a few things, but ultimately, max_connect_errors has nothing to do with aborted connections, but rather networking issues or invalid passwords.

For completion sake, I will mention I was looking at the Percona 5.5.15-rel21 source code.

So, what does Aborted_connects have to do with it? Well, according to this documentation, it gets incremented anytime:

A client does not have privileges to connect to a database.
A client uses an incorrect password.
A connection packet does not contain the right information.
It takes more than connect_timeout seconds to get a connect packet.

So, Aborted_connects is just an indicator for a DBA to know there might be a problem (eg: if it increases dramatically over a period of time, someone might be attempting to hack into the server, but you should be protected by max_connect_errors in this case).

If yours increases steadily from the healthchecks, you can still use it if it spikes abnormally.


Integrity constraints in a relational database - should we overlook them?

If, as stated in your post, the intention is to create a relational database (RDB for brevity) and, therefore, it is expected that it functions as such, the short answer is:

No, you should not overlook data integrity constraints.

The primary objective should be to manage the pertinent data as it is: a quite valuable organizational asset, and a reliable manner to achieve said objective is employing technical means that are supported on sound theory.
Thus, as a database professional, you can take advantage of the state-of-the-art and elegant relational model mechanisms supplied by Dr. E. F. Codd to enforce business rules, and avoid the (technical and organizational) problems that would eventually arise if they are not utilized.
In this respect, I will share (a) my overall take on constraints and also (b) several considerations about the state of affairs of the database and the work environment at issue as follows.
FOREIGN KEY constraints, data relationships and referential integrity
A RDB must reflect the characteristics of the business context of interest with high accuracy, which definitely requires an in-depth conceptual-level analysis led by a modeler or designer who follows best practices, counting with the indispensable assistance of the business experts. That analysis must yield the correct identification and formulation the applicable business rules.
Consequently, if such a modeler has identified that there exist interrelationships between the data of relevance, he or she must configure the corresponding logical-level restrictions so that the database management system (DBMS) can guarantee that the data remains consistent with the exact characteristics and rules determined in the analysis referred to above at all times.
Regarding the database under discussion, one can infer that the pertinent interrelationships have been identified, since you mention that there is a procedural (and easy to circumvent) attempt to enforce them from outside of the DBMS facilities, by dint of application program code (which is a pre-relational approach) that in any case has to “touch” the database to try to validate the wholeness of said interrelationships.
However, as you know, that is not the optimal technique to protect referential integrity, because the relational science has prescribed a very powerful instrument for this purpose, i.e., FOREIGN KEY (FK) constraints. These constraints are very easy to create (via the superior declarative approach) as they are single sentences that avoid resorting to unnecessary and error prone ad hoc procedures. It is very useful to note that the execution speed of FK constraints has been highly optimized by specialized programmers (and the major platform vendors have worked on it for even decades now).
Furthermore, since a RDB must be an independent (self-protective, self-describing, etc.) software component that is capable of being accessed by multiple application programs (desktop, automatic, web, mobile, combinations thereof), it should not be “coupled” with the code of any of these apps.
Likewise, the data —being an significant organizational resource— naturally tends to outlive application programs, application programmers, application development platforms and programming paradigms.
PRIMARY KEY constraints and implications of duplicate rows
When —conceptually speaking— a particular kind of thing has been deemed of significance in a business environment, a database modeler has to (1) determine its relevant characteristics —i.e., its properties—, confirm said kind of thing as an entity instances prototype —i.e., an entity type— and (2) represent it by way of a table that is integrated by one or more columns in a logical design.
Then, just like it is paramount to distinguish each individual instance of a given entity type in the real world business, each corresponding row enclosed in a table must be uniquely distinguished as well. If a table does not have any KEY declared, it will eventually retain duplicates, and if there are two or more rows that retain exactly the same values, then they all carry the same meaning, they all represent the same fact.
On that point, duplicate rows should be discarded due to multiple reasons. From a theoretical perspective, the designer has to make sure that each row is always unique for the purpose of having tables that work as relationally as the SQL data sub-language permits (having important repercussions on data manipulation operations). Besides, from an informational perspective, if multiple rows represent the same fact, recording them is not only superfluous but harmful, as exemplified bellow:

Suppose that someone has inserted two identical rows in a certain table.
Later, someone else comes and updates only one occurrence of the duplicates. As a consequence, the other occurrence is not up-to-date anymore.
Successively, another person updates the occurrence that had not been modified so far. In this manner, both duplicates have undergone different changes at distinct points in time.
After that, when someone is interested in selecting the information conveyed by the rows in question, he or she can find two different “versions” of it.

In this way:

Which “version” can be considered the correct, reliable one?
Which one reflects the real world accurately?

As you know, this phenomenon can even have legal implications, a circumstance that surely is of enormous importance.
Besides, the time and effort that has to be employed to handle such contradictions (perhaps through some kind of “update synchronization”) should better be devoted to tasks that actually produce value for your organization. So, retaining contradictory rows ought to be avoided by design to keep the consistency of a database intact.
That is why the identification of a PRIMARY KEY (PK) and the declaration of the respective constraint should always be performed by the database designer. But it must be mentioned too that a table may have more than one column or combination of columns that hold values that uniquely identify every row; as a consequence, besides setting up a PK constraint (ideally established as PRIMARY due to pragmatical reasons), the designer must as well declare one or more ALTERNATE KEYs (usually defined via one or more UNIQUE plus NOT NULL constraints) when applies (which is pretty common).
Another advantageous property of PKs is that, when “migrated” to other tables to take part in single or composite FKs, they can help to enforce the cardinality ratios of the relationships/associations that exist among the data. All this, yes, by means of simple and efficient declarative settings, ensured by the DBMS invariably.
(Current) CHECK constraints and single-row validation
Let us not forget about the relevance of (current) CHECK constraints that, restricting declaratively the valid set of column values of a row (which may appear simple, but is in fact an fundamental feature of a relational DBMS), help as well to make certain that the rules of the business context are reflected with precision always.
As you marked your question with the MySQL tag, starting with version 8.0.16 (see also this MySQL server team blog post) such a platform finally! enforces this type of constraint. In this regard, it must be mentioned that in all its prior versions this DBMS did permit declaring CHECK restrictions, but ignored its enforcement!, situation that, understandably, was reported as a bug since 2004.
So, if using legacy versions, you would have to take care of this factor by other means, e.g., ACID TRANSACTIONS, TRIGGERS, or other methods within the DBMS itself (see this answer by @ypercubeᵀᴹ
 for information on this subject) so that the data continues to be consistent.
ASSERTION constraints: setting up further multi-row and multi-table business rules declaratively
One aspect that for whatever reasons is very poorly supported —if at all— by the different SQL DBMSs, including MySQL, is enabling multi-row and multi-table constraints in a declarative fashion —beyond PKs and FKs, evidently—.
For its part, the SQL standard has included ASSERTIONs from many years now. I do not know what rules of your business environment would benefit from that logical-level validation approach but, as a database designer, I consider that it would be pretty handy to constraint data with one or more ASSERTIONs when required, although I have to mention that from the point of view of the DBMS developers, this paramount kind of tool has been difficult to implement at the physical level of abstraction.
It appears that the Oracle vendor and/or developers are evaluating ASSERTION support since 2016, and that would make that DBMS more relationally-compliant and, hence, more robust and competitive. I guess that, if (i) their consumers keep pushing and (ii) Oracle succeeds in the implementation, then (iii) other DBMS vendors/communities will have to enable them too, and their usage will start to spread. Certainly, that would be a huge progress in the data management field, and being one of the most distinctive tools envisioned by Dr. Codd, I personally hope that we will see that happening soon.
Data consistency and the decision-making process
As discussed above, one of the most important aspects of a RDB is that it guarantees by itself the consistency of the data it retains, and said consistency is only met when the RDB complies with the integrity constraints declared by the modeler.
In this respect, it is compulsory to have base tables (those established in a DDL structure) which integrity is protected in order to be able to create derived tables (e.g., a SELECT statement or view that retrieves columns from multiple tables) that are trustworthy, because derived tables have to be produced necessarily in terms of base tables.
It is pretty obvious that people use information as the main tool in the organizational (and in the ordinary) decision-making process. Then, if the information presented by a database is not coherent and accurate, the decisions based on such information will not be sound (to say the least). That is why a RDB must be carefully designed and implemented: it should be built to become a reliable resource that can assist its users to make well-founded decisions.
“Denormalization”
Alas, “a ‘denormalized’ database is faster than a normalized one” is a widely spread misconception, although it is also an “argument” that can be refuted on logical, physical and pragmatical grounds.
Firstly, denormalization implies necessarily that a base table has been previously normalized (by virtue of a formal, science-based, procedure fulfilled at the logical level of abstraction of a database).
So, assuming that said table was in actual fact normalized correctly, “denormalizing” it (which, in contrast to the formal meaning of the word, involves appending to it columns that belong in, and are also part of, other tables in an ad hoc fashion) might aid, e.g., to speed up (at the physical level) the processing of only one or a few particular SELECT statement, while such course of action might, at the same time, be undermining the execution of many other associated data manipulation operations (e.g., several INSERT, UPDATE, DELETE and SELECT statements, or combinations thereof enclosed within a single or multiple ACID TRANSACTIONS).
In addition, denormalization (be it formal or informal) would introduce update/modification anomalies that deteriorate the coherence of the database, a problem that “may” be handled by complex, costly and error-prone procedures, when all this can be prevented from the very beginning.
Physical-level scaffoldings supporting normalized and “denormalized” tables
A logical (abstract) layout (SQL-DDL design) that is meant to be utilized in the real world clearly holds physical (concrete) repercussions that must be considered.
In this manner, a “denormalized” table would necessarily be “wider” (holding additional columns) which means that its rows would necessarily be heavier (requiring more and larger physical-level components), so that means that the underlying computing processes (e.g., those that have to do with the hard drive or memory) can easily turn slower.
In contrast, a normalized table that is of course “narrower” (having less columns) would be a “lighter” element (served by less and smaller physical components) that “behaves faster”, which would speed up the series of actions related to, e.g., data writing and reading.
That being so, it is very convenient to (a) normalize the relevant tables formally and prudently, keeping them as such, and then (b) to make use of any physical level resource that can optimize data retrieval and modification speed, e.g., implementing a careful and efficient indexing strategy, enabling proper software and hardware server configurations, upgrading network bandwidth capabilities, etc.
The functioning of the database under consideration and approaching your colleagues
The following paragraphs of your question have to do with the speed of the data retrieval operations:

[A]s the product “works”, there is hesitation to enhance the database; nevertheless, the first thing I noticed is one page taking 1 minute to load (yes, 60 seconds!).

If loading a certain page takes that long, it is evident that the users of the system are not receiving an appropriate service; therefore, even when it “works”, its functioning does not seem to be optimal at all, point that demonstrates that your intentions to make the entire computerized information system  (database and apps) more efficient are well sustained, and shows a very constructive attitude.
Then, even when science definitely supports you and hence you should maintain a firm posture, I suggest approaching the situation in a diplomatic way, since at the end of the day, your employers, coworkers and yourself are JOINing efforts in order to make the whole organization more successful. Thus, that is one argument that you should stress, that, while they are doing other things more than well, improving general and specific data management practices can considerably help in producing more organizational and individual growth.

Most of the relevant queries include JOIN operations, which makes them run very, very, very slow with large amounts of data (the database contains millions of rows).

It is worth to note that the JOIN operator is an essential and powerful element that pertains to relational manipulation of data. Then, although more robust platforms serve it with comparatively faster executions, the circumstance you describe is most probably a symptom of a non-eficient design (at the conceptual, logical and physical levels of abstraction). So, my first sight estimations are:

The INDEX settings may require improvement.
The PK and FK column type and size definitions need to be reviewed (and I totally agree with @Rick James regarding his PK considerations, as composite KEYs tend to be much more efficient than appended surrogates in the appropriate cases).
Further (formal, science-based) normalization might help to alleviate these problems, on account of the fact that, in the right circumstances (i.e., carried out in a well-designed RDB), JOINs are executed very fast.

Moreover, yes, as @TommCatt mentions in his answer, sometimes a (logical) rewrite of a query modifies its (physical) execution plan accelerating data reading/writing, which is a factor that should decidedly be taken into account.


Postgresql upgrade generated vaccumdb script not vacuuming?

That's not a VACUUM script, it is an ANALYZE script. That's why it isn't vacuuming. The confusion arises because vacuumdb can also be used for ANALYZEing a database.

After pg_upgrade you have no table statistics at all, so most queries will perform very badly. That's why it is essential to mend that as soon as possible.

Since pg_upgrade copies (or links) the table files, there will be as many dead tuples after the upgrade as there were before. That is not alarming, and autovacuum will take care of it in due course.


The answer depends quite a bit on what you want to do.  One option is EAV as one of the notes pointed out.  Another option is what I call EAV-lite which is to have stored procedures for adding columns to an additional "extended attributes" table.  This gives you something of a fixed schema with extended attributes.  If you need to add something else you can do it.  Of course you need EAV-like catalogs to tell you which attributes to search on, which is why I call it EAV-lite.

What we did in LedgerSMB was to create two additional tables for this catalog info: custom_table_catalog and custom_field_catalog for tracking these extensions.  Then the extends_* tables hold these extended attributes horizontally.

If you were on Postgres you could use hstore for these extended attributes too.  In 9.1 you could even use JSON.  Of course care needs to be taken in breaking the standard normalized approach here.  I would recommend EAV-lite more likely.

Edit: adding sample code.  Note that this works on PostgreSQL but on MySQL you will need to rewrite the functions as stored procs and possibly some other things.

CREATE TABLE custom_table_catalog (
table_id SERIAL PRIMARY KEY,
extends TEXT,
table_name TEXT
);

CREATE TABLE custom_field_catalog (
field_id SERIAL PRIMARY KEY,
table_id INT REFERENCES custom_table_catalog,
field_name TEXT
);

CREATE OR REPLACE FUNCTION add_custom_field (table_name VARCHAR, new_field_name VARCHAR, field_datatype VARCHAR)
RETURNS BOOL AS
'
BEGIN
        perform TABLE_ID FROM custom_table_catalog
                WHERE extends = table_name;
        IF NOT FOUND THEN
                BEGIN
                        INSERT INTO custom_table_catalog (extends)
                                VALUES (table_name);
                        EXECUTE ''CREATE TABLE '' ||
                               quote_ident(''custom_'' ||table_name) ||
                                '' (row_id INT PRIMARY KEY)'';
                EXCEPTION WHEN duplicate_table THEN
                        -- do nothing
                END;
        END IF;
        INSERT INTO custom_field_catalog (field_name, table_id)
        values (new_field_name, (SELECT table_id
                                        FROM custom_table_catalog
                WHERE extends = table_name));
        EXECUTE ''ALTER TABLE ''|| quote_ident(''custom_''||table_name) ||
                '' ADD COLUMN '' || quote_ident(new_field_name) || '' '' ||
                  quote_ident(field_datatype);
        RETURN TRUE;
END;
' LANGUAGE PLPGSQL;
-- end function

CREATE OR REPLACE FUNCTION drop_custom_field (VARCHAR, VARCHAR)
RETURNS BOOL AS
'
DECLARE
table_name ALIAS FOR $1;
custom_field_name ALIAS FOR $2;
BEGIN
        DELETE FROM custom_field_catalog
        WHERE field_name = custom_field_name AND
                table_id = (SELECT table_id FROM custom_table_catalog
                        WHERE extends = table_name);
        EXECUTE ''ALTER TABLE '' || quote_ident(''custom_'' || table_name) ||
                '' DROP COLUMN '' || quote_ident(custom_field_name);
        RETURN TRUE;
END;
' LANGUAGE PLPGSQL;

I don't know if MySQL allows for alter table statements to be done dynamically, so worst case scenario you might have to maintain these functions in the app layer.

The application then selects the contents of custom_table_catalog and custom_field_catalog and caches these in memory.  When an item is saved or retrieved, these are then used to create SQL CRUD functions.  Currently they are used for orders, invoices, goods, and services.


How to create a csv with SQLCMD which can be imported into another database system

If you are open to using Powershell...

Assume the following table definition:

--Demo setup
DROP TABLE IF EXISTS BillingData;
go
CREATE TABLE BillingData
    (BillingId int, Feature varchar(13), Usage int, Measurement varchar(9), Cost int)
;

INSERT INTO BillingData
    (BillingId, Feature, Usage, Measurement, Cost)
VALUES
    (1, 'Walks', 25, 'walks', 200.32),
    (1, 'Baths', 5, 'baths', 251.32),
    (2, 'Litter change', 53, 'changes', 110.21),
    (2, 'Groom', 25, 'brushings', 123.00),
    (2, 'Scratching', 213, 'clipping', 123.00),
    (3, 'Pilling', 11, 'medicate', 10.00),
    (4, 'Groom', 5, 'brushings', 50.00),
    (4, 'Exercise', 1, 'run', 25.12),
    (1, 'Walks', 500, 'walks', 12351.31),
    (1, 'Baths', 53, 'baths', 1235.00),
    (2, 'Baths', 53, 'baths', 1235.00)
;

Using Powershell:

$OutFile = "c:\test\testout.txt"
Invoke-SqlCmd -Query "select * from test.dbo.billingdata" -ServerInstance . |
ConvertTo-CSV -NoTypeInformation |
Select-Object | % {$_ -replace '"', ""} | Out-File $OutFile -Force -Encoding ascii

Results of output file:

BillingId,Feature,Usage,Measurement,Cost
1,Walks,25,walks,200
1,Baths,5,baths,251
2,Litter change,53,changes,110
2,Groom,25,brushings,123
2,Scratching,213,clipping,123
3,Pilling,11,medicate,10
4,Groom,5,brushings,50
4,Exercise,1,run,25
1,Walks,500,walks,12351
1,Baths,53,baths,1235
2,Baths,53,baths,1235


Select max value from many to many tables

The question would have been a better candidate for stackoverflow.

Anyways, the problem with your SQL is that the GROUP BY should only be on the author name and not on author name + book title.

From the GROUP BY on author name, get the max(number_of_pages) and wrap it inside another SELECT to to get the books by that author that have that those number of pages.

You would also have to consider the possibility that multiple books can have the same number of pages and if that should result in all of them being returned or just 1.


Use of IMPERSONATE permissions in SQL Server?

Personally I use impersonation for three major categories of tasks.

Testing
If I need to test what access someone has I can impersonate them, try out the task, and see if it works.  This is particularly useful when I've granted permissions but the user is still telling me they can't perform a given task.

Collecting information
There are a number of system views/functions that give you information about the connecting principals permissions (even some AD information).  As an example if I impersonate a database principal (user) and query sys.user_token I can get a list of all AD groups they are a member of and which ones give them access to the current database.

Granting access to a task without granting the permission
A specific example here is the ability to truncate a table.  In order to truncate a table you have to have the ALTER permission on the table.  I want to let some truncate that table but I don't want to risk them making changes to it.

Create a database principal (user) that has alter on the table
Create a stored procedure that does the TRUNCATE and uses EXECUTE AS to cause the SP to run as the user I created.
Grant execute permissions execute to the stored procedure.

You can even use this technique to grant sysadmin level permissions although it does have it's own difficulties and risks.

Edit:

I can see two possible reasons why you might grant someone impersonate rights.

Separate application permissions from direct access permissions

ApplicationA requires that user Joe have access to read from any table.  But as part of Joe's responsibilities he also needs to update a status table in case something needs to be re-written.  By granting update permissions to user UpdatePerms and granting Joe impersonate access to it he can log into SSMS, impersonate that user and update the table.  This means he has no update access through the application but can still perform this occasional task.

Require extra thought/action before performing a task

Similar to above.  Joe needs to be able to delete rows from a table, but you don't want him to do it by accident (or at least make it harder).  By requiring he impersonate another user before performing the delete he at least has to think about it a bit harder making it less likely to happen by mistake.

Note: I've never had to do either of these in production.  It just seems like logical possibilities.


don't want to be able creating stored procedures with incorrect

No, you can't change the way deferred name resolution works. See this Connect item, this Connect item and also this section on Erland Sommarskog's "SET STRICT_CHECKS ON" proposal: http://www.sommarskog.se/strict_checks.html#killDNR


Loop through list of fields, check against lookup table

Native dynamic SQL (in a PL/SQL anonymous block):

01    DECLARE
02        l_table_name VARCHAR2(100);
03        l_field_name VARCHAR2(100);
04        l_domain_name VARCHAR2(100);
05    BEGIN
06        DELETE FROM ENG.CV_ERRORS;
07        FOR list_fields IN (
08            SELECT
09                TABLE_NAME
10                ,FIELD_NAME
11                ,DOMAIN_NAME
12            FROM
13                ENG.FIELD_DOMAIN_ENG_VW
14            WHERE
15                TABLE_NAME NOT LIKE '%ANNO%'
16            )
17        LOOP
18            l_table_name := list_fields.TABLE_NAME;
19            l_field_name := list_fields.FIELD_NAME;
20            l_domain_name := list_fields.DOMAIN_NAME;
21
22            EXECUTE IMMEDIATE
23            'INSERT INTO ENG.CV_ERRORS
24            (TABLE_NAME, FIELD_NAME, ERROR)
25            SELECT
26                :bv1 AS TABLE_NAME
27                ,:bv2 AS FIELD_NAME
28                , ' || l_field_name || ' AS ERROR
29            FROM ' ||
30                l_table_name ||
31                ' LEFT JOIN
32                (
33                SELECT CODE
34                FROM  ENG.D_CV_ENG_VW
35                WHERE DOMAIN = :bv3
36                )
37                ON ' || l_field_name || ' = CODE
40            WHERE
41                ' || l_field_name || ' IS NOT NULL
42                AND
43                CODE IS NULL'
44
45            USING l_table_name, l_field_name, l_domain_name;
46
47        END LOOP;
48    COMMIT;
49    END;

Result set

+------------+------------+-------------+
| TABLE_NAME | FIELD_NAME |    ERROR    |
+------------+------------+-------------+
| TABLE_1    | FIELD_1    | BLACK SHEEP |
| TABLE_1    | FIELD_2    | zzzz        |
| TABLE_2    | FIELD_1    | asdf        |
+------------+------------+-------------+

Steps

Delete all existing rows in ENG.CV_ERRORS (oddly, the ODBC connection I'm using doesn't have truncate privileges for the table).
Loop through the list of fields in FIELD_DOMAIN_ENG_VW.
For each field, generate a dynamic query that looks for values that don't match the corresponding domain. Then insert them into ENG.CV_ERRORS.

It's not all that complicated now that it's all said and done. The hardest part was wrapping my head around bind variables vs. string-concatenated variables (correct terminology?), and when to use each (although I don't fully understand this yet).

Related questions

Bind variable vs. string-concatenated variable

Beginner PL/SQL: Return row value from dynamic SQL function (function, rather than a loop)

For each field name in a list of fields, get the unique values (union)


The Quick Answer - Yes. Happens all the time.

There are plenty of good solutions. What solutions are already in your environment? I am helping one client that takes their web site/session activity information from their web application, they write it to xml then deserialize that xml into Hadoop. They then use Hive on top of Hadoop to create aggregations and facts (by joining this log data to reference data from their website that they store in a Postgres database). These aggregtions and facts are then loaded into an RDBMS (they use SQL Server, because of the rich analytics provided in the SQL Server stack).

I would explore Hadoop, look at Hive which is a query engine on top of Hadoop that allows you to write declarative queries that closely resemble SQL and get translated into MapReduce jobs behind the scenes. Then look at Sqoop which is another tool in the Hadoop family that assists you with exporting/importing data.

http://hadoop.apache.org/
http://www.amazon.com/Hadoop-Definitive-Guide-Tom-White/dp/1449389732/ref=sr_1_1?s=books&ie=UTF8&qid=1319160463&sr=1-1
http://www.cloudera.com/blog/2009/06/introducing-sqoop/
http://hive.apache.org/

There are other options out there too...


Backup Oracle DB using TSM server version 6.3.0

to backup the Oracle DB to a storagepool name ORADisk this is done through backup policy by tsm server not the client by assign the client to a domain then assign it to the group policy.

channel is oracle rman command used to hold the new settings for this backup session only without any changes to the rman predefined settings.


How to completely "reset" an Oracle instance?

There are just so many things in the database in addition to user data that checking all of them would take a considerable amount of time. Also there are some parts that were not meant to be removed at all, for example the history of used features (for license audit).

Sure, you could try to check all non-default configuration or rerun catalog scripts (but whats the point of that? creating a new database does exactly that). Its easier to just drop and recreate in 15-30 minutes depending on your selected components.

PS: there is no reinstall involved in dropping and recreating a database.


MySQL 8.0 table with JSON column that uses JSON "merge" operations has 500k rows but analyze table shows 7MM

This has more to do with InnoDB

As stated in the documentation to table status

  The number of rows. Some storage engines, such as MyISAM, store the exact count. For other storage engines, such as InnoDB, this value is an approximation, and may vary from the actual value by as much as 40% to 50%. In such cases, use SELECT COUNT(*) to obtain an accurate count.

So COUNT(*) gives you the exact number.


Optimizing PostgreSQL where clause with OR

Solution:

create index on my_table (name);
create index on my_table (other_name);

Credit: @a_horse_with_no_name

Comment: I am befuddled that I didn't find this myself. I'm sure I tested [multiple] variations of adding indexes, and I don't know how I missed the obvious one. But I'm so happy that I'm giggling, and I'm grateful for the additional eyes on the problem to find this simple workaround.


How to Create Live connection between Amazon Redshift and Amazon RDS (MySQL)

In order to create a live connection from MySQL to Redshift you need to choose an appropriate database replication method. Among all existing methods, the Change Data Capture (CDC) is the most suitable for your case.

In this method, after a dump of the initial state of the database, the log of each database operation is continuously streamed and loaded into the data warehouse, and consolidated on a schedule (configurable) and the biggest advantage is that it enables near real-time replication.

A tool that I had a good experience with is Alooma, it can support near real time connection between Amazon Redshift and MySQL data base.


Using an intersection table is a cleaner and more flexible design.

With extra columns you are limited to the number of instances you can include (one per foreign key column).  Also, the types of instances are limited to those given by your FK columns.  Making any changes requires a table design change.  Also, since your roles are optional, you may have a lot of nulls in your FKs.  Having sparsely populated columns rubs some people the wrong way.  Whether this is a real design problem or just a "code smell" is debatable and depends on how you actually populate your table.

With an intersection table, you can either use constraints to enforce a single instance per type or you can allow multiple instances per type.  That might prove useful in the future if you start having contacts share a role.

Also, with an intersection table, you can create a new role without having to make a table design change.  This could save you a lot of work in the future.


Update Local Table With Data From Linked Server Using Open Query

So OPENQUERY() is basically saying "Open this query on the linked server" - It's the preferred method for doing a select when able for me. Instead of saying something like

SELECT col1, col2
FROM LinkedServerName.DatabaseName.Schema.Table
WHERE SomeValue = Something Local Here

it does the filtering and joining at the linked server. This avoids you having to bring data back to just filter it out, etc with the four part query aproach.

The problem, though, is that update is trying to run on the destination. It's basically a pass-through query, so you are trying to pass that update through.

So you'll need to do the update locally, but can join to the open query to join to the data on the other side. For more you can see the syntax guide here with some notes.