Is this substring_index behaviour documented or a bug?

To me it would seem like it should be a bug. Yet, note the MySQL Documentation:

Returns the substring from string str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. SUBSTRING_INDEX() performs a case-sensitive match when searching for delim.

Here is an output of every count from -5 to 5
mysql> SET @foo = '|A|B|';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT x,SUBSTRING_INDEX(@foo,'|',x)
    -> FROM
    -> (
    ->     SELECT 0 x
    ->     UNION SELECT  1 UNION SELECT  2
    ->     UNION SELECT  3 UNION SELECT  4 UNION SELECT 5
    ->     UNION SELECT -1 UNION SELECT -2
    ->     UNION SELECT -3 UNION SELECT -4 UNION SELECT -5
    -> ) A ORDER BY x DESC;
+----+-----------------------------+
| x  | SUBSTRING_INDEX(@foo,'|',x) |
+----+-----------------------------+
|  5 | |A|B|                       |
|  4 | |A|B|                       |
|  3 | |A|B                        |
|  2 | |A                          |
|  1 |                             |
|  0 |                             |
| -1 |                             |
| -2 | B|                          |
| -3 | A|B|                        |
| -4 | |A|B|                       |
| -5 | |A|B|                       |
+----+-----------------------------+
11 rows in set (0.00 sec)

mysql>

