How to measure the size of InnoDB's fulltext index

I don't know the answer; let me try to figure it out.  First, I don't see anything likely in information_schema or mysql.  In the filesystem, in the directory with same name as the database, look for files starting with FTS_.  If you have multiple tables with FULLTEXT indexes, segregate them by prefix, then sum up the sizes.  For one table, I see

SHOW CREATE TABLE fti

CREATE TABLE `fti` (
  `msg_id` mediumint(8) unsigned NOT NULL,
  `title` varchar(255) NOT NULL,
  `msg` longtext NOT NULL,
  PRIMARY KEY (`msg_id`),
  FULLTEXT KEY `title_msg` (`title`,`msg`),
  FULLTEXT KEY `title` (`title`),
  FULLTEXT KEY `msg` (`msg`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ;

mysql> SHOW TABLE STATUS LIKE 'fti'\G

           Name: fti
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 175483
 Avg_row_length: 1766
    Data_length: 310050816
Max_data_length: 0
   Index_length: 3686400
      Data_free: 4194304

ls -l FTS* (blank lines added)

-rw-rw---- 1 mysql mysql 37748736 Jan 16  2017 FTS_0000000000000090_0000000000000107_INDEX_1.ibd
-rw-rw---- 1 mysql mysql 83886080 Jan 16  2017 FTS_0000000000000090_0000000000000107_INDEX_2.ibd
-rw-rw---- 1 mysql mysql 50331648 Jan 16  2017 FTS_0000000000000090_0000000000000107_INDEX_3.ibd
-rw-rw---- 1 mysql mysql 50331648 Jan 16  2017 FTS_0000000000000090_0000000000000107_INDEX_4.ibd
-rw-rw---- 1 mysql mysql 83886080 Jan 16  2017 FTS_0000000000000090_0000000000000107_INDEX_5.ibd
-rw-rw---- 1 mysql mysql 30408704 Jan 16  2017 FTS_0000000000000090_0000000000000107_INDEX_6.ibd

-rw-rw---- 1 mysql mysql   507904 Jan 16  2017 FTS_0000000000000090_000000000000010e_INDEX_1.ibd
-rw-rw---- 1 mysql mysql 13631488 Jan 16  2017 FTS_0000000000000090_000000000000010e_INDEX_2.ibd
-rw-rw---- 1 mysql mysql 11534336 Jan 16  2017 FTS_0000000000000090_000000000000010e_INDEX_3.ibd
-rw-rw---- 1 mysql mysql 11534336 Jan 16  2017 FTS_0000000000000090_000000000000010e_INDEX_4.ibd
-rw-rw---- 1 mysql mysql 14680064 Jan 16  2017 FTS_0000000000000090_000000000000010e_INDEX_5.ibd
-rw-rw---- 1 mysql mysql 10485760 Jan 16  2017 FTS_0000000000000090_000000000000010e_INDEX_6.ibd

-rw-rw---- 1 mysql mysql 37748736 Jan 16  2017 FTS_0000000000000090_0000000000000115_INDEX_1.ibd
-rw-rw---- 1 mysql mysql 79691776 Jan 16  2017 FTS_0000000000000090_0000000000000115_INDEX_2.ibd
-rw-rw---- 1 mysql mysql 50331648 Jan 16  2017 FTS_0000000000000090_0000000000000115_INDEX_3.ibd
-rw-rw---- 1 mysql mysql 50331648 Jan 16  2017 FTS_0000000000000090_0000000000000115_INDEX_4.ibd
-rw-rw---- 1 mysql mysql 83886080 Jan 16  2017 FTS_0000000000000090_0000000000000115_INDEX_5.ibd
-rw-rw---- 1 mysql mysql 29360128 Jan 16  2017 FTS_0000000000000090_0000000000000115_INDEX_6.ibd

-rw-rw---- 1 mysql mysql    98304 Jan  9  2017 FTS_0000000000000090_BEING_DELETED_CACHE.ibd
-rw-rw---- 1 mysql mysql    98304 Jan  9  2017 FTS_0000000000000090_BEING_DELETED.ibd
-rw-rw---- 1 mysql mysql    98304 Jan 16  2017 FTS_0000000000000090_CONFIG.ibd
-rw-rw---- 1 mysql mysql    98304 Jan  9  2017 FTS_0000000000000090_DELETED_CACHE.ibd
-rw-rw---- 1 mysql mysql    98304 Jan  9  2017 FTS_0000000000000090_DELETED.ibd

The files add up to about 340MB for the largest index (title+msg).

The total text size (SUM(LENGTH...) is 220MB; 310MB is about right once you add on the off-record storage overhead.  It seems reasonable for the FT index for a text column to be about the same bulk as the text.


How can I recover oracle database without new data?

You performed a complete recovery. Recovery uses the available archivelogs and redo logs as well.

To perform an incomplete recovery until the time that's before the changes, use:

recover database until time "to_date('2018-02-24 15:05:20', 'YYYY-MM-DD HH24:MI:SS')";
alter database open resetlogs;

Set the point of time as needed.


Baron Schwartz, the author of that post, is one of the coauthors of High Performance MySQL, 3rd Edition, one of the best books out there regarding MySQL performance. While the argument of authority is not always a good one, I would like to remark that probably he knows what he is saying.

While everything he says is correct -in my humble opinion-, you must understand the actual underlying argument: defragmenting an InnoDB table is in many cases useless (for performance), and many people recommending to do it frequently are wrong.

Fragmentation and page-splitting is a delicate topic, which people like Jeremy Cole http://blog.jcole.us/2013/04/09/innodb-bugs-found-during-research-on-innodb-data-storage/ and Facebook engineers have mentioned a lot (specially, regarding to compression): https://www.facebook.com/note.php?note_id=10150348315455933 and its implications on performance.

Many times, your performance is load-dependent- Do you insert using an auto-increment? Do you insert and delete many times in the middle of your table? Can you afford the extra disk space if your table is very dynamic?

There are some good practices that I can recommend you (which is probably what you want):

Defragment only if you have done a batch DELETE of lots of records (and you do not intend to insert them back). In other cases, it may not be necessary. If you want to know if there is a huge difference between logical data and file size, compare the .idb file with the data + index size from show table status.
Speedup insertion by inserting always in PRIMARY KEY order, so you do not force unnecessary page splits.
Use partitioning to isolate changes that may alter the internal structure of the table.
There is no way to "optimize the secondary indexes", but I would never find such a thing necessary. The change buffer makes sure that changes/rebalances to indexes are done asyncronously without huge performance problems. A BTREE should always be balanced, so assuming your change buffer is not full and the purge thread deletes old row records promptly, your performance should be ok. One way I can think of "optimizing secondary indexes", as you called it, is DROPing the index and recreating it (asuming you are using the InnoDB Plugin or MySQL 5.5+), but I see absolutely no reason to do that.

Of course, if you really want to dig into this topic, create some tables, defragment them and check if you actually have some gain afterwards. In general, tablespace handling and statistics gathering is something relatively automatic on InnoDB.


I may be missing something, but it seems like this "could" mean that the primary OLTP will not be impacted by long running (and blocking) queries on the secondary.

There isn't a one sized fits all answer, so assuming that the reporting workload doesn't run the secondary replica out of CPU/Memory/Disk/Network resources and there isn't any contention for said resources then the primary should not be impacted but log growth will occur, which in the end could impact the performance of the AG/SQL.

  I envision that the logs just build in size on the secondary until the blocking report query completes, and then applies all the changes, that have occured on the primary and been hardened to disk on the secondary.

That's the gist of it, if you can't truncate and are growing log that may eat up CPU/Disk time that is wasted or cause an issue with the disk growing too much and eating all available space. Don't laugh (or do!), I have witnessed it multiple times - it really does happen.

The other item which isn't discussed is synchronization and redo. Assuming synchronization stays nominal (synchronized) the redo may fall behind. Yes, at some point it might "catch up" but the SLA was already missed. REDO queue build up is a thing in extremely high performant environments and even though SQL Server 2016+ has the potential for parallel redo it doesn't mean it'll always be faster or that the database is using parallel redo. I'm assuming we don't care about automatic failover or failover times (database startup times will be affected).

  Does SQL Server 2016 eliminate poor primary performance when reports take locks on seconday?

ASSUMING all of the previous, it acts no differently than 2012/2014 in terms of readable secondary replicas. Yes, there were enhancements but the real reason why there are performance implications on the primary is mostly due to a resource being run out on a secondary and causing long waits which show up in the primary as HADR_SYNC_COMMIT.


It makes sense to store tags themselves in a separate table, and add a link table to implement many-to-many relationship between users and tags, for example

users (user_id (PK), name, [other user attributes]);
tags(tag_id(PK), tag_name, [other tag attributes]);
users_tags(user_id (FK to users) ,tag_id (FK to tags), PK(user_id,tag_id));

Depends on your needs, users_tags may have other attributes as well, for instance you might want to add datetime column to store when user added tag (surely, if it has any value to your application)


Postgres 10 Log Shipped Replica with cascading replication

PostgreSQL supports cascading replication, so you can configure your warm standby as a normal streaming replication standby server to your WAL shipping standby.
If you have recovery_target_timeline = 'latest' on that standby server (default in later PostgreSQL versions), that second standby server will keep replicating when you promote your WAL shipping standby.


What does Oracle XE perform during installation?

XE changes the following:

tnsnames.ora
Path variables
takes the default port 1521 for itself and this cannot be changed

The best way to install XE and other products is to install XE first, then any other Oracle version afterwards.

I think you can fix this by

examining the path variables by entering PATH at the command line
ensuring that each installation has a file path to the bin folder, something like this

c:\oracle\db11g\product\11.1.0\db_1\bin;
c:\XE\product\11.1.0\db_1\bin;
check that the listener.ora has the correct parameters by starting a command line and entering lsnrctl status  This will show you which listener is in use, it's location and what databases it is servicing.
change the port for the non XE database to anything other than 1521 in the listener.ora


How can I do a query that shows any row that have more than twice in the same table without using GROUP BY or COUNT

with data as
(
select 'New York' as city from dual union all
select 'Los Angeles' from dual union all
select 'London' from dual union all
select 'Washington' from dual union all
select 'London' from dual union all
select 'Peking' from dual union all
select 'London' from dual union all
select 'San Fransisco' from dual union all
select 'New York' from dual union all
select 'Tokyo' from dual union all
select 'Dubai' from dual
)
select
  distinct city
from
(
  select
    city,
    row_number() over (partition by city order by null) as rn
  from
    data
)
where
  rn > 1
;

CITY
-------------
London
New York

No GROUP BY, no COUNT.

But we have DISTINCT, ROW_NUMBER(), PARTITION BY, ORDER BY.

Is it simpler/better than?

with data as
(
select 'New York' as city from dual union all
select 'Los Angeles' from dual union all
select 'London' from dual union all
select 'Washington' from dual union all
select 'London' from dual union all
select 'Peking' from dual union all
select 'London' from dual union all
select 'San Fransisco' from dual union all
select 'New York' from dual union all
select 'Tokyo' from dual union all
select 'Dubai' from dual
)
select
  city
from
  data
group by
  city
having
  count(*) > 1
;

No, I guess.


How to make ST_DWithin to use meters for distance in Postgis?

Cast the type to geography,

SELECT ST_AsText(geom)
FROM table
WHERE ST_DWithin( geom::geography, ... )

Consider setting the type on the table to geography too.


Query execute slowly when changed compatibility Level of my database

I have recently seen this exact same problem with an upgrade from SQL Server 2008 R2 to SQL Server 2014 where a very small number of Stored Procedures (SPs) (approx 5 out of 3,000 ) performed worse in SQL 2014 Compatibility Level 120 than in SQL 2014 Compatibility Level 110 or in SQL  2008.

In SQL 2014 the Cardinality Estimator (CE) has changed which may impact how a SP performs in SQL 2014 Compatibility Level 120.

There are a few possible solutions

Leave the SQL 2014 Database running in Compatibility Level 120 and add a dbcc Trace Flag to force SQL Server 2014 to use the prior CE <-- I would not recommend this
Leave the SQL 2014 Database running in Compatibility Level 120 and Tempoarily add

OPTION (QUERYTRACEON 9481)

to the offending SP to force just that SP to use the prior CE. The SP should now perform in Compatibility Level 120 as it did with Compatibility Level 110. <-- I prefer this approach as your Database/System gains all of the other advantages of SQL 2014 while the "fix" only impacts the offending SP.

Irrespective of the approach I would then profile the execution plan/read/writes etc of the offending SP on a test system in SQL Server 2014 Compatibility Level 120 and rewrite the SP to cater for the new CE.

Once tested and verified I would then apply to production.

Microsoft have a good article on the Cardinality Estimator changes in SQL 2014


The simplest way to merge rows is with an aggregate function such as MIN/MAX. These functions will ignore nulls (see MSDN) and can operate similarly to ISNULL/COALESCE with aggregation. For example:

SELECT
  EmployeeNo,
  MAX(Column1) AS Column1,
  MAX(Column2) AS Column2
FROM MyTable
GROUP BY EmployeeNo

This will return the sample desired results. If your only goal is to merge columns with null values into rows with data in those columns, it doesn't matter whether you use MIN or MAX - the result set will be the same.


Why mysql multiple column index match from left?

it simple and very clear for understanding without technical details:

1,4,1
1,5,1
2,1,4
2,3,1
3,0,2

1st column is sorted

2nd column sorted independent for each cluster in 1st column

3rd column sorted for each 1+2 cluster

so, (2,1,x) will always after (1,4,x), but it is not true for (x,4,1) vs (x,1,4)


Data type - VARCHAR for ANSI which uses 1 byte per character or NVARCHAR for UNICODE which uses 2 bytes per character. In your case, I would suggest NVARCHAR, so you can store multiple languages within the same columns. Avoid TEXT and NTEXT as they are deprecated.

Searching - It is possible to use LIKE predicate, for simple searching with Wild Cards, but I suggest you investigate the use of Full Text indexing which will increase query performance and also allow you to use the FULLTEXT predicate for very flexible fuzzy searching.

Bullets and hyperlinks - I think you are thinking of SQL in the wrong way here. I think you should be thinking of those tasks within your client application. To facilitate this from the back end, hyperlinks I would split into a separate table with a key that you can then report on. For bullets, again strongly suggest controlling this through the client side, but if you really wanted to, you could insert a U+2022 at the beginning of each row. If there are multiple bullets per row of data, you could store this char at the beginning of each bullet and then use a string splitting function when reading from the table. Many ways of accomplishing this but suggest using XML sub query.


Mysql replication dump without down time? empty sql file

I found the problem. The problem is --flush-log option. I don't know why this causes the problem, but mysqldump works correctly after I remove the option.


How to index of filter extended event files in Sql Server

You could potentially filter the events using a custom program, but that would require a fair amount of work (mostly in testing) to ensure that you don't adversely impact the SQL Server.

You're probably best off simply logging all the errors, and sorting through them later (possibly on a different computer).  You can read the XEvent files with the function sys.fn_xe_file_target_read_file .


How to add comment to a column in a table in Postgres?

To update comment, don't use alter command.

Here is the PostgreSQL syntax:

comment on column app_user_bookings.mod is '1# mobile booking,    2# admin booking,    3# web booking, 4# tell call'

It makes sense to add a check to prevent wrong values appearing:

ALTER TABLE app_user_bookings
ADD CHECK (mod IN (1,2,3,4));


In Oracle what is the difference between STDEV and STDEV_SAMP functions?

In fact you have STDDEV, STDDEV_SAMP and STDDEV_POP.

Sample and population are standard definitions for STDDEV. When calculating a standard deviation one needs to know if the data set is a sample or the entire population.

There is plenty of explanation on the web about this. See for example Standard Deviation which states:

  The standard deviation is a measure of the spread of scores within a
  set of data. Usually, we are interested in the standard deviation of a
  population. However, as we are often presented with data from a sample
  only, we can estimate the population standard deviation from a sample
  standard deviation. These two standard deviations - sample and
  population standard deviations - are calculated differently. In
  statistics, we are usually presented with having to calculate sample
  standard deviations, and so this is what this article will focus on,
  although the formula for a population standard deviation will also be
  shown.

If you look at how a sample standard deviation is calculated versus a population standard deviation you can see 1 is subtracted from the population

Sample STDDEV:

Population STDDEV:

Since one is subtracted from the population when calculating a standard deviation you end up with a division by zero, and oracle gave you the option to choose whether you want NULL or zero in that case.

I think there are mostly historic reasons for the illogical naming, you see that in for example Excel's stddev functions too. The stddev is the sample one, and then later on a stddev.p and stddev.s was added. I guess Oracle took the opportunity to make both aliases behave a bit differently


You could try something like this.

declare @command varchar(8000)

set @command = 'select udf_0 as ' + (select udf_0
                                     from tbl_project_desc
                                     where project_name = @project) +
               ' from table where column = value'

EXECUTE(@command)


Experimentally, your code worked for me on both 10.50.1600 (2008 R2 RTM) Developer and 10.0.4000 (2008 SP2) Express. However, it failed against 9.0.5057 (2005 SP4 + hotfix). So, I dug into the documentation.

The 2005 version says this about expressions in the VALUES clause (emphasis mine):

  expression

  Is a constant, a variable, or an expression. The expression cannot contain a SELECT or EXECUTE statement.

while the 2008 and higher version looks like this:

  expression

  Is a constant, a variable, or an expression. The expression cannot contain an EXECUTE statement.

From this evidence, I can only assume that the server version where you're executing these queries is not what you think it is.


This is just a guess on my behalf, it is not clear to me how you define highest row and next operation:

select ProdId, max(OprNum)
from (
    select ProdId, max(OprNum) as OprNum
    from ProdRoute pr1
    where not exists (
        select 1
        from ProdRoute pr2
        where pr1.ProdId = pr2.ProdId
          and pr1.OprNum = pr2.OprNum
          and SetupTime + ProcessTime > 0
    )
    group by ProdId
    union all
    select ProdId, min(OprNum) as OprNum
    from ProdRoute pr1
    where exists (
        select 1
        from ProdRoute pr2
        where pr1.ProdId = pr2.ProdId
          and pr1.OprNum = pr2.OprNum
          and SetupTime + ProcessTime > 0
    )
    and not exists (
        select 1
        from ProdRouteTran prt
        where pr1.ProdId = prt.ProdId
          and pr1.OprNum <= prt.MaxOpCompleted
    )
    group by ProdId
) as t
group by ProdId;


MySQL: To use MYISAM or INNODB engine? (plot twist enclosed)

Here is something to really think about

MyISAM only caches indexes, so there is always disk I/O. InnoDB caches data and indexes. If you could guarantee that every query you call against this one table can only be fetch necessary columns and all columns can be fetched from the index only (called a covering index)

Here are some nice links on Covering Indexes:

http://peter-zaitsev.livejournal.com/6949.html
http://www.mysqlperformanceblog.com/2006/11/23/covering-index-and-prefix-indexes/
http://ronaldbradford.com/blog/tag/covering-index/

MyISAM can do shared reads. So can InnoDB.

Please remember that MyISAM inserts, updates, and deletes perform full tables locks each time (that can be the critical path for disk I/O in making InnoDB wait its turn in certain circumstances). If the table is ready-heavy, you may want to use a dedicated MyISAM key cache.

Heavy INSERTs from LOAD DATA INFILE will lock the MyISAM table. InnoDB can tolerate it better but check the behavior of LOAD DATA INFILE to see if it locks the table regardless of Storage Engine.

The tradeoff depends upon how often you write to the MyISAM table.

PLEASE DO NOT JOIN THE MyISAM TABLE TO ANY INNODB TABLE !!! The locking is clumsy and MyISAM changes cannot rollback.


Azure SQL Warehouse - Data Ingestion - Convert a huge fixed width (with commas) file to delimited

Polybase is powerful but not really that sophisticated so will not be able to deal with this weird format.  You have three options as I see it:

Correct the file format at source.  Instead of having weird mix of fixed-width and delimited file format, use a standard file format, such as .csv.  This format you have where the column delimiter is either carriage return or comma I think is genuinely strange.  Are there any tools that can read this easily?  Is this a common format where you work?
Import the file specifying one of the delimiters into one row, then shred it based on the other delimiter.  I started trying this with your sample data but did not get very far.  Do different rows really have different numbers of columns?  In your sample data, row 1 has 24 columns and row 2 has 17 columns.    Please provide a small sample file eg via gist that accurately represents your data.
Write a highly customised import routine.  The point of using standard data interchange formats like csv, tab-delimted, pipe-delimited, XML, JSON etc is that you should not have to write a highly custom routine every time you want to import some data.  However, this could be an option, if you cannot change your file at source, or import it in stages.  I've been working with Azure Data Lake Analytics (ADLA) and U-SQL recently, and that might be able to do this.

Please try and answer my questions above and provide a sample file and I will try and help.


Update Query DeadLock in SQL Server when ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT are enabled

Writers block other writers of the same row regardless of isolation level. To avoid blocking during data modifications, examine execution plans to ensure only needed rows are touched by the query and perform query and index tuning as needed.

The READ_COMMITTED_SNAPSHOT database option avoids readers from blocking writers in the default READ COMMITTED isolation level by using row versioning instead of locking for statement level read consistency but at the cost of additional overhead for the row version store and in-row version data. You do not need to set ALLOW_SNAPSHOT_ISOLATION to use READ_COMMITTED_SNAPSHOT.  ALLOW_SNAPSHOT_ISOLATION allows use of the separate SNAPSHOT isolation level, which provides transaction level (not just statement level) consistency using row versioning.


want to download 3GB database table from my development mysql server

The simple answer is "use the command line tools", these will run until complete assuming you have enough disk space, to produce the backup then draw that down to where you want it (if not the same server) via HTPS, SFTP, or similar.

The other option of course, unless you need up-to-the-minute data, is to take a copy of the latest backup. If you don't have regular backups already then look into implementing them otherwise you are setting yourself up for a potential world of hurt later.

For more detailed/specific answer you'll need to update your question to describe you environment and what you have tried so far in more detail. Is this your own server or VM, a work machine you have limited access to, a shared host, ...? Linux/Windows/other? What actually happened on your attempts other than "didn't work"? A long delay then nothing, a long delay then an error message, an error message quickly, a fire is produced but is empty or incomplete, ...? If an error message: what was the text of that message?


If you happen to be using the same platform (cpu/OS) it is fairly simple to get this up and running, if you know from which version of Oracle this backup is AND the backup is taken in the correct way. In your case, since all you have is ctl and dbf files, it should have been a COLD backup.

If all of the above is not in place: forget it.

If all of the above is in place:

create a init.ora file containing the dbname, db_unique_name, and control_files parameter. The latter one points to at least one of your ctl files. With a little luck you can guess the dbname from the file names.
install the correct oracle rdbms version in a location called ORACLE_HOME
export ORACLE_HOME=/where/your/Oracle/product/version/home_1 is
export PATH=$ORACLE_HOME/bin:$PATH
sqlplus / as sysdba
startup nomount pfile=your_full_name_of_init.ora from step 1
alter database mount - this accessed the ctl files and checks dbname. Fix dbname in init.ora if reported wrong and shutdown abort the instance to start over at step 5.
now you are able to look into the original files locations. If your files are in exactly the same locations as the original database thi sis easy: alter database open read only;

If any error: hire a dba to help you. If step-8 gives problems because of incorrect file locations: you can rename the files one by one, or create a text dump of the controlfile, edit that dump for the correct locations and use it to create a new controlfile.

Oracle is very flexible and many situations can be handled.
In your case, your database is 9.2.0.6 on Windows. You could try to request a download from oracle support and perform an upgrade. This is the easiest is your dbf locations are the same as on the original system (they are in the controlfile)


Inconsistent statistics on jsonb column with btree index

Currently (version 9.6), Postgres does not have any statistics about the internals of document types like json, jsonb, xml or hstore. (There has been discussion whether and how to change that.) Instead, the Postgres query planner uses constant default frequency estimates (like you observed).

However, there are separate statistics for functional indexes like your idx_test_btree. The manual has this tip for you:

  Tip: Although per-column tweaking of ANALYZE frequency might not be
  very productive, you might find it worthwhile to do per-column
  adjustment of the level of detail of the statistics collected by
  ANALYZE. Columns that are heavily used in WHERE clauses and have
  highly irregular data distributions might require a finer-grain data
  histogram than other columns. See ALTER TABLE SET STATISTICS, or
  change the database-wide default using the default_statistics_target
  configuration parameter.

  Also, by default there is limited information available about the
  selectivity of functions. However, if you create an expression index
  that uses a function call, useful statistics will be gathered about
  the function, which can greatly improve query plans that use the
  expression index.

The volume of statistics gathered depends on general setting of default_statistics_target, which can be overruled with a per-column setting. The setting for the column automatically covers depending indexes.

The default setting of 100 is conservative. For your test with 1M rows, if data distribution is uneven, it may help to increase it substantially. Checking on this once more I found you can actually tweak the statistics target per index column with ALTER INDEX, which is currently not documented. See related discussion on pgsql-docs.

ALTER TABLE idx_test_btree ALTER int4 SET STATISTICS 2000;  -- max 10000, default 100

Default names for index columns are not exactly intuitive, but you can look it up with:

SELECT attname FROM pg_attribute WHERE attrelid = 'idx_test_btree'::regclass

Should result in the type name int4 as index column name for your case.

The best setting for STATISTICS depends on several factors: data distribution, data type, update frequency, characteristics of typical queries, ...

Internally, this sets the value of pg_attribute.attstattarget, and the exact meaning of this is (per documentation):

  For scalar data types, attstattarget is both the target number of
  "most common values" to collect, and the target number of histogram
  bins to create.

Then run ANALYZE if you don't want to wait for autovacuum to kick in:

ANALYZE test_data;

You must ANALYZE the table, since you cannot ANALYZE indexes directly. Check with (before and after if you want to verify the effect):

SELECT * FROM pg_statistic WHERE starelid = 'idx_test_btree'::regclass;

Try your query again ...

Related:

Check statistics targets in PostgreSQL
Index that is not used, yet influences query
PostgreSQL partial index unused when created on a table with existing data


I explain how to do this here: http://blogs.lobsterpot.com.au/2014/07/08/ssis-lookup-transformation-in-t-sql/

The basic concept is to use OUTER APPLY with TOP (1). In your case you may need to start with your list of people that you're looking up. Like this:

SELECT p.Person, ISNULL(c1.color, c2.color) AS color
FROM (VALUES ('Nina')) AS p(Person)
OUTER APPLY
    (SELECT TOP (1) f.color
    FROM fav_colors AS f
    WHERE f.name = p.Person
    AND f.eff_date <= @date
    ORDER BY f.eff_date DESC) c1
OUTER APPLY
   (SELECT TOP (1) f.color
    FROM fav_colors AS f
    WHERE f.name = p.Person
    AND c1.color IS NULL
    ORDER BY f.eff_date ASC) c2

The post details how the Query Optimizer will only do the second lookup if the first one fails. It's really very efficient with the right index in place (on (name, eff_date) include (color)).

Oh - if color can be NULL legitimately, then test a different column in the c2 sub-query.


In 2-node cluster using failover with Dynamic Quorum, is Witness needed?

Dynamic quorum basically dynamically adjusts votes depending on available servers.

Each time when one of nodes goes down, dynamic quorum will remove the vote from that node.
In your scenario you have 2 nodes only and dynamic quorum will automatically remove the vote from your passive node, so the 1st node will have the majority of votes. In planned maintenance scenario when you are shutting down the first node quorum will transfer the vote from first to the second, and remove it from the first node.

However in scenario where first node just crashes quorum does not have time to transfer the vote and your second node wont get to vote, which basically will just shut down your cluster.

Therefore in scenario with 2 nodes only, it is recommended to have a witness.


High Disk I/O after batch deleting from innodb table

First and foremost if your data had some sort of identification (ie PRIMARY KEY, or TIMESTAMP) then you could have done:

DELETE FROM table_name WHERE PRIMARY_KEY_column < PRIMARY_KEY_value;
-- or
DELETE FROM table_name WHERE TIMESTAMP_column < TIMESTAMP_value;

As for what to do now. I would run the following processes:

In a WHILE TRUE make sure that the mysql isn't hanging

mysql -h $HOST -P $port -uroot -e "show processlist;"

Check the row count to see if you are increasing or decreasing

SELECT COUNT(*) FROM table_name;

If the row count is increasing then you should check what other processes are being utilized.
ps fux | grep mysql

However, if the row count is decreasing or consistent then it may mean that your deletes are not fully finished.

Wish I can be more help, but your question felt incomplete and thus not very clear.


Azure - How can I check if my first full backup is available

You can use Get-AzureRmSqlDatabaseGeoBackup Powershell cmdlet to get all available backups on a specified server.

Get-AzureRMSqlDatabaseGeoBackup -ResourceGroupName "ContosoResourceGroup" -ServerName "ContosoServer"

Hope this helps.


Full text indexing not working + Mysql 5.6.4

You need check - configuration of server, memory for InnoDB, for FullText

compare size of index and FT buffer size, by default it only 8Mb, max value is 80Mb

Than next step and profile query - how many records returned, what time of intialyze of index

and upgrade 5.6.4 to something more new
in 5.6.4 FullText was only implement for InnoDB, many  bugs fixed from that time


How to dump an advanced revelation database?

Advanced Revelation is a multi-valued database based on the original PICK operating system.  You can find information on PICK and it's spin-offs here:

https://en.wikipedia.org/wiki/Pick_operating_system

I've been developing on PICK and like systems for over 35 years, including Advanced Revelation.  Manuals are a little difficult to find online. You may also have some luck finding more information here:

http://www.jes.com


Database corruption: QueryStore internal table

As noted in my comment above I had a similar corruption issue with a query store internal table.

As you yourself have suggested I used ALTER DATABASE MyDatabase SET QUERY_STORE CLEAR; to attempt to fix the issue and that did work fine.  In SQL Server 2017, Microsoft added a repair procedure that can be attempted prior to clearing the data: sp_query_store_consistency_check (source)

If you want to preserve the data then probably the only method is to copy the tables - I can't find anyone who has created a script for that.

Usually with corruption I too would be worried about my disks, but in this case I'm a little suspicious that the issue is with query store itself.


Dealing with empty strings while loading a table from a CSV

According to the documentation, you can use SET statements to transform the data on the way in.

 [SET col_name = expr,...]

The expr expression can include the column name, which will be interpreted as the data being read from the file and destined for that column... so, for example, at the end of your LOAD DATA INFILE statement you might use:

SET latitude = IF(latitude + 0 = 0,NULL,latitude),
    area_code = IF(area_code = '',NULL,area_code)

This example transforms 2 columns.  If latitude + 0 is 0, latitude gets set to NULL, and otherwise it gets set to the value from the file as the data is inserted; if area_code contains an empty string, it gets set to NULL, otherwise to the data from the file.  The more appropriate choice will depend on how MySQL handles casting the data, but I suspect either of these constructs would work in your situation.

You do not have to reference columns you don't intend to transform.  They'll be inserted as-is.


MySQL Trigger: Can you turn a query updating multiple columns to multiple queries where each update one column?

I suggest that using a VIEW is the wrong starting point, and that TRIGGERs are just adding complication.

Instead of using Views and Triggers, have a "database layer" that segregates the Application code from the Database.

The layer could be written as subroutines in your programming language or as Stored Routines in MySQL.

The application would ask the layer to perform, say, an API like Set_Foobar('a', 'b', '100') and the function would do whatever it takes.  Under the hood might be one UPDATE today, maybe 2 or 3 tomorrow.

With this technique, you have the freedom to rearrange the schema without touching the application code.  But you will have to change the layer.  And that will be easier than trying out how to implement the Trigger you are asking about.


I'll try to address your concerns one at a time.

  Cassandra doesn't store the rows in any ordered way, so simply adding LIMIT X to the CQL query returns random-ish rows.
  The ORDER BY command in CQL only works for columns that are (part of) the key.

Not entirely correct, on either point.  The on-disk sort order in Cassandra is determined by the first clustering column of your primary key.  As a result, ORDER BY will also only work on that column (and only that column).

  I'm trying to fetch the top X rows with the highest counter value.

Cassandra will not allow you to put a secondary index on a counter column, nor can you make it a part of your key (which also means you cannot sort by it).  Therefore, querying data by the value of a counter column is not possible.

It would appear that your use case is not an appropriate fit for Cassandra.  I would suggest solving this issue with a relational database or something else that would provide you with the necessary aggregation tools.


Remove all Null values from Sql table at once

I'm not sure I completely understand what you are trying to do. ISNULL(COLUMNNAME,0) is a function that returns 0 if COLUMNNAME contains a null (it does not change anything in the table, you will have to do an update to modify the table).

The error message you get is likely because COLUMNNAME is a different type than a number. If COLUMNNAME is of type string you can use:

ISNULL(COLUMNNAME, '')

if it's of type date you need a date:

ISNULL(COLUMNNAME, DATE('1900-01-01') ) -- I just invented one here, use one that makes sense in your context

and so forth. As you noted, applying a function to a column does an implicit rename of the resulting column. Most DBMS generates a rather cryptic name so it makes sense to do an explicit rename as in:

ISNULL(COLUMNNAME, '') AS COLUMNNAME

if you want to preserve the name.


How to extract the last inserted row in SQL Server?

By definition, a table is an unordered bag of rows (see #3 here). There is no way to ask SQL Server which row was inserted last unless you are doing so in the same batch as the insert. For example, if your table has an IDENTITY column, you can use SCOPE_IDENTITY() (never use @@IDENTITY, since that can be unreliable if you have or will ever add triggers to the source table):

INSERT dbo.table(column) SELECT 1;
SELECT SCOPE_IDENTITY();

More generally, you can use the OUTPUT clause, which doesn't rely on an IDENTITY column (but will still make it difficult to identify which row(s) the clause identifies if there is no PK):

INSERT dbo.table(column) OUTPUT inserted.* SELECT 1;

If you're not talking about the same batch, then the only real way to identify the last row inserted is to use a date/time column where the timestamp of insertion is recorded. Otherwise it is like you emptied a bag of marbles on the floor, then asked someone to enter the room and identify which one hit the floor last.

You may be tempted or even advised to use the IDENT_CURRENT() function, but I explain here why this is unreliable too.

You could add a column to track this going forward:

ALTER TABLE dbo.table ADD DateInserted DEFAULT CURRENT_TIMESTAMP;

Now you can find the last row(s) inserted by simply:

;WITH x AS (SELECT *, r = RANK() OVER (ORDER BY DateInserted DESC)
   FROM dbo.table)
SELECT * FROM x WHERE r = 1;

(If you don't want ties, you can add a tie-breaking column to the ORDER BY, or you can simply change RANK() to ROW_NUMBER() if you don't care which of the tied rows you get.)

You might make the assumption that the last row inserted is the highest identity value, but this isn't necessarily the case. The identity can be reseeded and it can also be overridden using SET IDENTITY_INSERT ON;.


Casting an array of texts to an array of UUIDs

Like has been commented, the column project_ids should be uuid[], which would preclude the problem. It would also be more efficient.
To change (with no illegal data in column like you asserted):
ALTER TABLE users ALTER COLUMN project_ids DROP DEFAULT;

ALTER TABLE users
ALTER COLUMN project_ids SET DATA TYPE uuid[] USING project_ids::uuid[];

You had uuid instead of uuid[] by mistake.
And this:

ERROR: default for column "product_ids" cannot be cast automatically to type uuid[]

.. means you have a default value set for the column. That expression cannot be transformed automatically. Remove it before altering the type. You can add a new DEFAULT later.
Fix to original problem
The efficient fix in your original situation is to remove empty strings from the array with array_remove() before the cast (requires Postgres 9.3+):
SELECT *
FROM   users    u
JOIN   projects p ON p.id = ANY(array_remove(u.project_ids, '')::uuid[]);

... after investigating why there can be empty stings in that text[] column.
Related:

Delete array element by index
Would index lookup be noticeably faster with char vs varchar when all values are 36 chars

Fine points

The [INNER] JOIN in your query removes users without valid projects in projects_ids from the result. Typically, you'd want to keep those, too: use LEFT [OUTER] JOIN instead (with users first).

The JOIN folds duplicate entries either way, which may or may not be as desired. If you want to represent duplicate entries, unnest before the join instead.

And if your aim is simply to resolve the array of IDs to an array of project names, you'll also want to preserve original order of array elements:
SELECT *
FROM   users u
LEFT   JOIN LATERAL (
   SELECT ARRAY(
      SELECT project_name  -- use the actual column(s) of your case
      FROM   unnest (array_remove(u.project_ids, '')::uuid[]) WITH ORDINALITY AS p(id, ord)
      JOIN   projects USING (id)
      ORDER  BY ord
      )
   ) p(projects) ON true;

db<>fiddle here (loosely based on McNets' fiddle)
Related:

How to preserve the original order of elements in an unnested array?


SSMS Intellisense - Data Dictionary Incorporation - Show Object Descriptions in Popup?

Using native tools there's no way to do this.  You could try checking with RedGate or SSMS Tool Pack and see if either team would be able to assist you.


Beautify/reformat the SQL script button partially working

Bug:  NOW() is to the second.  You probably want CURDATE(), which is midnight this morning.  And would work nicely if first_invoice_due_date were declared DATE.

Avoid Cursors!  They are a speed bump.  Instead, learn how to write SQL statements that do everything all at once.

(As for beautifing, your code is prettier than most of the stuff on this forum!)


error query did explode the postgres database

Limiting is hard, but you can set a timeout on query for a user :

ALTER ROLE <user> SET statement_timeout TO '60s';

As of the disk I'm not aware of any technique to limit disk usage of a query in postgresql.


This can be due to the maximum amount of characters that the result to grid can return, 65535.

Starting from SSMS 18.2 you are able to change this.

Thanks Aaron Bertrand for pointing that out.

Aaron also mentions:

  if 64kb is the limit you’re hitting, there are a lot of places where
  statement text is truncated long before that any way (see literally
  any execution plan).

The text datatype of sys.dm_exec_sql_text is nvarchar(max), no issues there.

You could cast the column to XML as a workaround

SELECT CAST(t.[text] AS XML)
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t

Or a better method by Evgeniy Gribkov

SELECT t.[text]
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
FOR XML RAW, ELEMENTS;

  Better this way: SELECT t.[text] FROM sys.dm_exec_requests AS r CROSS
  APPLY sys.dm_exec_sql_text(r.sql_handle) AS t FOR XML RAW, ELEMENTS;
  Since the request text can not always be converted to XML. In
  particular, it cannot convert query text when remotely calling stored
  procedures: "XML parsing: line 13, character 129, illegal qualified
  name character"

Or save the results to a file

Thanks Erik Darling for linking some more related answers


Your query

SELECT b.*
FROM
a,b
LEFT JOIN c ON hour(c.datetime) = a.hour;

means (parentheses added)

SELECT b.*
FROM
a,
(b LEFT JOIN c ON hour(c.datetime) = a.hour);

and that means a is not visible in the join. You may need to shuffle it around a bit

SELECT b.*
FROM
(a LEFT JOIN c ON hour(c.datetime) = a.hour), b;

http://sqlfiddle.com/#!9/4114a/2

That said, such cross-join seems weird, so make sure you actually need it that way.

Modified after the comment http://sqlfiddle.com/#!9/4114a/3

SELECT b.*
FROM
(a,b)
LEFT JOIN c ON hour(c.datetime) = a.hour;


Microsoft SQL Server -> Postgres Data Migration

There's no need for a commercial product. You could make use of for example Pentaho Data Integration to do the task.

Back to the main question, here are the steps you could do to feed your data without recreating the tables every time:

Create a capture data change mechanism (it works as a table), to log changes on tables (which table has been inserted with which record (id) etc)
Update your destination data with changes logged in CDC mechanism.

This kind of process is broadly implemented in Data Warehousing to migrate changes.

If this seems a bit like an overkill for you, then a slower solution might come in handy. That'd be comparing the snapshot in your destination with current data in source and based on differences take proper actions (insert, update, delete rows from destination). This method would require you to scan whole tables each time you need to synchronize.


Lock Escalation and Count Discrepancy in lock_acquired Extended Event

The XE is reporting a page lock being acquired each time a row is updated (one event for each of the 8066 rows affected by the update).  However, these rows are only stored on 6159 unique pages, which explains the discrepancy.
I don't have StackOverflow2013 on this machine, but get a similar experience with SO2010:

1368 rows updated (and that many XEvents fired)
958 page locks

You can see the same pages being locked repeatedly in the XE output if you sort by resource_0:

Using DBCC PAGE:
DBCC TRACEON (3604); -- needed for the next one to work
GO

DBCC PAGE (StackOverflow2010, 1, 180020, 3);
GO

I can see that page 180020 has 163 records on it (m_slotCnt = 163):
Page @0x000002C278C62000

m_pageId = (1:180020)               m_headerVersion = 1                 m_type = 1
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 174   m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594049331200
Metadata: PartitionId = 72057594044350464                                Metadata: IndexId = 1
Metadata: ObjectId = 1525580473     m_prevPage = (1:180019)             m_nextPage = (1:180021)
pminlen = 24                        m_slotCnt = 163                     m_freeCnt = 31
m_freeData = 7835                   m_reservedCnt = 0                   m_lsn = (203:19986:617)
m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
m_tornBits = 1894114769             DB Frag ID = 1

And that 3 of those match the update criteria (I pasted the output into notepad++ and searched for "Reputation = 56"):

Here's the first match, as an example:
Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 39
Memory Dump @0x000000EDB2B7821D

0000000000000000:   30001800 86540000 38000000 00000000 6041c2e4  0...T..8.......`AÂä
0000000000000014:   c3020000 04000801 00270045 00720069 006300    Ã........'.E.r.i.c.

Slot 9 Column 1 Offset 0x4 Length 4 Length (physical) 4

Id = 21638

Slot 9 Column 2 Offset 0x1f Length 8 Length (physical) 8

DisplayName = Eric

Slot 9 Column 3 Offset 0x8 Length 8 Length (physical) 8

Reputation = 56

Slot 9 Column 4 Offset 0x0 Length 0 Length (physical) 0

Score = [NULL]

Slot 9 Offset 0x0 Length 0 Length (physical) 0

KeyHashValue = (e1caffa60313)
Slot 10 Offset 0x244 Length 43

I believe this behavior is due to the pipelined nature of execution plans, and the way this specific XE is implemented.


Don't specify two conflicting FORMATs (you have one in the ALLOCATE CHANNEL command and one in the BACKUP command).

If and when you need different FORMATs (e.g. one for database datafile backups, one for archivelog backups and one for controlfile backups) specify them explicitly with the BACKUP command.

Eg:

> rman target mydatabase

    shutdown;
    startup mount;
RUN {
      ALLOCATE CHANNEL ch1 TYPE
        DISK FORMAT 'C:\Backup_Folder\mydatabase%d_DB_%u_%s_%p';
      BACKUP DATABASE;
      RELEASE CHANNEL ch1;
    }
    EXIT;

Note: I noticed that you provided the path starting with "C:/". Shouldn't it start with "C:\"?


Using indexed views for aggregates - too good to be true?

As you've noted, the view itself only materializes a small number of rows - so even if you update the whole table, the additional I/O involved with updating the view is negligible. You probably already felt the biggest pain you're going to feel when you created the view. The next closest will be if you add a gazillion rows to the base table with a bunch of new IDs that require new rows in the view.

This isn't too good to be true. You're using indexed views exactly how they were meant to be used - or at least one of the most effective ways: to pay for future query aggregations at write time. This works best when the result is much smaller than the source and of course when the aggregations are requested more often than the underlying data is updated (more common in DW than OLTP, generally).

Unfortunately many people think indexing a view is magic - an index won't make all views more efficient, especially views that simply join tables and/or produce the same number of rows as the source (or even multiply). In these cases the I/O from the view is the same or even worse than the original query, not only because there are the same or more rows, but often they are storing and materializing more columns, too. So materializing those in advance doesn't provide any gains, since - even with SSDs - I/O, network, and client processing/rendering still remain the primary bottlenecks in returning large resultsets to the client. The savings you get in avoiding the join at runtime just aren't measurable in comparison to all the other resources you're still using.

Like non-clustered indexes, just be careful to not over-do it. If you add 10 different indexed views to one table, you're going to see more impact to the write portion of your workload, especially if the grouping column(s) are not (in) the clustering key.

Gosh, I've been meaning to blog about this topic.


Simply delete the rows where item_isbn IS NULL and then import the updated CSV file.

While importing, you can disable auto_increment as well in case you're concerned about messing item_id identity.


SELECT  t1.weight,
        t2.weight,
        t3.weight,
        ...
        t12.weight
    FROM tbl t1
    JOIN tbl t2  ON t2.weight > t1.weight
    JOIN tbl t3  ON t3.weight > t2.weight
    ...
    JOIN tbl t12  ON t12.weight > t11.weight
    WHERE t1.weight +
          t2.weight +
          t3.weight +
          ...
          t12.weight  = 296.35;

Possible issues:

What I provided does not allow for duplicate weights.  If that can happen, I need an id or something instead of weight for avoiding dups.
If you want the closest, not exact, match:

Replace the WHERE clause with something like:

    ORDER BY ABS( t1.weight + ... + t12.weight - 296.35 ) ASC
    LIMIT 1

This would probably run a lot faster in any programming language then SQL.

I worry that this query will not complete in my lifetime.  I think it would take billions, maybe trillions of iterations.

OR

The code above is "brute force".  With some more thinking, it should be possible to 'prune' the list to cut back significantly (down from trillions to billions??) the amount of work.  But that would need a recursive CTE (upgrade to MySQL 8.0) or, as I say, use a real programming language.


How to find current roles available on a database

You can find this information in the DBA_* views in Oracle.

To find all roles in the database:

select * from dba_roles

To find what roles a particular user has:

select * from dba_role_privs where grantee=<user name>


Error "ORA-12726:unmatched bracket in regular expression" while trying to separate a part of a string from other part

The error is because [ and ] are part of regex syntax, which makes handling them in a regex tricky as you have to escape them, e.g. 'sum:\[\]+,'
I found it easier to replace them on the fly with { and } before applying regexp_substr to the result:
with demo (str) as
      ( select 'sum:{sum(x)>1 and sum(y)>2 and sum(z)>3},where:{n=1 and m=2}'
        from dual )
select regexp_substr(translate(str,'[]','{}'),
         '(sum:{)([^{}]+)', 1, 1, 'i', 2) as sum_expr
     , regexp_substr(translate(str,'[]','{}'),
         '(where:{)([^{}]+)', 1, 1, '', 2) as where_expr
from   demo;

SUM_EXPR                            WHERE_EXPR
----------------------------------- -----------
sum(x)>1 and sum(y)>2 and sum(z)>3  n=1 and m=2


Why can the SA account no longer access the master database?

We know you are getting 18456 errors. The message (other than the 'Login failed' part) and the error state are much more important for diagnosing. In the SQL Server error log, there will be more information than you get from the application or the dialog in SSMS. In SSMS open Object Explorer for the server in question, expand Management, expand SQL Server Logs, right-click "Current - ..." and choose View SQL Server Log. You should be able to find events like this:

This number and the extended reason are not reflected elsewhere because it's meant to obscure the actual failure from the end user, in case that user is malicious. (For example, the state for wrong password might help them see that they are on the right track.)

You can see a list of all the states I know here, which should help you resolve the issue:

https://sqlblog.org/2011/01/14/troubleshooting-error-18456

For state 16 it doesn't make sense that the database is offline or inaccessible, unless someone has somehow demoted sa from the sysadmin role and/or explicitly denied access to master, since master can't be offline. I suspect it is much more likely that their default database has changed, or that the database they tried to connect to explicitly is not online. What happens if you run:

ALTER LOGIN sa WITH DEFAULT_DATABASE = master;

Also, why is your application using the sa login? Have you considered creating a separate, less-privileged account to dedicate to your application?


Logical condition when declaring handler procedure

According with the Mysql documentation https://dev.mysql.com/doc/refman/5.6/en/handler-scope.html you can create a handler with two handlers and the same scope, be aware of the precedence

Example

CREATE PROCEDURE p1()
BEGIN
  DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'
    SELECT 'SQLSTATE handler was activated' AS msg;
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    SELECT 'SQLEXCEPTION handler was activated' AS msg;

  DROP TABLE test.t;
END;


How to Restrict INSERT operations on table for DB2?

User in DB2 can have privileges granted via user's groups and roles as well.
DB2 stores permissions on objects, not restrictions. You can't revoke a privilege on an object from a user, if this user hasn't been granted this permission explicitly (you get SQL0556N on such a revoke statement). The reason you see INSERTAUTH = 'N' in SYSCAT.TABAUTH (SYSIBM.SYSTABAUTH) is that this user has some other *AUTH permission(s) on this table, but not INSERT.

So, you can't revoke a privilege from a user explicitly, if this user has this privilege via its groups or roles (we don't consider LBAC here).

2 checks should be done to understand why some particular user has some privilege on some object.
Change the constants 'MYUSER', 'MYSCHEMA', 'MYTABLE' below accordingly.

1) If the user has DATAACCESS database authority:

SELECT P.GRANTEE, P.GRANTEETYPE
FROM SYSCAT.DBAUTH P
CROSS JOIN TABLE(VALUES 'MYUSER') A (AUTHID)
JOIN TABLE (
SELECT GROUP, 'G' FROM table(AUTH_LIST_GROUPS_FOR_AUTHID(A.AUTHID))
  UNION ALL
select ROLENAME, 'R' from table(AUTH_LIST_ROLES_FOR_AUTHID(A.AUTHID, 'U'))
  UNION ALL
SELECT * FROM TABLE(VALUES ('PUBLIC', 'G'), (A.AUTHID, 'U')) T (AUTHID, AUTHIDTYPE)
) U (AUTHID, AUTHIDTYPE) ON U.AUTHID=P.GRANTEE AND U.AUTHIDTYPE=P.GRANTEETYPE
WHERE P.DATAACCESSAUTH='Y';

2) If the user has the privilege on the table:

SELECT
  P.PRIVILEGE, P.OBJECTTYPE, P.OBJECTSCHEMA, P.OBJECTNAME
, U.AUTHID, U.AUTHIDTYPE
FROM SYSIBMADM.PRIVILEGES P
CROSS JOIN TABLE(VALUES 'MYUSER') A (AUTHID)
JOIN TABLE (
SELECT GROUP, 'G' FROM table(AUTH_LIST_GROUPS_FOR_AUTHID(A.AUTHID))
  UNION ALL
select ROLENAME, 'R' from table(AUTH_LIST_ROLES_FOR_AUTHID(A.AUTHID, 'U'))
  UNION ALL
SELECT * FROM TABLE(VALUES ('PUBLIC', 'G'), (A.AUTHID, 'U')) T (AUTHID, AUTHIDTYPE)
) U (AUTHID, AUTHIDTYPE) ON U.AUTHID=P.AUTHID AND U.AUTHIDTYPE=P.AUTHIDTYPE
WHERE P.OBJECTSCHEMA = 'MYSCHEMA' AND P.OBJECTNAME='MYTABLE' AND P.OBJECTTYPE='TABLE'
AND P.PRIVILEGE IN ('INSERT', 'CONTROL')
ORDER BY 2, 3, 4;

@Redhat: Show the results of the queries above for MYSCHEMA=HVD121, MYTABLE=OAUTH20_TOKEN_CACHE, MYUSER=HVD121.


Set the Priority for two different "after insert" triggers in SQL Server

You can specify the first and/or last trigger to fire using sys.sp_settriggerorder. If you have two or three triggers, this allows you to completely control the execution order of all triggers. If you have more than three, you can make one first, another fire last, but the order of execution for the triggers in the middle will be non-deterministic.

In your case, to make sure A always fires first:

EXEC sys.sp_settriggerorder N'dbo.A', 'First', 'INSERT';

To make sure B always fires last (while not necessary when there are only two triggers, you could add more later, just note that if you later attempt to specify another trigger as last, it will generate an error):

EXEC sys.sp_settriggerorder N'dbo.B', 'Last', 'INSERT';

If you're not asking how to control the order but rather to determine after the fact which one fired first for a specific insert, then you will have to add logging to both triggers. Once you are logging both triggers' activity to a table, you can determine order by an identity column on the logging table, or a date/time column populated with the current time.

Of course, you are probably better off combining the two triggers into one (and the part that is insert only would have conditional logic around it). I did not test the overhead of additional insert logic in an insert/update/delete trigger getting bypassed when invoked by a delete or update operation, but in general, my tests concluded that a single trigger has less overhead than multiple even when the end result affected the same number of rows:

T-SQL Tuesday #64 : One Trigger or Many?


Using a join to return multiple values of the same column?

You have to join the user table to the rank table twice

SELECT u.id,u.Name,r1.rank_name Rank,r2.rank_name Supervisor
FROM user u
INNER JOIN rank r1 ON u.rank_id = r1.id
INNER JOIN rank r2 ON u.supervisor_id = r2.id
WHERE u.id = 4;

or

SELECT u.id,u.Name,r1.rank_name Rank,r2.rank_name Supervisor
FROM user u
INNER JOIN rank r1 ON u.rank_id = r1.id
INNER JOIN rank r2 ON u.supervisor_id = r2.id
WHERE u.Name = 'aby';

Give it a Try !!!


Some update: when I start it with innodb_force_recovery=2, I can start the db and dump my data which is one (big) step forward.
But when I set to innodb_force_recovery=0, it fails with the message below. Is InnoDB not healing itelf when you start with a force recovery?
Thanks - C
InnoDB: End of page dump
InnoDB: Page may be an update undo log page
2021-03-24T10:38:09.631071Z 1 [ERROR] [MY-011899] [InnoDB] [FATAL] Unable to read page [page id: space=4294967279, page number=176] into the buffer pool after 100 attempts. The most probable cause of this error may be that the table has been corrupted. Or, the table was compressed with with an algorithm that is not supported by this instance. If it is not a decompress failure, you can try to fix this problem by using innodb_force_recovery. Please see http://dev.mysql.com/doc/refman/8.0/en/ for more details. Aborting...
2021-03-24T10:38:09.631894Z 1 [ERROR] [MY-013183] [InnoDB] Assertion failure: ut0ut.cc:532 thread 139943235643136
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
10:38:09 UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x5e68130
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7f4712d95bc0 thread_stack 0x46000
/usr/sbin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x3d) [0x1f70f0d]
/usr/sbin/mysqld(handle_fatal_signal+0x333) [0xfc3603]
/lib64/libpthread.so.0(+0xf5f0) [0x7f472b48d5f0]
/lib64/libc.so.6(gsignal+0x37) [0x7f4729544337]
/lib64/libc.so.6(abort+0x148) [0x7f4729545a28]
/usr/sbin/mysqld() [0xd2e0aa]
/usr/sbin/mysqld(ib::fatal::~fatal()+0xa4) [0x221d614]
/usr/sbin/mysqld(Buf_fetch<Buf_fetch_normal>::read_page()+0x192) [0x227c182]
/usr/sbin/mysqld(Buf_fetch_normal::get(buf_block_t*&)+0x18) [0x227c1d8]
/usr/sbin/mysqld(Buf_fetch<Buf_fetch_normal>::single_page()+0x42) [0x2281882]
/usr/sbin/mysqld(buf_page_get_gen(page_id_t const&, page_size_t const&, unsigned long, buf_block_t*, Page_fetch, char const*, unsigned long, mtr_t*, bool)+0x1cd) [0x228244d]
/usr/sbin/mysqld(trx_undo_lists_init(trx_rseg_t*)+0x262) [0x2217c22]
/usr/sbin/mysqld(trx_rseg_mem_create(unsigned long, unsigned int, unsigned int, page_size_t const&, unsigned long, std::priority_queue<TrxUndoRsegs, std::vector<TrxUndoRsegs, ut_allocator<TrxUndoRsegs> >, TrxUndoRsegs>*, mtr_t*)+0x3b3) [0x21ff0d3]
/usr/sbin/mysqld(trx_rsegs_init(std::priority_queue<TrxUndoRsegs, std::vector<TrxUndoRsegs, ut_allocator<TrxUndoRsegs> >, TrxUndoRsegs>*)+0x7cd) [0x2200b2d]
/usr/sbin/mysqld(trx_sys_init_at_db_start()+0x134) [0x2205b74]
/usr/sbin/mysqld(srv_start(bool, std::string const&)+0x2055) [0x21cc055]
/usr/sbin/mysqld() [0x204860e]
/usr/sbin/mysqld(dd::bootstrap::DDSE_dict_init(THD*, dict_init_mode_t, unsigned int)+0x90) [0x1ca5960]
/usr/sbin/mysqld(dd::upgrade_57::do_pre_checks_and_initialize_dd(THD*)+0x19a) [0x1f3b1ca]
/usr/sbin/mysqld() [0x107fe88]
/usr/sbin/mysqld() [0x246d2bc]
/lib64/libpthread.so.0(+0x7e65) [0x7f472b485e65]
/lib64/libc.so.6(clone+0x6d) [0x7f472960c88d]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0): Connection ID (thread ID): 1
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.


Avoiding entitized characters when using FOR XML PATH for string concatenation

Try changing you statement to match this:

FOR XML PATH(''), TYPE).value('(./text())[1]','varchar(max)')

This uses the .value xml method to solve your entitization problem.


overstating field size in database design

If you're talking about varchar and nvarchar then no, there is no penalty for allowing a higher field length.

Some caveats to bear in mind, though:

There is a 2 byte overhead per row for variable length fields (per field).  If you have a very short field it may make more sense to use a CHAR.  Varchar(2) for instance actually uses between 2-4 bytes per row, while CHAR(2) always uses 2.
Very long fields cannot be indexed.  The maximum length for all fields in an index key set is 900 bytes.
If you allow more data than you expect, you will eventually get unexpected results.  If you allow 100 characters for a street name, at some point other data is likely to get into that field without you being aware of it (for instance the entire address).  If you had it appropriately sized, you would likely get an error on insert instead.
Allowing very wide rows could lead to page splits and fragmentation.  If you have a row longer than 8k it will need to be split onto multiple data pages.  A lot of these can really hurt performance.  Narrower in general is more efficient.


Can replicated commands be forced to follow each other in transactional replication?

It sounds like you'd benefit from using procedural replication for those stored procedures.  That way, SQL Server will just run the procedures on the replicated instance rather than replicating all the DELETE and INSERT operations.  I'll wager that will be much faster and will reduce latency in addition to fixing the issue you're seeing where the destination tables are empty for a period of time.


SQL PROFILER Replay statistics event interpretation

I am afraid this replay will not help you at all. It will not give you any indication about performance in your new test server as all the replay events failed.

I recommend you read this when you take your trace at source, especially following sections:

Event Classes Required for Replay

Data Columns Required for Replay

I will use these terms so I do not repeat the same thing:

SQL Server 2012 production environment = Source

SQL 2016 test environment = Destination

Total events = Number of events that was captured from source
Replayed events = Number of events replayed at destination
Provider errors = Look at the textdata column for details on each provider warnings or error
Internal errors = Look at Errors and Warnings column for details
Total errors = Sum(Provider errors + Internal errors)
Provider hit ratio = Ration between Replayed events and Providers errors
Internal hit ratio = Ration between Replayed events and Internal errors
Overall hit ratio = Meaning out of 39 Replayed events all of those timed out either and Provide errors or Internal error. In your case it 13+26=39.
Replay time = Time took to replay trace at destination.

You can load your test result into a table and then you should be easily able find details about errors. As an example for Provider Errors you can use following query which I copied from this blog post.

SELECT RowNumber,
       EventClass,
       TextData,
       ConnectionID,
       SPID,
       EventSubclass,
       RequestProperties,
       RequestParameters,
       BinaryData
  FROM dbo.Trace_Replay_Results
 WHERE EventClass = 63485


SQL Server 2008 R2 - VLDB Backup Strategies

Log shipping to the primary site then doing backups isn't going to work as you can't backup a database which is waiting for t-log backups.

With SQL 2008 your only option would be to backup to local disk, have the storage array (I'm assuming that you have a storage array in both sites that can replicate data between the sites) replicate the backup LUN to the array at DC1.  Then during the day once the backup has been fully copied over to DC1 snapshot the copy of the LUN at DC1, mount it to a server at DC1 and backup the database to tape.

Another option would be to upgrade to SQL Server 2012 and take advantage of AlwaysOn Availability Groups which would let you replicate the data to a server in DC1 then you can take your backups from the server in DC1.

A third option would be to buy more storage at DC2 and keep more than two backups around.

A fourth option would be to move the application to DC1 and run it from there.

A fifth option would be to buy a tape backup unit for DC2.


DBCC CheckDB Consistency error on a empty table

It would be a lot easier if you post what error are you getting?

Usually the corruption was already there in the 2000 version but the latest and improved version of DBCC catches the errors that werent reported by the 2000 version of DBCC. Usually running DBCC UPDATEUSAGE might fix it and if the error doesnt go away you have to check what the specific error is and try to solve it accordingly. For all things CHECKDB i suggest you dig into all paul randal checkdb related blogs.


What is the standard de facto unit test framework for Postgres?

pgTAP

pgTAP is the only I know that is ever even talked about.

  pgTAP is a suite of database functions that make it easy to write TAP-emitting unit tests in psql scripts or xUnit-style test functions.

github
web
pgxn


One approach:

In Management Studio 2008 R2, you can add your servers into groups called Registered Servers. I have groups for prod, dev, Prod-SQL2000 etc. You start this by going View - Registered Servers, and then adding them one by one (you can share with colleagues by exporting them)

This will then allow you to click on one of your groups (eg Prod), and selecting New Query..... Management Studio will then connect to all the SQL Servers in that group, and you can issue a query across all of them in that group. I personally do read only operations, as changing a number of servers at the same time is more risky.


Duplicate row with Primary Key in PostgreSQL

Simple with hstore

If you have the additional module hstore installed (instructions in link below), there is a surprisingly simple way to replace the value(s) of individual field(s) without knowing anything about other columns:

Basic example: duplicate the row with id = 2 but replace 2 with 3:

INSERT INTO people
SELECT (p #= hstore('id', '3')).* FROM people p WHERE id = 2;

Details:

How to set value of composite variable field using dynamic SQL
Assign to NEW by key in a Postgres trigger

Assuming (since it's not defined in the question) that people.id is a serial column with an attached sequence, you'll want the next value from the sequence. We can determine the sequence name with pg_get_serial_sequence(). Details:

PostgreSQL SELECT primary key as "serial" or "bigserial"

Or you can just hard-code the sequence name if it's not going to change.
We would have this query:

INSERT INTO people
SELECT (p #= hstore('id', nextval(pg_get_serial_sequence('people', 'id'))::text)).*
FROM people p WHERE id = 2;

Which works, but suffers from a weakness in the Postgres query planner: The expression is evaluated separately for every single column in the row, wasting sequence numbers and performance. To avoid this, move the expression into a subqery and decompose the row once only:

INSERT INTO people
SELECT (p1).*
FROM  (
   SELECT p #= hstore('id', nextval(pg_get_serial_sequence('people', 'id'))::text) AS p1
   FROM   people p WHERE id = 2
   ) sub;

Probably fastest for a single (or few) row(s) at once.

json / jsonb

If you don't have hstore installed and can't install additional modules, you can do a similar trick with json_populate_record() or jsonb_populate_record(), but that capability is undocumented and may be unreliable.

How to set value of composite variable field using dynamic SQL

Transient temporary table

Another simple solution would be to use a transient temporary like this:

BEGIN;
CREATE TEMP TABLE people_tmp ON COMMIT DROP AS
SELECT * FROM people WHERE id = 2;
UPDATE people_tmp SET id = nextval(pg_get_serial_sequence('people', 'id'));
INSERT INTO people TABLE people_tmp;
COMMIT;

I added ON COMMIT DROP to drop the table automatically at the end of the transaction. Consequently, I also wrapped the operation into a transaction of its own. Neither is strictly necessary.

This offers a wide range of additional options - you can do anything with the row before inserting, but it's going to be a bit slower due to the overhead of creating and dropping a temp table.

This solution works for a single row or for any number of rows at once. Each row gets a new default value from the sequence automatically.

Using the short (SQL standard) notation TABLE people.

Dynamic SQL

For many rows at once, dynamic SQL is going to be fastest. Concatenate the columns from the system table pg_attribute or from the information schema and execute it dynamically in a DO statement or write a function for repeated use:

CREATE OR REPLACE FUNCTION f_row_copy(_tbl regclass, _id int, OUT row_ct int) AS
$func$
BEGIN
   EXECUTE (
      SELECT format('INSERT INTO %1$s(%2$s) SELECT %2$s FROM %1$s WHERE id = $1',
                    _tbl, string_agg(quote_ident(attname), ', '))
      FROM   pg_attribute
      WHERE  attrelid = _tbl
      AND    NOT attisdropped  -- no dropped (dead) columns
      AND    attnum > 0        -- no system columns
      AND    attname <> 'id'   -- exclude id column
      )
   USING _id;

   GET DIAGNOSTICS row_ct = ROW_COUNT;  -- directly assign OUT parameter
END
$func$  LANGUAGE plpgsql;

Call:

SELECT f_row_copy('people', 9);

Works for any table with an integer column named id. You could easily make the column name dynamic, too ...

Maybe not your first choice since you wanted to stay away from stored procedures, but then again, it's not a "stored procedure" anyway ...

Related:

psql: SELECT * ... except one column
How do I list all columns for a specified table

Advanced solution

A serial column is a special case. If you want to fill more or all columns with their respective default values, it gets more sophisticated. Consider this related answer:

Generate DEFAULT values in a CTE UPSERT using PostgreSQL 9.3


I would guess that position is not really part of a relation, but rather an ordering key used to denote what book is the user's first, second, third and so on.

Or, like you say, it could be that a user can have multiple copies of a book, but that still wouldn't make position part of the relation, only of the primary key of UserBooks.

Or, a combination of the above two (ordering key and user can have multiple books).


One way of doing this:

update memberships
   set expired_at = expired_at::date + time '08:00:00'
where extract(hour from expired_at) = 0
  and extract(day from expired_at) = 15

expired_at::date will create a date without a time and then the time is simply added.


What you want is how sequences in PostgreSQL generally behave.

You must have some rogue process which is issuing a command such as:

alter sequence client_id_seq restart;

Or something equivalent to that.  Figure out what is issuing that command, and make it stop.

If you can't figure out who is sabotaging your sequence, there are a variety of ways to figure it out.  One is to crank up your log level by setting log_statement to 'ddl' or 'all', and look in the logs.  Another is to make your table and sequence owned by a role which does not normally log into the database, and grant rights to the table and sequence to the normal log-in users.  They will then get errors when they try to reset the sequence, because only the owner can do that.  (This is a good practice anyway, to have objects owned by special roles which do not log in except when it is necessary to change the schema.)


Why did a SQL Server Restart roll-back seemingly committed transactions?

As per docs online:

  Returns the number of BEGIN TRANSACTION statements that have occurred
  on the current connection.

https://docs.microsoft.com/en-us/sql/t-sql/functions/trancount-transact-sql

This is a connection level variable. If there were transactions open for connections other than the one your colleague ran his query on, there could have been open transactions.


How to represent bike components and their relationship to each other?

Are you documenting a tear-down of an specific existing bike or presenting options for the build of a new bike from available components?

For the former, a tree representing the bill of materials is the solution. There are several ways to represent a tree in a relational database. And, being a tree, a graph DBMS is a natural fit.

To build a bike from options, as in a shopping site for customizable products, a different approach is required. Here the basic units of design are the "precludes" and "requires" relationships.  For example, choosing disk brake hubs "requires" a disk-compatible frame. Choosing a 36 hole hub "precludes" a 20 hole rim. Often it is an attribute of a part that determines applicability rather than the full part number. So we don't say "part XY123 precludes ZQ987" rather we say "frames that are RED cannot have saddles that are leather."

You must also list the categories of items that must all be chosen to form a complete product and those that are optional e.g.

bike = frame + wheel (x2) + seat + chainset + handle bar (all "must")
       + bell + carry rack (both optional).

Given the likely combinatorial explosion it is best to start from a root category (the frame?) and define the choices from there, rather like those choose-your-own-adventure books of yore.

In the abstract this can be modeled as node-types (frame, hub, chainset) with specific instances of each type (Trek steel frame, Norco carbon frame). The connections between the nodes form as denser, more linked graph than in a tear-down.


You say that by using RDP directly for logging onto and managing that server you obviously have enough rights to manage the server.

What is different is that you are now no longer using a direct connection?

If there are more than two computers involved, then probably Kerberos needs to be configured.
Are you sure that SQL Server Agent installed correctly.  Check for installation errors in the Error Log.
Have you manually tried starting or reconfiguring SQL Server Agent?  Or used the Services panel to start SQL Server?  Or run net start SQLSERVERAGENT.

If Kerberos is needed because of multiple hops between computers: Microsoft® Kerberos Configuration Manager for SQL Server® has some guidance on setting up and configuring Kerberos in your environment.


How to get latest timestamped result from time series in a join table

Try this one

with cte as (
select metric_id, value, datetime, ROW_NUMBER () OVER (PARTITION BY metric_id ORDER BY datetime desc) as rn
from his_metric
)

select n.ip, m.id, m.key, m_h.value, m_h.datetime
from metric as m
join node as n on m.node_id = n.id
join cte as m_h on m.id = m_h.metric_id and m_h.rn=1


Race condition in DELETE with nested query?

It appears from empirical evidence that a race condition does exist.

I've seen in the logs of an application running a query such as this where two threads each got to delete a record at the same time, and those records had the same notPK. If a race condition did not exist then only one thread would have gotten to delete a record.

Note that when I asked the above question I didn't think that it would be possible for both threads to each dequeue a notPK; I thought it would only be possible for one thread to dequeue any particular notPK.

Since I observed that there is at least this one kind of race condition, then it must also be possible for a process to fail to dequeue anything even when there is no shortage of records in the queue.

I'm not sure how to concisely put something here that readers can take and validate on their own, so I guess everyone will just have to trust me on this.


Is it worth time converting Access query to T-SQL query

To me 30 queries does not sound like to much work in order to obtain the level of performance increase you will see having SQL Server do the work instead of Access. Especially if the data is going to expand to more users and is a more mission critical type thing.

Something you might consider is running your Access database through the SQL Server Migration Assistant for Access tool. This tool will actually move your queries to SQL Server. You can find more information about the tool here: Access to SQL Server Migration: How To Use SSMA


Partial mysqlbinlog on latest binlog file

You can use FLUSH LOGS to rotate the binlog before it reaches max size.


Monitoring for and dealing with sudden jump in TOAST bloat

If you want to check how bloated the table is, you should install the pgstattuple extension and execute pgstattuple() on the suspected table. If the table is large, as in your case, you can use pgstattuple_approx() to get an approximation.
Regarding your hypothesis, I don't think that Multi-AZ can explain your issue, as PostgreSQL Multi-AZ replication is synchronous. Network issues between master and standby would halt the activity on the master.
Other possible explanation might be:

Autovacuum not being able to catch up with many deletes or updates.
This can happen because of database load that is unrelated to that
query. Look at your logs and CloudWatch metrics for clues
If you have a regular read-replica (not Multi-AZ) then your hypothesis could explain what has happened. There is a CloudWatch metric that shows the replication lag.


stored procedures location influence their performance?

This is something you can easily test for yourself, but in my testing, no, there is no significant overhead in calling a stored procedure across a database boundary (I am sure you could make something noticeable though if you tried hard enough).

However, I would say that a stored procedure should live closer to the data that it manipulates than the object(s) that call it. This way, if you move the database, all that breaks is the remote calls to that procedure, rather than all of them.


How to use postgresql service file in pgadmin4

Try downloading the latest version of pgAdmin4, Many features are implemented now,

Specifically, the one you asked for https://redmine.postgresql.org/issues/3140


MySQL ORDER BY id, but keep identical names next to each other

You need to create an ordering based on the maximum id for name. Here's one attempt:

select id, name
from animals x
order by (select max(id) from animals y where x.name = y.name) desc
    , id desc;

It might be easier to understand if the derived column is explicit, and then order by that:

select id, name
from (
    select id, name
         , (select max(id)
            from animals y
            where x.name = y.name
           ) as max_id
    from animals x
) as t
order by max_id desc, id desc;

You could also use a join to a derived table, instead of the correated subquery:

select x.id, x.name
from animals x
    join
       (select name, max(id) as max_id
        from animals
        group by name
       ) y
    on x.name = y.name
order by y.max_id desc, x.id desc;


Database setup/design for multiple services

good lord, no.

read up on multi-tenancy http://msdn.microsoft.com/en-us/library/aa479086.aspx
read up on the Party Model http://www.tdan.com/view-articles/5014/
read up on Table Inheritance  http://www.martinfowler.com/eaaCatalog/singleTableInheritance.html

Then go read the data modeling books by Fowler, Silverston, or Hay.


Cross-database view - Not able to access database under the current security context

First things first: DO NOT ENABLE TRUSTWORTHY!! There is absolutely no reason to open up such a large security hole. (note: msdb has TRUSTWORTHY enabled and that is fine as it is a Microsoft-supplied DB; User created DBs never need TRUSTWORTHY enabled)

Now, if this works when impersonating a User instead of a Login, it is due to your [Adb] Database already being enabled as TRUSTWORTHY ON, which removes the default quarantine that exists when using Database-level Impersonation. You can see this by executing the following:

SELECT db.is_trustworthy_on, *
FROM   sys.databases db
WHERE  db.[name] IN (N'Adb', N'Bdb');

Assuming that it is the case that Adb is enabled for TRUSTWORTHY and Bdb is not, then still please do not enable TRUSTWORTHY for Bdb. It would be best to disable TRUSTWORTHY for Adb and use Module Signing to accomplish this:

ALTER DATABASE [Adb] SET TRUSTWORTHY OFF;

For an example of doing this cross-database access via Module Signing, please see the following answer of mine (here on DBA.SE):

Access view based on table in another database without account in that other database

For more info on why you should use Module Signing instead of TRUSTWORTHY (or even Cross-Database Ownership Chaining), please see the following post of mine:

PLEASE, Please, please Stop Using Impersonation, TRUSTWORTHY, and Cross-DB Ownership Chaining

For more info on Module Signing in general, please see:

https://ModuleSigning.info/

As @Nic mentioned in a comment on the question, it is best to use EXECUTE AS LOGIN instead of EXECUTE AS USER when testing. Logins are at the Server-level and will have access to Database's that a User has been created in for that Login. This will be just like logging into SQL Server as that account.

The reason for the difference is stated in the Microsoft documentation page for Extending Database Impersonation by Using EXECUTE AS

  Understanding Impersonation Scope

  ...

  However, when impersonating a principal by using the EXECUTE AS USER statement, or within a database-scoped module by using the EXECUTE AS clause, the scope of impersonation is restricted to the database by default. This means that references to objects outside the scope of the database will return an error.

Also, there is a lot of good information on the "Extending Database Impersonation by Using EXECUTE AS" MSDN page (linked above) that explains authenticators and the reasoning behind these rules.

Given that these two Databases are vendor-supplied (info added after I submitted this answer), then it is probably best to just switch to EXECUTE AS LOGIN and not make any changes to the Databases (for Module Signing).


Create postgres schema in db with multi user in AUTHORIZATION

Owned by more than one user: no
Accessible by more than one user: yes

To give a user (or role) access to the tables in a schema you can use something like this:

grant all on schema foobar to role_name;
grant all on all tables in schema foobar to role_name;
grant all on all sequences in schema foobar to role_name;
grant all on all functions in schema foobar to role_name;

The grant all on schema grants the two privileges usage and create on that schema. To quote the manual: "allows access to objects contained in the specified schema (assuming that the objects' own privilege requirements are also met). Essentially this allows the grantee to "look up" objects within the schema". But this does not grant the privilege to select data from a table within the schema. So all of the four statements above are necessary. If you don't want the users to create new objects, then you would use grant usage on schema ....

It is probably easier to grant this to a role and then grant that role to the individual users if you need to do this for more than one user.

If you want to extend the grants to any newly created table (or other objects), you need to change the default privileges on the schema:

alter default privileges in schema foobar grant all on tables to role_name;
alter default privileges in schema foobar grant all on sequences to role_name;
alter default privileges in schema foobar grant all on functions to to role_name;
alter default privileges in schema foobar grant all on types to role_name;

Don't forget to commit those statements if you are not running in autocommit mode.

Of course if you only want those users to access the objects replace the ALL keyword with SELECT or SELECT,INSERT,UPDATE depending on the object type and what you want to allow them.

You probably also want to set the default search_path for those users (if they only need to access that single schema) to avoid having to fully qualify the object names


"Untrusted Domain" error when connecting to SQL Server localhost and specifying port

So, we just had this problem, adding details in case it helps someone else and they don't spend time going down the rabbithole of Kerberos debugging like we did.

I don't have all the details because our next level tier fixed it, but apparently in our case since we had a cross-forest trust authentication, the issue was that we needed to grant the user's group "Allowed to Authenticate" permission on the server object they were trying to connect to, after that it worked fine.

we had a win2k16 server running Sql2017 in domain1, and users running win7 in domain2 with trusts setup between the two, this worked fine.

But when the users migrated to win10, they received the above error. Fix for us was granting 'allowed to authenticate' to the domain user group in domain2 on the server in domain1, then win10 worked fine.

If anyone knows why upgrading client OS makes any difference please let us know!


Unique-like constraint in a complex scenario

To explain ypercubeᵀᴹ's comment:

Yes, this can be solved if you add a second table and an FK from this to the second table

If the requirement is one BatchId per PartnerId,Year,Quarter,Distribution_Type then you will need a table such as:
CREATE TABLE dbo.Partner_Batch --Or whatever
(
    PartnerID             INT           NOT NULL,
    Distribution_Year     INT           NOT NULL,
    Distribution_Quarter  INT           NOT NULL,
    Distribution_Type     VARCHAR(100)  NOT NULL,
    Batch_ID              INT           NOT NULL,
    CONSTRAINT PK_Partner_Batch PRIMARY KEY (PartnerId, Distribution_Year, Distribution_Quarter, Distribution_Type)
)

Of course you'd need the relevant FK references for PartnerID and Batch_ID as well as something to ensure Quarter is only valid values (1,2,3,4).
You would then modify your existing table with the foreign key constraint to ensure the rule is enforced:
CREATE TABLE [dbo].[Distribution]
(
    Distribtution_Result_ID   INT IDENTITY(1,1) NOT NULL,
    PartnerID                 INT               NOT NULL,
    Distribution_Year         INT               NOT NULL,
    Distribution_Quarter      INT               NOT NULL,
    Distribution_Amount       DECIMAL(16,4)     NULL,
    Distribution_Type         VARCHAR(100)      NOT NULL, --Note: This column cannot be NULL
    Batch_ID                  INT               NOT NULL,
    CONSTRAINT FK_Distribution_Partner_Batch FOREIGN KEY (PartnerId, Distribution_Year, Distribution_Quarter, Distribution_Type) REFERENCES dbo.Partner_Batch (PartnerId, Distribution_Year, Distribution_Quarter, Distribution_Type) --I would use a more descriptive name for the relation, but I don't have enough information about your system
)

From afar, there's a lot more work to be done, but at minimum I'd say you'd need to:

Have a reference table for Distribution_Type to only allow valid values.
Trim down the data type for Distribution_Type or substitute a shortname/code.
Define an actual key for Distribution other than the row identifier.


Linked Server to Oracle seems to be slow

You must change the Incompatable Database Engine Server Collation into Linked Server Options. Set Value to True


In short: SHOW INNODB STATUS will give you the last detected deadlock.
 use SHOW ENGINE INNODB STATUS for MySQL 5.x

Line 1 gives the time when the deadlock happened. If your application code catches and logs deadlock errors,which it should, then you can match this timestamp with the timestamps of deadlock errors in application log. You would have the transaction that got rolled back. From there, retrieve all statements from that transaction.

Line 3 & 11, take note of Transaction number and ACTIVE time. If you log SHOW ENGINE INNODB STATUS output periodically(which is a good practice), then you can search previous outputs with Transaction number to hopefully see more statements from the same transaction. The ACTIVE sec gives a hint on whether the transaction is a single statement or multi-statement one.

Line 4 & 12, the tables in use and locked are only with respect to the current statement. So having 1 table in use does not necessarily mean that the transaction involves 1 table only.

Line 5 & 13, this is worth of attention as it tells how many changes the transaction had made, which is the “undo log entries” and how many row locks it held which is “row lock(s)”. These info hints the complexity of the transaction.

Line 6 & 14, take note of thread id, connecting host and connecting user. If you use different MySQL users for different application functions which is another good practice, then you can tell which application area the transaction comes from based on the connecting host and user.

Line 9, for the first transaction, it only shows the lock it was waiting for, in this case the AUTO-INC lock on table t1. Other possible values are S for shared lock and X for exclusive with or without gap locks.

Line 16 & 17, for the second transaction, it shows the lock(s) it held, in this case the AUTO-INC lock which was what TRANSACTION (1) was waiting for.

How to avoid a MySQL deadlock
There are things we could do to eliminate a deadlock after we understand it.

– Make changes to the application. In some cases, you could greatly reduce the frequency of deadlocks by splitting a long transaction into smaller ones, so locks are released sooner. In other cases, the deadlock rises because two transactions touch the same sets of data, either in one or more tables, with different orders. Then change them to access data in the same order, in another word, serialize the access. That way you would have lock wait instead of deadlock when the transactions happen concurrently.

– Make changes to the table schema, such as removing foreign key constraint to detach two tables, or adding indexes to minimize the rows scanned and locked.

– In case of gap locking, you may change transaction isolation level to read committed for the session or transaction to avoid it. But then the binlog format for the session or transaction would have to be ROW or MIXED.


JDBC local MSSQL Server connection string

I have found my answer from dbeaver support forum.

http://dbeaver.jkiss.org/forum/viewtopic.php?f=3&t=624&p=4261#p4261

First enable TCP/IP for your SQL instance in the SQL Server Configuration Manager tool.

Open SSCM and navigate to SQL Server Network Configuration > Protocols for
SQLEXPRESS > TCP/IP
Enable the TCP/IP protocol in the Properties
dialog for TCP/IP. (You can also enable it on the right-click menu.)
Set the TCP/IP port -- which can only be done in the Properties
dialog (i.e., can't do it with just a right-click).  On the "IP
Addresses" tab of that Properties dialog, I used the IPAll settings
at the bottom. Clear out TCP Dynamic Ports, then set TCP Port to
1433.

From within DBeaver, you can create either a jDTS or a "Microsoft Driver" connection

To create a jTDS connection in DBeaver

Create a new connection, specifying MS SQL Server | jTDS driver
Fill out the info on the first (General) tab, complete with User name and Password. I was able to enter my User name as just my Windows user name, not domain\username. But domain\username worked too, and that may be required on some networks. (I'm not sure.)
Go to the "Driver properties" tab and re-specify the DOMAIN and the USER as two separate parameters.
I did not need to re-specify my PASSWORD on the "Driver properties" tab.
And just to emphasize where to enter this: it's entered on the "Driver properties" tab -- not in the dialog box you get to by clicking the "Edit Driver Settings" button that's on the General tab.
Click the "Test Connection..." button to make sure it works, click Next a couple of times, then click Finish.

To create a "Microsoft Driver" connection in DBeaver

Create a new connection, specifying MS SQL Server | Microsoft Driver
Fill out the info on the first (General) tab, without specifying User name and Password (leave them blank).
Go to the Driver properties tab and set integratedSecurity=true.
And, once again, just to emphasize where to enter this: it's entered on the "Driver properties" tab -- not in the dialog box you get to by clicking the "Edit Driver Settings" button that's on the General tab.
Click the "Test Connection..." button to make sure it works, click Next a couple of times, then click Finish.


Find out which columns must be specified in insert-statement

The BOL documentation for the INSERT grammar has this to say about column_list

  If a column is not in column_list, the Database Engine must be able to
  provide a value based on the definition of the column; otherwise, the
  row cannot be loaded. The Database Engine automatically provides a
  value for the column if the column:

  Has an IDENTITY property. The next incremental identity value is used.
  Has a default. The default value for the column is used.
  Has a timestamp data type. The current timestamp value is used.
  Is nullable. A null value is used.
  Is a computed column. The calculated value is used.

It looks as though your query covers all these cases to me.


Huge index for covered queries (indexOnly: true) or (indexOnly: false) with collection scan

Why is indexOnly: false. Isn't it supposed to be a covered index
  query? (see the explain later)

I believe this is a result of the isMultiKey : true field in the explain results.  Basically, currently indexOnly is never true when isMultiKey is true.

This is a known problem in general with multi key indexes.  You can find the relevant bug here:

https://jira.mongodb.org/browse/SERVER-3173

As well as some decent explanation in the linked/dupe bug here:

https://jira.mongodb.org/browse/SERVER-7595

I think you have done some manual munging of the fields here for some reason, but I would guess that search.keywords is the problem here.  Try an index without that as the final field and see if that performs better.

  I need to retrieve some additional fields from the collection (the id
  and the profile_picture url). Should I add them to the index to avoid
  hitting the collection, even if I'll never have to query them?

I'd recommend a separate index for those queries rather than massive single index.  If you end up with too many fields in the index you are going to lose most of the benefit by simply having to scan through a massive index instead of a collection.  An index that big will also likely have performance issues for updates/writes.