Need Help maintaining multiple databases for a multi-tenant application?

You should store all information that is common for all companies in one database and copy the data that are needed to the database that needs the information.
Adding a new customer would be done in the common db.
After that the information will be copied to company1.
If it's needed in company2, then the data will be copied to company2.
Make sure you also add an update process to keep the copied information in sync.
Alternatively, you could keep the customer information in the main db and link to it. However, this would only be recommended if all db's are on the same SQL Server instance.


Group not inheriting access privileges for all tables

The group to which the users a or b belong does not affect the permissions on the tables created by these users.

There must be default privileges defined for a, but not for b.

Check with \ddp in psql.


Cost of updating one of several toasted columns

An experiment says more than a thousand theoretical considerations:

/* helper function to create large byteas */
CREATE OR REPLACE FUNCTION noise(size integer) RETURNS bytea
   LANGUAGE plpgsql AS
$$DECLARE
   b bytea := '';
   i integer;
BEGIN
   FOR i IN 0..size/16 LOOP
      b := b || ('\x' || md5(random()::text))::bytea;
   END LOOP;
   RETURN b;
END;$$;

CREATE TABLE toastme (
   id integer PRIMARY KEY,
   a bytea,
   b bytea
);

INSERT INTO toastme VALUES (
   1,
   noise(2048),
   noise(2048)
);

Let's check the physical location of the new row in the main table:

SELECT ctid
FROM toastme
WHERE id = 1;

 ctid
-------
 (0,1)
(1 row)

Now let's have a look at the toast table, along with the physical location of the rows:

SELECT ctid
FROM toastme
WHERE id = 1;

 ctid
-------
 (0,2)

SELECT reltoastrelid::regclass AS toast_table
FROM pg_class
WHERE relname = 'toastme' \gset

SELECT ctid,
       chunk_id,
       chunk_seq,
       substring(chunk_data FOR 10)
FROM :toast_table;

 ctid  | chunk_id | chunk_seq |       substring
-------+----------+-----------+------------------------
 (0,1) |    57174 |         0 | \x0f9eae178bcda3dbf745
 (0,2) |    57174 |         1 | \xecbe99e83b8416d4f31c
 (0,3) |    57175 |         0 | \x4b3b9425742c299c7d20
 (0,4) |    57175 |         1 | \x43d1b1782ad3e0c1bf4c
(4 rows)

There are two chunk_ids for the two table columns, each of which consists of two “slices” of TOAST.

Let's update one of the toasted columns:

UPDATE toastme
SET a = noise(2048)
WHERE id = 1;

Now let's check again:

SELECT ctid,
       chunk_id,
       chunk_seq,
       substring(chunk_data FOR 10)
FROM :toast_table;

 ctid  | chunk_id | chunk_seq |       substring
-------+----------+-----------+------------------------
 (0,3) |    57175 |         0 | \x4b3b9425742c299c7d20
 (0,4) |    57175 |         1 | \x43d1b1782ad3e0c1bf4c
 (0,5) |    57176 |         0 | \xc780f3b8e2a5e4976f4a
 (0,6) |    57176 |         1 | \x5702616863dfbc54829b
(4 rows)

What do we see:

When there is an update of the table row — even if only a toasted value is modified — a new row version is written.
Only the toasted values that are modified are changed, the unmodified toasted values stay the same.


RMAN uses backupsets by default.  The following commands will create a backup to a backupset:

RMAN> backup database;
RMAN> backup database plus archivelog;

This is assuming that you haven't changed the default RMAN parameters:
RMAN> SHOW ALL;
...
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

"Backup as backupset" is not a valid command and will return a syntax error.
"Backup as backupset database include current controlfile" might be useful if you changed the default backup type to something other than a backupset (e.g. "BACKUP TO COPY").
Source: Database Backup and Recovery Basics


DB2 Express C 10.5.4 using up all memory for package cache

Many applications or persistence frameworks generate new statements for each interaction with the database. The DB2 statement concentrator can help save system resources by mapping similar statements to one and reusing access plans and thereby share resources. Usually this help to cut query compilation time and reduce required system resources.

The package cache size can either be automatically set (self-tuning memory manager) or manually specified. In your case it seemed manually setting made more sense as the cache was hoping to reuse statements. If it is set to a fixed value, cache entries would need to be removed for new statements. However, the rest of your system memory could have been assigned to a bigger bufferpool.


PostgreSQL slow query log entries contain no query details, only duration

You may have log_duration set to on.
If so, changingit to off should allow you to get statements with your log_min_duration_statement set to 1.


Combine two queries from 1 table, 1 to Many

Here is what you asked for, but I think you would want to further aggregate this result set by GROUP BY customer_id to roll up the joined table. If that is the case, then simply add GROUP BY o.customer_id.

SELECT
  o.increment_id,
  o.customer_id,
  b.LastOrderMonth,
  ROUND(o.base_grand_total * o.base_to_global_rate,2) as revGBP,
  DATE_FORMAT(o.created_at, '01/%m/%Y') as 'MonthofOrder',
  b.monthCustCreated,
  b.CountOfOrders
FROM
  sales_flat_order as o
LEFT JOIN
(
    SELECT
      customer_id,
      date_format(MAX(created_at), '01-%m-%Y') as 'LastOrderMonth',
      sum(round((base_grand_total * base_to_global_rate),2)) as 'Rev GBP',
      date_format(MIN(created_at), '01-%m-%Y')  as 'monthCustCreated',
      COUNT(increment_id) as 'CountOfOrders'
    FROM
      sales_flat_order
    WHERE
      status in ('complete', 'processing')
    AND
      customer_ID IS NOT NULL
    GROUP BY
      customer_ID
) b ON o.customer_id = b.customer_id
WHERE
  o.status in ('complete', 'processing')
AND
  o.customer_email IS NOT NULL;


Limiting the occurence of a value in PostgreSQL

You can't access the fields of the new record dynamically using names stored in a variable.

The only way I can think of, is to convert the NEW record into a JSON value, then you can access the value "dynamically" through the name:

this_column_value := (to_jsonb(new) ->> this_column)::int;
EXECUTE FORMAT('SELECT COUNT(*) FROM %I WHERE %I = $1', this_table, this_column)
  USING this_column_value
  INTO total;


How do I horizontally partition an oracle database table, and should I?

To answer your second question first: yes you should partition. Oracle's query optimizer has a feature called partition elimination, which will check the predicate for the partition and only execute the SQL on the appropriate partitions.

Partitioning also leaves all the data in one space. Conceptually, think of it as many tables of identical structure, with an implicit UNION ALL between them if you were to do a SELECT from the entire table. Except "under the hood" Oracle sorts the actual rows into the right "table" based on the criteria you specify. Any rows that come in that don't match any of the criteria, go into what's known as the "default" partition.

For what you want to do, a "range partition" might be a good approach (so you can add more tenants later), e.g.:

create table transaction (id, tenant_id, a, b, c, d)
partition by range(tenant_id)
partition p_tenant1 values less than (2) tablespace ts_tenant1
partition p_tenant2 values less than (3) tablespace ts_tenant2
partition p_tenant3 values less than (4) tablespace ts_tenant3
partition p_tenantd values less than (MAXVALUE) tablespace ts_default;

Then later

alter table transaction
add partition p_tenant4 values less than (5) tablespace ts_tenant4;

This will create something that looks and behaves just like a normal table, but actually rows where tenant_id=1 will be in a partition in tablespace ts_tenant1, and queries will ignore all other partitions. Queries across the entire table can run in parallel on each partition. If tenant_id=4 in this scenario, the row will live in ts_default unless you add the new partition as shown, but the INSERT won't be rejected because there's no partition for it!

FWIW At my site we use partitioned tables in our 40Tb DW, you don't need to worry about this approach scaling or performing, if you choose your partitioning strategy well (e.g. you could partition on tenant_id then subpartition on month perhaps), create the right indexes, and so on.


Is quite simple. Make sure that oracle can write on a folder on the NAS and run the following RMAN job:

run
{
  CONFIGURE DEFAULT DEVICE TYPE TO disk;
  BACKUP FULL DATABASE INCLUDE CURRENT CONTROLFILE FORMAT '<NAS-directory>/<some-name>_%d_F_%T_%s';
}


MySQL Error Reading Communication Packets

One of the silent killers of MySQL Connections is the MySQL Packet.

First, let's figure out what a MySQL Packet is.

According to the page 99 of "Understanding MySQL Internals" (ISBN 0-596-00957-7), here are paragraphs 1-3 explaining MySQL Packets:

  MySQL network communication code was
  written under the assumption that
  queries are always reasonably short,
  and therefore can be sent to and
  processed by the server in one chunk,
  which is called a packet in MySQL
  terminology. The server allocates the
  memory for a temporary buffer to store
  the packet, and it requests enough to
  fit it entirely. This architecture
  requires a precaution to avoid having
  the server run out of memory---a cap
  on the size of the packet, which this
  option accomplishes.

  The code of interest in relation to
  this option is found in
  sql/net_serv.cc. Take a look at my_net_read(), then follow the call to my_real_read() and pay
  particular attention to
  net_realloc().

  This variable also limits the length
  of a result of many string functons.
  See sql/field.cc and
  sql/intem_strfunc.cc for details.

Knowing this about MySQL Packets allows a Developer/DBA to size them up to accommodate multiple BLOBs inside one packet even if they are obnoxiously large. Definitely, a packet too small will cause problems for open connections in this respect.

According to the MySQL Documentation

You can also get these errors if you send a query to the server that is incorrect or too large. If mysqld receives a packet that is too large or out of order, it assumes that something has gone wrong with the client and closes the connection. If you need big queries (for example, if you are working with big BLOB columns), you can increase the query limit by setting the server's max_allowed_packet variable, which has a default value of 1MB. You may also need to increase the maximum packet size on the client end. More information on setting the packet size is given in Section C.5.2.10, “Packet too large”.
An INSERT or REPLACE statement that inserts a great many rows can also cause these sorts of errors. Either one of these statements sends a single request to the server irrespective of the number of rows to be inserted; thus, you can often avoid the error by reducing the number of rows sent per INSERT or REPLACE.

RECOMMENDATION

Try raising the max_allowed_packet to a much larger number, since the default is 1M. I would suggest about 10 times the largest TEXT or BLOB field you have in your current dataset.

To set the max_allowed_packet to 256M, you can add it to /etc/my.cnf or my.ini

[mysqld]
max_allowed_packet=256M

to cover future restarts of mysqld. To install the value now on the server, please run this:

SET GLOBAL max_allowed_packet = 1024 * 1024 * 256;

Give it a Try !!!


Adding where condition with index make query slower(use filesort and temporary)

this index actually helped: deleted_at, category_id, however, I tried to do this without adding new index
Since there are much more records where deleted_at is null, i tried next queries:

SELECT
    COUNT(*) AS count_all,
    `users`.`category_id` AS users_category_id
FROM
    `users`
WHERE
     `users`.`id` NOT IN (SELECT `users`.`id` FROM `users` WHERE `users`.`deleted_at` IS NOT NULL)
GROUP BY `users`.`category_id`;

And query optimizer used much better schema. I will try it soon on big set and hoe it will work.


I think your table should also include the database name, otherwise once you identify a user that is abandoned in some database on your system, how are you going to find them?

