"Wrong affix file formate for flag" while loading dictionaries from LibreOffice in PostgreSQL 9.3

So I found the answer for Debian at least.
Hunspell is the successor of Myspell, and they ship with your distribution (in my case Debian).  These are the dictionaries you can use.  You can install these by executing

apt-get install hunspell-en-us

These dictionaries are installed in /usr/share/hunspell or /usr/share/myspell. The PostgreSQL dictionaries that are automatically generate out of these file when installing through apt-get are located in /var/cache/postgresql/dicts. Furthermore apt-get automatically creates links to the files generated in you /usr/share/postgresql/VERSION/tsearch_data dir. In short you are ready to use them.

If you want to manually install hunspell or myspell dictionaries you can place them in the /usr/share/hunspell or /usr/share/myspell dirs. And execute

pg_updatedicts

This will generate PostgreSQL compatible dictionaries in

/var/cache/postgresql/dicts

and you will have to link them yourself /usr/share/postgresql/VERSION/tsearch_data

ln –s /var/cache/postgresql/dicts/de_de.affix           \
  /usr/share/postgesql/VERSION/tsearch_data/            \

ln –s /var/cache/postgresql/dicts/de_de.dict            \
  /usr/share/postgesql/VERSION/tsearch_data/            \


Is it advisable to asynchronously update the summary table every time a transaction is deleted?

1M/year is 2 per minute.  You can do a lot of updating in 30 seconds.  1M/day (12/second) would be more exciting.

Do you need to rerun the summary queries?  Can't you just do a subtract via an UPDATE?  Possibly fast enough to be synchronous.

How often do you need to post a correction?  If it is rare, consider adding a row to the summary table with a negative number.  Then, when you do your reports, the SUM() comes out right.  (You do sums, correct?)

It seems just wrong to have summary tables but to continually rebuild the table.


MS SQL explicit rowversion column overhead?

The "hidden" and "explict" row versions are entirely different beasts. The explict rowversion (a.k.a. timestamp misnomer) is part of the table schema with an 8-byte fixed size for all rows. The rowversion column value may be used to determine if a row has changed since it was last retrieved as part of an optimistic concurrency check in either T-SQL or application code.
The hidden rowversion is a 14-byte value in the internal row structure the database engine uses internally to support consistency when either the ALLOW_SNAPSHOT_ISOLATION or READ_COMMITTED_SNAPSHOT database options are turned on. Unlike rowversion, this internal value is not part of the schema and not exposed directly. The field (sic) may or may not exist in a given record since it is added and maintained by SQL Server as needed to support row-versioning instead of locking for consistency.
The internal row version field and rowversion column values are independent of one another. Both may exist in the same record.


This is a bit long for a comment so I'm putting it here.

First try running this:

exec sp_msforeachtable 'sp_spaceused [?]'

Yes I realize it uses sp_msforeachtable and there are a number of better methods, this is just quick and hopefully "good enough".

At a guess you have a heap (no clustered index) that has a large amount of "unused" space.  I wrote about how to duplicate it here.  If in the list of tables you find one that has a large amount of space shown in the unused column then try running this:

ALTER TABLE tablename REBUILD

Fair warning it may take up some space to run the operation but it should free up the excess space in the table.


No, you will need an Enterprise-equivalent (Evaluation/Developer) Edition to take the 70-463 course. It requires a broad range of Enterprise-only features, including column store indexes and partitioning, among many others.


Multiple one to many relationships in series

Both are correct in the sense that both are normalized, based on your question. It seems like there isn't a strong consensus on whether to use natural keys or surrogate keys (the ids). Here are a couple of stackexchange questions on the subject:

Surrogate vs. natural/business keys [closed]
Should every table have a single-field surrogate/artificial primary key?


That Mode should be mode, so no Capital first letter.

Those = should be :


Select the MAX(time) grouping by vid and find the corresponding Id.

select t1.id, t1.vid, t1.data, t1.time
from yourTable t1
     join (select vid, max(time) as time
           from yourTable
           group by vid) t2
     on t1.vid = t2.vid
     and t1.time = t2.time;

If you need WHERE or ORDER BY:

select t1.id, t1.vid, t1.data, t1.time
from yourTable t1
     join (select vid, max(time) as time
           from yourTable
           group by vid) t2
     on t1.vid = t2.vid
     and t1.time = t2.time
WHERE t1.SOME_FIELD = SOME_VALUE
ORDER BY t1.SOME_FIELD;


Looking for a tool for managing complex powershell automation processes with multiple scripts

I propose you two things:

With a GUI: https://www.minionware.net/
Without a GUI: https://dbatools.io/


In DB2 LUW, When should I use a 4K, 8K, or 16K tablespace rather than just creating a 32K tablespace and being done with it?

It's a good question, but the answer is worthy of a whole chapter in a book that does not exist, sadly. Ember Crooks' article you're linking to is a good overview; I'll add here some random factors that one might want to take into consideration when deciding on the tablespace page sizes.

TL; DR.

Choose one page size that is most suitable for your data, considering the points below. If your performance testing shows problems that can be addressed by moving some tables to a tablespace with a different page size, do it judiciously.

Decision factors.

As you have mentioned, table row width determines the minimum page size required to accommodate them. It doesn't follow though that you always want "the smallest that will work for your data".

Firstly, the usual arguments of "avoiding unnecessary I/O" and "handling less data at a time" with smaller page sizes may be a bit misplaced. If your tablespace container sits on a ZFS file system on an LVM volume on a VMWare virtual disk on a Ceph volume on an unknown number of RAID6 devices that may use spinning disks or SSDs, do you really know how much physical I/O your 4K (or 32K) read request will cause?

Smaller page sizes certainly help if your workload creates tablespace hotspots (most I/O requests go to a limited number of pages) that cannot be resolved by other means. In such situations smaller pages can improve bufferpool efficiency and reduce page latch waits between agents competing for access to the same page. On the other hand, smaller page size means longer LRU chains and hence possibly less efficient page cleaning.

There are also arguments for larger page sizes.

Presence of LOB data.

Normally LOB data are stored outside table rows, in separate data structures that have several performance disadvantages:

You can only access them via synchronous direct reads and writes that bypass the database bufferpools; the only caching available is that of the underlying file system, if enabled for the tablespace; direct reads also don't take advantage of page prefetching.
Since LOBs are not loaded into the bufferpools, repeated access to the same data will cause repeated direct read requests.
They are not compressed, even if the table compression is enabled.

If most of your LOB values are relatively small and could fit into the row itself given larger page size (as is often the case), you could store them inline, alleviating those disadvantages.

Compression.

Larger page sizes improve efficiency of adaptive (page-level) compression. Often I/O reduction that data compression affords outweighs its CPU costs.

Don't forget temp tablespaces.

Even if each of your tables individually can be placed into a 4K tablespace, a system temporary tablespace with a larger page size (and a corresponding bufferpool) might be required. If a query joins sub-4K rows from two or more tables, the result set width might exceed the 4K limit and, if it needs to spill, an appropriately-sized tablespace will be needed.

It's worth mentioning that creating tablespaces of each possible page size "just in case" is not a good idea, because, as you said, each requires a dedicated bufferpool, and multiple bufferpools, unless necessary, are almost always worse (less efficient) than a single large one.


Is it a waste to set innodb_buffer_pool_instances greater than the # of CPUs?

I don't think you will need to have too many buffer pools for you queried data because the size of the frequently queried data doesn't quite justify it. This does of course depend on the definition of "frequently".

The appropriate documentation you should be referencing the page on innodb buffer pools, here:

http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-buffer-pools.html

The numbers I'm focusing on here is

  the frequently queried data is ~1.25 gb.

My rule of thumb when trying to keep InnoDB buffers the right size is to keep them at or around 1GB a piece, in order to keep the list of blocks as short as possible but keeping the list of buffers from being too far fetched, this will always depend on your actual needs however.

This is in line with MySQL's recomendations:

  For best efficiency, specify a combination of innodb_buffer_pool_instances and innodb_buffer_pool_size so that each buffer pool instance is at least 1 gigabyte.

The point of the multiple buffer pools is to ensure your CPU threads don't meet high contention in accessing the data. Or as they put it:

  You might encounter bottlenecks from multiple threads trying to access the buffer pool at once. You can enable multiple buffer pools to minimize this contention.

However this feature is more for larger amounts of data being frequently accessed as opposed to the 1.25GB you're system uses frequently. Ultimately if I were in your position, I wouldn't see a need for having more buffer pools than the number of CPU's assuming all CPU's are only performing MySQL related tasks. I would also look into the affects of using innodb_old_blocks_time to prevent the occasional query of your archived tables from taking the place of a block of data that is used over and over again.

I hope that helps, let me know how everything works out.


Eliminate filter operator before columnstore index scan operator

How could I remove the Filter operator and somehow force the Index Scan operator to read only the few thousand rows?

The Filter operator is applying the bitmap built on the join columns at the hash join.
Of the three join predicates, only order_date has a data type that is supported for bitmap pushdown to the column store scan. If you look at the Predicate on the scan you should see this as something like:
PROBE([Opt_Bitmap1005],[dbo].[MyLargeFactTable].[order_date])

The remaining join predicates are strings and so will appear at the Filter as part of the full bitmap test:
PROBE([Opt_Bitmap1005],
    [dbo].[MyLargeFactTable].[customer_id],
    [dbo].[MyLargeFactTable].[location_id],
    [dbo].[MyLargeFactTable].[order_date])

Pushing (parts of) the bitmap test down into the column store scan is an optimization that is only available for data types that can fit in 64 bits (like date in your example). Note join bitmap pushdown is different from string predicate pushdown (e.g. pushing customer_id LIKE '%XYZ%').
There are several ways you could look to work around this limitation. Redesigning the schema such that long strings are moved to a dimension table and referenced using an integer key is one option.
Somewhat less intrusively, you might be able to add an integer checksum to the column store (sadly not as a persisted computed column) and temporary table, then add that into the join - e.g an integer computed from CHECKSUM(customer_id, location_id, order_date) or the like.
There would still be a Filter, but the bitmap would include the checksum column, which could be pushed into the scan. This ought to significantly reduce the number of rows passed into the Filter.


if you look at the statistics the execution plans make perfect sense.
The table has around 840k tuples almost all of table id 3.
So if you're looking for max(timecode) scanning the timecode index backwards makes perfect sense with table id 3 (almost every line you hit has that id).

But if you're looking for table id 1 - you have only 120 tuples out of 840k, your best bet is to scan these 120 tuples looking for the  max(timecode).

It's like looking for a needle in a hay stack - looking for hay is simpler than looking for the needle.

