Mysql Select recent 2 records for each group[user]

In this case I'd suggest to use a subquery to count rows using a variable.

select   user_id, name, group_concat(login_date)
from     (select user_id, login_date, name
          from (select user_id, login_date, name,
                       if(@last_user = user_id, @rn := @rn + 1, @rn := 0) rn,
                       @last_user := user_id
                from (select @last_user := 0, @rn := 0) a,
                     (select users.user_id, login_date, users.name
                      from users
                      left join login_track
                      on users.user_id = login_track.user_id
                      order by users.user_id, login_date desc) b
               ) c
          where rn <= 1
         ) d
group by user_id;

| user_id | name     | group_concat(login_date) |
|---------|----------|--------------------------|
| 1       | John     | 2017-06-27,2017-06-23    |
| 2       | Mike     | 2017-06-23,2017-06-21    |
| 3       | Anderson | NULL                     |

rextester here


Your thoughts are to the point.

The only thing I want to suggest is not to use the db_ddladmin account if you only want to truncate tables.
The minimum permission for truncating a table is ALTER, see Truncate Table. So you could make a custom database role with ALTER permissions on all tables you need to truncate. Assign that role to the ETLUser account.

A proxy account is in my opinion indeed the best way to run the package under the ETLUser account in an SQL Server agent job.


SQL Server 2012 SQL Log "the log file is not using Unicode format":

I had an idea which worked. I forced creation of a new log using sp_cycle_errorlog and the new log can be read fine in SSMS. This server recently had a bad crash due to a SAN connection failure. I think that the SQL log file may have bcome corrupted as a result of that.


Why PostgreSQL volatile function is faster than inline subquery?

Volatility has to do with the expected results and the type of query so the query planner can be more efficient.
I suspect it has something to do with that your query is performing an index scan.
Without an explain it is very difficult to know what the query planner is doing. Please add this.


Remove InnoDB files (ibdata1 and iblog files) after migration to MyISAM

In case you do not have a single InnoDB table, then yes, you can remove the ibdata file and the ib_logfile[01] files.

However, note:

With MySQL 5.6 there are system InnoDB tables, which means you don't want to remove the InnoDB files ever.
It is best done when the server is stopped.


Check connection details to SQL Server 2000/2005

I am not sure regarding SQL Server 2000 since I did not work on it before, but if you want to check active transactions on SQL 2005, you can execute the following command SELECT * FROM sys.dm_tran_session_transactions. to check active sessions execute sp_who.
Useful commands:

check active queries

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

or

select
P.spid
,   right(convert(varchar,
        dateadd(ms, datediff(ms, P.last_batch, getdate()), '1900-01-01'),
        121), 12) as 'batch_duration'
,   P.program_name
,   P.hostname
,   P.loginame
from master.dbo.sysprocesses P
where P.spid > 50
and      P.status not in ('background', 'sleeping')
and      P.cmd not in ('AWAITING COMMAND'
                ,'MIRROR HANDLER'
                ,'LAZY WRITER'
                ,'CHECKPOINT SLEEP'
                ,'RA MANAGER')
order by batch_duration desc

to check free disk space: https://www.mssqltips.com/sqlservertip/1706/determine-free-disk-space-in-sql-server-with-tsql-code/
to check Server status I recommend to use activity monitor (you can use resource monitor as well).


SQL Split Address Line into Separate Columns

STRING_SPLIT() is probably best in SQL Server 2016. I've tested against 1 million rows and returns results in 12 seconds (fairly contrived test).

You can use the PIVOT operator to produce the columns from your rows following the split as there are a known number of elements.

Code to setup

CREATE TABLE #Table (ID INT IDENTITY, StreetAddress VARCHAR(MAX))

DECLARE @I INT = 1000000

WHILE (@I) > 0
BEGIN
       INSERT INTO #Table (StreetAddress) VALUES ('123 Fake St, BigCity, My State, 12345')
       SET @I = @I -1
END

Code to retrieve split address elements

SELECT ID,
       [1] AS Street,
       [2] AS City,
       [3] AS State,
       [4] AS Code
FROM
(
       SELECT ID, value, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS Rn
       FROM #Table
       CROSS APPLY STRING_SPLIT(REPLACE(StreetAddress, ', ', ','), ',')
) src
PIVOT
(
       MAX(Value) FOR Rn IN ([1], [2], [3], [4])
) pvt

You can probably improve performance by eliminating the REPLACE in STRING_SPLIT, but you'll then need to TRIM your columns to remove whitespace.


What is the last version of postgres with pgadmin 3?

While you may be strongly discouraged from continuing to use pgAdmin 3, there's nothing stopping you if you really want to use it. Below you will see pgAdmin 3 connected to a postgresql 12 instance.

Note however, that you're very much doing this at your own risk, you're going to have to click through a lot of error messages when running in an unsupported setup like this.

Some pretty obvious in retrospect...

Some just annoying...

In general I find a lot of these errors stem from breaking-change updates to the system catalogs between 9.* and 10+. Whether or not they're deal breakers to using this configuration is left to you. If you do decide to press ahead and try to use this setup, there will be a lot of heartache (for example, pgAdmin 3 as provided just won't run on macOS Catalina, end of discussion).

Around these parts you will hear a lot of the "just use psql" line. I use psql and I love it. But sometimes it's nice to have a GUI. I use pgAdmin 4 and I have a love-hate relationship with it. The pgAdmin line has been "meta-stable" IMO for quite some time and I imagine it will remain so for all but the most power-users for at least a bit.


Connecting to SQL Server with SQL Server Management Studio

The default server name from your screenshot is showing DESKTOP-6ECNPU1, not DESKTOP-6ECNPU1\matt, the latter is the user you are connecting into sql server with.

The sql server browser service is used to route the connection to the appropriate instance on the server. The default sql server instance is installed on port 1433. If you were to connect to a named instance, then the name will be translated to the port that the instance is running on, otherwise if only the machine name is specified you get routed to the default instance.

Based on your screenshot that installation is the default instance (because instance name is MSSQLSERVER), so if you only specify the name of the machine, you will be connecting
to that 2017 sql server instance you installed.

If you want to double check even further, see James answer and run that command, also see @@servicename


Update large number of row in MySQL table

Look at this:

-- this trigger needed for initial balance calculation
-- or emergency re-calculation (generally for one customer)
CREATE TRIGGER tr_bu_customer_transaction
BEFORE UPDATE
ON customer_transaction
FOR EACH ROW
SET NEW.balance = COALESCE( ( SELECT balance
                              FROM customer_transaction
                              WHERE customer_id = NEW.customer_id
                                AND id < NEW.id
                              ORDER BY id DESC LIMIT 1 ), 0) + NEW.amount;

-- this trigger needed for balance calculation for newly inserted records
CREATE TRIGGER tr_bi_customer_transaction
BEFORE INSERT
ON customer_transaction
FOR EACH ROW
SET NEW.balance = COALESCE( ( SELECT balance
                              FROM customer_transaction
                              WHERE customer_id = NEW.customer_id
                              ORDER BY id DESC LIMIT 1 ), 0) + NEW.amount;

Usage sample: fiddle.

PS. Of course the whole table will be locked during initial calculation. But I think this will take less time then the technique with correlated query usage. And this allows to perform initial calculation for separate user or users list per chunk easily.

PPS. I assume that the transaction records (more precisely - their base data) are updated NEVER.


How to enforce a column value on duplicate values on postgresql using constraint?

A partial index like suggested by @ypercube is likely the best solution. If you for some reason would like to keep it at the logical level you can generate a column:
If ACTIVE: concatenate the "partially unique" columns, I used a token separator to distinguish columns, result of 'a' || 'bc' should be different from 'ab' || 'c'
If NOT ACTIVE: use the primary key instead of the "unique" columns
I used xyz as the name of the generated column because I did not know better:-)
create table mytable
( id serial primary key
, action integer not null
, model text not null
, model_id integer not null
, active boolean not null
, xyz text GENERATED ALWAYS AS ( case when active
                                      then action::text
                                        || '#'
                                        || model
                                        || '#'
                                        || model_id::text
                                     else id::text
                                 end
                               ) stored
, unique (xyz)
);


Dealing with co-dependent not null columns

First, a name like 'entities' is too vague. The common name for org or individual is 'parties'.

Second, people can have zero, one, or many addresses, and can share the same address. It's a many2many relationship.

I would do it like this (using STI here, but break it out to CTI if you like):

PARTIES
id
type (org or individual)
name

ADDRESSES
id
type
address_info...

PARTY_ADDRESSES
party_id
address_id
role (ex 'sales', 'service', 'mobile', 'work', ...)
priority

Your validation rule would be that a party must have an address of type 'email' whose priority is higher than any other of their addresses. Business rules are best enforced via triggers any applicable logic and not your data model.


Sum every X rows with no common group by parent

One way is to divide the rows into groups/partitions - using simple math (integer division) and group over the partitions. I assume that the Row_ID values are UNIQUE.

If - unlikely but if - there are no gaps in the Row_ID values and they start from 1, then we could use the simple:

select row_id, amt,
       sum = sum(amt) over (partition by (row_id-1)/@GroupSize)
from t ;

But since this is unlikely - the values probably come from an IDENTITY column or a SEQUENCE, which don't guarantee gapless sequences - we have to create a gapless sequence, with ROW_NUMBER():

with c as
  ( select row_id, amt,
           grp = (row_number() over (order by row_id) - 1) / @GroupSize
    from t
  )
select row_id, amt,
       sum = sum(amt) over (partition by grp)
from c ;

Tested in dbfiddle.uk:

create table t
(Row_ID int, amt DECIMAL(18,2)
) ;
GO

✓

insert into t values
  (1, 20),
  (2, 24),
  (3, 20),
  (4, 15),
  (5, 15) ;
GO

5 rows affected

with c as
  ( select row_id, amt,
           grp = (row_number() over (order by row_id) - 1) / 2
    from t
  )
select row_id, amt,
       sum = sum(amt) over (partition by grp)
from c ;
GO

row_id | amt   | sum
-----: | :---- | :----
     1 | 20.00 | 44.00
     2 | 24.00 | 44.00
     3 | 20.00 | 35.00
     4 | 15.00 | 35.00
     5 | 15.00 | 15.00

with c as
  ( select row_id, amt,
           grp = (row_number() over (order by row_id) - 1) / 3
    from t
  )
select row_id, amt,
       sum = sum(amt) over (partition by grp)
from c ;
GO

row_id | amt   | sum
-----: | :---- | :----
     1 | 20.00 | 64.00
     2 | 24.00 | 64.00
     3 | 20.00 | 64.00
     4 | 15.00 | 30.00
     5 | 15.00 | 30.00

In order to make the number of rows parametrisable, you just need to replace the divisor in the grp expression with a parameter/variable reference:

grp = (row_number() over (order by row_id) - 1) / @GroupSize

and, consequently, pass the parameter value when you invoke the query.


Export roles *with* privileges under PostgreSQL 9.3

Documentation says: "Database roles are global across a database cluster installation (and not per individual database)." Hovewer, privileges are object based, therefore their definition is stored in databases separately. If you backup your database with a command like; "pg_dump .. --schema-only" you will see the privileges in there.


Postgres 9.2 select multiple specific rows in one query

UNION ALL
I would go with a simple UNION ALL query here:
(
SELECT *
FROM   tbl
WHERE  timein >= '2013-04-26 0:0'
AND    timein <  '2013-04-27 0:0'
ORDER  BY timein DESC
LIMIT 1
)
UNION ALL
(
SELECT *
FROM   tbl
WHERE  timein >= '2013-04-27 0:0'
AND    timein <  '2013-04-30 0:0'
ORDER  BY timein
);

This is a single query to Postgres.
Parentheses are required in this case.
NOT EXISTS
Alternative, most likely slower:
SELECT *
FROM   tbl t
WHERE  timein >= '2013-04-26 0:0'
AND    timein <  '2013-04-30 0:0'
AND    NOT EXISTS (
   SELECT 1 FROM tbl t1
   WHERE  t1.timein >= '2013-04-26 0:0'
   AND    t1.timein <  '2013-04-27 0:0'
   AND    t1.timein > t.timein
    )
ORDER  BY timein;

-> SQLfiddle.