Anyway here is a way to do it by building a single query rather than executing a query database by database (and relying on a system procedure that is undocumented, unsupported, and has community replacements because it's so buggy). It isn't better or faster, just easier to understand and troubleshoot, IMHO.

DECLARE @AbandonedUsers TABLE(db sysname, username sysname);

DECLARE @sql  nvarchar(max) = N'SELECT db = NULL, username = NULL WHERE 1 = 0',
        @base nvarchar(max) = N'
UNION ALL SELECT PARSENAME(N''$qn$'',1), dp.name COLLATE SQL_Latin1_General_CP1_CI_AS
  FROM $qn$.sys.database_principals AS dp
  WHERE dp.authentication_type = 1
    AND NOT EXISTS (SELECT 1 FROM sys.server_principals AS sp
      WHERE sp.SID = dp.SID)';

SELECT @sql += REPLACE(@base,N'$qn$',QUOTENAME(name))
  FROM sys.databases
  WHERE state = 0; -- might also add AND database_id > 4 to avoid common system DBs

INSERT @AbandonedUsers(db, username) EXEC sys.sp_executesql @sql;

SELECT db, username FROM @AbandonedUsers;


Remove duplicate records with special rules - Is it possible with a single query?

WITH cte AS ( SELECT *,
                     ROW_NUMBER() OVER (PARTITION BY id ORDER BY zip_code IS NULL, loc1 IS NULL) rn
              FROM test )
SELECT id, loc1, loc2, zip_code
FROM cte
WHERE rn = 1
ORDER BY id;

If I want to use this for deleting the duplicates, all I need to do is change WHERE rn = 2. Is that correct? – advncd
@advncd No, MySQL does not support updatable CTE. You must use this query as a subquery which selects rows which must be stored while deleting from another copy of source table.

DELETE t1.*
FROM test t1
JOIN (WITH cte AS ( SELECT *,
                           ROW_NUMBER() OVER (PARTITION BY id ORDER BY zip_code IS NULL, loc1 IS NULL) rn
                    FROM test )
      SELECT id, loc1, loc2, zip_code
      FROM cte
      WHERE rn > 1) t2 ON t1.id = t2.id
                      AND t1.loc1 <=> t2.loc1
                      AND t1.loc2 <=> t2.loc2
                      AND t1.zip_code <=> t2.zip_code;

fiddle


Need to get identify all tables and columns in a SQL Query

not "ready to use" solution, but may be is what You need:
http://www.sqlparser.com

description

as I explain in comment, this is framework, it not ready to use solution, but it has very good set of code examples, as well as live-demo.

Live demo

USE [Audit]
GO
ALTER PROCEDURE [dbo].[ReportData3]
    -- Add the parameters for the stored procedure here
    @PERNR int,
    @PERID int,
    @SNAME int,
    @DateSt date,
    @DateFin date,
    @GROSS_PAY bit

AS
BEGIN
    SET NOCOUNT ON;

IF @GROSS_PAY = 0

   IF @PERNR IS NOT NULL
   SELECT        PERNR, SNAME, WEEK_BEGIN_DATE, PERID, PLSTX, STLX, VDSK1_TEXT, HIRE_DATE,
                       WEEK_ENDING_DATE, WOSTD, HR_REG, REG_PAY, HR_HOL, HOL_PAY, HR_VAC, VAC_PAY, HR_SIC, SIC_PAY, HR_OVT, OVT_PAY, HR_PRE, PRE_PAY, HR_OTH,
                       OTH_PAY, HR_TOT_WORKED, HR_STR, STR_PAY, HR_TOT, TOT_PAY, Subtotal, RATR_OF_PAY_HORLY, RATR_OF_PAY_WEEKLY, GROSS_PAY, PAYDT,
                       AVG_STRAIGHT, AVG_TOTAL, AVG_GROSS, RESULT, SEQNR, TERMDT
   FROM            data_hist
   WHERE WEEK_ENDING_DATE >= @DateSt AND WEEK_ENDING_DATE <= @DateFin and

-- DELETED 118 rows of code

Result of parsing:

Tables:
data_hist
Fields:
data_hist.AVG_GROSS
data_hist.AVG_STRAIGHT
data_hist.AVG_TOTAL
data_hist.GROSS_PAY
data_hist.HIRE_DATE
data_hist.HOL_PAY
data_hist.HR_HOL
data_hist.HR_OTH
data_hist.HR_OVT
data_hist.HR_PRE
data_hist.HR_REG
data_hist.HR_SIC
data_hist.HR_STR
data_hist.HR_TOT
data_hist.HR_TOT_WORKED
data_hist.HR_VAC
data_hist.OTH_PAY
data_hist.OVT_PAY
data_hist.PAYDT
data_hist.PERID
data_hist.PERNR
data_hist.PLSTX
data_hist.PRE_PAY
data_hist.RATR_OF_PAY_HORLY
data_hist.RATR_OF_PAY_WEEKLY
data_hist.REG_PAY
data_hist.RESULT
data_hist.SEQNR
data_hist.SIC_PAY
data_hist.SNAME
data_hist.STLX
data_hist.STR_PAY
data_hist.Subtotal
data_hist.TERMDT
data_hist.TOT_PAY
data_hist.VAC_PAY
data_hist.VDSK1_TEXT
data_hist.WEEK_BEGIN_DATE
data_hist.WEEK_ENDING_DATE
data_hist.WOSTD


Postgres uuid: Use as primary key, or in addition to SERIAL -for disconnected app-

Of course, integer columns are smaller and faster to operate on. The data type uuid has a size of 16 bytes, integer has 4 bytes. Smaller indexes, faster JOIN operations, etc. But not a dramatic difference.

If you only ever query by UUID, additional integer IDs will give you nothing. Also, if your tables are small, the difference is negligible and you will do just as well without additional integer IDs.

If you have to join multiple tables on the ID, if you have foreign keys referencing the ID or if your tables grow huge (as well as your indexes), then operate with integer IDs internally.


You can't reference an alias on the same "level" where it was defined. You need to wrap your base query into a derived table:

select *
from (
  select u.*,
         l.*,
         (select count(cr.id) from cloud_recordings cr left join cameras c on c.owner_id=u.id where c.id=cr.camera_id) valid_licence
  from users u
    left join licences l on l.user_id=u.id
) t
where valid_licence > 0;


It depends on your RTO (Recovery Time Objective) and RPO (Recovery Point Objective).

Recovery Time Objective: Time duration to restore the database in case of any disaster.

Recovery Time Objective: The time interval that may pass until the database recovered. (Acceptable time duration for data loss).

Before you decide your recovery model and backup strategy, I would suggest you go through the Database Recovery Model and Backup Types in SQL Server before you decide your recovery model and backup strategy.

Edit:1

The ola-hallengren backup SP doesn't automatically execute all supported backups according to the recovery model of respective databases.

So, if you execute the SP with @BackupType = 'LOG' for the database with simple recovery model, I hope it'll fail and raise & log an error.

You need to execute separate script for all types of backup with @BackupType = 'FULL',@BackupType = 'DIFF' & @BackupType = 'LOG'.
You can execute separate script for LOG backup of the databases with FULL/BULKED_LOG recovery model with @Databases='Database_1, Database_2,...'.

Note: All backup logs (success/failure) are maintained into CommandLog table.

Edit:2

At line 2136, it's validating, " if @BackupType with recovery model of the database, then only it's proceeding to prepare backup script. to if condition is like this. Prior to that line I didn't see any validation to raise/log an error message into the commandlong table.

So I would suggest you to execute separate script for transaction log backup that includes the databases with full recovery model.

Fourth line of the below script validating the condition.

IF @CurrentDatabaseState = 'ONLINE'
AND NOT (@CurrentUserAccess = 'SINGLE_USER' AND @CurrentIsDatabaseAccessible = 0)
AND NOT (@CurrentInStandby = 1)
AND NOT (@CurrentBackupType = 'LOG' AND @CurrentRecoveryModel = 'SIMPLE')
AND NOT (@CurrentBackupType = 'LOG' AND @CurrentRecoveryModel IN('FULL','BULK_LOGGED') AND @CurrentLogLSN IS NULL)
AND NOT (@CurrentBackupType = 'DIFF' AND @CurrentDifferentialBaseLSN IS NULL)
AND NOT (@CurrentBackupType IN('DIFF','LOG') AND @CurrentDatabaseName = 'master')
AND NOT (@CurrentAvailabilityGroup IS NOT NULL AND @CurrentBackupType = 'FULL' AND @CopyOnly = 'N' AND (@CurrentAvailabilityGroupRole <> 'PRIMARY' OR @CurrentAvailabilityGroupRole IS NULL))
AND NOT (@CurrentAvailabilityGroup IS NOT NULL AND @CurrentBackupType = 'FULL' AND @CopyOnly = 'Y' AND (@CurrentIsPreferredBackupReplica <> 1 OR @CurrentIsPreferredBackupReplica IS NULL) AND @OverrideBackupPreference = 'N')
AND NOT (@CurrentAvailabilityGroup IS NOT NULL AND @CurrentBackupType = 'DIFF' AND (@CurrentAvailabilityGroupRole <> 'PRIMARY' OR @CurrentAvailabilityGroupRole IS NULL))
AND NOT (@CurrentAvailabilityGroup IS NOT NULL AND @CurrentBackupType = 'LOG' AND @CopyOnly = 'N' AND (@CurrentIsPreferredBackupReplica <> 1 OR @CurrentIsPreferredBackupReplica IS NULL) AND @OverrideBackupPreference = 'N')
AND NOT (@CurrentAvailabilityGroup IS NOT NULL AND @CurrentBackupType = 'LOG' AND @CopyOnly = 'Y' AND (@CurrentAvailabilityGroupRole <> 'PRIMARY' OR @CurrentAvailabilityGroupRole IS NULL))
AND NOT ((@CurrentLogShippingRole = 'PRIMARY' AND @CurrentLogShippingRole IS NOT NULL) AND @CurrentBackupType = 'LOG')
AND NOT (@CurrentIsReadOnly = 1 AND @Updateability = 'READ_WRITE')
AND NOT (@CurrentIsReadOnly = 0 AND @Updateability = 'READ_ONLY')
AND NOT (@CurrentBackupType = 'LOG' AND @LogSizeSinceLastLogBackup IS NOT NULL AND @TimeSinceLastLogBackup IS NOT NULL AND NOT(@CurrentLogSizeSinceLastLogBackup >= @LogSizeSinceLastLogBackup OR @CurrentLogSizeSinceLastLogBackup IS NULL OR DATEDIFF(SECOND,@CurrentLastLogBackup,GETDATE()) >= @TimeSinceLastLogBackup OR @CurrentLastLogBackup IS NULL))

Thanks!


postgresql recovery.conf file location in 11.5

That's what you get by following tutorials. It is better to understand the process (as explained in the documentation), then problems like this won't happen.

You have to place recovery.conf in the data directory. The data directory is the directory where postgresql.conf is, unless the data_directory parameter in postgresql.conf says differently.


MySQL : Conditional ORDER BY to only one column

SELECT* FROM mytable ORDER BY
LOCATE(CONCAT('.',`group`,'.'),'.9.7.6.10.8.5.');

I took your sample data, loaded it into a table called mytable and ran it.

Here are the results:

mysql> use test
Database changed
mysql> drop table if exists mytable;
Query OK, 0 rows affected (0.04 sec)

mysql> create table mytable
    -> (
    ->    names varchar(10),
    ->    `group` int
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql> insert into mytable values
    -> ('susanita',  10),
    -> ('miguelito', 5),
    -> ('mafalda',   7),
    -> ('manolito',  8),
    -> ('libertad',  6),
    -> ('felipe',    9),
    -> ('guille',    8);
Query OK, 7 rows affected (0.09 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM mytable;
+-----------+-------+
| names     | group |
+-----------+-------+
| susanita  |    10 |
| miguelito |     5 |
| mafalda   |     7 |
| manolito  |     8 |
| libertad  |     6 |
| felipe    |     9 |
| guille    |     8 |
+-----------+-------+
7 rows in set (0.00 sec)

mysql> SELECT * FROM mytable ORDER BY
    -> LOCATE(CONCAT('.',`group`,'.'),'.9.7.6.10.8.5.');
+-----------+-------+
| names     | group |
+-----------+-------+
| felipe    |     9 |
| mafalda   |     7 |
| libertad  |     6 |
| susanita  |    10 |
| manolito  |     8 |
| guille    |     8 |
| miguelito |     5 |
+-----------+-------+
7 rows in set (0.01 sec)

mysql>

Give it a Try !!!

UPDATE 2011-09-06 12:33 EDT

Here is another approach:

SELECT* FROM mytable ORDER BY
IF(FIELD(`group`,9,7,6,10,8,5)=0,99999,FIELD(`group`,9,7,6,10,8,5));

This will force any groups other than 9,7,6,10,8,5 to appear at the very bottom of the query.

UPDATE 2011-09-06 14:39 EDT

mysql> SELECT names, `group`
    -> FROM mytable
    -> WHERE `group` IN (9,7,6,10,8,5)
    -> ORDER BY find_in_set(`group`,'9,7,6,10,8,5');
+-----------+-------+
| names     | group |
+-----------+-------+
| felipe    |     9 |
| mafalda   |     7 |
| libertad  |     6 |
| susanita  |    10 |
| manolito  |     8 |
| guille    |     8 |
| miguelito |     5 |
+-----------+-------+
7 rows in set (0.00 sec)

Hey @Nick, yours works as well against my sample data !!!


Postgresql - Minor version upgrade 9.3.5 to 9.3.6 on Ubuntu

I'm guessing your log on server A is being shared by the two databases (master of A and the slave of B).

It looks like you have set ssl = on in your master's postgresql.conf file and have set ssl = off in your slave(B). You should either set ssl to on on all servers or none.

PostgreSQL doesn't usually have a problem replicating across minor versions


Why does DELETE not work after an UPDATE in a CTE?

Common table expressions (CTE) of the same statement are all based on the same snapshot of the database. I.e. all sub-statements see the same state of underlying tables.

Your DELETE tries to modify the same row(s) that your UPDATE already modifies.
The manual addresses your case exactly:

  Trying to update the same row twice in a single statement is not
  supported. Only one of the modifications takes place, but it is not
  easy (and sometimes not possible) to reliably predict which one. This
  also applies to deleting a row that was already updated in the same
  statement: only the update is performed. Therefore you should
  generally avoid trying to modify a single row twice in a single
  statement. In particular avoid writing WITH sub-statements that could
  affect the same rows changed by the main statement or a sibling
  sub-statement. The effects of such a statement will not be
  predictable.

Bold emphasis mine.


Try specifying -h localhost as a psql parameter, to connect via tcp/ip rather than a unix socket.

Likely, your psql and postgresql have different ideas of the correct setting for unix_socket_directory. Especially seeing as you have at least two different builds of postgresql installed! You might want to make sure you're using the psql client that corresponds to your postgresql server, since they have different major versions that will have other benefits too. Failing that, you can give a unix socket directory name as the hostname, e.g. psql -h /tmp will look for /tmp/.s.PGSQL.5342.


Display dates not in ascending for To_Char

If you sort by dates formatted in DD/MM/YYYY, then the sorting will put all the first of month first (of every month), then the seconds of month, etc.

You can't sort by a column not included in a select distinct, though. To sort by ascending date, group by and use the actual date column instead of the formatted date via to_char:

select To_Char (attendance_Date, 'dd/mm/yyyy')
from DIT_2010MAR_CIT4114A_FYP1_NO
group by attendance_Date
order by attendance_Date asc

If attendance_Date is a timestamp, cast it to date first (everywhere in the query).


Update Statistics with full scan for a single table

While you could schedule a job to run UPDATE STATISTICS on that particular table, you should also look into Parameter Sniffing. What's likely happening here is that a bad plan was wiped from the plan cache when you updated statistics on that table. Instead of doing a costly UPDATE STATISTICS, remove the bad plan from cache. Once wiped from the plan cache, the query will compile and get a new plan. Whichever parameter values were used at the time of compilation is what the execution plan will be optimized, that is as long as you are not overriding that behavior with the OPTION OPTIMIZE FOR query hint.

If the search function is a stored procedure, you can use the good ol' sp_recompile. Otherwise, use DBCC FREEPROCACHE and pass that command the SQL handle or the plan handle. You can use sp_BlitzCache with ExpertMode = 1 to get the command (it's all the way to the right in the result set).

To help diagnose which plan is causing the issue, I like to use sp_WhoIsActive or sp_BlitzCache. For sp_WhoIsActive, I like to log current activity to a table and then I can go back in time to compare execution plans and see who was the culprit.

For more information on Parameter Sniffing, see Erland's Slow in the Application, Fast in SSMS? post.


Disable Binary Logging During MySQL Dump

Only "writes" go to the binlog.  A backup only does "reads".

Hence "How ... when the MySQL dump occurs, it is not written to the binary logs?" is a non-issue.

(Or, do I not understand the perceived problem??)


Is EAV an appropriate design for a collection of coexisting metadata-rich "fact estimates" on objects?

EAV is not evil; like any other tool, it can be implemented poorly and abused. You can find articles trash talking cursors, dynamic SQL, triggers, even SQL Server itself. That doesn't make them bad things.

EAV can be an appropriate solution. Whether it's the right answer in your specific case is probably more opinion-based than anything; I'm answering more to suggest that you not close your mind to a solution because someone said it was bad. There are always opposing viewpoints to consider:

What's so bad about EAV, anyway?


How do I run a SQL Server 2005 job on Windows 7?

Probably the major problem is that your username don't have rights.
There are some examples how to create Accounts:

SSIS run  on different Account

SSIS runninng job

I would try set Owner "yourself" your PC Administrator user.


SQL Fast database restore with Azure Virtual Machine instant recovery snapshot and SQL in Azure VM?

Then if we have an outage, can I do a faster restore by restoring the VM image?

No.  See my answer here. Is it possible to restore a database with database backups starting on top of a restored server point in time snapshot that is out of date?

You can't attach a database without crash recovery, and you can't perform a crash recovery without the transaction log file.  Log backups cannot be substituted.

If you put your database files on Azure Page blobs, instead of on an attached disk you can use File-Snapshot Backups for Database Files in Azure.


What does the position of the ON clause actually mean?

It determines the logical tables involved in the join.

With a simple example

SELECT w1.WidgetID,
       w2.SomeValue,
       wp.PropertyName
FROM   #widgets1 w1
       LEFT JOIN #widgets2 w2
         ON w2.WidgetID = w1.WidgetID
       JOIN #widgetProperties wp
         ON w2.WidgetID = wp.WidgetID
            AND wp.PropertyName = 'b'
ORDER  BY w1.WidgetID

#widgets1 is left outer joined to #widgets2 - the result of that forms a virtual table that is inner joined to #widgetProperties. The predicate w2.WidgetID = wp.WidgetID will mean that any null extended rows from the initial outer join are filtered out, effectively making all the joins inner joins.

This differs from q2...

SELECT w1.WidgetID,
       w2.SomeValue,
       wp.PropertyName
FROM   #widgets1 w1
       LEFT JOIN #widgets2 w2 --no ON clause here
                 JOIN #widgetProperties wp
                   ON w2.WidgetID = wp.WidgetID
                      AND wp.PropertyName = 'b'
         ON w2.WidgetID = w1.WidgetID
ORDER  BY w1.WidgetID

#widgets2 is inner joined onto #widgetProperties. The virtual table resulting from that join is then the right hand table in the Left Outer Join on #widgets1

The same result can be achieved by using a derived table or Common Table Expression...

WITH VT2
     AS (SELECT w2.WidgetID,
                w2.SomeValue,
                wp.PropertyName
         FROM   #widgets2 w2
                JOIN #widgetProperties wp
                  ON w2.WidgetID = wp.WidgetID
                     AND wp.PropertyName = 'b')
SELECT w1.WidgetID,
       VT2.SomeValue,
       VT2.PropertyName
FROM   #widgets1 w1
       LEFT JOIN VT2
         ON VT2.WidgetID = w1.WidgetID
ORDER  BY w1.WidgetID

... Or alternatively you could re-order the virtual tables and use a RIGHT JOIN instead.

SELECT w1.WidgetID,
       w2.SomeValue,
       wp.PropertyName
FROM   #widgets2 w2
       INNER JOIN #widgetProperties wp
               ON w2.WidgetID = wp.WidgetID
                  AND wp.PropertyName = 'b'
       RIGHT JOIN #widgets1 w1
               ON w2.WidgetID = w1.WidgetID
ORDER  BY w1.WidgetID

This is covered by Itzik Ben Gan here

  ... the JOIN conditions must follow a chiastic relationship to the table
  order. That is, if you specify tables T1, T2, T3, and T4 in that order
  and the JOIN conditions match T1 with T2, T2 with T3, and T3 with T4,
  you must specify the JOIN conditions in the order opposite to the
  table order, like this:

FROM   T1
       <join_type> T2 T2
                  <join_type> T3 T3
                             <join_type> T4
                               ON T4.key = T3.key
                    ON T3.key = T2.key
         ON T2.key = T1.key

  To look at this join technique in a different way,
  a given JOIN condition can refer only to the table names right above
  it or table names that earlier JOIN conditions already referred to and
  resolved.

but the article has a number of inaccuracies, see the follow up letter by Lubor Kollar as well.


Indexing of a PostgreSQL bitstring (up to 20,000 bits each)

Famous problem in cheminformatics.  You will have a hard time coming up with an index that will beat a full-table scan for this purpose.  The most performant solutions keep the bitstrings outside of the database, where they don't have to deal with transactional overhead, and are optimized for efficient use of popcnt hardware support.

You can take some shortcuts, by for example keeping the bitstring data sorted by the number of on bits.  If the query says "find me things with each of these 45 different bits turned on" then you can quickly skip all the records with less than 45 bits turned on, as they can't possibly qualify.  The problem is that queries are usually sparser in bits than whole compounds are, so the number of records skipped by this manner is pretty small.  Another optimization is to use something like a genetic algorithm to select out certain bits as being more important than others (trained on the nature of the queries you run), and sorting on the population count of just those select bits rather than the entire bitstring.

Anyway, I don't think you will find what you want in an in-core PostgreSQL index, so be prepared to do some heavy hacking.  You might want to look to Indigo source code for some inspiration.  (Or maybe you can just use Indigo for your intended purpose directly)


How does MySQL coerce types during joins?

You need to make absolutely sure the character sets of the tables are identical. Keep in mind that a unicode issues could be at play here.

You could redo the query like this:

select FOOBAAR_hhs.relationship_group from FOOBAAR left join FOOBAAR_hhs
on FOOBAAR.relationship_group = FOOBAAR_hhs.relationship_group where new_hh_id = 15387929;

or

select FOOBAAR_hhs.relationship_group from FOOBAAR left join FOOBAAR_hhs
USING (relationship_group) where new_hh_id = 15387929;

Try the explain for these and see if it changes


Native dynamic SQL - execute immediate with column and table names

Because a bind variable is a value or value address, not a table or column name.

bind variable

  A placeholder in a SQL statement that must be replaced with a valid
  value or value address for the statement to execute successfully. By
  using bind variables, you can write a SQL statement that accepts
  inputs or parameters at run time.

Binding happens after parsing, but a statement can not be parsed without knowing the required tables and columns.


Creating a global temp table in a SQL Agent job

The difference between global and local temp tables isn't how long they exist, it's who can see them.

Local temp tables can only be seen by the session (spid) that created them.  Global temp tables can be seen by everyone, but only while the session that created them is active.

If you are running it in a SQL Agent job, as soon as it completes it disappears. That's why you can't access it, because it is no longer there.


It's not completely clear what you're trying to accomplish. However, it seems most likely that you're trying to update EmpSales.Madrigal to '0' after your insert.

To set EmpSales.Madrigal to '0' for all newly inserted records:

MERGE INTO EmpSales es
USING (
    SELECT employee, COUNT(sales) as SaleCount
    FROM salesInfo
    WHERE [Sale Date] BETWEEN '01/01/2016' AND '12/31/2016'
    GROUP BY employee
) cs
    ON es.employee = cs.employee
WHEN MATCHED THEN
    UPDATE SET es.TotalSales = cs.SaleCount
WHEN NOT MATCHED BY TARGET THEN
  INSERT (employee, TotalSales, Madrigal)
  VALUES (cs.employee, cs.SaleCount, '0')
;

To set EmpSales.Madrigal to '0' for all inserted or updated records:

MERGE INTO EmpSales es
USING (
    SELECT employee, COUNT(sales) as SaleCount
    FROM salesInfo
    WHERE [Sale Date] BETWEEN '01/01/2016' AND '12/31/2016'
    GROUP BY employee
) cs
    ON es.employee = cs.employee
WHEN MATCHED THEN
    UPDATE SET es.TotalSales = cs.SaleCount, es.Madrigal = '0'
WHEN NOT MATCHED BY TARGET THEN
  INSERT (employee, TotalSales, Madrigal)
  VALUES (cs.employee, cs.SaleCount, '0')
;

If you're looking for something else, please clarify what you need.


In SQL Server, what happens when a constraint is added to a table with data in it?

The question:

  What happens if a "NOT NULL" constraint is added to a table to a
  column that already has NULL values?

A simple example in SQL Server is a constraint to disallow NULLs.

Create a table and insert two rows, one not null and one null.

USE tempdb

CREATE TABLE dbo.OneNull (c1 INT)

INSERT dbo.OneNull ( c1 )
    VALUES  (0), (NULL)

The ALTER TABLE syntax can direct what happens when you create the constraint. Using WITH NOCHECK will tell the engine (vroom vroom) not to check the column contents, but just to add the constraint for future rows.

ALTER TABLE dbo.OneNull WITH NOCHECK ADD CONSTRAINT ck_checkone CHECK (c1 IS NOT NULL)

Adding this constraint runs successfully, but it means that the constraint is untrusted, and you lose certain query optimizations. For instance, if this were a Foreign Key, you would lose join elimination. For a column constraint, the optimizer can skip doing any work period if constraint information tells it that a search will yield no results.

But now this insert fails:

INSERT dbo.OneNull ( c1 )
    VALUES  (NULL)

You can also direct the engine (beep beep) to check the column values while adding the constraint.

ALTER TABLE dbo.OneNull WITH CHECK ADD CONSTRAINT ck_checkonechecktwo CHECK (c1 IS NOT NULL)

Adding this constraint will fail.

If you add one with no formal direction (at least for column check constraints), the engine (screeeeech) will throw an error.

ALTER TABLE dbo.OneNull ADD CONSTRAINT ck_checkornocheck CHECK (c1 IS NOT NULL)

An example of the type of optimization you can get with a trusted constraint is like so:

ALTER TABLE dbo.OneNull WITH CHECK ADD CONSTRAINT ck_checkonechecktwo CHECK (c1 <= 1)

INSERT dbo.OneNull ( c1 )
    VALUES  (1)

SELECT *
FROM dbo.OneNull AS onu
WHERE onu.c1 > 2
AND 1 = 1 --this is added to prevent a trivial plan

Rather than look for values at all, the engine (honk honk) knows that such a value can't exist because of the trusted constraint. Rather than put together a 'real' query plan and read any data, the optimizer short circuits and uses a Constant Scan operator to return NADA!


Insert statement sometimes runs slow and sometimes fast SQL Server 2014

You may have a disk bottleneck.  Run a Perfmon.exe recording specifically you want the individual "average disk read wait queue" and "average disk write wait queue" of all drives.  Perfmon is part of windows functionality and is on all Windows PC / servers / etc.  You will want a recording from a weekday and a recording from the weekend.
Are you running DBCC database integrity check on weekday nights?  While I want this running every night, it causes to much performance problems so to live in reality, mine only runs on weekends.


best practice either create multiple databases or multiple users

Databases use server resources: CPU, RAM, disk I/O.  Unless your server is physically partitioned (e.g. Solaris Zones) or you are using VMs it is a really bad idea to host multiple databases on the same server.  You are just creating an environment where contention will be rife and tuning will be even trickier than normal.

Oracle RDBMS has always been designed to host multiple schemas in the one database, and for different applications that is the way to go.  This is one of the main differences between Oracle and (say) MS SQL Server or MySQL.


SQL Server 2008R2 Ent + Win2003 Ent R2 32bit will not use all available memory

AWE memory can only be used for data cache. Things like procedure cache, workers stacks, execution memory grants, SQLCRL and so on can only use the process 'real' memory space (ie. the 3Gb). So even if you did set up everything correctly, it does not mean that 1) SQL Server will actually use all the 6Gb and 2) you won't run into out-of-memory issues despite plenty of AWE memory being available.