Hope it made some sense into what the optimizer planned.

Btw, you could try to build a composite index of table id and timecode - I'm not near a db to check but I think PG could use that to skip to the max timecode per table id directly.
- worth a shot :)

Regards
Jony


2 Different database or 1 Single database

Following up on @JonathanFite's comment, current and future resource usage is critical to answer this question, specifically memory capacity and I/O write performance.

Rule of thumb: if both databases can fit in RAM with room to spare, run both engines. If memory is limited, consider migrating the smaller database of the two, to the engine running the larger one.

Because you're most concerned about hosting costs, it will probably work out cheaper to consolidate, but you may have to increase your memory down the line.


Reference a newly created computed column in the same query where we create it

You can use CROSS APPLY for this:

SELECT ComputedColumn
INTO Table2
FROM Table1
CROSS APPLY (SELECT SUBSTRING(Column1,1,3)) CxA(ComputedColumn)
WHERE ComputedColumn !="dec"


memory used by a query result set in Oracle 11g

Usually it's in the PGA (Program Global Area). But it will depend on a number of things. For more info, take a look at: https://docs.oracle.com/cd/B28359_01/server.111/b28318/memory.htm#CNCPT007


Clustered vs Nonclustered indexes, similar to arrays vs linked lists?

Hmmm... The leaf level of a clustered index is the table in SQL Server.  This is comparable to a phone book (to borrow from @BrentO) in that all entries in the book are stored in the order of the index - i.e. The index is ordered logically alphabetically (and physically if there is no fragmentation).  This is somewhat similar to a physically contiguous in-memory array, although SQL Server can make no guarantees about data being stored contiguously on disk or about physical order. Each page at leaf level of either index type is part of a doubly linked list and has pointers to the previous and next pages in key order, an ordered index scan uses these to traverse the index in key order.

A non-clustered index is similar to an index at the back of a book in that the index itself is sorted, making it easy to find an item in the index.  You might have two non-clustered indexes, one listing all the phone book entries by phone number, and one listing all the phone book entries by address.  The clustered index (the actual phone book) is laid out physically by the name of the owner of the phone number.  This makes sense since you typically "look up" a phone number by "name of owner".  This is somewhat analogous to an in-memory contiguous array of pointers that point to the locations of the actual data.  The array only holds the index details.

If you only knew the phone number for a person, and needed to know their name, it would take a very long time indeed to find the name by looking through the entire book, name-by-name, for the number, when instead you could simply look at the phone number index to find the name of the owner.  This is how a non-clustered index works.


As a MongoDB DBA, what do I need to know about the meltdown/spectre vulnerabilities?

An official response from Mongo on 1/6/2018 (emphasis mine):

  Recently disclosed research regarding security vulnerabilities in
  almost all modern processors such as Intel and AMD (CVE-2017-5715,
  CVE-2017-5753, and CVE-2017-5754) has prompted public and private
  institutions, including cloud providers, to patch OS and hypervisor
  infrastructures. These patches disable performance optimizing features
  of CPUs, and it is expected that, regardless of OS or cloud provider,
  all workloads will see a performance impact. We are investigating the
  performance implications of these patches to MongoDB on both OS
  kernels as well as cloud hypervisors.

  Hypervisor Patch Impact

  As a result of the patches applied by AWS
  between January 3rd and January 5th, 2017, we have observed the
  following:

  For high-load benchmarks (e.g. YCSB) we measured a 10-15% impact on
  throughput with some exceptional cases that are still being studied.
  We believe these results are consistent with other industry research.
  We will release additional results (including additional cloud
  providers) in the coming days.

  Kernel Patch Impact

  This is the impact that customers can expect to
  see when they apply the recommended patches to their cloud VMs or
  on-premises hardware:

  At this time we have not seen a substantial signal (either better or
  worse) on major distributions such as Amazon Linux, Red Hat Enterprise
  Linux, and Ubuntu. Based on the industry research we expect that there
  will be some measurable performance impact (although yet to be
  quantified). We will have additional results made available in the
  coming days. If you have any questions or concerns, please file a
  ticket in the Support Portal.

  You can read Intel’s white paper to learn more about these
  vulnerabilities.

Related Posts:

Glenn Berry: Checking your Meltdown and Spectre Mitigation Status in Windows


SQL Server Database size unchanged after dropping File column

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


Removing Databases in Recovery Pending Status after AG group was removed

To resolve this I had to recreate the whole Failover cluster, re-enable HA in the SQL Configuration manager and then I could set one of the db's to recover


Prompt user for input using substitution variable

When the user types A, the code will look like:

DECLARE
 localvar char;
BEGIN
 localvar := A;
 IF localvar ='A' then
  dbms_output.put_line('Please proceed to Level1');
 ELSIF localvar='B' or localvar='C' then
  dbms_output.put_line('Please proceed to Level2');
 ELSIF localvar='D' then
  dbms_output.put_line('Please proceed to Level3');
 ELSE
  dbms_output.put_line('Invalid Entry');
 END IF;
END;
/

Notice the line localvar := A;. This is obviously incorrect, as A is an unknown identifier.

Either type 'A', or put apostrophes in the code:

DECLARE
 localvar char;
BEGIN
 localvar := '&ENUMBER';
 IF localvar ='A' then
  dbms_output.put_line('Please proceed to Level1');
 ELSIF localvar='B' or localvar='C' then
  dbms_output.put_line('Please proceed to Level2');
 ELSIF localvar='D' then
  dbms_output.put_line('Please proceed to Level3');
 ELSE
  dbms_output.put_line('Invalid Entry');
 END IF;
END;


Is there a logical size limit to enum tables

I recommend that you keep the questions in a separate table and have a FK in the answer/response table. I like my enum types to represent a well-defined and discrete concept. For example, in a order tracking system, the status of an order would be a good enum. A concept like a question does not fit that requirement. A question can have attributes (e.g is the question mandatory or optional, are the responses constrained or open-ended, etc), which in my opinion makes it something I would want to store in a table.
I can imagine a question fitting multiple categories.  If that is the case, then there should be a category table and a link table between the question table and the category table. If a question can fit only one category, then that would be a good fit for an enum column in the question table.


Is this surprising deadlock reasonable or a MariaDB bug?

A guess...

DELETE without WHERE needs to lock the entire table.  Perhaps that is done via "start through current end", where the 'end' is at 10.  Hence messing with '9' would be problematic.

On the other hand, '11' is off the end, so less ambiguity.

Are you arguing that neither should deadlock?  Or that both should deadlock?

Here's another aspect...  To do everything "correct" in every case would possibly be very complex.  So some shortcuts have been taken.  And your test case pulls out a case where the shortcut led to an inconsistency.

Or is it "right" to have the inconsistency?  One thread is deleting "old" stuff; the other thread is adding "new" stuff.  Think of a queue -- this is exactly what you need.


Programmatically, you can use DATEName(DW, GETDATE()) to see if it is Saturday along with time and then have your sp_stop_job run to stop the job.

You can put that code in IF .. check saturday and time then run stop job code else exit gracefully.

It would be much better if you use Ola's Index maintenance solution (or Minnionware - I have not used it though, since I am using the former).

EXECUTE dbo.IndexOptimize @Databases = 'ALL_DATABASES',
        @FragmentationLow = NULL,
        @FragmentationMedium = 'INDEX_REORGANIZE',
        @FragmentationHigh =  'INDEX_REORGANIZE',
        @FragmentationLevel1 = 5,
        @FragmentationLevel2 = 30,
        @UpdateStatistics = 'ALL',
        @OnlyModifiedStatistics = 'Y',
        @TimeLimit = 5400, -- 90 minutes, specified in seconds
        @LogToTable = 'Y',
        @Indexes = 'ALL_INDEXES',
        @SortInTempdb = 'Y'


connect SQL server file (mdf) with R tool

I am assuming you are trying to use SQL Expess localDB. Since you did not give specific error messages my answer is also very generic.

Here is a good reading about LocadDB.  Introducing LocalDB, an improved SQL Express.

Check if you have LocalDB installed and what version by running this in a command window.

sqllocaldb.exe info MSSQLLocalDB

From books oniline:

  Once installed, LocalDB is an instance of SQL Server Express that can
  create and open SQL Server databases. The system database files for
  the database are stored in the users' local AppData path which is
  normally hidden. For example
  C:\Users\\AppData\Local\Microsoft\Microsoft SQL Server Local
  DB\Instances\LocalDBApp1. User database files are stored where the
  user designates, typically somewhere in the C:\Users\\Documents\
  folder.

If it not installed you can install 2016 using this method.

Once you are confirmed that you have localDB installed and version is correct use this document Working with SQL Server LocalDB to create new database(files) or attach existing files. See section SQL Server Express LocalDB.


Azure SQL Server - Cannot get rid of the sort operator

I don't know if you are going to be able to eliminate the sort operator, the use of ROW_NUMBER necessitates a sort operation.

But you may be able to reduce the total cost of your query by using something similar.

;WITH CTE_Initial AS
    (
    SELECT op.OfferProgressId
        , ROW_NUMBER() OVER(PARTITION BY op.OfferId, op.OfferStatusId ORDER BY op.DateCreated) AS FirstStatus
    FROM OfferProgress AS op
    WHERE op.OfferStatusId IN (3, 5, 6, 7, 8, 9, 16, 17)
        AND op.DateCreated >= '8/1/2017'
    )
SELECT op.OfferProgressId, op.OfferId, op.DateCreated, op.OfferStatusDate, op.OfferStatusId,  u.Name
FROM CTE_Initial AS I
    INNER JOIN OfferProgres AS op ON op.OfferProgressId = I.OfferProgressId
    LEFT OUTER JOIN [User] AS u ON u.UserID = op.UserID
WHERE I.FirstStatus = 1


Can I restrict a SQL Server spatial field to hold values of specified spatial types and SRIDs?

The usual way to do this would be through a check constraint.

The properties you want to check are .STSrid and .STGeometryType()

So something like

ALTER TABLE Test
    ADD CONSTRAINT chk_shape_gtype_srid
    CHECK (
        shape.STSrid = 4326 AND
        shape.STGeometryType() in ('POLYGON','MULTIPOLYGON')
    );


Which allows for faster queries in a graph database: a node with multiple properties, or a node that points to multiple nodes?

With the given information, I find it difficult to give you a simple answer.  As you have discovered, data can be hierarchical with many different logical groupings that could work.

Here is a checklist of things you can do to get a better answer for yourself:

What are the full set of queries you will want to do?  (Among your
schema options, which one(s) minimizes the number of times you're
going to disk to retrieve data for your set of queries?)
How will your data set scale?  (Among your schema choices which one(s) scale gracefully, minimizing the number of fat nodes or other degenerates
that will not scale well?)
What kind of churn do you expect?  How much are you willing to slow writes in order to accelerate reads?

I hope this helps.


Making collations uniform across multiple SQL Server 2008 databases

what would be a good approach for making [collations for columns] all uniform?

Well, "good" is highly subjective. The first thing to consider is the scope of what you believe should be changed. How many databases are affected? How many columns in each database would be changed? And how many total string columns are there? Does the collation need to change at the database level for any of the databases? Does the collation need to change at the server level? Does the issue you are trying to fix revolve around column-to-column comparisons / joins, or is it column-to-variable / literal comparisons, or both?

If most columns have the same collation but a small percentage have different collations, was that intentional such that changing the collation would change the behavior and/or performance of some queries? Meaning, it is one thing to want to change SQL_Latin1_General_CP1_CI_AS to Latin1_General_100_CI_AS as that is at least the same code page, culture (i.e. linguistic rules), and sensitivities (accent-sensitive, case + kana type +  width insensitive). But it is quite another to change Latin1_General_100_CI_AS to Latin1_General_100_BIN2 or Latin1_General_100_CS_AS, or French_100_CI_AS, or even to SQL_Latin1_General_CP1_CI_AS.

If it is a small number / percentage of columns to change, AND assuming that the "incorrect" collations were not intentional and purposeful, then you should probably change them one at a time via ALTER TABLE ... ALTER COLUMN. You might need to drop and recreate dependencies if any exist, such as FKs, indexes, functions referencing those columns, etc.

If it is a lot of columns, and even more so if there is a need to change database and/or instance collations, then you could look into the undocumented approach that I documented here:

Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?

There are pros and cons to that "undocumented" approach, but it does greatly reduce the workload of making the changes as it side-steps the dependency issues. There are a few gotchas that will affect some folks and not others, but only one that can't be fixed easily enough: changing code pages on a CHAR / VARCHAR / TEXT column1. However, I am guessing that it is fairly rare that someone would be changing collations that also change the code page (but if that is the case, then you can't use this undocumented approach without first making sure that you aren't using any of the characters that would be changing). Still, it changes ALL2 collations, which is generally desired in these cases, but does require that you might need to change a few back manually if there are some non-standard collations being used that are there for a good reason.

1 Code page change happens when the code page used by the old collation is different than the code page used by the new collation. If the old and new collations are in the same culture / locale (i.e. Latin1_General, French, Japanese, etc), then there shouldn't be any chance of that happening. And even going from Latin1_General to French isn't a problem as they both use Code Page 1252. But, going from Latin1_General to Japanese is a problem as they use different code pages. You can determine the code page using the COLLATIONPROPERTY built-in function. For example:

SELECT COLLATIONPROPERTY(N'Latin1_General_100_CI_AS', 'CodePage') AS [Latin1_General],
       COLLATIONPROPERTY(N'French_100_CI_AS', 'CodePage') AS [French],
       COLLATIONPROPERTY(N'Japanese_CI_AS', 'CodePage') AS [Japanese];

returns:

Latin1_General    French    Japanese
1252              1252      932

2 Almost all: collations of User-Defined Table Types are skipped.


Matheus de Oliveira created handy functions for JSON CRUD operations in postgresql. They can be imported using the \i directive. Notice the jsonb fork of the functions if jsonb is your data type.

9.3 json
https://gist.github.com/matheusoliveira/9488951

(and while jsonb is not an option for Michael, for those who are using 9.4, there is a jsonb variant: https://gist.github.com/inindev/2219dff96851928c2282)

Operations Provided:

postgres=# SELECT json_append('{"a": 1}', '{"b": 2, "c": 3, "a": 4}');
        json_append
---------------------------
 {"a":1,"b":2,"c":3,"a":4}
(1 row)

postgres=# SELECT json_delete('{"b": 2, "c": 3, "a": 4}', '{b, c}');
 json_delete
-------------
 {"a":4}
(1 row)

postgres=# SELECT json_update('{"a": 1}', '{"b": 2, "c": 3, "a": 4}');
 json_update
-------------
 {"a":4}
(1 row)

postgres=# SELECT json_merge('{"a": 1}', '{"b": 2, "c": 3, "a": 4}');
     json_merge
---------------------
 {"b":2,"c":3,"a":4}
(1 row)


~2 Hours after a large insert SQL Server gets "I/O requests taking longer than 15 seconds to complete"

BOL refers to 833 event ID as :

  This problem can be caused system performance issues, hardware errors, firmware errors, device driver problems, or filter driver intervention in the IO process.

What it means is that

SQL server is doing more I/O's that what the disk subsystem can handle.
There could be some rogue process running on the system that is saturating the disks with all the I/O requests.

Below is the approach I would follow:

Troubleshoot using Wait Statistics - DMV's

WITH [Waits] AS
    (SELECT
        [wait_type],
        [wait_time_ms] / 1000.0 AS [WaitS],
        ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
        [signal_wait_time_ms] / 1000.0 AS [SignalS],
        [waiting_tasks_count] AS [WaitCount],
        100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
        ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
    FROM sys.dm_os_wait_stats
    WHERE [wait_type] NOT IN (
        N'CLR_SEMAPHORE',    N'LAZYWRITER_SLEEP',
        N'RESOURCE_QUEUE',   N'SQLTRACE_BUFFER_FLUSH',
        N'SLEEP_TASK',       N'SLEEP_SYSTEMTASK',
        N'WAITFOR',          N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH',
        N'XE_TIMER_EVENT',   N'XE_DISPATCHER_JOIN',
        N'LOGMGR_QUEUE',     N'FT_IFTS_SCHEDULER_IDLE_WAIT',
        N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT',
        N'CLR_AUTO_EVENT',   N'DISPATCHER_QUEUE_SEMAPHORE',
        N'TRACEWRITE',       N'XE_DISPATCHER_WAIT',
        N'BROKER_TO_FLUSH',  N'BROKER_EVENTHANDLER',
        N'FT_IFTSHC_MUTEX',  N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        N'DIRTY_PAGE_POLL',  N'SP_SERVER_DIAGNOSTICS_SLEEP')
    )
SELECT
    [W1].[wait_type] AS [WaitType],
    CAST ([W1].[WaitS] AS DECIMAL(14, 2)) AS [Wait_S],
    CAST ([W1].[ResourceS] AS DECIMAL(14, 2)) AS [Resource_S],
    CAST ([W1].[SignalS] AS DECIMAL(14, 2)) AS [Signal_S],
    [W1].[WaitCount] AS [WaitCount],
    CAST ([W1].[Percentage] AS DECIMAL(4, 2)) AS [Percentage],
    CAST (([W1].[WaitS] / [W1].[WaitCount]) AS DECIMAL (14, 4)) AS [AvgWait_S],
    CAST (([W1].[ResourceS] / [W1].[WaitCount]) AS DECIMAL (14, 4)) AS [AvgRes_S],
    CAST (([W1].[SignalS] / [W1].[WaitCount]) AS DECIMAL (14, 4)) AS [AvgSig_S]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
    ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum], [W1].[wait_type], [W1].[WaitS],
    [W1].[ResourceS], [W1].[SignalS], [W1].[WaitCount], [W1].[Percentage]
HAVING SUM ([W2].[Percentage]) - [W1].[Percentage] < 95; -- percentage threshold
GO

Look at the pending I/O's

SELECT database_id
    ,file_id
    ,io_stall
    ,io_pending_ms_ticks
    ,scheduler_address
FROM sys.dm_io_virtual_file_stats(NULL, NULL) t1
    ,sys.dm_io_pending_io_requests AS t2
WHERE t1.file_handle = t2.io_handle

Below are PERFMON counters that will help you:

Avg. Disk sec/Transfer => Time taken to perform the I/O operation
Disk Bytes /sec  => Total reads and writes to disk per second in bytes.
Process:IO Data Bytes/Sec => Total reads and writes to disk per second in bytes by each process.
Buffer Manager: Page Read/sec + Page Writes/sec =>Total reads and writes to disk per second in bytes by SQL Server process.

Also, sometimes such errors can also occur when a different Power Management scheme is selected for the server. So better check your Windows Power Plan setting, and make sure you are using the High Performance Power Plan.

  All three stored procedures had flaws which made them insert not a few hundred records but over 500.000 each time they ran. (These are now fixed so the issue doesn't present itself but I feel there is an underlying problem here).

If you are inserting a huge amount of data, then you need to refer to The Data Loading Performance Guide to cater your server for such operations e.g. enabling trace flag 610.

Note: Others have mentioned that Auto Update Stats might be causing it, which is also worth looking at, but I would primarily concentrate on finding out rouge process that is causing lots of Disk I/Os and checking the storage subsystem to see if all the divers, etc are up-to-date and is configured correctly.

Good reading links :

I/O requests taking longer than 15 seconds to complete on file
Troubleshooting SQL Server I/O requests taking longer than 15 seconds - I/O stalls & Disk latency
Event ID 833: I/O requests taking longer than 15 seconds


Scaling Azure SQL database will make my website goes down?

How does the scaling process work? Is there a flow, diagram that
illustrates it?
See this link:
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-service-tiers
Will the scaling cause any downtime in my website?

No new connections for a brief period and existing connections might rollback.

  Changing the service tier and/or performance level of a database
  creates a replica of the original database at the new performance
  level, and then switches connections over to the replica. No data is
  lost during this process but during the brief moment when we switch
  over to the replica, connections to the database are disabled, so some
  transactions in flight may be rolled back. This window varies, but is
  on average under 4 seconds, and in more than 99% of cases is less than
  30 seconds. If there are large numbers of transactions in flight at
  the moment connections are disabled, this window may be longer.

Am I able to write things to the database during the scaling
process?

Yes but you might see your transaction is rolled back.


Design advice regarding a users/groups/tasks database scheme

Try these design which will satisfy your requirements.

Group

 - Group Id Primary Key
 - Group Name)

User

- User Id Primary Key
- Name
- Email
- Created on
- Group Id references Group(Group Id), Referred User ID references User(User ID))

Task

- Task ID Primary Key
- Name
- Priority
- Date)

User-Tasks

- Relation ID Primary Key
- User ID references User(User ID)
- Task ID references Task(Task ID))


Mysql - tablename.ibd file is huge in size

If you have enabled innodb_file_per_table, the only way in Operative System can recover and demand to MySQL the used space is through OPTIMIZE TABLE command, you should run that command for you could see equally the data in both ways through .ibd file and using query.