Notice in both directions, since @foo only has 3 pipe characters, any position whose absolute value exceeds 3 should produce an error. Since the Documentation says final delimiter, it does not seem to enforcement counts outside the range of delimiters (at least in the Windows version).
I tried it out on my laptop running MySQL in Windows 8.1
mysql> drop table if exists t;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table t as select '|A|B|' as foo;
Query OK, 1 row affected (0.64 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select foo, substring_index(foo,'|',-4) from t;
+-------+-----------------------------+
| foo   | substring_index(foo,'|',-4) |
+-------+-----------------------------+
| |A|B| | |A|B|                       |
+-------+-----------------------------+
1 row in set (0.00 sec)

mysql> drop table if exists t;
Query OK, 0 rows affected (0.13 sec)

mysql> create table t(foo varchar(5) not null);
Query OK, 0 rows affected (0.35 sec)

mysql> insert into t(foo) values('|A|B|');
Query OK, 1 row affected (0.03 sec)

mysql> select foo, substring_index(foo,'|',-4) from t;
+-------+-----------------------------+
| foo   | substring_index(foo,'|',-4) |
+-------+-----------------------------+
| |A|B| | |A|B|                       |
+-------+-----------------------------+
1 row in set (0.00 sec)

What version of MySQL am I running ?
mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.6.15                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.6.15                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Win64                        |
+-------------------------+------------------------------+
7 rows in set (0.00 sec)

mysql>

My Windows version works ? I say you definitely have a bug in the Ubuntu version.
I reran it again, this time running SHOW CREATE TABLE t\G
mysql> drop table if exists t;
Query OK, 0 rows affected (0.11 sec)

mysql> create table t as select '|A|B|' as foo;
Query OK, 1 row affected (0.36 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `foo` varchar(5) CHARACTER SET cp850 NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select foo, substring_index(foo,'|',-4) from t;
+-------+-----------------------------+
| foo   | substring_index(foo,'|',-4) |
+-------+-----------------------------+
| |A|B| | |A|B|                       |
+-------+-----------------------------+
1 row in set (0.00 sec)

mysql> drop table if exists t;
Query OK, 0 rows affected (0.09 sec)

mysql> create table t(foo varchar(5) not null);
Query OK, 0 rows affected (0.31 sec)

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `foo` varchar(5) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> insert into t(foo) values('|A|B|');
Query OK, 1 row affected (0.03 sec)

mysql> select foo, substring_index(foo,'|',-4) from t;
+-------+-----------------------------+
| foo   | substring_index(foo,'|',-4) |
+-------+-----------------------------+
| |A|B| | |A|B|                       |
+-------+-----------------------------+
1 row in set (0.00 sec)

mysql>

The bug may involve the character set since the last line of the SUBSTRING_INDEX Documentation says This function is multibyte safe. Perhaps SUBSTRING_INDEX may be OK after all, but that's me messing around in Windows.
Run the script
SET @foo = '|A|B|';
SELECT x,SUBSTRING_INDEX(@foo,'|',x)
FROM
(
    SELECT 0 x
    UNION SELECT  1 UNION SELECT  2
    UNION SELECT  3 UNION SELECT  4 UNION SELECT 5
    UNION SELECT -1 UNION SELECT -2
    UNION SELECT -3 UNION SELECT -4 UNION SELECT -5
) A ORDER BY x DESC;

and see if you get the same output that I did.
GIVE IT A TRY !!!


Always Encrypted: How to insert or update encrypted columns?

You need to use variables for the values, so that SSMS can parameterize the query against the API that it is using. Something like:

    DECLARE @fname varchar(30) = 'Kula'
DECLARE @ename varchar(30) = 'Kalle'
DECLARE @pnr varchar(11) = '752312-4545'
DECLARE @age tinyint = 45

insert into dbo.Personer2 (Förnamn, Efternamn, Personnummer, Ålder)
VALUES (@fname, @ename, @pnr, @age)

SELECT * FROM Personer2


Allow refresh of lower environment from production when using a HSM

A method we found to be viable was to convert production to use the HSM and then clone the partition containing the key for production to development.  This needs to be repeated with each clone of the database and then a post clone step can modify the key for the lower environment using normal key change processes.


Not sure why one of the answers made such bold statement .
Regardless: everything can be modeled in relational model

create table entry
(
  id number(12) primary key,
  name varchar2(256) not null unique,
  customer_id number not null references customer(id)
);

create table datatypes
(
 id number primary key,
 name varchar2(256) not null unique
);
create table entry_data (
  id number(12) primary key,
  datatype_id  number not null references datatypes(id),
  value     varchar2(256),  --or CLOB ,no index
  value_geometry geom  ,    -- no index
  --value_blob   blob -- if you neeed, no index
  entry_id number not null references entry(id),
);

your task is related to the multi-valued attributes task.
Datatypes table will abstract your datatypes, however 2 value** columns are created in entry_data due to the fact that your data can be different in nature(belong to different Classes), thus would need different storage. But as far as varchar2 vs. number vs.date -- all can be placed into varchar2 , just would need later on some casting. And with very simplistic logic of NVL in code you can determine which column was populated out of value or value_geometry.


Selecting non-aggregate column on 3 table inner join

This can be solved by aggregating first and joining to the result of the aggregation:
SELECT c.name as campaign_name,
       o.name as organization_name
       d.total_amount
FROM (
  select "campaignId", sum(amount) as total_amount
  from donations
  group by "campaignId"
) d
  JOIN campaigns c ON c.id = d."campaignId"
  JOIN organisations o ON o.id = c."organisationId"
;


Logon failed for login due to trigger execution

According to the screenshots you posted you are encountering an Error 17892 which translate roughly to a logon trigger error.

You might have previously had a logon trigger that evaluated if you were allowed to log in to the SQL Server or not. This trigger may have been removed or might produce wrong results which now does not allow anybody to log in to the SQL Server instance.

There are three possible workarounds:

1. Open Query Window Available

Search for the trigger via:

SELECT * FROM sys.server_triggers;

If you see a server level trigger that might be responsible for the login issues, then you can drop it with:

DROP TRIGGER triggername ON ALL SERVER;

Instead of dropping the trigger just disable it with:

DISABLE TRIGGER triggername ON ALL SERVER;

2. DAC Allowed; No Open Query Window Available

If you don't have an open Query Window, then you will have to log in to your SQL Server instance with a DAC (ADMIN) connection:

sqlcmd –S 127.0.0.1,1434

1434 is the default port for the DAC connection

You can then run the above queries from solution 1. to determine the trigger and then drop or disable the trigger.

3. No Open Query Window; No DAC Allowed

If you don't have an open Window and DAC has not been turned on, then you are going to have to stop the instance and start again using the following steps:

sqlserver.exe –c -m –f

This will allow you to start the instance in single user mode with minimal configuration and shorter startup time.

Then you can disable the triggers according to step 1. above.


You have misplaced parenthesis.

with open(filename, 'rb') as f:
    f1 = f.read()
    cur.execute(
        "INSERT INTO testable (title, content, chron_date, clock) VALUES (%s, %s, %s, %s);", # remove parenthesis here, which ends the execute call
        (filename, f1, '2017-12-30', '23:59:00')
    ) # move above parenthesis here


A question about oracle upgrade from 12.0.0.1 to 12.0.0.2 using dbua

Yes, but do not simply delete it - deinstall it properly: http://docs.oracle.com/database/121/LADBI/remove_oracle_sw.htm#LADBI7824

Run $ORACLE_HOME/deinstall/deinstall and follow the instructions.


Combine Multiple primary databases to single AlwaysOn AG

No, you cannot. This is because a database can belong to one and only one availability group.

You can, however, create an availability group from Server1 to DR and Server2 to DR which would have the DR server as a secondary to both availability groups and a copy of both databases would reside there.


Change logging level programmatically for multiple jobs

The logging level is held in the command column of msdb.dbo.sysjobsteps. By editing the command you can set the logging level you require.  There is a system stored procedure - sp_update_jobstep - to achieve this.

From a quick look on my local system, SSIS job steps' commands seem to be a reasonably straight-forward concatenation of labeled values. A bit of regex and some substitutions should be sufficient.


concerning "The full-text index is updated in the background, however, so propagated changes might not be reflected immediately in the index." from what I can tell and experience it means the change to the full-text is updated as any other clustered index, using a background task and not on the immediate instant that the data was changed and the update was triggered.

Also reading a bit more on the documentation (create fulltext index):
"CHANGE_TRACKING [ = ] { MANUAL | AUTO | OFF [ , NO POPULATION ] }
Specifies whether changes (updates, deletes or inserts) made to table columns that are covered by the full-text index will be propagated by SQL Server to the full-text index. Data changes through WRITETEXT and UPDATETEXT are not reflected in the full-text index, and are not picked up with change tracking."

This is for the manual option meaning that for the AUTO the reverse can be applied, the change is done by the change tracking event and not immediate.

And here is why (Population based on change tracking):

"Optionally, you can use change tracking to maintain a full-text index after its initial full population. There is a small overhead associated with change tracking because SQL Server maintains a table in which it tracks changes to the base table since the last population. When you use change tracking, SQL Server maintains a record of the rows in the base table or indexed view that have been modified by updates, deletes, or inserts. Data changes made through WRITETEXT and UPDATETEXT are not reflected in the full-text index, and are not picked up with change tracking."


Joining 4 tables and selecting distinct values based on 3 timestamps

You have to define first how a "testing" is identified and what "latest" is. Which of the columns identifies a testing and which of the date columns is going to be used for the ordering?

Then, one way to write the query is using window functions:

With cte As
  ( Select
        lb.request_ky,
        lb.build_dm,
        lb.part_id,
        dv.date_test As date_test1,
        dv.result    As result1,
        dw.date_test As date_test2,
        dw.result    As result2,
        tt.date_test As date_test3,
        tt.result    As result3,
        Row_number() Over (Partition By          -- pick a column for the
                             lb.part_id          -- identification
                           Order By              -- and another for the
                             dv.date_test Desc)  -- order
            As rn
    From
        tableA lb
      Join tableB dv
        On lb.part_id = dv.part_id
      Join tableC dw
        On lb.part_id = dw.part_id
      Join tableD tt
        On lb.part_id = tt.part_id
  )
Select
    request_ky,
    build_dm,
    part_id,
    date_test1,
    result1,
    date_test2,
    result2,
    date_test3,
    result3
From
    cte
Where
    rn = 1 ;


It means that the parameter should be defined to match the data type of the underlying column - currently the parameter is nvarchar(4000) but it should be varchar (looks like 255 but you should check the table).

Whether this actually makes your query slow or leads to a bad plan is tougher to determine. But making those two definitions match certainly isn't going to make it worse.

If you can't change the parameter data type, either get a better ORM or change the column as a last resort.


Oracle: deleting all services associated with a SID

SYS$BACKGROUND and SYS$USERS are built-in internal services, they are not exposed to the listener and clients.

XE is your default service, because an Oracle database by default has a service with its db_unique_name, and registers itself to a listener running on the default port (1521) with the db_unique_name as service name, and instance_name as instance.

The XEXDB service exists, because you have the XML database option configured, and the dispatchers parameter is set.

You do not need to modify service_names manually, the database handles that parameter based on the service configuration.

In my opinion, the use of services should be promoted, not prevented. Using multiple services for a single database has the benefit of being able to manage clients connecting through different services seperately, when troubleshooting (tracing), or prioritizing workload (resource manager).


Right outer join seems to not work because of where clause filter

To simplify your query slightly you have basically the following:

SELECT *
FROM
    [src_boss_entwicklung].[dbo].[tckopf] AS commission
RIGHT OUTER JOIN
    [src_boss_entwicklung].[dbo].[tckopf] AS originDocForCurrency
    ON commission.referenzid = originDocForCurrency.id
WHERE
    originDocForCurrency.id = 68
AND (commission.referenzid = 68 OR commission.referenzid = 68);

Where you have no match in [src_boss_entwicklung].[dbo].[tckopf] the column commission.referenzid will be NULL, meaning this condition:

AND (commission.referenzid = 68 OR commission.referenzid = 68);

will not be true for these rows (because NULL = 68 evaluates to NULL). This effectively makes your RIGHT OUTER JOIN an INNER JOIN

I have never been a big fan of RIGHT JOIN especially when LEFT JOIN appears in the same query. I would rearrange the query to make all the outer joins left outer joins for continuity purposes:

SELECT *
FROM
    [src_boss_entwicklung].[dbo].[tckopf] AS originDocForCurrency
LEFT OUTER JOIN
    [src_boss_entwicklung].[dbo].[tckopf] AS commission
    ON commission.referenzid = originDocForCurrency.id
LEFT OUTER JOIN
    [src_boss_entwicklung].[dbo].[devisen] AS currencies
    ON currencies.datum = (
            SELECT TOP 1 fc.datum
            FROM [src_boss_entwicklung].[dbo].[devisen] AS fc
            WHERE fc.datum <= originDocForCurrency.von
                AND fc.usd_brief IS NOT NULL AND fc.usd_brief > 0
                AND fc.usd_geld IS NOT NULL AND fc.usd_geld > 0
            ORDER BY fc.datum DESC
            )
WHERE
    originDocForCurrency.id = 68
AND (commission.referenzid = 68 OR commission.referenzid = 68);

This does not solve the problem though since you still have the possibility of commission.referenzid being NULL and turning the LEFT OUTER JOIN into an INNER JOIN. You can get around this by moving the predicates to the JOIN conditiion:

SELECT *
FROM
    [src_boss_entwicklung].[dbo].[tckopf] AS originDocForCurrency
LEFT OUTER JOIN
    [src_boss_entwicklung].[dbo].[tckopf] AS commission
    ON commission.referenzid = originDocForCurrency.id
    AND
    ( commission.referenzid = 68
        AND commission.btyp = 7
        AND ( commission.storno <> 1 OR commission.storno IS NULL )
        AND ( commission.proforma <> 1 OR commission.proforma IS NULL ) )
    OR
    ( commission.referenzid = 68
        AND commission.btyp = 8
        AND ( commission.storno <> 1 OR commission.storno IS NULL )
        AND ( commission.proforma <> 1 OR commission.proforma IS NULL )
    )

I am also fairly certain this condition can be simplified to:

SELECT *
FROM
    [src_boss_entwicklung].[dbo].[tckopf] AS originDocForCurrency
LEFT OUTER JOIN
    [src_boss_entwicklung].[dbo].[tckopf] AS commission
    ON commission.referenzid = originDocForCurrency.id
    AND commission.referenzid = 68
    AND commission.btyp in (7, 8)
    AND ISNULL(commission.storno, 0) <> 1
    AND ISNULL(commission.proforma, 0) <> 1

Making your final query:

SELECT
    commission.netto AS commissionNetValue,
    commission.r_art AS commissionDocClass,
    commission.waehrung AS commissionCurrency,
    currencies.usd_brief AS foreignCurrencyUSDAsk,
    currencies.usd_geld AS foreignCurrencyUSDBid,
    originDocForCurrency.r_art AS originDocForCurrencyDocClass,
    originDocForCurrency.poswert AS originDocForCurrencyCommission,
 (SELECT originDoc.poswert FROM [src_boss_entwicklung].[dbo].[tckopf] AS originDoc WHERE originDoc.id = 68) AS originDocCommission,
 (SELECT originDoc.r_art FROM [src_boss_entwicklung].[dbo].[tckopf] AS originDoc WHERE originDoc.id = 68) AS originDocClass,
 (SELECT originDoc.waehrung FROM [src_boss_entwicklung].[dbo].[tckopf] AS originDoc WHERE originDoc.id = 68) AS originDocCurrency
FROM
    [src_boss_entwicklung].[dbo].[tckopf] AS originDocForCurrency
LEFT OUTER JOIN
    [src_boss_entwicklung].[dbo].[tckopf] AS commission
    ON commission.referenzid = originDocForCurrency.id
    AND commission.referenzid = 68
    AND commission.btyp in (7, 8)
    AND ISNULL(commission.storno, 0) <> 1
    AND ISNULL(commission.proforma, 0) <> 1
LEFT OUTER JOIN
    [src_boss_entwicklung].[dbo].[devisen] AS currencies
    ON currencies.datum = (
            SELECT TOP 1 fc.datum
            FROM [src_boss_entwicklung].[dbo].[devisen] AS fc
            WHERE fc.datum <= originDocForCurrency.von
                AND fc.usd_brief IS NOT NULL AND fc.usd_brief > 0
                AND fc.usd_geld IS NOT NULL AND fc.usd_geld > 0
            ORDER BY fc.datum DESC
            )
WHERE
    originDocForCurrency.id = 68;

ADDENDUM

As pointed out in a comment, you don't need the three subselects, you can just use the columns in the table aliased originDocForCurrency:

SELECT
    commission.netto AS commissionNetValue,
    commission.r_art AS commissionDocClass,
    commission.waehrung AS commissionCurrency,
    currencies.usd_brief AS foreignCurrencyUSDAsk,
    currencies.usd_geld AS foreignCurrencyUSDBid,
    originDocForCurrency.r_art AS originDocForCurrencyDocClass,
    originDocForCurrency.poswert AS originDocForCurrencyCommission,
    originDocForCurrency.poswert AS originDocCommission,
    originDocForCurrency.r_art AS originDocClass,
    originDocForCurrency.waehrung AS originDocCurrency
FROM
    [src_boss_entwicklung].[dbo].[tckopf] AS originDocForCurrency
LEFT OUTER JOIN
    [src_boss_entwicklung].[dbo].[tckopf] AS commission
    ON commission.referenzid = originDocForCurrency.id
    AND commission.btyp in (7, 8)
    AND ISNULL(commission.storno, 0) <> 1
    AND ISNULL(commission.proforma, 0) <> 1
LEFT OUTER JOIN
    [src_boss_entwicklung].[dbo].[devisen] AS currencies
    ON currencies.datum = (
            SELECT TOP 1 fc.datum
            FROM [src_boss_entwicklung].[dbo].[devisen] AS fc
            WHERE fc.datum <= originDocForCurrency.von
                AND fc.usd_brief IS NOT NULL AND fc.usd_brief > 0
                AND fc.usd_geld IS NOT NULL AND fc.usd_geld > 0
            ORDER BY fc.datum DESC
            )
WHERE
    originDocForCurrency.id = 68;


What is a common way to save 'debit' and 'credit' information?

Another way to do this (no idea if this is common or not!):

create table `transactions` (
  ... some columns ...
  amount decimal(10,2),
  ttype int,
  foreign key (ttype) references transactiontypes (id)
  ...
);

create table transactiontypes (
  id int primary key,
  description varchar(30),
  multiplier int
);

And then in transactiontypes you have:

select * from transactiontypes;

+----+---------------+------------+
| id | description   | multiplier |
+----+---------------+------------+
|  1 | deposit       |          1 |
|  2 | withdrawal    |         -1 |
+----+---------------+------------+

And possibly more rows.

Then all amounts in transactions will be positive, and a join to transactiontypes + multiplication by multiplier gets the "real" (positive/negative) amount.


In simple terms you've run out of space in the primary filegroup of the Reporting database.

The data in a database is stored in FileGroups.  The main filegroup (and the one created by default) is Primary.  By default this is where everything is put unless you specify otherwise.  Most databases are just going to have the Primary filegroup.

The error you are getting is caused by running out of space in that Primary filegroup (obviously).  This can be caused by one of two possibilities.

Insufficient disk space.  Your database has expanded to the limit of the drive it's on.  You can easily check this by looking at your server and seeing if the drive the database is on is full.  The solutions here are to either expand the drive, reduce the amount of data in the database, or add additional files to the Primary filegroup on a different drive (or additional filegroups and move data into them).
Your autogrowth settings have limited your growth.  This could be one of a couple of things.

You could have autogrowth turned off entirely.  Basically you have used up the available space in the database and it can't grow by itself.  Typically if you are going to do this then you need to be monitoring the free space in your database and manually grow it when you start running low.
You have a size limit on the files in the filegroup.  In this case you can either increase the size limit (and manually grow the DB) or add additional files as I mentioned above.

To do any of these tasks (except looking at the physical space of the drive) you can use the GUI.  Go to the properties of the database in SSMS.  (Right click on the database name and select properties at the bottom). Then Navigate to the Files tab (you can only control the size of the files within the filegroup)

From here you can see which files are in the Primary filegroup and control their size (Initial Size) & autogrowth settings (hit the ellipsis ...)

The autogrowth settings look like this:

You can see here you can control the max size of the file & if autogrowth is allowed at all.


How confident are you that max concurrent connections will stay at 10 in the future?

If you're interested in raw speed - which is part of the domain of In-Memory OLTP - then transmitting via wifi will likely be a bottleneck. Even if  you're mainly interested in scalability of the write workload - wifi or USB cradle will have its limits - preventing the full (potential) benefit of using In-Memory OLTP.

While Tony Hinkle is correct that an IDENTITY is more "efficient" than a GUID, clustering on an IDENTITY is an anti-pattern for highly concurrent, write-intensive workloads. It's exactly this anti-pattern that In-Memory is meant to solve, but consideration of using In-Memory OLTP should start a dialog that takes into account all of the many gotchas, i.e. no db mirroring, no snapshots, can never remove the memory-optimized filegroup, etc.

Queries can produce different results when using any form of snapshot isolation, so you'd have to verify that the way your app works can handle that.

So while the volume of data to be inserted is not particularly high, if you throw enough concurrent connections at it, scalability will be an issue. This is easily demonstrated with ostress (as long as you don't output to files). As concurrency ramps up, say from 100 to 500 connections, INSERT performance simply crawls. This is due to having to latch the last page of the clustered index, which essentially serializes all writes to the table.

Given what you've described, In-Memory OLTP might be best used like a "shock absorber", as a landing pad for your data. Then you could validate it, before sending it to its final destination. If that final destination is another memory-optimized table, be advised that all writes to a memory-optimized table are serial, no matter what the source. So you would have to roll your own parallelism, and ensure that your processing does not step on its own toes. You'll essentially have a queue, and due to its lock-free architecture, In-Memory OLTP is poorly suited to queue processing. There must be some form of serialization to make sure your concurrent processes don't attempt to process the same set of rows.

All traditional/disk based tables can suffer from deadlocks, which can usually be reduced, and perhaps eliminated, but there is no guarantee of that. Deadlocks are not possible with memory-optimized tables, due the lock/latch free architecture (same for indexes on memory-optimized tables).

Queries will probably not be any faster with In-Memory OLTP, as it's not designed for that.

I have blogged extensively about In-Memory OLTP. If you are seriously considering deploying it, I strongly suggest you study what I've written.


mysql: unknown variable 'table_definition_cache=400'

In order to solve this please put these configurations under [mysqld] section of config file. For reference check here

[mysqld]
table_definition_cache = 400
table_open_cache = 400


Oracle TimesTen vs Oracle database in-memory

There's a massive difference. TimesTen is an dedicated in-memory database.

Oracle in-memory is just an added optional extra for Oracle 12c that lets you mark data, given certain indexing rules (you drop them), as in-memory. Oracle then caches the data in memory in both a row and columnar fashion, enabling fast retrieval for both OLTP and reporting workloads. It requires no code changes for existing Oracle-based applications - completely transparent & can offer significant performance improvements.


Percona Xtradb Cluster 5.5: node can't join cluster - SST fails with auth problem

I managed to perform SST by creating default user mysql@localhost without password on donor node, with required grants RELOAD, LOCK TABLES, REPLICATION CLIENT. Ugly workaround, but it worked.

On the next day I stopped mysql on node03 and started it after several minutes. SST started normally, with user and password from wsrep_sst_auth variable. Bug disappeared, I don't know what it was.


How to see who made a change to a table in SQL Server

You should have an extended event session in place but as a quick and dirty solution you can use the report below:


On modeling pairs with a possibly empty second element

Other options I see:

A) add column x to table _data. A few more changes will be required:

CREATE TABLE X (
      x TEXT PRIMARY KEY
);

CREATE TABLE Y (
      y TEXT PRIMARY KEY
    , x TEXT NOT NULL
    , FOREIGN KEY (x) REFERENCES X(x),
    , UNIQUE (x, y)                    -- required for the modified FK below
);

CREATE TABLE _DATA (
      data_id INTEGER PRIMARY KEY
    , x TEXT NOT NULL
    , y TEXT
    , FOREIGN KEY (x, y) REFERENCES Y(x, y)    -- modified FK
    , FOREIGN KEY (x) REFERENCES X(x)          -- additional FK
);

The view has to be modified as well:

CREATE VIEW mydata AS
  SELECT X.x, Y.y
    FROM X JOIN Y     USING (x)
           JOIN _DATA USING (y)
  UNION ALL
  SELECT X.x, NULL
    FROM X JOIN _DATA USING (x)
  WHERE _DATA.y IS NULL ;

Example:

INSERT INTO X (x) VALUES
    ('a')
  , ('b')
  , ('c')
  , ('d')
;

INSERT INTO Y (y, x) VALUES
    ('h', 'a')
  , ('i', 'b')
  , ('k', 'c')
;

INSERT INTO _DATA (data_id, x, y) VALUES
    (1, 'a', 'h')
  , (2, 'b', 'i')
  , (3, 'b', NULL)
  , (4, 'c', 'k')
  , (5, 'd', NULL)
  , (6, 'a', 'h')
;

SELECT x, y FROM mydata;
 x | y
---+---
 a | h
 b | i
 c | k
 a | h
 b |
 d |
(6 rows)

B) separate the data that have no y value associated to another table. No changes at all required to the existing tables, only for the view:

CREATE TABLE X (
      x TEXT PRIMARY KEY
);

CREATE TABLE Y (
      y TEXT PRIMARY KEY
    , x TEXT NOT NULL
    , FOREIGN KEY (x) REFERENCES X(x)
);

CREATE TABLE _DATA (
      data_id INTEGER PRIMARY KEY
    , y TEXT NOT NULL
    , FOREIGN KEY (y) REFERENCES Y(y)
);

CREATE TABLE _DATA_X (
      data_x_id INTEGER PRIMARY KEY
    , x TEXT NOT NULL
    , FOREIGN KEY (x) REFERENCES X(x)
);

The view modified:

CREATE VIEW mydata AS
  SELECT X.x, Y.y
    FROM X JOIN Y     USING (x)
           JOIN _DATA USING (y)
  UNION ALL
  SELECT X.x, NULL
    FROM X JOIN _DATA_X USING (x) ;

Example:

INSERT INTO X (x) VALUES
    ('a')
  , ('b')
  , ('c')
  , ('d')
;

INSERT INTO Y (y, x) VALUES
    ('h', 'a')
  , ('i', 'b')
  , ('k', 'c')
;

INSERT INTO _DATA (data_id, y) VALUES
    (1, 'h')
  , (2, 'i')
  , (3, 'k')
  , (4, 'h')
;

INSERT INTO _DATA_X (data_x_id, x) VALUES
    (1, 'b')
  , (2, 'd')
;

SELECT x, y FROM mydata;
 x | y
---+---
 a | h
 b | i
 c | k
 a | h
 b |
 d |
(6 rows)


Concurrent transactions result in race condition with unique constraint on insert

Try the insert first, with on conflict ... do nothing and returning id.
If the value already exists, you will get no result from this statement, so you have then to execute a select to get the ID.

If two transactions try to do this at the same time, one of them will block on the insert (because the database does not yet know if the other transaction will commit or rollback), and continue only after the other transaction has finished.


Ola Hallengren SQL Server Backup - CleanupTime

There's a separate @CleanupMode parameter that controls whether cleanup happens before or after. The default behavior is to do the cleanup AFTER. This is the safest option, but requires the most disk space. In the event of a failed backup, nothing is cleaned up, ensuring you have extra files, rather than having deleted a file without making a new one.

This is particularly important if backups were to fail repeatedly (cleaning up, but never making a new one) or when there's a very short retention.

From the documentation:

CleanupMode

Specify if old backup files should be deleted before or after the backup has been performed.

Values:

BEFORE_BACKUP - Delete old backup files before the backup has been performed.
AFTER_BACKUP - Delete old backup files after the backup and verify has been performed. If the backup or verify failed, then no backup files are deleted. This is the default.


You should always store dates using the DATE type. It should use less storage and will generally be faster when searching than if you stored the same date as a VARCHAR.

Additionally, if you stored it as VARCHAR then the format is baked into your data, which will make it harder for you to use different date formats in the presentation layer. For example, what if one user uses en_US and one uses en_GB, the date will get stored differently within the same column of your database. Then when you query your data, how will you know whether '01/12/2020' is January 12th 2020 or December 1st 2020? There is a big difference.

Storing the date as a date allows you to apply different formatting (such as language differences, or precision differences) in the presentation layer where it belongs.


I would have to say, resoundingly, that your co-worker is correct and you are not. dBASE (.dbf) is a great example of a flat file database--which has been used for 35 years or so. https://en.wikipedia.org/wiki/.dbf

Even RDBMS like SQL use simple 8kb pages for storing data. The tools we use to access the data makes it look more complex, but in reality it's stored in a very simple structure. Using hints, you can even have SSMS display these pages of data right in the output window.

By going by the simple definition of the database, it's a structured collection of data. CSV, XML, JSON, DBF all meet this definition.


Are the contents of all control files the same?

yes, normally that is what you do, if you happen to know which file is the correct one. Make a copy before overwriting the file first.


SQL Server Buffer Pool Utilization under VMWare

Trace flag 834 can allow the maximum amount of memory to be allocated at startup for x64 machines.  The following blog has more detail but here are the basics:

Enterprise Edition needs to be installed
8GB of RAM or more needs to be present
Lock Pages in Memory needs to be on.

http://blogs.msdn.com/b/psssql/archive/2009/06/05/sql-server-and-large-pages-explained.aspx

Read the full article as there are caveats and warning spread throughout as this can lead to a much longer startup time (or failed starts).  A big one being that it needs to allocate a contiguous chunk of memory and I'm not sure how that will work on VMWare.  Also, if the physical memory in the host is 128GB I would reduce max server memory some more to make sure there's enough space left for VMWare to do it's thing.


But how far can I continue to break the information down into smaller
  lookup tables before it becomes unmanageable? I could create a Gender
  table and have a 1 correspond to Male and a 2 correspond to Female in
  a separate lookup table.

You're mixing two different issues. One issue is the use of a "lookup" table; the other is the use of surrogate keys (id numbers).

Start with this table.

ID  LName   FName   Gender  Position
1   Doe     John    Male    Manager
2   Doe     Jane    Female  Sales
3   Smith   John    Male    Sales

You can create a "lookup" table for positions like this.

create table positions (
  pos_name varchar(10) primary key
);

insert into positions
select distinct position
from employees;

alter table employees
add constraint emp_fk1
foreign key (position)
  references positions (pos_name);

Your original table looks exactly like it did before creating the "lookup" table. And the table of employees requires no additional joins to get useful, human-readable data out of it.

Using a "lookup" table boils down to this: Does your application need the control over input values that a foreign key reference provides? If so, then you can always use a "lookup" table. (Regardless of whether it uses a surrogate key.)

In some cases, you'll be able to completely populate that table at design time. In other cases, users need to be able to add rows to that table at run time. (And you'll probably need to include some administrative processes to review new data.) Gender, which actually has an ISO standard, can be completely populated at design time. Street names for international online product orders probably have to be added at run time.