32bit is dead. Just move to a 64 bit environment and call it a day.


mysql - select random rows with max 3 rows per parent id

If you want random rows per parent_id, your inner ORDER BY should arrange rows in a way that same parent_id rows are grouped together. So, instead of

ORDER BY rand()

it should be

ORDER BY parent_id ASC, rand()

As for this:

  it returns to me Ascending id's each time I try

The reason your query returns ascending IDs each time is because you have instructed it to return the results in the ascending order of id with this line at the end of the query:

ORDER BY id ASC

So, again, if you want the output to show same parent_id rows grouped together, add the parent_id column as the first sorting criterion to the final ORDER BY as well:

ORDER BY parent_id ASC, id ASC


String Function to Get Left 1000, But Only To Last Period or Carriage Return

Edit: This was posted to address an earlier version.

The left 1000 characters are easy.

Then find the first period in the reverse of the left 1000. That's how much you should subtract from the original 1000.

Like:

LEFT(@s,
    1001-
    CHARINDEX('.',
        REVERSE(LEFT(@s,1000))
    )
)

...I think - you may have to add one somewhere if it wipes the period or leaves a space.


Optimized my.cnf for high-end and busy server

If you're running at that load on that hardware, you really should get some experts in to do this for you. Pythian and Percona are 2 consultancy/support organisations that I can think of: I have no link to either of them BTW.

