How should I use RMAN clone refresh using set until time?

First you need to ensure that that the backup is in your library (surely you know better ways for find it, I put my query):

select
    D.DB_NAME, B.DB_ID, BASET.BCK_TYPE,
decode(BASET.CONTROLFILE_INCLUDED,'BACKUP','YES','NO') as CONTROLFILE_INCLUDED,
BASET.INCR_LEVEL, bapiece.tag, bapiece.handle, bapiece.start_time,
bapiece.completion_time,
round(bapiece.bytes/1024/1024,2) as Mb
from rman.bp bapiece,
        rman.dbinc d,
        rman.bs baset,
        rman.db b
where BAPIECE.DB_KEY=D.DB_KEY
   and D.DB_NAME='SFPROD'
   and BASET.DB_KEY=D.DB_KEY
   and BASET.BS_KEY = BAPIECE.BS_KEY
   and  bapiece.start_time>sysdate-1
   and B.DB_KEY=D.DB_KEY
---   and bapiece.handle like '%.dbf%'
order by tag desc, handle desc    ;

And... I hate to use "set until time" to restore/duplicate database. It is more human friendly but you can be surprised with an rman error at the end of the restore. Try to find the last commit (SCN) for duplicate process:

SQL> select max(next_change#)
    from v$archived_log
    where archived = 'YES'
    group by thread#;

or with rman:

RMAN> list backup of archivelog all;
--Next SCN

And then:

run { set until scn ... ; allocate auxiliary channel t1 type 'sbt_tape'; duplicate target database to DEVDB; }


Delete every row that is not the first duplicate

This removes all rows apart from the first in each group ordered by resh id:
DELETE FROM org_unit o
WHERE o.id NOT IN
  (SELECT MIN(ID) AS id
   FROM  org_unit
   GROUP BY resh_id
   ORDER BY resh_id);

If you wanted the last two or three, you would use the window function ROW_NUMBER and select the wanted number

CREATE TABLE "org_units" (
  "ID" INTEGER,
  "RESH_ID" VARCHAR(1)
);

INSERT INTO "org_units"
  ("ID", "RESH_ID")
VALUES
  ('1', 'a'),
  ('2', 'b'),
  ('3', 'a'),
  ('4', 'a'),
  ('5', 'b'),
  ('6', 'c');

  DELETE FROM "org_units" o
where o."ID" in (SELECT MIN("ID")    FROM  "org_units" GROUP BY "RESH_ID" )

3 rows affected

SELECT * FROM org_units

ID | RESH_ID
-: | :------
 3 | a
 4 | a
 5 | b

CREATE TABLE org_units2 (
  "ID" INTEGER,
  "RESH_ID" VARCHAR(1)
);

INSERT INTO org_units2
  ("ID", "RESH_ID")
VALUES
  ('1', 'a'),
  ('2', 'b'),
  ('3', 'a'),
  ('4', 'a'),
  ('5', 'b'),
  ('6', 'c');

DELETE FROM "org_units2" o
where o."ID" NOT in (SELECT MIN("ID")    FROM  "org_units2" GROUP BY "RESH_ID" )

3 rows affected

SELECT * FROM org_units2

ID | RESH_ID
-: | :------
 1 | a
 2 | b
 6 | c

db<>fiddle here


During restore restart, an I/O error occurred on checkpoint file

Anyone have any ideas? Safe to ignore? Potential time bomb? I'm
  stumped.

It's not a "bomb", this means only that if you want to use RESTORE with RESTART option (to complete interrupted RESTORE ) you'll not be able to do it, i.e. checkpoint file cannot be used and the RESTORE will not be able to skip some passes if they woudl be done prior to interruption.

You can read more about checkpoint file here: Restores using Invalid Backup Default Locations

The cause of this error is that your default backup location becomes temporarily inaccessible.

You can just ignore this error or you can change the default backup location with any local path.


Automating an SQL task on a monthly basis

What you are telling is possible.

A pseudo code would look like this :

[ Make sure to use database compression and have instant file initialization enabled.]

Check if it is first day of the month using select DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)

if it is first day of month, backup the previous month database to archive as db_name_datetime_Month_archive.bak.
backup the live PROD database and copy the file to DEV server and restore / overwrite the current database - use WITH REPLACE option when restoring.
Sync up users
Optionally since you are restoring database from PROD, you may have to sanitize it - remove PII or any sensitive information.
send out email to the concerned teams (DEV in your case) saying the restore or refresh job has been completed.

For backup and restore you can use PowerShell (make sure you use backup and restore method) or tsql.

You can completely automate the above pseudo code using sql agent job.


Avoid unique violation in atomic transaction

In addition to what @Craig provided (and correcting some of it):

Effective Postgres 9.4, UNIQUE, PRIMARY KEY and EXCLUDE constraints are checked immediately after each row when defined NOT DEFERRABLE. This is different from other kinds of NOT DEFERRABLE constraints (currently only REFERENCES (foreign key)) which are checked after each statement. We worked all of this out under this related question on SO:

Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?

It is not enough for a UNIQUE (or PRIMARY KEY or EXCLUDE) constraint to be DEFERRABLE to make your presented code with multiple statements work.

And you can not use ALTER TABLE ... ALTER CONSTRAINT for this purpose. Per documentation:

  ALTER CONSTRAINT

  This form alters the attributes of a constraint that was previously
  created. Currently only foreign key constraints may be altered.

Bold emphasis mine. Use instead:

ALTER TABLE t
   DROP CONSTRAINT category_name_key
 , ADD  CONSTRAINT category_name_key UNIQUE(name) DEFERRABLE;

Drop and add the constraint back in a single statement so there is no time window for anybody to sneak in offending rows. For big tables it would be tempting to conserve the underlying unique index somehow, because it is costly to delete and recreate it. Alas, that does not seem to be possible with standard tools (if you have a solution for that, please let us know!):

Drop primary key without dropping an index

For a single statement making the constraint deferrable is enough:

UPDATE category c
SET    name = c_old.name
FROM   category c_old
WHERE  c.id     IN (1,2)
AND    c_old.id IN (1,2)
AND    c.id <> c_old.id;

A query with CTEs also is a single statement:

WITH x AS (
    UPDATE category SET name = 'phones' WHERE id = 1
    )
UPDATE category SET name = 'tablets' WHERE id = 2;

However, for your code with multiple statements you (additionally) need to actually defer the constraint - or define it as INITIALLY DEFERRED Either is typically more expensive than the above. But it may not be easily feasible to pack everything into one statement.

BEGIN;
SET CONSTRAINTS category_name_key DEFERRED;
UPDATE category SET name = 'phones'  WHERE id = 1;
UPDATE category SET name = 'tablets' WHERE id = 2;
COMMIT;

Be aware of a limitation in connection with FOREIGN KEY constraints, though. Per documentation:

  The referenced columns must be the columns of a non-deferrable unique
  or primary key constraint in the referenced table.

So you cannot have both at the same time.


Most efficient way to identify records with any number of key columns

One common approach is to choose a hash function with a very, very small chance of collision and then assume there won't be any:

CREATE TABLE Staging.myTest (
    [ID] INT IDENTITY(1,1) NOT NULL,
    [Hash] AS
        CONVERT(binary(32),
            HASHBYTES('SHA2_256',
                CONCAT(Dim_1, N'|', Dim_2, N'|', Dim_3))),
    [Dim_1] NVARCHAR(32) NOT NULL,
    [Dim_2] NVARCHAR(32) NOT NULL,
    [Dim_3] NVARCHAR(32) NULL,
    [Met_1] INT NULL,
    [Met_2] DECIMAL(5,2) NULL
);
GO
CREATE TABLE IdMatch.myTest (
    [ID] INT IDENTITY(1,1) NOT NULL,
    [Hash] AS
        CONVERT(binary(32),
            HASHBYTES('SHA2_256',
                CONCAT(Dim_1, N'|', Dim_2, N'|', Dim_3))),
    [Dim_1] NVARCHAR(32) NOT NULL,
    [Dim_2] NVARCHAR(32) NOT NULL,
    [Dim_3] NVARCHAR(32) NULL,
);
GO
-- Declared unique because we have decided it will be
CREATE UNIQUE NONCLUSTERED INDEX
    IX_HASH
ON IdMatch.myTest
    ([Hash]);

Note: Most people replace column NULLs with empty string for hashing, which is the default behaviour of CONCAT. If you need to differentiate NULL from empty string, you would need to identify some other magic value to use, and wrap the nullable column with ISNULL or COALESCE.

Adding rows that don't match is then:

INSERT Staging.myTest
(
    Dim_1,
    Dim_2,
    Dim_3
)
SELECT
    SRC.Dim_1,
    SRC.Dim_2,
    SRC.Dim_3
FROM Staging.myTest AS SRC
WHERE
    NOT EXISTS
    (
        SELECT 1
        FROM IdMatch.myTest AS TGT
        WHERE
            TGT.[Hash] = SRC.[Hash]
    );

See Finding rows that have changed in T-SQL – CHECKSUM, BINARY_CHECKSUM, HASHBYTES by Greg Low. You should test this approach with your data to see if this scheme will work for you.


MySQL 5.7 Can slave be ahead of master during replication

On one occasion, I helped a company that had a replica ahead of its master. It was a very odd situation. It isn't supposed to be possible.

What caused it was that their master server's hard drives were failing, and data was not able to be written to disk. So the data committed to the InnoDB redo log and tablespace wasn't being saved.

But the transactions were committed as far as MySQL knew, and it was writing to the binary logs, which used buffered I/O, so the operating system still had a portion of the file in RAM, trying to sync to disk.

Then the replica requested the latest binary log events, and the master sent them, and the replica faithfully applied them to its database. Since the replica's disk was not having faults, it saved to disk fine.

The master server finally rebooted after many disk I/O faults. It came back up and was able to start processes. MySQL read its InnoDB redo log file, trying to do crash recovery, but the latest transactions had never been written to the disk, so they were not able to be recovered.

But the replica had gotten those events!

Thus the replica was slightly ahead of its master.


Optimizing single-row DELETEs from a table with many FOREIGN KEY references

the situation would greatly improve if I added supporting indexes for all the FK (currently, only 20 of the 60 tables have it)

You should always index fields used as keys, foreign or otherwise.
This gives the database far more options when analysing how to execute the query and adding them may actually increase the overall performance of your application.

  many of these FK are for columns like "modified by", so all the indexes would have no other purpose

As with [pretty much] everything on Planet Database, it's a compromise.
Here, you're balancing data integrity - knowing with certainty who changed something - against speed - not having those extra indexes that slow down inserts.
Does it really matter if you don't know (with certainty) the user that changed these records?  If not, you're OK as you are.  If it does matter - and in lots of cases, it really, really does - then you're stuck with having the overhead of the indexes.

  Before running the DELETE, I already make sure that all the referencing records have been deleted ...

With the FK's in place, you would not be able to delete them anyway, so the values of this activity is dubious.

The question really should be, why is the delete taking an hour?
If you're only deleting one record out of a million row table and there are no rows in the tables with FK's back to this table, then what's taking the time?  It should be practically instantaneous.