Definitely, in a Relational Data Management System, you SHOULD use relationships between your entities.

A simple example query where you will be stuck by using the first option :

  Select all users and role name with roles 1 or 11

You will have to perform character to character searches and comparisons that are much less efficient than relational integer search and comparisons beacause the latter are indexed, and algorithms to perform them are much more sophisticated than pure string compare.

Unless you are really constrained (and even if so I would say...) use table relations, referential integrity constraints, indexes and so on, they will save your time !


Create View in specified database with dynamic sql?

You can use nested EXEC calls. The database context changed by the USE persists to the child batch.

DECLARE @DB SYSNAME

SET @DB = 'tempdb'

DECLARE @CreateViewStatement NVARCHAR(MAX)
SET @CreateViewStatement = '
      USE '+ QUOTENAME(@DB) +';
      EXEC(''
             CREATE VIEW [dbo].[MyTable] AS
             SELECT 1 AS [Foo]
      '')

                          '
EXEC (@CreateViewStatement)


1)

SELECT t1.*
FROM current_table t1
LEFT JOIN previous_table t2 ON t1.field1 = t2.field1
                           AND t1.field2 = t2.field2
                     --    another fields in tables structures
                           AND t1.fieldN = t2.fieldN
WHERE t2.fieldX IS NULL /* fieldX - any field defined as NOT NULL */

2)

SELECT t2.*,
       t1.fieldZ -- new value
FROM current_table t1
INNER JOIN previous_table t2 ON t1.field1 = t2.field1
                            AND t1.field2 = t2.field2
                      --    another static fields in tables structures
                            AND t1.fieldN = t2.fieldN
                      --    altered field
                            AND t1.fieldZ != t2.fieldZ
                      /* if more than one field may be changed, then
                            AND (     t1.fieldZa != t2.fieldZa
                                   OR t1.fieldZb != t2.fieldZb
                               --  OR ...
                                )
                      */


Conditional formatting based on number of days in SSRS against a cell value in reference to another field

You will need to come up with an expression for your fill color of that cell or field you want to change. So an example:

The basic setup is I just have 2 variables to enter a date and then a text box on the body of the report I will adjust the fill color based on the days between those two dates:

So I go into the text box properties and find the Fill page, I will set the "Fill Color" to this expression:

=IIF (DateDiff("d", Parameters!activity_date.Value, Parameters!valid_to.Value) > 14
,"Green","Yellow")

Then my result:

In your case you will just build out the "IIF" to follow the criteria you have for your formatting.

This is just a guess from doing it in my head (and writing it out in Notepad++) but I think this should be what you are looking for:

=IIF(Fields!valid_to.Value = Today() AND IsNothing(Fields!cell_value.Value) = 1, "Red",
    IIF(Fields!valid_to.Value < Today() AND IsNothing(Fields!cell_value.Value) = 0, "Orange",
        IIF(Fields!valid_to.Value = Today() AND DateDiff("d",Today(),Fields!valid_to.Value) <= 14, "Yellow",
            IIF(Fields!valid_to.Value > Today() AND IsNothing(Fields!cell_value.Value) = 0, "Blue",
                IIF(DateDiff("d",Today(),Fields!valid_to.Value) >= 15 AND IsNothing(Fields!cell_value.Value) = 0, "Green","White")
                )
            )
        )
    )


Try building a GIN index with gin_trgm_ops instead of GiST.  I've never found GiST useful for trgm indexes.

That said, if your are failing with that message, you probably have some weird data in your table that should be cleaned up. Can you figure which row is causing the problem?  (If not, start with the longest records first as the most likely suspects)


Why has my root password changed after I updated MySQL from 5.7 to 8.0?

MYSQL changed the default
If you chosen

Use Strong Password Encryption for Authentication (RECOMMENDED)

mysql 8.x uses the new authentication based on improved, stronger SHA256-based password methods.
https://dev.mysql.com/doc/mysql-installer/en/server-authentication-method.html
when you and your apps don't support it, you have to choose the legacy mode
But you can use user with the old authentficationwhen you use
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '<password>';