Each install and load has different tweaks needed. The "huge" cases for my.cnf files I've seen are what you'd use a few years ago or on your mobile phone nowadays...

The default settings are amazingly low so if you've never changed them then there is your problem.

Edit: Have you already hacked your my.cnf? https://drupal.stackexchange.com/questions/12085/default-to-myisam-instead-of-innodb-in-drupal-7


Why does a count obtain an ExclusiveLock in Postgresql?

Every transaction is born owning an ExclusiveLock on itself.

For a read-only transaction, this will only block things like CREATE INDEX CONCURRENTLY.  For DML transactions, this lock will be used to block other transactions trying to update or delete the same row, or insert conflicting rows.


The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions

This is an error and No, you can't use ORDER BY to sort rows in the derived table for the reason given in the error message. The order of rows in the result set is ultimately controlled by an ORDER BY clause in the outer SELECT, not by the OVER clause. The OVER clause "specifies the logical order in which the window functioncalculation is performed". It doesn't sort the result set. There are workarounds for this issue though and you can check solution proposed at https://stackoverflow.com/questions/18031421/the-order-by-clause-is-invalid-in-views-inline-functions-derived-tables-subqu either by using ROW_NUMBER function or ORDER BY column OFFSET 0 ROWS.
Alternative method is explained by Mr. Pinal Dave at this link.
Hope this helps.


This is most likely going to be the result of memory exhaustion. Every connection to the database requires a bit of memory and, if each connection needs 1MB (a low number), you could be looking at about 2GB of memory used just for the connections.
This SQL query will let you know the maximum amount of memory any single connection can use:
SELECT ( @@read_buffer_size
       + @@read_rnd_buffer_size
       + @@sort_buffer_size
       + @@join_buffer_size
       + @@binlog_cache_size
       + @@thread_stack
       + @@tmp_table_size
       + 2*@@net_buffer_length
       ) / (1024 * 1024) AS MEMORY_PER_CON_MB;

Check that this number isn’t too high (which is a subjective number based in experience with your specific databases). If the database is having its memory exhausted regularly, then you may need either a memory-optimized instance or stricter limits on how many resources each connection may consume.


In InnoDB both data and indexes are stored together, there is no separate file for indexes. Within your MySQL install directory you will find directories for each database, and within each database directory will be the tablespace files. Depending on whether you have InnoDB File-Per-Table enabled or not, you will see individual files per table or not.


Which SQL Server system database is more important: master or resource?

Both the master and resource DB is important.

If the master DB becomes corrupt, you can still restart your server by creating a new master DB. You will loose your server principals and other data that are stored in the master, but at least you will be up and running. Once you recreate your logins (hopefully these were scripted) then you have access to your data.

The resource DB contains the sys schema. I am not sure if you can just copy these from another instance if there are not much changes done to these. Will have to see if there is anybody else who had this issue.


In my experience, even if you're doing full table scans, it is unlikely that extent fragmentation will affect performance much, and for more typical query patterns it should be negligible at best. That is for queries that use cached data that fits into memory - obviously fragmentation of any kind becomes rather moot if the data is in memory and isn't being read directly off disk.

Now, you've got a table that is > 8 GB, so it is possible that extent fragmentation might be harmful to your queries. If this query is using a table scan across 34 million rows, and the worst you're getting (on first execution only!) is 30 seconds, then it is extremely unlikely that lowering that extent fragmentation number is going to help much. That 30 seconds is spent loading the data into memory, and I can't fathom that improving the extent fragmentation will buy you much there. If you have the memory to spare to keep this table in memory, perhaps you should consider a startup job or some background process that periodically runs the query without forcing a user to wait for it, ensuring that it stays fresh in the cache.

Hekaton might be for you.


Refresh SQL Server's service account security group membership

You can use klist purge to do this.  See How do you force an update to a user's group membership in Windows 7? and Viewing and Purging Cached Kerberos Tickets.

I would test this a lot before doing it in production, and avoid doing it in production unless absolutely critical.  If a restart of SQL Server is completely impossible, a safer workaround would be to temporarily assign permissions to the necessary resource directly to the service account.  This would provide the access without a restart, and not require anything out of the ordinary that might cause further issues.  You would simply need to remove the temporary permission after the next service restart.


I might be misunderstanding the question, but assuming that I do understand it, it seems that you could fairly easily take the where clause from the first

where digitalfiles.path in (
        select *
        from Staple
        )

And make it a part of the second query.

There are a number of ways you could do this (CTE, derived table, etc...) but it seems like just adding that to the where clause is probably as easy as anything.

select ft.Extension
    ,COUNT(f.OriginalFileName) as [Count]
    ,sum(df.Length) / 1024 as [Size KB]
    ,ft.FileTypeId
    ,cast(count(f.OriginalFileName) * 100.0 / sum(count(*)) over () as decimal(18, 6)) as [Pct]
from Files f
inner join DigitalFiles df on f.FileId = df.FileId
left join FileTypes ft on df.FileTypeId = ft.FileTypeId
where f.Deleted = 1
AND df.path in (select * from Staple)
group by ft.Extension
    ,ft.FileTypeId;


Sum of table sizes don't match with mdf size