"Error while processing statement" running a query finding unique values with multiple values in other rows

I suspect you are using a Hive version that is before 0.13.0. In that case the problem is the AS keyword when giving alias to subquery.

From HiveQL documentation Subqueries in the FROM Clause:

SELECT ... FROM (subquery) name ...
SELECT ... FROM (subquery) AS name ...   (Note: Only valid starting with Hive 0.13.0)

  ...
  The optional keyword "AS" can be included before the subquery name in Hive 0.13.0 and later versions (HIVE-6519).

Therefore, valid syntax would be:

Select Distinct Ss.`cd`, Ss.`cd`
From nit.assrr AS Ss
    Join (
       Select `cd`
       From nit.assrr
       Group By `cd`
       Having Count(Distinct `cd`) > 1
      ) Y                                  -- AS removed
On Ss.`cd` = Y.`cd` ;

Regarding the semantics of the query. I'm not sure what the actual problem is but this query is guaranteed to return 0 rows, no matter what the data in the table are.

COUNT(DISTINCT cd) will be 1 when you have GROUP BY cd so the HAVING clause will be FALSE always and the Y subquery will have 0 rows.


You definitely want to keep database constraints.
They guard data integrity at the database level, not allowing for inconsistent data situations.

An example: your database and application are about students and lockers. So you have two tables: Students and Lockers, and the Locker table has a column linking/pointing/referencing/assigning a locker to a student.
Say your application allows to submit a new student AND at the same time he can choose a locker. That means two database actions and an email to the student:
1. Insert a new student with a new ID.
2. Update the Locker table record linking assigning it to the new student.