You have to add native password to all your  roo
'root'@'::1'   refers ipv6 localhost
'root'@'127.0.0.1'
And
'root'@'localhost'  zto ip V4
As you connect via 1pv6 change this apsword as well but use 'root'@'::1'`
localhoszt and 127.0.0.1 only work on a computer itself not over a network
how you can bind  the server to ipv4 or ipv6 is described here https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html
And as last poit see upgrade instructions in mysql, you can't only exchange the exe https://dev.mysql.com/doc/refman/8.0/en/upgrading.html


In PostgreSQL a function is always be executed in one transaction. It does not matter if you loop over batches of 10.000 rows or just UPDATE the whole table.

Thanks to MVCC there should also no need to split the UPDATE in order to allow other processes to do something on the table, as the locks are very lightweight in this scenario.

So, the correct replacement would be:

CREATE OR REPLACE FUNCTION emp_up() AS
$func$
  update employees set commission_pct=0 where commission_pct is null
$func$
LANGUAGE plpgsql;


SQL Server Login attempts fail when using SSMS but succeed when using SQLCMD

Issue seems to be with the local SSMS install on the server. Everything works fine from odbc connections, remote SSMS connections etc.


Here is a query I use to get all the used space of MySQL for all storage engines:

SELECT
    IFNULL(ENGINE,'Total') "Storage Engine",
    LPAD(CONCAT(FORMAT(DAT/POWER(1024,pw1),2),' ',
    SUBSTR(units,pw1*2+1,2)),17,' ') "Data Size",
    LPAD(CONCAT(FORMAT(NDX/POWER(1024,pw2),2),' ',
    SUBSTR(units,pw2*2+1,2)),17,' ') "Index Size",
    LPAD(CONCAT(FORMAT(TBL/POWER(1024,pw3),2),' ',
    SUBSTR(units,pw3*2+1,2)),17,' ') "Total Size"
FROM
(
    SELECT ENGINE,DAT,NDX,TBL,
    IF(px>4,4,px) pw1,IF(py>4,4,py) pw2,IF(pz>4,4,pz) pw3
    FROM
    (SELECT *,
        FLOOR(LOG(IF(DAT=0,1,DAT))/LOG(1024)) px,
        FLOOR(LOG(IF(NDX=0,1,NDX))/LOG(1024)) py,
        FLOOR(LOG(IF(TBL=0,1,TBL))/LOG(1024)) pz
        FROM
        (SELECT
            ENGINE,
            SUM(data_length) DAT,
            SUM(index_length) NDX,
            SUM(data_length+index_length) TBL
        FROM
        (
           SELECT engine,data_length,index_length FROM
           information_schema.tables WHERE table_schema NOT IN
           ('information_schema','performance_schema','mysql')
           AND ENGINE IS NOT NULL
        ) AAA GROUP BY ENGINE WITH ROLLUP
) AAA ) AA) A,(SELECT ' BKBMBGBTB' units) B;

By Database

SELECT
    IFNULL(DB,'Total') "Database",
    LPAD(CONCAT(FORMAT(DAT/POWER(1024,pw1),2),' ',
    SUBSTR(units,pw1*2+1,2)),17,' ') "Data Size",
    LPAD(CONCAT(FORMAT(NDX/POWER(1024,pw2),2),' ',
    SUBSTR(units,pw2*2+1,2)),17,' ') "Index Size",
    LPAD(CONCAT(FORMAT(TBL/POWER(1024,pw3),2),' ',
    SUBSTR(units,pw3*2+1,2)),17,' ') "Total Size"
FROM
(
    SELECT DB,DAT,NDX,TBL,
    IF(px>4,4,px) pw1,IF(py>4,4,py) pw2,IF(pz>4,4,pz) pw3
    FROM
    (SELECT *,
        FLOOR(LOG(IF(DAT=0,1,DAT))/LOG(1024)) px,
        FLOOR(LOG(IF(NDX=0,1,NDX))/LOG(1024)) py,
        FLOOR(LOG(IF(TBL=0,1,TBL))/LOG(1024)) pz
    FROM
    (SELECT
        DB,
        SUM(data_length) DAT,
        SUM(index_length) NDX,
        SUM(data_length+index_length) TBL
    FROM
    (
       SELECT table_schema DB,data_length,index_length FROM
       information_schema.tables WHERE table_schema NOT IN
       ('information_schema','performance_schema','mysql')
       AND ENGINE IS NOT NULL
    ) AAA GROUP BY DB WITH ROLLUP
) AAA) AA) A,(SELECT ' BKBMBGBTB' units) B;

By Database/Storage Engine

SELECT
    IF(ISNULL(DB)+ISNULL(ENGINE)=2,'Database Total',
    CONCAT(DB,' ',IFNULL(ENGINE,'Total'))) "Reported Statistic",
    LPAD(CONCAT(FORMAT(DAT/POWER(1024,pw1),2),' ',
    SUBSTR(units,pw1*2+1,2)),17,' ') "Data Size",
    LPAD(CONCAT(FORMAT(NDX/POWER(1024,pw2),2),' ',
    SUBSTR(units,pw2*2+1,2)),17,' ') "Index Size",
    LPAD(CONCAT(FORMAT(TBL/POWER(1024,pw3),2),' ',
    SUBSTR(units,pw3*2+1,2)),17,' ') "Total Size"
FROM
(
    SELECT DB,ENGINE,DAT,NDX,TBL,
    IF(px>4,4,px) pw1,IF(py>4,4,py) pw2,IF(pz>4,4,pz) pw3
    FROM
    (SELECT *,
        FLOOR(LOG(IF(DAT=0,1,DAT))/LOG(1024)) px,
        FLOOR(LOG(IF(NDX=0,1,NDX))/LOG(1024)) py,
        FLOOR(LOG(IF(TBL=0,1,TBL))/LOG(1024)) pz
    FROM
    (SELECT
        DB,ENGINE,
        SUM(data_length) DAT,
        SUM(index_length) NDX,
        SUM(data_length+index_length) TBL
    FROM
    (
       SELECT table_schema DB,ENGINE,data_length,index_length FROM
       information_schema.tables WHERE table_schema NOT IN
       ('information_schema','performance_schema','mysql')
       AND ENGINE IS NOT NULL
    ) AAA GROUP BY DB,ENGINE WITH ROLLUP
) AAA) AA) A,(SELECT ' BKBMBGBTB' units) B;

CAVEAT ON InnoDB FRAGMENTATION

To find the fragmentation of an individual InnoDB table (.ibd) you cannot fully trust the INFORMATION_SCHEMA database. You have to piggyback off the OS.

Let's say the table is mydb.mtable and datadir is /var/lib/mysql

Get the Size of the InnoDB Table from OS and subtract it from what INFORMATION_SCHEMA.tables says

SQLSTMT="SELECT SUM(data_length+index_length) size FROM information_schema.tables"
SQLSTMT="${SQLSTMT} WHERE table_schema='mydb' AND table_name='mytable'"
FILESIZE_OS=`ls -l /var/lib/mysql/mydb/mytable.ibd | awk '{print $5}'`
FILESIZE_DB=`mysql -uroot -ppassword -ANe"${SQLSTMT}"`
(( FILESIZE_FRAG = FILESIZE_OS - FILESIZE_DB ))

CAVEAT ON MyISAM FRAGMENTATION

For the MyISAM table mydb.myatble for the .MYD

SELECT data_length INTO @datsize
FROM information_schema.tables
WHERE table_schema='mydb'
AND table_name='mytable';
SELECT table_rows*avg_row_length INTO @sizerows
FROM information_schema.tables
WHERE table_schema='mydb'
AND table_name='mytable';
SET @tblfrag = @datsize - @sizerows;
SELECT @datsize,@sizerows,@tblfrag;

CAVEAT ON Tablespace file ibdata1

Here is a look at the InnoDB Architecture

Please note that ibdata1 has 5 moving parts

Data Dictionary
Double Write Buffer (support data consistency; used for Crash Recovery)
Insert Buffer (Buffers Changes to Secondary Non-Unique Indexes)
Rollback Segments
Undo Space (where the most uncontrolled growth can happen)

Since you have innodb_file_per_table enabled, the data and index pages for all InnoDB have been separated from these internal parts. Thus, there is no need to monitor ibdata1 internally.

For the benefit of those who have innodb_file_per_table disabled, then everything InnoDB and its grandmother is inside ibdata1. This is how you monitor ibdata1:

Let's say your innodb_data_fie_path is this

[mysqld]
innodb_data_file_path=ibdata1:2000M;ibdata2:10M:autoextend

You would have to add the file sizes of the two files and subtract the sum of the data and index pages. Perhaps like this:

cd /var/lib/mysql
IBDATA_SIZE=0
for X in `ls -l ibdata* | awk '{print $5}'` ; do (( IBDATA_SIZE += X )) ; done
SQLSTMT="SELECT SUM(data_length+index_length)"
SQLSTMT="${SQLSTMT} FROM information_schema.tables"
SQLSTMT="${SQLSTMT} WHERE engine='InnoDB'"
INNODB_SIZE=`mysql -uroot -ppassword -ANe"${SQLSTMT}"`
(( IBDATA_FRAG = IBDATA_SIZE - INNODB_SIZE ))

See my additional suggestion on handling multiple ibdata files : How to solve "The table ... is full" with "innodb_file_per_table"?


If you need just to check existence I'd go with
SELECT 1 FROM DUAL WHERE EXISTS (SELECT NULL FROM [your query]) ...

For "top-n" queries there are quite a few alternatives - https://oracle-base.com/articles/misc/top-n-queries . Note, some of them may have side effects ; for instance, where rownum < :value can change optimizer mode to FIRST_ROWS.


Store it in a way that makes sense, which is to say ignore the interval approach and just store the breakpoints like such:
Role        | MaxMinuteAmt      | Cost
Plumber     | 10                | 16
Plumber     | 15                | 23
Plumber     | 40                | 58
Plumber     | 32767             | 63

The primary key for this table is (Role,MaxMinuteAmt).  This prevents any data integrity issues with improperly entered intervals and assists in the quick lookup of the appropriate value.
To return the appropriate Cost, you would use the following query (which can be hardened into a stored procedure):
SELECT
  EmpRoleCost.Cost
FROM
  EmployeeRoleCost EmpRoleCost
WHERE
  EmpRoleCost.Role = 'Plumber'
    AND EmpRoleCost.MaxMinuteAmt =
      (
        SELECT
          MIN(MaxMinuteAmt)
        FROM
          EmployeeRoleCost
        WHERE
          Role = EmpRoleCost.Role
            AND MaxMinuteAmt >= <Number of Minutes>
      )

So if we query with a duration of 1 minute, the minimum value greater than or equal to that value is 10 and so on. Anything over 40 minutes up to the maximum SMALLINT value would return the same value (you could go with a larger datatype if needed).
This is assuming that anything up to 32,767 minutes would be charged €63, but my guess is there's probably some fixed rate once you get above a certain value (say 60 minutes).  In that case you'd change the above table to:
Role        | MaxMinuteAmt      | Cost
Plumber     | 10                | 16
Plumber     | 15                | 23
Plumber     | 40                | 58
Plumber     | 60                | 63

And create a second table to store the rate for amounts over, along with the duration of the rate (let's say 15 minutes for this example):
Role        | MinuteAmt     | Rate
Plumber     | 15            | 15.75

Then we'd change the query to utilize that rate if no flat cost is found:
SELECT
  COALESCE
    (
      EmpRoleCost.Cost
     ,EmpOverage.Rate * CEILING(<Actual number of minutes>/EmpOverage.MinuteAmt)
    ) AS Cost
FROM
  EmployeeRole EmployeeRole
LEFT JOIN
  EmployeeRoleOverageRate EmpOverage
    ON EmpOverage.Role = EmployeeRole.Role
LEFT JOIN
  EmployeeRoleCost EmpRoleCost
    ON EmpRoleCost.Role = EmployeeRole.Role
        AND EmpRoleCost.MaxMinuteAmt =
          (
            SELECT
              MIN(MaxMinuteAmt)
            FROM
              EmployeeRoleCost
            WHERE
              Role = EmployeeRole.Role
                AND MaxMinuteAmt >= <Number of Minutes>
          )
WHERE
  EmployeeRole.Role = 'Plumber'

So in this case if the number of minutes was 65, the cost would be €78.75 (CEILING(65/15) * 15.75).
See this Fiddle.


Does the row locking on slave database also apply to master database?

There is no communication from Slave to Master (aside from network protocols to receive the Replication updates from the Master.

Writing on a Slave is a no-no.  The basic principle of Replication is that the Slave will have exactly (except for a slight lag) the same data as the Master.  Writing to the Slave does not allow for keeping them in sync.

An exception is with Master-Master -- wherein two servers are acting both as Master and as Slave.  Still, writing to both leads to a lot of potential problems, and should be avoided.


Mysql get records more then 3 in interval of 1 minute, return group_concat ID

Here is the answer - see the fiddle. Another answer I wrote to a similar question may provide some clearer background and is a bit simpler - see here.
All I will say is that it gives some idea of the power of window functions.
I noticed in the comments that there was some debate about what constituted a group - in this example, I have constructed the SQL such that it picks out as a group where all subsequent sessions started within 180 seconds (i.e. 3 mins) of the first - you can change the 180 to 60 (or whatever) yourself.
I've added in some records for the purpose of testing and also added CONSTRAINTs to the table definition. It's always best to put as much as possible into the DDL - your database is your final bastion of defence for your data!
CREATE TABLE test
(
  id bigint NOT NULL,
  created_date TIMESTAMP(2) NOT NULL,
  origin_url   VARCHAR (200) NOT NULL,
  client_session_id VARCHAR (50) NOT NULL,
  CONSTRAINT test_id_pk PRIMARY KEY (id),
  CONSTRAINT test_cd_url_sess_id_uq UNIQUE (created_date, origin_url, client_session_id)
);

Always use named constraints - these provide more meaningful messages than: ... failed... CONSTRAINT xyz_000_43abc has been violated....
I populated it as follows:
INSERT INTO test VALUES

--
-- 1 lone record...
--

(1,'2021-01-18 10:30:24.0', 'https://zendes.com/', 'znkjoc3gfth2c3m0t1klii'), -- XX

--
-- 4 records within 180 seconds of the first one
--

(2,'2021-01-18 11:02:24.0', 'https://zes.com/', 'znkjoc'),
(3,'2021-01-18 11:02:35.0', 'https://zes.com/', 'znkjoc'),
(4,'2021-01-18 11:03:03.0', 'https://zes.com/', 'znkjoc'),  -- **
(5,'2021-01-18 11:04:15.0', 'https://zes.com/', 'znkjoc'),  -- YY

--
-- 3 records within 180s of the first one
--

(6,'2021-01-18 11:49:28.0', 'https://rararar.com/', 'znkjoc3gfth2c3m0t1klii'),
(7,'2021-01-18 11:49:48.0', 'https://rararar.com/', 'znkjoc3gfth2c3m0t1klii'), -- **
(8,'2021-01-18 11:50:13.0', 'https://rararar.com/', 'znkjoc3gfth2c3m0t1klii'), -- **

-- 1 lone record

(9,'2021-01-18 12:57:24.0', 'https://zendes.com/', 'znkjoc3gfth2c3m0t1klii'),  -- XX

(10,'2021-02-18 09:02:24.0', 'https://rar.com/', 'znkjoc3'), -- ZZ
(11,'2021-02-18 09:02:35.0', 'https://rar.com/', 'znkjoc3'), -- ZZ
(12,'2021-02-18 09:03:03.0', 'https://rar.com/', 'znkjoc3'), -- ZZ
(13,'2021-02-18 09:04:15.0', 'https://rar.com/', 'znkjoc3'); -- ZZ

-- -- XX - Added record > 1 minute from next or previous.
-- -- ** - Changed created_date to get groups within 180 seconds.
-- -- YY - Added record < 3 minutes from previous to give 4 records
-- -- ZZ - Added group of 4 records at the end.

It's always worth checking out for edge cases - single records at the beginning/end of your dataset + groups that you want to capture at the beginning and end also! I leave it to you to do more exhaustive testing!
I'll give the results first:
rn  st  sids                URL:                Session id: Session start time:            Session end time:
1   2   2,3,4,5             https://zes.com/    znkjoc      2021-01-18 11:02:24.00  2021-01-18 11:04:15.00
2   5   10,11,12,13         https://rar.com/    znkjoc3     2021-02-18 09:02:24.00  2021-02-18 09:04:15.00

There's a bonus - you've got the starts and ends of the multiple close-together sessions thrown in for free!
I do have one word of advice - you really shouldn't be using or gathering data as comma-separated lists - SQL wasn't designed for string manipulation and extracting meaningful information and knowledge from such lists is painful - better to have a single atomic datum in a single field - see 1st Normal Form!
I've left in the various "sub-fiddles" that I used to arrive at the final result - hopefully they'll help you to learn about window functions &c... My own preference for the results would be in this format (see the fiddle  - with one record/session - you can prune as you see fit):
sid created_date    st  min_cd  max_cd  f_ts    l_ts    c_ts_asc    c_ts_desc   o_url   c_sess_id
1   2021-01-18 10:30:24.00  1   2021-01-18 10:30:24.00  2021-01-18 10:30:24.00  1   1   1   1   https://zendes.com/ znkjoc3gfth2c3m0t1klii
2   2021-01-18 11:02:24.00  2   2021-01-18 11:02:24.00  2021-01-18 11:04:15.00  1   4   1   4   https://zes.com/    znkjoc
3   2021-01-18 11:02:35.00  2   2021-01-18 11:02:24.00  2021-01-18 11:04:15.00  2   3   2   3   https://zes.com/    znkjoc
4   2021-01-18 11:03:03.00  2   2021-01-18 11:02:24.00  2021-01-18 11:04:15.00  3   2   3   2   https://zes.com/    znkjoc
5   2021-01-18 11:04:15.00  2   2021-01-18 11:02:24.00  2021-01-18 11:04:15.00  4   1   4   1   https://zes.com/    znkjoc
6   2021-01-18 11:49:28.00  3   2021-01-18 11:49:28.00  2021-01-18 11:50:13.00  1   3   1   3   https://rararar.com/    znkjoc3gfth2c3m0t1klii
7   2021-01-18 11:49:48.00  3   2021-01-18 11:49:28.00  2021-01-18 11:50:13.00  2   2   2   2   https://rararar.com/    znkjoc3gfth2c3m0t1klii
8   2021-01-18 11:50:13.00  3   2021-01-18 11:49:28.00  2021-01-18 11:50:13.00  3   1   3   1   https://rararar.com/    znkjoc3gfth2c3m0t1klii
9   2021-01-18 12:57:24.00  4   2021-01-18 12:57:24.00  2021-01-18 12:57:24.00  1   1   1   1   https://zendes.com/ znkjoc3gfth2c3m0t1klii
10  2021-02-18 09:02:24.00  5   2021-02-18 09:02:24.00  2021-02-18 09:04:15.00  1   4   1   4   https://rar.com/    znkjoc3
11  2021-02-18 09:02:35.00  5   2021-02-18 09:02:24.00  2021-02-18 09:04:15.00  2   3   2   3   https://rar.com/    znkjoc3
12  2021-02-18 09:03:03.00  5   2021-02-18 09:02:24.00  2021-02-18 09:04:15.00  3   2   3   2   https://rar.com/    znkjoc3
13  2021-02-18 09:04:15.00  5   2021-02-18 09:02:24.00  2021-02-18 09:04:15.00  4   1   4   1   https://rar.com/    znkjoc3

Or another way might be (again, see the fiddle):
sid Session no. Start of sessions   End of sessions Session count   o_url   c_sess_id
2   2   2021-01-18 11:02:24.00  2021-01-18 11:04:15.00  4   https://zes.com/    znkjoc
10  5   2021-02-18 09:02:24.00  2021-02-18 09:04:15.00  4   https://rar.com/    znkjoc3

You have the starts and ends and the number of sessions... anyway, that's up to you. Take a look at this bit (easily missed):
MIN(created_date) OVER (PARTITION BY st ORDER BY created_date ASC) AS min_cd,
MAX(created_date) OVER (PARTITION BY st ORDER BY created_date DESC) AS max_cd,

This allows you to have an ASCending and a DESCedning list.
This means that you can then do this:
WHERE (v.f_ts + v.l_ts) >= 5

And the records where this  corresponds must be the ones of interest for the bunched sessions.
So now, the SQL, here it is (drum roll, trumpets sound in the distance...)! Go get a cup of coffee to read it - it's a monster (needs pruning - but that's an exercise for the OP and anybody else who's got this far):
SELECT
  ROW_NUMBER() OVER () AS rn,
  v.st,
  GROUP_CONCAT(v.sid SEPARATOR ',') as sids,
  o_url AS "URL:",
  c_sess_id AS "Session id:",
  min_cd AS "Session start time:",
  max_cd AS "Session end time:"
FROM
(
  SELECT
    sid,
    created_date, st,
    MIN(created_date) OVER (PARTITION BY st ORDER BY created_date ASC) AS min_cd,
    MAX(created_date) OVER (PARTITION BY st ORDER BY created_date DESC) AS max_cd,
    ROW_NUMBER() OVER (PARTITION BY st ORDER BY created_date ASC)  AS f_ts,
    ROW_NUMBER() OVER (PARTITION BY st ORDER BY created_date DESC) AS l_ts,
    COUNT(st) OVER (PARTITION BY st ORDER BY created_date ASC)  AS c_ts_asc,
    COUNT(st) OVER (PARTITION BY st ORDER BY created_date DESC) AS c_ts_desc,
    o_url,
    c_sess_id
  FROM
  (
    SELECT
      sid,
      created_date, YYY,
      SUM(testy) OVER (PARTITION BY testy ORDER BY created_date ASC) AS s,
      ROW_NUMBER()
        OVER (PARTITION BY testy ORDER BY created_date ASC) AS rn,
      SUM(testy) OVER y AS st,
      FIRST_VALUE(created_date) OVER (PARTITION BY testy ORDER BY created_date) AS fv,
      LAST_VALUE(created_date) OVER y AS lv,
      o_url,
      c_sess_id
    FROM
    (
      SELECT
        id AS sid,
        LAG(created_date, 1) OVER x AS lag_1,
        created_date,
        LEAD(created_date, 1) OVER x AS lead_1,

        UNIX_TIMESTAMP(LEAD(created_date, 1) OVER x) - UNIX_TIMESTAMP(created_date) AS XXX,

        UNIX_TIMESTAMP(created_date) - UNIX_TIMESTAMP(LAG(created_date, 1) OVER x) AS YYY,

        --
        -- The IMPORTANT one!
        --

        CASE
          WHEN (UNIX_TIMESTAMP(created_date) - UNIX_TIMESTAMP(LAG(created_date, 1) OVER x) > 180
          OR   UNIX_TIMESTAMP(created_date) - UNIX_TIMESTAMP(LAG(created_date, 1) OVER x) IS NULL)
          THEN 1
          ELSE 0
        END AS testy,

        FIRST_VALUE(created_date) OVER x AS f_val,

        UNIX_TIMESTAMP(created_date) - UNIX_TIMESTAMP(FIRST_VALUE(created_date) OVER x) AS c_f_diff,

        ABS(TIMESTAMPDIFF(MINUTE, created_date, LEAD(created_date, 1) OVER x)) AS min_diff,

        UNIX_TIMESTAMP(created_date) - UNIX_TIMESTAMP(LEAD(created_date, 1) OVER x) AS ut_d,

        origin_url AS o_url,
        client_session_id AS c_sess_id

      FROM test
      WINDOW x AS (PARTITION BY origin_url, client_session_id
                     ORDER BY created_date ASC, origin_url, client_session_id)
      ORDER BY created_date ASC
    ) AS t
    WINDOW y AS (ORDER BY created_date ASC ROWS BETWEEN UNBOUNDED PRECEDING
                                                    AND CURRENT ROW)
    ORDER BY created_date ASC
  ) AS u
  ORDER BY created_date ASC
) AS v
WHERE (v.f_ts + v.l_ts) >= 5
GROUP BY v.st, o_url, c_sess_id, min_cd, max_cd;


Deleting old rows when a new one is added and there are more than 100 rows

Your trigger is not specifying which row to delete and is only limiting it to deleting a single record. You are going to want to update the trigger to specify which record to delete and I would guess that you want to delete the oldest one.

https://dev.mysql.com/doc/refman/5.6/en/delete.html

  The LIMIT clause places a limit on the number of rows that can be deleted


What are the design alternatives when having multiple "type" tables

Please don't combine the various _type tables.  Your future self (and anyone that ends up writing queries against the tables) will thank you.

If you combine the tables, you give up the ability to have referential integrity constraints ensure that your tables have valid data.  Inevitably, someone will inadvertently insert a row that doesn't reference a valid row in your lookup table.  Then, when you to run a report, you'll find that you have customers in all 61 states with folks in interesting states like 'NA' and 'X'.
If you combine the tables, you make it much harder for future developers to write queries.

I would much rather write something like

SELECT p.product_name,
       pt.product_type_name,
       ct.color_name
  FROM product p
       JOIN product_type pt ON (p.product_id = pt.product_id)
       JOIN color_type   ct ON (p.color_id   = ct.color_id)

than

SELECT p.product_name,
       pt.label product_type_name,
       ct.label color_name
  FROM product p
       JOIN (SELECT *
               FROM labels
              WHERE context = 'PRODUCT_TYPE') pt ON (p.product_id = pt.id)
       JOIN (SELECT *
               FROM labels
              WHERE context = 'COLOR_TYPE') ct ON (p.color_id = ct.id)

If you have separate tables, the optimizer is much more likely to be able to make good decisions.  You'll inevitably have some lookup tables that have half a dozen rows and others that have hundreds of rows.  If you have separate tables, it's relatively easy for the optimizer to figure out which lookups are more restrictive and which are less restrictive.  If you throw all the _type tables together, the database will tend to have much less information to work with so it will be much less likely to pick the most efficient plans.

Beyond that, I completely second @BillThor's suggestions for how to create the various lookup tables.


Mongo error : Could not lock auth data update lock. on root user

You are actually using root and admin database by your query
'use root' or 'use admin'

use root will not change your user it will just change your database.

So to add user or role .you need to login by root.As you have mentioned in very first line actually you are login as "myadmin " on database "admin"


Multi-Column index not used for index-only scan, but partial index is

Tuple visibility is the problem.  It uses the bitmap index scan in the unvacuumed case because it thinks it will be faster, not because it is incapable of using the IOS.  Once I do a VACUUM test on your exact example, it starts using the index only scan on index i2, because now it thinks that the IOS will be faster.

As for why it doesn't use the bitmap scan for the partial index when the table is not vacuumed, that is a bit more complicated and is not about visibility. One of the benefits of bitmap scans (and in your case, the only benefit of them) is that it reads the table in physical order and visits each table block only once, regardless of the order of the index. PostgreSQL sees a perfect correlation between id and the order of the physical table rows, so for the index with id as a leading column (the partial index) it think this benefit does not really matter, as it will read the table in physical order anyway even with a regular index scan.  Since bitmap scans have slightly higher CPU cost, it won't do one when it offers no predicted IO advantage.  The correlation between the flag column and the physical table order is lower, and so it thinks bitmap index scans will get a benefit when using the index which has flag as its leading column, which happens to be the full index.  It thinks this benefit outweighs the CPU cost.  If the table was marked all-visible, then the benefits of an index-only-scan would tip the scales back again, but without vacuuming, that doesn't happen.

Now in the case of the index on (flag, id), the leading column is being tested for equality and the only rows selected will have the same value for flag.  So what really matters is the correlation with the next column in the index, id, not with the flag column.  But PostgreSQL isn't (apparently) clever enough to understand this. Now I am kind of curious how hard it would be to make it understand that.


How to create an index on CASE expression in Postgres

You need to add extra parentheses around the CASE expression:

CREATE UNIQUE INDEX test_index
  ON test ((CASE WHEN i=1 THEN j END)) ;

As the docs state in CREATE INDEX:

  The key field(s) for the index are specified as column names, or alternatively as expressions written in parentheses.

Consider also using a filtered index, which is equivalent in terms of functionality but would be using less space, as it will be storing the j values only for rows with i = 1 and not the (possibly millions) or the rest NULL values:

CREATE UNIQUE INDEX test_index_2
  ON test (j) WHERE i=1 ;


SQL Server Files in Secondary file group updates much slower from linked server

The problem was not on my database as I thought, but on the system's tempdb. I added some additional files that point to a different location to the tempdb.  This resolved the space problem.  Thanks to Denis for resolving the performance issue for me.


Use a scalar hashing function in a computed column - non-deterministic?

How do I convince SQL Server that the function is nondeterministic and make the function evaluate for each row, computing different hash values?

SQL Server marks the function nondeterministic, because it accesses data in system tables:

SELECT
    UserDataAccess = OBJECTPROPERTYEX(FN.ObjectID, 'UserDataAccess'),
    SystemDataAccess = OBJECTPROPERTYEX(FN.ObjectID, 'SystemDataAccess'),
    IsDeterministic = OBJECTPROPERTYEX(FN.ObjectID, 'IsDeterministic')
FROM (VALUES(OBJECT_ID(N'[dbo].[fn_get_hash_fields]', 'FN'))) AS FN (ObjectID);

Nevertheless, the function actually does return the same value for each row in the table, because nothing in the function's query depends on anything in the source row. SQL Server does execute the function for each row (something to be avoided, in general for performance reasons), but each execution returns the same result.

The function returns a string, which would need to be executed (with SELECT and FROM <table_name> added in the right places) to return a result. Even then, it would return a value for every row of the table, because there is nothing in the constructed SQL to link the generated text to a particular row.

More to the point, it is not even possible to execute dynamic SQL in the context of a function (unsafe hacks aside).

  Is there a way around this? Is there a better approach to store a hash in each row built from all the columnar values in that row?

This isn't as straightforward as one might think at first glance.

For example, given:

CREATE TABLE dbo.Test
(
    c1 integer PRIMARY KEY,
    c2 varchar(10) NOT NULL,
    c3 datetime NOT NULL
);
GO
INSERT dbo.Test
    (c1, c2, c3)
VALUES
    (1, 'ABCDE', GETDATE()),
    (2, 'FGHIJ', GETUTCDATE());

The following query returns the broad sort of thing needed:

SELECT
    T.c1,
    T.c2,
    T.c3,
    Chk = BINARY_CHECKSUM(*) -- or CHECKSUM
FROM dbo.Test AS T;

Unfortunately, the Chk column cannot be added as a computed column in the table:

Msg 1789, Level 16, State 1, Procedure ComputeChecksum, Line 5
Cannot use CHECKSUM(*) in a computed column, constraint, or default definition.

It cannot be used directly in a function either, for a variety of reasons depending on exactly how you try it, but one entertaining result is the CHECKSUM(*) becomes recursive (referencing itself) and execution terminates when the depth limit of 32 is reached.

A trigger implementation of an all-columns checksum/hash is probably possible, but would need to contain dynamic SQL (or some XML trickery) because CHECKSUM(*) would include the checksum/hash column itself. Aside from DML triggers, a DDL trigger would also be needed to populate the values when a new column is added. This might be an interesting exercise, but it is not trivial.

If the restrictions are relaxed so the checksum/hash does not need to be in the same table, more options become available. For example, a view:

CREATE VIEW dbo.TestChk
AS
SELECT T.*, Chk = BINARY_CHECKSUM(*)
FROM dbo.Test AS T;

Though this looks like it would dynamically adapt to changes in the base table, in fact it does not. You will get wrong results if a column is added to the table, for example. When the table schema is changed, the view would have to be refreshed:

-- New column
ALTER TABLE dbo.Test
ADD c4 decimal(5,2) NOT NULL DEFAULT 0.0;

-- Refresh required
EXECUTE sys.sp_refreshsqlmodule
    @name = N'dbo.TestChk',
    @namespace = DEFAULT;

Note the view also cannot be materialized, because it cannot be schema-bound:

Msg 1054, Level 15, State 7, Procedure TestChk, Line 4
Syntax '*' is not allowed in schema-bound objects.

I haven't tried every possible implementation option (including a SQLCLR function), because even if it can be made to work as you want, it is very likely to be horribly inefficient, and not the 'best' solution to the problem you really need to solve.

We would need to know the reasons for wanting this column, as it is quite likely there is a better way to achieve the end result you need via a different implementation option, possibly a standard feature in the product. I would suggest asking a new question for that.


You can do it with a (derived) table that holds these 3 values and then LEFT joining it to the findings:

SELECT
    d.host_id,
    CASE WHEN f.host_id IS NOT NULL THEN f.status ELSE 'No Data' END AS status
FROM
    ( SELECT 1 AS host_id UNION ALL
      SELECT 2 UNION ALL
      SELECT 3
    ) AS d
  LEFT JOIN
    findings AS f
      ON  f.finding_id = 1
      AND f.host_id = d.host_id ;

If the findings.status column is not nullable, you can replace the CASE ... END with the simpler:

    COALESCE(f.status, 'No data') AS status

If you have a hosts table (and a large number of IDs to check), the derived table can also be simplified:

...
FROM
    ( SELECT host_id
      FROM hosts
      WHERE host_id IN (1,2,3)
    ) AS d
  LEFT JOIN
...


Best order of execution of mysql maintenance tools

It depends on what storage engine you use. The default storage engine has been InnoDB since MySQL 5.5 (circa 2010), so I will assume that's what you use, since you did not specify.

OPTIMIZE TABLE <name> does the same as ALTER TABLE <name> FORCE or ALTER TABLE <name> ENGINE=InnoDB. This should be done with care, because it rebuilds the whole table, and that locks the table and takes a long time, proportional to the size of the table and number of indexes. Because it copies data pages to a new table, and rebuilds indexes, this accomplishes defragmentation. It also accomplishes the same as ANALYZE TABLE <name>. I almost never run OPTIMIZE TABLE. I prefer to use pt-online-schema-change to defragment InnoDB tables, because that does its job without blocking clients from reading or writing the table.

ANALYZE TABLE <name> accomplishes a resampling of pages to update table and index statistics. You can do this anytime. It is quick regardless of the size of the table, and it does not lock the table. At my workplace, I developed a scheduled task to run ANALYZE TABLE on all the tables about every ten days (it runs every day, and analyzes 1 out of 10 tables each time).

CHECK TABLE <name> validates the pages of the table for corruption. If a corrupt table or index is detected, this may halt the MySQL Server. Refer to documentation for details. FWIW, I don't think I've ever run CHECK TABLE on an InnoDB table.

REPAIR TABLE <name> does nothing for InnoDB. It works on MyISAM, ARCHIVE, and CSV tables. I have definitely never run this on any InnoDB table.

I can't think of any reason you would run all of these statements on the same table during the same session, regardless of the order you run them in.


Mysql - Grouping and getting the Max(col1) row variables

SELECT id,col1,col2,col3, riskLvel
FROM table1
where risklevel IN ( SELECT MAX(riskLevel)
                     FROM table1
                     GROUP BY col1 )

IF This query is not correct then you can try this query

SELECT id,col1,col2,col3, riskLvel
    FROM table1
    where col1 IN ( SELECT MAX(col1)
                         FROM table1
                         GROUP BY col1 )


No, it is not very safe if you get untrusted input into the database (i.e. if it is on a web server or such). You basically hand the user a really dangerous tool if you have any SQL injection vulnerability. I would not run that risk on public-facing servers.

Internal servers may be another thing, but internal attacks are often overlooked, so probably "we" should protect against internal attack also.

Also, if setup correctly (see https://msdn.microsoft.com/en-us//library/ms175046.aspx ) you need a proxy account and all kinds of SQL Server pereparations to use it.

I would always suggest using SSIS or SQL Server Agent to perform OS level tasks.


Will Postgresql statements execute synchronously if autocommit=true?

With autocommit enabled each statement forms a separate transaction. In a single session (connection) only one transaction can be active at a time. However, transactions in multiple concurrent sessions can, and will run simultaneously. In other words, as long as you issue your insert statements using the same database connection they will execute serially.

Whether you do that in a single application thread or not is irrelevant, since it's possible to open multiple database connections in a single thread, as it is possible to reuse the same connection between multiple threads (with proper synchronization, of course).


Restore InnoDB tables / database using only data files

Unfortunately, there is really not an easy way to get this table back. Although the data is likely still in the ibdata1 file, only specialized tools can recover it. If this data is business critical, some consulting companies such as Percona can help you to recover it, but that will be quite expensive (thousands of dollars at least), and not all of the data may be recoverable.

If this data is important to you enough to spend the money or time recovering it, save a copy of the ibdata1 file immediately as the disk space will be more likely to be re-used the longer the server is running.


How to calculate end_date from start_date and duration in SQL Server Query

Iterative solution:

WITH cte AS ( SELECT id,
                     DATEADD(DAY, CASE WHEN DATEPART(WEEKDAY, start_date) > 5
                                       THEN 9 - DATEPART(WEEKDAY, start_date)
                                       ELSE 1
                                       END, start_date) cur_date, duration-1 reminder
              FROM test
              UNION ALL
              SELECT id,
                     DATEADD(DAY, CASE WHEN DATEPART(WEEKDAY, cur_date) > 5
                                       THEN 3
                                       ELSE 1
                                       END, cur_date),
                     reminder - 1
              FROM cte
              WHERE reminder > 0 )
UPDATE test
SET test.end_date = cte.cur_date
FROM cte
WHERE test.id = cte.id
  AND cte.reminder = 0;

fiddle

PD. It is optimal hardly... for large duration values you may int. divide it by 7 and add according weeks (5 days per each) then iterate by int. reminder only.


troubleshooting login failure when AD group seems to be fine

18456 (login failed) needs the STATE value to know why it failed. Look in the error log. – Sean Gallardy

Are you sure that if you added yourself tot he group that this sql server is connecting to a domain controller that has the updated info? If you have multiple domain servers it may take some time to have that info replicate a across to all servers. – Jeff A

This is the most comprehensive guide I have seen so far:

Troubleshooting Error 18456 by Aaron Bertrand

You need the state first to use that document. – SqlWorldWide

After you created the AD group and added yourself to it, did you logoff/login? AD Group permissions are only enumerated at login (e.g. the Kerberos Access Token). – John Eisbrener

Community Wiki answer compiled from question comments. Community wiki posts have been donated to the community in hopes that others will edit them to keep them up to date, to add useful information, and generally improve their quality.


Is there a SQL Server implementation of the Longest Common Substring problem?

This can be done rather easily as a SQLCLR User-Defined Aggregate (UDA). An aggregate operates over a set of rows, so you would be able to operate over all rows or just a subset, based on a WHERE condition and optional GROUP BY (if wanting to operate over separate sets of rows).

BUT, whether or not you should do this depends on what you are planning on doing with the result. If this is a one-off project to do some research that won't be repeated, then it is probably best to just make a small Console App to read in the rows and process accordingly.

However, if you do have some need to use the returned value within a database-centered process, then SQLCLR should be fine (assuming you follow the two recommendations mentioned under "The following tricks can be used to reduce the memory usage of an implementation" in the Pseudocode section. You will just need to find a "creative" way of dealing with the situation of having multiple results for what is considered the longest common substring (i.e. if 2 or more common substrings tie for "first place"). Using the example from the Wikipedia page (which shows 2 matches for the 2 strings):

ABAB
BABA

Returns both:

BAB
ABA

Perhaps returning an XML document of matches since that is parsable and can contain any string (when properly escaped).

UPDATE (updated, and updated again)

The .NET C# source code for this can be found on Pastebin.com at:

SQLCLR UDA for Longest Common Substring - Source Code

And for anyone that wants to play with this UDA without compiling it, an installation T-SQL script (no external DLL) can be found on Pastebin.com at:

SQLCLR UDA for Longest Common Substring - Installer

The UDA should be fairly memory-efficient as it only stores substrings that match the current string and all previously encountered strings. As a new row calls the UDA, any substrings that are not found in the new string are removed from the collection.

It is also CPU-efficient in that, if at any point the number of "common" substrings goes to zero, it sets a flag indicating that no substrings are even possible and short-cicruits all future executions to simply exit upon being called. This happens immediately if an empty string is encountered. In any of these cases, an empty XML document (i.e. root element only) is returned. The meaning of the empty document is equivalent to an empty string, since the only thing the input strings have in common is that they were non-NULL strings.

NULL, in my interpretation, is ignored and does not indicate no possible matches like an empty string does.

A NULL is returned in the following two cases:

All inputs are NULL
Only a single non-NULL row was in the set and hence there was no other string to compare to, hence nothing to be considered "common".

I added a second input parameter which controls whether or not the return is just the longest common substrings or all common substrings. In the case of returning all, an attribute is added to each "item" to indicate whether or not it is one of the "longest" substrings:

SELECT dbo.LongestCommonSubstring(tab.col, 0) AS [Test1a]
FROM   (VALUES (N'ABAB'), (N'BABA')) tab(col);

Returns:

<Items Merged="False">
  <Item>ABA</Item>
  <Item>BAB</Item>
</Items>

And

SELECT dbo.LongestCommonSubstring(tab.col, 1) AS [Test1b]
FROM   (VALUES (N'ABAB'), (N'BABA')) tab(col);

Returns:

<Items Merged="False">
  <Item IsLongest="True">ABA</Item>
  <Item IsLongest="True">BAB</Item>
  <Item IsLongest="False">AB</Item>
  <Item IsLongest="False">BA</Item>
  <Item IsLongest="False">A</Item>
  <Item IsLongest="False">B</Item>
</Items>

Also, the comparisons are now case-InSensitive to match the typical Collation.

Below are 16 more test cases that check functionality only, not performance. I will post additional tests later that operate over many rows of much longer strings. I intentionally left out Combining Characters and Supplementary Characters, for now, as they are bit a more complicated.

SELECT dbo.LongestCommonSubstring(tab.col, 1) AS [Test2]
FROM   (VALUES (N'ABAB'), (N'BABA'), (N'2BAB5')) tab(col);
-- <Items><Item>BAB</Item></Items>

SELECT dbo.LongestCommonSubstring(tab.col, 0) AS [Test3]
FROM   (VALUES (N'ABAB'), (N'BABA'), (NULL), (N'2BAB5')) tab(col);
-- <Items><Item>BAB</Item></Items>

SELECT dbo.LongestCommonSubstring(tab.col, 0) AS [Test4]
FROM   (VALUES (NULL), (NULL), (NULL)) tab(col);
-- NULL

SELECT dbo.LongestCommonSubstring(tab.col, 0) AS [Test5]
FROM   (VALUES (N'ABAB'), (N'BABA'), (N''), (N'2BAB5')) tab(col);
-- <Items />

SELECT dbo.LongestCommonSubstring(tab.col, 0) AS [Test6]
FROM   (VALUES (N'ABAB'), (N'BABA'), (N'L'), (N'2BAB5')) tab(col);
-- <Items />

SELECT dbo.LongestCommonSubstring(tab.col, 0) AS [Test7]
FROM   (VALUES (N'ABAB')) tab(col);
-- NULL

SELECT dbo.LongestCommonSubstring(tab.col, 0) AS [Test8a-DuplicatesAcross2Rows]
FROM   (VALUES (N'ABAB'), (N'ABAB')) tab(col);
-- <Items><Item>ABAB</Item></Items>

SELECT dbo.LongestCommonSubstring(tab.col, 0) AS [Test8b-DuplicatesAcross3Rows]
FROM   (VALUES (N'ABAB'), (N'ABAB'), (N'ABAB')) tab(col);
-- <Items><Item>ABAB</Item></Items>

SELECT dbo.LongestCommonSubstring(tab.col, 0) AS [Test8c-DuplicatesAcross4Rows]
FROM   (VALUES (N'ABAB'), (N'ABAB'), (N'ABAB'), (N'ABAB')) tab(col);
-- <Items Merged="False"><Item>ABAB</Item></Items>

SELECT dbo.LongestCommonSubstring(tab.col, 0) AS [Test9-DuplicatesWithinOneString]
FROM   (VALUES (N'ABAB'), (N'zABABh2348923ABABf')) tab(col);
-- <Items Merged="False"><Item>ABAB</Item></Items>

SELECT dbo.LongestCommonSubstring(tab.col, 0) AS [Test10-XmlEncodableCharacters]
FROM   (VALUES (N'ABA&B'), (N'zABA&Bh2348923ABA&Bf')) tab(col);
-- <Items Merged="False"><Item>ABA&amp;B</Item></Items>

SELECT dbo.LongestCommonSubstring(tab.col, 0) AS [Test11a-FinalMatchesShorterThanInitialSet]
FROM   (VALUES (N'ABCDq1234g'), (N'1234qABCDg'), (N'uiyuiuy1234qBCDg'), (N'512tttrtrtBCDdfdfgdg')) tab(col);
-- <Items Merged="False"><Item>BCD</Item></Items>

SELECT dbo.LongestCommonSubstring(tab.col, 0) AS [Test11b-FinalMatchesShorterThanInitialSet]
FROM   (VALUES (N'BCDq1234g'), (N'1234qABCDg'), (N'uiyuiuy1234qBCDg'), (N'512tttrtrtBCDdfdfgdg')) tab(col);
-- <Items Merged="False"><Item>BCD</Item></Items>

SELECT dbo.LongestCommonSubstring(tab.col, 0) AS [Test11c-FinalMatchesShorterThanInitialSet]
FROM   (VALUES (N'ABCDq1234g'), (N'1234qABCDg'), (N'uiyuiuy1234qBCDg'), (N'5123tttrtrtBCDdfdfgdg')) tab(col);
-- <Items Merged="False"><Item>BCD</Item><Item>123</Item></Items>

SELECT dbo.LongestCommonSubstring(tab.col, 0) AS [Test11d-FinalMatchesShorterThanInitialSet]
FROM   (VALUES (N'BCDq1234g'), (N'1234qABCDg'), (N'uiyuiuy1234qBCDg'), (N'5123tttrtrtBCDdfdfgdg')) tab(col);
-- <Items Merged="False"><Item>BCD</Item><Item>123</Item></Items>

SELECT dbo.LongestCommonSubstring(tab.col, 0) AS [Test11e-FinalMatchesShorterThanInitialSet]
FROM   (VALUES (N'BCDq1234g'), (N'1234qABCDg'), (N'uiyuiuy1234qBCDg'), (N'123tttrtrtBCDdfdfgdg')) tab(col);
-- <Items Merged="False"><Item>BCD</Item><Item>123</Item></Items>

SELECT dbo.LongestCommonSubstring(tab.col, 1) AS [Test12-CaseInSensivity]
FROM   (VALUES (N'AbAB'), (N'BAbA')) tab(col);
/*
<Items Merged="False">
  <Item IsLongest="True">AbA</Item>
  <Item IsLongest="True">bAB</Item>
  <Item IsLongest="False">Ab</Item>
  <Item IsLongest="False">bA</Item>
  <Item IsLongest="False">A</Item>
  <Item IsLongest="False">b</Item>
</Items>
*/

Final Update (hopefully)

I made a couple of minor changes to the test code provided in @MisterMagoo's answer so that it would: a) return ties for "longest" common substring, and b) include the last character in each string as part of the search. I also changed, slightly, how the initial test rows are gathered such that there would be just over 1 million rows, and re-ran the tests. The results were that the T-SQL version took 1 minute and 13 seconds while the SQLCLR UDA took only 12 seconds (and even has the option of returning all common substrings, not just the longest).

I then modified the test data to include another common substring of the same length as the current winner (7 characters), a shorter but still common substring of 4 characters, and 3 random characters. This increased the max test string size from 32 characters to 46 characters. Running the test again (same code), the T-SQL version had to be killed at 23 minutes and had only tested the first 3 lengths: 27, 26, and 25. The SQLCLR UDA returned in about 1 minute and 10 seconds.

Is it time to celebrate? Has SQLCLR saved the day? Hold on..

On a hunch, I decided to see if the optimization I added to the SQLCLR version would help the T-SQL, namely:

Grab the shortest two strings (shortest would have the fewest number of possible substrings and would be the longest possible match anyway).
Extract all possible common substrings from the two short strings (any substrings that are "common" across all rows necessarily must be in the set derived from just these two strings, and no new substrings from other rows can be introduced as they wouldn't be "common").
Starting with the longest common substring, test to see if it is found in all rows. I used IF (NOT EXISTS (WHERE CHARINDEX(substring, test_row) > 0)) because the EXISTS clause will exit on the first row that returns a 0 (meaning substring not present) and hence not need to test all rows. This part is accomplished with a CURSOR (shh, don't tell anyone) because it allows for starting at the top of the list and just picking each new row off without needing to re-scan the list each time to find the next entry.
Once the first substring is found, save its length in a variable, and save the substring itself into a table variable.
Keep testing, but only for strings that are the same length as the first common substring found. as soon as the length of the next substring to search for is less than the length of the first substring to match, exit the loop and clean up the cursor.

All of this cursor stuff must make it painfully slow, right? Well, keeping in mind that the prior T-SQL version would have taken several hours to finish, and the SQLCLR UDA took 1 minute and 10 seconds, you might guess this updated version would take, what? A few minutes? 10 minutes? More? After all, just mentioning "cursor" is an automatic 5 minute hit, right? The actual time for the modified version:

22 seconds!!!

Of course, that is largely due to the substrings being on the longer side (compared to the size of the strings) and so the loop was able to exit earlier than if the longest common substring was only 3 or 4 characters long (i.e. it had less to test, but that is still a valid case and is not cheating). Also, one benefit of working in T-SQL is that you can test the entire set against a single value, whereas SQLCLR only has the current row and cannot see the entire set, hence the SQLCLR cannot short-circuit upon finding the longest common substring (because it won't know what is "common" in the first place until it has been executed across all rows).

A final change was to allow the new T-SQL version to return all "common" substrings, not just the longest ones, while indicating which ones were the longest in a second column of datatype BIT. When returning all substrings, mirroring the functionality of the SQLCLR UDA (even when the UDA only returns the longest common substrings, it still has the full list of all common substrings stored since it, again, has no ability to short-circuit), the T-SQL version returns in 2 minutes and 41 seconds.

So, there are conditions were the T-SQL can be faster, even at 1.2 million rows. But the performance is far more stable for the SQLCLR version, and definitely faster when you want all common substrings.

The test script, containing all 3 tests along with their results, can be found on Pastebin at:

SQLCLR UDA for Longest Common Substring - Testing

P.S. Even though the test was done over 1.2 million rows, the longest string tested was 46 characters. I am not sure how the performance of either approach would be affected by operating over strings that are much longer. That testing will have to wait until there is time to do it ;-).


RapidMiner Not Connecting To SQL Server Database Connection

Check if TCP-IP is enabled with SQL Server Configuration Manager. Run SQL Server Configuration Manager for different versions of SQL.

Then Edit TCP-IP and go to IPAll and set TCP-IP port to 1433 and Dynamic port to 0, then restart SQL Server service.


MYSql Full Text Boolean search and relevancy score improvements

The problem was:
ft_min_word_len is only for MyIsam and the correct variable is innodb_ft_min_token_size


Our solution: Set the variable optimizer_search_depth to zero.
The same query now needs 0.3 instead of 13 seconds.

To test it, call before your statement:

set SESSION optimizer_search_depth=0;

To make it permanent put it in the my.conf file and restart the db.


Are there any guidelines for setting innodb_stats_persistent_sample_pages in MySQL?

It's a crap shoot.

Too high --> takes too long; and not necessarily better stats
Too low --> inaccurate stats
Percentage --> 1% is close to hitting every block (for the typical table that has about 100 rows per block).
As table grows, InnoDB allegedly re-ANALYZEs after 10% growth, so no manual action needed.
Most tables have decent stats from 20.
Tables with uneven distribution won't benefit from changing the '20', at least not until some decent "histogram" stats are implemented.  Then, Optimization becomes much more complex, and potentially slower.

Bottom line:  Leave it at 20.  Keep the slowlog on.  Tackle the worst entry in the slowlog, with an eye for solutions that don't depend on the '20'.


Some important factors to consider for backup are:

how annoyed the forum users would be if X days of activity disappeared
the probability of that happening
how long will it take to recover the data if it's backed up
how much does backup interrupt the normal operation of the site

As for, "Am I making sure my data is protected enough ...?", I'll ask a question back. What Recovery Point Objective (RPO) and Recovery Time Objective (RTO) are you aiming for?

Your current backup schedule has a recovery point of

for the current month, to the nearest weekly backup
for the last three months, to the nearest 1/2 monthly backup
for the "life-time of the site", to the nearest monthly backup

I'll suggest that you add daily backups to your rotation. If space is a concern, look into differential backups that only backup the data created that day. If space is a concern and RTO is not of paramount importance, you can reduce the size of backup files by compressing them. Backups of a DB containing primarily text might compress well.

By the way, if your forum keeps growing at an average of 200 MB/day (and growth is not accelerating), that's 6 GB/month. If you keep doing full backups on the schedule you outlined and backups are more-or-less the same size as the forum DB and they aren't compressed, by the end of the year you will have:

January monthly backup, 2 GB
February monthly backup, 8 GB
March monthly backup, 14 GB
April monthly backup, 20 GB
...
September monthly backup, 50 GB

September 1/2 monthly backup, 53 GB

October monthly backup, 56 GB

October 1/2 monthly backup, 59 GB

November monthly backup, 62 GB

November 1/2 monthly backup, 65 GB

December monthly backup, 68 GB

Week 1 backup, 69.5 GB
Week 2 (December 1/2 monthly) backup, 71 GB
Week 3 backup, 72.5 GB
Week 4 backup, 74 GB

approximately 1 TB of stored backups. And backups will be taking 20-40 times longer than they are now. An interesting followup question would be, "Are you doing cold or hot backups?"


Issue 1 - How to do the ALTER without blocking everything too badly.  Answer:  use pt-online-schema-change.

Issue 2 - Storage for char-like datatypes.

In the past, CHAR(n) always reserved n*c bytes, where c is the maximum size based on the CHARACTER SET: 1 for latin1, 3 for utf8, 4 for utf8mb4.  This was sometimes handy for Engine=MyISAM with FIXED sized rows.

In certain ROW_FORMATs of InnoDB in new enough versions (sorry, I don't have the specifics), CHAR(n) will occupy between n and n*c bytes, depending on the actual characters needed.  With InnoDB, there is no concept of fixed sized rows.

A complex SELECT that needs a temp table (say for ORDER BY), will try to use a MEMORY table for such.  In that case, the length of CHAR(n) and VARCHAR(n) will always be n*c.  This sometimes leads to an inefficiency.  Or it might use MyISAM for the tmp table.  Version 8.0 will be moving toward using InnoDB for tmp tables, so this paragraph will eventually be moot.

Converting CHAR to VARCHAR and/or converting the column from one CHARACTER SET to another requires a heavy-duty ALTER (not INPLACE).  See https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html  That link is for 5.6; there have been changes; pick the page for your version.

VARCHAR has 1 or 2 bytes for the length, plus only the number of bytes needed for the actual characters.  So it is 1+n*c for short max len.


It seems that you misconfig the mysqld and that the service is reserving more memory than the server actually has (VIRT is 37G, but the server has 32G). Please have a look at the doc. I don't know your my.cnf but you should start by setting the innodb_buffer_pool_size to 24G and restart the service.


Why does my columnstore index not fill the entire rowgroup?

There is an Extended Event that fires when a columnstore group is being 'cut', I think the event is column_store_index_build_process_segment. This event will have a 'trim' reason, and you should look for two possible trim causes:

memory (unlikely for your case), if the index build does not have enough memory to build a segment.
dictionary size (the likely cause), this happens if the dictionaries used to encode the data in a column segment reach the maximum size (16Mb).

Of course, to capture this event you need to set up an XE session during the index build (the linked article shows how).

You could also look at the post-build artifacts, specifically at the dictionaries, and see if the secondary dictionaries associated with the small segment are already at full size (16Mb). This would indicate the likely cause of trim to be full dictionary.


If this is a job consisting of a Transact-SQL step, you can specify a SET TRANSACTION ISOLATION LEVEL statement at the beginning of your Transact-SQL script:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

…  /* the rest of your script */


How do I truncate performance_schema tables on a read replica?

Connecting to the Slave as root (or other SUPER user) bypasses readonly, thereby allowing you to do that 'truncate'.

(Caveat: this Answer is theoretical; I have not tested it.)


How to store multiple data in a cell inside MySQL?

Unless each date is a specific property (start date, end date, due date, date created, ...) then storing them as columns is probably bad form.

It is likely that you would be better storing the dates in separate rows like so:

ItemDates
===========
ID     (PK)
ItemId
Date

so:

ID   Item   Date
 1   1      2018-05-19
 2   1      2018-04-09
 3   1      2018-08-09
 4   2      2016-01-30
 5   2      2017-05-04
 6   2      2016-06-07
 7   2      2020-05-17
 8   2      2021-06-23
 9   3      2001-05-06
10   4      2018-11-05
11   4      2013-06-09

Or if the dates will be unique per item, you can do without the surrogate key (ID) and declare ItemId,Date to be the primary key.

If you are not careful this could turn into a property bag (the Entity/Attribute/Value or EAV pattern, which is often considered an anti-pattern) though. For more specific advice for your use case, you should update the question with a bit more information about what the date values represent in the system that you are modelling.


Remote connection to DB instance from fresh formatted system?

I checked this out and the latest available standalone for SQL Server Management Studio is a 2008 version here.

Thanks to @AaronBertand, the process of obtaining the SQL Server Management Studio 2014 involves clicking the download button for the full SQL Server Express 2014 and then you will be offered a choice of only downloading the Studio (see here - with thanks again to Aaron Bertrand). Detailled instructions available here.

If you just want a command line client, then check out this post.


Upgrading SQL Server 2019 from CTP to RTM version

I cannot find anything specific to SQL Server 2019, but I was able to find unofficial references about previous versions of SQL Server not supporting CTP to RTM upgrades.  Additionally, the reference Bahtiyar posted in the comments also shows that upgrades from previous versions of 2019 are not included.  Once the RTM is available, the documentation for the CTP builds is gone, so I'm not sure if this limitation is included in the documentation of the pre-RTM releases.

This makes sense from the standpoint that the preview and release candidate versions are not licensed, supported versions.  Supporting upgrades from these versions would require Microsoft to spend a lot of additional resources in testing those upgrades.  Additionally, this might encourage customers to put a production workload on a CTP or release candidate build thinking they can just upgrade to RTM, so by not supporting that upgrade, they are discouraging customers from putting production workloads on unsupported versions.


Isolation level serializable not working as expected?

Your understanding is fundamentally correct. What you didn't realize is that there can be false positive results when with the SERIALIZABLE isolation level.

The documentation says:

  While PostgreSQL's Serializable transaction isolation level only allows concurrent transactions to commit if it can prove there is a serial order of execution that would produce the same effect, it doesn't always prevent errors from being raised that would not occur in true serial execution.

In this particular case, I would assume that the problem is that you don't have an index on c1 and c2 or PostgreSQL chooses to do a sequential scan anyway. As you can read further down on the same page:

  A sequential scan will always necessitate a relation-level predicate lock. This can result in an increased rate of serialization failures.


How to join 3 tables together given 2 many-to-many relationships tables in between?

Join conference to person_conference, selecting the person key from person_conference. Do the same for publication and person_publication. Make the two queries sub queries in a from clause and join the two on the person key.

Edit:

You would want to do something like this:

SELECT
    p.person_id,
    p.name,
    a.conference,
    b.publication
FROM
    person AS p
    LEFT JOIN (SELECT
                   pc.person_id,
                   c.conference
               FROM
                   person_conference AS pc
                   INNER JOIN conference AS c
                     ON pc.conference_id = c.conference_id) AS a
      ON p.person_id = a.person_id
    LEFT JOIN (SELECT
                   pp.person_id,
                   ppp.publication
               FROM
                   person_publication AS pp
                   INNER JOIN publication AS ppp
                     ON pp.publication_id = ppp.publication_id) AS b
      ON p.person_id = b.person_id

The inner queries are called sub queries. These will act as a "table" (they are not the same) that you can then query against. If you intend to use sub-queries as a "table" though, you do need to alias them using the AS key-word.

EDIT:

I have learned a lot since 2014 and the query above shows that. I am leaving it as a reference for the answer that was given but the following would be a better query:

SELECT per.person_id,
    per.name,
    conf.conference,
    pub.publication
FROM person AS per
    LEFT JOIN person_conference AS pconf
        ON per.person_id = pconf.person_id
    LEFT JOIN conference AS conf
        ON pconf.conference_id = conf.conference_id
    LEFT JOIN person_publication AS ppub
        ON per.person_id = ppub.person_id
    LEFT JOIN publication AS pub
        ON ppub.publication_id = pub.publication_id


How i can create backup script from diagram in SQL Server?

I write this query and this work.

USE DestinationDatabase

DELETE  sysDiagrams
WHERE   name IN ( SELECT    name
              FROM      SourceDatabase.dbo.sysDiagrams )

SET IDENTITY_INSERT sysDiagrams ON

INSERT  sysDiagrams
    ( name ,
      principal_id ,
      diagram_id ,
      version ,
      definition
    )
    SELECT  name ,
            principal_id ,
            diagram_id ,
            version ,
            definition
    FROM    SourceDatabase.dbo.sysDiagrams

SET IDENTITY_INSERT sysDiagrams OFF


Is there a performance difference in committing and rolling back a read-only transaction?

Having run this through a debugging session (to refresh my failing memory):

Rollback does more checks than a commit, but it shouldn't result in additional work or have a noticeable affect on performance in the situation you describe.
The read-write transaction doesn't truly begin unless and until a data modification is made.

You can see much of this using DMVs, for example:

-- Temporary procedure to show the state of the transaction
CREATE PROCEDURE #TranState
    @Comment varchar(100)
AS
BEGIN
    SELECT
        @Comment AS Comment,
        DTCT.transaction_id,
        database_name =
            CASE DTDT.database_id
                WHEN 32767 THEN N'resource'
                ELSE DB_NAME(DTDT.database_id)
            END,
        tran_begin_time = DTDT.database_transaction_begin_time,
        tran_type =
            CASE DTDT.database_transaction_type
                WHEN 1 THEN 'read/write'
                WHEN 2 THEN 'read only'
                WHEN 3 THEN 'system'
            END,
        tran_state =
            CASE DTDT.database_transaction_state
                WHEN 1 THEN 'The transaction has not been initialized.'
                WHEN 3 THEN 'The transaction has been initialized but has not generated any log records.'
                WHEN 4 THEN 'The transaction has generated log records.'
                WHEN 5 THEN ' The transaction has been prepared.'
                WHEN 10 THEN 'The transaction has been committed.'
                WHEN 11 THEN 'The transaction has been rolled back.'
                WHEN 12 THEN 'The transaction is being committed. In this state the log record is being generated, but it has not been materialized or persisted.'
            END
    FROM sys.dm_tran_current_transaction AS DTCT
    JOIN sys.dm_tran_database_transactions AS DTDT
        ON DTDT.transaction_id = DTCT.transaction_id;
END;

Testing on AdventureWorks:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN TRANSACTION;

EXECUTE dbo.#TranState @Comment = 'After Begin Tran';

SELECT TOP (1)
    P.Name
FROM Production.Product AS P
ORDER BY
    P.Name;

EXECUTE dbo.#TranState @Comment = 'After Select';

UPDATE Production.Product
SET Name = N'New Blade'
WHERE Name = N'Blade';

EXECUTE dbo.#TranState @Comment = 'After Update';

-- Or Commit
ROLLBACK TRANSACTION;

EXECUTE dbo.#TranState @Comment = 'After Tran';

Output:

From a purely practical point of view (as Aaron noted in a comment), it is probably safer to issue a rollback to guarantee no changes are made, in case the code is modified in future. So, it's all about intent: no changes = rollback.

In passing, REPEATABLE READ is an unusual isolation level to choose; it does not always work how people would intuitively expect. Depending on your requirements, you might find SNAPSHOT isolation is a better fit.


...tried with Trigram, but the table name and column(more than 50 columns) are dynamic...

There is no particular problem with using the trigram technique I wrote about in Trigram Wildcard String Search in SQL Server with multiple columns, dynamic SQL, or 200 million records.
The comments on that article show people using the basic technique with multiple columns, much larger tables, and even with encryption. The modifications needed are normally quite straightforward, assuming the person making the changes understands the basic underlying implementation shown in my article.
Note though that trigram search requires at least a three-character substring to search for (as the name suggests). If you really need to perform two-character searches (as in the question example), more modifications would be needed, and you would need to carefully assess the costs and benefits with your data. Without further information, it would seem that a two-character match on hundreds of millions of rows would produce a great number of matches.
If n-gram search is not suitable for you, and you can tolerate some latency, an external solution like Elasticsearch might be worth looking into.
Example
The code below shows one way to extend the basic trigram search to work with three string columns in the same table. For brevity, it does not implement the trigger logic shown in my article to keep the trigrams synchronized with the underlying data. If this is required, the modifications needed follow much the same pattern.
CREATE TABLE dbo.Example
(
    id integer IDENTITY NOT NULL,
    string1 varchar(10) NOT NULL,
    string2 varchar(20) NOT NULL,
    string3 varchar(30) NOT NULL,

    CONSTRAINT [PK dbo.Example (id)]
        PRIMARY KEY CLUSTERED (id)
);
GO
-- 1 million rows
INSERT dbo.Example
    WITH (TABLOCKX)
(
    string1,
    string2,
    string3
)
SELECT TOP (1 * 1000 * 1000)
    -- 10 characters numeric
    REPLACE(STR(RAND(CHECKSUM(NEWID())) * 1e10, 10), SPACE(1), '0'),
    -- 10 characters numeric plus 10 characters [0-9A-F]
    REPLACE(STR(RAND(CHECKSUM(NEWID())) * 1e10, 10), SPACE(1), '0') + RIGHT(NEWID(), 10),
    -- 30 characters [0-9A-F]
    RIGHT(NEWID(), 10) + RIGHT(NEWID(), 10) + RIGHT(NEWID(), 10)
FROM master.dbo.spt_values AS SV1
CROSS JOIN master.dbo.spt_values AS SV2
OPTION (MAXDOP 1);
GO
--- Generate trigrams from a string
CREATE FUNCTION dbo.GenerateTrigrams
(
    @string varchar(255)
)
RETURNS table
WITH SCHEMABINDING
AS RETURN
    WITH
        N16 AS
        (
            SELECT V.v
            FROM
            (
                VALUES
                    (0),(0),(0),(0),(0),(0),(0),(0),
                    (0),(0),(0),(0),(0),(0),(0),(0)
            ) AS V (v)),
        -- Numbers table (256)
        Nums AS
        (
            SELECT n = ROW_NUMBER() OVER (ORDER BY A.v)
            FROM N16 AS A
            CROSS JOIN N16 AS B
        ),
        Trigrams AS
        (
            -- Every 3-character substring
            SELECT TOP (CASE WHEN LEN(@string) > 2 THEN LEN(@string) - 2 ELSE 0 END)
                trigram = SUBSTRING(@string, N.n, 3)
            FROM Nums AS N
            ORDER BY N.n
        )
    -- Remove duplicates and ensure all three characters are alphanumeric
    SELECT DISTINCT
        T.trigram
    FROM Trigrams AS T
    WHERE
        -- Binary collation comparison so ranges work as expected
        T.trigram COLLATE Latin1_General_BIN2 NOT LIKE '%[^A-Z0-9a-z]%';
GO
-- Trigrams for Example table
CREATE TABLE dbo.Example_Trigrams
(
    id integer NOT NULL,
    column_id integer NOT NULL,
    trigram char(3) NOT NULL
);
GO
-- Generate trigrams for string1
DECLARE @column_id integer =
    COLUMNPROPERTY(OBJECT_ID(N'dbo.Example', 'U'), N'string1', 'ColumnId');

INSERT dbo.Example_Trigrams
    WITH (TABLOCKX)
    (
        id,
        column_id,
        trigram
    )
SELECT
    E.id,
    @column_id,
    GT.trigram
FROM dbo.Example AS E
CROSS APPLY dbo.GenerateTrigrams(E.string1) AS GT;
GO
-- Generate trigrams for string2
DECLARE @column_id integer =
    COLUMNPROPERTY(OBJECT_ID(N'dbo.Example', 'U'), N'string2', 'ColumnId');

INSERT dbo.Example_Trigrams
    WITH (TABLOCKX)
    (
        id,
        column_id,
        trigram
    )
SELECT
    E.id,
    @column_id,
    GT.trigram
FROM dbo.Example AS E
CROSS APPLY dbo.GenerateTrigrams(E.string2) AS GT;
GO
-- Generate trigrams for string3
DECLARE @column_id integer =
    COLUMNPROPERTY(OBJECT_ID(N'dbo.Example', 'U'), N'string3', 'ColumnId');

INSERT dbo.Example_Trigrams
    WITH (TABLOCKX)
    (
        id,
        column_id,
        trigram
    )
SELECT
    E.id,
    @column_id,
    GT.trigram
FROM dbo.Example AS E
CROSS APPLY dbo.GenerateTrigrams(E.string3) AS GT;
GO
-- Trigram search index
CREATE UNIQUE CLUSTERED INDEX
    [CUQ dbo.Example_Trigrams (column_id, trigram, id)]
ON dbo.Example_Trigrams (column_id, trigram, id)
WITH (DATA_COMPRESSION = ROW);
GO
-- Selectivity of each trigram (performance optimization)
CREATE VIEW dbo.Example_TrigramCounts
WITH SCHEMABINDING
AS
SELECT
    ET.column_id,
    ET.trigram,
    cnt = COUNT_BIG(*)
FROM dbo.Example_Trigrams AS ET
GROUP BY
    ET.column_id,
    ET.trigram;
GO
-- Materialize the view
CREATE UNIQUE CLUSTERED INDEX
    [CUQ dbo.Example_TrigramCounts (column_id, trigram)]
ON dbo.Example_TrigramCounts (column_id, trigram);
GO
-- Most selective trigrams for a search string
-- Always returns a row (NULLs if no trigrams found)
CREATE FUNCTION dbo.Example_GetBestTrigrams
(
    @column_name sysname,
    @string varchar(255)
)
RETURNS table
WITH SCHEMABINDING AS
RETURN
    SELECT
        -- Pivot
        trigram1 = MAX(CASE WHEN BT.rn = 1 THEN BT.trigram END),
        trigram2 = MAX(CASE WHEN BT.rn = 2 THEN BT.trigram END),
        trigram3 = MAX(CASE WHEN BT.rn = 3 THEN BT.trigram END)
    FROM
    (
        -- Generate trigrams for the search string
        -- and choose the most selective three
        SELECT TOP (3)
            rn = ROW_NUMBER() OVER (
                ORDER BY ETC.cnt ASC),
            GT.trigram
        FROM dbo.GenerateTrigrams(@string) AS GT
        JOIN dbo.Example_TrigramCounts AS ETC
            WITH (NOEXPAND)
            ON ETC.column_id = COLUMNPROPERTY(OBJECT_ID(N'dbo.Example', 'U'), @column_name, 'ColumnId')
            AND ETC.trigram = GT.trigram
        ORDER BY
            ETC.cnt ASC
    ) AS BT;
GO
-- Returns Example ids matching all provided (non-null) trigrams
CREATE FUNCTION dbo.Example_GetTrigramMatchIDs
(
    @column_name sysname,
    @Trigram1 char(3),
    @Trigram2 char(3),
    @Trigram3 char(3)
)
RETURNS @IDs table (id integer PRIMARY KEY)
WITH SCHEMABINDING AS
BEGIN
    DECLARE @column_id integer =
        COLUMNPROPERTY(OBJECT_ID(N'dbo.Example', 'U'), @column_name, 'ColumnId');

    IF  @Trigram1 IS NOT NULL
    BEGIN
        IF @Trigram2 IS NOT NULL
        BEGIN
            IF @Trigram3 IS NOT NULL
            BEGIN
                -- 3 trigrams available
                INSERT @IDs (id)
                SELECT ET1.id
                FROM dbo.Example_Trigrams AS ET1
                WHERE ET1.trigram = @Trigram1
                AND ET1.column_id = @column_id
                INTERSECT
                SELECT ET2.id
                FROM dbo.Example_Trigrams AS ET2
                WHERE ET2.trigram = @Trigram2
                AND ET2.column_id = @column_id
                INTERSECT
                SELECT ET3.id
                FROM dbo.Example_Trigrams AS ET3
                WHERE ET3.trigram = @Trigram3
                AND ET3.column_id = @column_id
                OPTION (MERGE JOIN);
            END;
            ELSE
            BEGIN
                -- 2 trigrams available
                INSERT @IDs (id)
                SELECT ET1.id
                FROM dbo.Example_Trigrams AS ET1
                WHERE ET1.trigram = @Trigram1
                AND ET1.column_id = @column_id
                INTERSECT
                SELECT ET2.id
                FROM dbo.Example_Trigrams AS ET2
                WHERE ET2.trigram = @Trigram2
                AND ET2.column_id = @column_id
                OPTION (MERGE JOIN);
            END;
        END;
        ELSE
        BEGIN
            -- 1 trigram available
            INSERT @IDs (id)
            SELECT ET1.id
            FROM dbo.Example_Trigrams AS ET1
            WHERE ET1.trigram = @Trigram1
            AND ET1.column_id = @column_id;
        END;
    END;

    RETURN;
END;
GO
-- Search implementation
CREATE FUNCTION dbo.Example_TrigramSearch
(
    @column_name sysname,
    @search varchar(255)
)
RETURNS table
WITH SCHEMABINDING
AS
RETURN
    SELECT
        Result.id,
        Result.string
    FROM dbo.Example_GetBestTrigrams(@column_name, @search) AS GBT
    CROSS APPLY
    (
        -- Trigram search
        SELECT
            E.id,
            string =
                CASE @column_name
                    WHEN 'string1' THEN E.string1
                    WHEN 'string2' THEN E.string2
                    WHEN 'string3' THEN E.string3
                    ELSE NULL
                END
        FROM dbo.Example_GetTrigramMatchIDs
            (@column_name, GBT.trigram1, GBT.trigram2, GBT.trigram3) AS MID
        JOIN dbo.Example AS E
            ON E.id = MID.id
        WHERE
            -- Residual exact match check
            CASE @column_name
                WHEN 'string1' THEN E.string1
                WHEN 'string2' THEN E.string2
                WHEN 'string3' THEN E.string3
                ELSE NULL
            END LIKE @search
            -- At least one trigram found
            AND GBT.trigram1 IS NOT NULL

        UNION ALL

        -- Non-trigram search
        SELECT
            E.id,
            string =
                CASE @column_name
                    WHEN 'string1' THEN E.string1
                    WHEN 'string2' THEN E.string2
                    WHEN 'string3' THEN E.string3
                    ELSE NULL
                END
        FROM dbo.Example AS E
        WHERE
            CASE @column_name
                WHEN 'string1' THEN E.string1
                WHEN 'string2' THEN E.string2
                WHEN 'string3' THEN E.string3
                ELSE NULL
            END LIKE @search
            -- No trigram found
            AND GBT.trigram1 IS NULL
    ) AS Result;

That particular implementation choice allows trigram searches with the column name as a parameter:
SELECT
    ETS.id,
    ETS.string
FROM dbo.Example_TrigramSearch
(
    N'string1',
    '%12345%'
) AS ETS;

SELECT
    ETS.id,
    ETS.string
FROM dbo.Example_TrigramSearch
(
    N'string2',
    '%ABC12%'
) AS ETS;

SELECT
    ETS.id,
    ETS.string
FROM dbo.Example_TrigramSearch
(
    N'string3',
    '%ABC45%'
) AS ETS;


Connecting to Oracle DB with LDAP-Credentials

For special usernames, use double quotes.

SQL> create user "USER@REALM.DOMAIN" identified by 1;

User created.

SQL> grant create session to "USER@REALM.DOMAIN";

Grant succeeded.

SQL> connect USER@REALM.DOMAIN/1
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Warning: You are no longer connected to ORACLE.
SQL> connect "USER@REALM.DOMAIN"/1
Connected.
SQL>


Index method for very few updates and many inserts

The manual for the pg_trgm module has some advice for your question here:

  As a rule of thumb, a GIN index is faster to search than a GiST index,
  but slower to build or update; so GIN is better suited for static data
  and GiST for often-updated data.

The FASTUPDATE feature of GIN indexes (introduced in Postgres 8.4, ON by default) should be interesting for you. INSERTs (and UPDATEs) are stored in a pending list and integrated into the index in later bulk operations by VACUUM. Makes those operations a lot faster, since direct GIN maintenance tends to be expensive, especially for columns with many indexable elements, i.e. many words in your case.

More basic advice on GIN vs GiST in the manual here.
In particular, if your rows are big and you insert in bulks, you may want to raise the work_mem setting.

For very big loads (large parts of the table), it might pay to drop the index and recreate it afterwards.
Although, 15k rows spread out over a day, shouldn't be a problem at all.

Finally, there is a whole chapter on performance of GIN and GiST in the Full Text Search section. Applies generally. Read this.

The gist of it (no pun intended): GIN is considerably bigger and more expensive to maintain, but faster for most lookups (special exceptions apply for pg_trgm). Since the addition of FASTUPDATE writes are only moderately more expensive.


Merge replication - can't create snapshot – timeout

So it works again...

Maybe someone else has got the same issue one day, so i post the solution here:

I researched on the server and found out, the sql server service is running under a local user. The reason for this is, that there were problems with the backupsystem, used by our customers and so they changed it years ago.

Because of the local user account the 15404-Error occures.

Knowing, that i mustn't use domain-accounts, I also solved the initial problem with my snapshot-agent. I searched for hours (nearly days ;) ) and it was just this little change:

When the Replication is created, the job is created too. The job has three steps. The Job-owner is the local-admin, also for the server-agent-service. But the second step of my job (replictionsnapshot) has one setting: run as. And by default this isn't the job-owner but the user running the creation, in my case my domain-account.

Now, that I set it to the local-administrator as well everything works fine again.

Thanks, Karl


Attaching Database Incorrect name in sys.master_files

Well, for one, you really shouldn't be moving database files around using detach/attach:

Bad Habits : Using MDF/LDF Files as "Backups"

The problem probably happened because you reused MDF/LDF files for new databases with different names - the files keep the properties of the original database even if the new database has a different name and the files have different filenames/locations.

But this is an easy "problem" to fix:

ALTER DATABASE dbname MODIFY FILE (NAME = N'old_name', NEWNAME = N'new_name');

(Another way to fix it is to stop your alerting system from even looking at this, never mind complaining about it - other than OCD, there is no reason why two different databases can't have the same logical file names - they're unique per database, not per instance.)


slow wal replay in backup postgres12 server

There must be a replication conflict with a query that prevents applying the changes to the data files.

Look into pg_stat_database_conflicts on the standby for details.

You probably have max_standby_streaming_delay set to a value greater than 0 and long running queries on the standby. Now if some action on the primary server causes WAL entries whose application would conflict with the running query, replay of these data modifications gets delayed.

You can try to minimize conflicts by setting hot_standby_feedback to on (at the risk of bloating your primary server's tables), but you'll never totally get rid of them.

It is difficult to have a standby that is both never delayed and supports long running queries at the same time. Better use two standby servers with different max_standby_streaming_delay if you need both. Of course, if you want the standby for scaling reads, you want both at the same time, and you are in a difficult position. Streaming replication is not very good for that.

You could also experiment with setting vacuum_defer_cleanup_age to a value greater than 0 to reduce the number of replication conflicts caused by VACUUM.


This is a misunderstanding. A function like this is perfectly valid:

CREATE OR REPLACE FUNCTION test()
 RETURNS SETOF record AS
$func$
VALUES (1, 2), (3, 4);  -- 2 rows with 2 integer columns
$func$ LANGUAGE sql;

However, since it returns anonymous records, you are required to provide a column definition list with every call , just like the error message told you. SQL demands to know what to expect from a function up front. The call would work like this:

SELECT * FROM test() AS f(a int, b int);

Functions returning anonymous records have their uses. I try to avoid them mostly, because the call is rather unwieldy. Depending on your exact requirements, there are a couple of ways around it. I wrote a comprehensive answer for that on SO recently.