Slow queries, even deletes, are often caused by missing indexes - are you performing the delete based on the record's Primary Key field?  Or some other combination of [not-indexed] fields?


what does SHOW /*!50000 GLOBAL */ STATUS do ?

The "50000" refers to a MySQL version -- it means that only version 5.0.0 and above should pay attention to that command.  5.0.0+ will see SHOW GLOBAL STATUS, while earlier versions will see just SHOW STATUS.

Example:

mysql> /*!50511 select 1 */;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql> /*!50512 select 1 */;
Query OK, 0 rows affected (0.00 sec)

(I have MySQL 5.5.11 -- i.e. 5.05.11).


Mysql query for get repeated customers list

SELECT c.*
FROM oc_customer c
JOIN oc_order o ON c.cus_id = o.cus_id
GROUP BY c.cus_id
HAVING COUNT(o.order_id) > 1


Where does kerberos tool need running and settings configured?

You want to have SPNs in place for both servers. And you'll need the delegation settings in place for the first server (Server A in the linked article). Specifically, for the service account that runs the SQL Engine service on Server A.
Your title asks about where the tool needs to be run from. The Kerberos Configuration Manager can be run from anywhere, but the changes it will help you make are to Active Directory (AD) objects, so you'll need to run it under an account that has enough rights to make the AD changes. (Typically, you'd use a domain administrator account for this.)