Based on your comments

  It needs to run with this logic "Does drugname 1 exist anywhere within
  the Logic rows? If so, return which row (it's location within is not
  important) Then repeat the same logic with drugname 2 etc etc. Thanks

&

  The Soundex is not working. I don't think it is suitable in what I am
  trying to do. I just need a query to search each drugname within the
  dataset to see if it exists anywhere in the logic table. Thanks

You could use CROSS APPLY + LIKE to only get the drugname's that have a match with the logic column. It won't be sargable but should get the job done. If you need to add the logic to identify the row, multiple of the same drugname's could be returned.

Query

SELECT drugname,A.logic
FROM #drugs t1
CROSS APPLY
(
SELECT logic
FROM #logic t2
WHERE t1.drugname <> t2.logic
AND t2.logic LIKE '%' + t1.drugname + '%'
)
AS A;

Test data

CREATE TABLE #drugs(id int identity(1,1) PRIMARY KEY NOT NULL,
                    drugname varchar(20));
INSERT INTO #drugs(drugname)
VALUES('Paracetamol 500mg'),('Paracetamol 1000mg');

CREATE TABLE #logic(id int identity(1,1) PRIMARY KEY NOT NULL,
                    logic varchar(max));
INSERT INTO #logic
VALUES('BlablaParacetamol 500mg'),('__Paracetamol 1000mg__'),('...');

Result

drugname    logic
Paracetamol 500mg   BlablaParacetamol 500mg
Paracetamol 1000mg  __Paracetamol 1000mg__

If you don't care about the logic column and only want one row for each drugname that has one or more matches.

SELECT drugname
FROM #drugs t1
CROSS APPLY
(
SELECT TOP(1) logic
FROM #logic t2
WHERE t1.drugname <> t2.logic
AND t2.logic LIKE '%' + t1.drugname + '%'
)
AS A;


Give this a try
You have some (potentially) misplaced join conditions

SELECT        P.Id id0,
              P.ProgramId ProgramId1,
              P.ProgramName ProgramName2,
              P.ProgramLevel ProgramLevel3,
              P.Department Department4,
              P.Track Track5,
              P.AcademicYear AcademicYear6,
              P.StartTerm StartTerm7,
              P.Delivery Delivery8,
              P.Fee Fee9,
              P.City City10,
              P.STATE State11,
              P.StartDate StartDate12,
              P.Deadline Deadline13,
              P.DeadlineDisplay DeadlineDisplay14,
              P.ProgramType ProgramType15,
              O.Id as OrganizationId16,
              O.NAME OrganizationName17,
              P.ApplicationType ApplicationType18,
              P.Concentration Concentration19,
              P.ZipCode ZipCode20,
              P.Campus Campus21,
              P.WADisplayName WADisplayName22,
              P.UpdatedDate updateDate23,
              AF.Id InstanceId24,
              RD.Id stateId33
INTO #TempGetFullProgramSelectionInfo
FROM       UNICAS_CONFIG..Organization O
INNER JOIN UNICAS_CONFIG..AcademicInstitution AI
            ON AI.id = O.academicInstitutionid
INNER JOIN unicas_config..applicationForm AF
            ON AF.casid = AI.casid
           AND AF.Id = 6286
INNER JOIN UNICAS_CONFIG..AssociationOrg asOrg
            ON asOrg.FormId         = AF.id
           and asOrg.OrganizationId = O.id
INNER JOIN UNICAS_CONFIG..Program P
            ON P.AssociationOrgId = asOrg.Id
INNER JOIN unicas_config..ReferenceData RD
            ON RD.ValueId = P.STATE
           AND RD.ValueAbbr ='US'
INNER JOIN unicas_config..ReferenceDataSet RS
            ON RS.SetId = RD.ReferenceSetId
           AND RS.NAME = 'LK_States'

If #temp has indexes then sort on the one that makes the most sense

If that does not improve response then just force a hash join on all
Still use the select in my answer - add the HASH to the query above

INNER HASH JOIN


Can both primary and secondary servers be online simultaneously in Log Shipping?

I am new to log shipping and I want to know can both primary and secondary servers be online at the same time like in replication,

No, not like replication where the records in the tables at the subscriber can both be read and updated. The secondary server can be in read_only/standby and restoring mode. When in read_only/standby mode the read only queries can access the database but would either block the restore operation or be disconnected when restore happens so that is not a truly online in the sense you are asking.


Is conditional execution possible for SQL Server Agent job steps?

You can't configure the agent to act as you have described, but you might be able to achieve the desired result by building the logic into the code executed in steps 2, 3, and 4.  For instance you could use an IF statement at the start of step 2 to check the result of step 1, and only continue if the result was 1.  If the result is not 1, then it would move on to step 3, and so on.


Can I use OpenLDAP to authenticate users in Oracle?

Yes!

According to this post, you absolutely can, with significant speed improvements over OID.

Looking at this absolutely ancient post, it was apparently possible back in 2003.

Here's the Archive.Org transcript of the page they reference.

This is a Oracle documentation link about hooking OID up to an "external directory". Good luck.


Performance effects of database file size and available space

The size in and of itself is what you will need to appropriately determine when setting and growing. This is all part of properly sizing your database.

But you need to be careful with growing your files at such a small increment. If you are growing your files often, you are causing SQL Server to have to do this relatively expensive operation. Take, for instance, growing of the transaction log. When this happens all write activity will be impeded. Another thing about that is if you grow your transaction log at a small interval and it grows often, you will end up with a large amount of VLFs that are small in size.

Instant File Initialization is a great approach, as it bypasses the zero-initialization of the additional file (or file allocation). This shaves off a lot of the overhead and performance impact, but this is only possible with data files, not log files.

As you can see, there are a handful of things to consider depending on your environment and how much your database grows. Size your database appropriately and find the right growth increment.

Note, autogrowth should be reserved for emergency situations and it is recommended to manually grow your files if at all possible. This gives you full control over all of the points above, as well as allowing you to determine when this operation occurs.

As for your numbers, "space available" is just that:  The free space that you have for additional data until growth will need to happen. One strategy is to get notified when you reach a certain threshold of used space so that you can schedule and kick off growth of the appropriate file(s).


MySQL failures after changing innodb_flush_method to O_DIRECT and innodb_log_file_size

The source of the problem was removing the log files before flushing everything out of the logs
You should run this to shut it down (using innodb_fast_shutdown)
mysql ... -ANe"SET GLOBAL innodb_fast_shutdown = 0"
service mysql stop

After shutting down like this, then you can put in new logs. That way, there is nothing in terms of InnoDB Crash Recovery to worry about from the old logs.
InnoDB Architecture

What innodb_flush_method does is have the InnoDB storage engine cache any needed blocks to the double write buffer (See the chart). The error messages you have are about the  sequence numbers from the logs being misaliged because of the incomplete way you made new logs.


How to get a list of restricted_components with it's related information?

Playing with LEFT + INNER You can change logic of SQL query

If You need all from restricted_component - LEFT JOIN
But because groups must be for all - INNER

SELECT
  `rc`.*,
  `rs`.resources_name,
  `gr`.`groups_name`
FROM `restricted_components` AS `rc`
  LEFT JOIN `resources` AS `rs` ON rc.resources_id = rs.resources_id
  INNER JOIN `acl_groups_to_resources` AS `acl_gr` ON rs.resources_id = acl_gr.resources_id
  INNER JOIN `groups` AS `gr` ON acl_gr.groups_id = gr.groups_id
ORDER BY component_id

result from Your dataset

1   1   button_1    button_1        1   Users   Administrators
1   1   button_1    button_1        1   Users   User Admins
2   2   button_2            0   Companies   Administrators
3   3   button_3            0   Hardware    Administrators
4   4   button_4            0   Reports Administrators
4   4   button_4            0   Reports Reports Admin
5   5   button_5            0   Notifications   Administrators
5   5   button_5            0   Notifications   Company Admins


Setup MongoDB replica servers with different power?

You can use machines of different sizes as long as the lesser machines can actually keep up with the more capable primary when replicating (particularly at peak traffic times).  You can use priorities to ensure that the more powerful machines are preferred when they are up and healthy and you can set any host that absolutely should not become primary to priority 0.  Caveats:

Do not mess around with votes unless absolutely necessary
Always keep the number of voting members to an odd number
If the only node left healthy in the set is priority 0 it will stay secondary (no primary, no writes)

One other note - if (for example) the nodes fall behind at peak, but catch up later that is usable but you will want to make sure your oplog window is big enough to cover the lag and you may want to make the lagging nodes hidden so that secondary reads do not see a lagging version of the data. Similarly, if you are seeing lag you will need to be careful with write concerns that require replication (w:2 for example) since they might end up requiring the lagging node to catch up to satisfy the concern and return successfully.

To summarize: yes you can do this, but you need to be aware of the possible implications of doing so and it can potentially complicate your deployment (versus homogeneous hosts).


Adding a database to existing distributed availability group

I am working in a test situation and have already successfully created a distributed availability group with automatic seeding.

Automatic seeding is a property of the replica not a distributed availability group.

  I cannot figure out how to add another database to the distributed AG.

Interestingly I went to find the MSDN BOL page to show this... and there isn't one. That definitely needs to be corrected and I'll follow up on that.

  I successfully added the new database to the primary AG, but the alter availability group statement does not seem to work on the distributed AG.

Distributed AGs don't hold databases, they hold availability groups. Availability Groups hold databases, that's where we need to go.

  Surely there is a way to add a database after you create the distributed AG?

Surely there must be, as to create a distributed availability group the secondary availability groups must be void of all databases.

How to add a database to an existing distributed availability group:

Create the new database on the distributed availability group's primary replica for that availability group.
If you are using automatic seeding, it should seed the database to the other replicas in the primary replicas group. If you're not using automatic seeding, backup and restore the database to the secondary replicas leaving it in a restoring state. Then join the database to the local availability group.
Regardless of the automatic seeding setting, you'll need to backup and restore the database to each secondary distributed availability group. Restore the database and leave it in a restoring state. Then join the database to the local availability group. Do this for each secondary availability groups' replicas.
Have yourself a treat because that's a bunch of work.


Does the transaction isolation level affect indices as well?

No. It doesn't affect anything regarding the internal management within SQL Server. You're setting that connection for you, for your queries. SQL Server manages it's own locking it's own way.

Why would you turn off page and row locking on an index? You're more likely to see more severe locking than if you let SQL Server manage that index as it sees fit. By setting both those values to OFF, you just told SQL Server to take a table lock out. Considering you're also messing with read_uncommitted, I'll bet you don't want that.

If you're asking does read_uncommitted allow you to get dirty reads on indexes, yes. It does.