Logically, we would first insert the new Student and then update the Locker record, but your programmer is not so experienced and codes it the other way round, not very logical but nobody sees the illogical code and hey it works perfectly while he demo's it.

Now in the real world, as Murphey dictates, when a real user is using the app, halfway the process the web server crashes (database timeout, deadlock, etc...). So the locker record has been updated pointing it to the new Student ID (and the email got sent out), but the Student record never got inserted.
Oops, so you've ended up with a locker assigned to a non-existing student.

You want those database constraints to guard database integrity.


As @Nick suggested, this question is too broad to have straight forward answer. Are you looking for how to write queries ?? Or you are looking for commonly used best practices for any query ?? Or something else ??
May be for starter you can google around this question, or look at some of the following sites,

http://blog.sqlauthority.com/2009/01/20/sql-server-rules-for-optimizining-any-query-best-practices-for-query-optimization/

http://www.databasejournal.com/features/mssql/article.php/3827091/T-SQL-Best-Practices.htm

http://www.sql-server-performance.com/2001/sql-best-practices/

http://www.codeproject.com/Articles/34372/Top-10-steps-to-optimize-data-access-in-SQL-Server


How to convert time without time zone to timestamp without time zone in PostgreSQL?

Since time does not have a date component you need to provide a date. You can just add date and time values in the ALTER TABLE statement:

ALTER TABLE students ALTER COLUMN time_since_missing_schedule_notification
 TYPE timestamp USING ('2000-1-1'::date + time_since_missing_schedule_notification)


ExclusiveLock on related table due to update?

There is no way to fix this with PostgreSQL 9.1

Up until 9.2 (including) any referencing table was locked  during an update.

This was fixed in the PostgreSQL 9.3 (also unsupported by now)

Quote from the release notes:

  Prevent non-key-field row updates from blocking foreign key checks


Cassandra writing (too?) large partitions

By default, Cassandra uses SizeTieredCompactionStrategy that compacts several files (4 by default) of similar size into bigger file. These files contain multiple partitions, so big file size isn't a necessary sign of wide partitions.

When you're using Cassandra (especially with SizeTieredCompactionStrategy) you need to have ~50% of disk space free so Cassandra will able to write data during compaction - after compaction happens, the old files will be removed.

You may also consider the use of other compaction strategies, such as, LeveledCompactionStrategy, but it's more suited for read-heavy workloads (like 90% of all operations are reads).

You can read more about compaction strategies in documentation, and about size tiered compaction in this blog post.


SQL Server 2008 R2 Express Edition - generating database create script

This SQL script is a SQL Server 2005 table scripting tool that I wrote to reverse engineer a database schema to SQL.  It might do what you want.  Load it in SSMS and set 'Results to Text' from the menu.  Then run the script.  It will reverse engineer most of the major database objects.


Schema Change/Update script for Database deploy

First, I would go with an existing solution, such as Liquibase. If you use an ORM such as Hibernate, it can also do schema migrations for you.

Otherwise, it would go something like this:

Start with an initial database schema script, and give it a version number, such as 0.0.0001.sql. It would contain mostly CREATE statements to create tables, etc, such as:

-- use me to keep track of what version the database schema is using:
create table schema_version (
  name text primary key
);

create table contact (
  id bigserial primary key,
  first_name text not null
);

insert into schema_version values ('0.0.0001');

You would store all of your .sql schema scripts in source control, such as git.

Then, as needed, you would add patch files, such as 0.0.0002.sql. It would normally contain ALTER statements that adjust your database schema:

alter table contact add column last_name text;

insert into schema_version values ('0.0.0002');

You will probably need to read the documentation for your particular database, as they often have slightly different ALTER syntax.

You would then have a non-sql script (bash perhaps), that would check which schema version the database is using and apply any available patches that are greater than that version, or the first sql script if there is no schema_version table.

This book is highly rated on Amazon:

Refactoring Databases: Evolutionary Database Design


Optimum design / structure for product data database?

All you need is two tables. 1 for Products and 1 for Attributes. You would then have a one to many relationship from the Products table to the Attributes table.

This means that each product can have as many attributes as it needs.

Table structure would look something like this.

Products
Product_Id INT PK Auto Increment
ProductCode VARCHAR(255)

Attriutes
Attribute_Id INT PK Auto Increment
Product_Id INT FK
AttributeName VARCHAR(255)

AttributeValues
AttributeValue_Id INT PK
Attribute_Id INT FK
AttributeValue VARCHAR(255)


Why SSAS tries to connect to the default instance?

It would be helpful to know if your running in a Clustered environment or not. However here are some suggestions:

1) Make sure that SQL Browser service is running and that the service user has local administrator rights (Local System Account). Please note that SQL Browser service listens for client requests on port 2382 and then redirects the client connection to the port used by SSAS instance. (In your case it would be 2383)

2) If Analysis Services is running on a Windows Server Failover Cluster (WSFC), the SQL Browser service has to be started on all of the nodes of the WSFC

3) The port information for the instance is relayed by the SQL Browser services only if property InstanceVisible is set to 1 (true). Verify this under General / Advanced settings of SSAS.

4) Test for connectivity by using the telnet command:

  TELNET InstanceName PORT

If you get a "Could not open a connection to the host, on port" error message, it could mean that the instance is running on a different port or something is blocking access to this port.


is there a way to know all filters applied to one publication with query (SQL Server 2014)?

You can use the table sysaticle but if you don't find it, there is an alternative table sysmergearticles it has all the filters in column subset_filterclause.


A UNIQUE, multicolumn, partial expression index should do the trick:

CREATE UNIQUE INDEX ON edges (LEAST(src, tgt), GREATEST(src, tgt), rel)
WHERE rel_type = 2;

Works with INSERT ... ON CONFLICT DO NOTHING.

Related:

Unique postgres constraint for permutations across multiple columns
How to create a postgres table with unique combined primary key?
PostgreSQL multi-column unique constraint and NULL values

Aside: If those are IP addresses, consider the data type cidr or ip4 from the additional module ip4r for column rel.


How should I handle Products with different attributes in my database structure?

What you can also do, is following. You create a table with three columns: product_id, property and value. So you get not more tables then neccessary. But it's a matter of taste.


How to selecting data where the date is with the next month or later

This is what you want:

    SELECT *
    FROM Date
    WHERE Expiry >= DATE_ADD(curdate(), INTERVAL 1 MONTH);

MySQL date and time functions are documented here.


A skipped id can happen in several situation.  Keep in mind that an INSERT generally starts by allocating all the ids that it may need.  Here's a few examples where AUTO_INCREMENT ids are 'burned':

BEGIN; INSERT; ROLLBACK;

INSERT IGNORE -- and some of them are ignored due to dup key

REPLACE -- DELETE, then INSERT

The one I dislike is:

SET autocommit = 0;
INSERT
forget to ever do the COMMIT

For that reason, I avoid autocommit=0.

In a multi-master (including Galera) setup, with auto_increment_increment > 1, the INSERTs on one master skip values.


You should fully tune the MySQL Environment, particularly your InnoDB settings. (See the bottom of my Answer for tuning tips). This would be much better than fighting Amazon for elbow room in RAM/Disk. Why did I say fight?
If you just spun up an Amazon RDS instance of MySQL, you would subject yourself to whatever  constraints are given. All models of MySQL Amazon RDS have the same major options but differ in only two aspects

max_connections
innodb_buffer_pool_size