you can make use of the so called ctid (which is basically an internal thing but for a lousy hack it should do. here is an example:
'

test=# CREATE TABLE x (id int);
CREATE TABLE
test=# INSERT INTO x SELECT * FROM generate_series(1, 4);
INSERT 0 4
test=# INSERT INTO x SELECT * FROM generate_series(1, 4);
INSERT 0 4
test=# SELECT ctid, * FROM x;
 ctid  | id
-------+----
 (0,1) |  1
 (0,2) |  2
 (0,3) |  3
 (0,4) |  4
 (0,5) |  1
 (0,6) |  2
 (0,7) |  3
 (0,8) |  4
(8 rows)

test=# SELECT id, min(ctid) FROM x GROUP BY 1;
 id |  min
----+-------
  4 | (0,4)
  1 | (0,1)
  3 | (0,3)
  2 | (0,2)
(4 rows)

test=# DELETE FROM x WHERE ctid NOT IN (SELECT min(ctid) FROM x GROUP BY id);
DELETE 4
test=# SELECT ctid, * FROM x;
 ctid  | id
-------+----
 (0,1) |  1
 (0,2) |  2
 (0,3) |  3
 (0,4) |  4
(4 rows)
`

keep in mind; the ctid is an internal thing so be careful.


How to install olaptrain sample schema on oracle 12c?

The problem is solved after changing the location for datafile in 'install_olaptrain.sql', and

changing the 'ho impdp' statement to the given below string resolves any login error in Oracle 12c:

ho impdp olaptrain/manager@//localhost:1521/my_pdb dumpfile=OLAPTRAIN12232009.DMP directory=OLAPTRAIN_INSTALL

But then "ORA-06512: at "SYS.UTL_FILE", line 536" occurs.


errno: 150 "Foreign key constraint is incorrectly formed"

It starts to work on 5.7.27 if second table uses matching DEFAULT CHARSET and COLLATE options:

CREATE TABLE IF NOT EXISTS `login`(
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `username` varchar(50) NOT NULL,
    `password` varchar(20) NOT NULL,
    PRIMARY KEY (`username`),
    FOREIGN KEY(`id`) REFERENCES `Register_Per`(`id`)
) Engine=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


PostgreSQL database size doesn't match used space on disk

I've updated query for computing database sizes (indexes included):

SELECT schema_name,
       pg_size_pretty(sum(table_size)::bigint),
       (sum(table_size) / pg_database_size(current_database())) * 100 as pct
FROM (
  SELECT pg_catalog.pg_namespace.nspname as schema_name,
         pg_total_relation_size(pg_catalog.pg_class.oid) as table_size
  FROM   pg_catalog.pg_class
     JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
) t
GROUP BY schema_name
ORDER BY pct DESC;

As for the tmp files, I've deleted all files older than 1 day:

find /var/lib/postgresql/9.6/main/base/pgsql_tmp/ -type f -mtime +1 -delete

at least in our setup queries typically takes minutes, at most hours. So, temporary files older than one day were probably left by some postgresql process that had crashed. Temp files are suffixed with process's PID, like pgsql_tmp13774.1 where 13774 should be PID of postgresql process.


Remove Column From Replicated Article without re-snapshoting in 2008r2

The article below talks about adding, dropping, and modifying columns in detail for 2008 R2 for various types of replication. I would start there, let me know if this is not what you were looking for.

  Replication supports a wide range of schema changes to published objects. When you make any of the following schema changes on the appropriate published object at a Microsoft SQL Server Publisher, that change is propagated by default to all SQL Server Subscribers:

  ALTER TABLE
  ALTER TABLE SET LOCK ESCALATION should not be used if schema change replication is enabled and a topology includes SQL Server 2005 or SQL Server Compact 3.5 Subscribers.ALTER VIEW
  ALTER VIEW
  ALTER PROCEDURE
  ALTER FUNCTION
  ALTER TRIGGER

  ALTER TRIGGER can be used only for data manipulation language [DML] triggers because data definition language [DDL] triggers cannot be replicated.

  You can use replication stored procedures to specify whether these schema changes are replicated. The stored procedure that you use depends on the type of publication.

Source


How can i give SECADM to instance owner in DB2?

Under 9.7, whichever ID creates the database is automatically granted DBADM WITH DATAACCESS WITH ACCESSCTRL and SECADM.

So you should be able to log in to the databases with that ID to perform the grants you need. If this came from another environment, you may have to create the ID locally in order to get into the database. Only an ID with SECADM can grant SECADM to another ID. And also with 9.7, you cannot GRANT to or REVOKE from yourself.

Now...as to your problem.....when you do not have EXECUTE on a NULLID package, then you may either need to perform a BIND of the appropriate bind file, or grant EXECUTE on that package. If it is a system package that pretty much everyone can use, you can always GRANT EXECUTE ON PACKAGE NULLID.SQLC2H23 TO PUBLIC.


Adding an Organization schema to existing person/relationship schema

If you want to keep things simple, it may be enough to add
organization(organization_id PK, ...) table and person_organization_relationship (person_org_relationship_id (PK) , person_id,organization_id ,reltype_id , date_from, date_thru). Depends on requirements you may or may not add unique constraint, on , for instance, (person_id, organization_id,reltype_id).  I would reuse the same relation table which is used for describing relationships between persons.  In many cases such model will be working just fine.

If more general model is needed, concept of Party can be added.  Party can be a person, or organization, or something else that interacts with the system .  For instance if RDBMS supports enums, you can have

PARTY (party_id PK, party_type enum('person','organization'), [common attributes]);

Then you either add 2 tables, PERSON and ORGANIZATION (which both have party_id as a primary key and foreign key to PARTY) and store their attributes separately, or store all attributes in PARTY table.

Concept of Party lets you store any type of relationship between person and organization,or 2 organizations , or 2 persons in one table.  Moving forward, it'll make other things easier to implement. For example, various contact information such as address(physical or electronic), phone, etc can stored in one place .
On the other hands, it has its price. The more universal model you are implementing, the more time you'll need to spend in the beginning.

So I'd recommend to use simplest model that satisfies requirements .

Some notes regarding current physical implementation of relationship table:

     UNIQUE KEY outer_affinity (reltype_id,person_id1,person_id2),
     UNIQUE KEY inner_affinity (reltype_id,person_id2,person_id1),
     KEY has_relationship_to (person1_id,reltype_id),
     KEY has_relationship_by (person2_id,reltype_id)

It's better not to have the least selective column (reltype_id)  as a leading column in outer_affinity and inner_affinity
For enforcing uniqueness it's enough to have one of outer_affinity, inner_affinity
I'd start with just two indexes UNIQUE KEY UQ_relationship_person_1_person_2 (person_id1, person_id2, reltype_id) and INDEX IDX_relationship_person_2(person_id2) (depends on typical queries it might be useful to person_id1 as second column to this index).
The table seems to miss date_from (and maybe date_thru) attribute .


Oracle 12c how to purge the FGA audit records before a certain date

Administering the Audit Trail

begin
  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP
  (
    audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
    last_archive_time => sysdate - 7
  );
end;
/

begin
  DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL
  (
    audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
    use_last_arch_timestamp => true
  );
end;
/


How extract cldr timezones from xml file to store in a DB table?

Partial answer: how to convert the relevant info to csv.

Edit the file to remove everything except this section:

<metazoneInfo>
    <timezone type="Africa/Abidjan">
        <usesMetazone mzone="GMT"/>
    </timezone>
...
    <timezone type="PST8PDT">
        <usesMetazone mzone="America_Pacific"/>
    </timezone>
</metazoneInfo>

Use an XML to CSV converter. (I used https://www.convertcsv.com/xml-to-csv.htm)

Result:

usesMetazone/0/_mzone,_type,usesMetazone/0/_to, ...
GMT,Africa/Abidjan,,,,,,,,,,,,,,,,,,,,,,,,,
GMT,Africa/Accra,,,,,,,,,,,,,,,,,,,,,,,,,
...
Wallis,Pacific/Wallis,,,,,,,,,,,,,,,,,,,,,,,,,
America_Pacific,PST8PDT,,,,,,,,,,,,,,,,,,,,,,,,,

The first line describes the XML attributes extracted. The remaining lines start with "metazone,timezone".

The additional columns describe historical changes; e.g. "from date, to date, metazone". That is, some time zones used different metazones over specified date ranges.

Care must be taken when interpreting any timezone that has such historical data. The metazone in first column is probably NOT the CURRENT timezone; this is easier to see in the XML, where "metazone 0" is the first metazone mentioned, hence probably the EARLIEST one (it has a "to" date but no "from" date).

In that situation, I think the CURRENT metazone is always the LAST one mentioned (column farthest to right, that has a .../_mzone header).

For example, timezone "Africa/Algiers":

XML:

<timezone type="Africa/Algiers">
    <usesMetazone to="1977-10-20 23:00" mzone="Europe_Western"/>
    <usesMetazone to="1979-10-25 23:00" from="1977-10-20 23:00" mzone="Europe_Central"/>
    <usesMetazone to="1981-05-01 00:00" from="1979-10-25 23:00" mzone="Europe_Western"/>
    <usesMetazone from="1981-05-01 00:00" mzone="Europe_Central"/>
</timezone>

CSV:

Europe_Western,Africa/Algiers,1977-10-20 23:00,1979-10-25 23:00,1977-10-20 23:00,Europe_Central,1981-05-01 00:00,1979-10-25 23:00,Europe_Western,1981-05-01 00:00,Europe_Central,,,,,,,,,,,,,,,,

In Spreadsheet, we see that rightmost two columns are:

usesMetazone/3/_from   usesMetazone/3/_mzone
1981-05-01 00:00       Europe_Central

So current metazone is Europe_Central, NOT Europe_Western from first column.

Except for this issue getting the correct "current" metazone, the first two columns can be easily imported to DB: stackoverflow thead.


How do I change where database diagram relationship lines attach in MySQL Workbench?

No, it's not possible to specify attachment points. That layout algorithm has a few flaws.


Index fillfactor and index pages in PostgreSQL

Table and index fillfactor are independent from each other.
Both tables and indexes are stored in files on the operating system. These files are organized in 8KB blocks. fillfactor determines to what extent these blocks are filled by an INSERT (in the case of a table) or the initial creation or extension on the right end (for an index).
Even though the fillfactors on table and index are independent, the indication for setting lower values is similar for tables and indexes: if the table receives many updates. Bear in mind that many table modifications also cause an index modification.
Note that a lower fillfactor on the table will only help if none of the updated columns are indexed. Otherwise, it is only a waste of space.
I would set a lower fillfactor on tables if the above condition is satisfied and the table receives many updates. I would not set a lower fillfactor on indexes unless I see that an index regularly gets fragmented by many updates.


Multiple Instances Of SQL Server Windows NT Eating Up Resources - What do they do?

When you install SQL Server it installs some shared components and also some components that are specific to the instance itself. In its simplest form, an instance can be likened to a virtual machine or a container running on a host server. You can have multiple instances but each one requires it's own resources and has it's own limitations in terms of how many databases may be created. Check out the Microsoft docs to understand more about instances and shared components.

Something to be aware of especially if you are new to SQL Server or come from an Oracle background is that you do not need to install more than one instance to have more than one database. Each instance of SQL Server can have up to 32767 databases on it. More information on limits can be found on Microsoft Docs.

A common scenario for having multiple instances is when you need to develop against different versions of SQL Server. For example, on my one of my development workstations I have 2008, 2008R2, 2012 and 2014 installed. However, I normally would not have them all active at one time.

From your question above it sounds like you don't have many databases so unless you need to isolate a development environment (such as having two separate 2014 instances e.g. one DEV and one QA) or support multiple versions of SQL Server, I would actually recommend uninstalling the instances that you don't require - not only to free up CPU and RAM but also to free up any disk space that was used. Be sure to back up anything you may need first. To uninstall SQL Server follow these instructions.

Alternatively you can disable the service's so that they do not automatically start when you boot your machine. The instructions on how to do this can be found here. The only side-effect to stopping the service is that you will no longer be able to connect to the instance. But you could easily restart it when you do need it.

If you do require all instances to be running at the same time, then I would suggest setting a limit to how much RAM and CPU resources are allocated to each instance. I suggest looking at the documentation on how to set the max memory and processor affinity. However, as a new user to SQL Server I would recommend one of the solutions I proposed earlier over changing server properties until you understand them better.


Why MySQL Workbench automatically sets NOT NULL for ID column that is PRIMARY KEY?

Explicitly setting the NOT NULL is simply an additional action to make this fact clear also for the non-experienced users. It doesn't hurt and you can safely ignore it.


How to maintain production database with less data and report database with all data

I would keep both, but do it slightly differently...

Keep the large table with all the data, but purge it after 3 months.  (More in a minute)

Build and maintain one or more Summary tables for reporting.  These will be much smaller than the main table and much faster for building Reports.  These are kept forever.

For purging, I would use PARTITION BY RANGE(TO_DAYS(...)) to break it into weekly partitions, then DROP PARTITION and REORGANIZE PARTITION every week.  Details here.  DROP PARTITION is orders of magnitude faster than DELETEing rows.

For Summary tables, see this.  I often see 10x speedup in report generation.

I have sacrificed saving "all" the data.  You may be unconfortable with that.  See the partition link, above, for an alternative to DROP, namely "transportable tablespaces".  With this, you turn the partition into a table (and remove it from the main table, having the effect of DROP).  Now this "table" (with one week's data) can be moved to some other place for safe keeping.  Or move it into a non-replicated database.  Or copy it to the server with a big disk that keeps it "forever".  Optionally, use "transportable tablespaces" again to hook it into a partitioned table at the target.

(This could be done by the "month" instead of by the "week", if you prefer.)


Installing service packs with SQL Server services stopped

You can definitely apply Service Packs or Cumulative Updates while the services are stopped. However, if there are steps that may require a reboot, you will be responsible for that.


Add multiple rows in a single row with different columns

You are on 10g, so PIVOT is unavailable, but still have the good old sum(case ...).

drop table reads purge;
drop table registers purge;

create table registers (id number,  n varchar2(2), prod_id number);
insert into registers values (331, '01', 112233);
insert into registers values (332, '02', 112233);
insert into registers values (333, '03', 112233);
insert into registers values (334, '04', 112233);
commit;

create table reads (read_id varchar2(3), register_id number);
insert into reads values ('011', 331);
insert into reads values ('012', 332);
commit;

SELECT
  sum(case when rg.n = '01' then rd.register_id end) as "RegisterID1",
  sum(case when rg.n = '02' then rd.register_id end) as "RegisterID2",
  sum(case when rg.n = '03' then rd.register_id end) as "RegisterID3",
  sum(case when rg.n = '04' then rd.register_id end) as "RegisterID4",
  count(rd.register_id) as "Count"
FROM Reads rd RIGHT JOIN Registers rg on rd.Register_ID = rg.ID
WHERE rg.Prod_ID = 112233;

RegisterID1 RegisterID2 RegisterID3 RegisterID4      Count
----------- ----------- ----------- ----------- ----------
        331         332                                  2


I'm going to assume there is a table with the activities and dates, and you are only inserting the conforming records into your table.  I'm going to call that data_source.
So we need to do a few things here:

Establish intervals of three months around traject_id for each date_performed.
Find the number of activities that occurred within those intervals, and a distinct count of activity_id.
Remove intervals that do not contain at least three distinct activities.
Ensure the intervals do not overlap then join to the original data.

So here goes:
WITH traj_interval AS
(
  SELECT
    interval.traject_id
   ,interval.start_date
   ,CASE
      WHEN interval.end_date < LEAD(interval.start_date,1,'9999-12-31') OVER (PARTITION BY interval.traject_id ORDER BY interval.start_date) THEN interval.end_date
      ELSE DATEADD(DAY,-1,LEAD(interval.start_date) OVER (PARTITION BY interval.traject_id ORDER BY interval.start_date))
    END AS end_date
  FROM
    (
      SELECT DISTINCT
        traject_id
       ,date_performed AS start_date
       ,DATEADD(MONTH,3,date_performed) AS end_date
      FROM
        data_source
    ) interval
  INNER JOIN
    data_source data_source
      ON data_source.traject_id = interval.traject_id
           AND data_source.date_performed >= interval.start_date
           AND data_source.date_performed <= interval.end_date
  GROUP BY
    interval.traject_id
   ,interval.start_date
   ,interval.end_date
  HAVING
    COUNT(DISTINCT data_source.activity_id) >= 3
)
SELECT
  data_source.traject_id
 ,data_source.activity_id
 ,data_source.date_performed
FROM
  data_source data_source
INNER JOIN
  traj_interval traj_interval
    ON traj_interval.traject_id = data_source.traject_id
         AND traj_interval.start_date <= data_source.date_performed
         AND traj_interval.end_date >= data_source.date_performed

So this handles gaps and overlapping intervals, which is the major headache from before.
This does not make the prettiest execution plan, but it has to be faster than performing loops.


Identify which parameters were passed to a procedure (Oracle)

So the way I do this is I don't use default values. Or rather, I set them to NULL as default. Then within my procedure body I check to see if it's equal to NULL and if so set it to a sane default. At that point you could mangle a flag or two in the same space to see what's being set or not set.

However, there's the edge case of if they intended to pass in NULL for some reason. However, most devs don't deal well with tripletcase, so I don't usually worry about that (additionally, if I'm passing in a value, I need to pass a value, not null, so null for me means never-was-defined)


Is there a way to restrict new database creation to a particular zone?

Answer is yes and no. Clustered system marks DB's primary location to shard what have less DB's. Trying to distribute evently. So, automatically you cannot define that location.

IF you log into specific replica set directly and create DB there, then that's it DB's primary location then.

However, you can use that movePrimary command (without mongos reboot) if you move sharded collection. You can shard collection what don't exists, you move that non-existing collection to wanted location and then "drop" that collection. DB have new primary location and all mongos knows now that new location. ;-)


You first have an (implied) CROSS JOIN between mass_categories and mass_groups, then LEFT JOIN to mass_prices. But the left join is using the id (group_id) only from one of the two tables for the join condition:

FROM
    mass_categories mc, mass_groups mg   -- the CROSS join
    LEFT JOIN mass_prices mp
      ON  mp.id_group = mg.id             -- LEFT join condition

Use both the group and the category id (and an explicit CROSS JOIN to not get a different error):

FROM
    mass_categories AS mc
    CROSS JOIN                         -- doesn't byte
    mass_groups AS mg
    LEFT JOIN mass_prices AS mp
      ON  mp.id_group = mg.id          -- LEFT join condition
      AND mp.id_categorie = mc.id
;

note:

You could still use the (implicit CROSS join) comma operator if you want but that would just make your life difficult. The two operators have different precedence, so you'd need to add parentheses as well:

FROM
    ( mass_categories AS mc , mass_groups AS mg )
    LEFT JOIN mass_prices AS mp
      ON  mp.id_group = mg.id          -- LEFT join condition
      AND mp.id_categorie = mc.id
;


Confused over encoding/locale in postgresql

The encoding defines the very basic rules how characters are represented in binary format (like @a_horse explains in his comment). It should be mentioned that the server encoding has to match the client encoding for successful communication. Postgres can translate if necessary, there is a dedicated setting client_encoding for this.

The locale is a superset of settings, which can be split up for PostgreSQL into

LC_COLLATE
LC_CTYPE
LC_MESSAGES
LC_MONETARY
LC_NUMERIC
LC_TIME

The settings of particular interest for you are LC_COLLATE (defines how strings are sorted) and LC_TYPE (defines the type of characters).
In older versions, these two settings could not be changed after a database had been initialized. Since Postgres 9.1 you can at least override the collation setting when needed.


Postgresql - how to list indexes on a table that is partitioned?

Workaround:

create or replace view vw_list_indexes
as
    select i.relname as indexname, t.relname as tablename, t.OID, idx.indisprimary
    from pg_class i
    join pg_index idx on idx.indexrelid = i.oid
    join pg_class t on t.oid = idx.indrelid
    where i.relkind = 'I';

select * from vw_list_indexes where oid = 'schema.table'::regclass;


SQL Server Stored procedures run very slowly after a while

What you're describing sounds like parameter sniffing to me. Parameter sniffing occurs because SQL Server caches the execution plan of the procedure if the plan is not found in the cache. Subsequent executions will use that version of the plan. In most cases, that's desirable since it eliminates compilation time. However, if the characteristics of the data accessed by the procedure's queries varies significantly when different parameters are passed, the result can be very poor performance.

Your description of the runtimes of short date ranges versus long ranges fits that pattern perfectly. I would guess that the query optimizer is choosing different indexes in the two scenarios.

You're on the right track for your solutions. WITH RECOMPILE can resolve the parameter sniffing in some cases, but recompilation is a high cost to pay on every execution. OPTION(RECOMPILE) on individual queries can definitely help and is sometimes the best answer, but again, it comes at a high cost. OPTION(OPTIMIZE FOR UNKNOWN) is another possibility, but it resolves the issue by providing a plan that's consistent (but may still be horribly inefficient).

Start by reviewing these articles, which explain parameter sniffing far better than I can in a short answer:

Kimberly Tripp, Building High Performance Stored Procedures
Kendra Little, Optimize for… Mediocre?

Then, check the execution plans for your procedure, looking for the specific queries that vary the most between executions. Try OPTION(RECOMPILE), OPTION(OPTIMIZE FOR UNKNOWN), or Kimberly's sp_executesql method on those, rather than the entire procedure.


What is the significance of sync_binlog?

Turning it on flushes binlog events to disk.  This costs some extra I/O.

Turning it off lets the events eventually go to disk.  If the one of the servers crashes, the Slave can be in an "impossible" position in the replication stream.  The 'fix' is to move its pointer to the beginning of the next binlog on the Master.

Recommend you turn it on.  If you are overloaded on I/O, then look around for other remedies, such as improving the worst query in the slowlog.


How to find database usage for a specific application action

Best scenario is only do this test and not let anything else run
on the server during the test.
If not isolate one application server for this purpose so you can
filter with host name or login (whatever is easier in your case).
Set up an extended event trace with the events you want to capture,
include necessary Global Fields.
Set up data storage type event_file so you can review the result later and even load
into a SQL Server database table.
Since you are capturing the trace under one minute I would not bother to
roll files.
If you are not familiar with Extended Event trace search for 'getting
started with extended events' and you will see a large amount of
useful resources.


Galera multi-nodes Master-Master issue with READ right after INSERT

This answer provided by @Bill Karwin explains how this is one of the drawbacks of Galera Cluster.

What are the drawbacks of using Galera Cluster instead of Master/Slave Replication?

"...Therefore there's still a small chance of reading stale data from another cluster node. There's an option you can set to force SELECT to wait until the queue of worksets has updated the database, but this means you have delays on SELECT..."

Refer to this article on how to force the SELECT to wait for the most up to date data. http://galeracluster.com/2015/06/achieving-read-after-write-semantics-with-galera/

You will need to set the "wsrep_sync_wait" session variable.


Your insert statement:

INSERT INTO test_table ( entity_id, nominated_by_48 )
      VALUES( MEMBERSHIP_ID, NOMINATOR_CONTACT_ID );

It's inserting MEMBERSHIP_ID into entity_id and looking at how MEMBERSHIP_ID is fetched, problem seems to be there. If you have multiple records but limit 1 is used, it will most likely (results can be indeterminate though) pick the same value.

Possible solution is to change the query for cursor_i and select entity_id along with the ID and just use that value for insert when you loop through (no need to fetch entity_id again within the loop.


Are there limits to SSRS 2014 RDL files going over to SSRS 2008 (or 2012)?

SSRS Report Definition Language for SQL Server Reporting Services 2014 cannot be reliably imported into previous versions of SQL Server Reporting Services.

The Report Definition Schema files were modified in 2010; SSRS 2014 uses that version, which supports features that are not supported in the 2008 version of the schema.

The 2010 schema is at: http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition/

The 2008 schema is at: http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition/


How do you extend the tablespace when using innodb_file_per_table?

This is interesting because last month (July 25, 2012 15 days ago) some asked a similar question about extending the system tablespace file ibdata1 : Database Design - Creating Multiple databases to avoid the headache of limit on table size

Please read that link and see if you would like to extend ibdata1 the way I detailed

To be totally honest with you, storing ibdata across separate volumes from the datadir is a bad idea , and storing .ibd files is even worse, because of

MetaData Issues : I wrote about this as well

Mar 25, 2012 : Why does InnoDB store all databases in one file? (See WARNING)
Oct 16, 2011 : Error 'Unknown table engine 'InnoDB'' on query. after restarting mysql
Sep 28, 2011 : How to Recover an InnoDB table whose files were moved around

Performance Issues : I wrote about that back in August 2011

MySQL InnoDB data file directly on a disk slice (fixed-size)?

You will improve things if you go with innodb_file_per_table. Why?

You only need one ibdata1 file
No data would reside in ibdata1
All data and index pages would not spread across multiple tablespaces

Controlling individual tablespace growth is rather simple. You must schedule proper maintenance windows for this:

For example, to shrink an InnoDB table named mydb.mytable simply run

ALTER TABLE mydb.mytable ENGINE=InnoDB;

With innodb_file_per_table on, the table shrinks.

With innodb_file_per_table off, ibdata1 just grows rapidly.

There is no autoextend feature for .ibd files. That only applies to ibdata1. In light of this, the maximum size of an InnoDB table stored in a .ibd file would be OS dependent:

In ext3, an InnoDB table can go to 2TB
In ext4, an InnoDB table can go to 16TB

To be more blunt, you should never attempt to spread .ibd to other volumes. Percona has strongly denounced this : http://www.mysqlperformanceblog.com/2010/12/25/spreading-ibd-files-across-multiple-disks-the-optimization-that-isnt/


How to store number of years, months and days in PostgreSQL?

You could use the interval data type here you can find a guide to start with: http://www.postgresqltutorial.com/postgresql-interval/

CREATE TABLE components
(
  id SERIAL PRIMARY KEY,
  name CHARACTER VARYING(30),
  lifetime INTERVAL
)
INSERT INTO components(id, name, lifetime) VALUES(1,'bulb','3 years 4 months 23 days 3 hours 2 minutes 1 second');


Why is it not possible to create indexes on temp tables in SNAPSHOT isolation?

In modern versions of SQL Server (2014+) you can create the indexes when you create the table, e.g.:
create table #t(id int primary key, a int, index ix_a nonclustered(a))

Also you can create the temp table before the snapshot transaction starts.
Almost all DDL is prohibited within a SNAPSHOT transaction.  ALTER TABLE and TRUNCATE TABLE are obviously not allowable.  CREATE TABLE is whitelisted.  CREATE INDEX could be whitelisted, but simply isn't.


Querying number of subscription to each publication

I was able to get the necessary info I needed by poking around a bit:

use Distribution;
select count(s.publisher_id) as 'Number of Subscribers', p.publication_id, p.Publisher_db, p.publication from MSpublications p
    left outer join MSmerge_subscriptions s on p.publication_id = s.publication_id

 group by p.publication_id, p.publisher_db, p.publication
 Order by Publisher_db, Publication_id asc


ORA-29339: tablespace block size 16384 does not match configured block sizes

your db_block_size is 8k and your db_16k_cache_size = 0.

Normally we don't build databases with mulitple block sizes because the optimizer does not take the different block sizes into account. If you still want to do this, start with something like

alter system set db_16k_cache_size = 16m;

and try again.
It does not have much to do with the filesystem blocksize but aligning your database storage blocks sizes with the storage blocksizes is performance wise a smart thing to do.


100+ DBs in one instance or divide and conquer : MariaDb Ubuntu 64bits

The term describing this architecture is 'multi-tenancy'. There are many q&as on here on that topic already, so presumably something can be learned from those.

You'll have to consider e.g.:

All customers in the same schema or one schema for each customer?
How many queries/sec each customer generates and how much your server can handle. If each customer has its own DB user, then MariaDB (at least more recent versions) has features to limit how many queries/sec and simultaneous connections are allowed.
Problems related to volume of data:

Backup: Can you do it non-disruptively? mariabackup is pretty good. One backup for all customers or a separate backup for each?
Disaster recovery and full restore from backup, how long each takes, and whether that is acceptable to your customers.

How to do schema changes: Non-disruptively to users is the ideal. Newer MariaDB versions can do a lot of ALTER TABLE operations with ALGORITHM=NOCOPY|INPLACE|INSTANT.
Stability / high availability: If all users are on the same server, then all users will be affected if there's a problem with that server. Are you planning to do any kind of replication?
Security: Isolating customers in different databases or even different servers will help with security, but comes at higher maintenance costs.


Isolation levels are for reading data. Not sure what you mean by "dirty write". Any RDBMS wont allow dirty writes - since they follow ACID properties.

A write operation will always take X (Exclusive locks) till the transaction is completed. Multiple concurrent transactions will use MVCC - sql server uses locking and row versioning since 2005.

Read up - Paul White's series on Isolation levels.


select from many to many by matching field within a column

Replace the ??? in your attempted query with

COUNT(DISTINCT Customer.Surname) = 1

So, the whole query, with a couple of improvements, would be

SELECT
  co.OrderID,
  CustomerCount = COUNT(distinct c.CustomerID),
  FamilyName    = MAX(c.Surname)
FROM
  dbo.CustomerOrderLink AS co
  INNER JOIN dbo.Customer AS c ON c.CustomerID = co.CustomerID
GROUP BY
  co.OrderID
HAVING
  COUNT(DISTINCT c.CustomerID) > 1
  AND COUNT(DISTINCT c.Surname) = 1
;

An inner join would be better here since orders without associated customers would not match the COUNT(DISTINCT c.CustomerID) > 1 condition anyway. Talking of that condition, if the CustomerOrderLink table can never have more than one CustomerID/OrderID pair (and it should not), then you can replace it with COUNT(c.CustomerID) > 1 or even COUNT(*) > 1.

Note that if Customer.Surname is a nullable column and some customers do have a null there, the above may not return what you expect, because COUNT ignores nulls (and so it will return 1 if a group contains a couple of customers with the same surname and the rest without a surname).


Difference between Create login, credential and user in sqlserver

Login:
A login is any principal that is granted access to a SQL Server instance.  The access can be granted to domain users, domain group, or SQL Server authenticated accounts.

Credential:
 A credential provides a mechanism to store login information for a domain account within SQL Server.  The credential can then be used to pass that authentication information from into another login or a proxy to grant it permissions to resources external to SQL Server.

.....use of Login
if you need to login and access a SQL Server instance, you’ll just use a login.  Regardless of your needs, if you need to get in and do work, the login is through route to go.  The other two won’t help you out at all.

Use of credentials..
Credentials get brought in when permissions from domain users need to be granted to accounts or services that wouldn’t usually have those permissions.  For instance, if an assembly required EXTERNAL_ACCESS, those permissions could be granted through the use of a credential.  Credentials can also be used to grant SQL Authentication accounts access to external resources.


User unable to edit a subscription after migration of SSRS from 2008 R2 to 2017

After a week of mucking around with permissions and getting ready to place a support call with Microsoft, Bart Edgerton's final suggestion to me in the Slack SQL Server Community workspace gave the solution:  I needed to give the user permissions to the folder containing the data source used by the report.

It's a bit odd to me as editing a subscription doesn't allow the user to edit the data source.  But, after a week puzzling over this and tweaking every permission under the sun, I'm at peace with it.

And, finally, this wasn't a migration issue.  At least, not related to my current 2008 to 2017 migration.  The same problem exists on the 2008 SSRS instance.  But, that instance was itself a migration from 2000 or 2005, I don't recall which.


MySql event ENABLED on both primary and replicate of RDS multi-AZ deployment

Looks like AWS RDS has a bug where when creating a read-only replica of a mysql database with events, it creates the events on the replica with ENABLED status instead of SLAVESIDE_DISABLED.

I verified this by recreating the failing replica. When it created the replica from the snapshot, it put in the events with the wrong state.

Dropping and recreating the event(s) on the primary resolved the issue by creating the same events with the correct status.


There are a couple of patterns for splitting a single logical entity across several physical tables.

Vertical partitioning puts some of the columns in one table and some in another. There can be several such additional tables, if required. All these tables share the same primary key. Columns that are used together are stored together so one page fetch read all the required values. The advantage is there are more rows per page so scans and aggregates require fewer IOs. Sometimes it can be tricky deciding which columns should go together. Every INSERT becomes proportionately more expensive as the number of partitions increases.

Horizontal partitioning splits the data by key range. For example, all users with a surname beginning A through M go into table User_1 and N through Z go into User_2. The application figures out which sub-table to use at run-time, usually algorithmically or through a look-up, though most DBMS these days will offer this as a build-in feature, implemented through DDL, transparent to the application. If you have hot spots in data writes this can spread the pain out. The optimiser can eliminate whole partitions from range scans, improving response time. Loading and removing whole partitions can be very fast metadata operations.

Sharding is where key ranges are moved, not just to a different table, but to a whole different instance of the DBMS. The application decides which instance to connect to, depending on key range. This is a scale-out technique. Some DBMS support this as a feature, such as Galera for MariaDB. The obvious cost is in additional hardware to run the other instances, duplication of reference data across all nodes to maintain RI and application complexity.

The techniques may be combined, of course, so a table could be both horizontally and vertically partitioned.

General advantages of splitting:

Indexes may becomes shallower, saving costs on index look-ups.
Each partition can write to separate disk, spreading IO load.

Disadvantages include:

Application complexity
It will be more difficult to ensure data integrity
Upgrades become trickier.
Purging data is incrementally more difficult to get right.

Basically, if you don't have a problem, and don't see one coming, don't bother. Horizontal partitioning using DBMS functionality is the only one worth considering as a pre-emptive measure. Even then it will require a re-write of 200GB. Do you have the disk and service window to accomplish this?


Is there a way to watch for schema changes in mySQL?

If you use standard MySQL or Percona Server (not RDS, for example), you will be able to use Percona Audit Plugin. You can configure it to only log ALTER TABLEs, DROP TABLEs, etc, see this page:
https://www.percona.com/doc/percona-server/LATEST/management/audit_log_plugin.html#filtering-by-sql-command-type

MariaDB has its own plugin, with a very similar functionality to filter queries (it allows to only log DDL).


MySQL: UNION query to get a line for each date

I have the following solution, which gives the desired result, except the values for the days that there is no entries for:

set @total=0;
set @sku_name='';
SELECT date, sku, total, CATEGORY FROM
   (
    SELECT date, sku, @total:=IF(@sku_name=sku, @total, 0)+qty AS total, @sku_name:=sku FROM
        (
        SELECT date, sku, SUM(qty) qty FROM
        (SELECT * FROM table1 UNION ALL SELECT * FROM table2 ORDER BY sku, date) union_table GROUP BY sku, date
        ) summary_table
    ) final_table
    INNER JOIN table3 USING (sku) ORDER BY sku, date;

Result:

+------------+------+-------+----------+
| date       | sku  | total | CATEGORY |
+------------+------+-------+----------+
| 2015-06-20 | ABCD |     4 | cat1     |
| 2015-06-21 | ABCD |     8 | cat1     |
| 2015-06-22 | ABCD |     4 | cat1     |
| 2015-06-23 | ABCD |     7 | cat1     |
| 2015-06-20 | EFGH |     0 | cat2     |
| 2015-06-21 | EFGH |     5 | cat2     |
| 2015-06-23 | EFGH |     8 | cat2     |
+------------+------+-------+----------+

I would give credits to @RolandoMySQLDBA who answered my question, where I get the algorithm from.

EDIT: Introduced new variable old_total

If you want to show the rows for all dates, assuming that first date in the min among all dates in both table, and the max date in the max among all dates in both tables, you can use this method:

set @total=0;
set @old_total=0;
set @sku_name='';

create temporary table tmp_table as
    select distinct date, sku from
        (select date from table1 union select date from table2) u1,
        (select sku from table1 union select sku from table2) u2
    order by sku, date;

SELECT tmp_table.date, tmp_table.sku, ifnull(total, @old_total), CATEGORY FROM
tmp_table left join
   (
    SELECT date, sku, @old_total:=@total, @total:=IF(@sku_name=sku, @total, 0)+qty AS total, @sku_name:=sku FROM
        (
        SELECT date, sku, SUM(qty) qty FROM
        (SELECT * FROM table1 UNION ALL SELECT * FROM table2 ORDER BY sku, date) union_table GROUP BY sku, date
        ) summary_table
    ) final_table using (date, sku)
    INNER JOIN table3 USING (sku) ORDER BY sku, date

The result:

+------------+------+---------------------------+----------+
| date       | sku  | ifnull(total, @old_total) | CATEGORY |
+------------+------+---------------------------+----------+
| 2015-06-20 | ABCD |                         4 | cat1     |
| 2015-06-21 | ABCD |                         8 | cat1     |
| 2015-06-22 | ABCD |                         4 | cat1     |
| 2015-06-23 | ABCD |                         7 | cat1     |
| 2015-06-20 | EFGH |                         0 | cat2     |
| 2015-06-21 | EFGH |                         5 | cat2     |
| 2015-06-22 | EFGH |                         5 | cat2     |
| 2015-06-23 | EFGH |                         8 | cat2     |
+------------+------+---------------------------+----------+


How can I use an array variable inside the IN operator for Oracle SQL?

WHERE PARENT_ID IN my_array;

This will not work. First, as the error message states, you are not allowed to use local collection types in SQL statements, you need to define them in the database. Second, that syntax does not exist.

So first you define the type:

CREATE TYPE arr_type is TABLE of VARCHAR2(11 BYTE);

And after that, you can use the collection with the TABLE() function as below:

set serveroutput on;
DECLARE
  my_array arr_type := arr_type();
  my_array_two arr_type := arr_type();
BEGIN

  SELECT MY_ID BULK COLLECT INTO my_array FROM XYZ_REQUEST;
  SELECT ANOTHER_ID BULK COLLECT INTO my_array_TWO FROM ABC_REQUEST
  WHERE PARENT_ID IN (select * from table(my_array)); -- <==========================

   FOR i IN 1..cm_array.COUNT LOOP
     DBMS_OUTPUT.PUT_LINE(my_array(i));
   END LOOP;
END;
/


How can I create a join on three tables, each from a different database?

If you are using MySQL you can do this:

select *
from dob.dob_state
join numid.numid_history as t2
    on t2.create_time = dob_state.create_time
    and t1.last_name = t2.last_name
    and t1.first_name = t2.first_name
join entity.entity as t3
    on t3.create_time = t2.create_time
    and t2.last_name = t3.last_name
    and t2_first_name = t3_last_name;

Tip: You don't have to write inner join, just join.


If you store old text and differences, you will have to always apply everything to get current text. So I suggest storing current text and diff (sort of reversed changes) to get previous version. Keep table for those diffs like (text_id, version_number, diff).

For differences you can sure use standard diff tool. And if you want to save space, use some compression (either on mysql side or application side).

Do you need to search in those texts? If yes then in current version only or in all versions? The latter might be a bit bigger challenge to do effectively.


MSSQL select count entities in date range

You should group by the result of division by 60 starting by the minimal date per Id:

declare @UserAction table (Id int, Date datetime, Action nvarchar(200))

INSERT INTO @UserAction VALUES
    (111, '2000-01-01 15:30:10', 'Action Made'),
    (555, '2000-01-01 15:30:10', 'Action Made'),
    (111, '2000-01-01 15:30:20', 'Action Made'),
    (111, '2000-01-01 15:31:05', 'Action Made'),
    (111, '2000-01-01 15:31:10', 'Action Made'),
    (555, '2000-01-01 15:31:10', 'Action Made'),
    (111, '2000-01-01 15:32:05', 'Action Made'),
    (555, '2000-01-01 15:32:05', 'Action Made'),
    (555, '2000-01-03 15:35:10', 'Action Made'),
    (555, '2000-01-03 15:35:20', 'Action Made'),
    (111, '2000-01-03 15:36:05', 'Action Made'),
    (555, '2000-01-03 15:36:05', 'Action Made');

with cte AS
(
select *,
       min(Date) over(partition by id) as min_dt
from @UserAction
)

,cte_gr AS
(
select *,
       datediff(ss, min_dt, Date) / 60 as gr
from cte
)

select id,
       min(date) as [From],
       max(date) as [To],
       count(*) as cnt
from cte_gr
group by id, gr;


You should get the correct result if you perform your calculation using the sums for each part instead of performing the calculation on each row and summing the result.

rextester: http://rextester.com/ZGQ51639

Declare @Calc Table
(
  employee varchar(100)
  ,annualsales decimal(10,2)
  ,dep1sales decimal(10,2)
  ,dep2sales decimal(10,2)
  ,dep3sales decimal(10,2)
  ,dep4sales decimal(10,2)
  ,dep5sales decimal(10,2)
  ,tax decimal(10,2)
)

Insert Into @Calc (employee, annualsales, dep1sales, dep2sales, dep3sales, dep4sales, dep5sales, tax) Values
('emp1', '1111.00', '26.12', '83.00', '16.55', '21.77', '87.08', null)
,('emp1', null, '28.07', '11.00', '101.24', '47.03', '76.19', null)
,('emp1', null, '35.25', '7.00', '33.64', '43.54', '43.52', null)
,('emp2', 1954.85, '1120.65', '0', '0', '0', '0', 392.70)

Select
employee
,AnnualSales=sum(isnull(annualsales,0))
,DeptSales  =sum(isnull(dep1sales,0)+isnull(dep2sales,0)+isnull(dep3sales,0)+isnull(dep4sales,0)+isnull(dep5sales,0))
,Tax        =sum(isnull(tax,0))
,[SomeCalc] =(sum(isnull(annualsales,0))
             -(
               sum(isnull(dep1sales,0)+isnull(dep2sales,0)+isnull(dep3sales,0)+isnull(dep4sales,0)+isnull(dep5sales,0))
             +(sum(isnull(tax,0))*.75)
              )
             )
            /nullif(sum(isnull(annualsales,0)),0)
from @Calc
group by employee
order by employee asc


Below t-sql code will surely help you.

DECLARE @vXML XML;
SET @vXML = '<salesorder>
   <ordnum>123</ordnum>
   <orddate>2017-04-17</orddate>
   <details>
      <detail>
         <itemtype>Stationery</itemtype>
         <itemdesc>Pencil</itemdesc>
         <specifications>
            <specification>
               <name>Qty</name>
               <value>5</value>
            </specification>
            <specification>
               <name>Description</name>
               <value>Pencil H1</value>
            </specification>
            <specification>
               <name>Price</name>
               <value>1.50</value>
            </specification>
         </specifications>
      </detail>
      <detail>
         <itemtype>Stationery</itemtype>
         <itemdesc>Pencil</itemdesc>
         <specifications>
            <specification>
               <name>Qty</name>
               <value>10</value>
            </specification>
            <specification>
               <name>Description</name>
               <value>Pencil H2</value>
            </specification>
            <specification>
               <name>Colour</name>
               <value>Red</value>
            </specification>
            <specification>
               <name>Price</name>
               <value>2.75</value>
            </specification>
         </specifications>
      </detail>
   </details>
</salesorder>';

SELECT
       salesorder.Col.value('ordnum[1]', 'int')
       , detail.Col.value('itemtype[1]', 'varchar(255)')
       , specification_qty.Qty AS Qty
       , specification_price.Price AS Price
FROM @vXML.nodes('//salesorder') AS salesorder(Col)
    CROSS APPLY salesorder.Col.nodes('details') AS details(Col)
    CROSS APPLY details.Col.nodes('detail') AS detail(Col)
    CROSS APPLY detail.Col.nodes('specifications') AS specifications(Col)
    CROSS APPLY (SELECT specification.Col.value('value[1]', 'int') AS Qty FROM specifications.Col.nodes('specification') AS specification(Col) WHERE specification.Col.value('name[1]','VARCHAR(255)') = 'Qty') AS specification_qty
    CROSS APPLY (SELECT specification.Col.value('value[1]', 'decimal(3,2)') AS Price FROM specifications.Col.nodes('specification') AS specification(Col) WHERE specification.Col.value('name[1]','VARCHAR(255)') = 'Price') AS specification_price

Little description about the solution:

As the provided XML is a multilevel XML, we will need to process the data in chunk. Hence we need to segregate the multiple levels of xmls in multiple tables and then cross apply them to get the required result. If it was a single level XML data then a single SELECT * FROM Tbl method could have worked.

Reference Links:

Single level XML solution provided by Sanchitos on Stack Overflow

Multilevel XML solution provided by George T on Stack Overflow

Query XML in SQL Server for Different Hierarchy Levels using Cross Apply

Hope this will help.


support of multiple databases on different hosts by pgpool2

no. With this kind of configuration, pgpool2 will not throw an error.
Pgpoll configuration is much more complicated than that. I used to think it as easy as you list config. Later, I just follow the full procedure.
Here for your reference.
https://www.pgpool.net/docs/latest/en/html/example-cluster.html
Good luck


SSRS using external images through tls 1.2 leaves src(unknown)

I ran into this issue when attempting to run images from IIS Server which was locked down to TLS1.1 and TLS1.2

The issue was two fold

I had to update the Registry to add the following keys

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\v2.0.50727]
"SystemDefaultTlsVersions"=dword:00000001

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\.NETFramework\v2.0.50727]
"SystemDefaultTlsVersions"=dword:00000001

per this article https://support.microsoft.com/en-us/help/3154519/support-for-tls-system-default-versions-included-in-the--net-framework

I also had to use the following Entry in the configuration file for Reporting Services

  [Install Location]/Reporting
  Services/Bin/ReportingServicesService.exe.conf
      inside the runtime element

  Default SQL Server 2016 Location: "C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\ReportServer\bin\ReportingServicesService.exe.config"

<AppContextSwitchOverrides value="Switch.System.ServiceModel.DisableUsingServicePointManagerSecurityProtocols=false;Switch.System.Net.DontEnableSchUseStrongCrypto=false" />

After all of this all I had to do is restart the reporting service and it was fine

Note that i do have tls 1.1 and 1.2 locked down my SSRS and SQL Server (not sure if this is also a requirement for this fix)

My System Sql Server 2016 and Windows Server 2012 R2


The only way to get binlog rotation in hours is to script it

Here is the code

#!/bin/bash
HRS=16
mysql -uroot -prootpass -e"PURGE BINARY LOGS BEFORE NOW() - INTERVAL ${HRS} HOUR"

Put this code in a script called /root/mybinlog_rotate.sh

Make sure you run chmod +x /root/mybinlog_rotate.sh

Add that script in the crontab to rotate every 30 min

0,30 * * * * /root/mybinlog_rotate.sh

This is a little heavy handed but it can be done

I suggested this months ago : Maximum overall size of MariaDB binary logs


Backup & recovery strategies for Partitioned Database in Sybase ASE

Yes is a good idea, if the DB is spread across different devices. ( that ideally would be on different volumes ). The percentage of the volume of corruption would be less. However to restore you still have to restore the whole DB, which in separate devices and volumes can be faster. ( I/O is spread, less concurrency )
No. You have to recover the Db(s) that the corrupted device holds. So if you have a DB that has 1 corrupt device and you have those logical connections (good practice), you just need to change on OS, the connection to the new physical device. Then restore DB.
How is you replication ? Repserver or disk mirroring? For repserver, i do not think so, your source ASE would stop functioning, and you would have to stop replication to restore the db. Disk mirroring I don't know.

Response to updated question:

My point 2 was:

You will need to recover the whole db, unless you know exactly which objects where present on that device (and segments of it) that got corrupted, if yes then you can rebuild them manually( tables, procs, views... ). If you don't know or the labour to do it manually is too great, complete db recover.

What I mentioned about the logical connections was this, for example:

You have DB called TEST1 on the the following devices:

data01
data04
log02
data03

When you create the device with disk init, you have to give the path. But the path is not directed to the device itself, it goes to a soft link. Your data01 for example:

disk init.... physname='..sybase/data01.dat" ...

On the OS that sybase/data01.dat will be pointing to /dev/data01.dat. This way if you need on the OS to replace a corrupted device, you won't have to rebuild the database, you just create a new raw file, point data01.dat to it, and on DB restore data (LOAD). Faster process, than drop old DB, create new, load.

Well I'm pretty sure your errorlog would have some information about that corrupted device and also about your dumps had issues.
If your dumps were successfully dumped you should be able to use them on your restore (good devices). So I don't really get why you had to go back so many days. The thing is for instance if you have a corrupted device, it will manifest whenever some action tries to use it, (I/O). So your dumps would have failed i believe.


If you are just looking to log this information, you can set up an Extended Events session and capture the error_reported event.  Here is an example:

CREATE EVENT SESSION [ErrorCapture]
ON SERVER
ADD EVENT sqlserver.error_reported
(
    ACTION
    (
        sqlserver.client_hostname,
        sqlserver.database_id,
        sqlserver.sql_text,
        sqlserver.username
    )
    WHERE
    (
        [severity] >= (11)
    )
)
ADD TARGET package0.asynchronous_file_target
(
    SET filename=N'C:\SqlServer\Testing\ErrorCapture.xel'
)
WITH
(
    MAX_MEMORY=4096 KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=30 SECONDS,
    MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=OFF,
    STARTUP_STATE=ON
);
GO

ALTER EVENT SESSION [ErrorCapture]
ON SERVER
STATE = START;
GO

To test this out, here is a test error with RAISERROR():

raiserror('This is a test error', 2, 1);
go

Then by looking at the XE log through the sys.fn_xe_file_target_read_file system function, you will be able to see all of the logged errors.


I suspect that you did not execute this command with sqlplus. Not every sql program can handle the desc command.

You can either use sqlplus or get the table definition with the following SQL command:

SELECT dbms_metadata.get_ddl (object_type, object_name, owner)
FROM   all_objects
WHERE  owner = '<owner>'
  AND  object_name LIKE '<whatever_table>%';

This will give you the complete table definition with all column definitions.


You can change this behavior with the ALTER SERVER CONFIGURATION statement.  The flexibility to modify all of the necessary parameters (path, max files, max file size) should give you enough control to get it to where you want.  See the bottom of the referenced link above for samples/examples.  Here is one copied from the source:

ALTER SERVER CONFIGURATION
SET DIAGNOSTICS LOG MAX_SIZE = 10 MB;

HOWEVER ... these diagnostic logs are great tools for troubleshooting.  Whereas you can limit the amount of retained data, I would much rather get more storage to store the default amount of data (1000 MB, which is almost free in today's day) for the time when you will need as much diagnostic data as you can get.


Performance impact of including result column in indexed columns

Based on the information given, I will take a shot at an answer...

You stated

  If I add identifiedtex as the last column indexed in the index, will this speed up a lookup of the texture since the result is part of the index?

Let me say that you have the following query:

SELECT identifiedtex FROM texels
WHERE texelx = 210938
AND texely = 378432
AND texelhue = 23;

What would be the effect of having an index (call it Index3)

texelx, texely, texelhue

versus this index (call it Index4)

texelx, texely, texelhue, identifiedtex

When you retrieve identifiedtex using only Index3, it will require an additional table lookup to ascertain the identifiedtex from the table.

When you retrieve identifiedtex using only Index4, it will an index-only lookup because all mentioned columns are in the index. It is known as a covering index.

I mentioned covering indexes in my past posts:

Mar 12, 2012 : How to index this table (a_level, b_level, item_id)
Oct 17, 2012 : Combining columns in index
Nov 13, 2012 : Must an index cover all selected columns for it to be used for ORDER BY? (See My Conclusion)

So to answer your original question, yes adding identifiedtex will index make for a faster query.


Indications from obscenely high Operator Costs in Query Plan?

JNK,

You meant something like this?

http://sankarreddy.com/wp-content/uploads/2011/03/CropperCapture2.jpg

This is an estimated execution plan that's generated based on the statistics by the client tools and note that its NOT an engine issue. If you want MSFT to fix this issue then please vote for this connect item item.

https://connect.microsoft.com/SQLServer/feedback/details/436184/huge-operator-cost-in-estimated-execution-plan

Since you mentioned SQL Server 2008, I would encourage you to look at the WAIT STATS information when the job is being run. Look at why the execution is NOT moving along and figure out the bottleneck.

http://blogs.technet.com/b/sqlos/archive/2008/07/18/debugging-slow-response-times-in-sql-server-2008.aspx

Also, look at the sys.virtualfilestats information and see if there are significant IO lags and as well as memory usage.

HTH


What is the reason of Host 'host_name' is blocked in MySQL?

At this point, you may need to run FLUSH HOSTS and see if this unblocks.

The MySQL Documentation says this about FLUSH HOSTS:

  Empties the host cache tables. You should flush the host tables if
  some of your hosts change IP address or if you get the error message
  Host 'host_name' is blocked. When more than max_connect_errors errors
  occur successively for a given host while connecting to the MySQL
  server, MySQL assumes that something is wrong and blocks the host from
  further connection requests. Flushing the host tables enables further
  connection attempts from the host. See Section C.5.2.6, “Host
  'host_name' is blocked”. You can start mysqld with
  --max_connect_errors=999999999 to avoid this error message.

Why should a host get blocked to begin with ??

According to the MySQL Documentation:

  If you get the following error, it means that mysqld has received many
  connect requests from the host 'host_name' that have been interrupted
  in the middle:  Host 'host_name' is blocked because of many connection
  errors. Unblock with 'mysqladmin flush-hosts'   The number of
  interrupted connect requests permitted is determined by the value of
  the max_connect_errors system variable. After max_connect_errors
  failed requests, mysqld assumes that something is wrong (for example,
  that someone is trying to break in), and blocks the host from further
  connections until you execute a mysqladmin flush-hosts command or
  issue a FLUSH HOSTS statement. See Section 5.1.3, “Server System
  Variables”.

  By default, mysqld blocks a host after 10 connection errors. You can
  adjust the value by starting the server like this:

shell> mysqld_safe --max_connect_errors=10000 &

  If you get this error message for a given host, you should first
  verify that there isn't anything wrong with TCP/IP connections from
  that host. If you are having network problems, it does you no good to
  increase the value of the max_connect_errors variable.


How to reduce redundant time-series data in MySQL into a function?

YOUR SAMPLE DATA

DROP DATABASE IF EXISTS rep;
CREATE DATABASE rep;
USE rep
CREATE TABLE timeseries
(
    id             INT NOT NULL AUTO_INCREMENT,
    polltime       DATETIME NOT NULL,
    lastupdatetime DATETIME NOT NULL,
    name           VARCHAR(20) NOT NULL,
    value          VARCHAR(20) NOT NULL,
    PRIMARY KEY    (id),
    KEY series_ndx (lastupdatetime,name,value,polltime)
);
INSERT INTO timeseries
(polltime,lastupdatetime,name,value) VALUES
('2016-09-09 15:19:45','2016-09-09 15:19:44','x','15,2'),
('2016-09-09 15:19:50','2016-09-09 15:19:44','x','15,2'),
('2016-09-09 15:19:55','2016-09-09 15:19:44','x','15,2'),
('2016-09-09 15:20:00','2016-09-09 15:19:56','y','16,05'),
('2016-09-09 15:20:05','2016-09-09 15:19:58','c','16,12'),
('2016-09-09 15:20:10','2016-09-09 15:20:12','x','15,98');
SELECT * FROM timeseries;

PROPOSED QUERY

SELECT SEC_TO_TIME(MAX(polltime)-MIN(polltime)) timediff,lastupdatetime,name,value
FROM timeseries GROUP BY lastupdatetime,name,value;

PROPOSED QUERY EXECUTED

mysql> SELECT SEC_TO_TIME(MAX(polltime)-MIN(polltime)) timediff,lastupdatetime,name,value
    -> FROM timeseries GROUP BY lastupdatetime,name,value;
+----------+---------------------+------+-------+
| timediff | lastupdatetime      | name | value |
+----------+---------------------+------+-------+
| 00:00:10 | 2016-09-09 15:19:44 | x    | 15,2  |
| 00:00:00 | 2016-09-09 15:19:56 | y    | 16,05 |
| 00:00:00 | 2016-09-09 15:19:58 | c    | 16,12 |
| 00:00:00 | 2016-09-09 15:20:12 | x    | 15,98 |
+----------+---------------------+------+-------+
4 rows in set (0.00 sec)

mysql>

GIVE IT A TRY !!!

Please note that you will have to index this table to make the aggregation fast


maintaining full text index on large table

Full text in SQL Server should have no trouble keeping up with your workload.  I currently support a full text index in SQL Sever that is  approximately 500,000  rows with 125,000  rows being inserted and deleted everyday.  Query load peaks at around 200 full text searches/sec. Response time it's fairly consistent in the .5 to 1.5 sec range.

Recommendations for full text

Use auto change tracking.  It just works and you don't have to schedule anything.
Create sql agent alerts for full text population errors.  Populations can fail (they rarely do).  When they do fail you have to resume them manually.  Having a sql agent job kick off an "ALTER FULLTEXT INDEX ON myTable RESUME POPULATION"  makes it a non-event.
Consider trace flag 7646  if your query load and the volume of updates are both really high.  This flag is applicable to 2008/2008 R2 (not 2012).  It reduces some blocking on internal data structures and was documented in a SharePoint best practices document. (You probably won't need this)
Otherwise, you probably won't need to do any additional tuning.  It typically just works.

Sql 2012 introduced substantial improvements in scalability and performance  so upgrade if you can.

As for how to manage the inserts and updates, just update the rows add you would normally.  I wouldn't recommend truncate/reload, but otherwise don't worry about it.


how to insert data from one table to another

You'd use an INSERT..SELECT query like this:

insert into custom (sku, OrderIn, OrderOut)
select
a.id,
a.stock,
sum(b.prod_order)
from
table1 a inner join table2 b on a.id=b.id
group by a.id

More details at http://dev.mysql.com/doc/refman/5.1/en/insert-select.html

The basic idea is that you build up your select query to define the set of data you want, and then use that as an input to an Insert statement to write it into the table you want to put it in.

In your question you've not defined how the created column is generated, so that isn't covered in this query.


The only option will be to script out the index and create it on the new table in the other database.


How to determine optimal value for pg_stat_ramdisk_size on Amazon RDS?

The pg_stat_ramdisk_size controls the location and size of the stats_temp_directory on RAM. It's easy to determine the size on bare metal or out of the RDS byt issuing a du -h over the directory specified on the variable. There is no way yet to access this folder size at RDS, will try update here if any feature is added for this.

The used space on this folder, will depend strictly on the settings you have for track_* variables (See 27.2.1. Statistics Collection Configuration ). It is not necessary a huge amount of space, although is very write intensive as it holds the counters per global and database basis (counters over database per table/index/functions). So this also means that is more related to the amount of databases and tables+indexes (as pgstat_write_statsfiles at postmaster/pgstat.c iterates, writing the PgStat_StatDBEntry) than the amount of data.

You don't want to have it smaller(as a crash will happen), so you may want to start with a value over the 256MB.

Update:

Per configuration, it won't let you set pg_stat_ramdisk_size higher than 1GB.

Update 2:

  PGSTAT_FILE_FORMAT_ID 1 byte

  PgStat_StatTabEntry  = 164 bytes

  PgStat_StatFuncEntry = 28 bytes

  closingChar = 'E'

  describers is a char (T or F in this case)

First of all, as it'll explained later, not all the tables, indexes and functions are written on the db statsfile. Basically, a basic formula will be _SizeOfDBStatFile = PGSTAT_FILE_FORMAT_ID + describers + (tableCount * PgStat_StatTabEntry) + (funcCount * PgStat_StatFuncEntry) + closingChar_.

A select count(*) * 164 "size in bytes" from pg_class where relkind IN ('r','i','S') will give you the estimate for the tables if all of them were flushed on the file. Also, you need to do the same within pg_proc, but instead the factor will be 28 bytes.

This file is one per database.

  PgStat_StatDBEntry = 180 bytes

  PgStat_GlobalStats = 92 bytes

  PgStat_ArchiverStats = 114 bytes

  describer is a char (D)

The global statfile is smaller, and contains only the global stats and the counters across databases. Should be something close to _PGSTAT_FILE_FORMAT_ID +describer + PgStat_GlobalStats + PgStat_ArchiverStats + (PgStat_StatDBEntry + describer) * numDatabases_.

So, as you can see, the limitation imposed by AWS in regarding is way above the amount of data held on this directory in most of the databases that can run inside RDS expectations.

Reference


Log shipping - failed over to secondary, how to fail back

Check this article -- Reversing Log Shipping! Now what you need to do is to take the log backup with NORECOVERY on the primary and restore this log on the secondary with RECOVERY. This will preserve the log chain.


How to execute alter database commands from DbUp scripts?

This is DbUp's default behavior; nothing you can do on the SQL Server side. There's a similar discussion on DbUp's github issues section with workaround that may work for you so do check it out. I don't think there's a DbUp tag on SE so if those workarounds don't work for you, try posting on that page or their discussion group.


Unfortunately no, you can't exclude anything from a backup.

If you are talking about Windows Logins then it probably isn't that big a deal.  The SIDs won't match anything on their network (very unlikely anyway) so all they will have is the usernames without any of the permissions associated.  SQL Logins of course will work fine as long as they create a login to match the users.

The only ways I can think of to avoid having the users in your DB when you do the backup are going to range from annoying to difficult.  Basically you either create a copy of your DB that you keep up to date but doesn't have any of the database principals you don't want or you script out all of the permissions, drop them, run the backup, and re-create the permissions.  Of course I wouldn't try that second option during the business day.


Database Default Locations doesn't persist

1) Make sure the account running the SQLservice should have the required rights on the directory you are wishing to change:

2) Also, you can give a try with below T-SQL

You can also do the same with T-SQL and here is the T-SQL code to do the same.

/* I am trying to change to D drive in below example*/

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, N'D:\DATA'
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, N'D:\DATA'
GO

Most important:-After Changing the Default Locations

You must stop and start the SQL Server service to complete the change


The way to go is using a GIST index. This sort of index helps checking if a value is contained within a range.
Because you want to filter also by person_id, you will need to install the btree_gist extension. In addition, you should convert the valid_from and valid_until columns to a single tstzrange column, which is a range column that holds timestamp with time zone range limits.
After doing that, you can create an index on person_id and the new range column which you can call valid_range:
CREATE INDEX ON diary2person USING GIST (person_id, valid_range);

Good luck!


Why is SSMS inserting new rows at the top of a table not the bottom?

In the SQL world, order is not an inherent property of a set of data. Thus, you get no guarantees from your RDBMS that your data will come back in a certain order -- or even in a consistent order -- unless you query your data with an ORDER BY clause.
From Craig Freedman:

Combining TOP with ORDER BY adds determinism to the set of rows
returned.  Without the ORDER BY, the set of rows returned depends on
the query plan and may even vary from execution to execution.

Always use ORDER BY if you expect a well-defined and consistent order in your result set. Never rely on how your database may store the rows on disk (e.g. via a clustered index) to guarantee a certain ordering of data in your queries.


grant all privileges of all tables in database to a user

GRANT ALL PRIVILEGES ON .... is MySQL privs syntax.

You need to do:

GRANT ALL ON SCHEMA.TABLE TO USER

See the documentation.


Oracle listener does not restart because port 1521 is not released fast enough after stop

This is Working As Designed™.
TCP socket lifecycle is managed by the TCP stack outside the database process(es), asynchronously, and involves various delays when transitioning between states. It's entirely conceivable that on a fast machine the listener will terminate and start back up within few milliseconds, faster than the socket is fully released.
Since you have no control over the TCP stack logic, your only alternative solution is, in my view, to introduce a slight delay before starting the listener (as you already did). You may not need the full 30 seconds; try smaller values.


Am I able to create table using EM Express Oracle 12c

You can not do that in EM Express.

Oracle Database 12c: EM Database Express

I'm trying to perform a task I used to do in DB Control.  Where is it in EM Express?

  Although EM Express provides support for basic administrative tasks,
  it is not a direct replacement for DB Control.  As explained in the
  Architecture section, EM Express is built inside the database server
  and cannot perform actions outside the database.  Also, EM Express is
  focused primarily on providing performance management and monitoring
  functionality, and is not meant to be an all-around database
  administration tool.


The following articles explore the advantages and disadvantages of the -E start-up option:

Focus on Fast Track : Understanding the –E Startup Parameter

Summary:

-E changes the proportional fill algorithm. Rather than moving on to the next file after a single extent allocation, the movement occurs after 64 extents have been allocated.

These extent allocations do not need to come from a single table. For example, when loading two tables in parallel with –E enabled, both tables consume the 64 extents in the single file before moving on to the next file. -E does not alter the fact that SQL Server allocates one extent at a time.

The purpose of –E is to enable large-I/O read-ahead.  If two tables that share the same files/filegroup are loaded in parallel then the advantage of –E is removed. The extents will be fragmented resulting in small-I/O read-ahead.

-E was originally put into the code to allow index builds and rebuilds to use 256KB of each file before round-robining, and so get better sequential I/O for large data warehouses. It was originally a 'benchmark special' and had very little testing, it was only documented because it was used during benchmarks (and so had to be documented).

Microsoft White Paper (pdf)

Community Wiki answer generated from a comment on the question by @Kin


You could do that. I'm thinking something along the lines of having an active and inactive date on your fact table for each product for each customer. Then you could mark a previous product with an inactive date.

This should allow you to query it for date ranges that are include a specific date to get what their product list was for a specific point in time.


Initialize replication from multiple split backup files

As no one got back to me (fair enough - maybe you've got jobs too!) I have tested this.

We didn't use this method in the end for the production task because the powers that be decided it was ok/best to use 7-Zip to split the single .bak file.

However, the answer is - you only need to specify the a file of the set and it works fine (I did this on my test setup):

Restore as normal > when specifying the Backup to use as the initialization point, give it the first file name.

I assume this is because the LSN is listed in this file (whether it contains the right bit of the Log data or not is irrelevant - as it's only looking for a point to reference through the Log Reader agent).

In fact to double check this, I have just gone back to my test setup and commenced replication (P2P in this case as in SQL 2008R2 it allows  you to specify the Backup file through the SSMS GUI Wizard - call me lazy!) by using the 3rd file in my 4 file backup set, which worked fine. I believe this proves that the LSN is in the header of each file in the Backup set and also why using a Log backup or Differential backup also works.

Just to make sure that anyone in the future that needs the same answer to the multi-file backup initialization question can find this - I need to include the initial question as I would have asked it so they find this when they search:

Q: Can I use a split Backup set to Initialize replication?

A: Yes you can, simply specify any of the bak files used in the Restore to initialize.


How to connect to another db using stored procedures?

You want to use a Database Link (DBLink).

http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/statements_5005.htm

http://psoug.org/reference/db_link.html

-- create tnsnames entry for conn_link
conn_link =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = perrito2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orabase)
    )
  )

CREATE DATABASE LINK conn_user
USING 'conn_link';

SELECT table_name, tablespace_name FROM user_tables@conn_user;

@conn_user on the end of a procedure or table name will tell the pl/sql engine to query the db link specified for that info.


Conditional field compare based on the value of another field (Case statement?)

I don't know what is that you are trying to do with this logic but the T-SQL query that you are asking for will most probably look like this :

DECLARE @IsComplete integer,@Isup integer,@LatestAlertid integer
        SET @Isup = 1
        SET @IsComplete = 1
DECLARE @id INT
DECLARE @getcoinid  CURSOR

SET @getcoinid   = CURSOR FOR
SELECT coinid from Alerts
OPEN @getcoinid
FETCH NEXT
FROM @getcoinid  INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN

    --where  IsComplete is not 1 and Isup is also not 1
        IF @Isup <> (SELECT Isup from Alerts
                     WHERE IsComplete <> @IsComplete
    and coinid=@id)
    --this query will execute
            (SELECT *
            FROM Alerts as Alerts
            join  Coins as Coins
            on Coins.coinid = Alerts.coinid and Alerts.threshold <= Coins.LastPriceBtc
            WHERE Alerts.IsComplete <> @IsComplete and Alerts.Coinid =@id);
        ELSE
        --where IsComplete is not 1 but Isup is 1
        --this query will execute
            (SELECT *
            FROM Alerts as Alerts
            join  Coins as Coins
            on Coins.coinid = Alerts.coinid and Alerts.threshold >= Coins.LastPriceBtc
            WHERE Alerts.IsComplete <> @IsComplete and Alerts.Coinid =@id);
    FETCH NEXT
    FROM @getcoinid INTO @id
END

CLOSE @getcoinid
DEALLOCATE @getcoinid

Output:

Alerts for test:

Coins for test:


How to improve this product rental schema?

When designing schemas that have a "current state" (in your case: What inventory is currently in use) and a historical log of states, it is generally a good idea to split those two concepts into two different tables.

In your case, you would have:

inventory_status
==========================
id                       (PK)
inventory_item_id        (FK)  --> inventory_items
location_id              (FK)  --> locations (Store 1, Store 2 etc.)
checked_out_flag
status_type_id           (FK)  --> status_id (rented out, stolen, on service etc.)
comment

And you will have

inventory_events
==========================
inventory_item_id          (PK, FK) --> inventory_items
location_id                (FK)     --> locations (Store 1, Store 2 etc.)
checked_out_flag
status_type_id             (FK)     --> As per above
comment
event_date                 (PK)     --> Together with the item and perhaps the status.

Whenever status (or any other thing you want to track) changes, you write a new row in inventory_events and update the row in inventory_status. You may even decided to do this with a trigger. You could argue that this is a slight break with normalisation. But until someone invents a temporal database with reasonable performance, this is the fastest and easiest way to query the database.

From your description, it is not clear to me if an inventory_item can have more than one status at the same time (for example, can a car be BOTH rented out and stolen at the same time?). If each inventory_item can only have one status, you can collapse the table inventory_item and inventory_status into one without breaking normalisation.

A design like this has the advantage that joins are clean and easy to write. You do no need to look for the latest status - as that is always present in the inventory_status table.

A lot of ERP systems handle the balance sheet in a very similar manner - with a snapshot table (the balance sheet) and a log of transactions that add up to it. With the risk of jumping to conclusion, this may be the analogy that Pieter is cleverly hinting at.


You can change the parameter by editing postgresql.conf or with ALTER SYSTEM via SQL. Don't forget to reload the database.
But you should tell the application development team that they have messed up. If the application relies on a certain setting of timezone, then the application has to set that parameter itself. The parameter can be changed using the SET SQL statement.
If you don't care about time zone handling by PostgreSQL at all, you should use the data type timestamp without time zone.


inserting large quantity of data into mysql table

Dump the source data into a file:

SELECT  a.id,  b.id INTO OUTFILE '/tmp/result.txt' FROM `src` a INNER JOIN src b  WHERE a.id <> b.id

Tune MySQL for large writes:

innodb_flush_log_at_trx_commit=2
innodb_log_file_size=2G

See how to change the log size

Start a pipe to split results.txt in chunks

pt-fifo-split --lines 1000000 /tmp/result.txt

(pt-fifo-split is a part of Percona Toolkit)
Load the dump

while [ -e /tmp/pt-fifo-split ]; do mysql -u <user> -p<pass> -e "LOAD DATA INFILE '/tmp/pt-fifo-split' INTO TABLE tgt" <tgt db>; done

(if you have problem with "File '/tmp/pt-fifo-split' not found", try  this solution )


If you're concerned about failed logins, that implies that your server is accessible from the Internet.  It shouldn't be.

There is, however, a way to get the information without resorting to the general log.

According to the manual you can trigger logging these failed connections to the error log with the system variable log_warnings:

  If the value is greater than 1, aborted connections are written to the
  error log, and access-denied errors for new connection attempts are
  written.

This is a dynamic variable, but changing it at run-time does not appear to trigger the logging behavior.  Add this to your my.cnf in the [mysqld] section:

log-warnings = 2

Restart the server daemon.

You should then start to see failed logins in your MySQL error log.

I don't know what you mean by "saving password details."  The password is sent from the client to the server as...

SHA1( password ) XOR SHA1( "20-bytes random data from server" <concat> SHA1( SHA1( password ) ) )

...so you're not going to find the client's attempted password getting logged anywhere.  The server doesn't know what password the client used.  It only knows whether it used the correct one, by doing its own calculation of the hash the client would have returned if the client had known the right password.


SQL Server 2012 install hangs during the RunRemoteDiscoveryAction

The install is no longer hanging.  After opening a call with Microsoft we discovered the problem was trying to open the registry of the other node on the cluster.  Additionally we were unable to use admin shares (i.e. \NODE1\C$).  Once we enabled the Admin Shares, I was able to perform the installation and get past the point where it was hanging.

Enable Admin Shares (i.e. \NODE1\C$ should be accessible from NODE2)


Local login impersonation not working with a linked server

The error you have is unrelated to logins as such.

This is caused when SQL Server tries to "pass through" the NT login token to the remote server. It doesn't have permission to pass the token through. The remote server looks for this because the local servers connects with Integrated Security.

You need "Security Account Delegation" to be configured for the local server.

As for the default mapping...

What MSDN says is that if you have "MyDomain\bob" locally then you have an "virtual" entry "stored" locally called "MyDomain\bob". There is no reconciliation of the 2 servers between the 2 servers. This is the same as running this:

EXEC sp_addlinkedsrvlogin 'MyLinkedServer', @useself = 'TRUE';

Note that "MyDomain\MyGroup" is not mapped: only discrete NT users and SQL logins. Not NT Groups.

This is all described in "Security for Linked Servers"

  The default mapping for a linked server configuration is to emulate the current security credentials of the login. This kind of mapping is known as self-mapping. When a linked server is added by using sp_addlinkedserver, a default self-mapping is added for all local logins. If security account delegation is available and the linked server supports Windows
  Authentication, self-mapping for the Windows authenticated logins is supported.

  ...

  If security account delegation is not available on the client or sending server, or the linked server/provider does not recognize Windows Authentication Mode, self-mapping will not work for logins that use Windows Authentication.


Rank variable value in MySQL is always being set to null

= is the assignement operator only for SET statements,in your case is a comparison operator,for SELECT use rank := rank+1

Example

SET @var=1;
SELECT @var = 1 + 1; -> This compares @var which is one to 2 so it returns 0.

Result 0

SET @var=1;
SELECT @var := 1 + 1; -> This actually adds up.

Result 2


How do I extract a repeating string pattern from multiple rows?

Here is a sample of how I would do this:

select
      a.id
    , substring(a.value,T.N,7)
from
    (
    values
        (1,'All of R489804 R489805 R489806 R489807 R489808'),
        (2,'SP fr R308999                                 '),
        (3,'R308777, R104621, R708931                     ')
    ) a(id,value)
cross apply (select N from Tally where N<len(a.value)-5) T(N)
where substring(a.value,T.N,7) like 'R[0-9][0-9][0-9][0-9][0-9][0-9]';

Here, I am using a Tally (or numbers) table to walk the length of each value, extracting substrings of length 7 that match the required format.