Database migration program, that can analyze source and target db, and tell diff

There are multiples alternatives out there. I would recommend these two:

Idera comparison Toolset and the Red Gate SQL Compare & Red Gata Data Compare. All of them offer full funcionality during the trial period so you can take a look and decide which one is better for you. They work in a similar way: you select source and target databases, compare and generate deployment script with the differences.
For database diagram you can also use the Red Gate Dependency tracker.

I use Redgate ones everyday and used Idera in the past so let me know if you have any issue.


Best approach for Legal/Natural can have many Orders

This is an alternative to your solution. I suppose your id columns are just for mysql internal use, so you could have:

person table with id, name, and type which would be a bit or 1 character to say if it is a natural or legal person. This table will connect to order table where you will only need one column - person_id
On natural_person table you remove the name column and connect it to the person table
On legal_person table you remove the name column and connect it to the person table


How do I convert between the transaction ID formats in fn_dblog() and sys.dm_tran_database_transactions?

I do not believe that the [Transaction ID] column of sys.fn_dblog() is the transaction ID you are looking for. The transaction id in that result set that matches the transaction_id column of sys.dm_tran_database_transactions is [Xact ID].

Try the following:

Find a Transaction ID

SELECT *
FROM   sys.dm_tran_database_transactions
WHERE  database_id = DB_ID();

Get that Transaction's details from the Log

DECLARE @TransactionID NVARCHAR(50);

SELECT @TransactionID = [Transaction ID]
FROM   sys.fn_dblog(NULL, NULL)
WHERE  [Xact ID] = 10450416; -- some [transaction_id] from sys.dm_tran_database_transactions

--SELECT @TransactionID;

SELECT *
FROM   sys.fn_dblog(NULL, NULL)
WHERE  [Transaction ID] = @TransactionID;


Unnest ids from arrays based on priority with no duplication

I've solved this relationaly by unnesting and assigning the weights as suggested by @Akina.
with weighted as (
  select
    case priority
      when 'High' then 6
      when 'Medium' then 3
      when 'Low' then 0
    end as priority,
    productive,
    unproductive,
    neutral
  from
    data
), unnested as (
  select
    unnest(productive) as id, priority, 2 as category
  from
    weighted
  union all
  select
    unnest(unproductive) as id, priority, 1 as category
  from
    weighted
  union all
  select
    unnest(neutral) as id, priority, 0 as category
  from
    weighted
)
select
  case category
    when 0 then 'Neutral'
    when 1 then 'UnProductive'
    when 2 then 'Productive'
  end,
  array_agg(id)
from unnested x
where not exists (
  select null
  from unnested
  where id = x.id
  and priority + category > x.priority + x.category
)
group by category, 1
order by category desc;

See https://dbfiddle.uk/?rdbms=postgres_13&fiddle=8a54e1f8018c49146faba3e446f7b694
It can probably be done more efficiently.


As @SteBov noted in his comment, comparing the sid with quotes around it is problematic since that forces an implicit conversion to be performed against the sid column.  Since the conversion converts the hexadecimal value into a character value, you will never see the login in the query results.

If you check the execution plan for the following statement:

SELECT *
FROM sys.server_principals sp
WHERE sp.sid = '0xFEEBD63ECD0F6A41AE60A6E0414F5A1D';

We see the following clustered index scan against the internal table, sys.sysxlgns:

In the details, you can see the following predicate:

  [master].[sys].[sysxlgns].[type] as [p].[type]<>'M' AND CONVERT_IMPLICIT(varchar(85),[master].[sys].[sysxlgns].[sid] as [p].[sid],0)='0xFEEBD63ECD0F6A41AE60A6E0414F5A1D'

If you run a select changing the CONVERT_IMPLICIT into a simple CONVERT statement, as below, you can see the sid column is not what you'd expect:

SELECT CONVERT(varchar(85), sp.sid, 0)
FROM sys.server_principals sp;

In order to successfully find the login with the duplicate sid, you'll need to run the select like:

SELECT *
FROM sys.syslogins sl
WHERE sl.sid = 0xFEEBD63ECD0F6A41AE60A6E0414F5A1D;

Preferably, you'd look at sys.server_principals since syslogins is a SQL Server 2000 compatible view that has been deprecated.

SELECT *
FROM sys.server_principals sp
WHERE sp.sid = 0xFEEBD63ECD0F6A41AE60A6E0414F5A1D;


Quality Control: Check file names in a folder against filenames in a table

The following steps will need some tweaking, as you are working with multiple machines - your client machine, a file server (Windows?), a database server etc.  Thus, the paths and the file locations are just examples (which have been tested, though).  The steps A-B-C are performed only once.

Prerequisites / setup:

[A] Operating system: create a folder for storing the dir listings (in form of text files, see footnote 1) .

eg C:\Users\Wilson\datafiles

[B] Oracle: you need the permissions CREATE TABLE and CREATE ANY DIRECTORY  (ie read/write)

SQL> grant create any directory to wilson;
Grant succeeded.

[C] In Oracle, create a directory and an external table.

SQL> create directory external_tables as 'C:\Users\Wilson\datafiles';

Directory created.

create table xtable ( path_ varchar2(4000) )
organization external (
  type oracle_loader
  default directory external_tables
  access parameters
  (
    records delimited by newline
    fields terminated by ','
    missing field values are null
  )
  location
  (
    'dir_contents.txt'
  )
)
/

 Table XTABLE created.

Get the file list, and use the external table:

[1] Open a command line window.  Navigate to the network drive (X:).
    Execute the following command:

dir /s /b > C:\Users\Wilson\datafiles\dir_contents.txt

REM (this may take a minute ...)
REM Explanation:
REM  dir /s dives into every subdirectory (recursively),
REM  /b returns the dir listing without headers and summaries.
REM  > redirects the output into the file (absolute path provided)

Load the dir_contents.txt file into a text editor, make sure that it contains what you need.
If - as you wrote - Oracle runs on a different machine, you may have to upload it (via sftp).

[2] Use the external table (see footnote2).

SQL> select count(*) from xtable;
COUNT(*)
19362

SQL> select * from xtable fetch first 7 rows only;
PATH_
C:\Windows\System32\0409
C:\Windows\System32\12520437.cpx
C:\Windows\System32\12520850.cpx
C:\Windows\System32\3DAudio.ax
C:\Windows\System32\3DES.dll
C:\Windows\System32\8point1.wav
C:\Windows\System32\aaclient.dll

7 rows selected.

footnote1: This must be accessible when you are working with Oracle.  If you collect the datafile(s) on your client machine first, you must "upload" the files (eg via sftp) into the directory that you have specified when executing CREATE DIRECTORY ...

footnote2: The sample file contains the output of

C:\Windows\System32>dir /s /b > C:\Users\Wilson\datafiles\dir_contents.txt

(notice the paths and the command redirection).  If you are working with Linux, use "find / -type f > dir_contents.txt"


Why is the backup sizes of AlwaysON replicas different?

The size of the database is always exactly the same on both primary and secondary replicas. The difference you are noticing on the backup file size could be because one of the two reasons

You didn’t use compression on secondary and applied compression when you took the backup on primary server.
As you already mentioned you were using Dell Lite Speed, which has different algorithm and compression level which also make a difference in your backup file size.

The performance issue could be because of different reasons. After you restore the database, make sure to check and if necessary to update the statistics, Index fragmentation level, and the query plan cache which usually has significant performance influence after a database restore.


whats the fastest way to copy data from table to new one?

try alter table exchange partition, as this will not phsycaily move data but it will alter the dictionary, but keep in mind that the source table will be empty as this is exchnage


double date conversion when adding partition

There is no way around it in Oracle 9i.

If you were using 11g, you could define the table to be interval partitioned in order to avoid having to create a new partition every day.  But that's not going to help you on an ancient version of Oracle.


Recommendations on Partitioning Multi-Tenant Data in SQL-Server

If I don't partition, is (tenantID, id) or (id, tenantid) a better
  choice. Assume reads are all by tenantid, writes are random by
  tenantid, and a there is enough downtime nightly to run a maintenance
  plan.

(tenantID,id) is the better ordering, as it creates locality for each tenant's data.

  What are the pros and cons of creating a separate partition for each
  tenantID?

Pro: partition operations can be used to operate on single tenants.  Eg SWITCH, TRUNCATE, REBUILD.

Con: Maintence, and possibly cost of partition splitting to add new tenants.

But see:

  Will partitions reduce page fragmentation if the tenantID is the first
  key?

No.  Each tenant's rows will be logically and mostly physically contiguous in the index.  There will basically be one or a few fragments for each tenant.  So while the partitioning will reduce fragmentation, it shouldn't be a problem without partitioning.

  If the partitions are in the primary file, does that alter the
  arguments?

No.  That is separate.  All partitions should go to a single filegroup, unless there's some good reason to separate them.  You could potentially have a separate filegroup for some special tenant, but I wouldn't recommend that.

  If there are 10 or 1000 partitions, does it alter the arguments?

No, except that it exacerbates the maintence issue with the partitions.

  Does the answer depend on whether I use SQL 2016 or SQL Azure?

No.

But stepping back, you should plan to have multiple federated databases holding your tenant data.  You can still go with a multi-tenant database design, but in the end state you will have a mix of multi-tenant and single-tenant databases.  There are many important advantages to isolating a tenant in a database.

Tenant data is isolated.  This is important in selling to your customers, and it makes it very easy to make sure that tenants can never see each others' data.
You can upgrade and patch tenants separately.
You get the ability to move, backup, restore, export a tenant-at-a-time for free.  Critically this gives you point-in-time restore for single tenants.
Databases are a real security boundary, and you can enable users to see only a single tenant's data.
You don't have to write tenant export, and tenant import processes to move a tenant from one database to another, which you otherwise would have to write.
You can scale tenants separately, giving large or premier tenants differentiated access to server resources, or even giving them their own dedicated resources.  This makes your solution easier to manage, mitigates risk, and creates pricing opportunities for you.
Each tenant gets their own query plan cache, so you don't have query plans optimized for a small tenant used for queries for the data of a large tenant or vice versa.  And you have visibility into the performance metrics for each tenant.


Is it possible to write an SQL query that automatically reconciles/"transactionalizes" payments and charges

You might be able to do the INSERT into Payemnts_To_Charges in one SQL statement, but I'm not sure it would be worth it.  It seems like this would be easier to build, debug, and maintain in procedural code.  Something like this:

CREATE TABLE Payments (PaymentId Number(10), Amount Number(6,2));
CREATE TABLE Charges  (ChargeID Number(10), Amount Number(6,2));
CREATE TABLE Payments_To_Charges
   (PaymentID Number(10), ChargeID Number(10), Amount Number(6,2));