There are "internal" tables, queues, etc that don't show up in certain reports / queries as they are system objects. If you run the query below, which counts the number of actual pages defined in the data files in the current Database, you should see that one of the "Reserved" fields (match the appropriate "KB", "MB", or "GB" field to your report) matches the "Space Used" value in the Data File portion of that report.

;WITH cte AS
(
  SELECT COUNT(*) AS [Reserved],
         SUM(CASE WHEN pa.[is_allocated] = 1 THEN 1 ELSE 0 END) AS [Allocated]
  FROM sys.dm_db_database_page_allocations(DB_ID(), NULL, NULL, NULL, DEFAULT) pa
)
SELECT  [Reserved] AS [ReservedPages],
        [Allocated] AS [AllocatedPages],
        '---' AS [---],
        ([Reserved] * 8) AS [ReservedKB],
        ([Allocated] * 8) AS [AllocatedKB],
        '---' AS [---],
        ([Reserved] * 8) / 1024.0 AS [ReservedMB],
        ([Allocated] * 8) / 1024.0 AS [AllocatedMB],
        '---' AS [---],
        ([Reserved] * 8) / 1024.0 / 1024.0 AS [ReservedGB],
        ([Allocated] * 8) / 1024.0 / 1024.0 AS [AllocatedGB]
FROM    cte;

And below is the query to get the per-object details along with a grand total line at the top:

SELECT sch.[name], obj.[name], ISNULL(obj.[type_desc], N'TOTAL:') AS [type_desc],
       COUNT(*) AS [ReservedPages],
       (COUNT(*) * 8) AS [ReservedKB],
       (COUNT(*) * 8) / 1024.0 AS [ReservedMB],
       (COUNT(*) * 8) / 1024.0 / 1024.0 AS [ReservedGB]
FROM sys.dm_db_database_page_allocations(DB_ID(), NULL, NULL, NULL, DEFAULT) pa
INNER JOIN sys.all_objects obj
        ON obj.[object_id] = pa.[object_id]
INNER JOIN sys.schemas sch
        ON sch.[schema_id] = obj.[schema_id]
GROUP BY GROUPING SETS ((sch.[name], obj.[name], obj.[type_desc]), ())
ORDER BY [ReservedPages] DESC;


Insert data from 1 column to many columns in another table (using 1 line) in sql


SQL Server Linked Servers not connecting

This is a common error in delegation permission for the linked server connection however you can fix it checking the Kerberos Connectivity or adding but none recommendable SQL Login ID in your Linked Server Connection.

You can find more details on how to use the Kerberos Connection Manager and add the delegation permissions on your AD based on this article.
https://www.sqlshack.com/how-to-link-two-sql-server-instances-with-kerberos/

!


Monitoring real-time statistics on PostgreSQL

I believe the answer here is "not exactly". Those stats are coming from pgbouncer itself, so that don't map directly to the postgres backends. That said, you can track connection, data, and query statistics in postgres, but the information won't align exactly, and will probably be formatted differently (ie. you're more likely to get a counter than a gauge, so you'll need to do some tracking/math yourself)


Selecting "consecutive" rows with same values

SELECT userID, status, TIMESTAMPDIFF(minute, MIN(tStamp), MAX(tStamp)) AS duration
FROM (
    SELECT
    t.*
    , @groupNumber := IF(@prev_userID != userID OR @prev_status != status, @groupNumber + 1, @groupNumber) AS gn
    , @prev_userID := userID
    , @prev_status := status
    FROM t
    , (SELECT @groupNumber := 0, @prev_userID := NULL, @prev_status := NULL) var_init_subquery
    ORDER BY userID, tStamp
) sq
GROUP BY gn, userID, status

see it working live in an sqlfiddle

Here's how it works. We define three variables. One holding a group number and two holding the values of the previous row values of status and userId. Note, that in a relational database there is no order unless you specify it. This is very important. In the select clause, we first check, if the variable values differ from the current row. If yes, we increment the group number, if not we leave it as it is. After that, we assign the values of the current row. So when the variables are evaluated when the next row is processed, they still hold the values of the previous row. So the order is important here, too. In the outer query we simply can group by this group number to get the min and max values of the timestamp.

read more about user-defined variables here


The test is to restore the backup somewhere else.

Monitoring can be done via the return code of pg_dump: if that isn't 0, something went wrong.


Finding Conflicting Combinations Using SQL

SELECT Boy_Name, Girl_Name
FROM Everyone_Loves_Relationships
GROUP BY Boy_Name, Girl_Name
HAVING COUNT(DISTINCT Relationship_Status) = 2

This query assumes that none another value (including NULL) is present in Relationship_Status field. If this is not true then replace the condition with

HAVING SUM(DISTINCT CASE WHEN Relationship_Status = 'Yes'
                         THEN 1
                         WHEN Relationship_Status = 'No'
                         THEN 2
                         ELSE 3
                         END) < 3


SQL SERVER - Linked Server and query performance

Your problem begins and ends with statistics and estimates. I have reproduced your situation on my servers and found some interesting hints, and a workaround solution.

First things first, let's take a look at your execution plan:
When a view is used we can see that a filter is applied after the Remote Query is executed, while without the view there was no filter applied at all. The truth is that the filter was applied inside the Remote Query, at the remote server, before retrieving the data over the network.
Well, obviously applying the filter at the remote server and thus retrieving less data is a better option, and obviously that only happens when not using a view.

So... what is so intersting...?

Surprisingly, when I changed the filter from cognome = 'test' to cognome = N'test' (unicode representation of the string) the view used the same execution plan as the first query did.
I guess the reason is that somehow when using the view SQL Server estimated that there will be a small number of rows returning from the (remote) query, and that a local filtering will be cheaper, but when SQL Server had to implicit convert NVARCHAR to VARCHAR, statistics could no longer be used and the decision to filter locally was not taken.
I have looked for the statistics locally, but the view had no statistics, so my guess is that the view uses the remote statistics in a way that ad-hoc query does not, and than takes the wrong decision.

OK, so what solves the problem?

I stated earlier that there is a workaround (at least until someone comes up with a better solution), and no, I don't mean using unicode for your strings.
I wanted to give an answer first, I still have to find why, but when using an Inline Function SQL Server behaves exactly the same as with the query (without view), so replacing the view with the function will give the same result, in a simple query, and with good peformance (at least in my environment).

My code suggestion for you is:

CREATE FUNCTION fn_anagrafiche2()
RETURNS table
AS
RETURN
(
    SELECT *
    FROM dolph2.agendasdn.dbo.vistaanagraficagrp
    UNION
    SELECT *
    FROM dolph2.acampanet.dbo.vistaanagraficagrp
    UNION
    SELECT *
    FROM municipio2.dbnet.dbo.vistaanagraficagrp
)
GO

The query will then be:

SELECT *
FROM fn_anagrafiche2()
WHERE cognome = 'prova'

This works on my servers, but of course test it first.
Note: I do not recommend using SELECT * at all, as it is prone to future errors, I simply used it because it was in your question and there was no need for me to change that when I can add this remark instead :)


The error is being generated because you currently aren't joining your sailor table to reserve so the engine doesn't have any reference to the reserve table.

In order to make the reserve table available, you'll need to JOIN the tables on the sid column similar to:

select s.sname
from sailor s
inner join reserve r
  on s.sid= r.sid
where r.bid=103;

Or if you just want to return the sname that exists in the reserve table, then you could use EXISTS:

select s.sname
from sailor s
where exists (select 1
              from reserve r
              where bid = 103
                and s.sid = r.sid);

If you needed to include all 3 tables in a single query, then you would use:

select s.name
from sailor s
inner join reserve r
  on s.sid = r.sid
inner join boat b
  on r.bid = b.bid


Generate sample data for an Oracle database

I like Benerator. It's not super easy to setup but quite flexible.


How can I ONLY get the query execution time on the command line without waiting till the data is displayed?

Solution for Getting only Execution time of Query without printing result set :

Query :Select C1 , C2, C3 from Table where C3='1'; -- 40571 rows in set (0.079 sec)

Get Count of query :

Do : Select count(*) from (Select C1 , C2, C3 from Table where C3='1') as X;

Time : 0.035 sec
Redirecting output to /dev/null :

Do : time mysql  -u'user' -p'password' Database < query.sql > /dev/null

Time :

    real    0m0.180s
    user    0m0.112s
    sys     0m0.005s

Using mysqlslap :

Do :

vi query1.sql                   // Addin the query.
mysqlslap   -u'user' -p'password' --query="query1.sql"  --delimiter=";" --concurrency=1 >> output.log

Time :
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
    Average number of seconds to run all queries: 0.080 seconds
    Minimum number of seconds to run all queries: 0.080 seconds
    Maximum number of seconds to run all queries: 0.080 seconds
    Number of clients running queries: 1
    Average number of queries per client: 1

According to me ideal is 3rd solution using mysqlslap


While cross-database queries are possible and quite efficient with a current release (not such much with the somewhat dated 9.4 version), they are still more complicated then simply joining two tables from two different schemas.

So, if there is just a slight chance that you want to do queries that use tables from more then one module (e.g. for reporting) separate the modules using schemas.

Using different database doesn't really buy you that much unless you also host them on separate instances, which would enable you to e.g. replicate them individually (replication can - as of 9.6 - still only be done per cluster with stock Postgres).

Different database would help if those modules were more like tenants and you want to make sure that a single module and their (database) users never have the chance to even take a glimpse on the other tables.

Unrelated, but: for a new project you should really consider the current Postgres version which is 9.6.


Sql Server 2008 to R2 Restore Backup from R2

Most likely the first one "MSSQLSERVER".  Thats the default name for a new installation.  The express one is the free version and limits your DB size to 10GB.  The 3rd one isn't an instance, just shared components so ignore that one.

You should verify by logging into the server from SSMS and typing in SELECT @@SERVERNAME first to verify.  If it only returns the server name, then the MSSQLSERVER is the right instance.  If it returns ServerName\Express then you're on the express edition.


For MySQL's InnoDB Engine...

