How to do a cumulative count for certain values

I think that you want something like this (see fiddle here):

WITH item AS
(
  SELECT txt FROM ttable WHERE txt IN ('Eraser', 'Pen', 'Pencil')

  -- You have specified these three items as somehow "special", so that's where
  -- this bit comes from.

),
item_date AS
(
  SELECT DISTINCT i.txt, t.dtd FROM item i, ttable t  -- CROSS JOIN special items
  ORDER BY dtd, txt                                   -- and dates
)
SELECT id.dtd, id.txt, COALESCE(t.cnt, 0) AS icnt -- COALESCE to get 0 for NULLs
FROM item_date id
LEFT JOIN ttable t
  ON id.txt = t.txt
  AND id.dtd = t.dtd
UNION                             -- the UNION here is to reunite non-special items
SELECT t.dtd, t.txt, t.cnt        -- i.e. Other1 with the special ones.
FROM ttable t
WHERE t.txt NOT IN (SELECT txt FROM item)
ORDER BY dtd, txt

Result (dates separated for clarity as in your question):

                dtd txt   icnt
-----------------------------
2019-01-01 00:00:00 Eraser   0
2019-01-01 00:00:00 Pen     10
2019-01-01 00:00:00 Pencil  10

2019-01-02 00:00:00 Eraser   1
2019-01-02 00:00:00 Pen     20
2019-01-02 00:00:00 Pencil   0  -- << your INSERT has no Pencils for 2019-01-02

2019-01-03 00:00:00 Eraser   1
2019-01-03 00:00:00 Other1  10
2019-01-03 00:00:00 Pen      0
2019-01-03 00:00:00 Pencil   0

2019-01-04 00:00:00 Eraser   0
2019-01-04 00:00:00 Other1  10
2019-01-04 00:00:00 Pen      0
2019-01-04 00:00:00 Pencil   0


Not all columns show up on select * on DB2 for IBM i

There is an IMPLICITLY HIDDEN column attribute that can be applied. Please check for this...

This means the column will only appear when it is explicitly specified on the SELECT statement.

Check the CREATE TABLE statement for this property, or materialize the TABLE's DDL using some API or tool, such as RTVSQLSRC.


How can I use Spatial Indexes for timestamps (non-spatial 1d data)?

TIMESTAMP, DATETIME, INT, FLOAT, DECIMAL, etc are effectively "numbers".  Numbers can be ordered/searched/etc with ordinary BTREE indexes.  There is no need for SPATIAL for such one-dimensional quantities.

For date-like quantities, I like to do:

WHERE ts >= '2018-02-23'
  AND ts  < '2018-02-23' + INTERVAL 3 WEEK

INDEX(ts)

This avoids dealing with leap years, kludges like '23:59:59', inclusive/exclusive (not the careful choice of inequality operators), etc.


MYSQL Find (and Delete) Word Prefixes and Suffixes

DELETE t1.*
FROM table t1, table t2
WHERE LEFT(t1.word, 3) = 'sub'
  AND t1.word = CONCAT('sub', t2.word)


MySQL rapid Data Replication from multiple Databases

Some might say depends how big the databases are; I think regardless of the size, accessing the databases directly instead of having a "local" mySQL database to query would be best, for the following reasons:

No need for disk/memory/maintenance of the mySQL database
If the updates to the various databases happen every few seconds, and the users may only use the web page every few minutes/hours/days, you are executing a lot of queries that are not needed most of the time, which in turn places additional load on the six databases. In other words, I think the client doesn't realize the impact of having a replication scheme.  Even if just a few tables from each database is involved, the synchronization code running every few seconds would be much worse than occasional specific queries (occasional being something running every few minutes/hours).
The code to replicate data from 6 separate databases would be quite complex to do correctly.


PostgreSQL 10 - secure remote logical replication (SSH?)

For native SSL support:

On the subscription side, you can put sslmode=verify-full in the CONNECTION string for your CREATE SUBSCRIPTION, to force ssl to be used.

On the publication side, you can whitelist only the PostgreSQL port and only for the known IP address of the subscriber at the firewall.  There shouldn't be a need for fail2ban if you do this.  You can also demand that the subscriber side use ssl client cert authentication rather than password authentication, by using cert for the pg_hba method.

Of course you will need a CA to issue certificates, or run your own mini-CA.


SQL Server - How to prevent public connections?

As per your description, it seems to me that CONNECT SQL (or perhaps CONNECT) permissions has been assigned to the public server role.


Understanding multiple indexes with MySQL (and performance)

You speak of "the index" so I assume you mean "the clustered index."  I'd actually start by just slapping a unique index on phone_number - if you're confident that it really is and always will be unique, and if it's the field you most often filter by.

If it is unique, it's a good example of a "candidate key" - you could, technically, use it as a primary key.  However, it's also a "business key" - driven by human logic, and therefore susceptible to change, unlike an IDENTITY or other machine-generated sequence, like your phone_id field.  Biz keys are generally poor candidates for the PK, thus sticking with your current PK/clustered key.


After insert trigger only using single inserted virtual table row

The reason it's not working is that your join causes each row to be updated multiple times, but each update sees only the original values, not the cumulative values of previous updates. So your updates are in effect cancelling the previous updates.
(Or maybe SQL only allows one row update per statement. Either way the result is the same, with different resource use.)

Here's a quick and poorly formatted repro:

declare @insertedexample table (id INT, codeid int, value varchar(10))

INSERT INTO @insertedexample VALUES
(1,1,'newvalue1'),
(1,2,'newvalue2')

declare @targetexample table (id INT, codeid1value varchar(10), codeid2value varchar(10))

INSERT INTO @targetexample VALUES
(1, 'origvalue1', 'origvalue2')

SELECT * FROM @targetexample

UPDATE t
SET
t.codeid1value = CASE WHEN i.codeid=1 THEN i.value ELSE t.codeid1value END,
t.codeid2value = CASE WHEN i.codeid=2 THEN i.value ELSE t.codeid2value END
FROM @targetexample t
JOIN @insertedexample i on i.id = t.id

SELECT * FROM @targetexample

The only way I can think of at the moment to handle this is by building a dynamic query.

Aaron's suggestion of a view would certainly be better, provided users will only be filtering by fuelTransactions.id or the data will remain small enough to handle filtering after pivoting. (Edit: I later realised that it might also be possible to create a view, or a set of views, or certainly a function, that would allow efficient additional filtering, dependent of course on supporting indexing in the base table.)

UPDATE:

Actually you probably don't need a dynamic query, since the columns in the fuelTransactions table are fixed as are the efsCode codes you're interested in.

You might be able to use a pivot of one form or another, which incorporates the current values for the columns not being updated, even if you have to use a bunch of ugly subqueries.
I might have time to experiment with it, but can't promise.


I once came across a similar problem - I've just dug out the MSDN Blog article I used to fix it. Don't know if this will help you too?


I hit the EXEC sp_configure 'show advanced options', 1; But the "backup checksum default" is not showing

If you are sysadmin on the SQL Server instance, then you can run the following script to see which options are available:

SELECT [name] FROM sys.configurations

This will give you the following possible setting on a SQL Server 2012 instance:

configuration_id name
---------------- -----------------------------------
101              recovery interval (min)
102              allow updates
103              user connections
106              locks
107              open objects
109              fill factor (%)
114              disallow results from triggers
115              nested triggers
116              server trigger recursion
117              remote access
124              default language
400              cross db ownership chaining
503              max worker threads
505              network packet size (B)
518              show advanced options
542              remote proc trans
544              c2 audit mode
1126             default full-text language
1127             two digit year cutoff
1505             index create memory (KB)
1517             priority boost
1519             remote login timeout (s)
1520             remote query timeout (s)
1531             cursor threshold
1532             set working set size
1534             user options
1535             affinity mask
1536             max text repl size (B)
1537             media retention
1538             cost threshold for parallelism
1539             max degree of parallelism
1540             min memory per query (KB)
1541             query wait (s)
1543             min server memory (MB)
1544             max server memory (MB)
1545             query governor cost limit
1546             lightweight pooling
1547             scan for startup procs
1549             affinity64 mask
1550             affinity I/O mask
1551             affinity64 I/O mask
1555             transform noise words
1556             precompute rank
1557             PH timeout (s)
1562             clr enabled
1563             max full-text crawl range
1564             ft notify bandwidth (min)
1565             ft notify bandwidth (max)
1566             ft crawl bandwidth (min)
1567             ft crawl bandwidth (max)
1568             default trace enabled
1569             blocked process threshold (s)
1570             in-doubt xact resolution
1576             remote admin connections
1577             common criteria compliance enabled
1578             EKM provider enabled
1579             backup compression default
1580             filestream access level
1581             optimize for ad hoc workloads
1582             access check cache bucket count
1583             access check cache quota
16384            Agent XPs
16386            Database Mail XPs
16387            SMO and DMO XPs
16388            Ole Automation Procedures
16390            xp_cmdshell
16391            Ad Hoc Distributed Queries
16392            Replication XPs
16393            contained database authentication

  WARNING: Please do NOT change configuration settings in the table directly. Always use sp_configure <option> <value> to modify the instance.

Or you could read up on the configuration options on Technet:

Server Configuration Options (SQL Server 2012)


Cannot fetch the rowset from OLE DB provider "SQLNCLI" for linked server X

No, this is happening because you have a deadlocked resource.  What you need to do is capture the deadlock information with a SQL Trace (you can use SQL Profiler) or with trace flags.

This will give the information (and with a trace, you can get the deadlock graph) to show you what two sessions were having the conflict resulting in the deadlock and a victim to be chosen (which is why you see the error you posted).

Deadlocks happen.  If they are extremely infrequent then it's probably not an issue to worry about.  But if they are happening all the time, then you need to drill down into what query(ies) is(are) causing them.  You may need to rework your DML, or alter your transaction isolation level if it is appropriate (allowing dirty reads is not the answer), or even go with an optimistic isolation level like Snapshot Isolation or Read Committed Snapshot Isolation.  But great consideration needs to be made here, as those optimistic isolation levels utilize row-versioning which will hammer tempdb, so if you don't have an optimial configuration for tempdb you could bring your performance to its knees.


What you did applies to tables one at a time.  Instead...

Plan A:  Change datadir when installing.  (See documentation for details and caveats.)

Plan B:  Symlink .../mysql/data to some place in the other partition.

(And remove DATA_DIRECTORY, etc from CREATE TABLE.)


Why does sp_columns / sys.spt_columns_odbc_view return incorrect results?

I figured out why you get NTEXT but do not know why this is done. This note may be relevant:

  sp_datatype_info is equivalent to SQLGetTypeInfo in ODBC. The results
  returned are ordered by DATA_TYPE and then by how closely the data
  type maps to the corresponding ODBC SQL data type.

In the definition of sys.spt_columns_odbc_view there is a piece of code:

TYPE_NAME = CONVERT(sysname,
CASE
WHEN (
    t.system_type_id = 240
    OR
    t.user_type_id > 255
  )
  THEN -- CLR UDTs
  t.NAME
  ELSE d.type_name COLLATE database_default
END
), sys.types t ON (
  t.user_type_id = c.user_type_id
)
INNER JOIN sys.spt_datatype_info d ON (
  d.ss_dtype = c.system_type_id
  AND
  d.auto_increment = c.is_identity
)

In your case ELSE d.type_name COLLATE database_default this is being executed because the data type is -10.

sys.spt_datatype_info uses sp_datatype_info.

If you execute below code you will see where NTEXT is coming from.

EXEC sp_datatype_info -10;
GO


Prevent duplicate rows with many columns

Here's an example where a the unique constraint is part of the table creation command...

CREATE TABLE dbo.Ability_Main (
    Ability_ID SMALLINT NOT NULL,
    Ability_Text NVARCHAR (150) NOT NULL,
    Flag_Conditional BIT NOT NULL,
    Flag_Choose TINYINT NOT NULL,
    F1 BIT NOT NULL,
    F2 BIT NOT NULL,
    F3 BIT NOT NULL,
    F19 BIT NOT NULL,
    F20 BIT NOT NULL,
    CONSTRAINT UQ_AbilityMain_BitFlags
        UNIQUE (F1, F2, F3, F19, F20)
        --WITH (...) ON [FILEGROUP],
    CONSTRAINT PK_Ability_Main
        PRIMARY KEY CLUSTERED (Ability_ID ASC)
        WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
GO

Or added after the fact, in a separate command...

ALTER TABLE dbo.Ability_Main ADD CONSTRAINT UQ_AbilityMain_BitFlags UNIQUE (F1, F2, F3, F19, F20)
--WITH (...) ON [FILEGROUP];
GO


How can I achieve clustering of rows without the exclusive lock and logging overhead of the `cluster` command?

You can do this without using the cluster command and having the table locked or generating WAL for the whole table. The cost is that you need to full-scan the table regularly.

The basic idea is:

turn off autovacuum for the table
check each block to determine the degree of clustering
delete and re-insert all the rows from blocks below a clustering threshold
manually vacuum to free those (complete) blocks
repeat steps 2-4 as regularly as necessary

test schema sample data initially 'part-clustered':

create schema stack;
set search_path=stack;
create type t_tid as (blkno bigint, rowno integer);
create table foo(host_id integer, bar text default repeat('a',400)) with (autovacuum_enabled=false);
insert into foo(host_id) select mod(g,10) from generate_series(1,500000) g order by mod(g,10);
insert into foo(host_id) select mod(g,10) from generate_series(1,500000) g;
create index nu_foo on foo(host_id);

initial clustering statistics:

select cn, count(*)
from ( select count(*) cn
       from (select distinct (ctid::text::t_tid).blkno, host_id from foo) z
       group by blkno ) z
group by cn
order by cn;
/*
 cn | count
----+-------
  1 | 27769  <---- half clustered
  2 |     8
  5 |     1
 10 | 27778  <---- half un-clustered
*/
select count(distinct (ctid::text::t_tid).blkno) from foo where host_id=1;
/*
 count
-------
 30558  <--------- lots of blocks to read for `host_id=1`
*/

initial analyze (2146.503 ms):

explain analyze select count(bar) from foo where host_id=1;
/*
                                                           QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=15097.30..15097.31 rows=1 width=32) (actual time=2146.157..2146.158 rows=1 loops=1)
   ->  Bitmap Heap Scan on foo  (cost=95.17..15084.80 rows=5000 width=32) (actual time=21.586..2092.379 rows=100000 loops=1)
         Recheck Cond: (host_id = 1)
         Rows Removed by Index Recheck: 286610
         ->  Bitmap Index Scan on nu_foo  (cost=0.00..93.92 rows=5000 width=0) (actual time=19.232..19.232 rows=100000 loops=1)
               Index Cond: (host_id = 1)
 Total runtime: 2146.503 ms
*/

delete and re-insert the un-clustered rows:

with w as ( select blkno
            from (select distinct (ctid::text::t_tid).blkno, host_id from foo) z
            group by blkno
            having count(*)>2 )
   , d as ( delete from foo
            where (ctid::text::t_tid).blkno in (select blkno from w)
            returning * )
insert into foo(host_id,bar) select host_id,bar from d order by host_id;
--
vacuum foo;

new clustering statistics:

select cn, count(*)
from ( select count(*) cn
       from (select distinct (ctid::text::t_tid).blkno, host_id from foo) z
       group by blkno ) z
group by cn
order by cn;
/*
 cn | count
----+-------
  1 | 55541  <---- fully clustered
  2 |    16
*/
select count(distinct (ctid::text::t_tid).blkno) from foo where host_id=1;
/*
 count
-------
  5558  <--------- far fewer blocks to read for `host_id=1`
*/

new analyze (48.804 ms):

explain analyze select count(bar) from foo where host_id=1;
/*
                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=16110.64..16110.65 rows=1 width=32) (actual time=48.760..48.761 rows=1 loops=1)
   ->  Bitmap Heap Scan on foo  (cost=131.18..16098.14 rows=5000 width=32) (actual time=8.402..32.439 rows=100000 loops=1)
         Recheck Cond: (host_id = 1)
         ->  Bitmap Index Scan on nu_foo  (cost=0.00..129.93 rows=5000 width=0) (actual time=7.636..7.636 rows=100000 loops=1)
               Index Cond: (host_id = 1)
 Total runtime: 48.804 ms
*/

clean up:

drop schema stack cascade;

The above is workable now, but is a bit quirky (needing to turn off auto-vacuum for the table) and requires regular full-scanning the table. I think something similar without the disadvantages could built into postgres. You'd need:

A space efficient index to cluster on (this is coming in 9.4 with GIN compression, or better still in 9.5 with the new BRIN index type)
A 'vacuum-like' process that would scan that index to detect which blocks need to be deleted/reinserted (this would ideally be able to reinsert the rows into fresh blocks so auto-vacuum can be left at default)


How to avoid creating and dropping temp tables in functions?

Wouldn't a simple loop be enough?
create or replace function remove_vertices(par_geom geometry)
returns geometry as $$
DECLARE
  declare invalid_pnts record;
begin
  for invalid_pnts in select * from selfintersects(par_geom)
  loop
    -- do something with every record
  end loop;

  if not found then
    -- loop was empty
    return par_geom;
  end if;
end;
$$ language plpgsql;

Frequent creation of temporary tables will also bloat the system catalog and therefore slow down each and every query. It is really a bad idea to create and drop temporary tables (say a few tens per second) frequently in PostgreSQL.


What are the SET options that affect Plan Reuse and how to get their values in T-SQL?

If it's a case of different options causing different plans to be used, just check the options using sys.dm_exec_plan_attributes. If the options are not the same for a good and a bad plan, that can then be the reason.

A list of plan cache keys can be found in the following answer by Martin Smith:

What would cause parameter sniffing one one computer and not another?


If you are using Azure SQL Servers rather than Managed Instances or on-prem instances in VMs†, and are trying to create SQL Agent jobs†, then you can not: SQL Agent Jobs are not supported for Azure SQL.

There are a number of ways to automate tasks in Azure SQL. The official way it is heading appears to be Elastic Database Jobs, though this feature is still officiall in preview.

See these links for a bit more detail:
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-job-automation-overview
https://docs.microsoft.com/en-us/azure/sql-database/elastic-jobs-overview

[†] If you are using managed or VM instances, or are looking to create some other form of job, edit your question accordingly.


From what I can gather after some experiments, it seems that the prefix range is the one that must be split.


Running queries/jobs via SMSS - SQL service and SQL agent service accounts (SQL Server)

Job ownership is an important concept in SQL Server Agent. SQL Server Agent sets the security context for job execution based on the role of the user owning the job.

By default, SQL Server Agent executes job steps under the SQL Server Agent service account irrespective of job ownership, or under the context of a proxy account.
(about run as empty : Users who are not sysadmin have to access to the proxy account explicitly granted to their role or username)

The exception to this rule is T-SQL job steps, which execute under the security context of the job owner.

If the job owner is a member of the sysadmin role, then the job step executes in the context of the SQL Server Agent service account.

A common mistake when setting up jobs is to make "sa" the job owner – this will cause all T-SQL job steps to execute as the SQL Agent service account, which is a system administrator account. A better option is to set a non-sysadmin account as the job owner, and explicitly grant only the required database permissions to this account.

https://stackoverflow.com/questions/12112276/sql-agent-job-run-as-drop-down-list-is-empty

https://www.red-gate.com/simple-talk/sql/database-administration/setting-up-your-sql-server-agent-correctly/


Tables in one of my schemas are not listed in INFORMATION_SCHEMA.TABLES. What can be the reason?

Aaron Bertrand has an excellent post here about why you shouldn't trust the INFORMATION_SCHEMA views - unreliable information_schema schema information on SQL Server?

To summerize:

  1). The wording in the documentation is not accurate, and it is in the
  process of being corrected (see Connect #686118). I'm not sure if they
  will correct the 2005, 2008 and 2008 R2 docs all at once, or whether
  the older versions will even get updated. The point is that I can not
  envision a case where the schema in either view is incorrect, but even
  more so, that info_schema is incorrect while sys.objects is correct.
  The latter is impossible - the info_schema view is based entirely on
  the sys.objects view (just look at SELECT OBJECT_DEFINITION (OBJECT_ID
  ('INFORMATION_SCHEMA.TABLES'));), so if one is incorrect, they're both
  incorrect. There are probably obscure cases where they can both be
  incorrect, but not in current versions (for example, in SQL Server
  2000, with the config option allow updates enabled, delete from
  sysusers a user who owns an object - not really relevant or possible
  today, and not something I'm willing to try, but it's the only one I
  can imagine would have motivated the current wording at any point in
  time).

  2). In general, INFORMATION_SCHEMA views should be avoided in favor of
  the catalog views introduced in SQL Server 2005 (and augmented since
  then). Why? Because the catalog views continue being developed as new
  features are added to SQL Server, while the info_schema views have
  not. As I mentioned in my comment, try to find information about
  filtered indexes in info_schema. Same goes for included columns, XML
  indexes, identity/computed columns, foreign keys against unique
  indexes - these are all either missing entirely or represented
  differently in the info_schema views. In Denali they added an
  info_schema view for Sequences but again this meets the bare minimum
  of the standard and doesn't include any information about SQL
  Server-specific implementation details (for example, whether it's
  exhausted, and if they add any new features in the future you can be
  sure the info_schema view will not be kept in the loop). The only case
  where you would stick to info_schema views is if (a) you are writing
  metadata routines that need to work across info_schema-compliant
  platforms AND (b) you aren't using any platform-specific features that
  will be missed. Aside from multi-platform vendor tools this is
  probably a pretty rare scenario (and even in that case may lead to
  displeased customers who are using those features and the tool didn't
  pick them up).

  3). I filed a separate Connect suggestion (Connect #686121) that they
  plaster a warning about this incompleteness on all INFORMATION_SCHEMA
  view topics in Books Online. I don't think it's very well known that
  they are not the preferred way to get metadata out of SQL Server, and
  who could blame folks for not seeing this - after all, we're always
  told that using standards-compliant methods is a "best practice" and
  using proprietary methods are the opposite. As with a lot of database
  things, "it depends" - but I suspect, more often than not, you're
  better off using the sys catalog views unless you're in that rare
  scenario where you're using only the features in SQL Server that are
  common to the standard. I don't think I've come across a single
  instance in any capacity where this was the case, but I'm more than
  happy to learn of them if they do exist.


Does READ UNCOMMITTED make any performance difference on static data?

If your data is immutable mark the database as read only. The query execution engine recognizes this and skips locking. However the benefits are marginal. If locking is of concern it means that a) you have locking conflicts, which implies data is not immutable or b) you acquire too many locks, which implies large scans which needs to be addressed by adding an index as needed.