INSERT INTO Payments VALUES (1,4);
INSERT INTO Payments VALUES (2,4);

INSERT INTO Charges VALUES (1,2);
INSERT INTO Charges VALUES (2,5);
INSERT INTO Charges VALUES (3,6);
INSERT INTO Charges VALUES (4,4);
INSERT INTO Charges VALUES (5,10);

Declare
   vPaymentAmount Payments.Amount%Type;
   vAppliedAmount Payments_To_Charges.Amount%Type;
Begin
   For vPayment In (SELECT PaymentID, Amount FROM Payments) Loop
      vPaymentAmount := vPayment.Amount;
      For vCharge In (
            SELECT ChargeID, Amount FROM
            (
               SELECT ChargeID, Amount -
                  NVL((SELECT SUM(Amount) FROM Payments_To_Charges pc
                       WHERE pc.ChargeID = c.ChargeID),0) Amount
               FROM Charges c
            ) WHERE Amount > 0
         ) Loop
         vAppliedAmount := LEAST(vPaymentAmount, vCharge.Amount);
         INSERT INTO Payments_To_Charges (PaymentID, ChargeID, Amount)
            VALUES (vPayment.PaymentID, vCharge.ChargeID, vAppliedAmount);
         vPaymentAmount := vPaymentAmount - vAppliedAmount;
         If (vPaymentAmount = 0) Then
            Exit;
         End If;
      End Loop;
   End Loop;
End;
/

SELECT * FROM Payments_To_Charges;

Update:

remaining_balance is the issue.  You can't reference the aliased value in the WHERE clause.  You could either make the query a sub-query and add that condition on a higher level or change remaining_balance in the WHERE clause for (charges.amount - transactions.total_paid).


Is shrinking a transaction log OK to do on a live database?

DBCC SHRINKFILE itself does not make the database go offline. However, simply executing this command will not necessarily reduce the size on disk of the log, which depends on when the last full (for SIMPLE recovery types) or t-log (for FULL recovery type) backup was taken. If there's not been a recent backup of the logs then there'll be no log space to reclaim.

Finally, unless there has been some unforeseen, explosive growth due to an abnormally large write operation/batch, you may want to reconsider shrinking the file because the database is showing a need for a log file that large and the server needs to dedicate resources to expanding it back out. Perhaps adding storage would be a reasonable alternative.


pgAgent installation with Stack Builder in PostgreSQL

You write:

  I choose PostgreSQL 9.1 on port 5433

The error message says:

  Is the server running on host "localhost" (127.0.0.1) and accepting
  TCP/IP connection on port 5432?

Emphasis mine. Who is wrong? Seems like you are connection to the wrong port / db cluster.


how to use the keyword `VALUES` in an `IN` statement?

One option is to reference the VALUES clause just as you would if it were materialised.
INSERT INTO [APCore].[usr].[userBusinessArea]
           ([businessAreaID]
           ,[userID])
SELECT
    businessAreaID,
    userID
FROM (
     VALUES
           (21, 368),
           (21, 227419) ,
           (21, 99906),
           (21, 140470)
) v (businessAreaID, userID)
WHERE v.userID NOT IN (368,227419,99906,140470);

See also the examples in the docs
Try it on db<>fiddle


missing FROM-clause entry for table in trigger function in postgresql

You are trying to insert 42 values into 41 columns.  Check vendor_legal_id.


In SQL Server you could do something like this

IF EXISTS (
        SELECT 1
        FROM sys.objects AS o
        INNER JOIN sys.schemas AS s
            ON s.schema_id = o.schema_id
        WHERE CONCAT (s.NAME, '.', o.NAME) = 'dbo.UM_CLAIM_BEHAVIOR'
            AND o.type_desc = 'USER_TABLE'
        )
BEGIN
    DELETE T
    FROM UM_CLAIM_BEHAVIOR AS T
    WHERE UM_TENANT_ID = ?
END


Is it possible to disable logging on certain tables within the database?
  Can I place the two tables in the same schema, then disable logging on the schema?
  Is the only option to move the two tables to a separate database, and disable logging there?

Logging of user operations cannot be disabled.

There is a class of operations called minimally-logged operations that only allows a transaction to be rolled back (as opposed to also being able to roll forward). However, it doesn't sound like what you're doing would meet the criteria for that to apply, nor do I think this would solve the problem anyway.

The SQL Server solution would be to land the staging tables in a new database in SIMPLE recovery, separate from the aggregation results database, which I assume is in FULL recovery. While this wouldn't decrease the volume of logging, it would eliminate the need to back up the log generated by the data loads. (See my blog post here about logging, and some of the factors involved in choosing a database's recovery model.)

Separating the tables into another database would provide the flexibility to land the log file on a faster I/O subsystem, probably a small array of locally-attached solid-state drives.

Also, consider that SQL Server alone may not be the best solution to solve the problem. There are other RDBMS solutions that offer the ability to completely disable logging for certain tables. The data could be staged and aggregated in another system, and the results merged into the existing SQL Server database, which is well-protected by full logging and backups.


how to install PL/SQL Developer on centos

If you were referring to PL/SQL Developer it is only supported on windows

  PL/SQL Developer will run on Windows XP, 2003, Vista, Windows 7,
  Windows 8, Windows 8.1 and Windows 10.

If you were talking about Oracle SQL developer it is supported on

  Oracle SQL Developer 4.1 is available for Windows 7,8,10 and Windows
  Server 2008/2012, Linux or Mac OS X

You need to install Java version 1.8 and the SQL Developer executable. From here

Ensure you have a JDK installed, if not, download here
Download SQL Developer
rpm -Uhv sqldeveloper--1.noarch.rpm
cd sqldeveloper - sqldeveloper


How to Set Up a Shadow Production Database for PostgreSQL?

One option is to use a "hot standby" server, either with log shipping or with streaming (or both).  This will automatically apply changes from the master into the replica whenever there is connectivity. The problem is that the hot standby server is read only.  If your Django never needs to write into the database, this is fine, but if it needs to write this will not work.  So this will likely not work, as most applications will write into the database when someone logs in, even if the application is only used for reporting purposes.  Another potential problem is that unlogged tables are not available on the hot standby.

Another possibility to set up a standby server, and then promote it when you need to use it.  When you promote it, it will become writable, but it will sever the connection to the master and so will slowly diverge.  You will have to recreate it every now and then in order to bring them back into sync.  If you need to minimize downtime, you can be using the old promoted copy while you are re-establishing a new standby copy in the background.  This is a bit more work, and requires you to have enough storage for two full copies of the database (although it could be less if your master changes slowly and your file system supports fancy deduplication)

You could use logical replication to replicate changes from the master into a "live" writable test database.  This is a lot more work as you need to go through all the tables and decide which ones should have changes replicated from the master and which should stand alone on the replica after the initial sync, and which ones need to do both with some kind of conflict resolution.  And if the purpose of this dev database is so that you can test work that is likely to have bugs and cause bad data to get into your database and you want to protect the production database from getting corrupted with this bad data, this won't work very well.  Locally caused corruption will remain in the local database, replicating new changes from the master is unlikely to fix the bad local data, so you will need to do a full refresh to get rid of the bad data.  So overall, this method will probably be more work but no more effective than the previous solution.  This also has the problem that it will not replicate schema changes.  So if your master schema has not been stabilized but is still getting new tables, new columns, etc. this will be a nightmare.

  Further, what do I do if I want to get the shadow prod DB onto a laptop. What happens when the database size grows prohibitively large?

Either create a demo database which has enough data to serve its purpose, and forget about trying to keep it up to date; or get a bigger laptop.  If really really need to have all the latest data from the master, but don't need the old stuff, then you can use logical replication to do that.  You can create your subscription with "copy_data false" to avoid copying the historical data.  Presumably you need at least some history, but it would then be on you to figure out how much and how to get it.


How do I tactfully bring up database shortcomings?

Here's the line I like to use - it preserves everyone's dignity and lets everyone escape without finger-pointing:

"Yes, that used to be a best practice, and..."

It’s almost easier to explain that line in terms of what it DOESN’T do.  It doesn’t dispute the speaker’s claim, because you don’t want to go down the rathole of arguing about whether or not the point was right at the time.  You could spend the entire day arguing about why USB has the word Universal as part of the name, but that’s beside the point.  You need to get to the right solution as fast as possible, and sometimes that just means ignoring something that’s wrong.

It doesn’t chastise the other guy for not keeping up with the latest tips and tricks.  It’s hard enough to just do our jobs, let alone keep up with blogs and training, and real life interferes.  It lets the other person save face.

It doesn’t divide you against the other guy.  I try to use the word “we” whenever I use this line, because I want to take the other guy along with me on the journey to fix this problem.

I've written more about it in my Consulting Lines series.


Config enable sharding collection in MongoDB cluster

You can enable sharding on collection base, i.e. collection T3 is sharded.
Unsharded collection are created in the Primary Shard, see Sharded and Non-Sharded Collections

Sharded and Non-Sharded Collections
A database can have a mixture of sharded and unsharded collections. Sharded collections are partitioned and distributed across the shards in the cluster. Unsharded collections are stored on a primary shard. Each database has its own primary shard.

Typically the Primary Shard is the least loaded shard at the time when the database was created. When you are not happy with the selected Primary Shard then you can move it with movePrimary command. But you have to move all collections of one database, you cannot move a single collection.
db.adminCommand( { movePrimary: <databaseName>, to: <newPrimaryShard> } )


How to "join" two tables without PRIMARY KEYs

If we're to assume that these tables are actually used to determine who has access, then the query from blobbles' answer is the answer, even if there are multiple people with the same name in Persons.

If there are 4 "John Smith"s in Persons, and Access indicates "John Smith" has data access, then every "John Smith" would have data access.

If this is merely a record of access granted (rather than what defines the access granted), and Persons.name is not unique, then you can't know for certain. Perhaps two "John Smith"s have been granted access, perhaps only 1, perhaps none. The record is incomplete, because we don't know how access ties back to different Persons with the same name.


SQLCMD -L returns a number of servers that definitely do not have SQL installed

The servers might be running SQLServer Express and/or LocalDB

SCCM has a SQL Server backend with a runtime license, Domain Controllers might have a localdb instance and so forth, check in the services app on the servers for those or run the following on the command prompt

tasklist /FI "imagename eq sqlservr.exe"


Postgres - default date format when interpreting strings

There is the datestyle parameter which you can set to something like ISO, DMY or ISO, MDY to influence how such ambiguous dates are interpreted.