Here is a Chart I posted a Week Ago : https://dba.stackexchange.com/a/21498/877
MODEL      max_connections innodb_buffer_pool_size
---------  --------------- -----------------------
t1.micro   34                326107136 (  311M)
m1-small   125              1179648000 ( 1125M,  1.097G)
m1-large   623              5882511360 ( 5610M,  5.479G)
m1-xlarge  1263            11922309120 (11370M, 11.103G)
m2-xlarge  1441            13605273600 (12975M, 12.671G)
m2-2xlarge 2900            27367833600 (26100M, 25.488G)
m2-4xlarge 5816            54892953600 (52350M, 51.123G)

The larger the model, the bigger the InnoDB Buffer Pool. Oh yeah, the bigger the budget needs to be.
What most people are unaware of is that the InnoDB Transaction Log Files are the same size for all models : 128M. Given default settings (innodb_log_files_in_group = 2), that's 256M of Transaction Log Space. If you wish to set some other InnoDB option, do this:

Create a Custom DB Parameter Group (call it MySettings)
Download RDS CLI and setup a config file with your AWS Credentials
Execute the following : ./rds-modify-db-parameter-group MySettings --parameters "name=<InnoDB Option>,value=???,method=immediate"
Modify using DB Parameter Option List MySettings
Restart the MySQL RDS Instance

That's for your future consideration.
As for your import problem at hand, the AWS RDS Documentation has many suggestions for circumventing import slowness including:

Create all secondary indexes prior to loading. This is counterintuitive for those familiar with other databases. Adding or modifying a secondary index causes MySQL to create a new table with the index changes, copy the data from the existing table to the new table, and drop the original table.
Load data in PK order. This is particularly helpful for InnoDB tables where load times can be reduced by 75-80% and datafile size cut in half.
Disable foreign key constraints foreign_key_checks=0 For flat files loaded with LOAD DATA INFILE, this is required in many cases. For any load, disabling FK checks will provide significant performance gains. Just be sure to enable the constraints and verify the data after the load.
Load in parallel unless already near a resource limit. Use partitioned tables when appropriate.
Use multi-value inserts when loading with SQL to minimize statement execution overhead. When using mysqldump, this is done automatically.
Reduce InnoDB log IO innodb_flush_log_at_trx_commit=0

I hope you are not using LOAD DATA INFILE for importing. Why ? Because, only MyISAM tables benefit from it in conjunction with the bulk insert buffer (which by default is 8M). InnoDB does not benefit from it.
If you are loading InnoDB tables via mysqldump or your own SQL, all well and good. You may need to expand the Transaction Logs as I explained if your are imposing large transactions in bulk.
As a sidenote, please do not perform any large transactions if AWS is doing a snapshot. You should make sure snapshots are properly scheduled and then commit not to do any large transactions within the snapshot window. You could disable Snapshots for the duration of your bulk transaction, but the AWS RDS Documentation has a big caveat on that:

Warning: DO NOT DISABLE AUTOMATED BACKUPS IF YOU NEED TO RETAIN THE
ABILITY TO PERFORM POINT-IN-TIME RECOVERY. Disabling automated backups
erases all existing backups, so point-in-time recovery will not be
possible after automated backups have been disabled. Disabling
automated backups is a performance optimization and is not required
for data loads. Note that DB Snapshots are not affected by disabling
automated backups. All existing DB Snapshots are still available for
restore.
Disabling automated backups will reduce load time by about 25% and
reduce the amount of storage space required during the load. If you
will be loading data into a new DB Instance that contains no data,
disabling backups is an easy way to speed up the load and avoid using
the additional storage needed for backups. However, if you will be
loading into a DB Instance that already contains data; you must weigh
the benefits of disabling backups against the impact of losing the
ability to perform point-in-time-recovery.
DB Instances have automated backups enabled by default (with a one day
retention period). In order to disable automated backups, you must set
the backup retention period to zero. After the load, you can re-enable
backups by setting the backup retention period to a non-zero value. In
order to enable or disable backups, Amazon RDS must shut the DB
Instance down and restart it in order to turn MySQL logging on or off.
Use the rds-modify-db-instance command to set the backup retention to
zero and apply the change immediately. Setting the retention period to
zero requires a DB Instance restart, so wait until the restart has
completed before proceeding.
rds-modify-db-instance AcmeRDS --apply-immediately
--backup-retention-period=0 You can check the status of your DB Instance with the rds-describe-db-instances command. The example
displays the status of the AcmeRDS database instance and includes the
--headers option to show column headings.

EPILOGUE
Here are my past posts on tuning InnoDB:

Optimizing InnoDB default settings
Capabilities of InnoDB INSERT Performance
Scaling Percona datacenters: setup and replication


GRANT VIEW ANY DEFINITION will allow you to grant the ability to view any metadata on the server.  This includes security information at the server level.  This means that the user will be able to view other metadata as well, such as object definitions, which might be outside the scope of your goal.


Correcting a database that has had indexes and statistics created on it willy-nilly

Consider following the STOP pattern that's used in first aid training. Stop, think, observe and plan. That is, don't rush into action. Start by gathering some information, draft an action plan and only then start doing things. If there's an electricity accident, one doesn't touch the victim before cutting power. Likewise, one shouldn't start dropping indexes without knowing what use those would be.

Is the system working well enough? How can you tell? That is, what's the definition of "well enough?"

Do you have a set of business rules like "Report X must be ready within Y minutes" and "Daily ETL must be finished by 0600 as cube update starts at 0610." Maybe there's something like "Database backup must be finished at 0200, as disk-to-tape backup starts at 0300." Be aware there might be some ad-hoc rules like "CFO needs $stuff NOW" that are usually not documented at all.

If you don't have any business constraints, you need to discuss with your boss, other users and business about such constraints.

If you've got a set of rules, that's great news. Now, see if the system respects those limits at the moment.

After you know the rules for the system, maybe it would be a great idea to do some basic health check. Brent Ozar's sp_Blitz is widely used free tool. There's warnings if backups are not handled properly, lack of recent integrity checks, configuration issues and a lot more.


Postgres cost of large volume of inserts in many tables in a single transaction

You have it backwards - it is generally better to do many rows in a single transaction than one at a time with autocommit. The reasons are a) disk I/O and b) network round trips between client and server. You will need to run benchmarks to find the ideal batch size for your data and your hardware - try 100, 1000, 10000 sized transactions and see. At some point it will peak and past that the transactions will be "too big" as you bump into some other limit.


You've got a few issues here.

When using the UNION or UNION ALL operator, each query must return the same number of columns. The first query in your list has three columns in the SELECT but the queries that come later only have two. You can create a static value for these if you need to, something like this (using the second query as an example);

 SELECT
`tabmfi_loan_action`.responsible,
`tabmfi_loan_action`.action_type,
'' --Use a zero length string
FROM  `tabmfi_loan_action`

You cannot order your queries that you use with the UNION operator, you'll have to take your ORDER BY statements out. If you do need to order the result then you will have to use your current statement (all of it) as a subquery and ORDER in an outer SELECT. Something like this

SELECT
a.Field1
,a.Field2
FROM
    (
        SELECT Field1, Field2 FROM Table1
        UNION
        SELECT Field1, Field2 FROM Table2
    ) a
ORDER BY a.Field1

Try fixing these first and see if this resolves your issues.


You are trying to output avg_sal.salary which is not defined. Use alias in the cursor body :

.... cursor c1 is  select avg(e.salary)  AS salary ...

Side notes.
 It seems that parameter passed (ddept_id ) is not used anywhere inside the procedure except printing. Also, it's been long time since JOIN syntax was introduced and it's much more readable compared to SQL89 style (and recommended by Oracle as well). Finally, using cursor in this case seems quite questionable to me...


Kill concurrent materialized view refresh PID

That is not true.

CREATE MATERIALIZED VIEW slow AS
SELECT i,
       pg_sleep(1) IS NULL AS no
FROM generate_series(1, 10) AS i;

CREATE UNIQUE INDEX ON slow (i);

REFRESH MATERIALIZED VIEW CONCURRENTLY slow;

That statement does not complete immediately, but hangs until the materialized view is refreshed.

Then, while the statement is running, in another session:

SELECT pid, state, query FROM pg_stat_activity ;

  pid  | state  |                      query
-------+--------+--------------------------------------------------
 12618 |        |
 12620 |        |
 13049 | active | REFRESH MATERIALIZED VIEW CONCURRENTLY slow ;
 13032 | active | SELECT pid, state, query FROM pg_stat_activity ;
 12616 |        |
 12615 |        |
 12617 |        |
(7 rows)

SELECT pg_cancel_backend(13049);

 pg_cancel_backend
-------------------
 t

The statement is canceled. In the first session, I see:

ERROR:  canceling statement due to user request

Perhaps you are confused by the fact that the query column in pg_stat_activity does not only show the current query, but the latest query that was run in that session.  So if the state is idle, then the statement is done and the materialized view is already refreshed. Then of course canceling the query has no effect.


Help undoing an accidental restore on SQL Server 2014

First off...   I think it is best to come clean to your manager on such issues.  The business will notice the missing data and he will not appreciate being blindsided and not having all the facts at hand.  In that event,  things will be even worse for you.  If you have no formal background in DBAing, you have no business doing DBA tasks and/or the real production DBAs ( if they even exist ) should have ensured that the business is protected against such human errors.

Simply put there is no way to restore individual rows auto-magically. There are tool set available ( Red Gate Data Compare springs to mind ) that allow you to compare data sets and build scripts of the differences, but it is upto you to decide which changes are good.


SQL Server Standard Edition - Limitation

As has been exhaustively commented, licensing questions are out of scope, however for technical clarification...
AlwaysOn is built on top of Failover Clusters, but is distinct from Failover Cluster Instances (FCI).  The difference mostly comes up with whether they share storage or not.
This link https://straightpathsql.com/archives/2019/02/basic-availability-groups-myths-truths/ does a good job of dispelling / clarifying basic availability groups.
YES, basic AG only allow a single database.  HOWEVER, you can have many, many availability groups with a database each.  This does complicate things a bit as it means that the databases won't necessarily move in sync with each other.  But you get what you get.
Given your scenario, I don't think managing one AG per database would be terribly problematic.  You are going to have to script your failover commands but checking AG health through TSQL should let you keep an eye on the herd (flock?  gaggle?) of AG's.  I wouldn't stress about it.
As for using Failover Cluster Instances instead of AG's, go for it.  They have no limitation and if you just want HA then it's easier to manage as you don't need to sync logins, jobs, etc.  It does mean that you must rely on your storage layer's HA rather than potentially having completely separate HA, but using FCI will allow you to do rolling upgrades and survive compute (CPU, memory, O/S) failure.