Case 1 (slowest):  No index.  It will scan the entire table (all rows) to find one the row(s) with name = 'Scott' and fetchsalary` from them.

Case 2:  INDEX(name).  This will use the index to very efficiently the Scott row(s), then fetch the rows and get the salarys.

Case 3 (fastest):  INDEX(name, salary).  Now the entire query can be performed inside just the index -- without reaching over to the rest of the columns in the data.  More  This is called a "covering" index.  More precisely:  If all the columns needed for the entire SELECT are found in the INDEX, then only the index need be touched, not the data.

The Data is in one BTree, ordered by the PRIMARY KEY.  An INDEX is in a different BTree, ordered by the index.

BTrees are very efficient for finding a specific row or reading consecutive rows based on the index.  See the Wikipedia article on BTree.  This is also a good reference:  https://www.percona.com/files/presentations/percona-live/london-2011/PLUK2011-b-tree-indexes-and-innodb.pdf

For other databases, there could be differences.

How a row is stored

Case 1 (most situations):  All the columns of a given table "record" (aka "row") are stored together in one contiguous set of dozens, maybe hundreds, of bytes.  And this is all sitting in the same "block" along with perhaps a hundred other rows.  So, when you grab any column from the row, all the rest of the columns are already readily available.  Your example of an Employee table probably falls into this Case.  RDBMS optimization focuses on being able to get an entire row quickly, but sacrifices in not being able to get an entire column quickly.

Case 2:  In some DB implementations (including MySQL's InnoDB) big columns (TEXT and BLOB) may be stored 'off record'.  That is, they are put in some other block(s).  Advantages () it allows for scanning rows quickly when you don't need those bulky pieces; () typically you will fetch the bulky columns for only one row at a time.

Case 3:  There is a different type of database organization, with names like "Columnstore".  This pivots the data so that it is easy to get all the values for a column quickly, but loses the ability to quickly get all the columns of a single row.

Focus on Case 1, since it is very common.


MongoDB Replica Set Sync data by Copying Data Files from Another Member

The way you perform the resync isn't right. To perform resync of the secondary member (assuming it is already a member of the replica set), bring down (stop mongod process) the secondaray node, delete all the folders from the /data directory including journal and restart the node so it re-joins the replica set again

Once it re-joins the replica set, mongodb will automatically copy all the data files including local database to the secondary. You don't need to copy admin or local database manually as mongo will take care of it

Thank you!

-Anban


Why does querying a PostgreSQL database have an overhead of a few dozens of milliseconds?

It's the overhead that the SQL client has, in this case pgAdmin.
Use a different SQL client. pgAdmin isn't known to be the fastest. You will always have an overhead. Parsing and network communication (even if everything is local) can never be avoided. The client needs to receive and display the result - that is where pgAdmin seems to be the slowest option (I have never used it myself, but this question shows up here and on Stack Overflow on a regular basis).


SELECT *
FROM `trips`
WHERE carId in (147, 62, 59, 42)
GROUP by carId;

Remove the LIMIT and be sure that you get one row per carId. If you put 20 ids within in ( ), you will get 20 exclusive rows.

But this gives a rough result in the sense that it might choose any one row matching the carId. You might add some condition to pickup any preferred row per carId.


Creating CDC system table on SQL Server 2008

As the very article you linked says:

  You can script those objects from a new dummy database in which you
  have successfully enabled CDC

These tables are not 'system' as in 'they belong to the sys namespace and protected from direct user operations' but are 'system' as in 'they are absolutely ordinary tables used by stored procedures shipped with CDC and MS just wishes nobody would mess with them'...


Connect to SQL Server Management Studio over VPN (Hamachi)

Have you allowed remote connections in Surface Area Configuration -> Services and Connections -> Remote Connections?

I haven't specifically tried recently, but I have enabled TCP & Named pipes. You can also force a protocol if you need to, e.g. in the server name, use NP:myserver in order to force named pipes, or TCP:myserver in order to force TCP.

Try enabling named pipes in the connection setup too - client and server.

Also, hamachi normally uses its own IP range, so you should check that you can actually telnet to port 1433 of your server.


Could not connect to the specific instance. MySQL error number 1045. Access denied for user 'root'@'my public ip' (using password:YES)

1) 'my public ip' is this ip allowed to connect your mysql server?
note : i know you have allowed from everywhere '%' but better to check again, whether your server ('my public ip') is allowed to connect to mysql server

2) If 1st is ok then please check your password. Looks like something wrong with the password (typo, or copy issue etc).

Hope this helps.


One location for finding out about many of these states is a post by Aaron Bertrand at:

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

The error you are asking about are:

Error: 18456; Severity: 14 State: 23
Access to server validation failed while revalidating the login on the
connection.
Error 18456; Severity 14; State 38Failed to open the database specified in the login properties. (or)  Cannot open database "" requested by the login. The login failed.
Error 18056; Severity: 20, State 23

This is apparently related to connection pooling when the client could not reuse a spid.   If this is the same problem you can look at this Microsoft Connect item, but it has been closed as "Not Reproducible."

https://connect.microsoft.com/SQLServer/feedback/details/468478/sql-server-2008-periodically-does-not-accept-connections

and then:

Error 18451, Severity 14; State 1

SQL Server is in the process of shutting down. During shutdown, only members of the sysadmin fixed server role can make new connections.

This suggests that your installation needs some examination and perhaps updated to a more recent patch level.


How can I solve this error InnoDB: is in the future?

InnoDB doesn't provide means to fix corrupt tablespace. innodb_force_recovery allows you to disable some checks so you can start MySQL and dump your data. Still you have to re-create the table space and load it back.

If innondb_force_recovery doesn't work then the only way I'm aware of is to fetch records directly from InnoDB files... and re-create InnoDB tablespace from scratch.


How to create login and logout trigger for client audit?

You can add a simple IF statement to your LOGON trigger, as such:

USE test;
GO
CREATE TABLE LogonAudit
(
    AuditID INT NOT NULL CONSTRAINT PK_LogonAudit_AuditID
                PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , UserName NVARCHAR(255)
    , LogonDate DATETIME
    , spid INT NOT NULL
);
GO
GRANT INSERT ON Test.dbo.LogonAudit TO public;
GO
CREATE TRIGGER MyLogonTrigger ON ALL SERVER FOR LOGON
AS
BEGIN
    IF SUSER_SNAME() <> 'sa'
    INSERT INTO Test.dbo.LogonAudit (UserName, LogonDate, spid)
            VALUES (SUSER_SNAME(), GETDATE(), @@SPID);
END;
GO
ENABLE TRIGGER MyLogonTrigger ON ALL SERVER;

This will only audit logons for users not named 'sa' by virtue of the line IF SUSER_SNAME() <> 'sa'

Alternatively, if you want to ONLY audit the login of a specific user, you could write the trigger like:

CREATE TRIGGER MyLogonTrigger ON ALL SERVER FOR LOGON
AS
BEGIN
    IF SUSER_SNAME() = 'SomeUser'
    INSERT INTO Test.dbo.LogonAudit (UserName, LogonDate, spid)
            VALUES (SUSER_SNAME(), GETDATE(), @@SPID);
END;

This will only record when SomeUser logs into the server.


Cannot upgrade to v12 due to sql_identifier data type

You need to find the columns with that type outside of information_schema.  Information_schema is not user tables, they are system tables.
SELECT *
FROM pg_attribute
  join pg_class on attrelid=oid
  join pg_namespace on relnamespace=pg_namespace.oid
WHERE atttypid::regtype::text like '%sql_identifier'
  and nspname!='information_schema'


Permissions to execute a procedure on database a that reads a table on database b

This is an issue of ownership chaining and how that doesn't automatically translate across databases as most people would expect. The following MSDN page has a good explanation of what is going on in this situation:

Ownership Chains

That article states that in order to get this to work as most would expect, you need to enable "Cross-Database Ownership Chaining". However, this is a security risk, and that article even has a section for "Potential Threats".

The more secure way to extend permissions is by using module signing. Please see my answer on the following Question (here on DBA.SE) for how to remedy this, especially if you don't want to give the user any access to Database B:

stored procedure can select and update tables in other databases - minimal permissions granted

The first time you go through this exercise it might seem terribly complicated, but after a few times it will make enough sense to not seem to bad ;-).


Exclude certain results from in condition in sql

One way to get the results you're after is to "reverse" both the EXISTS and IN operators - it's a little confusing to read, though.  I also got rid of the distinct, since it's not really needed in the subquery.
SELECT *
FROM #Table1
WHERE NOT EXISTS
(
    SELECT ID
    FROM #Table2
    WHERE
        #Table1.ID = #Table2.ID
        AND WFID NOT IN (12,17)
);

Here's the setup for the query I'm using locally:
CREATE TABLE #Table1
(
    Id int NOT NULL,
    data1 varchar(2) NOT NULL,
    data2 varchar(3) NOT NULL
);
INSERT INTO #Table1
    (Id, data1, data2)
VALUES
    (1,  'ff',   'ffe'),
    (2,  'r' ,   '3'  ),
    (3,  'df',   'er' );

CREATE TABLE #Table2
(
    Id int NOT NULL,
    WFID int NOT NULL,
    data1 varchar(2) NOT NULL,
    data2 varchar(3) NOT NULL
);
INSERT INTO #Table2
    (Id, WFID, data1, data2)
VALUES
    (1,    12,    'd',     'e' ),
    (1,    13,    '3',     '4f'),
    (1,    15,    'e',     'dd'),
    (2,    12,    'f',     'ee'),
    (3,    17,    'd',     'f' );


I'm making assumptions here based on the available data.  I do not know your table name so I'm going to assume it's called institute_enroll_table for now.  I also called your insert variables by fake names myid and my_instutute_id since I don't know what your current insert looks like.

The easiest way to fix this is going to be to do the MAX(enroll_no) in your INSERT query rather than do it as a separate query.  The second INSERT will wait on the first and therefore will get a different MAX.  The COALESCE makes the value 1 when there is no record found.

INSERT INTO institute_enroll_table(id, institute_id, enroll_no)
SELECT myid, my_institute_id,
    COALESCE(
        (SELECT MAX(enroll_no)+1 FROM institute_enroll_table ie WHERE
ie.institute_id=my_institute_id)
     ,1)AS enroll_no


Any disadvantages to enabling federated storage engine?

IMHO it is really about security. If a table uses the federated storage engine, all one needs to do is this :
SHOW CREATE TABLE fedtblname\G

and you can see the connection information (server IP, database, table name, username, password) of the real MyISAM table on the remote DB server, and all in plain text.
It is too bad that the connection information is not encrypted.
The bottom line is that you can use federated option in my.cnf totally at your own risk.
UPDATE 2012-01-18 11:31 EDT
It was pointed out that you can create a server definition and then define that server definition in the CONNECTION option of the federated table.
I tested this out and it works great.
However, there is still a security flaw. If you can SELECT from the table mysql.servers, you can see all definitions. Here is the table's layout:
mysql> show create table mysql.servers\G
*************************** 1. row ***************************
       Table: servers
Create Table: CREATE TABLE `servers` (
  `Server_name` char(64) NOT NULL DEFAULT '',
  `Host` char(64) NOT NULL DEFAULT '',
  `Db` char(64) NOT NULL DEFAULT '',
  `Username` char(64) NOT NULL DEFAULT '',
  `Password` char(64) NOT NULL DEFAULT '',
  `Port` int(4) NOT NULL DEFAULT '0',
  `Socket` char(64) NOT NULL DEFAULT '',
  `Wrapper` char(64) NOT NULL DEFAULT '',
  `Owner` char(64) NOT NULL DEFAULT '',
  PRIMARY KEY (`Server_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='MySQL Foreign Servers table'
1 row in set (0.00 sec)

mysql>

In order to see the server definitions, run this query:
SELECT * FROM mysql.servers\G

This will expose all remote servers known by the MySQL Instance, including the password in plain text.
To see all the federated tables, run this:
select table_schema,table_name from information_schema.tables where engine='federated';

This confirms my original assertion: all one needs to do is this :
SHOW CREATE TABLE fedtblname\G

and you can see the connection information (server IP, database, table name, username, password) of the real MyISAM table on the remote DB server, and all in plain text. As given by my update, that plain text info is located in mysql.servers.


why the “size” field of a collection stat is larger than “storageSize” or totalSize of a collection in mongodb. “count” : 1552400

As per Mongodb documentation:

  The total size in memory of all records in a collection. This value
  does not include the record header, which is 16 bytes per record, but
  does include the record’s padding. Additionally size does not include
  the size of any indexes associated with the collection, which the
  totalIndexSize field reports.

You have :

The number of objects or documents in this collection = 1552400
The average size of an object in the collection (plus any
padding)=1012

1552400*1012=1589657600

Output shows 1571028800 which is very close to what is expected.

How do you get 264282112?

Documents are compressed when stored in disk but storageSize shows allocated space.

  The total amount of storage allocated to this collection for document
  storage. The scale argument affects this value.

  storageSize does not include index size.


On the "MySQL Model" page you can cut and paste your objects between schemas:


Find out date and or number of error logs xp_readerrorlog

If you run EXEC sys.sp_enumerrorlogs; it will return one row for each log file, the oldest date in the log file, and the log file size.

Which means you can do something like this:

CREATE TABLE #error_log
(
    log_number INT,
    log_date DATE,
    log_size INT
);
INSERT #error_log ( log_number, log_date, log_size )
EXEC ( 'EXEC sys.sp_enumerrorlogs;' );

SELECT *
FROM   #error_log AS el;

Then set up a loop to grab the max log number, only grab logs after a certain date, or perhaps exclude logs based on size. Some small logs may be uninteresting, and some large logs may take too long to process.


Not Null Fields and Unmatched Number of Columns

I want to know what to do with the fields/columns that will not be filled with data and has a NOT NULL constraints? How should I approach this? I am new in this kind of task and my apologies for asking.

Remove the NOT NULL constraint on MySQL. You have columns that you don't need -- as evidenced by wanting to do this, and you're declaring them as necessary. There is nothing wrong with null. MySQL views NULL and NOT NULL as attributes of the type, and not constraints on the table. From the docs on the ALTER TABLE Syntax,

ALTER TABLE myTable
  MODIFY myCol INT NULL;

Where INT is the column type.

With PostgreSQL

For comparison, PostgreSQL has the more sane and standardized syntax,

ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL

Which looks like this,

ALTER TABLE myTable
  ALTER COLUMN myColumn
  DROP NOT NULL;


Error creating scalar function "Invalid Object Name dbo.fnMyFunctionName'"

Try refreshing the Intlisense cache.

Hit crtl+shft+R

From SQL Server Management Studio Keyboard Shortcuts

  Refresh local cache

Or under

  EDIT->IntelliSense->Refresh Local Cache


Securely remove columns (and traces thereof) from a SQL database

Well there is always a chance that someone can find the bits at various hardware layers if they had access to the physical disk(s), but I think you can be fairly confident you have removed traces of the data accessible through less nefarious means by:

rebuilding the table where the column was removed (perhaps perform a select into, drop the old table, rename the new one, and re-create any indexes)
starting the log chain over (a new full backup)
forcing the log to wrap around and re-zero-initialize by backing the log up twice and then performing a slight shrinkfile
eradicating any old full/diff/log backups (you may want to wait on this step until your data recovery window has passed, e.g. if you might still need to recover back to last Monday, wait until two Mondays from now - in the meantime moving them to a more secure location). At some point you know that a backup from two weeks ago is absolutely useless, because you're not going to restore to a point in time that far back, so why keep it around?


The Oracle docs for 11.2 ASM migration are here:

http://docs.oracle.com/cd/E11882_01/server.112/e18951/asm_rman.htm#OSTMG12000

These explain the process that you need to go through to migrate to ASM from filesystem storage.


You actually have two different questions here. With respect to the numbers shown in pg_stat_database, most of them are cumulative since the last time they were reset, e.g. by pg_stat_reset() or friends. So, for example, if you frequently query a one-row table you will eventually accumulate high numbers of tup_fetched and blk_read_time.


We do this, the environment varaiable is at the user level, so the user for each environment that runs the SQL agent jobs is different. So on the the dev environment our agent user is something like SQLDev and on the QA environment it is something like SQLQA.

If you are not running from jobs (which I highly suggest doing except on dev while doing actual development), yes you have to change the enviroment variable to the correct one. We have created cmd line scripts to do that easily and placed them on our desktops, so that we can easily switch the environment variables.


Cannot connect to database hosted at CentOS VM through SQL Developer on Windows

try to setting listener, for example

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.20)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

tnsname

DBORACLE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.20)(PORT = 2001))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dboracle)
    )
  )


By definition, "tablespace" field of pg_tables says "Name of tablespace containing table (null if default for database)". To get an exact tablespace name for a table or a table list you should first get the default tablespace for particular database. The query below lists all tables in schema myschema

WITH tbl_spc AS (
  --Get default tablespace for the database
  SELECT ts.spcname AS spcname
    FROM pg_tablespace ts
    JOIN pg_database db ON db.dattablespace = ts.oid
    WHERE db.datname = current_database()
)
SELECT
  t.schemaname,
  t.tablename,
  --if null then get default tablespace
  COALESCE(t.tablespace, ts.spcname) AS tablespace
FROM pg_tables t, tbl_spc ts
WHERE
  t.schemaname = 'myschema'


Email open rate calculations in subqueries with postgres

Answering my own question...but after playing around with it for awhile this seems to provide the correct answer:

SELECT
  MIN(timestamp) as "Date",
  "EmailEvents".send_name as "Send Name",
  cohort as "Cohort",
  product as "Product",
  COUNT(DISTINCT email) AS "Send Size",
  (100*openCount.distinctCount / COUNT(DISTINCT email))::numeric AS "Open Rate"
FROM
  "EmailEvents",
  (SELECT
   DISTINCT send_name,
   COUNT(distinct email) as distinctCount
   FROM
   "EmailEvents"
WHERE
   event='open'
GROUP BY
   send_name) openCount
WHERE
   "EmailEvents".send_name = openCount.send_name
   AND EXTRACT(month
   from
   timestamp) = EXTRACT(month
   from
   now())
GROUP BY
  "EmailEvents".send_name,
  product,
  cohort,
  openCount.distinctCount
ORDER BY
  MIN(timestamp) DESC

Hope this helps someone else looking for a similar solution!


Lots of small SQL statements in SQL Server and measuring network time between DB and Client

It's been a while since my Oracle days but IIRC, that's at the Ora net layer so the equivalent in SQL Server will be ODBC or .NET provider layer. Profiler is a client tool that calls SQL Trace in the server. It can't capture client-side network stats so if you're trying to measure client/server network latency, it won't help you.

You can track ODBC clients statistics by enabling it from the data source administrator. See https://msdn.microsoft.com/en-us/library/ms131411.aspx for details.

For .NET provider statistics see https://msdn.microsoft.com/en-us/library/7h2ahss8(v=vs.110).aspx

Jes has a nice blog about enabling it from SSMS if you are testing specific queries, see https://www.brentozar.com/archive/2012/12/sql-server-management-studio-include-client-statistics-button/

If you really want to get details, there are a bunch of other tools with varying levels of detail and abilities like netmon, tcping, tracert, etc... but they'll typically require manual correlation so I won't start with this.


JSON subquery using WITHOUT_ARRAY_WRAPPER returning odd data?

use  json_query,

SELECT @@SERVERNAME AS [Servername],
json_query(( SELECT [Name], [Recovery_Model_Desc]
    FROM sys.databases
    WHERE name in ('master', 'model', 'msdb')
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ) ) AS d
FOR JSON PATH, ROOT('ServerInformation')


No. The recover process always starts from a full database backup and then it applies the log backups.

If your database has no backups then it is not in full recovery mode. Untill you actually take a backup it will always run in simple, no matter the intented recovery mode (aka 'pseudo simple', see New script: is that database REALLY in the FULL recovery mode?)

If your database really is in FULL recovery mode, then a backup was tacken and the recovery can start with that.

There are third party tools that can analyze the log and recover stuff, but they work only if the log was not recycled, so is back to the discussion whether the recovery model is really full or simple.


It's unclear what you mean by comparing map-reduce to sharding. But the short answer is: sharding.

Generally speaking you design-out map-reduce queries, you do not want 100s of map-reduce queries being executed at once - you'd just overload mongo since that essentially means 100s of full collection scans all being run at the same time.

If you have an example of one of your existing map-reduce queries - please add it to your question.

Regarding sharding, it all comes down to what you use for the sharding key.

If you shard a users collection on username for example,

db.users.find()

will cause mongos to send the query to all shards and merge the result sets together (intelligently). Adding the sharding key to the query:

db.users.find().sort({username: 1}).limit(100);

would give mongos the option to talk to less mongod at a time.

a better example, if you query for:

db.users.find({username: /^bob/})

mongos will send the query to the shards whose shard keys indicate that they could contain answers, most likely that is one server only, resulting in a fast query and no extra load on the part of mongos.

Maybe the above examples are not-news to you.

The queries you send to mongo now are same syntax you'd use to send to a sharded database. The only thing you'd do differently is (previously) analysing on what keys to shard so that you can, where necessary, modify your queries to incorporate the sharding key and thereby enable mongos to act like a proxy instead of an agregator.

A poor sharding key,  or simply not taking advantage of sharding in the queries you are generating, would result in mongos needing to query all mongod servers for all queries resulting in high load and poor performance.


How is the number of records on a select determined as high or low by optimizer?

MySQL 5.7 Reference manual contains the following:

The output from EXPLAIN shows ALL in the type column when MySQL uses a full table scan to resolve a query. This usually happens under the following conditions:
The table is so small that it is faster to perform a table scan than to bother with a key lookup. This is common for tables with fewer than 10 rows and a short row length.
There are no usable restrictions in the ON or WHERE clause for indexed columns.
You are comparing indexed columns with constant values and MySQL has calculated (based on the index tree) that the constants cover too large a part of the table and that a table scan would be faster. See Section 8.2.1.2, “How MySQL Optimizes WHERE Clauses”.
You are using a key with low cardinality (many rows match the key value) through another column. In this case, MySQL assumes that by using the key it probably will do many key lookups and that a table scan would be faster.
For small tables, a table scan often is appropriate and the performance impact is negligible. For large tables, try the following techniques to avoid having the optimizer incorrectly choose a table scan:
Use ANALYZE TABLE tbl_name to update the key distributions for the scanned table. See Section 13.7.2.1, “ANALYZE TABLE Syntax”.
Use FORCE INDEX for the scanned table to tell MySQL that table scans are very expensive compared to using the given index:

SELECT *
FROM t1, t2 FORCE INDEX (index_for_column)
WHERE t1.col_name = t2.col_name;

Start mysqld with the --max-seeks-for-key=1000 option or use SET max_seeks_for_key=1000 to tell the optimizer to assume that no key scan causes more than 1,000 key seeks.

See the original page here:  https://dev.mysql.com/doc/refman/5.7/en/how-to-avoid-table-scan.html


You could test space-reduction using something like:

USE tempdb;
CREATE TABLE xc
(
    x INT NULL
);

INSERT INTO xc (x) VALUES (CASE WHEN (RAND() * 10) < 5 THEN NULL ELSE (RAND() * 10) END);
GO 10000

SELECT o.name, SUM(au.used_pages) * 8192 / 1048576e0
FROM sys.allocation_units au
    INNER JOIN sys.partitions p ON au.container_id = p.hobt_id
    INNER JOIN sys.objects o on o.object_id = o.object_id
WHERE o.name = 'xc' AND (au.type = 1 or au.type = 3)
GROUP BY o.name;

SELECT * FROM dbo.xc

ALTER TABLE xc ALTER COLUMN x INT SPARSE NULL;
ALTER TABLE xc REBUILD;

SELECT o.name, SUM(au.used_pages) * 8192 / 1048576e0
FROM sys.allocation_units au
    INNER JOIN sys.partitions p ON au.container_id = p.hobt_id
    INNER JOIN sys.objects o on o.object_id = o.object_id
WHERE o.name = 'xc' AND (au.type = 1 or au.type = 3)
GROUP BY o.name;

Running this drops the number of pages used by the table.


PostgreSQL: Using schema and table names from other tables?

You need dynamic SQL - perhaps something like this:

create role stack;
create schema authorization stack;
set role stack;

create or replace function f(p_schema in text, p_table in text)
                           returns setof integer language plpgsql immutable as $$
begin
  return query execute 'select value from '||p_schema||'.'||p_table;
end;$$;

create table t1(value integer);
insert into t1(value) values (1);
insert into t1(value) values (2);

create table t2(value integer);
insert into t2(value) values (1);

create table schemas(schema_name text);
insert into schemas(schema_name) values ('stack');