But if you allow free-form date input, PostgreSQL's heuristics are bound to get it wrong at some point. So to make your program robust, always force your dates into a certain format and either use the ISO format and rely on automatic conversion or use to_date to parse the strings.


Sync MongoDB Replica set with Remote Replica Set

You can follow your original strategy with a minor tweak: add Google Compute (GC) nodes into the current replica set and keep them in sync until ready to cut over. The only thing you need to change is to set those new GC nodes to have priority 0. They will be full members of the set, they just cannot be elected primary (or trigger elections).

Once you are ready to switch over to the Google nodes, you need only reset the priority to 1 (or whatever your normal values are), and then step down or remove the AWS nodes from the set. You can guarantee that a GC node is elected primary by giving them a higher priority than the AWS nodes before issuing the step down.


Replication Subscribers and AlwaysOn Availability Groups (SQL Server)

The first problem:

Error 21488

  No active subscriptions were found. The publication must have active subscriptions in order to post a tracer token.

This happens when attempting to put in a tracer token without an active subscription. This error seems pretty obvious.

Error 18752

  Only one Log Reader Agent or log-related procedure (sp_repldone, sp_replcmds, and sp_replshowcmds) can connect to a database at a time. If you executed a log-related procedure, drop the connection over which the procedure was executed or execute sp_replflush over that connection before starting the Log Reader Agent or executing another log-related procedure.

Make sure there are no other logreader agents running for the same publishing database.

Second part:

  Second problem- How to maintain primary key value to avoid conflict between publisher 1 and publisher 2 to sync with node-1 because database are same ??

You'll either need to add a unique field (such as servername, etc) or configure some way of making each record unique. Generally, if not designed for at first, will require a schema change. Commons solutions are to use even/odd numbering in identity columns/id fields or using the source host name/database name.


PostgreSQL 9 'pgstats wait timeout' problem

Reloading the config makes the errors go away, and performance improves (and db statistics start getting recorded again).

I've set up a Windows Scheduled Task which performs a pg_ctl reload -D"insert here the address of your pg directory" on the hour (we're running an incredibly busy application). Hacky, but it's working.


How to add foreign key "with more than one column" in SQL Power Architect

If the CPF value in TABASSASSINATES should always be the value from the related CONTRATOA table, it is redundant data.  Foreign keys won't enforce the relationship.

In other cases, the column will have applicable business rules, and you may not want to force the value in TABASSINATES to change when the CONTERATOA value is updated. The business rules won't appear in the model you have extracted above.

If it is possible to have multiple CPF values in CONTERATOA for the same CDCONTR value, the primary key should include CPF.  In this case, the foreign key relationship will include both columns.

It is possible, but highly discouraged, to model the CONTERTOA primary key with both columns and add a unique contraint on the CDCONTR column.


The very first thing you should do is take a backup of your database using mysqldump or similar, because if you mangle something, your root password will be useless anyway.


Your log_reuse_wait_desc is database_mirroring and you mentioned it currently stuck in a Suspended state

select name, log_reuse_wait_desc from sys.databases

You should find a cause your mirroring was suspended. Try to search messages that include Database mirroring has been suspended in ERRORLOG file on the principal. It could be like one below:

  2019-08-13 11:40:48.98 spid64s     Error: 1453, Severity: 16, State:
  1.

  2019-08-13 11:40:48.98 spid64s     'TCP://xxx.com:5022', the remote mirroring partner for database 'DB', encountered error 5149, status 3,
  severity 25. Database mirroring has been suspended.  Resolve the error
  on the remote server and resume mirroring, or remove mirroring and
  re-establish the mirror server instance.

Using error number you are able to get its description from sys.messages:

select * from sys.messages where message_id = 5149

This case:

  MODIFY FILE encountered operating system error %ls while attempting to
  expand the physical file '%ls'.

That means there is not enough physical space to expand a file on the mirror.

After solving any found errors you have to turn on your mirroring. Run on the principal:

ALTER DATABASE YourDBName SET PARTNER RESUME;

In case you won't be able to find any related errors in ERRORLOG, just try to resume your mirroring using a command above and then look after the database and check your ERRORLOG for errors once the mirrored database goes to the suspended state (if it does).


Check that your SQL Server allows SQL authentication. By default it will be windows authentication only. You can check the SQL Server errorlog which will provide the login failure reason.

The message I you would see in the log in that situation would be

  Login failed for user 'FYI_DBA'. Reason: An attempt to login using SQL
  authentication failed. Server is configured for Windows authentication
  only. [CLIENT: ]

You can change the authentication type using the GUI or T-SQL


backup history using availability groups

We made a SSIS package that collect msdb history from each instance and centralize it on a "DBA" database. We then "purge" the local msdb from each instance.

That way, we can query the centralize DB for any backup history and it prevent every msdb from getting to big.


Return value formatted similar to if casted as money

This is really ugly and hopefully serves as a lesson as to why you should be fixing the receiving system to not be so fussy about trailing zeroes.

DECLARE @t TABLE(a DECIMAL(20,10));

INSERT @t VALUES
(1826.5695600000),
(1826.3000000000),
(  34.2300000000),
(   0.0030000000);

DECLARE @d INT = 10; -- number of decimal places in source data!

;WITH s(a,dot,nz) AS
(
  SELECT
    CONVERT(VARCHAR(32),a),
    PATINDEX('%.%',CONVERT(VARCHAR(32),a)),
    PATINDEX('%[^0]%',REVERSE(a))
  FROM @t
)
SELECT LEFT(a,dot)+SUBSTRING(a,dot+1,@d+1-nz)
  + COALESCE(REPLICATE('0',nz-(@d-1)),'')
FROM s;

Results:

1826.56956
1826.30
34.23
0.003

This assumes the source data type is a decimal(something,10). If it is a string, you need to have some extra precautions in here for values that aren't numbers (or aren't really numbers in this sense).


How are these null values stored in a NOT NULL column?

What you described is not normal.

As already mentioned in comments, in Oracle databases, empty/zero-length strings are treated as NULL.

https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements005.htm#i59110

  Note:

  Oracle Database currently treats a character value with a length of
  zero as null. However, this may not continue to be true in future
  releases, and Oracle recommends that you do not treat empty strings
  the same as nulls.

Also, WHERE col = '' will never return results, because it is basically WHERE col = NULL.

It is fairly simple to reproduce these wrong results by corrupting the data dictionary manually, but I do not know what caused it in your environment just from this information.

SQL> create table t1 (c1 varchar2(20) not null disable);

Table created.

SQL> select constraint_name from user_constraints where table_name = 'T1';

CONSTRAINT_NAME
------------------------------
SYS_C005148

SQL> insert into t1(c1) values ('');

1 row created.

SQL> commit;

Commit complete.

SQL> select count(*) from t1 where c1 is null;

  COUNT(*)
----------
         1

So far everything is normal.

The database can skip entire steps while executing a SQL statement based on a constraint. If you have an enabled, validated, NOT NULL constraint on col, and your predicate is col is null, the database knows that column can not contain NULL, so it will return 0 rows without actually executing the related steps. If you have an enabled, validated constraint on a column, the NULL$ column in the COL$ dictionary table for that column is set to 1. But even with a disabled, non-validated constraint, corrupting NULL$ is enough for the database to return wrong results.

The proper method for enabling that constraint should be (which obviously fails):

SQL> alter table t1 modify constraint SYS_C005148 enable validate;
alter table t1 modify constraint SYS_C005148 enable validate
                                 *
ERROR at line 1:
ORA-02293: cannot validate (SYS.SYS_C005148) - check constraint violated

Now I set NULL$ manually:

SQL> update col$ set null$ = 1 where obj# = (select object_id from user_objects where object_name = 'T1') and name = 'C1';

1 row updated.

SQL> commit;

Commit complete.

Then run the query again:

SQL> select /*+ gather_plan_statistics */ count(*) from t1 where c1 is null;

  COUNT(*)
----------
         0

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  fjf8bcs2hhb7b, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from t1 where c1 is null

Plan hash value: 4294799605

----------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      1 |00:00:00.01 |
|   1 |  SORT AGGREGATE     |      |      1 |      1 |      1 |00:00:00.01 |
|*  2 |   FILTER            |      |      1 |        |      0 |00:00:00.01 |
|   3 |    TABLE ACCESS FULL| T1   |      0 |      1 |      0 |00:00:00.01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(NULL IS NOT NULL)

Note
-----
   - dynamic sampling used for this statement

Returns 0 rows, the table was not accessed at all (Starts = 0 on Operation Id 3), because of the FILTER on Operation Id 2: NULL IS NOT NULL, which obviously to FALSE.

If you use NVL(col, 'N') instead of col, then the database is not able to use this kind of optimization, will access the table and return the correct result:

SQL> select /*+ gather_plan_statistics */ nvl(c1, 'N'), count(*) from t1 group by nvl(c1, 'N');

NVL(C1,'N')            COUNT(*)
-------------------- ----------
N                             1

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
SQL_ID  0zfyk18knxtfk, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ nvl(c1, 'N'), count(*) from t1 group by nvl(c1, 'N')