I am assuming that by mirror copy ==> you mean at the point when the database full backup was taken.

If that is not the case, then for near to realtime copy of your database, I would suggest transactional replication.

Also, if you are using enterprise edition, then you can leverage the benefits of database SNAPSHOTS.

Step 1: Make a FULL database backup

backup database database_name to disk = 'path to backup drive' with copy_only, stats =10
-- ,compression (sql server 2008 R2 and up, compression is available in standard edition as well).

  e.g. path to backup drive = D:\backups\db_name_FULL.bak

Step 2: Restore the backup taken in step 1 with move.

restore database database_name_mirror
from disk = 'path to backup drive'
with stats =10, recovery,
move 'Logical_Data_FileName' to 'Physical data file location.mdf',
move 'Logical_Log_FileName' to 'Physical log file location.ldf'

Logical file name can be found using sp_helpdb database_name or restore filelistonly from disk = D:\backups\db_name_FULL.bak

e.g.

restore database database_name_mirror
from disk='D:\backups\db_name_FULL.bak'
WITH move 'database_name_Data' to  'D:\Data\database_name_mirror_Data.mdf',
move 'database_name_log' to  'L:\logs\database_name_mirror_log.ldf';
GO


Yes, what you're talking about is a Filtered Index.  It works just like you think, by creating an index off of a subset of data.  Below is an excerpt from the above reference:

  A filtered index is an optimized nonclustered index, especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.

To your other question:

  how does one ensure the index is used?

A few things will dictate this index being used:

A well-designed filtered index correlated with
A well-designed query

Obviously things like data distribution and many other factors play a role, but that goes with both filtered and non-filtered indexes.


PostgreSQL: Use column with hash based on other columns as unique index?

You could use a UNIQUE INDEX instead of the UNIQUE CONSTRAINT using the function coalesce to treat null as a regular value for your uniqueness:

CREATE UNIQUE INDEX ON offer (
       product_id,
       coalesce(price_old,-1),
       coalesce(price,-1),
       coalesce(price_alt,''),
       valid_from,
       valid_to);

This will enforce your uniqueness as you described.

For details between a Unique constraint and a unique index,see:
https://stackoverflow.com/questions/23542794/postgres-unique-constraint-vs-index


Are SQL Server's temporal tables here to stay?

I don't think the concern is legitimate. This feature is introduced in SQL Server 2016 and it's very clearly mentioned that it applies to SQL Server 2016 and later versions. Later version meaning for now it is at least SQL Server 2019 which has been released.
Temporal tables

Applies to: SQL Server 2016 (13.x) and later, Azure SQL Database, Azure
SQL Managed Instance  - Azure Synapse Analytics
SQL Server 2016 introduced support for temporal tables (also known as
system-versioned temporal tables) as a database feature that brings
built-in support for providing information about data stored in the
table at any point in time rather than only the data that is correct
at the current moment in time. Temporal is a database feature that was
introduced in ANSI SQL 2011.

If at all, there is chance of removal of any feature or anything they do mention at Microsoft documentation, for instance "Database Mirroring". You can read the details about the same as below:

Note
This feature is in maintenance mode and may be removed in a future
version of Microsoft SQL Server. Avoid using this feature in new
development work, and plan to modify applications that currently use
this feature. Use Always On availability groups instead.

I hope this addresses your concern.
Note: Given temporal tables are part of the ISO/ANSI SQL standard, and it's highly unlikely the feature will be removed from SQL Server.


Does MSSQL log shipping automatically remove the backup files from both servers?

Log Shipping utilises an executable called SqlLogShip.exe. As per the documentation, when it runs as part of the LSBackup job, it removes old backup files outside the defined retention period on the primary:

  The backup operation creates the log backup in the backup directory.
  The sqllogship application then cleans out any old backup files, based
  on the file retention period. Next, the application logs history for
  the backup operation on the primary server and the monitor server.
  Finally, the application runs sp_cleanup_log_shipping_history, which
  cleans out old history information, based on the retention period.

When it runs as part of the LSRestore job, it removes old backup files outside the defined retention period on the secondary server:

  Any backup files in the destination directory that were created after
  the most recent restore point are restored to the secondary database,
  or databases. The sqllogship application then cleans out any old
  backup files, based on the file retention period. Next, the
  application logs history for the restore operation on the secondary
  server and the monitor server. Finally, the application runs
  sp_cleanup_log_shipping_history, which cleans out old history
  information, based on the retention period.

Provided your retention periods are set and SqlLogShip.exe is executing correctly, it should maintain the log files on both the primary and secondary locations.


Can I create an AlwaysOn replication of a DB where some tables don't have a primary key?

The replication that is used by availability groups is akin to database mirroring, not table replication (SQL Server Replication).  AG replication has no requirements regarding primary keys on tables.  The limitations and restrictions are clearly documented at Prerequisites, Restrictions, and Recommendations for Always On availability groups -> Availability Database Prerequisites and Restrictions.

For the situation you are describing, you would want to Configure read-only routing for an Always On availability group and have the BI team to include ApplicationIntent=ReadOnly in the connection string so that their queries run on the secondary.


Which mysql collation is good for storing unicode strings?

The issue was with connection character set. After setting it to utf8mb4, it succeeded. One way is to add default-character-set = utf8mb4 to client or mysql sections of ~/.my.cnf.

UPD Before adding default-character-set to ~/.my.cnf I had:

> show variables like 'character_set_%'
character_set_client    utf8
character_set_connection    utf8
character_set_database  latin1
character_set_filesystem    binary
character_set_results   utf8
character_set_server    latin1
character_set_system    utf8

And insert into t1 (f1) values ('') gave me:

ERROR 1366 (22007) at line 1: Incorrect string value: '\xF0\xA2\x84\x82'
    for column 'f1' at row 1

After adding default-character-set = utf8mb4 I have:

> show variables like 'character_set_%'
character_set_client    utf8mb4
character_set_connection    utf8mb4
character_set_database  latin1
character_set_filesystem    binary
character_set_results   utf8mb4
character_set_server    latin1
character_set_system    utf8

And insert into t1 (f1) values ('') succeeds.


Why number of matches in fulltext search get less when we put less strings?

Maybe more than 50% of your tablerows contain words starting with "resta*".
Words that are present in 50% or more of the rows are considered common and do not match.

So you would only have those rows returned, that have a non-common word also starting with "resta*" in your result.

see: http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html

Try

SELECT * FROM tableauxiliary WHERE MATCH (FullTextSearch)
AGAINST ('resta*' IN BOOLEAN MODE)

see http://dev.mysql.com/doc/refman/5.6/en/fulltext-boolean.html

Boolean full-text searches have these characteristics: - They do not use the 50% threshold that applies to MyISAM search indexes

I got this here:
Why does MATCH() give Score 0, if there are less rows in the database?


pg_dump: Write failed: Connection reset by peer

This looks like the error is coming from your ssh session, not pg_dump.  Use 'screen' or 'tmux' so you can resume the session after ssh failure.

Perhaps pg_dump is still running, or completed normally, even after it's controlling session went away.


You can use a conditional aggregate:

select m.id,
       t.name,
       count(case when m.action = 0 then m.id end) as action1,
       count(case when m.action = 1 then m.id end) as action2
from table1 m
  left table2 t ON (m.id = t.id)
where m.status <> '2'
group by m.id, t.name;

In the upcoming 9.4 release the count(case(...)) could be written a bit shorter using the new "filtered" aggregate: count(id) filter (when m.action = 0)


I need help understanding Postgres's archive cleanup functionality

You're correct. If you want PITR capability, you need to keep all archives between when the base backup was taken and when you want to be able to recover to.

For this reason it's often a good idea to do a fresh base backup (say) weekly, and rotate them out, so you keep a base backup plus a week of archives.

PgBarman should be able to help you with this.

Archive cleanup would not be desirable if you want PITR.


Generally, for best results on Stack, you want to break each question up into its own question, but here we go:

Q: When I run this command, is the data left unencrypted on the disk at any point?

Not by the key change process, but be aware that filestream and replication data aren't encrypted regardless. For more details, check Books Online's section on TDE.

Q: i.e. Does this command first unencrypt all the data using the old key/certificate and then re-encrypt it using the new one, which would mean that the data is unencrypted at some point in the process.

No. For more details, see Microsoft's post on encryption key management.

Q: Bonus question, what happens to the log file during the key change?

Changes to the database are logged operations, so you'll need to watch the size and speed of your transaction log just like you did when you first applied TDE to the database.


How to represent the relationship between artists and albums in an entity-relationship diagram

Replace ALBUM with SONG, keep ALBUM but add a "many to many" relationship between the two.

There are still a lot of complexities you are missing (eg "Artist formerly known as Prince" vs "Prince", cover songs, etc)


SQL Network Name owned by Available Storage OwnerGroup in Server 2012 FCI

It looks like a rogue client access point was added or someone manually moved/changed something.

Your two instances are as such:

SQL Server (I2)              Online                        SQL Server (I2)
SQL Server (I1)             Online                        SQL Server (I1)

They have corresponding network names in the proper groups:

SQL Network Name (clsnwname... Online                        SQL Server (I1)
SQL Network Name (clsnwname2... Online                        SQL Server (I2)

Each client access point also has at least one online IP:

SQL IP Address 1 (clsnwname... Online                        SQL Server (I1)
SQL IP Address 1 (clsnwname2... Online                        SQL Server (I2)

The outlier (failed) client access point:

SQL Network Name (I1)       Failed                        Available Storage

Which you've stated isn't part of any of the SQL Resource Groups, isn't online (failed) doesn't have an IP associated with it (as far as we can tell from your output).

This leads me to believe:

Someone attempted to make this and didn't know what they were doing
Incorrect automated setup was run (with DSC, powershell, or other tooling [chef, puppet,etc])
Testing was done and not cleaned up properly

I doubt it was malicious. In any event, check out the dependency report - which I'm guessing will be blank. If it is, feel free to remove it and the associated computer object in AD. If it isn't blank, update the post with more information please.


Un-Install OS Patch (KBs) in Physical PRODUCTION Servers - Consequences

As the MSDN page you link to states that a fix has already been released, I would install that rather than removing the previous update as the fix is likely to be intended to go over the top (though is likely to be made such that it can replace the older update too) and it will only be one operation rather than two potentially requiring two reboots.

In either case the main consequence of adding or removing patches is the potential need to reboot (or if not a full reboot, restart affected services) which may involve downtime depending on your arrangement (a cluster might be able to avoid downtime). The extra consequence of removing patches (without installing a replacement) is that the changes within them are gone, which if it is a roll-up patch could mean unrelated security updates that you need to mitigate by other means until the patch can be re-applied.

The best approach to take is to test in a production-like environment before making changes to production proper.


Restore data from an old rman backup - ORA-01152

Lets see the information provided by oerr utility.

ORA-01152: file 1 was not restored from a sufficiently old backup
Cause: An incomplete recovery session was started, but an insufficient number of logs were applied to make the database consistent. This file is still in the future of the last log applied. The most likely cause of this error is forgetting to restore the file from a backup before doing incomplete recovery.
Action: Either apply more logs until the database is consistent or restore the database file from an older backup and repeat recovery

According to your post you have not recovered the database. You may try the following commands to recover your database.

SQL> recover database using backup controlfile until cancel;
SQL>alter database open resetlog;

Since you are new to Oracle, you may find the following link useful to restore old backup to new host.
Restoring a Database on a New Host


What standard should I follow when naming tables and views?

OK, first NEVER put tbl in front of a table's name.  It's a table, we already now that.  That's called Hungarian Notation, and people stopped doing that 5+ years ago.

Just call the object based on what it is.  If a table holds employee data call it "Employee".  If it holds information about computers call it "Computer".  If it maps computers to employees call it "EmployeeComputer" or "ComputerEmployee" (personally I like "EmployeeComputer" better).

There's no real right naming convention to use (other than to not use Hungarian Notation).  As long as the object names make sense that it what is important.


Record of schema related changes - Oracle

That's exactly the kind of thing that oracle AUDIT is created for. The basic view is DBA_AUDIT_TRAIL, though there are several others.  By defalt, Oracle has some auditing already enabled, but you may want to review and modify. Also you want to use the dbms_audit_mgmt package to move the audit tables out of the SYSTEM tablespace into their own dedicated tablespaces, as well as to do normal housekeeping.  Details are in Part VI of the Security Guide.


Integrate MySql Full Text searches in CMS

It works.  It performs reasonably well.

You have two tables, one is InnoDB and contains most of the attributes of an entity.  The other is MyISAM, is 1:1 with the first table, and contains a TEXT field, plus FULLTEXT index.

The relevant query looks something like:

SELECT ...
   FROM inno_tbl i
   JOIN ft_tbl f ON i.id = f.id
   WHERE i.stuff...
     AND MATCH (f.text) AGAINST (...);

I think that the MATCH will always occur first, even though the tests on i might be more selective.  That is the nature of FULLTEXT.

jkavalik mentioned some consistency issues; but these can mostly be avoided by carefully picking the order in which you INSERT into the two tables, and whether you use REPLACE or IODKU instead of a plain INSERT for one of the `INSERTs.

(I believe I have done what you describe in one or two projects.  I have since measured that InnoDB's FULLTEXT seems to be faster.)

Bottom line: Go ahead and do it.

Addendum  How to order the statements to minimize data integrity problems.

BEGIN;
INSERT into InnoDB table
$id = SELECT LAST_INSERT_ID(); -- assuming you are using an AUTO_INCREMENT
INSERT INTO MyISAM_table
    (id, text)  VALUES  ($id, '$escaped_text')
    ON DUPLICATE KEY UPDATE
        text = '$escaped_text';
COMMIT;

Cases...

There is no integrity problem if both INSERTs succeed or both fail.
If there is an error on the MyISAM INSERT, you should catch it and ROLLBACK instead of COMMIT.  Hence good integrity.
If the MyISAM succeeds but the COMMIT fails, there will be an extra row in the MyISAM Table, for which there is no InnoDB row.  Two cases...

If a FULLTEXT search hits that row, then the JOIN to the InnoDB table will fail, thereby getting the 'right' answer (at a minor cost).

If you come along later and reuse that id to re-insert the row (or insert a different row), then the IODKU will "do the right thing".  All is well.

Note (aimed at other readers):  This technique of mixing an InnoDB table with a non-transactional INSERT works in other cases.  Consider putting an image (.jpg) in a file, while putting the image's 'meta data' in an InnoDB row.  At worst, you might have an extra or duplicate image stored in the file system.


I am assuming that DateFilled has a data type of DateTime or other date type which includes a time portion.  When using DateTime values in T-SQL be aware that a time is always included and if not specified midnight is used by default.  See: http://technet.microsoft.com/en-us/library/ms180878(v=sql.105).aspx#ConversionBetweenStringLiteralsandDateandTimeDataTypes
When the input string is "DATE only" then "the The TIME part defaults to 00:00:00."  In your case the string "02/28/2014" is converted to a datetime value of 2014-02-28 00:00:00.000.

Your query is interpreted as DateFilled between '02/01/2014 00:00:00 .000' and '02/28/2014 00:00:00.000' thus the record with a datetime of 2014-02-28 09:24:00.000 (which occurs after midnight on, is not found.

Your query may be re-written as

SELECT Column1, Column2
FROM MyTable
WHERE DateFilled >= '02/01/2014'
   AND DateFilled < '03/01/2014';


What process would you use to determine references on a database that has no integrity?

You could programmatically build queries to test which combinations of columns look like they might be keys. In order to test a given relationship you'd run a query of the form:

SELECT 1 AS [IsPossibleKey: ChildTable.KeyColumn => ParentTable.PrimaryKeyColumn]
WHERE NOT EXISTS (
 SELECT *
 FROM ChildTable c
 WHERE NOT EXISTS (
  SELECT *
  FROM ParentTable p
  WHERE c.KeyColumn = p.PrimaryKeyColumn
 )
)

This can give you a fairly raw list of key relationship candidates. I would write a SQL query to programmatically generate all possible queries of the above form, paste all of them into a new SSMS window and run time.

You probably should exclude candidates with zero non-NULL values in the child table column.

Another problem is that bad data might have slipped in. Heck, since there are no FKs it almost certainly has slipped in somewhere.

Also, this only finds single-column combinations.


Is there a performance difference in the 2 temp table initializations?

Is there a performance difference between the 2 initializations?

No. There is no performance difference. If you use set statistics time, io ON and check there is no CPU or IO involved

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

  What purpose does the ,'u' piece of code serve in the first statement?

The syntax for object_id is

  OBJECT_ID ( '[ database_name . [ schema_name ] . | schema_name . ]
        object_name' [ ,'object_type' ] )

The u specifies its a user object. It is called user defined table in sys.objects

I think it is good coding practice to define what you are doing. So in the 1st example the developer is saying that if #TempTbl table exists then drop it. I have seen poor naming where people name views like tables, do not use schema prefix and other bad habits !

In SQL server 2016, you will see a new syntax ..

DROP TABLE IF EXISTS


Missing DatabaseMail.exe.config after applying SQL Server 2016 SP2 CU1

The main point of the question was to check if other people had the same issue and to help spread the information on issues with the SQL Server 2016 SP2 CU1 patch. Since these objectives have been reached and there is a solution presented in the post, I will close this as answered.

The solution was as follows:

We recreated the file based on the following KB article:
https://support.microsoft.com/en-us/help/3186435/fix-sql-server-2016-database-mail-does-not-work-on-a-computer-that-doe

After that we had to restart the Database Mail queues to start processing queued mails:

USE msdb;
EXEC sysmail_stop_sp;
EXEC sysmail_start_sp;

This cleared the issue and any queued messages were successfully sent.

Patch link: https://support.microsoft.com/en-us/help/4135048/cumulative-update-1-for-sql-server-2016-sp2


Logical reads different when accessing the same LOB data

Not all reads are equal. SQL Server knows that accessing LOB data is expensive, and tries to avoid it when possible. There are also detailed differences in the way the LOB data is read in each case:

Summary

The numbers are different because:

The select reads the LOB in packet-sized chunks
The variable assignment test does not read the LOB at all
The "select into" test reads the LOB in whole pages

Detail

Plain SELECT

The Clustered Index Scan does not read any LOB data. It only assigns a storage engine LOB handle. The handle isn't used until control returns to the root of the plan.

The current row's LOB content is read in TDS packet sized chunks and streamed to the client. Logical reads count the number of times a page is touched, so:

The number of reads reported equals the number of chunked reads performed, plus one for each time a LOB page transition occurs.

For example: A logical read is counted at the start of each chunk as the process touches the page corresponding to the current position of the stream. Where packets are smaller than a database page (the usual case), several logical reads are counted for the same page. If the packet size were so large that the entire LOB could fit in one chunk, the number of logical reads reported would be the number of LOB pages.
Variable assignment

The Clustered Index Scan assigns a LOB handle as before. At the root of the plan, the LOB handle is copied to the variable. The LOB data itself is never accessed (zero LOB reads), because the variable is never read. Even if it were, it would only be via the LOB handle last assigned.

There are no LOB reads because the LOB data is never accessed.
SELECT INTO

This plan uses the bulk rowset provider to copy the LOB data from the source table to the new table. It processes a complete LOB page on each read (no streaming or chunking).

The number of logical reads corresponds to the number of LOB pages in the test table.


In this section of your procedure:
where rtrim(substring(PrdAcctId,1,8)) in (@prd)
              and floor(cast(substring(PrdAcctId,17,8) as int))=0
              and CblDate<=@fromdate

you are using the @fromdate as the filter and looking for the values earlier.  Then you increase the @fromdate value.  But the earliest date before Wednesday of the current week is still the same when I get the earliest date before Thursday of the current week - so wouldn't you end up with the same record?
I also assume there is more to the procedure as you only insert into the temp table and never query it?   What is the remainder of the procedure?  Please post the entire code.   Do you get multiple records with the same value as the output?


It is probably your only option to use the Percona Xtrabackup tool. Fortunately, this is a free tool that has very good documentation.

This tool takes backups in a physical way, rather than logical backups that mysqldump produces. This means that restore time is simply the amount of time required to copy the backup files into the MySQL datadir and not the time to process all the SQL statements in a mysqldump backup. Backup time is generally faster as well, and allows for the possibility for incremental backup.


Does ALTER TABLE - ADD COLUMN lock the table?

Yes, it locks the table. From the docs on MySQL 8,

  The exception referred to earlier is that ALTER TABLE blocks reads (not just writes) at the point where it is ready to clear outdated table structures from the table and table definition caches. At this point, it must acquire an exclusive lock. To do so, it waits for current readers to finish, and blocks new reads and writes.

And from the docs you linked, it's pretty explicit

  With instant ADD COLUMN, you can enjoy all the benefits of structured storage without the drawback of having to rebuild the table.

As you've stated, you're on 10.2. So it looks like adding a column will require rebuilding the whole table.

As to what happens when you can't receive a lock,

  I've noticed that INSERT statements complete and the rows are correctly inserted in the table (as verified via SELECT) before an ALTER TABLE ... ADD COLUMN on the table finishes.

Yes, that's generally what happens during a lock, but be aware that's not always what happens. Sometimes statements and transactions give up waiting. Sometimes backends and pools get reaped when they're stuck waiting. It's always safer to do this during downtime, to have timeouts, and to catch errors from libraries when the timeouts expire. So long as you're using transactions, things rollback if something is triggered and can't get it's lock before timeout -- all will be kosher.


What is the benefit of having multiple databases in the same Availability Group vs single AG per database

An Availability Group is a unit of failover. Therefore, when considering how to group databases into an AG, keep in mind that all of these DBs will fail over together.

If there are multiple databases that must failover together, then I would recommend putting those databases into a single AG.

Similarly, you may have databases that are completely independent, where you want to put them in separate AGs so that a failover of one does not affect the other.

In some cases, you may wish to put databases into AGs based on logical groups that fail over together, even if they technically can fail over independently.

Having too many AGs or too many DBs per AG can both be management headaches. For example:

If you have 50 DB, each in it's own AG: you'll have 50 unique connection strings to manage, and 50 failovers to execute for a planned failover.
If you have 50 DBs in a single AG: If there is a problem with one database that necessitates a failover, then the other 49 databases need to be failed over as well, with all 50 databases incurring a short outage during the failover.


In SQLITE, how do you create a new table into its own file?

If you're trying to store key-value pairs of data you should use a key-value store database instead of SQLite, since SQLite is meant to store relational data. Where is your database going to live?...mobile device or dedicated server?
If you do stick to SQLite though, file size is not much of a factor in terms of read and write speeds. Rather you're probably thinking of concurrency issues, especially with concurrent writes to the database. In that case, you can do things like enabling the WAL and possibly creating a separate database for any groups of tables you would need to heavily concurrently write to, since concurrency issues mostly occur within the same database. Also ensure you index your tables appropriately, which will likely just be the key of your key-value pairs.
Also switching to an appropriate key-value data store would likely help you with any concurrency issues as well.


Colin's suggestion fixed it. Installed windows installer 3.1 and found the native client 2005 somewhere and now it is ok.
Works also for sql server 2019 on windows 2019.


Inaccurate count when using multiple count functions in PostgreSQL

I suppose you have a problem with your channels table that may return more than one row per s.account_id. I propose this query :

SELECT
    COUNT (DISTINCT s.account_id),
    COUNT (DISTINCT CONCAT(s.account_id, c.type)) AS installed_shopify
FROM subscriptions s
LEFT JOIN channels c ON s.account_id = c.account_id AND c.type ILIKE 'shopifychannel'
WHERE s.status = 'paid'
  AND s.churned_at IS NULL

Again, you must be sure that there is only (zero or one) row in channels for each account_id having the condition c.type ilike 'shopifychannel'.

The LEFT JOIN assure you don't miss a row in your primary table subscriptions. The condition c.type ILIKE 'shopifychannel' is in the LEFT JOIN because if you put it in the WHERE clause, you'll also lose subscriptions lines that don't have installed Shopify.


When should nonclustered indexes be stored on separate filegroups?

The slowest part of a DB system is the disk drives. Eliminating bottlenecks at the disk level will improve performance. When data is being looked up and an index is used, the index is first looked up and then the corresponding data is fetched. If both the index and data are on the same disks, then there is some contention happening. Whereas, if the data were on a different (physical) disk, then there is faster IO happening, thereby increasing performance.
The main part to note is that the data or index are on separate physical disks or LUNs.

You would use such a scenario if you need to get better performance out of your system, provided you have the disks. For your perfmon counters you could use Physical Disk – Avg. Disk sec/Read, Physical Disk – Avg. Disk sec/Write, Physical Disk – Disk Reads/sec, Physical Disk – Disk Writes/sec to have a before and after comparison of your changes.


Fail to execute SQL command asynchronously

You need create a separate pCmdChange object for each INSERT:

for (UINT uIndex = 0; uIndex < 3000000, uIndex ++)
{
     // Create command object.
     pCmdChange.CreateInstance(__uuidof(Command));
     pCmdChange->ActiveConnection = pConnection;
     pCmdChange->CommandText = strSQLChange;

     pCmdChange->Execute(NULL, NULL, adCmdText | adAsyncExecute | adExecuteNoRecords);
}

Keep in mind that your server probably won't like 3,000,000 near-concurrenct commands. You may run into SQL server dead-locks and or other connectivity issues.


RDS crosstab: ERROR: permission denied for language c

RDS doesn't let you install arbitrary C functions. It's a restricted environment.

crosstab is part of the tablefunc extension. You may be able to CREATE EXTENSION tablefunc if it's on the whitelist but you won't be able to add new variants, even if the underlying C function is already loaded and approved.


How to enable force ssl on AWS Aurora Postgres?

For Aurora Postgres, there's two relevant cluster-level parameters (note they're not instance-level parameters): rds.force_ssl and ssl. I haven't tested this myself but you should be able to modify them in the usual way using DB Parameter Groups.


Guess I have figured it out.

By first selecting the first url:

select min("cdata".url) as url

And then order the query by date:

order by date asc

It does return the right url and it combines the rest of the columns.

EDIT:
Above answer did not work as expected. This didn't either: https://wiki.postgresql.org/wiki/First/last_(aggregate)

EDIT 2:
Alright, I ran several tests and this seems to go well:

last("cdata".url) AS url

in combination with the max(date). So if someone has a better solution, please let me know :)

EDIT 3:
Alright, I think I just fixed it. I just select the url by executing a subquery and select the right row by comparing all "group by" fields, guess this is the best solution to my problem:

(SELECT url from sqpr as cd where"cd".kword = "cdata".kword and cd.kid="cdata".kid and cd.accid="cdata".accid and cd. adgstatus ="cdata". adgstatus and cd. adgcid ="cdata". adgcid and cd. adgname ="cdata". adgname and cd. adgid ="cdata". adgid and cd. sterm ="cdata". sterm and cd. mtype ="cdata". mtype ORDER BY date DESC LIMIT 1) AS url


Optimization for table with text and time columns

If the only reason for the index is the above query, then a partial index would better:

CREATE INDEX my_index ON my_table(time desc, service_name) WHERE status='ok';

VACUUM ANALYZE my_table; may help too.

The query plan depends on actual data in the table. In a table with few or zero records a seq scan is cheaper.

If you really dont want the seq scan (and nothin else helps), try to turn off seqscans with SET enable_seqscan = off; before the query.


Imports & exports under storage constraints and preallocations

It seems that you use the old export (exp) utility and not the datapump export expdp. For expthere is a parameter compress that manages the size of an initial extent of a table that is imported.  The default value is compress=y which means that the initial extent of the table (this is in most cases the space preallocated when creating an empty table) is as large as the space the table currently uses. So you should use the paramter compress=no. Then the initial extent of an imported table is as large as the initial extent of the table in the source database.


Connection pools being reset with Error: 18056, Severity: 20, State: 46. & Perfmon Counters not showing

1 - can't say for sure, I'd have to go find a server to dig into myself.

2 - yes, I see this periodically in my environment though we're not on sql 2012 yet on the systems we see this from. You may also want to check this post though State 46 seems to be related to having a specific Database=xxx in the connection string, does that db still exist?

The way my network is set up I suspect it's the network's automatic closing of tcp sessions after being 5 minutes idle that's the issue - neither the db nor the client is closing the session so the connection pool still thinks the connection is open and tries to use it only to find it's not really open anymore. You don't mention how the network between your web servers and db is configured, maybe your case is similar.

Another possibility may be the (old, not sure if ever really resolved, see http://support.microsoft.com/kb/942861) issue about TCP Chimney Offload settings.

3 - My understanding is pooling requires exact string matches, so whitespace and different order of parameters would cause different pools. (If I'm wrong on that, please let me know.)


Mysql (Ver 14.14/Redhat): is a new column with default values efficient on column creation?

You're using mysql 5.1,  This means the only engines you'll have available are MyISAM or innodb. in both cases adding a new column, regardless of nullability will require a complete, blocking, table rebuild during alter table.

You maybe be able to use the pt-online-schema change tool that does some tricks to rebuild the table in a less blocking manner but may not work out if you have complicated composite primary keys.

If you have a more recent version (5.6.22 Percona Build) you could use a tokudb storage engine that allows you to to "instantly" add columns.  The way it's indexing works it propagates changes down as they're accessed.


Export:

mysqldump -u username –-password=your_password database_name > file.sql

Import:

mysql -u username –-password=your_password database_name < file.sql


To start with I do not see any technical issue in your current approach. If I may suggest and if it suits your requirement, since your RPO is 24 hours keep DB in simple recovery take daily full backup and every 8 hours diff backup(optional). The diff backup is not even required but just to give you some restore point in between. Now store these backups locally and also on different server in other DC.
Copy these backup files on different SQL Server in other DC and restore it. So now you have always have a complete data which is less than 24 hours new in other DC. This will meet your RPO but yes you should test this to see if that meets your RTO of 8 hours or not.
The advantage with this approach is its very simple configuration, saves licensing cost of few Windows server. You dont have to worry about complexity of AG, even a new DBA and junior DBA's can work on it easily, etc etc...


As your my.cnf is not on standard location, check
ps -ef | grep mysql for --defaults-file option.

Below command will give you default file options:

mysqld --verbose --help | grep -A 1 "Default options"

(There are many similar questions previously answered.)


Error when using DBCA to create database

This is in your log file:
------------------------------------------------------------------------------
Global Settings
------------------------------------------------------------------------------

- Global database name : course_management
- Configuration type : Oracle Single Instance database
- SID : coursemanage

Global Database Name is meant to be a fully qualified name, like "sales.us.example.com". The first part of that name becomes the "SID" value, and must not exceed 8 characters in length (e.g. "sales.us"). In other words, pick a much shorter name.
See here: https://docs.oracle.com/en/database/oracle/oracle-database/18/ssdbi/about-assigning-global-database-names-during-installation.html#GUID-1AC9E18F-0AE4-4263-B3CC-A83B859A178F