For more details read How to analyse SQL Server performance.


What's the difference between INSERT ... SELECT and SELECT INTO

SELECT INTO is usually used to select specific set of data into a table, esp., during scenarios when the data in the table is the priority and not the constraints. It automatically creates a table if there is no such table already. But, INSERT INTO is used when you already have a table that has specific defined constraints and need to add data from a different table.


ASPECT #1

While setting innodb_max_dirty_pages_pct to 0 is good to do prior to a dump, you will have to wait until the dirty page count falls below 1% of the InnoDB Buffer Pool size. Here is how you can measure it:

SELECT ibp_dirty * 100 / ibp_blocks PercentageDirty FROM
(SELECT variable_value ibp_blocks
FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_total') A,
(SELECT variable_value ibp_dirty
FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_dirty') B;

Keep running this report until PercentageDirty reaches close to 1.00. Perhaps you could just set innodb_max_dirty_pages_pct to 0 one hour before the dump.

If you do not change innodb_max_dirty_pages_pct, a mysqldump will forec a flush of dorty blocks involving the table you are dumping.

ASPECT #2

You should not have to prepend "SET autocommit=0; SET unique_checks=0; SET foreign_key_checks=0;" because a mysqldump includes them at the beginning of the dump. Here is a sample mysqldump's header (Please note the two lines after TIME_ZONE)

-- MySQL dump 10.11
--
-- Host: localhost    Database: dbAccessData
-- ------------------------------------------------------
-- Server version       5.0.51a-community-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Current Database: `dbAccessData`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `dbAccessData` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `dbAccessData`;

ASPECT #3

Please run this query

SELECT engine,count(1) table_count FROM information_schema.tables
WHERE table_schema='mysql' GROUP BY table_schema;

I ran this and got 25 for MySQL 5.5.23. Since you have 26 you have only 1 tables outside the mysql schema. To find it, run this:

SELECT table_schema,count(1) table_count FROM information_schema.tables
WHERE engine='MyISAM' GROUP BY table_schema;

If you stop writing to the one lone table, you should be able to mysqldump all databases just fine.

ASPECT #4

All the needed options for --opt are adequate. No need to alter it.

ASPECT #5

You may want to dump the databases into different file: Please see my Apr 17, 2011 post How can I optimize a mysqldump of a large database? on how to script parallel mysqldumps.


Can't restore my distribution DB postgres 9.2 backup into my staging DB postgres 9.3

I found several bug reports indicating that pg_dump has problems writing to a pipe on Windows:

BUG #7794: pg_dump: errors when using pipes/streams
BUG #6726: pg_dump - ftell mismatch when using -Fc and split

Instead of writing to stdout and redirecting to a file, try using -f specify the output file:

pg_dump -Fc -U "username" -w "dbname" -f "filename".dmp


Cannot overwrite file: /var/lib/mysql/ibdata1 when using Percona xtrabackup

You should be restoring to a completely empty folder - you need to remove all the content (files and folders) from the target folder before doing the restore. Xtrabackup contains the entire system, not just a particular database. As always, test the restore on your test environment before attempting on production! And remember to shut down MySQL before attempting the restore or cleaning the target folder...


Delete duplicate with same ID PHP/PDO/MYSQL

It seems like just the session id would be 'unique' for defining the User?  And maybe a separate table would hold the multiple bell_mode values for each user?  You have to think through the application to figure out what is "unique"; we cannot do it for you.

CURDATE() is a date, not a datetime.  NOW() is a datetime.

CURDATE() < ring_date -- before midnight this morning

It is clumsy to have two separate columns for a "date" and a "time".  I strongly suggest you combine those two columns into a DATETIME datatype; I'll call it ring_dt.  DATE(ring_dt) and TIME(ring_dt) are the 'date' and 'time' parts of ring_dt.  It's easier to take a DATETIME apart than to put it together.

CURDATE() < ring_dt -- same as above
ring_dt > NOW() - INTERVAL 1 DAY  -- more than 24 hours ago (to the second)
ring_dt > CURDATE() - INTERVAL 1 DAY  -- before yesterday's 12am. (not very useful)

- INTERVAL 1 DAY and - INTERVAL 24 HOUR have the same effect.


SQL Server Data Warehouse stored procedure location

This is a matter of taste.  The main reason I see to break things up at all is for security, and secondarily for backups.

I think of ETL activity, including loading the final star schema tables, as an aspect of the Staging layer.  That's where all the "heavy lifting" happens in my warehouses.

Here's how I typically break up a data warehouse.  These should be distinct physical databases, and may be separate VMs or physical machines if the volume of data requires it.

A separate database for each important data source (CRM, OLTP database, telephony system, etc.).  Here, I store a straightforward copy of data from the source system, with a minimum of modification.  E.g., I will record DateCaptured and DateModified for each record, but perform no cleanup of data values or types.  The idea is to grab data as quickly as possible, to minimize load on the source system.

In principle, these databases can be purged at will and repopulated from the source systems with no change to the rest of the warehouse.
Using separate databases for each source lets users (i.e., service accounts) have limited access.  My Service_SalesforceReader user has read and write privs in the Salesforce database, and nowhere else.
Writing to these databases typically happens via dedicated ETL applications, with a few SQL procedures supporting them.

A Staging database for the intermediate steps; the "T" in "ETL".  This includes lookup tables for data cleaning, tables for allocating synthetic keys, and log tables for ETL processes.

Data here is mostly static which can be recreated from source control (e.g., country codes) or transformations of the source data, which can be recreated from those systems, albeit perhaps with different synthetic keys being created.  Only people on the warehouse team should need access here.
This database has 90% of the SQL procedures.  They format and pivot the data into the star schema layout. There's one objective reason to keep the ETL logic in Staging: security. Only warehouse admins need access to those procedures, whereas many people may need access to the Warehouse layer.

A Warehouse database, with tidy fact tables and dimension tables.  This layer may have views to help with specific consumers.  E.g., if you have a visualization tool that doesn't play nice with joins, there might be a view (possibly materialized) for each star, with all dimensions joined in.

The only sprocs I have in this layer, in my current warehouse, are for logging, and I don't believe they're actually used.

In practice, you might need to grant some power users access to specific source databases, if there's data they need that doesn't rate being incorporated into the star schema.  In such a case, you may also need to grant access to the synthetic key tables in Staging that link them together, so they can traverse from (for example) a CustomerSK integer to a Salesforce AccountID string.  Doing so makes it difficult for you to change anything without breaking people's processes, so this should be a last resort.


Is there any way to use different InnoDB settings for different databases on the same server?

YOUR QUESTION
The reason there is no way to achieve this has to do with the InnoDB Architecture

There are three(3) things in this which makes different flushing behavior per database impossible

SINGLE BUFFER POOL
SINGLE LOG BUFFER
FUZZY CHECKPOINTING

These three(3) things work together. The log buffer flushes changes from the buffer pool into system tablespace files but there is no way to control how much or how little to flush from the log buffer and buffer pool (in more than one direction).
Look at a more sophisticated RDBMS : Oracle RAC. Scaling out Oracle RAC simply creates a log buffer per RAC Node to distribute writes to one or more servers that have the data files. You could probably unleash CHECKPOINT commands to flush the Log Buffer at a RAC Node. If you could, this would still not be wise (and your scenario impossible) because you cannot control which blocks are written to which RAC Node. Why? Even in a distributed architecture like Oracle RAC, you still have a single Data Dictionary. This is much more so the case with InnoDB when it comes to controlling changes and flushing of dirty pages.
Flushing databases differently puts the InnoDB Storage Engine at risk of losing all data integrity altogether.
ALTERNATIVE SOLUTION #1
Create another MySQL instance on port 3307 (or a different number other than 3306) on the same server. That second instance would have its own Log Buffer and its own Checkpointing. Using a separate my.cnf for the second instance, configure innodb_flush_log_at_trx_commit at your discretion since it will be independent of the first instance's flushing behavior.
ALTERNATIVE SOLUTION #2
Take the source code and implement multiples log buffers (each with a dedicated buffer pool), including a mechanism for tweeking the flushing of commits per log buffer (You'll be a millionaire in 2 years.
Give it a Try !!!


Way to ensure just one connection to a DB2 database

The easiest way to do this would be to simply uncatalog and then re-catalog the database with a different name (perform this as the instance owner on the database server):

db2 uncatalog database SAMPLE
db2 catalog database SAMPLE as XXX on <path>

... perform maintenance activities ...
db2 uncatalog database XXX
db2 catalog database SAMPLE on <path>

Any user trying to connect locally on the database server, using connect to sample, will get the error:

SQL1013N  The database alias name or database name "SAMPLE" could not be found.  SQLSTATE=42705

However, for remote connections to also be unable to connect, you'll have to restart the DB2 instance.  This is required because the DB2 TCP Connection Manager (db2tcpcm) will cache the the database catalog entries, so until the EDU is restarted it will still "know" about the database.  After restarting the DB2 instance, remote connections will get the error:

SQL30061N  The database alias or database name "SAMPLE            " was not
found at the remote node.  SQLSTATE=08004

Having to restart the instance isn't the best solution, but it works.


Order a nested table with path index according to list_order in SQLite

Needed ordering may be performed by the next query:
WITH
cte AS ( SELECT *, CAST(list_order AS TEXT) as ord
         FROM items
         WHERE parent_id = 0
       UNION ALL
         SELECT items.*, cte.ord || '.' || items.list_order
         FROM items
         JOIN cte ON cte.id = items.parent_id )
SELECT *
FROM cte
ORDER BY ord

https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=a7957edeabb5a9a1c9dff2d2e92ffe0c
If list_order values above 9 are present then you must apply zero-padding expression to its value. For example
WITH
cte AS ( SELECT *, printf('%04d', list_order) as ord
         FROM items
         WHERE parent_id = 0
       UNION ALL
         SELECT items.*, cte.ord || printf('%04d', items.list_order)
         FROM items
         JOIN cte ON cte.id = items.parent_id )
SELECT *
FROM cte
ORDER BY ord

https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=52d6d43db054cb64754c37d3820c0b1e

do you have any trick to make the zero-padding dynamic? – alexandre9865

WITH
printf_format AS ( SELECT '%0' || LENGTH(MAX(list_order)) || 'd' AS format
                   FROM items ),
cte AS ( SELECT items.*, printf(printf_format.format, list_order) as ord
         FROM items
         CROSS JOIN printf_format
         WHERE parent_id = 0
       UNION ALL
         SELECT items.*, cte.ord || printf(printf_format.format, items.list_order)
         FROM items
         JOIN cte ON cte.id = items.parent_id
         CROSS JOIN printf_format )
SELECT *
FROM cte
ORDER BY ord

https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=728695f41db0913bcde76370f70c885a


What's the maximum number of partition per table I can/should have in MySql 8?

The maximum number of partitions allowed in MySQL 8 for InnoDB tables is 8192 (from https://dev.mysql.com/doc/refman/8.0/en/partitioning-limitations.html). This includes subpartitions.

In general, partitions do not on their own improve performance, but there are a some scenarios where they can help:

Secondary indexes are local to the partition, so if you for example bulk insert data one partition at a time (or in order), it makes it more likely the secondary indexes fit into the buffer pool which can improve insert performance.
For partition pruning. This can for example mean that MySQL effectively uses two indexes for the same table without doing an index merge.
Managing logical grouped data such as moving all data that belongs to one partition to another table (for example to move it to another MySQL instance) or to delete all data in a partition.

One thing to be aware of if you have many partitions for a table is that by default each partition gets each own tablespace file (.ibd file). So if you have 8192 partitions, the table will end up using 8192 files.


Can a SQL Server Subscription Database be unsubscribed and used as standalone?

Yes .. you can remove replication.

USING GUI :

On the publication server, Right Click on the subscription and delete it. Make sure you select "connect to subscriber" so that all the replication bits are properly cleared.

USING TSQL :

---  removes replication objects from the subscription database.

-- Remove replication objects from the subscription database .
DECLARE @subscriptionDB AS sysname
SET @subscriptionDB = N'AdventureWorks2008R2Replica'

-- Remove replication objects from a subscription database (if necessary).
USE master
EXEC sp_removedbreplication @subscriptionDB
GO

----  script disables publishing and distribution on a server that is a Publisher and Distributor and drops the distribution database.

-- This script uses sqlcmd scripting variables. They are in the form
-- $(MyVariable). For information about how to use scripting variables
-- on the command line and in SQL Server Management Studio, see the
-- "Executing Replication Scripts" section in the topic
-- "Programming Replication Using System Stored Procedures".

-- Disable publishing and distribution.
DECLARE @distributionDB AS sysname;
DECLARE @publisher AS sysname;
DECLARE @publicationDB as sysname;
SET @distributionDB = N'distribution';
SET @publisher = $(DistPubServer);
SET @publicationDB = N'AdventureWorks2008R2';

-- Disable the publication database.
USE [AdventureWorks2008R2]
EXEC sp_removedbreplication @publicationDB;

-- Remove the registration of the local Publisher at the Distributor.
USE master
EXEC sp_dropdistpublisher @publisher;

-- Delete the distribution database.
EXEC sp_dropdistributiondb @distributionDB;

-- Remove the local server as a Distributor.
EXEC sp_dropdistributor;
GO

Refer to How to: Disable Publishing and Distribution (Replication Transact-SQL Programming)

MSDN Blog has How to cleanup Replication Bits

EDIT: Based on the comments :

For migration to new server, you are better off

If the database is not big (more than 500GB+) and you have a good maintenance window

Go for backup and restore method.

If the maintenance window is small then

Either you can choose between LogShipping or Database Mirroring.
When you want to cut over to the new server -

With logshipping, just take the final Tail log backup and restore on the new server with recovery.
With Mirroring, you can switch to synchronous mode when you decide to failover to the new server.

Above methods will help you to migrate your database to new server with minimum downtime. Obviously, there are many other pre and post migration steps that you need to be aware.


Convert rows to column for every nth row by ID

Here's the code I ran to simulate the problem you had and see what the error message was and what to do, however when I ran it - it ran successfully.

CREATE TABLE #TestTable
(   [user_id]   varchar(50) NULL
,   [meta_key]  varchar(50) NULL
,   [meta_value]    VARCHAR(50) NULL
)

INSERT INTO #TestTable
VALUES  ('16'   , 'first_name'          , 'Laxgdfds'    )
    ,   ('16'   , 'last_name'           , 'Narentdsaa'  )
    ,   ('16'   , 'stillingsbetegnelse' , 'Andet'       )
    ,   ('18'   , 'first_name'          , 'Riedsa'      )
    ,   ('18'   , 'last_name'           , 'Joe'         )
    ,   ('18'   , 'stillingsbetegnelse' , 'Andet'       )

SELECT  P.*
FROM    #TestTable
PIVOT   (MAX([META_VALUE])
         FOR [META_KEY] IN ([FIRST_NAME], [LAST_NAME], [STILLINGSBETEGNELSE])
        ) AS P

So I would recommend 1) copying and running my code, and if that doesn't work responding back with the error message. 2) If my code works with no error message, then I would recommend reviewing your original code for possible typos.


This is the expected behavior.
When you restore using "With no recovery", it tell SQL to leave the DB in the "recovery" state (allowing you to restore subsequent backup).
If you want to make the database available, you can run a
Restore database [dbname] with RECOVERY;

Note that running this command will bring the DB online and you will be able to run query in it but it will also make all other restore impossible.
If you need to look at the data while in the middle of the restore process, you could use the "with standby" option
Restore database [dbname] from disk =N'path to backup' with STANDBY=N'path to the standby file that will be created'

Your other option would simply be to complete your restore (your transaction log backup) and make sure that on the last one, you change the "norecovery" by "recovery"


Connect to SQL Server 2012 running on Azure VM via local SSMS 2012

Yes, this is well documented.

Once you have configured the firewall, the ports, and your instance to use SQL authentication, you should be able to connect to the computer name (i.e. name.cloudapp.net)


You can cast text fields as varchar(max)

Declare @Table1 Table(id text,name nvarchar(200))
Insert Into @Table1 (id, name) Values
('123', 'Joe'), ('222.00', 'Jim')
Declare @Table2 Table (id text,green nvarchar(200))
Insert Into @Table2 (id, green) Values
('123', 'JDSKLJS'), ('222.00', 'KLJKLJJKL');

Select
*
FROM @Table1 t1
INNER JOIN @Table2 t2
ON cast(t1.id as varchar(max)) = cast(t2.id as varchar(max))
GO

id     | name | id     | green
:----- | :--- | :----- | :--------
123    | Joe  | 123    | JDSKLJS
222.00 | Jim  | 222.00 | KLJKLJJKL

dbfiddle here


Unable to start SQL Server Instance 2008 R2 - DB file corrupt

Look here  http://msdn.microsoft.com/en-us/library/dd207003.aspx.  It's for 2012 but 2008 is basically the same.  It describes how to use the setup.exe to rebuild the system databases.  From there you have to do restores for your system databases & patch your instance but it works.  If you don't have backups of your system databases you can copy the files off (not master obviously) and copy them back after the rebuild.  It's not recommended but it will work.


change encoding of database from latin1_swedish_ci to utf8

If you have any stored procedures that use database defaults, you must drop and recreate those stored procedures.

If the ALTER DATABASE command runs quickly, chances are the tables were not altered.

You may have to run ALTER TABLE on all your tables as follows:

ALTER TABLE tblname CONVERT TO CHARACTER SET charset_name [COLLATE collation_name];


How to list all views in SQL in PostgreSQL?

From the documentation:

 select table_name from INFORMATION_SCHEMA.views;

If you don't want the system views is your result, try this:

 select table_name from INFORMATION_SCHEMA.views WHERE table_schema = ANY (current_schemas(false))


If you are modifying any object in Oracle where a synonym is linked, there is no alternative, you should recreate the synonyms.

I found that because in my application I have sometime the need to recreate custom views (e.g. for attribute length) , and each time i need to recreate synonyms.

Even adding a rows in a table lead the synonym to be invalid.


Permissions Dropped after Package Failed

The DB logins from live will overwrite your local DB logins. So, the local DB's logins, which contain the application user may be wiped out, unless it exists in the live DB also. Sometimes, the mappings between the DB user and the schema will get messed up. You can then recreate the user or use sp_change_users_login to fix the DB user mappings.


300 inserts per minute, 6 million rows per month

There are basically two ways to answer your question: speculation and simulation.

For the former you still have not provided much useful information, such as:

What is your current workload and how does your server behave today?
What are your current data volumes?
What are you planning to do with your 6 million new rows a month? Keep them indefinitely? Archive them after 3 months?

As for simulation, it would likely take you only a day or two to clone your existing database, creating a test system; download and install Apache JMeter; write a simple test plan generating your 300 inserts/second plus your queries; and run it to see how the system actually behaves under your projected workload. This will let you (or a temporary consultant, as suggested by others) to identify performance bottlenecks and determine whether they can be resolved by throwing additional hardware at them.


Using multiple disks for large InnoDB database

The best way to use multiple similar drives is to use RAID-0 striping.  While you are at it you could add a little bit of security by using RAID-5 - one drive is logically a parity for the others; you can suffer lost of any drive without loss of data.

Once you have RAID, you can increase write performance by having a "Battery Backed Write Cache" on the Raid controller.  This gives you near-instantaneous writes without loss of security.

By striping, you get better sharing of the I/O across the drives than you could possibly do manually.

By striping, you get one large (logical) drive, and do not have to manually worry about which ibd goes where.

I would suggest putting the largest 20% of the tables in .ibd files; the rest in ibdata1.  This becomes especially important if you have thousands of active tables.  Otherwise, the proliferation of .ibd files has other consequences on table caches, etc.  (Since this suggestion is not 'automatic', you may not want to do it.)

Unfortunately, you probably have to dump and reload the entire database to add drives to a RAID system.  LVM might have a distinct advantage if you want to avoid that.  (But it won't have the other advantages of RAID.


Database Design: What is the best structure for storing site offices and locations?

Here is some vanilla SQL:

CREATE TABLE RoomTypes
(
 RoomType VARCHAR(12) NOT NULL,
 UNIQUE (RoomType)
);

CREATE TABLE Zones
(
 Zone VARCHAR(10) NOT NULL,
 UNIQUE (Zone)
);

CREATE TABLE Rooms
(
 RoomType VARCHAR(12) NOT NULL,
 Zone VARCHAR(10) NOT NULL,
 Name VARCHAR(30) NOT NULL,
 UNIQUE (RoomType, Zone, Name),
 FOREIGN KEY (RoomType) REFERENCES RoomTypes (RoomType),
 FOREIGN KEY (Zone) REFERENCES Zones (Zone)
);

CREATE TABLE NumberedRooms
(
 RoomType VARCHAR(12) NOT NULL,
 Zone VARCHAR(10) NOT NULL,
 Name VARCHAR(30) NOT NULL,
 CHECK (RoomType = 'Numbered'),
 UNIQUE (Name),
 UNIQUE (RoomType, Zone, Name),
 FOREIGN KEY (RoomType, Zone, Name)
    REFERENCES Rooms (RoomType, Zone, Name)
);

CREATE TABLE AncillaryRooms
(
 RoomType VARCHAR(12) NOT NULL,
 Zone VARCHAR(10) NOT NULL,
 Name VARCHAR(30) NOT NULL,
 CHECK (RoomType = 'Ancillary'),
 UNIQUE (Zone, Name),
 UNIQUE (RoomType, Zone, Name),
 FOREIGN KEY (RoomType, Zone, Name)
    REFERENCES Rooms (RoomType, Zone, Name)
);

The CHECK constraints will not be tested by mySQL e.g. do the tests yourself using triggers. Consider adding other tests e.g. that attribute Name in table NumberedRooms represents an integer.

The idea that every row in the supertype table Rooms will have exactly one row in the union of AncillaryRooms and NumberedRooms. This is merely implied e.g. have 'helper' procs to add rows to both super- and subtype tables as a single operation and use triggers to ensure it is done.

Note NumberedRooms has a simple key Name alone, whereas AncillaryRooms has a compound key on (Zone, Name). All three (non-lookup) tables have a key on (RoomType, Zone, Name) throughout, allowing further subtype tables to referencing them and maintain integrity by further testing for valid RoomType values.


MySQL doesn't support it natively, but this is possible if you install the user-defined functions from the lib_mysqludf_preg library.

You should be able to then do something like this:

UPDATE table_name
   SET column_name = PREG_REPLACE('regex_match', 'replacement_value', column_name);

This is third-party software but it uses the standard MySQL User-defined function interface to enable custom functions written in C/C++ to interact and behave like built-in functions (e.g., REPLACE()) ... but UDFs are essentially plug-ins that can be installed and uninstalled without having to recompile the server or even having to shut it down and restart.

Above, the 'regex_match' and 'replacement_value' are the regular expression and replacement values, which can be from columns, variables, or supplied in the query as string literals.  In the last case, they need to be quoted.


Is it possible to have change tracking ONLY on a replication server, not original?

if you can consider not only SQL Server options take a look at GoldenGate.
We have a project to deliver content (ongoing changes) + change tracking from Oracle to Greenplum database. GoldenGate supports SQL Server as well, so idea and even parameter files for the processes are the same.
On a high-level it looks like:

capture transactions record info on source system (EXTRACT process)
deliver to the target independent system (it can be SQL Server, or whatever supported by GoldenGate) - (PUMP process)
apply changes not as DML operations but as full records with additional attributes like "operation name", "commit timestamp" and else you can get from trail files generated on source (REPLICAT process).
You can record only PK values or full row values into change tracking. It's just a matter of design.

EXTRACT and PUMP processes are quite simple. The most interesting here is REPLICAT config:

REPLICAT <PROCESS NAME>
INCLUDE ./dirprm/DBLOGIN.inc
INCLUDE ./dirprm/REPORT.inc
MACRO #map_history
PARAMS (#table)
BEGIN
IGNORETRUNCATES
MAP <SRC_SCHEMA>.#table, TARGET <TGT_SCHEMA>.#table,
  INSERTALLRECORDS,
  COLMAP (USEDEFAULTS,
    OPERATION = @GETENV('GGHEADER', 'OPTYPE'),
    CSN = @GETENV('TRANSACTION', 'CSN'),
    RSN = @COMPUTE(@COMPUTE(@NUMSTR(@GETENV('RECORD', 'FILESEQNO')) * 100000000000) + @NUMSTR(@GETENV('RECORD', 'FILERBA'))),
    CSN_TIMESTAMP = @GETENV('GGHEADER', 'COMMITTIMESTAMP')
  );
END;
INCLUDE ./dirprm/DML_REPLICAT.inc
#map_history(<TABLE1>)
#map_history(<TABLE2>)
...
#map_history(<TABLEN>)

This works well for hundreds of millions operations per day (including LOBs).

Hope it helps to get an idea of the one of the potential solution.


How to Import Data to MySQL RDS Instance?

When we did something similar recently ~250GB, we first used an EC2 slave from production database as a staging slave with SSL and binary logging enabled  -- to make table dumps, etc. Converting any tables to InnoDB if you need here. Load data dumps from EC2 to RDS. Then used a binary log feeding strategy to load data and keep it current. At cutover time, flush the last binary log-- and load it into RDS and you're done. I prepped the logs first (removing any set cmds that require super) and just fed them to the mysql client on rds. Next time I want to try Tungsten replicator.


Create two tables with identical superkeys?

A separate table would give you the flexibility of having realms that are in more than one group. If it makes sense to do that in your business context, or it might be something you would want in the future, then you might want to seriously consider that option. Otherwise, having the Realms.realmGroupID as nullable is simpler and easier to work with.


There's no way to clear the caches easily. On Linux you can stop the database server and use the drop_caches facility to clear the OS cache; be sure to heed the warning there to run sync first.

There is also a presentation from 2ndquadrant that shows Inside the PostgreSQL Shared Buffer Cache. It outlines the following points:

Windows does not perform well with large shared buffers
settings. Effective maximum size is around 10,000-50,000
buffers
I This talk is more appropriate for UNIX-like platforms; can’t
really take full advantage of the buffer cache on Windows

Digging deeper into this, I found an open source project that clears the cache. I don't see many people forking or using this project though. The safest way is to just restart the server since there is no way to bypass or flush the database's cache. But feel free to look at the code and see if it will work for you.

You can see all prepared statements available in the session by querying the pg_prepared_statements system view. This will allow you to see if the plan cache was cleared. You might even be able to get away with a DEALLOCATE statement. This might be what you are looking for...


SQL Server Wildcardless LIKE Optimization

Most likely yes, if there are proper indexes in place and statistics are up to date, then the query optimizer should be able to use the existing indexes and best operation when possible (seek).
You will have detailed information in these 2 articles:
Index usage by %Like% operator – Query Tuning on MSDN and
SQL Performance - Indexes and the LIKE clause  on myitforum.com.


I have a third party app that is using a master-master MySQL system and if  your network/communication is not good you may have some issue with your replication.

Types of errors you may encounter is 1062 (duplicate key).

Right now to avoid this error we put it in the slave_skip_errors in themy.cnf file.

Also if your network/communication is not good is not good, your instances may keep and failing back and forth.

It can work but you will need a lot of fine tuning and monitoring.


SQL Server Data Transactional Replication of huge data, locking down table

You may want to look into Publishing Stored Procedure Execution in Transactional Replication which can provide significantly better performance for large batch operations since only the procedure execution is replicated, bypassing the operation being sent to Subscribers as a large, multi-step transaction.

If you have anymore questions please let me know.  I hope this helps.


It looks like the join between ja_feedlog and ja_jobs is the culprit (it appears to be taking most of the time doing a filtered indexed scan on ja_jobs for each ja_feedlog resulting row). In cases like this where there are two joined heavily filtered large tables, I find it useful identifying which set of filter conditions will render the least rows on one of the tables, and separate that/those one/s into an indexed temporary table(s) for joining later.

I have assumed that the table ja_jobs, with those filter conditions, will produce a reasonably small rowset in a reasonably small time. If that is the case, you may want to try the following:

CREATE TEMP TABLE myjobs AS
  SELECT *
  FROM public.ja_jobs
  WHERE NOT j.deleted AND NOT j.templated AND ((((j.time_job <= 1461240000) AND (j.time_arrival >= 1460462400))));

CREATE UNIQUE INDEX ON myjobs (id);

SELECT      j.id,
             concat(c.company, ' ', c.name_first, ' ', c.name_last) AS client,
            c.email AS client_email,
            concat(bc.company,' ', bc.name_first, ' ', bc.name_last) AS bclient,
            j.title,
            j.refnum,
            j.job_number,
            j.time_job,
            j.time_arrival,
            j.address,
            j.suburb,
            j.city,
            j.stpr,
            j.postcode,
            j.priority,
            j.status_label_id,
            j.description,
            u.name_first AS staff_first,
            u.name_last AS staff_last,
            fl.serialised_data,
            fl.gtime,
            j.created_date

FROM public.ja_feedlog AS fl
JOIN myjobs AS j ON (j.id = fl.jobid)
FROM public.ja_status AS s ON (j.status_label_id = s.id)
JOIN public.ja_customers AS c ON (c.id = j.customerid)
LEFT JOIN public.ja_customers AS bc ON (bc.id = j.bill_customer)
LEFT JOIN public.ja_mobiusers AS u ON (u.id = fl.requestorid)

WHERE fl.clientid = 19233 AND fl.log_type IN (5,101,6,102) AND (fl.log_type IN (5,101) OR (fl.description LIKE '%status_change%' AND fl.log_type IN (6,102)))

ORDER BY j.id, gtime
LIMIT 50000

Also, for inner joins, it is clearer if filter conditions that are not strictly part of the joining condition are specified in the where clause rather than the join, for clarity and for making debugging easier. Finally, even if it doesn't affect the final plan, for readability and clarity I place in the FROM whatever table it will be the "carrier" table, i.e. the one that will take the biggest filtering effort, and "hang" from it all the dependent ones, like small master tables, since I find it easier commenting these out for debugging.


SQL Data Collector - SSIS required on source server?

Nope, you don't need SSIS on the server you are collecting data from, bits of SSIS are installed anyway when you install SQL server and this is enough for the data collector to work.  All the system data collector sets work out of the box once you have set up the management data warehouse and enabled them.

The actual SSIS packages for data collector are stored within MSDB, rather than SSISDB as with a full SSIS server and they are executed by a process called Dcexec.exe.

The full architecture for how data collector works is detailed here:

Data Collector Architecture


What is difference between SELECT * FROM table and SELECT * FROM table WHERE 1

Question 1 : What is the difference between these 2 queries, if it loads the same data.

None.

  Question 2 : Why there is these much time difference, if it loads the same data.

Within margin of error and your first query may have put them in a cache making it slightly faster.

  Question 3 : Why phpmyadmin by default loads SELECT * FROM table WHERE 1 query ?

Because it's a bit special. There are many odd behaviors of what phpMyAdmin manipulates queries in a browser to before it gets to SQL. Programmatic convenience maybe.

  Question 4 :What MySQL do for ABOVE queries in MySQL Query Caching?

Query caching is based on the literal text of the query, and some session variables. They would be cached differently.
PS. Query cache is evil.


SSIS connection used based on passed value

You want to incorporate Environment Variables into your SSIS Packages. Setup the agent job to use different environment variables.

You can set the variables at each step in your agent job. If you create multiple SSISDB Environments in your project folder they can be use or store different variables for executing your SSIS Packages.

Reference:
https://www.mssqltips.com/sqlservertip/4810/setup-environment-variables-in-sql-server-integration-services/


Is possible to backup database online using rsync?

When it comes to InnoDB, you are right to be concerned.

Here is a Pictorial Representation of InnoDB (Made by Percona CTO Vadim Tkachenko)

The whole left side of the diagram represents InnoDB's moving parts in memory. The critical piece here is the InnoDB Buffer Pool

The InnoDB Buffer Pool holds three things

Data Pages : If a page is dirty, it is eventually written to its .ibd file
Index Pages : If a page is dirty, it is eventually written to its .ibd file
Secondary Index Changes : Dirty index pages related to non-unique indexes are written to ibdata1

Any page that has been updated is also written beforehand into the Double Write Buffer inside ibdata1.

If you rsync everything in datadir, you are chasing too many moving files

ibdata1 itself has moving parts

Double Write Buffer
Inset buffer
Rollback Segments
Undo Logs

Redo Logs (ib_logfile0 and ib_logfile1)
Every .ibd file being updated

If you had to rsync your datadir to back it up and you attempt to setup it and start mysql, the best case scenario would be crash recovery, an initiated repair of data and index pages using the Double Write Buffer and the Insert Buffer and some playback from the Redo Logs. This will roll forward whatever data can be saved via crash recovery.

It would be fair to say that rsync could possibly miss dirty pages that have not been written the Double Write Buffer and the Insert Buffer if the rsync got passed it already.

I have written posts about doing rsyncs for backup

Aug 19, 2012 : How do I replicate a MySql server's data without using mysqldump?
Jun 17, 2011 : What is the difference between XtraBackup and rsync?
May 23, 2011 : How can I move a database from one server to another?

If you want to try to rsync, you could use the script from How do I replicate a MySql server's data without using mysqldump? but you need to properly flush all moving parts

About 10 minutes before the backup, set buffer pool to have dirty pages flush aggregsively

SET GLOBAL innodb_max_dirty_pages_pct = 0;

Then, just before starting the rsync, run these commands

FLUSH LOGS;
FLUSH TABLES WITH READ LOCK;
SELECT CONNECTION_ID() ID_With_Lock;
SELECT SLEEP(86400);

You could run the rsync at this point, but any new transaction will be held up in the Buffer Pool and will not commit to disk until you login to MySQL in a separate session and kill the process ID (ID_With_Lock).

EPILOGUE

It is possible to have a good rsync backup if handled with great care. Percona XtraDB Cluster has 3 modes of adding new node to a Galera Cluster (xtrabackup, mysqldump, rsync)

If you are not that confident doing rsync backups, you are better off just doing mysqldump --single-transacton and retrieving binlog events to copy data and roll forward to what ever point-in-time you want. You could also resort to Percona XtraBackup and let it do the heavy lifting.


Select to get a value from a id combined with a max value

greatest-n-per-group problems are typically solved with a window function:

select idAction, numberLine, action
from (
  select idAction, numberLine, action,
         row_number() over (partition by idaction order by numberline desc) as rn
  from the_table
) t
where rn = 1;

If you can have more then one maximum value for numberline the above will only return one of them. If you want to see all of them, use dense_rank() instead of row_number()


Why am I seeing `ERROR 1290 (HY000)` when altering my table?

From the 5.6.6 Changelog:

In MySQL, the TIMESTAMP ( http://dev.mysql.com/doc/refman/5.6/en/datetime.html ) data type differs in nonstandard ways from other data types:

TIMESTAMP ( http://dev.mysql.com/doc/refman/5.6/en/datetime.html ) columns not explicitly declared with the NULL attribute are assigned the NOT NULL attribute. (Columns of other data types, if not explicitly declared as NOT NULL, permit NULL values.) Setting such a column to NULL sets it to the current timestamp.

The first TIMESTAMP ( http://dev.mysql.com/doc/refman/5.6/en/datetime.html ) column in a table, if not declared with the NULL attribute or an explicit DEFAULT or ON UPDATE clause, is automatically assigned the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP attributes.

TIMESTAMP ( http://dev.mysql.com/doc/refman/5.6/en/datetime.html ) columns following the first one, if not declared with the NULL attribute or an explicit DEFAULT clause, are automatically assigned DEFAULT '0000-00-00 00:00:00' (the “zero” timestamp). For inserted rows that specify no explicit value for such a column, the column is assigned '0000-00-00 00:00:00' and no warning occurs.

Those nonstandard behaviors remain the default for TIMESTAMP ( http://dev.mysql.com/doc/refman/5.6/en/datetime.html ) but now are deprecated and this warning appears at startup:

[Warning] TIMESTAMP with implicit DEFAULT value is deprecated.
Please use --explicit_defaults_for_timestamp server option (see
documentation for more details).

As indicated by the warning, to turn off the nonstandard behaviors, enable the new explicit_defaults_for_timestamp ( http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp ) system variable at server startup. With this variable enabled, the server handles TIMESTAMP ( http://dev.mysql.com/doc/refman/5.6/en/datetime.html ) as follows instead:

TIMESTAMP ( http://dev.mysql.com/doc/refman/5.6/en/datetime.html ) columns not explicitly declared as NOT NULL permit NULL values. Setting such a column to NULL sets it to NULL, not the current timestamp.

No TIMESTAMP ( http://dev.mysql.com/doc/refman/5.6/en/datetime.html ) column is assigned the DEFAULT CURRENT_TIMESTAMP or ON UPDATE CURRENT_TIMESTAMP attributes automatically. Those attributes must be explicitly specified.

TIMESTAMP ( http://dev.mysql.com/doc/refman/5.6/en/datetime.html ) columns declared as NOT NULL and without an explicit DEFAULT clause are treated as having no default value. For inserted rows that specify no explicit value for such a column, the result depends on the SQL mode. If strict SQL mode is enabled, an error occurs. If strict SQL mode is not enabled, the column is assigned the implicit default of '0000-00-00 00:00:00' and a warning occurs. This is similar to how MySQL treats other temporal types such as DATETIME ( http://dev.mysql.com/doc/refman/5.6/en/datetime.html ).

To upgrade servers used for replication, upgrade the slaves first, then the master. Replication between the master and its slaves should work provided that all use the same value of explicit_defaults_for_timestamp ( http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp ):

Bring down the slaves, upgrade them, configure them with the desired value of explicit_defaults_for_timestamp ( http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp ), and bring them back up.

The slaves will recognize from the format of the binary logs received from the master that the master is older (predates the introduction of explicit_defaults_for_timestamp ( http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp )) and that operations on TIMESTAMP ( http://dev.mysql.com/doc/refman/5.6/en/datetime.html ) columns coming from the master use the old TIMESTAMP ( http://dev.mysql.com/doc/refman/5.6/en/datetime.html ) behavior.

Bring down the master, upgrade it, and configure it with the same explicit_defaults_for_timestamp ( http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp ) value used on the slaves, and bring it back up.

(Bug #63034, Bug #13344629, Bug #55131, Bug #11762529)


new Install and migrate from mysql to mariadb

So, for the after world.

I did it by following my idea and it worked out well.

A few minor things has to be taken into account.

After copying data over from one server to the other.

The *.pem and the auto.cnf files in /var/lib/mysql/ have to be deleted on the new machine.
Deleting auto.cnf is really important because it holds the servers ID and you don't wanna end up running several servers with same server ID, what will cause you problems afterwards.

On a fresh installed machine, before installing mariadb, there isn't a user mysql nor group mysql on the system, so change the /var/lib/mysql and it's sub folders to be owned by root:root.

I copied the entire /etc/mysql folder from source machine to destination machine, to make the mariadb install script believe there was a running mysql installation before on that machine.
In the config file /etc/mysql/mysql.conf.d/mysql.conf I changed the value of the server_id variable to be unique in the entire server landscape.

After that you are ready to fire the mariadb installation on the new machine using apt.
While installing, mariadb install script detects the mysql stuff and tries to run the mysql-upgrade on it. That will probably fail, as in my case, but that's not a big deal.

It is because the install script tries to run mysql-upgrade as root user on your freshly installed mariadb server with old data in place. But there are differences between mysql and mariadb in system tables and in what root on a local machine can do and what not, and system tables needs to be upgraded by the upgrade script.

I never run things as root user on the database, so I have my own user with rights do do everything on each of my databases, and that user credentials come with the data from the old machine. So I run the update script just from the command line using my user credentials instead of the root credentials.

And it turned out to run well, complaining about some missing stuff, but that is what the update script is written for. To detect missing or changed stuff and correct it.
Restart mysql and there we are.
All my users and 230+GB of data right into access managed now by mariadb on a debian 9 machine.

Hope it helps someone.

Ju


Multi master replication with MySQL and Django

Master-Master has potential problems.  If you have AUTO_INCREMENTs, they need to be configured correctly.  If there is a chance of "duplicate key" for other reasons, that will stop replication.

Hence, I recommend not writing to both Masters.  Instead write to only on, keeping the other one as a 'hot backup'.

Consider Galera.


Correct way of configuring remote users to connect to Microsoft SQL Server 2012

Managed service accounts are supported in SQL server 2012 onwards to run it's services.

There is no 'correct' way but managed service accounts on the application server are normal domain users as the SQL Server is concerned so as long as you can use them for the application they are a nice option. You might have to type the name in (domain\appuser$) as older versions of SSMS don't know the object type but you can add them manually

To minimize the configuration changes you can do the following:

Use client aliases on your application servers in the connection strings, for each application create an alias for the database (use cliconfg.exe) so that AppA connects to the the database server AppAdb. The alias on test will then point to the testdb server, prod to prod. Then you dont need to change the hostname in the connection string and you dont need to worry about the production application server suddenly starting to talk to the test database.
Use contained databases to minimize the need to create users on the database servers, you can then migrate databases between servers without having to remembering to add the users.
Use integrated App Pool Identity's and windows authentication using domain users, managed service accounts when possible, then you are not storing password in .config files, gMSA will allow you to have the same user on different servers.
If you have to use SQL Server authentication encrypt the passwords.


Concatenation Physical Operation: Does it guarantee order of execution?

No, there is no documentation from Microsoft guaranteeing the behavior, therefore it is not guaranteed.

Additionally, assuming that the Simple Talk article is correct, and that the Concatenation physical operator always processes inputs in the order shown in the plan (very likely to be true), then without a guarantee that SQL Server will always generate plans that keep the same the order between the query text and the query plan, you're only slightly better off.

We can investigate this further though.  If the query optimizer was able to reorder the Concatenation operator input, there should exist rows in the undocumented DMV, sys.dm_exec_query_transformation_stats corresponding to that optimization.

SELECT * FROM sys.dm_exec_query_transformation_stats
    WHERE name LIKE '%CON%' OR name LIKE '%UNIA%'

On SQL Server 2012 Enterprise Edition, this produces 24 rows. Ignoring the false matches for transformations related to constants, there is one transformation related to the Concatenation Physical Operator UNIAtoCON (Union All to Concatenation).  So, at the physical operator level, it appears that once a concatenation operator is selected, it will be processed in the order of the logical Union All operator it was derived from.

In fact that is not quite true. Post-optimization rewrites exist that can reorder the inputs to a physical Concatenation operator after cost-based optimization has completed. One example occurs when the Concatenation is subject to a row goal (so it may be important to read from the cheaper input first). See UNION ALL Optimization by Paul White for more details.

That late physical rewrite was functional up to and including SQL Server 2008 R2, but a regression meant it no longer applied to SQL Server 2012 and later. A fix has been issued that reinstates this rewrite for SQL Server 2014 and later (not 2012) with query optimizer hotfixes enabled (e.g. trace flag 4199).

But about the Logical Union All operator (UNIA)?  There is a UNIAReorderInputs transformation, which can reorder the inputs.  There are also two physical operators that can be used to implement a logical Union All, UNIAtoCON and UNIAtoMERGE (Union All to Merge Union).

Therefore it appears that the query optimizer can reorder the inputs for a UNION ALL; however, it doesn't appear to be a common transformation (zero uses of UNIAReorderInputs on the SQL Servers I have readily accessible. We don't know the circumstances that would make the optimizer use UNIAReorderInputs; though it is certainly used when a plan guide or use plan hint is used to force a plan generated using the row goal physical reordered inputs mentioned above.

  Is there a way to have the engine process more than one input at a time?

The Concatenation physical operator can exist within a parallel section of a plan.  With some difficulty, I was able to produce a plan with parallel concatenations using the following query:

SELECT userid, regdate  FROM (  --Users table is around 3mil rows
    SELECT  userid, RegDate FROM users WHERE userid > 1000000
    UNION
    SELECT  userid, RegDate FROM users WHERE userid < 1000000
    UNION all
    SELECT userid, RegDate FROM users WHERE userid < 2000000
    ) d ORDER BY RegDate OPTION (RECOMPILE)

So, in the strictest sense, the physical Concatenation operator does seem to always process inputs in a consistent fashion (top one first, bottom second); however, the optimizer could switch the order of the inputs before choosing the physical operator, or use a Merge union instead of a Concatenation.


Is a trade-in its own purchase order, or an adjustment on a sales order?

I would recommend modeling it as a sale with an adjustment for the trade-in, assuming that as a car dealer you accept trade-ins without a related sale of a car.

Therefore within your system you re-use the purchase features, and can later report on metrics like how many sales are linked to trade-ins, what values of sales are linked to trade-ins, whether trade-ins boost sales, etc


MongoDB page faults even when all data is in resident in RAM

The page faults metric for MongoDB on Windows essentially contains hard (actually hitting disk) and soft (reallocating a pointer in memory) page faults.  If you run the same experiment on Linux, the page fault metric only reports hard page faults and you will see the behavior you expect.

This is a known issue with the Windows version and the relevant issue for tracking/voting is SERVER-5799


MERGE a UNION query results down to one row per KEY value

It seems you just need to use a derived table or common table expression with a new grouping level:

DECLARE
    @FROM datetime = CONVERT(datetime, '12-30-2013 03:00:00', 101),
    @TO datetime = CONVERT(datetime, '12-28-2014 03:00:00', 101);

WITH Combined AS
(
    SELECT
        DivisionName = d.DivisionName,
        [CC w/o Tips] = (SUM(PaymentAmount) - SUM(TipAmount)),
        [Tips] = SUM(TipAmount),
        [Receipts] = SUM(PaymentAmount)
    FROM LFLive.POS.OrderPayments AS op
    JOIN LFLive.POS.Orders AS o
        ON o.OrderId=op.OrderId
    JOIN LFLive.POS.Payments AS p
        ON p.PaymentId=op.PaymentId
    JOIN LFLive.Directory.Divisions AS d
        ON d.DivisionId=o.DivisionId
    WHERE
        op.IsVoided=0
        AND op.IsDeleted=0
        AND o.IsVoided=0
        AND o.IsClosed=1
        AND o.OrderTypeId = 2
        AND p.PaymentTypeId = 6
        AND o.DateClosed BETWEEN @FROM AND @TO
    GROUP BY
        d.DivisionName

    UNION ALL -- Not UNION!

    SELECT
        DivisionName = d.DivisionName,
        [CC w/o Tips] = (SUM(PaymentAmount) - SUM(TipAmount)),
        [Tips] = SUM(TipAmount),
        [Receipts] = SUM(PaymentAmount)
    FROM LFArchive.POS.OrderPayments AS op
    JOIN LFArchive.POS.Orders AS o
        ON o.OrderId=op.OrderId
    JOIN LFLive.POS.Payments AS p
        ON p.PaymentId=op.PaymentId
    JOIN LFLive.Directory.Divisions AS d
        ON d.DivisionId=o.DivisionId
    WHERE
        op.IsVoided=0
        AND op.IsDeleted=0
        AND o.IsVoided=0
        AND o.IsClosed=1
        AND o.OrderTypeId = 2
        AND p.PaymentTypeId = 6
        AND o.DateClosed BETWEEN @FROM AND @TO
    GROUP BY
        d.DivisionName
)
SELECT
    C.DivisionName,
    [CC w/o Tips] = SUM(C.[CC w/o Tips]),
    [Tips] = SUM(C.Tips),
    [Receipts] = SUM(C.Receipts)
FROM Combined AS C
GROUP BY
    C.DivisionName
ORDER BY
    C.DivisionName;

I replaced the UNION with UNION ALL because you don't want duplicate removal.


Securely Send Database Mail via an Insert Trigger

You are far better off simply inserting into a mail queue table in the trigger and having a separate email process handling the mail - either in SQL Server Agent or an external program.

This gives you the benefit of using the trigger as you desire, relatively low latency as the other job can see the mail once it's committed, scalability of managing the mail load independently etc.

You can wrap the mail queueing into the transaction properly.  Then the queueing app is reliable if the mail server is temporarily unavailable or if you have email address issues.  If mail needs to be resent, it's simply a matter of resetting the sent flag.  You can make expiration on mail, so that mail which couldn't be sent to the SMTP won't be retried after a certain time.  You can quickly route all mail to a test mailbox.  You can do quite interesting things with attachments and other things based on data in the queue without tying up the trigger for extended periods - things like generating reports etc.

So the app offloads the mail responsibility to another component and is free to complete the database transaction and return success to the users, while the mail subsystem gets on with its work ensuring all you want to do with the mail gets done.


You need to pick one of the string aggregation techniques that works with your version and supports ordering. For example, I've adapted the "ROW_NUMBER() and SYS_CONNECT_BY_PATH functions in Oracle 9i" technique from the excellent ORACLE-BASE website:

WITH a as ( SELECT t3_field1,
                   t3_field2,
                   LTRIM(MAX(SYS_CONNECT_BY_PATH(field3,', '))
                       KEEP (DENSE_RANK LAST ORDER BY curr),', ') AS agg
            FROM   (SELECT field1 t3_field1,
                           field2 t3_field2,
                           field3,
                           ROW_NUMBER()
                             OVER (PARTITION BY field1, field2 ORDER BY field3)
                             AS curr,
                           ROW_NUMBER()
                             OVER (PARTITION BY field1, field2 ORDER BY field3) -1
                             AS prev
                    FROM   Table3)
            GROUP BY t3_field1, t3_field2
            CONNECT BY prev = PRIOR curr
                 AND t3_field1 = PRIOR t3_field1
                 AND t3_field2 = PRIOR t3_field2
            START WITH curr = 1 ),
     s as ( SELECT t3.field1 t3_field1,
                   t3.field2 t3_field2,
                   SUM(t2.field1) sum_t2_field1,
                   SUM(t2.field2) sum_t2_field2
            FROM Table1 t1, Table2 t2, Table3 t3
            WHERE t1.id = t2.idoft1 AND t2.id = t3.idoft2 AND t1.id = 3
            GROUP BY t3.field1, t3.field2 )
SELECT * FROM a JOIN s USING (t3_field1,t3_field2);

Results:

| T3_FIELD1 | T3_FIELD2 |                                             AGG | SUM_T2_FIELD1 | SUM_T2_FIELD2 |
|-----------|-----------|-------------------------------------------------|---------------|---------------|
|      asdf |      test | and_one_more_text, some_another_text, text_here |          1998 |           210 |
|     asdf2 |      test |                           text_in_another_group |           666 |            70 |

SQLFiddle here


Any reason to have both an index with included columns and one without?

To expand a little on what everyone's favorite n-dimensional analogue commented, there may be some benefit to having both.

Here's a small (but not inadequate) demo.

The necessary:

USE StackOverflow;
SET NOCOUNT ON;

CREATE TABLE dbo.IndexCrap
(
    Id INT IDENTITY PRIMARY KEY CLUSTERED,
    Whatever DATETIME,
    Nonsense VARCHAR(50),
    Etc UNIQUEIDENTIFIER,
    SoForth BIT
);

INSERT dbo.IndexCrap WITH (TABLOCK)
        (Whatever, Nonsense, Etc, SoForth )
SELECT TOP 1000 DATEADD(DAY, x.n, GETDATE()),
                REPLICATE('A', x.n % 50),
                NEWID(),
                CASE WHEN x.n % 15 = 0 THEN 1 ELSE 0 END
FROM (SELECT ROW_NUMBER() OVER (ORDER BY @@ROWCOUNT) AS n
        FROM sys.messages AS m ) AS x;

The indexes:

CREATE INDEX ix_tinydancer ON dbo.IndexCrap (Whatever);

CREATE INDEX ix_largemarge ON dbo.IndexCrap (Whatever) INCLUDE (Nonsense, Etc, SoForth);

So like your question, on index on one column, one index on the same column with some includes.

The queries:

SELECT COUNT(*)
FROM dbo.IndexCrap AS ic;

SELECT ic.Nonsense, ic.Etc, ic.SoForth
FROM dbo.IndexCrap AS ic
WHERE ic.Whatever >= DATEADD(DAY, 500, GETDATE());

The execution plans:

For the COUNT(*) query, the optimizer chooses our smallest single column index over the wider nonclustered index and the slightly wider clustered index, since it's not filtered, and all we need is a row count.

For the wider query, the optimzer chooses the covering index so it doesn't have to a) scan the clustered index or b) access the narrower index and do Key Lookups back to the clustered index.

Of course, you may not know every single query that runs on your system. That's why the company I work for writes sp_BlitzIndex, a free tool to analyze your indexes.

If we run EXEC master.dbo.sp_BlitzIndex @DatabaseName = 'StackOverflow', @Mode = 4;, here are some results we're interested in:

The two indexes I added to IndexCrap (not counting the PK/CX) get flagged as having duplicate key columns. It also gives their definitions, usage, size, and a whole lot more not shown in the screencap.

The usage counters aren't perfect. They'll reset if you add or drop indexes, in some versions of SQL Server they'll reset if you rebuild indexes, and of course they reset if you restart SQL Server.

Hope this helps!


You could move the subquery into the main query and add where cht.issueid = i.issueid. Optionally you could add distinct and an order by depending on how the data are related.

SELECT
    i.IssueID AS IssueID,
    i.PAndCAuditDate AS AuditDate,
    i.PAndCValidationCompletedOn AS ValidationCompletedOnDate,
    i.CustomerIssueNumber AS OldId,
    i.PAndCValidatedBy AS ValidatedBy,
    i.PAndCValidatedComments AS ValidatedComments,
    i.Title AS Title,
    i.OpenDate AS IssueOpenDate,
    i.PAndCAuditors AS Auditors,
    i.PAndCAuditees AS Auditees,
    i.Description AS Description,
    i.PAndCRootCause AS RootCause,
    i.PAndCContainmentAndCorrectiveActions AS ContainmentAndCorrectiveActions,
    i.PAndCPreventiveActions AS PreventiveActions,
    DATEDIFF(d, i.TargetDate, GetDate()) AS DaysOverdue,
    i.TargetDate AS IssueTargetDate,
    i.ClosedDate AS IssueClosedDate,
    CASE WHEN i.ClosedDate IS NULL
      AND DATEADD(d, DATEDIFF(d, 0, i.TargetDate), 0)
        < DATEADD(d, DATEDIFF(d, 0, GetDate()), 0)
        THEN 1 ELSE 0 END AS IsOverdue,
    --s1.CompanyHasType
    --s1.Name
    , STUFF((
    SELECT distinct ', ' + ct.Name + ' - ' + ct.ShippingAddressCity
        + ', ' + ct.ShippingAddressRegion + ' ' + ct.CountryID
    FROM Company ct
     INNER JOIN PAndCIssueLocation cht ON cht.CompanyID = ct.CompanyID
     --INNER JOIN Issue c ON c.IssueID = cht.IssueID
     where cht.IssueId = i.IssueId
     --order by ct.name
         FOR XML PATH(''), TYPE).value('.[1]',
       'nvarchar(max)'), 1, 2, '') AS CompanyHasType
FROM
    Issue i
/*LEFT JOIN
(
SELECT c.IssueID, STUFF((
    SELECT ', ' + ct.Name + ' - ' + ct.ShippingAddressCity + ', '
        + ct.ShippingAddressRegion + ' ' + ct.CountryID FROM Company ct
     INNER JOIN PAndCIssueLocation cht
     ON cht.CompanyID = ct.CompanyID INNER JOIN Issue c
     ON c.IssueID = cht.IssueID
         FOR XML PATH(''), TYPE).value('.[1]',
       'nvarchar(max)'), 1, 2, '') AS CompanyHasType
FROM dbo.Issue AS c
GROUP BY c.IssueID
) s1 ON
s1.IssueID = i.IssueID
*/


SMSS 2014 Network Server browser is empty

Network server discovery via SQL Browser Service is done via UDP broadcast. Broadcasts are subject to local network segment rules (as in network mask, default gateway etc), so it may be that your server is on a different segment and the broadcast does not reach the desired servers. If that is not the case then the obvious culprit would be Firewall rules.


Can Oracle 11g Express Edition Work on Parallels

Better solution would be to install docker or virtualbox and run the OracleXe image within one of those containers. Once docker is running you can easily launch a new OracleXe container with two commands. Already configured to allow easy access via SQLDeveloper. I tested my own steps by downloading the docker toolbox, installing and running the OracleXe image. Everything started up without an issue on my desktop. If all possible I would run this on a droplet at DigitalOcean since Oracle is a hog on space 2.3G!

OracleXe container can be found here: OracleXe Docker Container
Docker Install on Mac: Docker Install


Broken SELECT in Sql Server Compact 3.5 SP2?

I can only guess that the problem is related to some sort of index
  corruption - maybe it gets out of sync with the actual data store.

You could isolate index issues by just dropping the index. That will slow down some or all of the queries that access that table, but if the problem goes away, you know for certain that the index was the source of the problem.

But even if the index is the source of your problem, it's not the root cause of your problem. To my way of thinking, the root cause is quite likely to be in Microsoft's code. An index that doesn't match the data is one of those things the database shouldn't allow. And if it happens, you should see big, waving red flags.

You can verify and and try to repair a SQL Server CE database in code.  I'm not sure whether this exercises the same code you'd use by trying to repair through SSMS. (I'd hope so, but I know that hope doesn't scale well.)

How to: Verify and Repair a SQL Server Compact Edition Database (Programmatically)

  I think it might have to do with modifying the db from different
  threads (at some points we may have 2 threads working on the db), but
  I thought this shouldn't be an issue.

Documentation for .NET 3 SqlCEConnection class.

  Any public static (Shared in Visual Basic) members of this type are
  thread safe. Any instance members are not guaranteed to be thread
  safe.

I think you're safe if each thread uses a separate connection. I could be wrong.


Can postgresql WAL be regenerated or resent to the standby server?

except it does not attempt to resend the files it missed and so the standby server gets "stuck" waiting for file x

Your archive_command is buggy.

A correct archive command will never return zero (success) until the WAL of interest is safely archived. If the command returns non-zero (failure) then PostgreSQL will retry archival that WAL segment until it succeeds and will not discard the WAL segment until it's archived.

Either your archive_command is returning true before the archive is truly safely stored, or there's something wrong with the restore_command that's causing it to lose/remove WAL segments.

What you describe should not happen in normal, correct operation.

Now, if somehow you do land up with a gap of WAL segments between the standby's current position and the oldest available WAL segment, the only thing you can do is re-initialise the standby. The simplest and safest way to do this is with pg_basebackup -X stream. If you want to get more sophisticated and save some time/bandwidth you can instead use rsync to copy just changed parts of files:

Stop the standby
pg_start_backup() on the master
rsync -a --delete the master data dir to the standby data dir
pg_stop_backup() on the master
wait for the final WAL segments to archive
start the standby


If it is MySQL, you can do this with a simple join:

UPDATE music AS m
  JOIN albumList AS a
    ON m.songalbum = a.album
SET m.albumID = a.albumID
WHERE m.albumID IS NULL ;

You could use a correlated subquery for this (similar to your 2nd attempt) but I wouldn't suggest it for MySQL. Less efficient and more complicated as it needs an additional condition in the WHERE clause to protect from updating music.albumID with null, for rows that have no related album:

UPDATE music AS m
SET albumID = (SELECT albumID FROM albumList AS a WHERE m.songalbum = a.album)
WHERE albumID IS NULL
  AND EXISTS (SELECT 1 FROM albumList AS a WHERE m.songalbum = a.album) ;


Your query can be simplified (regardless of SQL flavor) not to use an inner query (subselect). This is not tested, but it should be closer to what you need:

SELECT tPLAN.Jahr, tPLAN.Monat, tPLAN.Land_ID, tPLAN.Kanal, T1.tPLAN, tPLAN.Planstand,
    tZ.Zeitraum, T2.IST_ITEMS_PER_HC_EXCL_EASY
FROM t_Plan_Items_per_HC tPLAN
    JOIN t_PLAN_ZEITRAUM tZ ON tZ.Jahr = tPLAN.Jahr AND tZ.Monat = tPLAN.Monat
    JOIN a_ITEMS_PER_CALL_ACT_MONTH_KANAL_EXCL_EASY_HC AS T2
        ON (T2.Zeitraum=tZ.Zeitraum AND T2.Land_ID=tPLAN.LAND_ID AND T2.Kanal=tPLAN.KANAL)

Since all JOINs are INNER, you can just select from one of the tables and JOIN with each of the others using your criteria. Also, WHERE conditions like:

FROM t_Plan_Items_per_HC tPLAN, t_PLAN_ZEITRAUM tZ
    WHERE tPLAN.Jahr = tZ.Jahr
    AND tPLAN.Monat = tZ.Monat)

are not recommended. Use JOINs instead.


Is log shipping good for periodic synchronization?

It is a creative way of thinking. If you know how to handle role exchanges (promoting to primary) and do this often, it could work but I am not sure it makes you happy. Especially not because there is a bug where WALL files are prematurely recycled which makes re establishing the old primary as a slave a pain.
(this is fixed in May 2020)
Since you want to be able to work on both(I assume read and write) you have a challenge. You can only have one writable db in a replication pair.
My choice - as a dba who does this kind f things regularly - would be to set up a little RDS instance in Amazon or similar so you can access the database always, if you have a network available.
It would be different if you only need writes to one db and the others only has reads. In that case: go ahead.


Just normalize it.

crawl-
crawl_id|crawltime
1       |2018-01-02 2:47 PM

terms-
crawl_id | term | count
1        | Joe  | 15


Your table structure is defined by the entity model it represents, not by opinions.

If components need to be represented individually, e.g. if they have unique serial numbers, different prices, or arrive as separate shipments, this would warrant separate records for each. If components are indistinguishable from each other (cf. nails, bricks, or gallons of paint) there is no reason nor method to track each one separately.


In general you should have full backups set on one schedule, and transaction log backups set on another (more frequent) schedule, so yes do both of these as soon as you can.

The issue you will run into is if another process is performing backups without "COPYONLY", as this will through off sequencing (making a full point in time restore impossible unless you can track down the location of the mystery backups).

The code snippet below will search the default trace for all BACKUP activity and should allow you to track things down.

SET NOCOUNT ON;
--this version reads all trace files
declare @path  nvarchar(100)
set @path = (select top 1 [path] from sys.traces where is_default = 1)
set @path = (select reverse(right(reverse(@path), (Len(@path) - (PATIndex('%[_]%', reverse(@path)))))) + '.trc')

SELECT  STE.name AS EventClassName,
       ST.StartTime ,
        ST.LoginName ,
        ST.HostName ,
        ST.ApplicationName ,
        ST.TextData
FROM    sys.fn_trace_gettable((@path), DEFAULT) ST
INNER JOIN sys.trace_events STE ON ST.EventClass = STE.trace_event_id
WHERE   TextData LIKE '%backup%' AND SPID <> @@SPID
ORDER BY StartTime DESC;


Register the time a machine running in a timerange using sql

The following code will spray your one row into 2 as desired.

Declare @starttime time
Declare @endtime time
Declare @machinename varchar(10)
Set @machinename = 'vxx:2019'
If OBJECT_ID ('tempdb..#A') is not null Drop Table #A
Create Table #A (machinename varchar(15), startdatetime datetime, enddatetime datetime, timerunning int, timerange varchar(10))
Insert into #A (machinename, startdatetime, enddatetime, timerunning, timerange)
Select 'vxx:2019',    '2020-02-07 13:45:18.790' ,   '2020-02-07 16:00:38.920' , 135, '12:00'

Select @starttime = cast(startdatetime as time)
from #A
where machinename = @machinename

Select @endtime = cast(enddatetime as time)
from #A
where machinename = @machinename

If (@starttime  between '12:00' and '14:00') and (@endtime > '14:00')

Begin
Select machinename, startdatetime, enddatetime, datediff(minute,@starttime,'14:00') as timerunning, '12:00' as timerange
from #A
UNION ALL
Select machinename, startdatetime, enddatetime, datediff(minute,'14:00',@endtime), '2:00' as timerange
From #A
End

The two time ranges specified in the question are 12-2 and 2 - end time. It uses variables to determine whether the job runs across the time range or not. If the job runs across the time range then it just uses the datediff function to determine the time running in those timeranges and sprays it out into two rows. If you need to do this on a whole table a while loop is probably your best bet.


In general, surrogate PKs are preferable compared to natural. Usually, primary key columns never get updated; in your case renaming file will require updating primary key which in turn may cause cascade updates. Aside from that, having a long primary key is never a good idea from storage and performance point of view.

Depends on RDMS you use, primary key may also define how the rows stored (the physical order), so even inserting will cause many more page splits and high level of fragmentation.

Summary .I'd strongly recommend not to use file names as primary keys. If they have to be unique, you always have an option to create unique constraint.