create table tables(table_name text);
insert into tables(table_name) values ('t1');
insert into tables(table_name) values ('t2');
insert into tables(table_name) values ('t1');
insert into tables(table_name) values ('t2');

select f(schema_name, table_name) from schemas cross join tables;
 f
---
 1
 2
 1
(3 rows)

I'm assuming that every table is present in every schema as the question implies


You just need to change the command delimiter that the client is looking for in order to split commands:

delimiter $$
CREATE TRIGGER new_trx_tmp_on_transaction_update AFTER UPDATE ON transaction
FOR EACH ROW
BEGIN
    IF NEW.trx_status = 1 AND ( NEW.cat_id = 'CASHBACK' || NEW.cat_id = 'REFUND') THEN
        INSERT INTO trx_tmp( `id`, `trx_id`, `user_id`, `trx_merchant_trxid`, `trx_amount`, `amount_left`, `cat_id`, `created_at`, `updated_at` ) VALUES ( NULL, NEW.id, NEW.userid, NEW.trx_merchant_trxid, NEW.trx_amount, NEW.trx_amount, NEW.cat_id, NOW(), NOW() );
    END IF;
END
$$

The default is a semi-colon, hence it complaining at the end of the INSERT line when you try and create your trigger.

It's all explained in the documentation here.


Delete duplicate keys with relation algebra

I would do something like the following (see the fiddle here):

Create and populate a table:

CREATE TABLE test
(
  key INTEGER NOT NULL,
  value INTEGER NOT NULL
);

INSERT INTO test VALUES
(1, 5), (2, 6), (2, 7), (2, 8), (4, 9);

and then:

SELECT * FROM test;

Result:

key     value
  1         5
  2         6
  2         7
  2         8
  4         9

and then run the following SQL:

SELECT key, value
FROM test WHERE key IN
(
  SELECT key
  FROM test
  GROUP BY key
  HAVING COUNT(key) = 1
  ORDER BY key
);

Result:

key     value
  1         5
  4         9

Which is the desired result. I have used PostgreSQL - the Open Source database which I would recommend that you use to learn relational database principles - it has a very high level of standards compliance and is a great learning tool.

The inner loop of SQL

SELECT key
FROM test
GROUP BY key
HAVING COUNT(key) = 1
ORDER BY key

produces those keys which have a COUNT of only 1 (in this case, the integers 1 and 4) and then the outer loop selects the values corresponding to those two integers (see the fiddle).


FAST_FORWARD cursors do not support parallelism (though the server generating the plan would need to be 2012 or above to get NonParallelPlanReason as part of the showplan XML).

When you specify FAST_FORWARD, the optimizer chooses between STATIC and DYNAMIC for you.

The provided execution plan shows the optimizer choosing a static-like plan.
Because the query contains aggregation, I doubt a dynamic cursor plan is even possible here. Nevertheless, requesting a FAST_FORWARD cursor type is preventing a parallel plan.

You should change the cursor type explicitly to either STATIC or KEYSET, for example. Both these cursor types can use parallelism.

That said, because this is an API cursor, changing the type of cursor would likely require an application change. Naturally, you would need to benchmark performance to check that changing the cursor type really is the best option for you.


Trying to connect to PostgreSQL with Entity Framework via Npgsql and C#

I recommend not using Entity Framework with anything else other than SQLSERVER. And then LINQ to SQL may be better. lightspeed was recommended to me as a better product. I personally have not used it.


Issues with module signing and SSIS catalog internal procedures

In Vedran's code which you reference, he also signs the stored procedure in the other database.  I do not see in your code that you have taken that final step.

Erland Sommarskog has an extensive discussion at http://www.sommarskog.se/grantperm.html of this problem.  This includes a discussion of, in his words, "the Problematic EXECUTE AS".

In the following link, which is for starting SQL Agent jobs, you can see yet another approach to delegating rights to a login which has no particular rights.  See: Allow non-sysadmin, non-owner of a SQL Server Agent job to execute it

If that appeals to you, you can create your own stored procedure to allow your user to start the SSIS process.

10/20/2014 Edit: "Cannot execute as the server principal because ..."

First of all, if you are a sysadmin, then the problem is not your rights, but the context you are trying to run.  (I believe that you are a sysadmin.)  So it could be something like:

If the server principle you want to use is a Windows Group it cannot be impersonated.
Have you recently restored a system database (e.g. msdb) from another server? Then the account you see in msdb may not match an account on the current server. (You might see the same name in database_principals, but the sid could be different.) If so, try using exec sp_change_users_login 'AutoFix','principalname'.
If the owner of a database (in master.sys.databases)is not the same sid as the
database's dbo user, then it cannot be impersonated.  (This is unlikely in msdb since sa is the normal owner of msdb.)
If the login was dropped, even if the user remains in the database, you may not be able to impersonate the account.

And there are likely other flavors as well.


postgresql: mapping json array to string array

Don't aggregate the whole row, just aggregate the column:
SELECT json_agg(invoice_number)
FROM
(
  SELECT DISTINCT invoice_number
  FROM sale.invoice_item
  WHERE product_id = ANY(_product_ids)
) record

Or even simpler:
SELECT json_agg(DISTINCT invoice_number)
FROM sale.invoice_item
WHERE product_id = ANY(_product_ids)


Finding out the hosts blocked by mysql server

Noted in 5.6.5 changelog.

MySQL now provides improved access to the host cache, which contains
client host name and IP address information and is used to avoid DNS
lookups. These improvements have been implemented:

A host_cache Performance Schema table exposes the contents of the
host cache so that it can be examined using SELECT statements. The
Performance Schema must be enabled or this table is empty.

If you upgrade to this release of MySQL from an earlier version, you
must run mysql_upgrade (and restart the server) to incorporate this
change into the performance_schema database.
The cache size is configurable using the host_cache_size system
variable. Setting the size to 0 disables the host cache.This is
similar to starting the server with --skip-host-cache, but
host_cache_size is more flexible because it can also be used to
resize, enable, or disable the host cache at runtime, not just at
server startup. If you start the server with --skip-host-cache to
disable the host cache, it cannot be re-enabled at runtime.
There are Connection_errors_xxx status variables that provide
information about the nature of connection errors and that can help
diagnose connection problems.

Improved access to host cache contents makes it possible to answer
questions such as how many hosts are cached, or how close hosts are
to being blocked (by checking whether the
host_cache.SUM_CONNECT_ERRORS column is approaching the value of the
max_connect_errors system variable).


Transfer data from standard mysql server to Percona XtraDB Cluster

If you have disk files, then the best would be to install 5.5 somewhere in order to get at the data.  5.7 is probably too advanced to be able to read 5.5 files without some hiccups.

If you have a data dump, then it will probably load into 5.7 with minimal hiccups.

A PXC node can be a Slave to your Master.  If we can get that set up, and if there are not conflicts with table names, etc, then we can feed the data into the cluster this way...  After setting up a Master with no data, "load" the data from a backup of your 5.5 data.  Probably (but I am not sure) a 5.5 Master should be able to replicate to a 5.7 Slave on a PXC cluster.

Or, it should be possible to simply load the backup directly onto any PXC node.  (Or the designated Master node, if you configured it that way.)

Do you have GTIDs enabled?  Galera type?  Or Oracle type?  (If enabled this may be another hiccup point.)

Sorry to be vague, but I hope I have given you some tips on where to look next.


This means this job is already runing. You might want to stop it and start it again, if you really need to re-run it.

Instead:

for a basic quick verification

I check the log reader on the publication, see if the log reader agent is reading the transactions and passing them to the distributor.

second thing

I go to the publication database and find out who is the distributor

sp_helpdistributor

then I connect to the distributor you got above
and run the following query to find out what is going on.

--Bocss Distribution
-- distribution_BOCSS is the name of my distributor database -make sure you change this to your distributor DB
--insert into tempdb.dbo.DistributorCheck
SELECT
(CASE
    WHEN mdh.runstatus =  '1' THEN 'Start - '+cast(mdh.runstatus as varchar)
    WHEN mdh.runstatus =  '2' THEN 'Succeed - '+cast(mdh.runstatus as varchar)
    WHEN mdh.runstatus =  '3' THEN 'InProgress - '+cast(mdh.runstatus as varchar)
    WHEN mdh.runstatus =  '4' THEN 'Idle - '+cast(mdh.runstatus as varchar)
    WHEN mdh.runstatus =  '5' THEN 'Retry - '+cast(mdh.runstatus as varchar)
    WHEN mdh.runstatus =  '6' THEN 'Fail - '+cast(mdh.runstatus as varchar)
    ELSE CAST(mdh.runstatus AS VARCHAR)
END) [Run Status],
mda.subscriber_db [Subscriber DB],
mda.publication [PUB Name],
right(left(mda.name,LEN(mda.name)-(len(mda.id)+1)), LEN(left(mda.name,LEN(mda.name)-(len(mda.id)+1)))-(10+len(mda.publisher_db)+(case when mda.publisher_db='ALL' then 1 else LEN(mda.publication)+2 end))) [SUBSCRIBER],
CONVERT(VARCHAR(25),mdh.[time]) [LastSynchronized],
und.UndelivCmdsInDistDB [UndistCom],
mdh.comments [Comments],
'select * from distribution_BOCSS.dbo.msrepl_errors (nolock) where id = ' + CAST(mdh.error_id AS VARCHAR(8)) [Query More Info],
mdh.xact_seqno [SEQ_NO],
(CASE
    WHEN mda.subscription_type =  '0' THEN 'Push'
    WHEN mda.subscription_type =  '1' THEN 'Pull'
    WHEN mda.subscription_type =  '2' THEN 'Anonymous'
    ELSE CAST(mda.subscription_type AS VARCHAR)
END) [SUB Type],

mda.publisher_db+' - '+CAST(mda.publisher_database_id as varchar) [Publisher DB],
mda.name [Pub - DB - Publication - SUB - AgentID]
FROM distribution_BOCSS.dbo.MSdistribution_agents mda
LEFT JOIN distribution_BOCSS.dbo.MSdistribution_history mdh ON mdh.agent_id = mda.id
JOIN
    (SELECT s.agent_id, MaxAgentValue.[time], SUM(CASE WHEN xact_seqno > MaxAgentValue.maxseq THEN 1 ELSE 0 END) AS UndelivCmdsInDistDB
    FROM distribution_BOCSS.dbo.MSrepl_commands t (NOLOCK)
    JOIN distribution_BOCSS.dbo.MSsubscriptions AS s (NOLOCK) ON (t.article_id = s.article_id AND t.publisher_database_id=s.publisher_database_id )
    JOIN
        (SELECT hist.agent_id, MAX(hist.[time]) AS [time], h.maxseq
        FROM distribution_BOCSS.dbo.MSdistribution_history hist (NOLOCK)
        JOIN (SELECT agent_id,ISNULL(MAX(xact_seqno),0x0) AS maxseq
        FROM distribution_BOCSS.dbo.MSdistribution_history (NOLOCK)
        GROUP BY agent_id) AS h
        ON (hist.agent_id=h.agent_id AND h.maxseq=hist.xact_seqno)
        GROUP BY hist.agent_id, h.maxseq
        ) AS MaxAgentValue
    ON MaxAgentValue.agent_id = s.agent_id
    GROUP BY s.agent_id, MaxAgentValue.[time]
    ) und
ON mda.id = und.agent_id AND und.[time] = mdh.[time]
where mda.subscriber_db<>'virtual' -- created when your publication has the immediate_sync property set to true. This property dictates whether snapshot is available all the time for new subscriptions to be initialized. This affects the cleanup behavior of transactional replication. If this property is set to true, the transactions will be retained for max retention period instead of it getting cleaned up as soon as all the subscriptions got the change.

hope this helps