Plan hash value: 136660032

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |       3 |       |       |          |
|   1 |  HASH GROUP BY     |      |      1 |      1 |      1 |00:00:00.01 |       3 |  1156K|  1156K|  323K (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
----------------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

No FILTER this time, and the table was accessed (Starts 1 on Operation Id 2 - TABLE ACCESS FULL).

There was a bug in 10g that caused wrong results with the optimization for CHECK constraints with NULL on transitive predicates:

Bug 5462687 - CHECK constraint can cause wrong results (Doc ID 5462687.8)

This will not help me, because I corrupted the dictionary myself. But if your actual query is more complex than you posted (otherwise do not bother with this), and have transitive predicates, you could try the workaround written in this, and disable this behaviour by setting event 10195

$ oerr ora 10195
10195, 00000, "CBO don't use check constraints for transitive predicates"
// *Cause:
// *Action:

Example:

alter session set event '10195 trace name context level 1';

I do not think this caused by how data is stored, rather just something about the constraints.


Move new data records automatically to another database in oracle xe

SQL> Create Directory EXPDIR as 'PATH';

The export command

expdp directory=expdir dumpfile=newExport.dmp full=y logfile=newExport.log

on the target database also create a directory

Create Directory EXPDIR as 'PATH';

The import command

IMPDP directory=ExpDir dumpfile=newExport.dmp Logfile=import_Database.LOG FULL=Y

By the way this is a full export and import, you can customize it according to your preference on what exactly to export or import

https://docs.oracle.com/cloud/latest/db112/SUTIL/dp_export.htm#SUTIL200


db2 range partitioning attach and detach issue

Use this:

db2 +c "lock table tabname in share mode"
db2 +c "alter table tabname add partition <partname> starting 'date' ending 'date' exclusive"
db2 "commit"

Here is the explanation:

+c : disable auto commit, so the command you send will not be committed until an explicit commit command sent or connection closed.

locking table in share mode: means that you want to acquire a shared lock which will prevent other transactions to make changes to the table. So, following command won't be blocked while working.

In this approach you say that: lock the table for me, then issue partition command, while issuing this command other transaction will wait till you finished your job and send commit statement.

I am not sure if it works or not. But it worth a try.


Why does STRAIGHT_JOIN consume more CPU?

According to the MySQL Documentation on JOIN Syntax

STRAIGHT_JOIN is similar to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases for which the join optimizer puts the tables in the wrong order.

The comment posted suggested using EXPLAIN on both versions of the query. Based on this suggestion and the join optimizer's current behavioral settings, the output of the two EXPLAIN plans should be noticeably different. Being more specific, the order of the tables in EXPLAIN plan of the STRAIGHT_JOIN will have tables being accessed in a different order from the other EXPLAIN plan of the regular join.
OPTIONAL SUGGESTIONS
You could change the optimizer switches to change its decision making process.
You could run one of the following switch settings with your regular JOIN:
SET optimizer_switch = 'batched_key_access=off,block_nested_loop=off'; SELECT ... JOIN
SET optimizer_switch = 'batched_key_access=off,block_nested_loop=on'; SELECT ... JOIN
SET optimizer_switch = 'batched_key_access=on,block_nested_loop=off'; SELECT ... JOIN
SET optimizer_switch = 'batched_key_access=on,block_nested_loop=on'; SELECT ... JOIN

Then, you could run one of the following switch settings with your STRAIGHT_JOIN:
SET optimizer_switch = 'batched_key_access=off,block_nested_loop=off'; SELECT STRAIGHT JOIN
SET optimizer_switch = 'batched_key_access=off,block_nested_loop=on'; SELECT STRAIGHT JOIN
SET optimizer_switch = 'batched_key_access=on,block_nested_loop=off'; SELECT STRAIGHT JOIN
SET optimizer_switch = 'batched_key_access=on,block_nested_loop=on'; SELECT STRAIGHT JOIN

You could measure CPU and query performance for each of these eight(8) JOINs.
GIVE IT A TRY !!!
BTW other have good answers on STRAIGHT_JOIN

Apr 28, 2011 : Why does STRAIGHT_JOIN so drastically improve this query, and what does it mean when it is written after the SELECT keyword?
Feb 04, 2009 : When to use STRAIGHT_JOIN with MySQL


Getters and Setters implies retrieving the data for one row and changing the data for one row.  It sounds like you are planning to call a get procedure for each row you want in the file level table and then for each of those rows loop through getting the corresponding data from the batch level table etc.

My suggestion is that you write one SQL statement that selects all the data you need from the file level table joining in the data you need from the batch level table.

I don't know anything about BI Publisher, so based on your comment, none of this may apply. Perhaps someone familiar with BIP can say whether these parameters could be bound into the query to allow the query to determine what data should be returned.  A PL/SQL method may be required for this application, but it needn't be more than a wrapper for your SQL.


Implement a two sided zero to many relationship?

For practical purposes, if you're implementing a many-to-many relationship with an intersection table - which is by far the most common way to do it, then you will in effect have a "zero or more to zero or more" relationship.

Relational databases don't have declarative referential constraints that enforce the child end of a one to many relationship.  In other words, your CAR table can't have a constraint (imposed by DRI) that enforces 0, 1 or many owners.  DRI works on the table with the foreign key, not the parent (referenced) table.

This means that unless you implement some additional procedural logic in your application, you wouldn't be able to enforce 1 or more, so you in effect get 0, 1 or more to each of CAR and PERSON from the intersection (OWNERSHIP).

Implementing a many-to-many relationship in this way is consistent with correct normalization.


NTFS Filesystem Fragmentation issue occur after migrating mysql 5.6 database to mariadb 10.2

This is a bug, which I filed and fixed, based on this question. BTW, we do have a bug system too, do not hesitate to use it directly.

Here is the bug report. https://jira.mariadb.org/browse/MDEV-13941

Basically, fragments are created because sparse file is being extended. The fix is not to create sparse files, unless user wants a table with page compression (which is an exotic feature that not many people would use). The fix will appear in the next 10.2 (i.e 10.2.10), mid-end October.

However, just the upgrade to 10.2.10 won't automatically fix  the problem for existing tables. There is something else you need to do prior to installation of 10.2.10

Stop the server
Unset the "sparse flag" on .idb files. in elevated command prompt, type

fsutil sparse setflag C:\path\to\table.ibd 0

Defragment file with contig (or other tools)


Design: How do you decide when items are related vs when they are attributes?

One rule of thumb is that if it is 'physical' (concrete as opposed to abstract) then it is an entity otherwise it is an attribute.

Another rule of thumb in ER modelling is that an table EITHER models an entity OR a relationship between entities but never both. There can be other kinds of table, of course e.g. it is sometimes conducive to put all attributes of the same type (e.g. colors) in a table, known as a lookup table (i.e. is neither an entity table nor a relationship table but is still valid and useful).


Log files in Always on Disaster Recovery Situation

Is there any way I can handle it better than just letting the log files grow?

I believe you have the ideal options covered already. You either

Let the log files grow for the duration of the outage, or
Remove the replicas that are offline from the AG (and re-seed them when the primary datacenter is available)

I'd lean towards #1 if you have the disk space, and #2 if you become aware that there will be an extended outage (or disk space is low).


How do I join getting one row from the left table, no matter how many matches i get from the right table?

DBFIDDLE

SELECT
    d.id1, d.id2, d.id3, d.id4
FROM (SELECT ROW_NUMBER() over (ORDER by d1.id1,d1.id2,d1.id3,d1.id4) as r,
             d1.*
      FROM DataTable d1) d
INNER JOIN MappingTable m on
    (m.id1=d.id1 or m.id1 is null) and
    (m.id2=d.id2 or m.id2 is null) and
    (m.id3=d.id3 or m.id3 is null) and
    (m.id4=d.id4 or m.id4 is null)
group by d.r,d.id1,d.id2,d.id3,d.id4;

output:

id1         id2         id3         id4
----------- ----------- ----------- -----------
1           1           1           1
1           1           1           1
2           1           0           1
2           1           0           1
2           1           1           1
2           2           1           1
3           3           1           3


Should I use mysql_upgrade after mysqld_safe when upgrading MariaDB?

When you upgrade versions, run the upgrade script.  Often there are new columns in various system tables.


better approach to design company hierarchies in table

If your hierarchy is rigid (i.e. the levels are consistent and mandatory) then there is nothing wrong with representing each level as it's own table.  This is your first option, except that instead of having foreign keys on the person table to each of the organization levels, you should have a single foreign key on the person table to the leaf level of your organization, and then each level in turn refers to it's parent/container.  i.e.:

Person => Service => Team => Group => Domain => Company

With this modification, your tables will be in third normal form (3NF) which is always a good starting point from a design perspective.

If, on the other hand, your hierarchy might be at all fluid, then keeping a hierarchy in a single table with a self-referencing (involuted) foreign key is a better option from a code stability perspective.  This is your second option.

One issue with this second option is that relational databases are not brilliant at handling unleveled trees in this form.  There are, however, a couple of pretty useful techniques for managing this type of data in this format a little more easily.  You can Google around for adjacency lists and visitation numbers to learn more.


In general should we avoid using ORM's with Redshift

There are known drawbacks related to using ORMs, period. They are a result of the inherent impedance between the object-oriented and relational models of the world. These issues will probably be exacerbated by the lack of Redshift-specific Hibernate dialect, and using a Postgres dialect will likely cause additional problems, as many Postgres features are not supported by Redshift.

In short, my opinionated advice would be, don't use an ORM for any non-trivial application, Redshift or not. "[G]ranular point in time rollback solution for Redshift data" doesn't sound like one.


The answer to your question depends on whether-or-not you're within a transaction that will span over more than one statement. (You've tagged the question with InnoDB, the answer would be different with MyISAM.)

From the reference manual: http://dev.mysql.com/doc/refman/5.1/en/commit.html

  By default, MySQL runs with autocommit mode enabled. This means that
  as soon as you execute a statement that updates (modifies) a table,
  MySQL stores the update on disk to make it permanent.

So yes, by default, if you're just using INSERT, the records you insert will be committed, and there is no point trying to roll them back. (This is effectively the same as wrapping each statement between BEGIN and COMMIT.)

However, if you're dealing with transactions explicitly, you will have to use COMMIT to commit store the records, but you will also be able to use ROLLBACK.

You can start a transaction explicitly by using START TRANSACTION (or BEGIN). This is independent of the autocommit setting (on by default):

  With START TRANSACTION, autocommit remains disabled until you end the
  transaction with COMMIT or ROLLBACK. The autocommit mode then reverts
  to its previous state.

Alternatively, if autocommit=0, I think any statement following another end of transaction, will start a transaction (but you can still use START TRANSACTION explicitly); that's at least the way I interpret this:

  The autocommit mode. If set to 1, all changes to a table take effect
  immediately. If set to 0, you must use COMMIT to accept a transaction
  or ROLLBACK to cancel it. If autocommit is 0 and you change it to 1,
  MySQL performs an automatic COMMIT of any open transaction. Another
  way to begin a transaction is to use a START TRANSACTION or BEGIN
  statement. See Section 12.3.1, “START TRANSACTION, COMMIT, and
  ROLLBACK Syntax”.

More specifically "another way to begin a transaction" seems to imply that setting "autocommit=0" is enough to start a transaction (at least just before each statement at the start a session or that follows a COMMIT/ROLLBACK).
I would suggest to use BEGIN or START TRANSACTION explicitly anyway even if autocommit=0, as it can make it clearer to see when the transaction starts or ends.

(How you start a transaction may depend on the way your application uses MySQL.)


How to configure SQL Server Port on multiple instances?

have fixed sqldwdev01
the reason I couldn't connect to UAT
instance is because of a couple of reasons

there was a dynamic port specified for the instance under SSNetworkConfig\Protocols for UAT, so had to remove that and specify 1435 (as 1434 is in use) then restart the service
still cant connect remotely to the instance name but can connect using the port name eg sqldwdev01,1435 so it needed sql browser service to be running to direct the port for connecting with instance name

i started sql browser and now  can connect using sqldwdev01\uat